texts:
Is there a reason significant reason to recompile stored procedures?
Of course the data in a database changes over time and the resultset of some stored procedures could differ due to changed underlying data. But unless there are changes of table structures, the stored procedures should be good forever?
I'm talking about Sybase ASE 15.


texts:
I'm trying to create a query for this problem.

Suppose I have these two tables:

Acceptor table:

ID  TYPE        QTY
--  ----------  ------------------
1   AB+          1
2   O-           5
3   O+           1
4   O-           5
5   O-           5
6   AB-          1
7   AB+          1

Donor table:

ID  TYPE        QTY
--  ----------  ------------------
1   AB+          2
2   O-           5
3   O+           3
4   O-           5
5   O-           3
6   AB-          1
7   AB+          3

I need a query to select the types of blood, and the difference between the quantities in cases where the SUM of Acceptor.QTY is greater than the SUM of Donor.QTY.

So in this case, Donor's O- => 5 + 3 + 5 = 13 and the Acceptor's O- => 5 + 5 + 5 = 15.

The query would have to return

O-   2


texts: How do I get Brent Ozar's sp_BlitzIndex to run on Azure?

texts:
I downloaded the SQL Server First Aid Kit from Brent Ozar's website.  When I tried to run the sp_BlitzIndex script against my master database via Microsoft Sql Server Management Studio while logged in as the Azure database server level admin and I got the following error:

  Msg 262, Level 14, State 18, Procedure sp_BlitzIndex, Line 18 CREATE
  PROCEDURE permission denied in database 'master'.

I successfully created the procedure on the database instance I wanted to test.  When I executed the procedure I got an error stating:

  Msg 50000, Level 16, State 1, Line 1265 Invalid object name
  'mydatabase.sys.partitions'.

Next I tried to be clever and run the stored procedure code directly against the master database without creating the stored procedure and got the following error:

  Msg 50000, Level 15, State 1, Line 1267 Reference to database and/or
  server name in 'mydatabase.sys.indexes' is not supported in this
  version of SQL Server.

I don't feel confident enough to start toying with the inner workings of ~2700 lines of index heuristic logic.  Is there a quick, and easy way to get this stored procedure to work nicely on Azure SQL Database or should I look elsewhere for an index analyzing tool/stored procedure?


texts:
We have a very big Oracle db and want to archive whole of the oracle db to SQL Server. We are trying to weigh between SSIS v/s SSMA. Can the complete migration be achieved using SSMA? We do not have any schema/tables setup in sql. We need to migrate whole of the oracle db to sql.

Is there any cons using SSMA in this kind of migration?


texts: Why is my log file so massive? 22gb. I am running log backups

texts:
I can't seem to figure out the answer. I've seen multiple answers like this:
Why Does the Transaction Log Keep Growing or Run Out of Space?

and everyone talks about running back ups on your log file so it shrinks down. I am doing that, but it doesn't shrink anything! I also don't believe I am running any super long transactions.

Server: SQL Server 2008

Recovery Mode: Full

I have a maintenance plan to store 5 days worth of backups. Task 1 backups up the databases with Backup Type Full, Task 2 backs up Transaction logs. Verify backup integrity is checked on both tasks.

My DB's normal .ldf file is 22gb. When I run the above task, the .bak file is 435mb, but the .trn. file is 22gb, same as the ldf. And after successfully running the .ldf doesn't shrink at all, despite everything I've read telling me it should?

What is going on here and why doesn't the log file ever shrink?

I've also tried running this command as mentioned in another answer:

select name, log_reuse_wait_desc
from sys.databases

And it says LOG_BACKUP for the db with the huge log file.

Based on an answer below I am confusing allocated with used space. These are my stats for:

For reasons I have no clue why, the initial size was set to 22gb...


texts: Update statement errors, parenthesis, failed validation ORACLE

texts:
i am writing an update query and it is giving me errors, what i am trying to do is to take a value multiply it added to another value.

Original statement

The statement below works flawlessly,

UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL =
   NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0) +
   NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.OUT_FLIGHT_ID), 0) +
   NVL((SELECT AC.ACC_PRICEPN FROM ACCOMMODATION AC WHERE AC.ACC_ID = R.ACC_ID), 0);

Modified version not working
This is the modified version,  i want to multiply the first two select with a value in each field for in flight and outfight seats no.

UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL =
   NVL(((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0) * NVL(R.IN_FLIGHT_SEATS_NO,0)) +
   NVL(((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.OUT_FLIGHT_ID), 0)* NVL(R.OUT_FLIGHT_SEATS_NO,0)) +
   NVL((SELECT AC.ACC_PRICEPN FROM ACCOMMODATION AC WHERE AC.ACC_ID = R.ACC_ID), 0);


texts: How to give select privileges to database in postgreSQL

texts:
How can we give select access at database level in postgreSQL.

At present I had created new user with select access on all tables in a schema using below syntax.

grant select on all tables in schema public to readonly;

But again if any new table gets created, the user readonly is not able to access the newly created table. I need to again execute the same syntax for giving access.

Is there any generic way, where we can give select privilege at database level just like MySQL. So that if any new table is created, the new table will be accessed by user automatically.


texts: Number of times PostgreSQL database restarted

texts:
How to get how many times my PostgreSQL database restarted and how to get each restart time?

I do not want to get the details from the log file. Is there any other way to get those details?


texts: Why would I need to update stats manually?

texts:
I have often found that when I am having performance problems on my MSSQL database, I can resolve them by running sp_UpdateStats.  I learned this because that's always the first line of defense from tech support, right after, "Is your computer turned on?"

My understanding is that SQL Server is supposed to keep stats automatically; I shouldn't need to nanny it.  So I'd like to understand: what circumstances might lead to the stats falling out of date so badly that I need to update them manually?


texts: Changing primary key column type from varchar to integer

texts:
A while ago we migrated an old database to SQL, surprisingly we found out that some primary key values contain text instead of an integer. This is causing the output column type to be of varchar. We would like to convert this to an integer, because we want to use auto increment.

Our database consists of quite a few tables and many more rows, so we can't do this by hand. There are also foreign keys, which we have to update those as well.

We used SSIS to import the data from the old database, and already used some data conversion blocks to fix some typing problems, however I couldn't find any blocks/functionality to solve this problem. I also considered writing a t-sql script, but because of all the foreign keys, I hope there is a better way to do this.

So, what's the best way to go about this?


texts: Using Import Wizard with Access - No null values

texts:
I am trying to import a table from Access to SSMS using the Import Wizard but when I do the resulting table has a lot of empty strings instead of the NULL values that I am expecting.

How do I make sure that any null values in Access come through correctly as NULL values in SSMS?


texts: sqlcmd returning NUL instead of NULL for tinyint column

texts:
sqlcmd -E -d MyDatabase -Q "sp_helpsubscription" -s "," -o file.csv -W -u

Column 23 which is suppose to be a tinyint (subscriber_type) is output as NUL instead of NULL when the value is NULL.

I cannot figure out why it's doing this or a way to fix it.  When I run sp_helpsubcription in SSMS the column contains the expected full NULL text.

In order to replication this you need a replicated database with a publication in at least one subscription to it.  This particular issue is with a transactional publication.

It does this even if I output the results to the screen instead of to a file.

Edit: The reason I'm doing this is because I can't issue an INSERT #TEMP EXEC sp_helpsubcription because of a NESTED EXEC INSERT inside sp_helppublication.


texts: Invalid column name even after checking if it exists so it should skip the query part

texts:
I've got the following query:

    IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'NotificationTableLogRows' AND COLUMN_NAME = 'DestinationAddress') BEGIN
      UPDATE NotificationTableLogRows
      SET [HandlerID] = m.ID
      FROM [Unicare].dbo.MobileDevices m
      WHERE HandlerID is null
      AND m.CallNumber = NotificationTableLogRows.DestinationAddress AND NotificationTableLogRows.HandlerTypeID = 2
    END
print 'end'

But after running this, I still got the error:

Msg 207, Level 16, State 1, Line 6
Invalid column name 'DestinationAddress'.

It should however just print end

What am I doing wrong?

Edit:

Even with:

IF 1 = 0 BEGIN
    UPDATE NotificationTableLogRows
    SET [HandlerID] = m.ID
    FROM [Unicare].dbo.MobileDevices m
    WHERE HandlerID is null
    AND m.CallNumber = NotificationTableLogRows.DestinationAddress AND NotificationTableLogRows.HandlerTypeID = 2
END

It produces the same error, the problem seems to be in the way sql checks the query pre executing..


texts: copy tables from one database to another

texts:
I have two Databases on mysql. They are used for completely different things and contain different tables. The problem is I need them both on the same server that can only host 1 db (and I can't change that fact). So I thought I can just use the same db for the to projects, since they use different tables and not overlaps.

One would think there is a simple way to do so.

There are not a lot of tables, so I can copy it manually one by one, but it seems that there is also no simple command to copy a table including all of the table rule's and restrains. (Working on a mac, so cant use sql server management studio).

It seems unreasonable that there is no simple way to do this. Is there one?


texts: Postgres - How to prevent locking when parent table is updated whenever an insert/delete occurs in child table

texts:
I have a simple use case wherein I update the parent table when a row is inserted/deleted in the child table.
Suppose we have a Products table and Items table and ProductId is the foreign key in the Items table.
Whenever an item is inserted or deleted in the Items table, an update is made in the Products table corresponding to the item. An update is simply a query like:
UPDATE "Products" SET "PropertyX" = $1, "UpdatedAt" = $2 WHERE "Id" = $3

Problem:
If the items inserted/deleted in the items table correspond to the same product in the products table then the database load increases considerably causing timeouts even for 3K reqs/min due to Lock:transactionid waits.
I tested this on Aurora Postgres (with 4 CPUs, 16G Ram) and this happens even if the number of rows in the Items table is in the thousands and the number of rows in the Products table is in the hundreds. I used .net core with entity framework.


texts:
I was trying to create a distribution database on an instance of SQL Server 2016 SP1 Standard Edition.

I got an error that the replication component of SQL Server was not installed. We have recently upgraded our server from SQL Server 2012 to 2016 where we had tested replication to a dev machine previously.

It occurred to me that the reason that the replication components aren't installed is that we perhaps aren't paying for them. Looking at the licensing guide found HERE, I can't find information on replication specifically - either because it IS included in the basic SQL Server app or it falls under some generic product name like 'Enterprise Data Management', or something else.

Questions:

Does SQL Server 2016 Standard Edition include replication?
Does the Distribution server count as a separate instance of SQL Server and require its own license?
If we are using this as a testing 'dev' ground, do we still need to purchase licenses for replication from prod to dev, with the distribution server hosted on the same dev machine?


texts:
I have a database which is running on  SQL Server 2014.

Activityid = 3 means: log in

Activityid = 4 means: log out

Rownr is not a column (it indicates the number of the row)

Scenario 1)
The database contains a table with the following rows. I would like to select let say row 6 and 7 if the given datetime is 2015-03-07 08:06:53.625. Because the given datetime falls between the StartTime value of row 6 and 7. How can I achieve this?

