texts: SQL Server 2016 SP1 Managed Backup to blob storage

texts:
I'm trying to configured managed backup for SQL Server 2016 SP1 to Azure's new cool blob storage and am only receiving errors. This is for a VM running in Azure. The documentation I've found is conflicting, but here's what I understand so far:

Cool blob storage only supports block blobs, not page blobs (reference)
"SQL Server Managed Backup to Microsoft Azure uses the Backup to Block Blob feature" (reference)
"Only a SAS URL is supported" by the managed_backup.sp_backup_config_basic procedure (reference)

With that in mind, I created a new cool blob storage account and a container called sqlbackups. I then created a shared access signature for the container with full permissions.

With this data, I then attempt to set the default backup configuration for the server:

EXEC msdb.managed_backup.sp_backup_config_basic
@enable_backup=1,
@container_url='https://mycoolblobstorage.blob.core.windows.net/sqlbackups?st=...',
@retention_days=30

Unfortunately, this just results in an error of:

  SQL Server Managed Backup to Microsoft Azure cannot configure the default backup settings for the SQLServer instance because the container URL was invalid. It is also possible that your SAS credential is invalid.

I've regenerated the SAS token several times without success. I've also tried creating a credential, which I think is needed only for the legacy page blobs method.

CREATE CREDENTIAL
[https://mycoolblobstorage.blob.core.windows.net/sqlbackups]
WITH IDENTITY = 'Shared Access Signature',
SECRET = '?st=...'

That seems to have no effect. Can someone help me get this working?


texts: MYSQL binary log file not capturing in slave machine

texts: mysql binary log files are not getting captured in slave machine, its available in master machine. When i check the binary log files its of size 1kb without any data in slave.


texts:
when I run xp_sendmail

exec master.dbo.xp_sendmail @recipients='me@hotmail.com',
@subject='terrrible thing happened',
@message='test'`

I am getting an error

  Msg 18025, Level 16, State 1, Line 0
  xp_sendmail: failed with mail error 0x80004005

It is SQL Server 2000. Outlook is set on this box. I can send emails from the Outlook.
It worked before. We just moved the server to DMZ.

We disabled Trendmicro antivirus's firewall


texts: Can SQL profiles be deployed to other Oracle Databases?

texts:
When using The SQL Tuning Advisor in SQL-Developer. I get often some recommendations to accept a SQL-Profil. I guess it is a good idea to do so.

But is this similar to proposed indexes, which I can deploy to similar databases or is this something, that must be analyzed for each individual database?


texts: Troubleshooting memory pressure - how to avoid paging out SQL Server?

texts:
I have received the email below that states that an alert has been fired and

  a significant part of SQL Server process memory has been paged out

should I raise the Minimum server memory to 1740 too?

  Microsoft SQL Server 2005 - 9.00.5000.00 (X64)    Dec 10 2010 10:38:40
    Copyright (c) 1988-2005 Microsoft Corporation   Standard Edition
  (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

There are other applications, even monitoring applications running on that server.

You can have a glimpse of those applications by looking at the picture below.

We received an alert at 12:15:37 PM on the wrsleic1 default instance that "a significant part of SQL Server process memory has been paged out."

We've investigated the alert and found that SQL Server previously reported this condition last year on 13/04/2015. SQL Server is currently allocated a maximum memory of 1.7 GB while the server has 4 GB physical memory available to it (and a dynamically sized page file that is currently 5 GB).

At the time of the alert SQL Server had it's maximum memory reserved while not necessarily actively using the cached data. Therefore when the server detected memory pressures from an external process (potentially IIS but we can't determine this retrospectively), SQL Server trimmed its working set to accommodate the requirement and is currently utilising approximately 900 MB.

The OS is responsible for memory management and SQL Server is designed to respond to memory release requests as it did at the time of the alert. Therefore if there was no perceivable impact to performance on the application that uses the hosted databases on this instance then we don't recommend any further action.

Lock Pages in Memory

The lock pages in memory has not been enabled for any sql server account.

I was going to change this, however, I decided to leave this server as it is because:

1) it is running on standard edition sql server 2005 SP2 and on this page it says

  Standard Edition Up until SQL Server 2005 SP3 Cumulative Update
  Package 4 and SQL Server 2008 SP1 Cumulative Update Package 2 'Lock
  Pages in Memory' was not supported in SQL Server Standard Edition.

2) after enabling it - needs a reboot according to this page

  Question:         “Weather we require Lock Pages in Memory in SQL
  Server 64-Bit”?

  Answer:            YES

3) We should (hope so) migrate this server to sql server 2014 soon

this page has a bit more about lock pages in memory and a reminder:


texts: Maintenance plan to delete backup and restore history not working

texts:
I have a maintenance plan set up on our SQL Server 2012 that contains only a "History Cleanup Task", which is configured to delete the following data if older than 4 weeks:

Backup and restore history
SQL Server Agent job history
Maintenance plan history

It runs every day at 3:00 a.m. without error, however I've only just noticed that it doesn't appear to be deleting records from the backupset table and the job completes in 0 seconds.

How can I fix it, or get more info about what's going wrong?

UPDATE

Thanks for the response but from some of the comments it appears people think I am having trouble deleting old backup files. I am actually trying to clean up the history data of old backup job execution, which I understand lives in the msdb database.

The reason I want to is that we've encountered long delays and ballooning server resource usage just when trying to drop a database, as a consequence of many years of backup history sitting in msdb.

Here is a good analysis of the problem:
https://sqlserver-help.com/2014/07/10/troubleshooting-slow-delete-database-from-management-studio/

The last line of the blog post is:

  If you clean msdb backup history regularly, you might not face the
  issue though. There is maintenance plan to do that. Try it out!

And this is what I'm attempting to do now!


texts: set global sql mode but showing a different mode

texts:
I have this command to run in my docker file

mysql -e "SET GLOBAL sql_mode = 'STRICT_ALL_TABLES';"

But when i check the mysql mode it shows as

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.01 sec)

Does anyone has any experience with this ?


texts: Rebuild dataguard configuration when standby is broken

texts:
I have an Oracle Data Guard configuration with two servers:

oradb (physical)
oradbstb (standby)

Both are running with Oracle 11g R2.

A few days ago I had problems with the standby (the server was turned off and the primary files were not received ... which were subsequently deleted).

I would like to know how to reconfigure the standby server from scratch. What would be the steps that I must carry out?

On the primary database I make a full backup with rman every Saturday and the rest of the days I make a backup of archives also with rman.

How should I go about restoring my standby database again?
What steps should I follow?


texts:
Im hoping you guys can help me.

I have a table full of users logging in and out.
Example row:
username,startdatetime,enddatetime

Im trying to select hourly how many users were active.
I Found the following:

select Time_Stamp_Hour=dateadd(hh,datepart(hh,StartDateTime), cast(CAST(StartDateTime as date) as datetime)), Count(*) as amount
from dbo.Sessions
group by dateadd(hh,datepart(hh,StartDateTime), cast(CAST(StartDateTime as date) as datetime))
order by Time_Stamp_Hour desc

This indeed groups together the amount of sessions started per hour. but im looking for how many would be active.  i cannot seem to figure out how i would build a query like that.
So bassicly grouped per hour a count which falls between Start and End datetime


texts:
I have SQL Server 2008 R2 that runs multiple jobs over night, the problem is SQL Server will eat up all RAM and CPU, then it will not release the RAM resources after the job finishes, as a workaround this problem, I run a schedule task for windows to kill the SQL process (taskkill /im sqlservr.exe /f)

Would anyone know a better way to release RAM?

Below is the SQL server info:

Microsoft SQL Server Management Studio      10.50.1600.1
Microsoft Analysis Services Client Tools    10.50.1600.1
Microsoft Data Access Components (MDAC)     6.0.6002.18005
Microsoft MSXML                             3.0 5.0 6.0
Microsoft Internet Explorer                 9.0.8112.16421
Microsoft .NET Framework                    2.0.50727.4216
Operating System                            6.0.6002


texts: Linked server returned message "Query timeout expired."

texts:
We have two separate SQL Servers. On one server we have a data warehouse (DWH), on the other we have sales information database.

Now on the DWH server there is an ETL job that collects the information from the sales server. The job runs daily after midnight. The DWH collects the information via linked server from the sales database.

Now, the most time the ETL job runs without any problems. But sometimes it fails because of query timeout. We have found out, that there is a specific pattern: The failure happens every 11th day. So on the 11th day the ETL job fails to collect information.

The following error occurs:

  SQLNCLI11 for linked server “my linked server” returned message “Query timeout expired.”

Note: The job fails usually 10 minutes after start.

We have searched everything and could not found out what this issue causes. Also we know that the amount of data is every time, almost the same. Also there is no any scheduled job that runs all 11 days or something.

The remote query timeout on the linked server is set to 0.

Our next step will be, to turn off the antivirus programm on the sales server, to check if this causes the problem.

Does anyone have any clue or idea, where i can search further to find the problem?


texts: Postgresql SELECT item from JSONB array - array as top-level json object

texts:
I have JSONB column which stores the data in this format

[
    {
        "item1": "value1",
        "item2": "value2"
    },
    {
        "item1": "value3",
        "item2": "value4"
    }
]

I am trying to select 'item1' with query

SELECT jsonb_array_elements(data)->'item1' FROM test_table

Error I am getting is:

cannot extract elements from an object

How should I change the query to make it work?


texts: what is good approach for storing different category product and also to implement filter on it?

texts:
I am having Product table where I am having multiple product category, and different product category have different attribute. as given is my table design
images
-----
id
desc
url
timestamp

images
----
image_id
imagesable_id (refer multiple table id)
imagesable_type (type of model targeted)

product
---------
id
user_id (refer user)
title
subtitle
price
metaData
timestamp

Category
--------
id
Category
parent_id
timestamp

product_category
-----------------
category_id (refer category)
product_id  (refer product)

Attribute
----
id
attribute
timestamp

attribute_value
-----
id
attribute_id
product_id
value
timestamp

Where product->metadata Store the value in json to avoid joins.
want to know is this approach is good or should i use some other way and also want to implement filters
like if some one select music i should able to filter product according to artiste and if some one searches book i should able to filter the product by author.
please give me proper guide how should i do to make database clean and optimized way.
thank you


texts: Query execution time in Oracle SQL Developer

texts:
I know I can use set timing on to see how long it took to execute a query after the execution is finished.

But is it possible to see current execution duration in Oracle SQL Developer while the query is still running?

SQL Server Management Studio as well as old Query Analyzer have such a feature:


texts: Need to write MDX for time dimension in Mondrian

texts:
This is my Dimesion and Measure:

<Dimension type="TimeDimension" foreignKey="ReqDateKey" name="Date">
      <Hierarchy name="YQMD" hasAll="true" allMemberName="All Dates" primaryKey="date_id">
        <Table name="dim_date" alias="">
        </Table>
        <Level name="Year" column="year" parentColumn="date_entered" type="Numeric" uniqueMembers="true" levelType="TimeYears">
        </Level>
        <Level name="Quarter" column="quarter" type="Numeric" uniqueMembers="false" levelType="TimeQuarters" captionColumn="quarter_label">
        </Level>
        <Level name="Month" column="month" type="Numeric" uniqueMembers="false" levelType="TimeMonths" captionColumn="month_label">
        </Level>
      </Hierarchy>
    </Dimension>
    <Measure name="CountAutheticatedMedicine" column="DrugAuthCode" aggregator="count" visible="true">
    </Measure>

I have to find Measure in drill down fashion as per Year->Quarter->Month Hierarchy. I have only one year(2013) data. I have properly design my cube and it works for other dimensions but I stuck in time dimension. I refer from here but couldn't find the right MDX.


texts: Hash index vs. btree index performance on inserts and updates

texts:
I have table which is several time bigger than available RAM. Table size is 181 GB, 92 million rows. PostgreSQL 10.

There is field with uuid in the table.
And there is need to insert into the table several hundreds rows, from time to time.

What is better, hash index or b-tree index for fast inserts? The table is practically never read, it just store archive information. The index is necessary only to check what there is no row in the table with the same uuid.

Currently, inserts work really slow. Insert of 1 million rows now works for more than 4 hours, still running. Typically, it works for about 5 hours.

The query looks like this:

insert into TableName (...)
select [list of fields]
from table_data_forinsert;

No where condition, the source table contains only data which needs to be inserted.
And how to insert faster?

I think about create btree index, and sort data by uuid before insert.
May it help to improve performance for btree index? Or may be it is possible to do something else to improve performance? I not want to make the table unlogged, it is necessary to not lose the data.

Is any approach how to make inserts faster for hash index?

And same question for updates. What is faster for updates, b-tree index or hash index, if there is update of several hundreds of thousands of rows in big table? Are any methods how to make updates faster?
Like may be sort data in source table or something else?


texts: What is the best way to cross check the record in multiple collections

texts:
I am developing the application in which system will show the campaign content to users which he/s never seen. To keep the logs either the specific user seen the specific content or not, i am maintaining logs of user activity in MongoDB collection. Here's what a campaign record in the database looks like.

campaigns

{
    "campaign_id": "123",
    "campaign_image": "<image path>",
    "min_user_age": 10
}

Here is how the user's activity log collection like

user_activity

{
    "i123i12391i2391i23kjnsa": {
        "user_id": 12,
        "campaign_ad_id": 34234,
    },
    "xajeou424u2934y293y4293": {
        "user_id": 32423,
        "campaign_ad_id": 34234,
    },
    ...
}

The user_activity collection will have approx 7 million records and campaigns will have more than 10 thousand records. I will always have user's id to get the campaign to show him. Here i want to get only those campaigns from campaigns collection which are not seen by the specific user.

I can do like fetch records from campaigns and then check that their records do not exist in user_activity collection based on specific user_id.

$campaigns = db.getCollection('campaigns').find({});

Loop though $campaigns and make sure activity does not exist for particular campaign_id and user_id.

$isExists = db.getCollection('user_activity').find({"campaign_id": 34234, "user_id": 12});

What is the best way to get the campaigns that are not seen by the user?

I am using mongodb 3.4.4.

Thanks


texts: Why does a Columnstore index build often wait on SLEEP_TASK?

texts:
I'm building a Columnstore index right now. The output from sp_whoisactive:

This is a dev machine. Nothing else is running. The server has 8GB of memory available to it. The CPU is nearly idle. The build seems to be in the global dictionary sampling phase because sqlservr.exe is doing random disk IO and writing almost nothing.

Does this SLEEP_TASK indicate that I can take action to make the index build go faster? Why is the build "sleeping" sometimes?

This is SQL Server 2014 SP1.


texts: Create a sub query for sum data as a new column in SQL Server

texts:
Suppose that I have a table named tblTemp which has this data:

|  ID | AMOUNT |
----------------
|   1 |     10 |
| 1-1 |     20 |
| 1-2 |     30 |
| 1-3 |     40 |
|   2 |     50 |
|   3 |     60 |
|   4 |     70 |
| 4-1 |     80 |
|   5 |     90 |
|   6 |    100 |

ID will be formatted as X (without a dash) if it's only one ID or (X-Y) if it's a new ID (Y) is child of (X).

I want to add a new column (Total Amount) to output as below:

|  ID | AMOUNT |   Total Amount |
---------------------------------
|   1 |     10 |        100     |
| 1-1 |     20 |        100     |
| 1-2 |     30 |        100     |
| 1-3 |     40 |        100     |
|   2 |     50 |         50     |
|   3 |     60 |         60     |
|   4 |     70 |        150     |
| 4-1 |     80 |        150     |
|   5 |     90 |         90     |
|   6 |    100 |        100     |

The "Total Amount" column is calculated as the sum of the amounts column for the same X ID.

In order to get the parent ID (X), I use the following SQL:

SELECT
  ID, SUBSTRING (ID, 1,
                 IIF (CHARINDEX('-', ID) = 0,
                        len(ID),
                        CHARINDEX('-', ID) - 1)
  ), Amount
FROM
  tblTemp

How can I write a query like this in SQL Server 2012?

You can use sqlfiddle here to test it.


texts: Postgres one to many query, returning array of objects, with another array of objects inside

texts:
I'm building a full stack web app that has a postgres database, and contains an organisation table, and a vendor table.
There can only be one organisation, whilst there can be many vendors, every vendor must be linked to one organisation, and there can be many vendors that are linked to the same organisation. I.e:

I'm using node/express to handle the backend API, with the results of the query being returned in the json format - response.json(res.rows).

I need to return a single array of objects that contains all organisation IDs, and the associated organisation names, then inside each organisation I need an array to list of all vendor IDs, and vendor names.
Something like this to be returned:

I hope that makes sense :/


texts:
I have a table that has 3 column. city_id, city_name and city_geo. city_geo type is geometry. and i have a another table that has a vehiclelog. I want to query if the latitude and longitude from vehiclelog table is inside the city_geo. I can get the result but not so accurate it's also get the Point that near in city_geo. this is my query

        SELECT  v.`City`,v.`Time`,c.`city_name`,v.`Latitude`,v.`Longitude`
        FROM `city` c, `vehiclelog` v
        WHERE c.`city_name`='$c' AND v.`plate`='$p' AND v.`Date` = '$d'
        AND  MBRCONTAINS(c.`city_geo`, POINT(v.`Latitude`, v.`Longitude`))

I plot it on google map to verify the result and i get

as you can see there are markers outside the polygon that are not suppose to include in result I want only the markers inside the polygon or city_geo. Im using MySql v5.0 I also try MBRCONTAINS, MBRCrosses, Contains and Crosses all same result.


texts: Sql 2016 STD SP1 Always On with DB Mirroring

texts:
I have 2 standard editions of sql 2016 sp1.
Since only 1 dB is allowed for always on I have to setup other 4 db’s in db mirroring.

My always on endpoint is already using port 5022.
Is there a place in db mirroring where I can change 5022 to let’s say 7022?

Thank you


texts: Failed transaction replicated and thrown errors on remaining Percona XtraDB cluster nodes

texts:
I am using Percona XtraDB cluster 5.5.37. I have run one alter command (to add unique constraint) on node1. But due to duplicate keys,alter command failed on it. But that failed statement passed to remaining nodes 2,3 and error logged like below.

160524  6:08:21 [ERROR] Slave SQL: Error 'Duplicate entry '176-60-FFP-1' for key 'constraint_name'' on query. Default database: 'db'. Query: 'alter table table_name add constraint constraint_name unique (col1, col2, col3,col4)', Error_code: 1062

My question is why its passed to other nodes, when it has failed on first node itself. It should not passed right. Please let me know where i am thinking wrong.


texts: How to maintain order when modeling outline in graph database?

texts:
(TaskPaper) outlines have a natural hierarchy relationship, so a graph database seems like a great fit. Each item in the outline is a node of the graph that points to its parent and children. I also really like the idea of modeling TaskPaper tags as nodes that any tagged items point to.

However, there is one problem: the order of items is important in an outline, but a simple graph doesn't keep that information. What is the best way to maintain the order of items when an outline is stored as a graph?

The method should be efficient when items are added/removed. New items may be inserted before existing items, so a simple increasing counter/timestamp won't work.

The goal is to store and query a giant outline that can't fit in memory (just the necessary items are streamed to/from the user). Two common ways of querying this outline would be:

Linear: get the first n=100 items when everything is "expanded."
Breadth first, with depth first traversal of certain items: only get the top level items, fulling expanding a few selected items. (If fully expanding an item would exceed n=100 items, stop expanding.)

Common updates would be:

Add/delete a new item somewhere in the hierarchy. Most inserts will probably be appending after the last child of an item. (Basic editing.)
Add/delete a new sub-tree (Copy-paste a section of an outline.)

I don't think other database types would be better than a graph database, but I'm open to using other databases (relational, NoSQL, etc).


texts:
I added remote host info on pg_hba.conf as:

host db1 all 10.6.169.88/27 trust

and also I did

listen_addresses = ‘*’
tcpip_socket = true

on postgresql.conf.

But still the remote client can not connect to db. What is the missing part about this? Remote client and host server is in different locations but with IT policies adjusted with correct ports and routes. So remote client is normally accessible to host server.

Here is what remote client gets as error:


texts: MySQL keeps getting “connect” commands 100X per minute!

texts:
My server was having performance issues, things loading slow, pages not loading, etc so I ran the "top" command after logging in via SSH. I noticed the CPU kept jolting up to 40%, on and off, when literally no one is accessing the website. I have other (much less powerful) servers with resting heartrates at 1-5% so I know something is wrong. I log into MySQL, enable general logging and this is what I find:

    event_time Descending 1     user_host   thread_id   server_id   command_type    argument
    2014-09-20 04:35:07     [root] @ localhost []   46833   0   Connect     root@localhost on
    2014-09-20 04:35:07     [root] @ localhost []   46834   0   Connect     root@localhost on
    2014-09-20 04:35:04     [root] @ localhost []   46832   0   Connect     root@localhost on
    2014-09-20 04:35:03     [root] @ localhost []   46829   0   Connect     root@localhost on
    2014-09-20 04:35:03     [root] @ localhost []   46830   0   Connect     root@localhost on
    2014-09-20 04:35:03     [root] @ localhost []   46831   0   Connect     root@localhost on
    2014-09-20 04:34:58     [root] @ localhost []   46828   0   Connect     root@localhost on
    2014-09-20 04:34:57     [root] @ localhost []   46827   0   Connect     root@localhost on
    2014-09-20 04:34:48     [root] @ localhost []   46826   0   Connect     root@localhost on
    2014-09-20 04:34:41     [root] @ localhost []   46823   0   Connect     root@localhost on
    2014-09-20 04:34:41     [root] @ localhost []   46824   0   Connect     root@localhost on
    2014-09-20 04:34:41     [root] @ localhost []   46825   0   Connect     root@localhost on
    2014-09-20 04:34:39     [root] @ localhost []   46822   0   Connect     root@localhost on
    2014-09-20 04:34:37     [root] @ localhost []   46821   0   Connect     root@localhost on
    2014-09-20 04:34:36     [root] @ localhost []   46820   0   Connect     root@localhost on
    2014-09-20 04:34:33     [root] @ localhost []   46819   0   Connect     root@localhost on
    2014-09-20 04:34:30     [root] @ localhost []   46818   0   Connect     root@localhost on
    2014-09-20 04:34:29     [root] @ localhost []   46815   0   Connect     root@localhost on
    2014-09-20 04:34:29     [root] @ localhost []   46816   0   Connect     root@localhost on

There is literally something like 100 "connect" commands mySQL is processing every minute. I'm sure this is what is causing my performance issues. But my problem is that I don't know where these commands are coming from or how I might go about tracing the source. Any ideas?


texts: Should ERD derived attributes be included in logical tables?

texts:
I have created my entity-relationship diagram (ERD for brevity) and it has derived attributes.

When it comes to database design using a relational schema, I've designed my tables and linked everything properly.

I don't know if I should add a column to my table that has a derived attribute? For example, the derived attribute is calculated as follows:

Total = Price * Quantity

And it is related to the following illustrative table schema:

Customer (
    ID,
    CustomerName,
    ItemsID,
    Total
);

Should Total be included in the Customer table?


texts:
We moved from SQL Server 2000 to SQL Server 2005. The client software, which I can not change, creates a user without option

CHECK_POLICY = OFF;

After creating a user, I have to run the command

ALTER LOGIN username WITH CHECK_POLICY = OFF;

Disable the policy, as recommended, I can not.

Is it possible to disable the default CHECK_POLICY that without her CREATE LOGIN user created with CHECK_POLICY = OFF?


texts:
I am aware of Oracle Express edition which is up to version 11g, which is pretty dated. However, I have read that Oracle are changinging/have changed their licensing of newer editions so that we will no longer have to wait years before a newer Express edition becomes available.

I teach SQL to users from different backgrounds, some of whom use Oracle at work. It would be useful to point them to a package they can practice with. However, I find Oracle’s web site somewhat perplexing.

Which editions are currently available free for personal use, such as training?
Is there also a free edition available to a small business, such as with 4 computers?


texts: Unable to locate database.mdf in production server

texts:
1) I want to attach a database from my development server to my production server.

The database ESpiceHerbs from my development server is located at:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA

2) When I log in using my production server credentials and right click on the Databases folder in Object Explorer and select Attach Databases and click Add,  I cannot find it there! :(

3) I am really confused as to why it cannot be located although its pointing to the same folder.

Could it be because the development server is running on 32 bit and the production server is running on 64 bit?


texts: CONCAT used in INDEX causes ERROR: functions in index expression must be marked IMMUTABLE

texts:
I am facing the following error:

ERROR:  functions in index expression must be marked IMMUTABLE

When trying to create index like this:

CREATE INDEX full_phone_number ON orders_clientphone (concat(area_code, phone));

On the other hand, when using alternative syntax for concatenation:

CREATE INDEX full_phone_number ON orders_clientphone ((area_code || phone));

Postgres is pretty much ok with that.
Both columns are defined as character varying(256).


texts: Assign the result of current_setting('the_setting') to a variable in PL/pgSQL

texts:
I have a function where I need to get a configuration parameter and assign it to a variable that I will use later in the function. The problem is that when the configuration parameter is not recognized (because it does not exist yet) the function dies. I want to evaluate if the variable was able to be assigned and if not set a null value to it.

This is what I tried:

   DECLARE
      conf_param text;
      num integer;

   BEGIN
      SELECT current_setting('the_setting') INTO conf_param;
         -- here is where dies when the_setting is not recognized!
      IF FOUND THEN
         num := conf_param::integer;
      ELSE
         num := NULL;
      END IF;
      -- more stuff

I am not sure if I am using Found the way is needed.


texts: rename datafile when file does not exists

texts:
I am trying to clone our production database in test virtual machine. Our production database works with ASM. I would like to use plain file system in test database.

I created database, services, pfile etc. I gave production database control files to test database.  Right now I am trying to rename datafiles in test database. I get following error.

 SQL> startup mount;
  .....
 SQL> alter database rename file '+NEWDATA/XX/XX01.dbf' to      'M:\oracle\datafiles\XX\XX01.dbf'; alter database rename file
 '+NEWDATA/XX/XX01.dbf' to 'M:\oracle\datafiles\XX\XX01.dbf';
* ERROR at line 1: ORA-01511: error in renaming log/data files ORA-01141: error renaming data file 162 - new file
'M:\oracle\datafiles\XX\XX01.dbf' not found ORA-01110: data file 162:
'+NEWDATA/XX/XX01.dbf' ORA-27046: file size is not a multiple of
logical block size OSD-04012: file size mismatch O/S-Error: (OS 4) The
system cannot open the file.

My backup is normal rman backup pieces not backup as copy. I am trying to rename nonexistent files. After this rename process , I will try to restore database from rman backup. But this backup is not piece by piece copy.


texts: Denormalized table designs in normalized database?

texts:
There was a discussion between the developers and DBA's last week on set of tables that the developers were creating with more than 400 columns in our OLTP environment with combinations of VARCHARs, Datetime and float. When asked for a reason for such denormalized table, we were told thats how the record record sets were provided by vendors and hence the mapping table has to be designed in that approach. In addition, table will be used for cross joining across other normalized tables and table will potentially grow to larger size. Currently there are few tables (with 375 columns) with similar design and average of 3 million rows or more on the same database. These existing denormalized tables are partitioned by date. No performance issues on these existing tables as they are not being heavily used yet.

Questions:

Other than saying it is a bad design, do you see any obvious issues with such design that can convince the developers to reconsider their designs strategy in a normalized environment. Of course Normalized versus Denormalized have their own pros and cons.
In your experience, have you seen any common real time performance issues with such columnar tables such as locking, takes longer for DML etc? The developers even mentioned about considering MongoDB as their alternative if SQL Server shows any performance issues.
Do you have some high level performance issues that could possible occur if such tables are designed more often in OLTP. Please note that the company is flexible with adding CPU or memory if needed for any IO or CPU issues.

I know in SQL 2012,there is columnstore index but haven't explored yet.


texts: MongoDB explain winning plan shows "EOF"

texts:
I've been working on optimizing my queries on some 2 million documents in MongoDB and I tried to use explain on aggregate functions, but it would display

"winningPlan" : {
      "stage" : "EOF"
    },

Prior to this, the function would show the winning plan with the stages such as "Fetch", but after I tried a few different syntax in writing the aggregate command now it is showing "EOF". I tried to simplify my command to a find().explain() function but it is still the same. Anyone has any ideas?

On a second note, has anyone figured out how to explain("executionStats") on an aggregate query. I see that the function has been implemented here, but when I run it I get "EOF" as well as a basic explain() results. Is it because my MongoDB is not updated to 3.5.5? Is this functionality supported for versions below 3.5.5? Many thanks in advance.


texts:
I am just starting to learn about memory usage on SQL Server.  When using the query in the answer to the question SQL Server 2008 R2 "Ghost Memory"?, I discovered that a single database is taking up the lion's share of space in the buffer pool.  Looking further, using sys.allocation_units and sys.indexes, I confirmed this is likely caused by the heavy use of indexes in the database.  Most indexes are clustered.

Another database developer believes we are having memory issues on the server - that queries are starting to run long because there is no available memory.

My question here is - does the use of these indexes, and their existence in the buffer pool, take away memory available for other processes?


texts:
I am trying to set up a MongoDB replica set on Digital Ocean.

I edited /etc/mongo.conf and specified replica set id:

replSet=rs1

and did not change anything else.

I did rs.initiate() on the first instance. Here is my config:

{
"_id" : "rs1",
"version" : 2,
"members" : [
    {
        "_id" : 1,
        "host" : "localhost:27017",
        "arbiterOnly" : false,
        "buildIndexes" : true,
        "hidden" : false,
        "priority" : 1,
        "tags" : {

        },
        "slaveDelay" : 0,
        "votes" : 1
    }
],
"settings" : {
    "chainingAllowed" : true,
    "heartbeatTimeoutSecs" : 10,
    "getLastErrorModes" : {

    },
    "getLastErrorDefaults" : {
        "w" : 1,
        "wtimeout" : 0
    }
}

}

Now, I want to add two more instances, but I see the error:

{
"ok" : 0,
"errmsg" : "Either all host names in a replica set configuration must be localhost references, or none must be; found 1 out of 2",
"code" : 103

}

How can I change the host to the server's ip address? If I try reconfig I get:

{
"ok" : 0,
"errmsg" : "No host described in new configuration 3 for replica set rs1 maps to this node",
"code" : 103

}

/etc/hostname contents:

mongodb-1

/etc/hosts contents:

# Your system has configured 'manage_etc_hosts' as True.
# As a result, if you wish for changes to this file to persist
# then you will need to either
# a.) make changes to the master file in /etc/cloud/templates/hosts.tmpl
# b.) change or remove the value of 'manage_etc_hosts' in
#     /etc/cloud/cloud.cfg or cloud-config from user-data
127.0.1.1 mongodb-1 mongodb-1
127.0.0.1 localhost

# The following lines are desirable for IPv6 capable hosts
::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
ff02::3 ip6-allhosts


texts: ORA-12899: importing a .dmp file + character set conversion

texts:
I'm working on a django project on Ubuntu 10.04 with Oracle Database Server , so I've installed: Oracle Database 10g xe universal Rel.10.2.0.1.0 cx_Oracle-5.0.4-10g-unicode-py26-1.x86_64

When I import a .dmp file which is generated by oracle 10gr2 enterprise edition (on Windows XP) I get errors as listed below(it happens with data types varchar2), probably connected to the characterset conversion (my db contains greek) and my data don't sync completely.

  Username: Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production Export file created by EXPORT:V10.02.01 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) export client uses EL8MSWIN1253 character set (possible charset conversion) . . importing table "xxx" IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "xxx"."xxx"."xxx" (actual: 41, maximum: 40) and so on

My oracle express server is running with these settings:

NLS_CHARACTERSET AL32UTF8
NLS_DATE_LANGUAGE AMERICAN
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE
NLS_TERRITORY AMERICA

And the database server which generated the .dmp file is running with:

NLS_CHARACTERSET EL8MSWIN1253
NLS_NCHAR_CHARACTERSET AL16UTF16

Does anyone have a hint how I could solve this problem?


texts: Is there a difference between an xtrabackup taken with and without --safe-slave-backup?

texts:
Currently I'm studying Percona's xtrabackup. In the manual in the paragraph "Taking Backups in Replication Environments" it says that using the --safe-slave-backup
option is always recommended and I understand the reasons behind it.

I'm just wondering now, if there is actually a difference in the result when I do not use this option. After applying the logs on a backup, I don't see why there should be a difference between a backup taken with or without this option.

I'm asking, because in our production environment we do not use this option. The backup runs at midnight, but it failed this night. The backup is taken on a slave and I'd have a bad feeling about stopping the SQL thread now to take the backup.


texts: Alternative to ORDER BY GROUP BY to get first matches per ForeignKey

texts:
Since there is so much talk and articles about this online. I simply don't seem to have enough SQL knowledge to solve this seemingly simple problem.

I have a table with layered data for a Price per ProductModelID/ProductVariantID (BundledProductID isn't important in this situation). I want to get the Highest and Lowest Price (in the figure below the lowest) per ProductModelID.

Obviously knowing that GROUP BY will just grab first hits (likely based on the BTREE?) so it would get ID's 1,4 and 6 instead of the blue marked ones I want.

Is there any sensible way of achieving this and still profiting from indexes (e.a. not suffering greaty on performance on temp tables and such)?

I'm running 10.2.13-MariaDB Homebrew.

The query:

-- SELECT * FROM (
    SELECT * FROM DomainProductPrice
    WHERE DomainID = 1 #AND ProductVariantID IS NULL
    ORDER BY ProductModelID, Price
-- ) a GROUP BY ProductModelID

Table creation:

CREATE TABLE `DomainProductPrice` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`DomainID` int(11) DEFAULT NULL,
`BundledProductID` int(11) DEFAULT NULL,
`ProductModelID` int(11) DEFAULT NULL,
`ProductVariantID` int(11) DEFAULT NULL,
`PriceGroupID` int(11) DEFAULT NULL,
`TaxGroupID` int(11) DEFAULT NULL,
`Currency` int(11) DEFAULT NULL,
`Price` double NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Table test data:

INSERT INTO `DomainProductPrice` (`id`, `DomainID`, `BundledProductID`, `ProductModelID`, `ProductVariantID`, `PriceGroupID`, `TaxGroupID`, `Currency`, `Price`)
VALUES
    (1, 1, NULL, 1, NULL, 1, 1, 1, 24),
    (2, 1, NULL, 1, 3, 1, 1, 1, 23.5),
    (3, 1, NULL, 1, 4, 1, 1, 1, 23.75),
    (4, 1, NULL, 2, NULL, 1, 1, 1, 68),
    (5, 1, NULL, 2, 5, 1, 1, 1, 66.25),
    (6, 1, NULL, 3, 9, 1, 1, 1, 101),
    (7, 1, NULL, 3, 10, 1, 1, 1, 99.5),
    (8, 1, NULL, 1, 2, 1, 1, 1, 25);

Update 1:

This would be a solution, but it's probably super bad with tons/milions of entries?

SELECT
    p.ProductModelID, p.PriceGroupID, TaxGroupID, Currency,
    (SELECT Price FROM DomainProductPrice WHERE DomainID = 1 AND ProductModelID = p.ProductModelID ORDER BY Price DESC LIMIT 1) AS Highest,
    (SELECT Price FROM DomainProductPrice WHERE DomainID = 1 AND ProductModelID = p.ProductModelID ORDER BY Price LIMIT 1) AS Lowest
FROM DomainProductPrice p
WHERE p.DomainID = 1
GROUP BY ProductModelID


texts: Group not inheriting access privileges for all tables

texts:
I can't figure out why tables created by one user can be accessed by a read-only user, but not tables created by another user.

I have a viewers read-only group with this access:

-- Grant access to existing tables
GRANT USAGE ON SCHEMA public TO viewers;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO viewers;

-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO viewers;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO viewers;

I have two superusers in my database and one read-only user, all members of the group:

 Role name | Attributes | Member of
-----------+------------+-----------
 a         | Superuser  | {viewers}
 b         | Superuser  | {viewers}
 readonly  |            | {viewers}

When I create a table as user a, the readonly user can access it.
But when I create the table as user b, the readonly user gets  permission denied for relation.

When I check the grants, when the table is created by user a, the viewers group gets granted select automatically:

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='testa'
;

 grantee | privilege_type
---------+----------------
 viewers | SELECT
 a       | INSERT
 a       | SELECT
 a       | UPDATE
 a       | ...

But if the table is created by b, the the viewers group does not have that select privilege.

I'm sure it's simple, but I am not sure what commands will help me troubleshoot the permissions error. Where can I check to see the existing privileges and get the root of this?


texts: localhost:port_number/em (what is my port number)?

texts:
I am new to SQL databases. I have started learning SQL databases from SQLZOO, where they told me to use Oracle. I have installed Oracle Database 11g on Windows 10 with the help of this video.
I have created a table using SQL*Plus, so I am assuming that I have installed Oracle correctly.

Now when I am trying to go to http://localhost:1158/em from my Google Chrome, it is saying:

  The site cannot be reached

I have searched and found that this 1158 is supposed to be my port number. I have tried many ways to find this port number for the last 3 days to no avail.

Now what should I do?
Have I missed installing something?


texts: Oracle client in Windows 7: how to change looking path for sqlnet.ora

texts:
For some security reasons I'm not able to amend the file sqlnet.ora, must meet security policies applied. Is it possible to change the search path of this file to some other drive/folder?

Thank you!


texts: MongoDB document found on replica set but not in mongos

texts:
I have a sharded cluster: two shards - each a replica set with 3 nodes, and a config replica set (with 3 nodes).

I sharded my cluster by two collections in the same database:
  - players.user_id - it's a hashed shard key
  - users._id - an unhashed shard key

I wanted to shard users._id with a hashed key. So I followed these steps https://docs.mongodb.org/manual/faq/sharding/#can-i-change-the-shard-key-after-sharding-a-collection.

Notes:
1. Since the users collection was already sharded, each backup and restore was done on the same replica set; the backup & restore was done with data dumps.
2. After I dropped my users collection I created the collection and added the _id: hashed index on it and then configured the sharding with _id: hashed.
3. After the sharding I restored the data and ran my application, which created other indexes, a _id: 1 index and other indexes - some of them being sparse.

After this I have a lot of users (like half of them) which I can find querying the mongod on any of my replica set nodes (from where I backed them up and restored), but not querying the mongos.

Also, my counts are wrong:

mongos> db.users.find({}).length()
355
mongos> db.users.find({}).count()
664

repl-set-1:SECONDARY> db.users.find({}).length()
36
repl-set-2:SECONDARY> db.users.find({}).length()
628

I have balacing enabled, all of my replica sets nodes are in sync within their replica sets, each server can reach the others, I don't have timeouts or other visible configuration problems.

mongos> sh.status()
--- Sharding Status ---
  sharding version: {
    "_id" : 1,
    "minCompatibleVersion" : 5,
    "currentVersion" : 6,
    "clusterId" : ObjectId("...")
}
  shards:
    {  "_id" : "repl-set-1",  "host" : "repl-set-1/repl-set-1-1:27017,repl-set-1-2:27017,repl-set-1-3:27017" }
    {  "_id" : "repl-set-2",  "host" : "repl-set-2/repl-set-2-1:27017,repl-set-2-2:27017,repl-set-2-3:27017" }
  active mongoses:
    "3.2.1" : 3
  balancer:
    Currently enabled:  yes
    Currently running:  no
    Failed balancer rounds in last 5 attempts:  0
    Migration Results for the last 24 hours:
        No recent migrations
  databases:
    {  "_id" : "mydb",  "primary" : "repl-set-2",  "partitioned" : true }
        mydb.players
            shard key: { "user" : "hashed" }
            unique: false
            balancing: true
            chunks:
                repl-set-1  2
                repl-set-2  2
            { "user" : { "$minKey" : 1 } } -->> { "user" : NumberLong("-4611686018427387902") } on : repl-set-1 Timestamp(2, 2)
            { "user" : NumberLong("-4611686018427387902") } -->> { "user" : NumberLong(0) } on : repl-set-1 Timestamp(2, 3)
            { "user" : NumberLong(0) } -->> { "user" : NumberLong("4611686018427387902") } on : repl-set-2 Timestamp(2, 4)
            { "user" : NumberLong("4611686018427387902") } -->> { "user" : { "$maxKey" : 1 } } on : repl-set-2 Timestamp(2, 5)
        mydb.users
            shard key: { "_id" : "hashed" }
            unique: false
            balancing: true
            chunks:
      repl-set-1    2
      repl-set-2    2
            { "_id" : { "$minKey" : 1 } } -->> { "_id" : NumberLong("-4611686018427387902") } on : repl-set-1 Timestamp(2, 2)
            { "_id" : NumberLong("-4611686018427387902") } -->> { "_id" : NumberLong(0) } on : repl-set-1 Timestamp(2, 3)
            { "_id" : NumberLong(0) } -->> { "_id" : NumberLong("4611686018427387902") } on : repl-set-2 Timestamp(2, 4)
            { "_id" : NumberLong("4611686018427387902") } -->> { "_id" : { "$maxKey" : 1 } } on : repl-set-2 Timestamp(2, 5)

My questions are:
  1. How can I debug this? I tried a lot of things...
  2. What may cause this? The way that I backed up and restored data, the presence of sparse indexes?
  3. How can I fix this, if I can?


texts:
I have a database schema exported into XML format.

My goal is to create a DDL statement from the XSD files that I can use to import the data into a different platform without manually having to parse it myself.

There is very little information on XSD files in general and it seems that only Oracle have any support at all for them.


texts: (SQL Server /SSIS) Managing IS Catalog permissions on a shared server?

texts:
I'm hoping for some ideas on how you and your team manage this. I'm supporting a shared SQL Server that will have several different departments deploying their SSIS packages via the IS Catalog. I would like users to be able to have some level of access to their packages, without that colliding with/seeing the packages from other groups. I envision users being able to:

Go to 'Integration Services Catalogs' -> SSISDB -> And have read-only access to the folders containing the projects and environments they have deployed. Possibly full access to deploy new ISPAC to that folder, but only for folders they are granted permissions to.
See the IS catalog execution reports for only their SSIS packages.
Query the SSISDB database for only information (executions, package names, parameters) related to their packages.

How do you do this in a shared environment, where more granular permissions are needed?


texts: SQL Server 2016 DB is SUSPECT - cannot do anything with it!

texts:
User DB is in SUSPECT state.

sp_who2 shows that is isn't being used in any SPID.

I cannot ALTER to SINGLE_USER.  I have a FULL+DIFF backup combo that I restore from, but without exclusive access, I cannot restore.

ALTER DATABASE failed because a lock could not be placed on database 'MyDB'. Try again later.

Tried to DROP, having closed connections - no joy.

Tried RESTRICTED_USER just in case - no joy.

Am I missing something really obvious, or do I need to take more drastic action?  (Such as restarting SQL with everyone disconnected from every SSMS on the planet... (developers on multiple continents))

Any assistance is hoped for...

Thanks!

Addendum

I should add that the transaction log filled up the disk.

All attempts to backup the existing TLog fail due to SUSPECT presence.


texts:
I have three tables like below.

user        company     user_company
--------    -------     ------------------
id(pk)      id(pk)      user_id(pk, fk)
                        company_id(pk, fk)

My problem is: Each user and company, must have a unique customer number. And I want to connect these user and company tables to single table.

So, should I create junction tables for user and company?

customer            user_customer           company_customer
---------------     ------------------      ----------------
customer_no(pk)     user_id(pk, fk)         company_id(pk, fk)
customer_type       customer_no(pk, fk)     customer_no(pk, fk)

Or should I just add id column to customer table?

customer
---------------
customer_no(pk)
user_id(fk)
company_id(fk)

Which one is correct? Or, is there any other way to do this?


texts:
Please Consider this scenario:

I have a table in my database. I want move this data in my OLAP database using SSIS.I can move all record from my table to OLAP database.The problem is I don't know how I can apply changes in OLAP environment.For example if just 100 record of my table were changed how I can apply this changes NOT copy all records from scratch.

How I can Merge this two tables?

thanks


texts: How to write validation trigger which works with all isolation levels

texts:
Background

I need to write validation trigger for a table. Due to nature of validation I can not use builtin integrity checks. For purpose of this question let's say that validation counts number of rows with specific properties and number of this rows has to be less than 5. In fact my conditions are more complicated (there is a relation between two columns). Please consider validation as blackbox with only one property, it uses whole test table, no data from outside.

I don't want limit usage of database isolation levels. SERIALIZABLE works by design but other two should be also possible to use.

The main idea is, that usage of the trigger should be as easy as usage of other constraints.

Ideas

raise error when repeatable read mode is used (can be detected by current_setting('transaction_isolation'). This is the least preferred solution but I least it ensures data integrity
after table lock check if table is different from the current visible snapshot, then raise serialization failure - I have no idea how do it
Use another transaction inside trigger (after table is locked) to see if data are unchanged or not autonomous transactions

Trigger example - my current best solution

SERIALIZABLE seems working by design - no locking is required
READ COMMITTED I was able to fix this mode by using table lock
REPEATABLE READ No idea how to fix this mode

Code

CREATE OR REPLACE FUNCTION verify() RETURNS trigger
AS $$
DECLARE
    count integer;
BEGIN
    LOCK TABLE test IN EXCLUSIVE MODE; --fixes races in READ COMMITTED mode
    count := (SELECT count(*) FROM test where val = NEW.val);

    IF count >= 5 THEN -- this is simplified for example, validation real validation uses more columns
        RAISE EXCEPTION '% Already present', NEW.val USING ERRCODE = 'integrity_constraint_violation';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

registration

CREATE TRIGGER validate BEFORE INSERT OR UPDATE
 ON test FOR EACH ROW EXECUTE PROCEDURE verify();


texts:
I'm thinking about using triggers as a logging mechanism in MySQL (v. 5.1) and therefore I'd like my trigger to get the query string in order to store it in another tabe.

I couldn't find anything like that in the MySQL documentation so I expect the answer to be no – but I still hope that I've overlooked something.

PS: this question is related to IcarusNM's answer to my question "Log MySQL DB changing queries and users"


texts: Challenge with modelling a basic membership table

texts:
I am facing some challenges modeling a table for mapping users and their chatroom. I found two ways to get around the specific challenge I am facing, but with performance concerns. I have added a simplified version of actual scenario below-

Table: membership

Columns:

user_id         int
user_name       varchar
user_role       int
chatroom_id     int
chatroom_name   varchar

constraints:

user_id                     unique: an user can only be in one chatroom
user_name+chatroom_id       composite unique: one chatroom can have only one user with same name

When an user leaves, or kicked from a chatroom, I simply delete the row from membership.

Problem: I want to store the membership record for historical reasons, either on same table or a separate one.

Approach 1:
Keep a column 'status'. values: active, left, kicked
Problem with approach 1: Cannot maintain either of the unique constraints (when the same user joins that same or some other chatroom)

-

Approach 2:
Delete the row and use a trigger to arcive the record to different table
Problem with approach 2: Cannot include the information as to why the record was deleted, as trigger cannot determine that, nor there is any way to pass that info to trigger

-

Approach 3:

INSERT INTO archive (..., reason) VALUES(SELECT from membership where ..)
DELETE FROM membership

within a transaction, no trigger
Problem with approach 3: Works. But performance concerns- requires three queries, insert and delete performance hits for index, requires separate table.

-

Approach 4:
using two new columns- 'status' and 'uuid'.
uuid will be null when status=active, and when status is updated to any other value, uuid will be populated with a GUID.
Each of two constraints will be changed to be composites including the uuid column:

user_id + state + uuid
user_name + chatroom_id + state + uuid

Problem with approach 4: Works. But performance concerns- too many composite unique indexes. Maybe int instead of varchar (GUID) for uuid would improve performance a bit, but that would require further tricks.

Should I normalize the table further? Or any basic design flaw or lack of database design knowledge that led me to this complexity?

If not, which one is the better approach for this case?


texts: Cannot connect to SQL Server database regardless of connection string

texts:
I'm having a tough time to connect to a local SQL Server instance within a C# app.  I can connect to external databases on other servers, but I need to be able to use my own local database.

The exception I get is:

  {"Login failed for user 'ASTROAdmin'."}

There isn't really any more info within the exception, so I don't know if the account doesn't exist, if the password is bad, if the database doesn't exist, etc.  The connection string I'm using is:

  connectionString="Server=MIKE-LAPTOP;Initial
  Catalog=ASTRO_PROD;Integrated Security=False;User
  ID=ASTROAdmin;Password=password;Persist Security Info=True;Type System
  Version=SQL Server 2008;Application Name=OpsMgr;"

I've also ran the following on the server:

ALTER LOGIN [ASTROAdmin] WITH PASSWORD=N'password'

So the password should be fine.

I've been trying different values (such as . and localhost) for Server and also using Data Source= instead, but I get the same exception no matter what.  My theory is that there are multiple instances of SQL Server on this machine, like perhaps SQL Server Express.

Also, if I run the SQL Profiler tool, I don't see anything come up as it tries to connect.  No data what-so-ever.

What's the best way to continue to diagnose this problem?  Thanks!


texts: Import CSV to MySQL with long text fields containing line breaks

texts:
I'm trying to import a CSV file (';' separated) to a MySQL table.
Normaly, I use a SQL like:

LOAD DATA LOCAL INFILE 'local-path/file.csv'
INTO TABLE tmp_table
CHARACTER SET latin1
FIELDS TERMINATED BY ';'

It always works fine to me, but now I have a CSV with a text field including many '\n','\r', and stuff like that. When I make the import, apparently, MySQL interprets the line breaks as a new field, spoiling all import.

I'd like to know if exists some method to indicate to MySQL to ignore any other symbol except the ';'.

I'm using MySQL workbench to make the import. My CSV have 3 fields (locale,id,terms) and my table have columns locale - VARCHAR(5), id- VARCHAR(50) and terms - TEXT (here is the problem).

Here I found some similar problems, but apparently is not the same thing, I'm not looking for multiple delimiters, I just want for a way to ignore \n and \r from text fields when I'm importing the CSV.


texts: How to compare and aggregate data in a table

texts:
Is there a way to evaluate data in a table to derive if in a set of ProductIDs a PRODUCTID passed or failed?  Here is an example of my data:

DECLARE @example TABLE (ID INT NOT NULL, PRODUCTID INT, PASSED BIT, LEVEL INT, CREATEDATE DATETIME, PRIMARY KEY (ID))
INSERT INTO @example VALUES
 (  1, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  2, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  3, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  4, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  5, 101, 1, 1, '2016-01-10 07:35:02.123' )
,(  6, 102, 0, 1, '2016-01-10 07:35:02.123' )
,(  7, 102, 1, 2, '2016-01-10 07:35:02.123' )
,(  8, 103, 1, 1, '2016-01-10 10:35:02.123' )
,(  9, 103, 1, 2, '2016-01-10 09:35:02.123' )
,( 10, 103, 1, 2, '2016-01-10 08:35:02.123' )
,( 11, 103, 1, 3, '2016-01-10 07:35:02.123' )

Is it possible to structure a query that returns:

PRODUCTID    ISPASSED
100          0
101          0
102          0
103          1

The conditions are there needs to be 2 PASSED for 2 different LEVELS for a given PRODUCTID.

100 is a 0 because all 4 records are for LEVEL 1
101 is a 0 because there is only 1 PASS and for only 1 LEVEL
102 is a 0 because one record PASSED and the other failed
103 is a 1 because there are 2 PASSED records for different LEVELS.

Thanks so much!

EDIT:

I have come up with this query, but it comes up short because I am only able to qualify first part of the requirements:

WITH cte(PRODUCTID, ISPASSED) AS
(
  SELECT PRODUCTID, CASE WHEN SUM(CONVERT(INT, PASSED)) = 2 THEN 1 ELSE 0 END AS ISPASSED
  FROM (SELECT PRODUCTID, RowNumber = ROW_NUMBER() OVER(PARTITION BY PRODUCTID ORDER BY CREATEDATE DESC), LEVEL, PASSED
    FROM @example
    GROUP BY PRODUCTID, LEVEL, CREATEDATE, PASSED) as agg
  WHERE RowNumber <=2
  GROUP BY PRODUCTID
)
select * from cte


texts: Dynamic length obfuscation method in T-SQL

texts:
I would like to obfuscate (scramble) sensitive data from a SQL Server database, but in the way which will provide:

irreversibility (the plaintext can't be derived from the obfuscated data),
obfuscated data length needs to be the same as a length of data before obfuscation.
obfuscated value does not need to be unique for repeated obfuscations of the same input value. To be honest, I rather like getting the same value for the same input which can used (e.g. some matching data in different tables, probably useful in test cases).

Example:

Abc -> zyx (lenght: 3)
StackOverflow -> a65vr4doqjd (lenght: 11)

Usually I avoid "home made" algorithms, so are you aware of some MS builtin solution which could provide this kind of obfuscation?

I hope I expressed my problem clearly, otherwise let me know and I'll try to add as much info as needed.


texts: Unexpected Indexing performance (Everything's equal)

texts:
I wanted to see the effects of having indexes on calculated columns, so I created a table like so:

CREATE TABLE [Domain\UserName].[CompColIndexing](
    [a] [int] NOT NULL,
    [nonIndexedNonPersisted]  AS ([a]+(1)),
    [nonIndexedPersisted]  AS ([a]+(1)) PERSISTED,
    [IndexedNonPersisted]  AS ([a]+(1)),
    [IndexedPersisted]  AS ([a]+(1)) PERSISTED
) ON [DATA]

I've added 800,000 rows to this, with the value for a cycling through 0 to 9.

The following indexes were added:

CREATE NONCLUSTERED INDEX [IX_DJB_CompNonPersisted] ON [Domain\UserName].[CompColIndexing]
(
    [IndexedNonPersisted] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [DATA]
GO

CREATE NONCLUSTERED INDEX [IX_DJB_CompPersisted] ON [Domain\UserName].[CompColIndexing]
(
    [IndexedPersisted] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [DATA]
GO

Than I ran some ORDER BY clauses to see what performance differences I would get, plannign afterwards to see how changing values of a would affect things.

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY a

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY nonIndexedNonPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY IndexedNonPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY nonIndexedPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY IndexedPersisted

Unexpectedly though, I find that I get exactly the same result for each of the queries:

I was at least expecting the SORT operation on the first query to be slower as that one is unindexed.

What's happening here?

The cardinality is low on purpose, in reality, I actually need to sort on three different values.

I'm using Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)

Actual execution plans available at:  https://www.brentozar.com/pastetheplan/?id=S10MTuxGg


texts:
We currently have an SQL Job that runs an SSIS package.  The output of the package is required for a report.  As a result, the application that can create the report on demand first tries to run the job that executes the package.  Because this is run by various people at various times, the previous way to handle this was to give SQLAgentOperatorRole to whichever person was in charge of kicking off the report.  I really would rather not be handing out this role (and I was not the one responsible for setting the precedent), for obvious reasons.

From what I know, you cannot give job level permissions to any group of people.  I have seen things about proxies, but not sure that would help here.  So, how do I grant X users access to:

check job status (via sp_help_job, at the moment)
Execute this job, and only this job

We are running SQL2008R2.


texts: How can I synchronize data between two RDS MySQL databases

texts:
I have 2 Amazon RDS MySQL instances, one is MySql v5.5 and the new one is 5.6.
I want to upgrade the 5.5 to the 5.6 but you cannot do upgrades at this time, so we are trying to migrate the data. I did a dump of the 5.5 and have imported the dump into the 5.6 but since I need to keep our down time to a minimum, I decided to try to do a data synchronization between the two.

What are my options for this?

I have a few tables that are huge! (1-8gb)
Tables are all innodb
Dump download took 3 hours
Dump Upload took 4+ hours

I need to try to stay under 5 hours total!


texts:
I would like some input on the best way to design this database relationship. We have a project entity, which has two areas as related entities. In the UI the main entity is the project, and then we have two tabs - one for each area. Both areas are of the same entity type, so I currently have this relationship modeled as a one-to-many relationship between project and area. We don't think we will ever need more than those two areas per project, and an area has a flag that signifies which type of area it is (the two areas have different types).

There is something bugging me about this design. When I need to get one of the areas for a project, I have to get the list of areas related to the project and filter on the flag. There will only ever be one area per type per project.

I have considered if I could reverse the relation, so the project references the two areas via two one-to-one relationships, but since both areas are of the same entity type I am not sure how to do that (as they cannot both have the same shared primary key as the project).

Can someone come up with a better design? - or do you think the current one-to-many is good enough?

We are using MySQL 8.
Extra bonus if the solution would work well with Entity Framework Core.

Thanks a lot!


texts: Virtual SQL Server not consuming enough CPU

texts:
We have a Win2008 server 32 bit running as a virtual machine under ESX server. It has 6 CPU cores of 2Gz each and 4GB ram. It's running SQL Server 2008 R2 only.

Problem: the server is heavily loaded and responds slowly. From windows taskmanager's point of view, it really looks overloaded, CPU wise. However, our external "cloud manager" says it's only using 2.5GHz worth of CPU-cycles in the cluster. I/O times looks "good".

We have already tried to set the SQL Servers number of working threads from 0 (auto) to 256, to no effect.

How to tune the VM host, guest or SQL Server to use all of it's aloted resources? Does it sound possible at all?


texts: Is following way is right Database model for following problem?

texts:
I am new to the database design so I have to check my data model is correct way to handle the problem or not.

Problem Statement:  I am designing the database for student portal which have following user roles

Administrator
School(Principal)
Teacher
Student

The functionalities is for phase I is just maintain all users data and provide the login system.

I am thinking about the database table are

-------------   -------------   -----------
Schools            Teachers       Students
-------------   -------------   -----------
id                id              id
name              name            name
other info        other info      other info

And I am decided to create the another table which store the login creadintilas and role with id as below

Users Table

--------------------------------------------
username    password      role      user_id
--------------------------------------------

but I am not sure about how to applying the PK/FK relations on the table.
I want create the same login system for all system because all the users shares some features i.e. teachers,student,schools can update the student info.
Questions

Is this right way of thinking?
Can i do it in another better way?


texts: Returning columns based on various conditions in Group By

texts:
I would like to do a 'group by' on the following example table

Input:

Output:

[Low] = Min of [Low] column
[High] = Max of [High] column
[Symbol]
[Epoch]
[CuVol]
[Dt10Min])
[Close]
[Open]

Columns 3-7 have the values corresponding to highest Epoch, which is 129 in this case and columns 8 will be the value of [Open] at lowest Epoch of the group [Dt10Min],127 in this case.

I am very new to SQL can you please suggest.


texts:
I have looked at previous questions dealing with New table or new column and When should I use a new table? and a few others. I'm a little confused, not sure if my problem has a different need/solution; so here goes:
I have a table for users with a userid, name, email, etc.  This is used for both employees and for customers (at around 800 entries, it is split nearly 50-50).
I have a need to incorporate about 4-6 more columns for just the employees; such as manager, citizenship, office, etc.  These would only be used for the employees and not for the customers.
My question is: do I create another table for the employees with those extra columns or do I add those columns to the 'users' table and default to NULL (for customers) in each one?
I thought this was a different situation than the other questions, which is why I posted it.  If not, please let me know.
Thank you


texts: Are operators also considered to be keys?

texts:
I'm taking a MongoDB course and one of the assignments asks to include quotes around keys.

So I did for a query like this {$push: {"key":value}}

But I was told that $push is also a key because it's on the left hand side. I always thought keys were like identifiers for values whereas operators are just actions, they don't have any value associated with.


texts: sp_send_dbmail stored procedure send with attachment

texts:
I've been tasked with sending a small monthly report to for one of my customers. The report has previously been run manually on the instance, the output copied to a spreadsheet and send to the customer as an attachment.

I'm looking for a more permanent solution, so I intend on using sp_send_dbmail stored procedure to run the query and send it as an attachment.

Everything works but the formatting of the message. Initially I tried to attach the output as a CSV file with a @query_result_seperator = ',' but the results were everywhere!

When I run the report normally, the output looks fine in SQL. But sending it as a CSV or just in the message body doesn't.

I think it might work better if I export the output as HTML and send that as an attachment/or as XML but I don't know how to do this.

Does anyone have any suggestions?

Thanks in advance!


texts: Adding a field to a table (but in another database)

texts:
I understand that this is a strange question but I'll try to explain it the best I can.

We have a database (I'll call it MachineDB) on our MSSQL Server 2016 that gets many datasets each day from a machine (we're talking ~50k datasets/day and this database has been running for 2 years now). A dataset consists of some fields specific to that machine. Whenever there is a an update from the machine manufacturer this will be a DacPack. I understand that this is basically a schema update which will delete some tables/fields and add others. I understand there are ways to compare those schemas manually and update them manually.

So, what I would like to do is to add one field of type int to one of the tables (the table has a primary key defined). We would like to use this field to track inventory usage. It would be easy to simply add this field and fill it with the required data.

BUT, whenever an update is rolled out, this field would get deleted because the update process does some kind of schema compare and deletes fields that are not used anymore.

So the actual question is: Is there a method I can add that field into another database which references the primary database or what would be the correct approach to this?


texts: Can you provide rights to a snapshot without providing rights to the database?

texts: Is it possible to give a user or group rights to a snapshot without giving them rights to the database if the database and snapshot both reside on the same instance?


texts: Query to find the "Number of days" a customer has an active card between a specified interval

texts:
I have a table with below structure :
create table test_table
(
  customer_num      NUMBER,
  card_number       char(2),
  card_issue_date   DATE,
  card_expire_date  DATE
)

Example data is:
customer_num    |  card_number   |  card_issue_date | card_expire_date
--------------------------------------------------------------------------
  1             |  x             |  1/1/2019        | 1/4/2020
  1             |  y             |  1/1/2020        | 1/4/2020
  1             |  z             |  1/11/2020       | 1/15/2020
  1             |  b             |  1/11/2020       | 1/20/2020
  1             |  w             |  1/12/2020       | 1/17/2020
  1             |  a             |  1/18/2020       | 2/12/2020

I have a calendar table with below structure :
create table dimdate
(
  datekey   DATE,
  <other columns related to date>
)

The above table has all dates .
For each custoemr_num  we need to find the total number of days he has an active card between '1/1/2020 ' and '1/31/2020'.
We need to take some important points into consideration here :
1)We need to pay attention that for some cards such as card number x and a the issue_date might be smaller than '1/1/2020 ' or bigger than '1/31/2020'.
2)We have to pay attention to overlaps. There might be overlap between issue_date and expire_date of different cards (b,w,z,a).
First we thought that we could calculate the number of active days for each card and then subtract the overlaps from the final result and
what we wrote so far is this :
select t.customer_num,
       t.card_number,
       t.card_issue_date,
       t.card_expire_date,
       lead(t.card_issue_date) over(partition by t.customer_num order by t.card_issue_date) next_card_issue_date,
       case
         when t.card_issue_date < to_date('1/1/2020', 'mm/dd/yyyy') and
              t.card_expire_date > to_date('1/31/2020', 'mm/dd/yyyy') then
          to_date('1/31/2020', 'mm/dd/yyyy') -
          to_date('1/1/2020', 'mm/dd/yyyy')

         when t.card_issue_date < to_date('1/1/2020', 'mm/dd/yyyy') then
          t.card_expire_date - to_date('1/1/2020', 'mm/dd/yyyy')

         when t.card_expire_date > to_date('1/31/2020', 'mm/dd/yyyy') then
          to_date('1/31/2020', 'mm/dd/yyyy') - t.card_issue_date

         else
          t.card_expire_date - t.card_issue_date

       end as card_active_days

  from test_table t
 where t.card_issue_date <= to_date('1/31/2020', 'mm/dd/yyyy')
   and t.card_expire_date >= to_date('1/1/2020', 'mm/dd/yyyy')

but we could not handle the overlaps. I was wondering if you could help me here .
Thanks in advance


texts: Database design for premium subscription service

texts:
I am trying to make web application that gives special features to users who pay for premium service and separate them from free accounts. Let's say there is a monthly fee for premium users who can pay for a couple of months and get the features. My question - if I want to keep records of that subscription what's the best way to go about it?

Here is what I had in mind

User_Table( UserId, isPremium, 'otherUserStuff')

Subscribers_Table( UserId, startDate, endDate, planType)

I think there is too much work with my way since the app has to go through the list every day and check if a user is premium and update the flag (isPremium) and I want to keep record of user subscription history for discount down the line.

Example: User pays for premium every other month that's 6 records for a year which the app has to go through to find if he/she is viable for premium features.Ideally, it should be checking the latest record because the other 5 records are not relevant.


texts:
I have question about using SSRS 2016 on SQL server 2014

First of all, I don't have much experience with Reporting services.

Company use SQL server 2014 and they have SQL database on it (DB is around 100GB). I could install SSRS 2014 on this server (because they have licensed SQL 2014), but I want to use SSRS 2016, because SSRS 2016 has many new features (web portal, mobile reports, custom design, KPI,..).

My idea is to install SQL 2016 Express version (It's free - 10GB) to install SSRS 2016 on it, only to store this two meta ReportServer DB and then connect to this production SQL 2014 DB to create queries on it.

Is this acceptable solution? Are there any other possibilities or they must buy SQL 2016 license to upgrade their SQL?

Thank you and best regards


texts:
Is it possible to separate out data files and log files of MySQL from one drive to another?

For eg:

Data (actual database data and system tablespace ibdata) in one partition, say /data and log files (ibdata0 and ibdata1 only) in one partition, say /redo?

Is it possible? I know the binary logs can be moved to a different partition but I have the doubt for above scenario.

I'm using Percona Server 5.6 on linux.

Edit:

Just checked out, If I set innodb_log_group_home_dir to a different location, would it work?


texts: Is Oracle DB immune to the InnoDB deadlocks found in MySQL?

texts:
It seems odd to me that MySQL would not handle this internally: "Deadlock found when trying to get lock; try restarting transaction"

Does Oracle DB work around this issue? After all, Oracle owns InnoDB.


texts: MySQL Master-Master Crash-Safe Replication: Slow UPDATE times

texts:
Environment

2 MySQL version 5.6.26 Dedicated MySQL Servers connected on a LAN.

Servers Have 16 Cores with 32 GB RAM each and are really fast.

Master-Master Crash Safe Replication is setup on both servers and is working fine. Although both are masters but application ensures that data is written only to 1 server unless it goes down and then a switch is to be made to the secondary master.

All the safety checks are in place to avoid auto increment conflicts.

Issue

UPDATE queries are extremely slow, as slow as a 20 year old server. If you insert 1 Million rows in a table that takes less time than if you UPDATE 2000 of them using a deterministic query.

Question

Where to look in order to find out what is causing this and what steps can be taken to improve this situation?

Sup Question
I noticed that we did not exclusively setup 1 database for replication, nor did we ignore any so it replications even the mysql schema. Since --relay-log-info-repository is set to TABLE can that be it? I mean wouldn't that mean even the relay log repository table is being replicated so that makes a lot of queries for 1 update?

I know that mysql schema is being replicated because every user on primary master was replicated on secondary master as well.


texts: POSTGRESQL error Could not create shared memory segment: Cannot allocate memory

texts:
Recently my client database has restarted and thrown the below error.

FATAL:  could not create shared memory segment: Cannot allocate memory
DETAIL:  Failed system call was shmget(key=5433001, size=5616156672, 03600).
HINT:  This error usually means that PostgreSQL's request for a shared memory segment
       exceeded available memory or swap space, or exceeded your kernel's SHMALL
       parameter. You can either reduce the request size or reconfigure the kernel with
       larger SHMALL. To reduce the request size (currently 5616156672 bytes), reduce
       PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or
       max_connections.
The PostgreSQL documentation contains more information about shared memory configuration.
LOG:  database system was interrupted; last known up at 2017-06-07 08:10:13 BST
FATAL:  the database system is starting up
FATAL:  the database system is starting up

How can I resolve this issue?


texts:
Using DB2 9.7, is it possible to produce a result set where the column-headers contain line-breaks? Something like:

select field_name as "Line1<br>Line2" from table_name

I want the column-header to look like this:

Line1
Line2

However, this is not for HTML. Instead I'm wanting it to look this way when I run the query (raw) in my sql editor (Dbeaver) and in the CSV file I export from Dbeaver (so that the columns are narrower -- to prevent horizontal scrolling while viewing the result-set).


texts: Cascading binary replication to logical postgres

texts: Im using Postgres 10, and I was wondering if its possible to cascade a logical replication from a binary slave replica? I am looking to have databases with the same data but indexed in a different way is this possible and makes sense?


texts:
We have a private SSRS database and have created a public atom url to import the data into Google Sheet. It works, but returns each row as a string. It doesn't return in table format.No columns.

URL is in format below:

=importfeed("SERVER:/FOLDERLOCATION/%252fJK%252fFOLDER%252fFILENAME&rc:ItemPath=Tablix1/atom?token=!xyz"

And return as "A B C D" instead of "A" "B" "C" "D".

What do we need to add in URL so data is returned in a table format?


texts:
I have the following case, which I can only describe as a chain of two many-to-many relationships:

There are three business entities (tables) and two junction tables:

Orders
Products
Features
Orders has a many-to-many relation with Products through a order_products table
Products has a many-to-many relation with Features through a feature_products table

Ideally, it means that the same Product may has different Features through one or more Orders.

For example, suppose we have Products of "Chocolate Cake" and "Cheese Cake", and within one Order we have:

2 Chocolate Cake: plain, small size and diet (so using 3 Features)
1 Chocolate Cake: decorated, medium size, Coconut added and candle (using 4 Features)
1 Chocolate Cake: plain, big size (2 Features)
3 Cheese Cake: cinnamon, banana (2 Features)

How could I store and maintain this relation, when the same Product may have one or more of its related Features within one Order?


texts: Determine minimal permissions required for a given SQL script or session

texts:
For security reasons I need to determine minimal permissions required for a given script or session without having to manually audit activity then work it out by hand.

I'm assuming this can be done by exhaustively tracing all the statements executed, which objects they refer to and which permissions are required to run that statement, but I don't really have the skills or time to build that.

Is there an out of the box solution to this?

I would like to be able to do this for SQL Server 2016 and SQL Azure.


texts: Invalid default value when trying to modify a different column

texts:
Problem: I'm trying to modify my column's datatype from a smaller VARCHAR to a larger one so that I can take in IPv6 addresses as well (my current column only takes a size large enough for IPv4 addresses)

Example:
ALTER TABLE accounts MODIFY COLUMN ip VARCHAR(45)

shoots back an error as

Invalid default value for 'birthday'

I'm a bit confused because while I do have a birthday column in my accounts table, why is an error being thrown here about it when I'm not touching it?

SHOW CREATE TABLE accounts


texts:
Well, I really have read so many articles, used different techniques and done some random things to do this.

My problem was that a have big tables (over one million rows) and some small (with few hundreds rows) - we are talking about 8 inner joins involved.

What reduces the execution time from over 2 minutes to 30 seconds was very strange for me and I was not able to find why this happens.

When I select columns from the tables I cast them. What I did was to cast the column to the most small possible type.

For example:

nvarchar(4000) to nvarchar(50 or 25)
bigint to int
int to tinyint

The result was over 1 minute and 30 second better execution time. Why this happen?

For example if my variable is string with length 10 the nvarchar(4000) and the nvarchar(50) will cast it to nvarchar(10) (or something close to that). So, why when I reduce the outcome type from cast the things go better?

Something more - I make a lot of test to check which function is better - cast or convert (do test for string to string, string to number,  number to string) but was not able to define which works better. Sometime the convert gives me few seconds better execution time, but not enough to make a conclusion. Has anyone do something like this and succeeded in?

Thank for your time in advance.


texts: I have a database that is over 50Gb. I'd like to take a backup of this database, but I don't want a full backup. I need to extract some of the data for a development test db. Is this possible?


texts: Return matches that have at least one message

texts:
I have two tables, the match table keeps track of matches and the message_log table keeps track of conversations for a match. Right now I'm getting all the matches by the following query

SELECT * FROM match order by id DESC

But I want to modify it so that it would only return matches that have at least one message in the message_log and at least one message has own_account set to false.

my match table

CREATE TABLE public.match
(
    id bigint NOT NULL DEFAULT nextval('match_id_seq'::regclass),
    match_id character varying(500) COLLATE pg_catalog."default",
    name character varying(30) COLLATE pg_catalog."default",
    birthdate timestamp with time zone,
    bio text COLLATE pg_catalog."default",
    my_id character varying(500) COLLATE pg_catalog."default",
    username character varying(500) COLLATE pg_catalog."default",
    insert_time timestamp with time zone DEFAULT now(),
    account_id bigint,
    CONSTRAINT match_pkey PRIMARY KEY (id),
    CONSTRAINT match_match_id_key UNIQUE (match_id),
    CONSTRAINT matcch_account_fk FOREIGN KEY (account_id)
        REFERENCES public.account (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
)

my message_log table

CREATE TABLE public.message_log
(
    id bigint NOT NULL DEFAULT nextval('message_log_id_seq'::regclass),
    message text COLLATE pg_catalog."default",
    from_id text COLLATE pg_catalog."default",
    to_id text COLLATE pg_catalog."default",
    match_id text COLLATE pg_catalog."default",
    unix_timestamp bigint,
    own_account boolean,
    reply_batch boolean DEFAULT false,
    insert_time timestamp with time zone DEFAULT now(),
    account_id bigint,
    match_pk bigint,
    CONSTRAINT message_log_pkey PRIMARY KEY (id),
    CONSTRAINT message_log_message_from_id_to_id_match_id_unix_timestamp_key UNIQUE (message, from_id, to_id, match_id, unix_timestamp),
    CONSTRAINT message_log_account_fk FOREIGN KEY (account_id)
        REFERENCES public.account (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT message_log_match_id_fk FOREIGN KEY (match_pk)
        REFERENCES public.match (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
)


texts:
I'm running a plpgsql script in Postgres 8.3 - I would like to pass arguments to this script via psql. I'm currently executing the script like:

psql -d database -u user -f update_file.sql

I came across This link which explains PGOPTIONS environment variable, but that doesn't work for "custom" arguments. i.e. I receive an error because the setting isn't listed in the postgres.conf file.

-bash-3.2$ export PGOPTIONS='--pretend=true'
-bash-3.2$ psql -d my_db -f update_database.sql
psql: FATAL:  unrecognized configuration parameter "pretend"

Any other ideas? Ideally I'd like to avoid environment variables...


texts: Need help understanding Postgres materialized views under the hood

texts:
postgres 9.6.20
Need some help getting an accurate mental model of whats happening with postgres materialized views.
Based on this from postgres docs (https://www.postgresql.org/docs/9.6/rules-materializedviews.html) "The information about a materialized view in the PostgreSQL system catalogs is exactly the same as it is for a table or view. So for the parser, a materialized view is a relation, just like a table or a view. When a materialized view is referenced in a query, the data is returned directly from the materialized view, like from a table; the rule is only used for populating the materialized view." (emphasis mine)
My assumptions based on the above (and what else I've been reading):

that making a mat view will then allow me to query against that new table
The query that generates that view is only run on the REFRESH call
That if the result of the query used to generate the mat view returns ~30000 rows, then that is how big the materialized view table will be

Something in my assumptions is wrong, and I'm trying to understand what.
I have a nice big (to me) query - some CTEs, some joins, some aggregating info into jsonb columns as my materialized view.
WITH
durations AS (SELECT DISTINCT ON(song) song, song_source_files.duration
    FROM song_source_files WHERE song_source_files.type = 'Full'
    ORDER BY song, updated_at DESC),
...<truncated more CTEs>
SELECT DISTINCT
    published_song.song,
    songs.bpm,
    ...<truncated>...
    COALESCE(durations.duration, 0) as duration,
    ...<truncated>
FROM
    published_song
    LEFT JOIN songs ON songs.id = published_song.song
    LEFT JOIN durations ON durations.song = published_song.song
    ...<truncated>...
    LEFT JOIN vocals ON vocals.song = published_song.song

CREATE UNIQUE INDEX IF NOT EXISTS "idx_{mv_name}_uuid" ON "{mv_name}" ("uuid");
...<truncated>

When I run the query, it takes about 30 seconds, and returns ~30000 rows.
In the mat view, when I query using a WHERE aimed at an indexed column, it's fast, as expected.
When I do EXPLAIN (ANALYZE, FORMAT JSON) SELECT count(*) FROM published_song_mv; this takes about 2.5 mins, and I see "Plan Rows": 12606570 and "Actual Rows": 29536.
So this is where I'm confused - my expectation is that the mat view only works with the Actual Rows. Not sure why the Plan Rows of 12 million even enters the equation (in the sense of, why it would apply on the final result set of ~30000 rows - i know i have some gnarly joins to generate the view)?
So I'm looking for help to correct the mental model I have in my head of whats happening under the hood.
Using Postgres 9.6 - can this be a factor - should I consider upgrading sooner rather than later?


texts: SQL Server 2012 Express Service Pack 2 - installation error 0x84B40001

texts:
I'm trying to install SQL Server 2012 Express Service Pack 2 through command line:

SQLEXPR_x86_ENU.exe /ACTION=Install /HIDECONSOLE  /FEATURES=SQL /INSTANCENAME=SQLEXPRESS /SECURITYMODE=SQL /SAPWD="SOMEPWD" /SQLSVCACCOUNT="NT AUTHORITY\SYSTEM" /SQLSYSADMINACCOUNTS="Builtin\Administrators" /AGTSVCACCOUNT="NT AUTHORITY\Network Service"

However I get this error message:

  The syntax of argument "/HIDECONSOLE" is incorrect;Either the
  delimiter '=' is missing or there is one or more space characters
  before the '='"  Error 0x84B40001

I removed the entire /HIDECONSOLE argument in my command, but I still get the same error.


texts:
Select  distinct  DATEDIFF(ww, GETDATE(), siparis_test) AS Hafta

The first day of week is sunday in the above function. How can I change it so that monday is the first day?

Thanks in advance.


texts: LOB_DATA, slow table scans, and some I/O questions

texts:
I have a rather big table with one of the columns being an XML data with an average size of XML entry being ~15 kilobytes. All other columns are regular ints, bigints, GUIDs etc. To have some concrete numbers, let's say the table has a million rows and is ~15 GB in size.

What I noticed is that this table is really slow to select data from if I want to select all the columns. When I do

SELECT TOP 1000 * FROM TABLE

it takes around 20-25 seconds to read the data from disk - even though I don't impose any ordering on the result.
I run the query with the cold cache (i.e. after DBCC DROPCLEANBUFFERS). Here's IO statistics results:

  Scan count 1, logical reads 364, physical reads 24, read-ahead reads
  7191, lob logical reads 7924, lob physical reads 1690, lob read-ahead
  reads 3968.

It grabs ~15 MB of data. Execution plan shows Clustered Index Scan as I'd expect.

There's no IO going on on the disk besides my queries; I've also checked that clustered index fragmentation is close to 0%. This is a consumer-grade SATA drive, however I'd still think SQL Server would be able to scan the table faster than ~100-150 MB/min.

Presence of XML field causes most of the table data to be located on LOB_DATA pages (in fact ~90% of table pages are LOB_DATA).

I guess my question is - am I correct in thinking that LOB_DATA pages can cause slow scans not only because of their size, but also because SQL Server can't scan the clustered index effectively when there's a lot of LOB_DATA pages in the table?

Even more broadly - is it considered reasonable to have such a table structure/data pattern? Recommendations for using Filestream usually state much bigger field sizes, so I don't really wanna go that route. I've not really found any good info about this particular scenario.

I've been thinking towards XML compression, but it needs to be done on the client or with SQLCLR and would require quite some work to implement in the system.

I tried the compression, and since XMLs are highly redundant, I can (in a c# app) compress XML from 20KB to ~2.5KB and store it in VARBINARY column, preventing usage of LOB data pages. This speeds SELECTs 20x times in my tests.


texts: OleDBConnection.Close() very slow on server

texts:
I am working on a few applications which connect to an MS Access database backend (.mdb) to read/insert/update records.

Everything is working fine, but I noticed that my db operations were quite slow. The backend is accessed by other users, but I still get the issue when querying a copy of the access file which no one else connects to.

I managed to narrow this down so that I can now see the offending code is the line

 connection.Close()

Called on an open OleDBConnection which has just executed some query, e.g:

var con = new OleDbConnection(connectionString);
con.Open();
var query = "SELECT * FROM subGRCReceived WHERE GRVNo=@grv";
var args = new DynamicParameters();
args.Add("@grv", grvNumber);
// Using Dapper
var pallets = (List<Pallet>)con.Query<Pallet>(query, args);
con.Close(); // This is often taking between 7-10 seconds

I can confirm that this is occurring when using using/con.Close()/con.Dispose(), and using or not using Dapper makes no difference.

I did notice that this only seems to happen with web based prjojects (ASP MVC or WCF soap service)  and not Console applications. The issue seems to be intermittent, but occurs frequently enough for it to be a pain for the user (especially when navigating to a page uses 2-3 db queries, as this can take as long as 20 seconds to load).

The problem does not lie with the code itself, as I am able to host the same application on my laptop on the same network as the server and the speed is perfect (~200ms per request). See the specs of the 2 machines below:

Laptop Details:

Processor : Intel Core i7-6700HQ CPU @ 2.60GHz
RAM       : 16.0 GB
OS        : Windows 10 x64

Server Details

Processor : Intel Xeon CPU E5-2603 v4 @ 1.70GHz (2 processors)
RAM       : 32.0 GB
OS        : Windows Server 2012 x64

Setup

32 bit MS Access 2016
2016 Microsoft ACE OLE Engine
64 it OS

What I have tried

Moved the database to same folder as application
Disabled antivirus (ESET)
Increased the MaxBufferSize key value from 0 in Access Connectivity Engine in the registry (does this need a restart?)
Measure the time it takes to run GC.Collect() before calling Close() to ensure it is not the garbage collector

Workarounds

Threading

I tried calling Close() from a new thread, which seemed to work after 1 request, but if I try accessing the application again I am getting unhandled win32 errors on the server (Even though I wrapped my thread and connection.Close() calls in try/catch. I suspect this might be failing because the thread might take 7 seconds to close the connection, but the IIS worker process gets terminated before that, so there may be some missing resources that Close() needs. It might be nice if I could get this working, but I understand that this i bad practise in MVC, and also does not actually solve the issue.

Persistent Connection

I could also just have 1 OleDBConnection and keep it open throughout the session. I did this with the WCF service (1 connection per request) and it works find, however I get the feeling that it wont work quite as well with ASP MVC, and after doing a bit of research it looks like this is not a good idea.

I have been struggling with this for a week now and its driving me crazy, does anyone have any advice at all?


texts: Restoring multiple snapshots with SQL Server 2008

texts:
I noticed I cannot have multiple snapshots of a SQL Server database and then restore from one of them (without I believe, deleting all but the one I want to restore).

Is there a way to keep multiple snapshots of the same database and be able to restore to any of them?

Or is the only way either buying some 3rd party software which does this, or
 taking full backups of each version of the database?


texts: What should be the fill-factor for a table where only fixed-width columns will be updated?

texts:
We have a table where we are planning to update timestamp fields only.

In the first import these columns will be nulls then we will update them as the computation finished on these rows.

The question is; should we set a fill-factor lower than 100 for this table or PostgreSQL will allocate a fixed size for these columns so lowering the fill-factor makes no sense?


texts:
I've tried to find this on Google but no love. Usually that means it's so basic everyone else knows it. But I would like an answer for my own peace of mind.

If I make a change to one server in an Always On situation - like say adding an index - do I have to make the same change to the other server? And if I do, is there a risk of the two servers not being in sync?

Any links to articles would be fine. Just want to be certain.

TIA