Scenario 2)
The Activityid 4 is missing at row 5. How can I select rows with missing Activityid 3 or 4?

Rownr   UserName    StartTime               Activityid  Value
1       Anneleen    2015-03-07 07:01:38.707 3           5811
2       Anneleen    2015-03-07 08:52:23.417 4           5811
3       Anneleen    2015-03-07 08:52:34.347 3           5811
4       Anneleen    2015-03-07 14:15:20.140 4           5811
5       Arnold      2015-03-07 11:03:20.420 3           5840
6       Dimitri     2015-03-07 07:02:53.633 3           5817
7       Dimitri     2015-03-07 21:24:20.633 4           5817

Rownr is not a column of the table. I've just added it to the result so I can point out which rows I want as result.


texts: How to get the SQL-server login name of an integrated security account?

texts:
Let's say I'm a user in windows domain DC (DataCenter).
My user-name is DC\Somebody
I'm in group DC\Developers
Now SQL-Server has the login group called DC\Developers, and with that, I can login on the server with integrated security.

I'm now trying to get the SQL-Server login (by which I mean DC\Developers), but I can't figure out how. All I can get is the username (DC\Somebody).
But I want to get DC\Developers.
How can I do that ?

This are the functions I tried so far, without success:

SELECT -- http://blog.sqlconcepts.co.nz/2011/07/who-am-i.html
        ORIGINAL_LOGIN() AS OriginalLogin
       ,SUSER_SNAME() AS CurrentContext
       ,SESSION_USER AS SessionUser
       ,SYSTEM_USER AS SystemUser
       ,CURRENT_USER AS CurrentUser
       ,user AS "WhichUserIsThat?"
       ,ORIGINAL_LOGIN() AS OriginalLogin
       ,USER_NAME() AS UserName

Also, what happens if multiple groups are added as sql-server logins, and I'm a member of them all, and then I login with integrated security. Which login will it take ? It appears to be the alphabetically first ...


texts: Database design: Modifying an open source database without a lot of changes

texts:
I am adding more functionality/features to an open source project.
The project is about a forum where each post can be given a specific list of tags.
The tags are homogeneous. I.e they are of similar type. Ex: Subject
I am required to add multiple homogeneous tags. For ex: Subject, Course, College, Experience. All of them should have the same features as current tag
Currently each post can have multiple tags of same type. The requirement is to multiple types where each type allows multiple tags
Below is a snippet of a already existing datamodel

How do I make changes to the already existing Database? (Please don't discard the question as opinion based since I saw many of them having similar doubts)
Do I need to add a separate table for each type of tags and redo whatever the original tag did?
Or should I create a level above tags which is a type of tags and they all inherit the basic tag functionality.


texts: How do I synchronize rows in tables in remote databases?

texts:
I'm actually developing a synchronization tool in vb.net, I have two database that have on each table records field GUID, this field help to have the same PK on both database. On each record there is also a field called lastUpdated, this field have a milliseconds value, so prevent two user to update the record in the same time. My question is, how I can compare the records of the same table from different db? For example:

ONLINE_DATABASE

TABLE_1

| ID |   GUID                             | NAME  |    LASTUPDATED              |
| 5  | 054ba092-b476-47ed-810b-32868cc95fb| John  | 06-01-2016 17:01:12.472438  |

CLIENT_DATABASE

TABLE_1

| ID |   GUID                             | NAME  |    LASTUPDATED              |
| 9  | 054ba092-b476-47ed-810b-32868cc95fb| Jack  | 06-01-2016 18:01:12.472438  |

How you can see I've update the record from client application, so I need to apply the same change to online database. Now I've a thousand records to check in about ten tables. So my question is, how I can made a system that do this? Actually I tough to read each row with a MySqlCommand reader but I also think that this procedure is slow... Suggest?

NOTICE THAT: the table have the same name in both db


texts: MariaDB corrupted after attempting full backup with mariabackup

texts:
I have Mariadb installed on a Raspberry Pi with a simple star schema for logging data that comes in on a Modbus server.  Everything was running fine for about a month and a half until one day I was met with errors about a corrupt database page.  This was the first time it happened.  Luckily I had a backup SD that I was able to recover the system from.

Today it happened again when I was trying to figure out another error that was occurring when trying to perform a full backup.
sudo mariabackup --backup --target-dir "${full_path}"/base --user=root
within my own script: "full_backup.sh".  The script has been working perfectly up until recently when I noticed the error.

I wasn't sure if I ran sudo mysql_secure_installation when I reinstalled mariadb after the first recovery, so I ran it and answered 'Y' to everything as usual.

Then, when I restarted mariadb sudo systemctl restart mariadb.service I was met with the same error I had the first time.

And when I looked in /var/log/mysql/error.log I saw the same error as last time there as well.

The first time I ran into this problem I spent hours researching what might be causing it to no avail.  I would greatly appreciate anyone out there who can help resolve this.  Thank you.

Update 1:  Tried following instructions here: https://mariadb.com/kb/en/library/what-to-do-if-mariadb-doesnt-start/ under the "Table Corruption" section.  Clicked link to "InnoDB recovery mode" followed that rabbit hole and tried setting the variable to 1 like this: mysqld_safe --innodb-force-recovery=1  Checked the variable with: mysqld --verbose --help | grep recovery and the variable is not changing to 1.  It stays set to 0.

Update 2: Out of desparation I tried uninstalling and reinstalling mariadb.  sudo apt purge mariadb-server then sudo rm -rf /var/lib/mysql/ then sudo apt install mariadb-server  Still getting the same error as before when I check sudo systemctl status mariadb  I'm running out of ideas.

Update 3: Just did sudo apt purge mariadb* then sudo reboot.  Now I can't SSH in.  The Pi is at a remote location.  What an absolute nightmare.


texts: In sys.dm_os_memory_cache_entries view, what does original cost mean?

texts:
Looking at a couple of DMVs, and trying to see if I can programmatically figure out the cost of the cached plans in my SQL Server.  I'm hoping that column represents the same concept of cost that is used by the optimizer to determine if the query should be parallelized, but I'm not sure.  Is that the case?

Edit: below is the script I'm using:

SELECT  text
      , objtype
      , refcounts
      , usecounts
      , size_in_bytes
      , disk_ios_count
      , context_switches_count
      , original_cost
      , current_cost
FROM    sys.dm_exec_cached_plans p
        CROSS APPLY sys.dm_exec_sql_text(plan_handle)
        JOIN sys.dm_os_memory_cache_entries e
          ON p.memory_object_address = e.memory_object_address
WHERE   cacheobjtype = 'Compiled Plan'
        AND type IN ( 'CACHESTORE_SQLCP', 'CACHESTORE_OBJCP' )
ORDER BY objtype DESC
      , usecounts DESC;


texts: Update performance: clustered versus covering index

texts:
I have a simple, 3-column table with about 50 million rows in it.  This table gets about 5,000 inserts/updates per second, and perhaps 20 queries per second are executed against the table.  The table looks like this:

Controller: Int
ExecutionTime: DateTime
Result: Int

To maximize the efficiency of my queries, I need two indexes.  (Result Includes Execution Time) and (Controller, ExecutionTime).  These two indexes fully cover my queries - all information is served directly from the indices, no table lookups required.

I chose nonclustered indices because I was worred about the performance hit using a clustered index with so many updates.  But it occurs to me that since I am fully covering the queries, this might not be a valid concern - perhaps my covering, nonclustered indices require the same amount of maintenance as a clustered index would.

So my question:  In a table with a lot of inserts/updates, will a covering, nonclustered index usually have a lower UPDATE performance hit than a clustered index?

Thanks for your time and help!


texts:
I restored a backup of a database from SQL Server 2008 R2 to SQL Server 2012.

When I try to access a particular page of my application, I get this error:

  Cannot execute as the database principal because the principal "dbo" does not exist

It works with my 2008 R2 project perfectly. How can I resolve this issue?


texts: Do I need to explicitly grant permissions for a login under the sysadmin role for it to access a custom schema?

texts:
So I have a login that has sysadmin rights on the db server.  However, it's not able to access database objects under a custom schema that was created (eg. it cannot select from tables with [custom_schema].[table_name]).  Do I need to explicitly grant permissions for this login to the custom schema even though it is part of the sysadmin server role?  If that is the case, do I simply run the following if I want it to have db_owner rights to my custom schema?

GRANT TAKE CONTROL ON SCHEMA :: [custom_schema] TO [login];

Thank you in advance!


texts: Measure CPU, Disk (I/O) and Memory Used by a Specific Database

texts:
For reasons outside the scope of this question, I need to deploy a specific database from my main server to a secondary server; the problem I have is that the secondary server does not exist yet and I need to purchase it.

The thing is, how can I measure the CPU, IO and Memory used by this specific database in order to buy a server capable of handling the load of this specific database in the same (or less) time than the current server?

Any guidance on this topic is greatly appreciated.


texts: Error when running sp_BlitzFirst from First-Responder-Kit-20170307

texts:
when I run

EXEC sp_BlitzFirst @expertmode=1

I get

Setting up configuration variables
Warning: The join order has been enforced because a local join hint is used.
Now starting diagnostic analysis
Capturing first pass of wait stats, perfmon counters, file stats
Beginning investigatory queries
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@LineFeed".
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ','.
Msg 8169, Level 16, State 1, Line 11
Conversion failed when converting from a character string to uniqueidentifier.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ' AS HowToStopIt,
                (SELECT TOP 1 query_plan FROM sys.dm_exec_query_plan(r.plan_handle)) AS QueryPlan,
           '.
Msg 105, Level 15, State 1, Line 27
Unclosed quotation mark after the character string ' AND tBlocked.wait_duration_ms > 30000;'.
Finished running investigatory queries
Waiting to match @Seconds parameter
Capturing second pass of wait stats, perfmon counters, file stats
Analyzing changes between first and second passes of DMVs
Analysis finished, outputting results

This is from First-Responder-Kit-20170307

The SQL Server version and build I have is:

Microsoft SQL Server 2012 (SP3-CU7) (KB3205051) - 11.0.6579.0 (X64)   Dec 22 2016 11:18:09   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


texts: Calculating duration between two events in a MySQL database (timeseries)

texts:
I have a (timeseries like) table in MySql which stores some on/off events. These events are reported at random intervals and represent the current state of an IoT switch:

I need help building the SQL query to calculate the duration (in munites) in which the switch was on.

There are a few obstacles:

The on/off events are reported a few times during a period i.e. there are duplicates
MySQL is version 5.5 and cannot be upgraded (i.e. lacking windowing functions). This version does not support nesting of sub queries in views.

UPDATE My goal is to calculate the total time the switch was ON for a given period. For example: between 2018-07-01 00:00:00 and 2018-07-10 23:59:59 the switch was ON for 56 minutes. My idea was to have the "ON" duration intervals in a table like this:

date-time           | duration-in-munites
2018-07-10 13:47:27 | 47
2018-07-11 00:01:13 | 12
...

Where date-time is when the first ON have occurred

From there I could sum up the durations for a given time span.
This may not be the right way to do it so I'm open to suggestions how to do it.

I'm interested only on OFF to ON transition time and duration
The initial values are unknown. The switch may have reported a few "off" events before reporting "on" event
Another issue is that the table contains data from other sources, so the IDs are no sequential.
The MySql server runs on Raspberry PI 3. As far as I know there is no ARM version of MySQL 8.

Here are Create/Insert statements for sample data


texts: Database Maintenance Job and Backup Scheduling

texts:
I would request to have expert advise on scheduling maintenance and backup jobs. Below was the scenario prior to my change:

Full backup of database scheduled to run at 12:30AM everyday.
Differential Backup scheduled to run every 2 hours in the business hour(8AM to 6PM) and every 6 hour in the non-business hour.
Log backup is scheduled to run every 15 minutes as Log-shipping is configured.
Index optimize job(Using script from Great Mr. Ola Hallengren) running every Sunday morning at 1:45AM.

We used to face space issue on the storage disk in the above scenario as Full backup was run before Maintenance job and hence subsequent differential backup was getting bigger and bigger in size until next full backup was run. This prompted me to run Full backup after maintenance job and I also checked the fragmentation level in mid of week and based on the values decided to run maintenance job twice a week, below is the modified plan:

Full backup of database scheduled to run at 12:30AM on all days except on Sunday and Tuesday when maintenance job is scheduled. On Sunday and Tuesday, Full backup is taken at 2:30AM.
Differential Backup scheduled to run every 2 hours in the business hour(8AM to 6PM) and every 6 hour in the non-business hour - No change.
Log backup is scheduled to run every 15 minutes as Log-shipping is configured - No change
Index optimize job(Using script from Great Mr. Ola Hallengren) running every Sunday and Tuesday morning at 1:45AM.

The issue I am facing right now is the size of log backup immediately after maintenance job, Log backup is much bigger than Full backup of database itself. Needless to say, Log backups are transferred to Secondary site, which is then uploaded there for syncing purpose. This taken longer than expected and in between Log-shipping alert gets triggered since primary and secondary is not in sync. Earlier also, Log backup was bigger however used to be much lesser than that of Full backup and was taking considerably lesser time in transferring from primary to secondary server.

I am not really sure, if this is a valid scenario wherein the changes(insert/update/delete) was so voluminous in last 3 days that the maintenance job created bigger log file than full backup and would stabilize gradually or I should be scheduling two full backup on Sunday and Tuesday(when maintenance job is running) - One at 12:30AM and another one after maintenance job.

Appreciate your kind advise.


texts: Activating standby DR after opening it in read-write mode

texts:
I have a primary and a standby database. Both two node RAC database.

As some testing had to be done in standby database, I had to open it in read-write mode.

I cancelled the recovery in standby and activated standby database and opened the two node database in read-write mode.

Now, the DR is broken. And to make the standby as a proper physical standby, full reconfiguration of DR is required?

If not, help me out in setting up my standby back in track!


texts: I know this is a basic question. but I can't find any answer on the internet. I know SQL server 2012 is running on Windows 2012 server but when I look at the start page (it has IE, Control panel, task manager and etc). but how do I start SQL server 2012 that is running on this server?


texts:
I have 3 tables which are related and a constraint on one of them which I can enforce in software but I don't know how to enforce in the database. I have a series of providers who send a set of data. A provider may send multiple schemes. So the pair of provider and scheme is distinct. A scheme consists of many records - let's say Credit Ratings. A user is associated with many credit ratings but only 1 from each provider/scheme pair. I'm stuggling to relate this data. What I currently have looks like:

Provider
========
id, providername, typename
1, Provider 1, type1
2, Provider 2, type1
3, Provider 2, type2

CreditRating
=======
id, providerid, rating, rank
1, 1, A, 1
2, 1, B, 2
3, 1, C, 3
4, 2, AA, 1
5, 2, B, 2
6, 3, A+, 1
7, 3, AA, 2

UserRating
==========
id, ratingid, userid
1, 1, 1
2, 1, 2
3, 5, 1

Now what I want to enforce is that the UserRating table, for each user there's only one credit rating from a given provider/type pair. With my current design I can't do that.
One option would be to add the providerid to the userrating table but then I can't ensure that this is consitent with the providerid on the credit rating itself.

Any ideas?


texts: Search between dates where all dates appear

texts:
I want to search house_id where all days appear within a range of 2 dates from startDate to finalDate.

Tables columns are:

id
house_id
date
status

house_id + date are unique

the closest I could get is:

SELECT * FROM (
SELECT *
FROM tests) AS T1
WHERE date BETWEEN '2016-02-01' AND '2016-02-02'
HAVING COUNT(*) = DATEDIFF('2016-02-02', '2016-02-01') < 1

sample data:

CREATE TABLE IF NOT EXISTS `tests` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`),
`house_id` int(10) unsigned NOT NULL,
`date` date NOT NULL,
`status` enum('0','1','2') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tests` (`id`, `house_id`, `date`, `status`) VALUES
(1,1, '2016-02-01', '1'),
(2,1, '2016-02-02', '1'),
(3,1, '2016-02-03', '1'),
(4,1, '2016-02-04', '1'),
(5,1, '2016-02-05', '1'),
(6,1, '2016-02-06', '1'),
(7,2, '2016-02-01', '2'),
(8,2, '2016-02-02', '2'),
(9,2, '2016-02-04', '2'),
(10,2, '2016-02-05', '2'),
(11,2, '2016-02-06', '2'),
(12,2, '2016-02-07', '2'),
(13,2, '2016-02-08', '2')


texts: Run a query in loop, with the results of another query

texts:
First, I want to list databases with a specific table:

 SELECT name
FROM   sys.databases
WHERE  CASE
         WHEN state_desc = 'ONLINE'
              THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[TB_INF_CLI]', 'U')
       END IS NOT NULL

Then, I want to use the database_name inside a loop, to list all indexes of this table ( TB_INF_CLI )  of all these databases, with this query:

SELECT DB_NAME() AS Database_Name
, o.name AS Table_Name
, i.name AS Index_Name
, i.type_desc AS Index_Type
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL
AND o.type = 'U'
ORDER BY o.name, i.type

How can I do this without msForEachDb?

with this solution, I can only list the dabatase i'm connected. I can't understand how can I implement the database_name inside this query:

SELECT DB_NAME() AS Database_Name
, o.name AS Table_Name
, i.name AS Index_Name
, i.type_desc AS Index_Type
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL
AND o.type = 'U'
and db_name() in
(SELECT name
FROM   sys.databases
WHERE  CASE
         WHEN state_desc = 'ONLINE'
              THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[TB_INF_CLI]', 'U')
       END IS NOT NULL
)
ORDER BY o.name, i.type

EDIT:

i could achieve what I want with this:

create table ##TB_REN (database_name varchar(200),table_name varchar(200),index_name varchar(200),index_type varchar(200))
sp_msforeachdb'use [?]
insert into ##TB_REN( database_name,table_name,index_name,index_type)
SELECT db_name() AS Database_Name
, o.name AS Table_Name
, i.name AS Index_Name
, i.type_desc AS Index_Type
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL
and o.name =''TB_INF_CLI''
AND o.type = ''U''
ORDER BY o.name, i.type'

Is there  a better solution??


texts: availability groups - checking for primary server - cannot call a procedure with parameters

texts:
In my servers that are involved in alwayson availability groups if there are jobs running, inside the jobs I always test whether the current server is the primary in the availability group.

So the job only runs on the primary server of the AG.

something like this is working fine:

If sys.fn_hadr_is_primary_replica ('JUNOReporting') =1
BEGIN
        declare @reportYear int, @reportMonth int, @reportMonthPrevious int

        SELECT @reportYear  = CASE WHEN month(getdate()) = 1 THEN year(getdate()) - 1 ELSE year(getdate()) END,
               @reportMonth = CASE WHEN month(getdate()) = 1 THEN 12 ELSE month(getdate()) - 1 END

        SELECT @reportMonthPrevious  = @reportMonth - 1
        EXEC JUNOReporting.dbo.usp_some_other_procedure_without_parameteres
END

However, if instead of the procedure dbo.usp_some_other_procedure_without_parameteres that lives in the database called JunoReporting,
I want to call a different procedure that has parameters, then SQL Server does not like it.

I think he is putting the cart before the horses, but how can I tell him?

when I have the following code within a job, the job fails:

declare @reportYear int, @reportMonth int, @reportMonthPrevious int

SELECT @reportYear  = CASE WHEN month(getdate()) = 1 THEN year(getdate()) - 1 ELSE year(getdate()) END,
       @reportMonth = CASE WHEN month(getdate()) = 1 THEN 12 ELSE month(getdate()) - 1 END

SELECT @reportMonthPrevious  = @reportMonth - 1

If sys.fn_hadr_is_primary_replica ('JUNOReporting') =1
begin
    -- this server is the primary replica, do something here
    print 'yes, primary'

            IF @reportMonthPrevious >= 1
            BEGIN
                EXEC JUNOReporting.dbo.usp_ins_feesDuePaid_ForMonth @reportYear, @reportMonthPrevious

            END
            EXEC JUNOReporting.dbo.usp_ins_feesDuePaid_ForMonth @reportYear, @reportMonth
END

with the boring and obvious error message - that's exactly why I was testing if the current server is in the primary role anyway:

  Msg 976, Level 14, State 1, Line 22

  The target database, 'JUNOReporting', is participating in an availability group and is currently not accessible for queries. Either
  data movement is suspended or the availability replica is not enabled
  for read access. To allow read-only access to this and other databases
  in the availability group, enable read access to one or more secondary
  availability replicas in the group.  For more information, see the
  ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

Any ideas how to work around this one?
I prefer not to add that piece of code inside the procedure, so avoiding the parameters issue sql server seems to have on the occasion.

Just to clear any misunderstanding:

On the seconday server  of my Availability Group (consists of 2 servers only)
the databases are not accessible as you can see on the picture below.


texts: Master slave replication in mysql seconds behind master goes on increasing

texts:
In replication at slave end seconds_behind_master goes on increasing.
The dump file for upload is of 6.7 gb large, can it be the reason for the lagging of seconds behind master?


texts: How different are the top DB vendor's SQL (DML-) dialects from each other?

texts:
Does someone who has worked extensibly SQL-wise with at least two top DB products (such as Oracle, SQL Server, Informix, Sybase, DB2, Teradata) know how different the DB vendor's SQL dialects are from each other. Since I come from an Oracle background, I am especially interested in

analytical functions
model clauses
hierachical queries (start with .. connect by comes to mind)
any other feature that is more than the usual (select x, y from a, b where...)

Probably, the question boils down to if and to what extent these features are regulated by an ansi standard.

Practically, I'd like to know if there are "rule of thumbs" that would indicate if (and how) I can take an SQL DML-statement that runs on one database and let it run on an other database.


texts: PostgreSQL: how do I list all triggers that use a specific field?

texts:
for example i have a student table with (studentid)
i want to get a list of triggers that use this field...

for example if in table X there is a trigger that uses studentid i want it to be shown in the query result.


texts: Configure database mirroring for multiple databases via TSQL or Powershell when mirror is already restoring

texts:
We are trying to automate the mirroring set up for multiple databases whose mirror is already in RESTORING state as the set up was part of log shipping

So when we switch from log shipping to mirror I am unable to find a way using TSQL or Powershell to set this mirroring between Primary and Secondary as most of the scripts on the internet are taking full backups initially and restoring. But we don't need to implement that as DB is already restoring.

We can set this up using GUI but it's time consuming when doing so for 20 DBs during migration. Please help with any mirroring automation that will set up for multiple databases having their mirror already set up with databases in restoring mode.


texts: How do I optimize this extremely simple MySQL query?

texts:
I am using MySQL 5.7 (can't change this), and I am kind of new to MySQL. I have a fair amount of experience with SQL sever so just trying to work through some of these quirks.

The query I want to run is

select *
from temp_main_working t
  left outer join temp_sf s on t.PM_ORDER_ID = s.pm_order_id
where s.pm_order_id is null

The explain looks like this

If I change the outer join to an inner join (which obviously won't give me what I want), as such

select *
from temp_main_working t
  inner join temp_sf s on t.PM_ORDER_ID = s.pm_order_id

I get an explain that looks like this

which is obviously much better for performance.

In other cases where i use a left join, it managed to use associated indexes just fine. What is going on here? How do I get it to run efficiently?

UPDATE

Including explain for Akina's suggested query


texts: If AB->C is an FD for a database then does that imply A->C

texts:
From below link on Functional Dependencies under FD Axioms(Armstrong's)
Reference Link on research

I came to know that
ab→ c does NOT imply a → c and b → c

But, when I was reading the book "An Introduction to Database Systems" by C.J.Date, on page 279, there is an example that tells about reduction of following set of FDs:

A -> BC
B -> C
A -> B
AB -> C
AC -> D

and under point number 3 of the reduction process it further mentions:
Next, we observe that the FD AB -> C can be eliminated, because again we have A -> C, so AB -> CB by augmentation, so AB -> C by decomposition.

So, that means that if A -> C, then we can imply AB -> C.

Assuming we are given the FD AB -> C in a separate case, then let us assume that A -> C, so AB -> C by above deduction, thus our assumption is true. Then, it proves the data in above link false! Is this mathematical approach not correct or I am wrong in a different way? Please throw some light on each perspective!


texts:
Possible Duplicate:
  mysql select query optimization

I have two tables, testa and testb.

CREATE TABLE `testa` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `testb` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT NULL,
  `aid1` INT(10) DEFAULT NULL,
  `aid2` INT(10) DEFAULT NULL,
  `aid3` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Currently I am running the below query for retrieving all rows where id in testa table matches with any columns of aid1, aid2, aid3 in tableb. The query returns the accurate result but it is taking minimum 30 seconds to execute which is too much. I have also tried to optimise my query using UNION but failed to do so.

SELECT a.id, a.name, b.name, b.id
FROM testb b
INNER JOIN testa a ON b.aid1 = a.id OR b.aid2 = a.id OR b.aid3 = a.id ;

How do I optimize my query so it's total execution time is within 2-3 seconds?

Result of EXPLAIN:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  b   ALL idx_aid1,idx_aid2,idx_aid3  (NULL)  (NULL)  (NULL)  10940
1   SIMPLE  a   ALL PRIMARY (NULL)  (NULL)  (NULL)  7512    Using where; Using join buffer


texts: Trace flag 2861 and what a 'zero-cost' plan actually means

texts:
I'm trying to get my head around trace flag 2861 and what it actually does with trivial queries?

The blurb says:

  SQL Server typically won't cache plans for these trivial queries because the cost of caching the plan is higher than the cost of generating a new plan for such a simple query.

That seems to be patently untrue since every 'trivial' query I run seems to get cached. So I'm wondering what the point of 2861 is, unless I'm misunderstanding what a trivial plan actually is. When I query the cached plan and it says it's ad hoc and trivial then I have no reason to doubt it.

Hoping someone can enlighten me.


texts: SQL Server 2012 Always-On replica change

texts: Newbie question.  Using SQL 2012.  I have AlwaysOn setup for 3 servers.  Server A is my primary, my replicas are Servers B and C.  I have 5 DBs on Server A, all of which are replicated to both replicas.  I want to pull just DB1 off of Server C and leave it replicated between Server A and Server B.  Doable?  If I pull DB1 off of my primary and then try to add it back in, just replicating to Server B, it is grayed out.  The only way to add DB1 back into the mix is by replicating to both of my replicas B and C...not what I want to do.


texts: Upgrade from 2008 RTM to 2008 R2 RTM failed due to perf-ReportServer-rsctr.dll is being used by another process

texts:
I was upgrading SQL Server 2008  RTM on Windows Server 2008 R2 SP1 to SQL Server 2008 R2 RTM. I got this error:

  The process cannot access the file
  'C:\Windows\SysWOW64\perf-ReportServer-rsctr.dll' because it is being
  used by another process.

  Service 'MSSQLServer' Stop request timed
  out and this occurred during
  'sqlEngineConfigAction_upgrade_shutdowninstance_cpu64 ' phase and I
  stopped & disabled WMI even though it was not running and did not
  install process monitor to see who locked this dll. I also shutdown
  application servers before starting the upgrade to avoid them to
  attempt to connect to SQL Server.

After the failed upgrade  full-text search, SQL Server Replication, Database Engine Services, Analysis Services and Reporting Services status were Failed but others succeeded such as management studio & integration Services etc and then I restarted the server as it was required  but as expected after reboot server was still in 2008 RTM and when I queried select @@version.

Do you think I should upgrade SQL 2008 to latest service pack SP4 and upgrade as this bug may have been fixed on latest service packs and then upgrade to SQL Server 2008 R2 or just figure out which process or application that may be using this dll?


texts: MySQL thinks Master & Slave have same server-id, but they don't

texts:
Trying to setup MySQL to run one-way replication. I have the master set to server_id = 1, a replication user setup, binary logging enabled, the Slave server_id = 2 and is connected and waiting for an event. I am also using Workbench.

However, the Master server has the following error...

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

I can't figure out any reason for this error. I have looked at dozens of tutorials, manuals, etc and none of them is explaining to me what is happening.


texts: SQL Server detected a logical consistency-based I/O error - tempdb

texts:
We started receiving these error messages suddenly

  Error: 824, Severity: 24, State: 2.

  Message SQL Server detected a logical consistency-based I/O error:
  invalid protection option. It occurred during a read of page (7:8192)
  in database ID 2 at offset 0x00000004000000 in file
  'X:\tempdb\tempdb_mssql_6.ndf'.  Additional messages in the SQL Server
  error log or system event log may provide more detail. This is a
  severe error condition that threatens database integrity and must be
  corrected immediately. Complete a full database consistency check
  (DBCC CHECKDB). This error can be caused by many factors; for more
  information, see SQL Server Books Online.

  SQL Server detected a logical consistency-based I/O error: incorrect
  checksum (expected: 0x31333637; actual: 0x9c88f38a). It occurred
  during a read of page (7:8216) in database ID 2 at offset
  0x00000004030000 in file 'X:tempdb\tempdb_mssql_6.ndf'.  Additional
  messages in the SQL Server error log or system event log may provide
  more detail. This is a severe error condition that threatens database
  integrity and must be corrected immediately. Complete a full database
  consistency check (DBCC CHECKDB). This error can be caused by many
  factors; for more information, see SQL Server Books Online.

  An error occurred while processing the log for database 'tempdb'.  If
  possible, restore from backup. If a backup is not available, it might
  be necessary to rebuild the log. During undoing of a logged operation
  in database 'tempdb', an error occurred at log record ID
  (239:1702646:104). Typically, the specific failure is logged
  previously as an error in the Windows Event Log service. Restore the
  database or file from a backup, or repair the database.

  SQL Server must shut down in order to recover a database (database ID
  2). The database is either a user database that could not be shut down
  or a system database. Restart SQL Server. If the database fails to
  recover after another startup, repair or restore the database.

After these messages, I had to start SQL services manually, however the errors continued

  Error while allocating extent for a worktable.  Extent (6:16248) in
  TEMPDB may have been lost.

I ran DBCC CHECKDB(tempdb)  --- no results.....

Checked with storage team for health checks, they say no issue.

However, I see many suspect_pages recorded for this DB and it even got recorded before the server crash too.

then I had to manually delete the tempdb files and restart SQL services. Errors stopped and DB health is OK.

We are on latest version - Microsoft SQL Server 2016 (SP2-CU8) (KB4505830) - 13.0.5426.0 (X64)   Standard Edition (64-bit) ...and I did this upgrade a week ago...it was running fine and suddenly it showed this behavior..and also Rebuild-Reorg maintenance job is running at that time...Storage admins denied any issues from their side...

Trying to investigate the root cause, any pointers/advise is really appreciable. Thanks in advance.


texts: SQL Server stored procedure that would enforce atomicity?

texts:
Let's say I have following insert statements

INSERT INTO tEmpl  (lastName)  VALUES('Smith')
INSERT INTO tEmpl  (lastName) VALUES('Jones')
INSERT INTO tEmpl  (lastName) VALUES('Smitty')
INSERT INTO tEmpl  (lastName) VALUES('Smithson')
GO

What stored procedure would enforce atomicity? I mean, all inserts have to be executed at once or none of them. Please give examples. I am still learning SQL Server.


texts: What happens when a SQL Server node goes down in an always on clustering?

texts:
We have an application (vendor provided) writes/reads data to/from SQL Server 2016 whenever the application is accessed. The database is located on shoreside, and the network connection between the application and database is Very Very Good (a dedicated high speed LAN Connection).
Our options so far
Now, we want to use the same application on our ships. We have three options now:

Create a separate database instance on shipside and let the application write the data to the database.
Create a new node in Ship1, form a Always On (Sync Mode) High Availability Group Cluster with the another node on shoreside. Let the application uses the database cluster through SQL Server Listener and write/read database. The connectivity between two databases (shoreside database and shipside database) is through satellite link. The users on the ship will use the application on shipboard. Shoreside Application is connected to the Shoreside Database. Shipside application is connected to shipside database. The application (shoreside/shipside) is configured as ACTIVE-ACTIVE, bothe the databases are connected on Always On SYNC Mode.
Do not create a separate database. Use the same database on shoreside, let the application on shipside use shoreside database through satellite link connection. I don't want to consider option # (3) here, as the satellite link goes down, the application users on the shipside can't access the database.

Considerations

With option (1), we have manage 'n' + 1 SQL Server installations and 'n' + 1 Application Instances where 'n' being number of ships. All database and applications are completely independent. This option provide the highest level of availability and it also comes with the highest level of administrative effort. In our case 'n' is 85, so we have to manage 85 web application instances, 85 separate database servers and this is without considering DR options. If we consider DR, then this will be doubled.

With option (2), We have to manage one web application, and one database instance. The web applications across the ships can be configured as ACTIVE-ACTIVE, so users from shoreside/shipside can use the highly available web application. At SQL Server "backend", we have the shoreside node and shipside nodes are connected on ALWAYS-ON SYNC Mode. Like this, all 85 ships database nodes are connected, making it single database instance across all ships.

The questions
My questions are about option (2).

What happens if one ship carrying a database node goes offline?
Considering the scenario, what happens if Shoreside application wants to perform "WRITE" action on the database, but all the 85 ship database nodes are offline? When this "write" action will be completed and all database nodes are synchronised?
What happens if shipside application wants to perform "read" when other nodes are offline?

Pictorial Representation (showing just 3 ships for illustration)
Dashed arrows between shipside database servers means connectivity using satellite; generally its poor with packet loss.


texts: Trigger/Event that fires when a Transaction finishes

texts:
I have a query that pulls from about 15 different tables.  I am looking to materialize this into a table that stores it in xml/json.  (To improve performance.)

The problem I have is that these tables are updated by several processes.  I am looking for a way to keep this in SQL Server if possible.

Ideally, I would love it if SQL Server had a trigger that would fire right before a transaction commits, so I could look at the tables and records that were affected and know if I need to update the "result" table.

Is there something like that in SQL Server?

NOTE: I have considered using the INSTEAD OF trigger, but I have no way of knowing the order of the tables in the transaction, so if the transaction updates all 15 tables, then I will be updating the "result" table 15 times for the same row.


texts: How to satisfy 2NF and what are superkeys? - Understanding the algorithm for database normalization in general

texts:
Given the table:

Student_subject(studentid, subjectid, username, grade)

Primary key consisting of a combination of:

studentid, subjectid

Candidate key consisting of a combination of:

subjectcode, username

Now it can be saide the following functional dependencies exist:

studentid, subjectid -> grade (BCNF)
username, subjectid -> grade (BCNF)
studentid -> username (3NF)
username -> studentid (3NF)

BCNF - Boyce–Codd normal form (3.5NF)

So the NF of the table will equal the lowest NF found among all FDs, right?
There seems to be some different explanations around for how to find the NF levels.

As I've seen from one explanation on how to satisfy 2NF, it is so that all data/columns need to depend on the whole/entire primary key, but I can't see that is the case (since the primary key is defined as the combination of studentid, subjectid) with

studentid -> username (3NF)
username -> studentid (3NF)

And if it doesn't fulfill 2NF, it can't satisfy a higher level 3NF. isn't it so that you start to see if 1NF is satisfied, and if it is you check if 2NF is as well and so on...

Also superkeys are anything that identifies a row uniquely, incl. candidate keys, primary key and all possible combinations of these with the rest of the attributes?


texts: How to check the memory allocator used by my mysql 5.7.20

texts:
I am suspecting memory leak in my mysql 5.7.20 server as per below filed bug
https://bugs.mysql.com/bug.php?id=83047

So i installed jemalloc in my centos 7 server and rebooted mysql(service mysqld restart).
Now i wanted to check whether mysql has picked jemalloc as the mem allocator or not

I tried

pt-mysql-summary | grep -A5 -i "memory management"
Memory management library
jemalloc is not enabled in mysql config for process with id 89539 The End

where can i check that information in running mysql instance?
Please help.. thanks in advance.


texts: User login error when trying to access secured SQL Server database

texts:
We have a username that was recently renamed from one username to another (think getting married). The Active Directory admin renamed the user because "it has always worked in the past".

One vendor package we use uses the built-in MS SQL Server security. Each module has three groups:

xxView = View Only
xxUser = Add, Update rows (cannot delete)
xxAdmin = Delete rows

So we can add a person to one of these groups an they get the appropriate access. I don't have the actual error message in front of me anymore, but it said that they are not authorized to table CriticalVendorTable. It worked before the rename. The admin removed the person from each group and re-added them. Still no go. I even restarted the server and it still doesn't work. My best guess is that there is UUID (or unique id) somewhere that is causing problems.

The vendor's response is to delete the user and then re-add them. I have only had time to do some brief searching, but I found this page; AD User SID Mis-mapping. Would this be worth trying? Would it be better to just delete the user and recreate them?


texts:
My MongoDB server was full so Mongod service could not start. There was a specific db that I didn't need, so I have deleted its file from the dbpath.
Now when I try to start the service again I'm getting the following error in the log:

*** SERVER RESTARTED ***
...
.. DBName_Example Assertion failure isOk() src/mongo/db/storage/extend.h 80
...
.. exception in initAndListen: 0 assertion src/mongo/db/storage/extent.h, terminating
...

I tried to run mongod -f /etc/mongod.conf --repair but I'm getting the same error.

I know that was a bad idea to delete the file, but I have no way to restore it. How can I fix it considering that the data is important and I need to keep it.


texts: transaction log backup format sector size

texts:
I am trying to backup a log file in SQL Server but I obtain an error message while yesterday I did it successfully.
The only difference between yesterday and now is that I just truncate the principal table in the database.
The error message in french I get is the following:

USE master BACKUP LOG MQ  TO DISK = 'D:\MSSQL\BackupToSendToFtp\MQLog.bak'

  Impossible d'utiliser le fichier de sauvegarde
  'D:\MSSQL\BackupToSendToFtp\MQLog.bak' car il a été formaté à
  l'origine avec une taille de secteur de 512 et se trouve maintenant
  sur une unité dont la taille de secteur est 4096.

Which in English should be:

  Cannot use the backup file 'D:\MSSQL\BackupToSendToFtp\MQLog.bak'
  because it was originally formatted with sector size 522 and is now on
  a device with sector size 4096.

A few days ago I upgraded the Azure VM to get a more powerful one. Can this be the main cause of the backup failure?

If yes then why did the backup succeed yesterday?


texts: SQL Full-text Filter Daemon Launcher with Startup set to Manual is Running after Bootstrap

texts:
I have on my Windows 10 PC an instance of SQL Server 2017 Developer edition
All the services Startups are set to Manual
Today I started the PC and found in the SQL Server Configuration Manager that the SQL Full-text Filter Daemon Launcher was Running
I'd expect for it to be Stopped, since the startup mode is set to Manual.
Why? Is this a normal behavior I'm unaware of?


texts:
How is PostgreSQL parsing supposed to work?

rivet=# SELECT 1 IS NOT NULL::int + 2 IS NOT NULL::int;
 int4
------
    1
(1 row)

But

rivet=# SELECT 1 IS NOT NULL::int + (2 IS NOT NULL)::int;
 ?column?
----------
        2
(1 row)

This seems very unintuitive?

Is this a mistake? Can anyone help me understand what PostgreSQL is doing?


texts: Alter TEXT column to NOT NULL when there is existing NULL rows

texts:
Mysql does not support default values for TEXT columns. I have a live database with NULL values

What is the proper way to change all null values to a predefined value and change the column to not null? I would like to do this in a single transaction to prevent new NULL values being inserted between updating all old NULL values and ALTERING the column.


texts: MySQL - Pass input parameter into Cursor query

texts:
Is it possible to pass input parameter into Cursor

SELECT statement WHERE clause?

For some reason I think it isn't working.

I'm trying to pass _TAG and _ITEM_NAME  into where clause.

    DECLARE cursor_test cursor
for SELECT itemid
FROM items
WHERE  key_ LIKE "sometext_@_TAG_sometext_@_ITEM_NAME"
AND STATUS = '0';

Here is the the Stored procedure:

    DELIMITER //
    CREATE PROCEDURE getSomething(IN _HOSTNAME VARCHAR(20),
                                  _TAG VARCHAR(20),
                                  _ITEM_NAME VARCHAR(50))
    BEGIN
    declare FINISHED BOOL default false;

    DECLARE cursor_test cursor for SELECT itemid
FROM items
WHERE hostid = @_HOSTID AND key_
LIKE "sometext_@_TAG_sometext_@_ITEM_NAME"
AND STATUS = '0';

    DECLARE CONTINUE HANDLER for not found set FINISHED := true;

    SET @HOSTNAME = _HOSTNAME;

    PREPARE STMT1 FROM

    "SELECT hostid INTO @_HOSTID FROM hosts  WHERE NAME = ?";

    EXECUTE STMT1 USING @HOSTNAME;
    DEALLOCATE PREPARE STMT1;

    open cursor_test;
       SET @TOTAL_VALUE := 0;
       loop_itemid: loop
            fetch cursor_test into _ITEMID;
            SELECT _ITEMID;
            if FINISHED then
                leave loop_itemid;
            end if;
            SET @TOTAL_VALUE := @TOTAL_VALUE +
             (SELECT value from history_uint WHERE itemid = _ITEMID
               ORDER BY clock DESC LIMIT 1);

       end loop loop_itemid;
       SELECT @TOTAL_VALUE;
    close cursor_test;

    END //


texts: Error 35338: Clustered columnstore index is not supported

texts:
On SQL Server 2012, I want to index a table with a columnstore index. But an error message apears:

  Msg 35338, Level 16, State 1, Line 1
  Clustered columnstore index is not supported.

My code is :

CREATE TABLE [dbo].[Card](
    [CardId] [int] IDENTITY(1,1) NOT NULL,
    [CardSerialNumber] [varchar](19) NULL,
    [CreateDate] [datetime] NOT NULL
) ON [PRIMARY]

GO
CREATE CLUSTERED COLUMNSTORE INDEX cci_Simple ON [dbo].[Card]


texts:
In one transaction I'm inserting a lot of rows into table T1.
"After insert" trigger of table T1 inserts into table T2.

Is it possible during that process select from T2 in another transaction? Or selecting from T2 waits after inserting to T1 is finished?


texts: How do I merge similar records with different validity dates?

texts:
The table I am working on has three components:

An ID column (primary key in another table)
Some data columns
Date valid from/to columns.

Values:

ID   Data From        To
1    a    2015-01-01  2015-01-05
1    a    2015-01-06  2015-01-10
1    b    2015-01-11  2015-01-15
1    a    2015-01-16  2015-01-20
2    c    2015-01-01  2015-01-05
2    c    2015-01-06  2015-01-10

The table is updated by taking "snapshots" of another data source at some intervals and assigning validity dates to records. The problem is that these snapshots create duplicate entries for records (with different validity dates) that were not changed at all during that interval.

I want to reduce the size of the table by looking for rows with consecutive dates and by merging them and assigning them a single validity period. For example:

ID   Data From        To
1    a    2015-01-01  2015-01-10
1    b    2015-01-11  2015-01-15
1    a    2015-01-16  2015-01-20
2    c    2015-01-01  2015-01-10

The logic I currently have is:

Select and sort all rows by ID, data fields, and 'valid from' fields (so they are in groups of consecutive rows).
Use a cursor to compare adjacent rows for similarity.
If they are same, then merge rows and change validity period to include both rows.

I understand that cursors are very inefficient (I have a large dataset), so I am looking for other approaches.


texts: SQL Server service broker related rights issue

texts:
CREATE PROCEDURE [dbo].[stproc_ssb_send]
  @iname varchar(20)
WITH EXECUTE AS 'xyz'
AS
BEGIN

DECLARE @DIALOG_HANDLE UNIQUEIDENTIFIER
DECLARE @MSG varchar(max)

SET @MSG='<MESSAGE>
        <test>' + @iname + '</test>
        </MESSAGE>'

    BEGIN DIALOG CONVERSATION @DIALOG_HANDLE
    FROM SERVICE START_PROC_PROCESS_SERVICE
    TO SERVICE 'EXECUTE_PROC_PROCESS_SERVICE'
    ON CONTRACT [PROC_PROCESS_CONTRACT]
    WITH ENCRYPTION= OFF;

    SEND ON CONVERSATION @DIALOG_HANDLE
    MESSAGE TYPE PROC_PROCESS_MT(@MSG);

    END CONVERSATION @DIALOG_HANDLE
END

error:

  Msg 15517, Level 16, State 1, Procedure [stproc_ssb_send], Line 0
  Cannot execute as the database principal because the principal "xyz" does not exist, this type of principal cannot be impersonated, or you do not have permission.

XYZ is db_owner / db_ddladmin for this database.

Procedure is created using sa; error occurs while executing.


texts: Which one of these two contact management database designs is correct or best practice?

texts:
In this first database design, column name starting from first_name to country exist in each table. The relationship of each table to table user_categories is ONE TO MANY.

In this second database design, there is no duplication of column name in each table. The relationship of table users and table shippers, suppliers, employees and customers are ONE TO ONE. Is the relationship correct?

The purpose of table shippers, suppliers, employees and customers is to store users based on its category in respective table just like the first design. I'm curious if this is a good practice since the columns of fourth tables is only its ID. Is there any column that I can add to those tables?

Could you please advice me if there are any better solutions?


texts:
I'm currently in the process of designing the database for a rather minimalistic Content Management System I'm working on alone. It should allow scalability while remaining speed, especially on boxes with low-end hardware.

As I am doing database design this carefully for the first time I am struggling more than a bit and could use some help. I've made two models for the database, which both fail to satisfy me:

Model 1: Everything dynamic is an entity (i.E. menu items, blog posts, projects):

I want to achieve maximal scalability by using the same base for every content type, but one of my examples already fails: the menu. Not all menu item's might have a URL (dropdown-item's for example) and some might be separator, which would be impossible to achieve with this.
Model 2: Posted dynamic content are entities (i.E. blog posts, projects, comments):

Not as scalable as Model 1, but most likely faster. Still, contents won't need a preview (which is shown as description on the landing page) as they're only shown on their parent's pages.

The Project-class is just an example class for content-type-specific metadata, in this case the info for a button that could lead to a github repository, an image gallery and so on.
I've got the feeling I'm completely failing at this, I also fear that this will result in hundreds of queries when trying to get comments on something (checking for parent's on each one for correct nesting etc.).

I'll be glad for everyone getting me an advice on how to approach this better, a tip on how to view this from a different angle or anything else that could point me in the right direction.


texts: What are good ways to store decorated objects in a relational database without adding a field for every possible property?

texts:
Suppose I have an object, I use a decorator to add properties to this object, and then I want to store the object and its properties in a relational database, and be able to run queries on these stored properties.

For example, say I've got a database with kitchen equipment. Base properties would include things like make, model, and description. Of the pieces of kitchen equipment, some use electricity, some use gas, some use water, some need a drain, some need fire protection, some need routine maintenance, etc. These properties are generally not Boolean flags, but are more of a rich collection of classes themselves. So, if I want to keep all of my "equipment" in one table, I've got to find a way to store all of these properties.

I'm using a decorator to add properties to objects instead of using inheritance to reduce the number of classes, as I don't want to have 2^(number of properties) classes.

Two things I've come up with so far are table per property and field per property.

Table per property has many more joins to reconstruct the object and field per property has a lot of empty fields for many objects, and requires adding a field whenever I introduce a new unique property. Is there some (sane) way to address this other than to use one of the two methods mentioned above? I know that this can be done in some NoSQL databases, this question specifically concerns relational databases.


texts: Postgres chooses seq scan over index scan

texts:
I have the following query:
SELECT *
FROM foo
  LEFT OUTER JOIN foo_history ON foo.id = foo_history.foo_id
WHERE foo.entity_id = 11111

foo table is around 8Million rows and foo_history is around 3Billion rows.
Postgres chooses the following plan
Gather  (cost=1000.58..132862.85 rows=9514 width=70) (actual time=462.013..5886.972 rows=14304 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Nested Loop Left Join  (cost=0.58..130911.45 rows=2378 width=70) (actual time=380.838..1450.773 rows=2861 loops=5)
        ->  Parallel Seq Scan on foo  (cost=0.00..93746.74 rows=6 width=24) (actual time=366.122..366.168 rows=6 loops=5)
              Filter: (entity_id = 11111)
              Rows Removed by Filter: 1634858
        ->  Index Scan using ix_foo_history_foo_id on foo_history foo_history_1  (cost=0.58..6005.63 rows=18849 width=46) (actual time=3.905..192.879 rows=511 loops=28)
              Index Cond: (foo_id = foo.id)
Planning Time: 1.091 ms
Execution Time: 5891.124 ms

Which requires a seqscan over foo table (8Million row). But when disabling seqscan, postgres chooses a much faster plan:
Nested Loop Left Join  (cost=1.01..148667.31 rows=9514 width=70) (actual time=0.138..27.413 rows=14304 loops=1)
  ->  Index Scan using ix_foo_entity_id on od  (cost=0.43..8.46 rows=24 width=24) (actual time=0.048..0.090 rows=28 loops=1)
        Index Cond: (entity_id = 11111)
  ->  Index Scan using ix_foo_history_foo_id on foo_history foo_history_1  (cost=0.58..6005.63 rows=18849 width=46) (actual time=0.027..0.678 rows=511 loops=28)
        Index Cond: (foo_id = foo.id)
Planning Time: 1.135 ms
Execution Time: 28.927 ms

Which use the index over foo.entity_id to find the relevant rows.
The statistics don't seem off to me since they roughly match the ones obtained when running the query (or i'm misreading smth in EXPLAIN ANLYZE).
Also random_page_cost is set to 1.1 which should "encourage" postgres to do index scans.
So I don't know what's mileading postgres here. Maybe the cost of "Index Scan using ix_foo_history_foo_id" is too high ? But why would that be the case since random_page_cost is set to a reasonable value !
EDIT:
Postgresql version: PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
Tables definitions:
create table foo
(
    id integer default nextval('foo_id_seq'::regclass) not null
        constraint foo_pkey
            primary key,
    entity_id integer not null
        constraint foo_entity_id_fk_entity_id
            references entity
                on delete cascade,
    misc1 integer not null,
    misc2 integer not null,
    misc3 integer
);

create table foo_history
(
    id integer default nextval('foo_history_id_seq'::regclass) not null
        constraint foo_history_pkey
            primary key,
    foo_id integer not null
        constraint foo_history_foo_id_fk_foo_id
            references foo
                on delete cascade,
    history_day integer not null,
    misc1 integer default 0 not null,
    misc2 numeric(10,2) default '0'::numeric not null,
    misc3 numeric(10,2) default '0'::numeric not null,
    misc4 numeric(10,2) default '0'::numeric not null,
    misc5 integer
);

Data and context:

I have a table foo, each foo row being attached to an entity. And each foo having it's own history. The goal is to get all foo items and their history that are related to a given entiy.
foo.entity_id contains 586972 distinct values
Here is a histogram representing how many entities have between 0 and 20 foo items related to them, etc.

Also I've noticed that the following query

SELECT COUNT(*) FROM (
  SELECT *
  FROM foo_history
    LEFT OUTER JOIN foo ON foo.id = foo_history.foo_id
  WHERE foo.entity_id = 11111
) t

uses the 2nd plan (the fast one)


texts: Query hint? for deduplicated CTE in Merge

texts:
Referring to this link: Optimizing MERGE Statement Performance

The following criteria are given for ideal performance:

Create an index on the join columns in the source table that is unique and covering.
Create a unique clustered index on the join columns in the target table.

I have many large tables (100+ million rows) that I merge 1-5 million rows into on a regular basis. There is a unique clustered index on the target table which matches the keys on the source table, satisfying the second criteria.

There is a bulk import process that populates the source table over time. I then place a non-unique clustered index on it in preparation for merging - this is because there is no way to ensure that duplicate values will not be inserted during the bulk process. This only sorta-half satisfies the first criteria.

I use a CTE with deduplication logic as the source for my merge -

...
USING
    ( SELECT
        cte.Key1,
        cte.Key2,
        cte.Key3,
        cte.RestOfTheColumns
      FROM
        ( SELECT
           Key1,
           Key2,
           Key3,
           RestOfTheColumns,
          ,ROW_NUMBER() OVER (PARTITION BY Key1, Key2, Key3 ORDER BY Key1, Key2, Key3) AS RowNumber
        ) cte
      WHERE cte.RowNumber = 1
    )
...

Ideally, I believe, this would already exist in a uniquely indexed table.

In summary:

I put a clustered index on the source then deduplicate it in-query.
In the query analyzer the Clustered Index Merge takes 91.9% of the plan cost.

Does it matter that the CTE/underlying table does not have an explicit unique constraint? There are no duplicates, but SQL doesn't know that, so I imagine it would check for every line in the upsert.


texts: SQL Server 2019 install - Python and R failing

texts:
I'm installing SQL 2019 Enterprise on the first node of a failover cluster.  Everything successfully installs except Python and R.  Error messages are as follows:
  Feature:                       Python
  Status:                        Failed
  Reason for failure:            An error occurred for a dependency of the feature causing the setup process for the feature to fail.
  Next Step:                     Use the following information to resolve the error, uninstall this feature, and then run the setup process again.
  Component name:                Machine Learning Services and Language Extensions
  Component error code:          -2147467261
  Error description:             Value cannot be null.  Parameter name: accountName
  Error help link:               https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=15.0.4013.40&EvtType=0xB05283BE%400x11428655&EvtType=0xB05283BE%400x11428655

  Feature:                       R
  Status:                        Failed
  Reason for failure:            An error occurred for a dependency of the feature causing the setup process for the feature to fail.
  Next Step:                     Use the following information to resolve the error, uninstall this feature, and then run the setup process again.
  Component name:                Machine Learning Services and Language Extensions
  Component error code:          -2147467261
  Error description:             Value cannot be null.  Parameter name: accountName
  Error help link:               https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=15.0.4013.40&EvtType=0xB05283BE%400x11428655&EvtType=0xB05283BE%400x11428655

Haven't found anything online about that error message with accountName specifically.  Of course the link provided in the error message redirects to generic Microsoft page.
I believe Python and R are the features that download the extra cab files during setup, and I usually see a "downloading file xxxxx.cab (8XX MB)" during installation for a while, but I'm not seeing it this time.  Not sure if that's related.
Windows firewall is turned off, and Anti-Virus is disabled (I think, may need to look more into this, but nothing in the AV logs about something getting blocked).
EDIT: The server does have internet access.
EDIT2: According to THIS link, machine learning services were not supported on 2017 failover clusters, but they are supported on 2019+ failover clusters.
Any ideas?


texts: No index used warning in WHERE column IS NULL


texts: Transform all columns records to lowercase

texts:
I'm using PostgreSQL 9.1 and I have a users table with a login column.

login names are case-sensitive, for example Bob, MikE, john.  I would like to transform all these records into lowercase.   How can I do that?


texts:
We're managing about 200 instances of postgres on Amazon RDS. The problem that we're constantly experiencing is that our "DBA" accounts can't access stuff. Basic stuff. Tables, views, etc.

Is there some kind of event-based automation, call it a trigger or so, that can handle this automatically? Manual grants are not an option - automation is the only possible solution.

Whenever a new database is created, a new schema is created, or anything and everything that is created - the "DBA" users are granted access to it.

What solutions are there for postgres 9.6 or 10?

Example use case:

RDS instance "dev1" has 10 postgres databases
So we go in and grant access to everything in those 10 databases to our dbauser
Next week, a new deployment takes place that creates an 11th database
Following that we get a request to export a table from that 11th database and our user doesn't have access to it.

We want to eliminate that.


texts: Delete every row that is not the first duplicate

texts:
I want to enforce uniqueness on a column, but in order to do so, I need to manually clean up a lot of duplicates. I have found out that all the duplicates have no references to them, so a simple sorting by resh id and then manually deleting every duplicate resh id apart from the first one with works (a la DELETE FROM org_units WHERE id in (3,4,5)), but it is boring, so of course I would like to create some kind of query to clean up this mess.
The data looks like this:
org_units
ID  | RESH_ID
-------------
1   |  a
2   |  b
3   |  a
4   |  a
5   |  b
6   |  c

A first step would perhaps to do some kind of ordering:
select id, resh_id from org_unit order by resh_id, id;

I am not sure how to go from here to deleting everything apart from the first row in each "group" (group by might be handy?).
I would like to end up with a situation like
ID  | RESH_ID
-------------
1   |  a
2   |  b
6   |  c


texts:
I have installed mysql and XAMPP on my system. I wanted to remove the mysql service of XAMPP. After removing it, I tried to install the new service for mysql using "MySQLInstanceConfig.exe". but now I can't see any of my databases or tables. Have I lost all my data? The data folder in mysql directory is full.

This is a picture of my data folder. I have all the data here. but mysql can not read them.

Is there any way to make the mysql read these !?


texts: Writing SQL injection proof dynamic DDL in Oracle

texts:
I have a need to write dynamic DDL statements such as CREATE USER ?. I know that I can use EXECUTE IMMEDIATE to do this, but so far I have found no way to incorporate dynamic parameters without simply concatenating strings, which leaves me open to SQL injection.

Coming from PostgreSQL I an used to being able to quote identifiers or using FORMAT to safely put identifiers into a formatted string that can then be executed. Is there anything like this in Oracle (particularly 12c)? If not, how does one perform such dynamic SQL safely?


texts:
How should I convert historic local time to UTC taking into account of daylight saving?

The main problem is the daylight saving doesn't occur at a fixed period every year and hence I cannot think of a way to do this task.


texts: How to Design Name and Address with Multiple Addresses

texts:
I have a names and addresses table:

 Name
 Address
 AddressType
 ID
 CustID

Sample:

 Bob Smith, 123 Main St, Corporate, 1, 1
 Bob Smith, 123 Main St, Home, 2, 1
 null, P.O. Box 123, Warehouse, 3, 1

and I have a customer table

 ID
 TaxID
 BusinessType
 etc

 1, 94-123456798, Consultant

The customer may have many addresses.

I need to query on specific address types and do CRUD operations.   For example, what is the corporate address?   Retrieve and edit the DBA and Corporate addresses.  Where is the inventory kept?

So my first inclination is to modify the customer table to add CorpAddressID, InventoryLocationID, etc.   There might be a slew of old and new addresses but certain addresses have to be designated clearly.

So then I thought I could just reference the address type -- Customer join Address where AddressType = Corporate -- I'm worried about doing this and I have no idea why.   Like having to hard code where AddressType = Corporate and relying on the Corporate designation being stored in the database.

I checked this out and it was similar but not close enough to help:

Data Warehouse Kimball Customer and Customer Address Dimensions

So, I ask, is there a compelling reason to not reference the name and address field IDs directly from fields such as CorpAddressID, DBAAddressID, etc?

Any thoughts or feedback would be greatly appreciated.


texts:
I'm trying to backup a database, which has been created by someone I don't have access to. I get this error message:

  The backup of the file or filegroup "fileStream" is not permitted, because it is not online.

and then it suggests to backup database via T-SQL to only backup online filegroups. However, this is not my requirement.

I want to bring the offline filegroup online again. I saw this answer which suggests using alter database command to take a fielgroup offline.

alter database dn_name
modify file (name='filegroup_name', offline)
go

However, as I saw the documentation of the mentioned command, there is no option to bring a fielgroup online back. What should I do? How can I bring a filegroup (or a file) back online in SQL Server 2012?


texts: Migrating triggers, functions and procedures from Oracle to PostgreSQL

texts:
We run a Java based application which uses Oracle as its backend. While they are rather simple and don't overly use Oracle specific functions, we have lots of triggers, function and procedures written in pl/sql.

Now we are forced to run the same application with a PostgreSQL backend too, this means we will run two instances of our code, one connecting to Oracle and the other to PostgreSQL.
We know that we will need to make some adjustments, but hope to keep them at a minimum.

Is it possible to transfer most of our triggers, functions and procedures to PostgreSQL ? I have had a look at the migration guide and heard rumors that PostgreSQL only supports C as a language for writing triggers and so forth.

To give you an idea of which complexity I am talking about:

CREATE OR REPLACE FUNCTION "STAGE"."NEST"
(X IN number, Y IN number, Z IN NUMBER)
RETURN number

AS

NL number;
BEGIN
  select count(*) into NL from ABC where foo_id = X
        and Y < A and Z > B order by Z;
  RETURN NL;
END;

And a trigger:

CREATE OR REPLACE TRIGGER "STAGE"."SOMETHING"
  BEFORE INSERT
  on  "CONFIG"
  for each row
begin
   SELECT CONF_ID_SEQ.NEXTVAL INTO :new.CONF_ID FROM DUAL;
END;


texts:
How can we use while loops in MySQL? My test script:

BEGIN
    SELECT 0 INTO @n;
    WHILE @n < 10 DO
    SELECT @n;
    SET @n := @n +1;
    END WHILE;
END;

But it has syntax errors. I'm running the loop using the SQLyog client in a standard query window. The syntax errors are of the following form:

  Error Code: 1064
  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near...

I've also tried to use the while loop example provided by https://dev.mysql.com/doc/refman/5.6/en/while.html but it still didn't work.

Which part of the script is wrong? (Using MySQL 5.6.)

I'm trying to make a 6 month moving average of some data so I was hoping that a while loop would be able to append the "new" 6 month average onto the "old" 6 month average through each iteration of the loop.


texts: Is an association class in UML the same as an Link entity in ERD?


texts:
I inherited a database server with SQL Server 2012 Enterprise Edition. All the databases (except for the system databases) are at the 90 compatibility level.

How do I justify updating them to 110 compatibility level?

Let me rephrase my question:

The server is upgraded to SQL 2012 but user databases' compatibility levels were not updated to 110.  So tempdb etc system dbs are 110 but user databases are still 90.  I would like to update them all to compatibility level 110.  I wish it's as simple as ALTER database compatibility level. :) But I need to  justify use of resources to thoroughly test the applications out.

Am I right to say that even though the system databases were in compatibility level 110 but the user databases are not reaping the benefit of SQL Server 2012?  Which means the databases were not really upgraded?

Thanks!


texts: Setting up a Postgresql Partition with foreign key

texts:
I have a Ubuntu 12.04 Server with PostgreSQL 9.1 installed.
I am trying to setup Partitioning on a existing Postgres DB(say 'xyz') which has very huge tables and some have foreign keys set on them.

This is what i did until now

I followed http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html to create Partition triggers.
Tested the Triggers, Each data i inserted got placed in the right partition.
Took take a pg_dump of all the tables individually from DB 'xyz'.
The option --column-inserts was enabled for each dump.
Created a new DB 'xyz_new'.
Imported Partition Triggers and Funtions i created into it.
Started importing tables with ones which were free of foreign keys.
Then i imported the ones with foreign keys, in a way that the tables referred to by the foreign keys are already imported.

But while doing the last step i get a error like

ERROR:  insert or update on table "aaaaa" violates foreign key constraint "bbbbb_id_refs_id_5b63fd27"
DETAIL:  Key (bbbbb_id)=(6817) is not present in table "bbbbb".
As a Result, no import happened on the foreign key based tables.

If i do a Select query on bbbbb for id 6817, the record does shows up.

Then after some googling i found this, http://www.postgresql.org/docs/9.1/static/ddl-inherit.html
As per the Caveats mentioned in the Wiki, the Inheritance doesnt work with the foreign key.
(That was a very bad news for me :( i had spent lots of time to create the triggers.)

Now, I would like to know if there is a workaround to manage Partitions on Postgres with foreign keys?
OR
Is there any other way where i can handle huge tables with foreign keys?


texts: Deadlock on SQL Agent job running IndexOptimize job from Ola Hallengren's script but still says job succeeded

texts:
Deadlock message from Idera SQLdm:

7/7/2017 4:24:02 AM, Deadlock on ServerName is Warning.

A deadlock occurred on server ServerName.  Full details are available in the SQLdm Desktop Client.

The deadlock victim was spid 86 with application name 'SQLCMD' by user 'SvcAgentAccount' on host 'ServerName'.

Last command issued:

EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @PageCountLevel = 500, @UpdateStatistics='ALL', @LogToTable = 'Y'

Deadlock: A deadlock occurred on the monitored server.  Note: The "Non-Query activities>Capture deadlocks (SQL 2005+)" must be enabled for this alert to function.

A deadlock occurred on server ServerName

Job history:

 Message
    Executed as user: ECS\SQLPROD02$. ...rsion: 10.50.6000.34  Edition:
Enterprise Edition (64-bit)  Procedure: [master].[dbo].[IndexOptimize]
Parameters: @Databases = 'USER_DATABASES', @FragmentationLow = NULL,
FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5
, @FragmentationLevel2 = 30, @PageCountLevel = 500
, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL
, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'N', @StatisticsSample = NULL
, @StatisticsResample = 'N', @PartitionLevel = 'Y'
, @MSShippedObjects = 'N', @Indexes = NULL, @TimeLimit = NULL
, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL
, @WaitAtLowPriorityAbortAfterWait = NULL, @AvailabilityGroups = NULL, @LockTimeout = NULL
, @LogToTable = 'Y', @Execute = 'Y'
Source: https://ola.hallengren.comDate and time: 2017-07-07 04:12:41
...<<REMOVED>>...  Process Exit Code 0.  The step succeeded.

Since the command running indexoptimize was a deadlock victim, I am wondering the job succeeded instead of an error!!


texts: Error using view to limit data access between databases in SQL Server

texts:
I am trying to limit user access to data through a view. I have a database full of replicated data that I do not want the users to be able to access, I also have a reporting database in which I will create views on the replicated data that the users can access.

e.g.
In database ReplData, I have a table refData(value int, source char(1)) with data values :
1, A
2, A
3, B
4, B

I want to use a view in a different database to limit users to just those rows with source = 'A'

So, in database Reporting, I have a view

use Reporting
go

create schema SourceA
go

create view SourceA.refData
as
  select * from ReplData.dbo.refData
   where source = 'A'
go

create user Steve for login Steve
go

grant select on SourceA.refData to [Steve]
go

However, when user 'Steve' connects to Reporting and tries to select from the view, he gets an error

Msg 916, Level 14, State 1
The server principal 'Steve' is not able to access the database 'ReplData' under the current security context

What have I missed?

[edit] The problem is not a select permission, it is a security context. If you can identify what I have missed, please do. I have turned cross database chaining on (see comments) but it has not resolved my issue.

[edit 2]
So the issue was the method I was using to test the view, which was to 'execute as user'. When I set up a new user and connected to SSMS using those credentials, then the view worked fine.


texts: Can't empty all my database in SQL Server

texts:
In my SQL Server I tried to truncate all tables, but there have some constraint.

So I running this

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" -- Disable all constraints for database
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

but still I get error as

  cant truncate table ... because it is being reference by a FK constraint

Why?


texts: What run-time configuration parameters are used for concurrent index creation

texts:
Suppose I were to run the following. The first line shows the server's default maintenance_work_mem value.

mhildreth=# show maintenance_work_mem;
 maintenance_work_mem
----------------------
 16MB
(1 row)

mhildreth=# set maintenance_work_mem = '256MB';
SET

mhildreth=# CREATE INDEX CONCURRENTLY ON my_table(columns);

mhildreth=# set maintenance_work_mem = DEFAULT;
SET

What would be used for maintenance_work_mem of the index creation?


texts: Unable to find Audit specification when I try to create Server level audit

texts: I am trying to use 'SERVER_PRINCIPAL_CHANGE_GROUP' audit action on a SQL Server to capture the sp_addlogin events. But when I try to create a Server level audit using GUI, I am not seeing the Audit action type drop down when I try to create the audit. Any help on how to do this ? I just want to capture who added a server level login using the SQL Server audits.


texts: Using EncryptByPassPhrase in where condition

texts:
I am using EncryptByPassPhrase of sql server. But i am not able to use where condition properly.

DECLARE @login_details TABLE(uid integer,username varchar(10),password varbinary(100))
insert into @login_details(uid,username,password) values(1,'abc',EncryptByPassPhrase('12','XXX'))
insert into @login_details(uid,username,password) values(1,'nvt',EncryptByPassPhrase('12','YYY'))
select * from @login_details --this give all rows from table with encrypted values--

select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) as password from @login_details --this gives decrypted values

But in below query i am using where condition but it does not work and result in 0 rows.Why?

select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) as password from @login_details
where password=EncryptByPassPhrase('12','YYY')

where as below query works fine.

select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) as password from @login_details
where convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password))='YYY'

Why there is a problem if i include encryptbyphrase command in where condition?


texts:
I have this table structure and am attempting to return the two rows where loglevel is null.  How should the query be written in order to return this?

Based off the sample DDL Below I want 2 rows for names returned:

  Pink Pig and Green Glove

  Declare @stuff Table
  (name varchar(50), vendorname varchar(100), vendorid varchar(10), loglevel varchar(10))
  Insert Into @stuff (name, vendorname, vendorid, loglevel) Values
  ('Pink Pig', 'Vendor 1', '214ac', NULL), ('Red Hat', 'Vendor 1', '214ac', '3')
  ,('Blue Sox', 'Vendor 1', '214ac', '3'), ('Green Glove', 'Vendor 1', '214ac', NULL)
  ,('Green Lantern', 'Vendor 2', 'Er23', '1'), ('Grey Goose', 'Vendor 2', 'Er23', '1')
  ,('Fox Fire', 'Vendor 2', 'Er23', NULL), ('Flyfish', 'Vendor 2', 'Er23', NULL)

  Declare @vendorid varchar(10), @loglevel varchar(10)
  Set @vendorid = '214ac';
  Set @loglevel = NULL

  Select * FROM @stuff
  WHERE vendorid = @vendorID
  AND loglevel = @loglevel


texts: Retrieve double data type value in non exponential form

texts:
Is it possible to retrieve non-exponential value in double data type column? After the width of the column is increased to 15 it shows the values in exponential form.

Now I get the following:

mysql> create table abc(a int,b double) engine innodb;
mysql> insert into abcd values(10,123456789123456789123456789);

mysql> select * from abc;
+------+-----------------------+
| a    | b                     |
+------+-----------------------+
|   10 | 1.2345678912345679e26 |
+------+-----------------------+

Instead of this I want it to be shown in the form what I entered as 123456789123456789123456789 on mysql client.


texts: List users permissions to tables and schemas on MSSQL 2016

texts:
List all permissions for a given role? has a nice query for listing users and their roles on a DB.

How could we list all tables and users with their permissions on these tables?


texts: Integrating MongoDB metrices with Cloud watch

texts:
We are hosting mongodb in AWS environment and planning to create incident in Servicenow using AWS Cloud watch alarm facility

Has anyone integrated MongoDB Monitoring metrices with Cloud watch to create Incident ?

As i am completely new to AWS, i got stuck with the integration of mongodb metrices with cloud watch

Any link/lead would be highly appreciated.

Thanks,


texts: Can I run a mongos without any config servers?

texts:
I just want to use mongos as a tool to scale up the number of connections I can open to mongo. So can I run the mongos without any config servers?

Has any one done this before?


texts:
Maybe this question has been asked already (it should
be quite simple). I've Googled loads of phrases
(PIVOT, ROWS to COLUMNS, TRANSPOSE &c.) and all I get is
TSQL this, TSQL that and or Oracle this, Oracle
that, proprietary function this, proprietary function that well, you get the picture.

I have a simple table structure - if it was any simpler, it'd
be a list :-) - i.e. it's two columns.

stock_date       sku
----------       ---
2016-06-01         0
2016-06-02       123
2016-06-03       123
2016-06-04       123
2016-06-06       123
2016-06-07       123
2016-06-03       245
<... &c...>

There may be many thousands of sku's but the date range
will be at most a month or so, but can vary, so the code needs to cater for that.

So, I want to make the stock_date field a column.

sku     1   2   3   4   5   6   7
---     -   -   -   -   -   -   -
123     0   Y   Y   Y   0   Y   Y
245     0   0   Y.... &c.

Ideally, the code would be dynamic and would work with any server (even MySQL).

But if it has to use common table expressions and/or window functions, that's
OK but not proprietary crosstab or pivot functions.


texts: Ora 01017 Invalid Username Password; with correct data

texts:
I am new to oracle (enterprise:12.2.0.1) and after installation I logged in with connect sys as sysdba.
I also created a new user with:
 alter session set "_ORACLE_SCRIPT"=true;
 create user dummy identified by dummy;
 GRANT ALL PRIVILEGES TO dummy;

Everything went successful until now, but when I tried to log in with the new created user with 'connect dummy' I get the error: Ora 01017 Invalid Username Password; logon denied
After resetting the password 10 times and creating 8 new users, I am nearly 100% sure, the password and username is correct.
I read, that this problem can also appear because of the tnsnames.ora file, but I don't try to connect with TNS, so I would rule this case.
After many hours, I would now be happy, if I can get here a simple step-by-step solution and explanation.
Thank you very much and greetings from Germany.


texts:
I run a PHP/MySQL website on a fairly large server with 16GB RAM and 3 vCPUs.

Everything was flying along until this morning when I noticed MySQL down.

I tried to restart MySQL and first got the too many connections error.

I didn't know why it was showing this, So killed MySQL processes and restarted the database after changing my.cnf to 500 Max connections.

However, MySQL is still very slow even after running optimize.

Any hints about what is happening, or how to troubleshoot this?


texts: How to determine database read/write ratio on MongoDB MMS?

texts:
By looking at MongoDB MMS charts, is there a way to calculate a database read/write ratio, or roughly determine whether a database is read-heavy or write-heavy?

Assume I do not have contact with developer and therefore has no idea on database's schema or use case.

Edit: I understand MMS metrics are based on entire databases on the single mongod instance. However, assume currently there is only one database exist.

Example MMS chart: