texts:
Working on setting an automated restore of dbs from Production to the test/dev environments using Net App SQL Snap Manager. The prod job handles the kick off of the restore, nothing being ran on the test/dev sql agent side. Not really familiar with the Net App process either, being handled by storage team. My question is, without using linked servers, is it possible to kick off a SQL agent job on the test/dev box remotely from the prod box? Would powershell give me the functionality I'm looking for? Or would linked servers be the better way to go?

Thanks


texts:
I am using the following guide to migrate a database from my Windows 10 machine to my Managed Database on Digital Ocean.
https://www.digitalocean.com/docs/databases/postgresql/how-to/import-databases/
Unfortunately, I am having some issues restoring my database from local to the remote server.  I am using the following command to perform my local dump:
pg_dump -U username -Fc dbname > filelocation.pgsql

This prompts for my password. Upon entering the password, pg_dump executes, and I am greeted with the following warning multiple times:
pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used

Following this, I attempt the pg_restore using the following command:
pg_restore -d 'my_connection_URI' filelocation.pgsql

However, I am greeted by the error:
pg_restore: [archiver] input file does not appear to be a valid archive

I believe that by specifying -Fc it should be a valid archive, so I can only presume that it has something to with the warnings I received, although I can't be sure.
Does anyone have any ideas of how to fix this?


texts: Importance of uniformity of hardware for Galera cluster

texts:
How important is it for the nodes in a Galera cluster to be identical?

I've found this answer which says they should be the same:
https://dba.stackexchange.com/a/55224/770

I understand that you should not have a node that is really slow compared to the others, but if the nodes use the same disks, the same amount of RAM and similar CPUs, shouldn't that be good enough?


texts: Query Efficiency - Filter people when name and surname are different columns, and string may appear in any of them

texts:
I am working with a table representing real people data, with a schema like this (MySQL Server):

CREATE TABLE Users (
   id INTEGER PRIMARY KEY,
   name NVARCHAR(32) NOT NULL,
   surname1 NVARCHAR(32) NOT NULL,
   surname2 NVARCHAR(32),
   (more columns irrelevant to this problem)
);

From this table I need to be able to search people by name. As you can see, I have the name divided on three columns (the second surname is for accounting for Spaniards and other countries where people can have two surnames).

In lots of searches I found around the web, they usually let you put a keyword, and people with that keyword as name OR as a surname would both be retrieved. That's what I did:

SELECT * FROM Users WHERE
   (name LIKE '%keyword1%' OR surname1 LIKE '%keyword1%' OR surname2 LIKE '%keyword1%')
   AND (...same with the rest of the keywords)

It works as intended. If I send, for example, 'Mark Paul' I will get any people named 'Mark Paul' or 'Paul Mark', or 'Paul Markusson'.

However, someone pointed out to me that this query may start to take its sweet time once we start to have a respectable number of rows on the User table.

Honestly, I don't know what kind of optimization I should use for this type of query. I think MySQL can't use indexes when using LIKE comparisons starting by wildcards.

Do any of you more experienced people have an idea of what I can do or should I give up and make the app user have to search by each column separately?


texts: Find max and min date over multiple fields of the same record

texts:
I have a table with 4 distinct data-type fields. For each record, I need to find the max and min date over them.

That's easy to do for a set of records over a unique field. But how to do it in my case, without using cursor?

I'd need some sort of function that receives a list of fields, like max(field1, field2, field3, field4).


texts:
We recently migrated a large chunk of our databases to a new data center and on one of our clusters we are now seeing high amounts of waits for HADR_SYNCHRONIZING_THROTTLE. We didn't see this in our previous data center.
The only thing I have noticed since the migration are errors related to Change Tracking that is enabled on two of the databases. These errors look like:

Error: 77735, Severity: 16, State: 1. Change Tracking cleanup job is
running in DB1 for 1475 minutes more than the threshold(24hrs); DB2
for 3379 minutes more than the threshold(24hrs)

It looks like I'll have to disable and re-enable CT to clear this message so possibly this might be what is causing the waits mentioned.
Has anyone seen HADR_SYNCHRONIZING_THROTTLE waits before and got to the bottom of why they are appearing?
In terms of our environment we are currently running SQL Server 2012 11.0.7462.6.
Thanks in advance.


texts:
I need to get the posts closest to a date.

SELECT id
     , datetime
  FROM dates
 WHERE id != 150003
   AND datetime > FROM_UNIXTIME(1549622615)
 ORDER BY datetime
 LIMIT 1

Returns a correct result:

{"id":"150015","datetime":"2019-04-13 17:47:55"}]

but when I change to "datetime < FROM_UNIXTIME(1549622615)" I get the result:

{"id":"1","datetime":"1970-01-10 12:06:07"}

when there are many posts closer to the datetime.

Any insights why?


texts: Is there a way in SQL Server 2008 R2 to cause a timeout for a database modification involving a transaction? We have a scenario where our application code hangs or throws an exception and fails to perform a rollback or commit. This then causes other sessions to hang waiting for the transaction to complete.


texts: Set MultiSubnetfalilver=Yes to linked servers in standalone SQL Server 2016 implications

texts:
I am using 1 node SQL Server 2016, having linked servers using MSOLEDBSQL provider and Provider string "MultiSubnetFailover=Yes".
Are there any implications/negative impacts, if i use "MultiSubnetFailover=Yes" to create linked servers in standalone SQL Server 2016.
OR "MultiSubnetFailover=Yes" should only be used in Alwayson setup.


texts:
I have a bunch of tables with many columns of data type ntext. I wish to change all such columns in all tables of a particular database to nvarchar(max), because of planned deprecation (EDIT: also because I can't use such columns with DISTINCT, GROUP BY etc.). Is it possible to accomplish this without using ALTER TABLE with ALTER COLUMN for each table manually?

I am using MS SQL Server 2008.


texts: Full text search on two tsvector columns

texts:
I'm trying to do a full text search across two columns of two separate tables in PostgreSQL 9.4.2. The tables and columns are:

article.article_title
keyword.keyword

Where there is a many-to-many relationship between the two. I think I've got most of the way following this blog post. However, I am struggling to formulate the query across the two columns. This query is returning results:
SELECT distinct title, pub_date, web_id, city_points, city_name FROM (
  SELECT article.article_title as title,
  article.pub_date as pub_date,
  article.web_id as web_id,
  string_agg(st_asText(city.geom), ', ') as city_points,
  string_agg(city.city, ', ') as city_name,
  article.title_tsv ||
  keyword.keyword_tsv
  as document
  from article, article_keywords, keyword, article_cities, city
  where article.id = article_keywords.article_id
  and keyword.id = article_keywords.keyword_id
  and article.id = article_cities.article_id
  and city.id = article_cities.city_id
  GROUP BY article.id, keyword.id) p_search
WHERE p_search.document @@ to_tsquery('putin');

But I think the query suffers many of the same deficiencies from a previous question I asked here.
I am also trying to return the following columns in the query:

article.pub_date
article.web_id
city.city_points
city.city_name

I am pretty new to full text search - and SQL in general.
Let me know if you need any more information.


texts: Archiving data from logging db (high writes, no reads)

texts:
I have a logging database with a few lookup tables and one massive event table that has 79 million rows. On busy days my code inserts over 3 million rows, so the event table would be enormous in production if not for the fact that every half hour I copy the latest data to an archive database on a separate server and then every night delete all rows more than one month old from production.

The problem is that these deletes are now taking over 10 hours to run. I don't think they cause any blocking as long as I put 100 ms delay between each delete statement (script below), but I feel like things could be improved. Should the logging db be in snapshot isolation mode? We typically write the row and then update the EndTime column after the function has completed.  Any suggestions on how to improve the process and/or speed up production would be appreciated.

    DECLARE @CutoffDate datetime = DATEADD(DAY, -30, GETDATE()), @Time datetime, @Stop bit = 0, @RowCount int

WHILE (@Stop = 0)
BEGIN
    SET @Time = GETDATE()

    DELETE TOP (100) af
    FROM ApplicationEvent ae
    JOIN ApplicationFault af ON af.ApplicationEventID = ae.ApplicationEventID
    WHERE LogTime < @CutoffDate

    DELETE TOP (100) ae
    FROM ApplicationEvent ae
    LEFT JOIN ApplicationFault af ON af.ApplicationEventID = ae.ApplicationEventID
    WHERE LogTime < @CutoffDate AND af.ApplicationFaultID IS NULL

    SELECT @RowCount = @@ROWCOUNT
    SELECT @Stop = CASE @RowCount WHEN 0 THEN 1 ELSE 0 END

    --PRINT DATEDIFF(MS, GETDATE(), @Time)

    WAITFOR DELAY '00:00:00:100';
END

Here's the schema.

CREATE TABLE [dbo].[ApplicationEvent](
    [ApplicationEventID] [int] IDENTITY(1,1) NOT NULL,
    [LogTime] [datetime] NOT NULL,
    [TransactionID] [int] NULL,
    [TransactionType] [int] NULL,
    [EventID] [int] NOT NULL,
    [EventLevelID] [smallint] NOT NULL,
    [StartTime] [datetime] NOT NULL,
    [EndTime] [datetime] NULL,
    [IPAddress] nvarchar NULL,
    [UserName] nvarchar NULL,
    [ApplicationID] [smallint] NOT NULL,
    [EventInformation] nvarchar NULL,
    [HostName] nvarchar NULL,
    [ApplicationSourceId] [smallint] NULL,
 CONSTRAINT [PK_ApplicationEvent_1] PRIMARY KEY CLUSTERED
(
    [ApplicationEventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


texts: Can someone please explain diff between different 'Priority' options viz. High, Medium, Low and No Auto Start ?


texts: Can I create a second filestream container on an existing SQL Server 2008 database without going offline?

texts: Our production DB is using the FILESTREAM option for our sharepoint environment. The volume where the files are residing is getting full and I am planning to add another volume to ease space issues. Is the solution as simple as creating another filestream container and adding this container on the existing filestream filegroup? Would SQLServer take care of balancing among the two volumes?


texts: Is MySQL Replication appropriate for keeping a laptop in sync?

texts:
I've setup Mysql master slave replication with the production server as master. I have three other machines as slaves: 1 laptop, 1 desktop, and 1 server. The desktop and laptop both get suspended to ram or hibernated at least daily, if not several times a day. At first I setup the default 'mixed' replication, which is basically statement-based, until I realized that the computations on the server that involved numerous temporary tables were not getting picked up by the two slaves (the slaves would error out when then couldn't find the temporary tables). The slave server works perfectly, because it's always on.

SO, I switched to row-based replication, thinking that would work better. However, I'm still getting frequent errors after the machines are woken up, the most recent of which is:

Could not execute Update_rows event on table database.tablename; Can't find record in    'tablename', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;

I can use the 'SET GLOBAL_SQL_SKIP_COUNTER=1' trick to bypass the error and continue, but I'm afraid the slaves will get more and more out of sync if I setup that to happen automatically after any error. FYI, the slaves are just used for development and never get written to.

Is this not an appropriate setup for replication usage? It sounded from the documentation that network availability would not be an issue (other than the temporary tables problem, which row-based replication should work around).

Anyone else have experience with this kind of setup? I can provide configurations if it would be useful. I can do a daily import of a mysqldump gz file, but it seems like a clunkier solution, especially as the production server data is growing every day...

Thanks!
Scott


texts:
I have a table of pigeon data.
pigeon_id = ID of pigeon
date_time = time of signal_event
pigeon_station = id of pigeon station
signal_strength = signal strength of pigeon

    CREATE TABLE `pigeon_signal` (
  `pigeon_id` varchar(20) NOT NULL,
  `date_time` int(11) DEFAULT NULL,
  `signal_strength` decimal(10,0) NOT NULL,
  `pigeon_station` varchar(20) DEFAULT NULL,
  KEY `index1` (`pigeon_id`,`pigeon_station`),
  KEY `index2` (`pigeon_id`,`date_time`,`pigeon_station`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Table count: 500,000 rows

Each pigeon has a transmitter. Every few seconds the pigeon station records the signal strength of all the surrounding pigeons.

I want to query the first time a pigeon station sees a pigeon. Here is my sql. It is slow but it returns the correct results:

select ps2.* from pigeon_signal as ps join
 (select MIN(date_time) as date_time, pigeon_id, pigeon_station from pigeon_signal group by pigeon_id, pigeon_station) ps2
 on ps.date_time = ps2.date_time and ps.pigeon_id = ps2.pigeon_id and
 ps.pigeon_station = ps2.pigeon_station

EXPLAIN EXTENDED:

The query takes about 2 seconds to run (too long).
Is there a better way of querying this data?
I would appreciate any tips/hints/advice related to this problem.

Many thanks :D

UPDATE:
Thanks for the answers!! :D
I have changed the pigeon_id column to a varchar(20).

@ypercubeᵀᴹ I changed the index to what you suggested and it is the fastest (thanks!!!)  But why?

distinct pigeon_id:      25306
distinct pigeon_station: 36
distinct date_time:      452322

My thinking: Order the index attributes by the number of distinct elems ascending. Results:
1. pigeon_station,pigeon_id, date_time:  1.8 seconds
2. pigeon_id, pigeon_station, date_time: 0.4 seconds
3. pigeon_id,date_time,pigeon_station:   1.8 seconds
4. date_time, pigeon_id, pigeon_station: 1.9 seconds

Why is option 2 faster than option 1? As there are less distinct pigeon_station values than pigeon_id.
But if the optimal index should order the attributes by the number of distinct values descending then why isn't option 4 the fastest?

Many thanks for your time


texts: The provided statistics stream is corrupt, when running UPDATE Statistics

texts:
When running
    UPDATE STATISTICS mytable

I'm getting following error:
The provided statistics stream is corrupt.

When our application is inserting rows to mytable we are getting the same error.

However
DBCC CHECKDB
Doesn't give any errors.

Any ideas on how to fix this issue?

We are running Microsoft SQL Server Express Edition with Advanced Services 2008 R2


texts:
I am getting below error while restoring Postgres instance to a point in time.

FATAL:  requested recovery stop point is before consistent recovery point

below is my recovery.conf content

restore_command = 'cp /var/lib/pgsql/pg_log_archive/%f %p'
recovery_target_time = '2018-04-17 10:56:27'
recovery_target_inclusive = false

I want to restore postgreSQL instance to a specified time.What am I doing wrong here?


texts: SQL Server Logical IO Block Sizes and Physical Reads

texts:
I am trying to understand cache churn caused by SQL statements that are doing some table scans.

Assuming LRU caches, if something is doing 400K logical IOs, how much memory is streamed across (IO block size * logical IOs).

Also, I am trying to understand how to take the Report on top Logical IO SQL statements and get the physical IO that these incurred.

Given that, my questions are:

What is the IO block size that a SQL Server logical IO operation refers to.
How do I get Physical IOs for a particular instance of SQL execution?


texts: Update Statement on one table takes locks on other tables

texts:
Given the following statement on the AdventureWorks database:

UPDATE Sales.SalesOrderDetail SET OrderQty = 100 WHERE ModifiedDate >= '2014-01-01'

If I open another query window and look at the locks the query is using by running

sp_WhoIsActive @get_locks =1

i can see locks as follows:

   <Object name="Person" schema_name="Person">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="SalesOrderDetail" schema_name="Sales">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="SalesOrderHeader" schema_name="Sales">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>

I was expecting the table lock on SalesOrderDetail however, I don't understand why the table lock has been taken on the Person Table and the SalesOrderHeader table

I can confirm the locks are present by trying to SELECT * from one of the tables which is then blocked.

Why are tables that are not being updated locked by the update statement?


texts: Update rows in table with set-returning function

texts:
I am trying to update a table of 100 already existing rows with 100 random values from a Gaussian distribution, for which I am using the normal_rand function from the tablefunc extension. Since this is a set-returning function I placed it it the FROM-clause.

UPDATE foo
SET bar = r
FROM normal_rand(100, 0, 1) AS r;

However, when executing the statement, all rows in the table are assigned the same value (presumably the first), as such:

bar
-------
0.2451
0.2451
0.2451
...
0.2451

I also tried generate_series, but this yields similar results. How can I assign the first value of normal_rand(100, 0, 1) to the first row, the second to the second etc.?


texts: MySQL table partition strange behavior (slow query suddenly)

texts:
(I asked this question in "stack overflow" and was recommended to ask in here).

(MySQL version: 5.6.15)

I have a huge table (Table_A) with 10M rows, in entity-attribute-value model.
It has a compound unique key [Field_A + Element + DataTime].

CREATE TABLE TABLE_A
(
  `Field_A` varchar(5) NOT NULL,
  `Element` varchar(5) NOT NULL,
  `DataTime` datetime NOT NULL,
  `Value` decimal(10,2) DEFAULT NULL,
  UNIQUE KEY `A_ELE_TIME` (`Field_A`,`Element`,`DataTime`),
  KEY `DATATIME` (`DataTime`),
  KEY `ELEID` (`ELEID`),
  KEY `ELE_TIME` (`ELEID`,`DataTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Rows are inserted/updated to the table every minutes, hence the row size of each [DataTime] (i.e. every minute) is regular, around 3K rows.

I have a "select" query from this table, after the above "inserted/updated".
The query selects one specified elements within most recent 25 hours (around 30K rows). This query usually processes within 3 sec.

SELECT
    Field_A, Element, DataTime, `Value`
FROM
    Table_A
WHERE
    Element="XX"
    AND DataTime between [time] and [time].

The original housekeeping would be remove any row after 3 days, every 5 minutes.

For better housekeeping, I try to partition the table base on [DataTime], every 6 hours. (00,06,12,18 local time)

PARTITION BY RANGE (TO_DAYS(DataTime)*100+hour(DataTime))
(PARTITION p2014103112 VALUES LESS THAN (73590212) ENGINE = InnoDB,
...
PARTITION p2014110506 VALUES LESS THAN (73590706) ENGINE = InnoDB,
PARTITION pFuture VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

My housekeeping script will drop the expired partition then create a new one

ALTER TABLE TABLE_A REORGANIZE PARTITION pFuture INTO (
  PARTITION [new_partition_name] VALUES LESS THAN ([bound_value]),
  PARTITION pFuture VALUES LESS THAN MAXVALUE
)

The new process seems running smoothly.

However, the SELECT query would slow down suddenly (> 100 sec).

The query is still slow even all process stopped. It won't be fixed until "analyzing partitions" (reads and stores the key distributions for partitions).

It usually happens ones a day.

It does not happen to a non-partitioned table.

Therefore, we think it is caused by corrupted indexing in a partitioned MySQL (huge) table.

Does anyone have any idea on how to solve it?

Many Thanks!!


texts:
I want to count two columns, but need to get the results of first column in one row.

SELECT country, COUNT(*)
FROM table1
GROUP BY country, type

This query gives me

country    type     COUNT(*)
Canada     first    22
Canada     second   42
Canada     third    15
Australia  second   23
Australia  third    18

but I need to get

country    type_first   type_second   type_third
Canada     22           42            15
Australia  23           18            0

as I want to update another table with these values, and with this row structure, I can update the country table row by row as I get from the above query.

UPDATE country SET first=x, second=x, third=x

NOTE: type column is ENUM with predefined values.


texts: Which one is more efficient: select from linked server or insert into linked server?

texts:
Suppose I have to export data from one server to another (through linked servers). Which statement will be more efficient?

Executing in source server:

INSERT INTO [DestinationLinkedServer].[DestinationDB].[dbo].[Table]
SELECT a, b, c, ... FROM [dbo].Udf_GetExportData()

Or executing in target server:

INSERT INTO [dbo].[Table]
SELECT a, b, c, ...
FROM OPENQUERY([OriginLinkedServer],
    'SELECT a, b, c, ... FROM [OriginDB].[dbo].Udf_GetExportData()')

Which one will be faster and consume fewer resourcers in total (both source and target server)? Both servers are SQL Server 2005.


texts: Can I have more than one relationship between two tables?

texts:
I want to make database for report maintenance.

I have 3 tables:

employees
header_maintenance_report
detail_maintenance_report

employees table I have employee_id and then in the header_maintenance_reporttable I use the employee_id ( Foreign Key ) as report maintenance maker.

Can I use another employee_id in the detail_maintenance_report table?

I use employee id in detail_maintenance_report table as team maintenance from report maintenance maker.

Every maintenance requires a lot of employees so I use detail_maintenance_report.


texts: Reference column alias in same SELECT list

texts:
I'm converting an old MS-Access-based system to PostgreSQL. In Access, fields that were made up in SELECTs could be used as parts of equations for later fields, like this:

SELECT
    samples.id,
    samples.wet_weight / samples.dry_weight - 1 AS percent_water,
    100 * percent_water AS percent_water_100
FROM samples;

When I do this in PostgreSQL, Postgres throws an error:

  ERROR: column "percent_water" does not exist.

Here's how I can work around it, by selecting out of a sub-selection:

SELECT
    s1.id,
    s1.percent_water,
    100 * s1.percent_water AS percent_water_100
FROM (
    SELECT
        samples.id,
        samples.wet_weight / samples.dry_weight - 1 AS percent_water
    FROM samples
    ) s1;

Is there any kind of shortcut like in the first code block to get around complicated nesting? I could also just say 100 * (samples.wet_weight / samples.dry_weight - 1) AS percent_water_100, but this is just a small example out of what is a much larger system of math going on in my code, with dozens of more complex bits of math stacked on top of each other. I'd prefer to do as cleanly as possible without repeating myself.


texts: sys.dm_db_index_physical_stats is extremly slow

texts: I have a database that is around 4.5TB, since we have parallel inserts (to reduce daily load time) on one table (partitioned by month) the clustered index on this table tends to be heavily fragmented. When I do a select from sys.dm_db_index_physical_stats (Limited) on this table it takes ages (> 4-5 hours). Is there a faster and better way to check the fragmentation levels on the partitions of this table, the current time this takes is completely inacceptable.


texts:
So, according to this article, low-cardinality indexes may negatively impact performance:
http://www.ibm.com/developerworks/data/library/techarticle/dm-1309cardinal/

Let's say you have 15-20 yes/no flags you want to be able to search on in any combination, with a kicker of having the final column of the index be your SORT BY column of high-cardinality values. Let's say we have 5 or so different columns we want to be able to sort on. We want our queries to always use an index without having to do any extra table scans or use temporary tables or do any extra sorting.

To create all of these indexes, it would require a permutation of all of these combinations - you're gonna have a lot of indexes to update. And it's possible it's going to be very inefficient if I understand the above article correctly.

Is it possible to consolidate all of your flags into one column using some kind of bitmasking technique? I'm vaguely familiar with the concept but pretty clueless when it comes to the execution.

P.S. If it makes a difference, I'm using MySQL.


texts: Clickhouse create database structure for json data

texts:
New to clickhouse and stuck on the database creation structure for importing json data which is nested

Take for example the json data that looks like the following

when there is data populated

"FirewallMatchesActions": [
    "allow"
  ],
  "FirewallMatchesRuleIDs": [
    "1234abc"
  ],
  "FirewallMatchesSources": [
    "firewallRules"
  ],

or

"FirewallMatchesActions": [
    "allow",
    "block"
  ],
  "FirewallMatchesRuleIDs": [
    "1234abc",
    "1235abb"
  ],
  "FirewallMatchesSources": [
    "firewallRules"
  ],

but there maybe json data which doesn't have them populated

  "FirewallMatchesActions": [],
  "FirewallMatchesRuleIDs": [],
  "FirewallMatchesSources": [],

what would the clickhouse create database structure look like ?


texts: Multiple Instances in AlwaysOn Availability Groups - Resource Balancing

texts:
I have 2 SQL 2017 Instances on each node in a 2 node cluster with a file share witness. 8 cores and 64GB RAM on each.
Node A acts as the primary for Instance 1 and Node B acts as the primary for Instance 2 . (Synchronous)
Node B acts as the secondary for Instance 1 and Node A acts as the secondary for instance 2. (Synchronous)
I am wondering what the right balance of resources (Max DOP and Max Memory) would be in the ideal scenario? Would I be shooting myself in the foot by setting maxdop = 8 on the primaries? What is advisable for Maxdop on the secondaries in this situation?
Likewise, generally how much max memory should I set for the primary/secondary? (Obviously leave some for OS as well)
I am already working on a solution to re-balance the resources in the event of a failover, so no need to account for that in your reply. Unfortunately, this is for a Production build where I am unable to replicate the amount of typical load, so any generalities are welcome.
Thanks!!


texts: CHECKPOINT stealing connection in SINGLE_USER mode

texts:
I have a console application that was written to automatically run several long running scripts.  Before running the scripts, it will take a database backup by using the BACKUP DATABASE command.  It will then place the database in single user mode:

ALTER DATABASE databaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

The application will then begin running the sql scripts.  This part all works great.  The problem that I am having is if a script fails, we are doing a restore from the backup taken before beginning the scripts.  The reason for this is because a transaction rollback will take far longer than simply doing a restore.

I've tried a few different ways:

-works but periodically gets its connections stolen

USE master RESTORE DATABASE databaseName FROM DISK=N'fileLocation'

-doesn't work, database already in use

EXEC master..sp_executesql N'RESTORE DATABASE databaseName FROM DISK=N''fileLocation'''

I've also tried using the .ChangeDatabase() method of the SqlConnection object (VB.NET).  This behaves much the same as the first script above in that it periodically will get it's connection stolen.

In code I am taking pains to ensure I never drop/close my connection and watching the Activity Monitor, it appears that the CHECKPOINT process is what is stealing my connection.

The errors I'm seeing are these:

Database 'databaseName' is already open and can only have one user at a time.

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

Cannot open database "databaseName" requested by the login. The login failed.

Is there a way to keep CHECKPOINT from stealing my connection?


texts: High Log Flush Wait Time and filling Transaction logs

texts:
I have problem with Log Flush Wait Time - as of now, they are averaging at 30000ms (min 0 max 35000); database itself is performing poorly, transaction logs are also growing beyond reasonable description. Log files have to be shrunk to keep the size at bear, not filling up whole disk drive with log files.  I checked DB waits and they are in general LCK_M_S, also OLEDB. Database itself lives on SQL 2016 Always-on.

I still believe that there may be some SQL server issues with settings - I can also see that there is a lot of SQL Memory pressure (30000 free stalls, ~900000000 page reads, 30000 page life exp.) - server has 32GB RAM, hosting ~80 databases.

I know the application is the probably main reason for this but still, any suggestions on proceedings?


texts:
I have a table with the following entries:

customer_ID
lastDate
Sum

1
'2012-04-30'
5

1
'2012-06-30'
4

1
'2013-07-31'
25

2
'2012-04-30'
7

2
'2012-05-31'
4

2
'2012-06-30'
1

2
'2012-07-31'
6

I need to add missing month and the date which gets added should be the last date of that month with frequency value as 0.
The expected output is:

customer_ID
lastDate
Sum

1
2012-04-30
5

1
2012-05-31
0

1
2012-06-30
4

1
2012-07-31
0

1
2012-08-31
0

1
2012-09-30
0

1
2012-10-31
0

1
2012-11-30
0

1
2012-12-31
0

1
2013-01-31
0

1
2013-02-28
0

1
2013-03-31
0

1
2013-04-30
0

1
2013-05-31
0

1
2013-06-30
0

1
2013-07-31
25

2
2012-04-30
7

2
2012-05-31
4

2
2012-06-30
1

2
2012-07-31
6

I need to add missing month and the date which gets added should be the last date of that.
We can achieve this using CTE method. But can a similar result be obtained using joins?
Solution through CTE:
with cte as (
      select id, date, frequency,
             lead(date) over (partition by id order by date) as next_date
      from t
      union all
      select id, eomonth(date, 1), 0, next_date
      from cte
      where eomonth(date, 1) < dateadd(day, -1, next_date)
     )
select id, date, frequency
from cte
order by id, date;

I am trying to build a data pipeline and when I run this on that editor, it does not support recursive CTEs. I have no options.


texts: Changing the Parametization option in SQL Server 2012 from Simple to Forced

texts: Does changing the Parametization option in SQL Server 2012 from Simple to Forced clear the cache; or should DBCC FREEPROCCACHE be run after the change?


texts: sqlcmd returns Msg 701 when importing +10MB queries

texts:
I have a powershell script that exports data from DBF files, saves them as .SQL files and then imports the SQL files into MS SQL 2016 using the sqlcmd command.

There's one SQL file per table in my SQL database.
The SQL file drops the table, recreates it and inserts the data into the table.

Most SQL files are small, however, I have 3 files that are 14MB, 26MB and 30MB.
Importing these files fails with a memory error:

  Msg 701, Level 17, State 123, Server SQL\INSTANCE02, Line -22204 There
  is insufficient system memory in resource pool 'internal' to run this
  query.

All SQL files have rather short insert statements. I.e. the 30MB file has 150610 insert statements like this one.

insert into [RITORDER] values(1668556,'   ',7452,6811,0,'F8429E4K4W',63267840,63269279,0,' ',0,0,'010203              ','',63267840,63269279,0,0,0,'      ',0,'               ',0,0,'','               ');

On SQL\INSTANCE02 I had the Maximum server memory set to 5000MB. So I have increased this to 8000MB.

After upgrading the Maximum Server Memory with ~3GB, the 10MB file gets imported, while the other two "larger files" still fail with the 701 message.

Since SQL\INSTANCE02 contains a couple of small database, I don't feel like upgrading the Maximum Server Memory setting for this instance any more.

Is there a way I can tweak the command so it can also run the 3 "larger" queries?

The command I use is:

sqlcmd -S 'SQL\INSTANCE02' -U 'user' -P 'thisisnotmypassword' -d 'dbname' -i $ExportDir\$($file[1])

Or should I use a whole different method instead?


texts: How to write "and" queries for a lookup table when you can't use the AND keyword?

texts:
I'm building a practice website that allows users to search hotels based off of the amenities that the hotel provides. From the FE, the user will select a checkbox for however many amenities they want, and the amenity keys will be sent to the backend. On the backend, I've got these three tables:
          hotels
| id | name         | vacancies
| ---| ------------ | -------- |
|   1| Marriott     |        0 |
|   2| Best Western |       10 |
|   3| Sheraton     |        3 |
 ------------------------------

          amenities
| id | name                  | key                   |
| ---| --------------------- | --------------------- |
|   1| Cafe                  | cafe                  |
|   2| Wheelchair Accessible | wheelchair_accessible |
|   3| Wifi                  | wifi                  |
 ----------------------------------------------------

    hotels_amenities_lookup
| id | amenity_id | hotel_id |
| ---| ---------- | -------- |
|   1|          1 |        3 |
|   2|          2 |        1 |
|   3|          2 |        2 |
|   4|          2 |        3 |
|   5|          3 |        2 |
|   6|          3 |        1 |
 ----------------------------

To search for one amenity, such as wheelchair_accessible, I would do something like this:
WITH hotels_with_amenity as (
    SELECT ha.hotel_id
      FROM hotels_amenities_lookup ha
      JOIN (
            SELECT id from amenities a
            WHERE  a.key = 'wheelchair_accessible'
        ) amenity ON ha.amenity_id = amenity.id
)

SELECT  h.name,
        h.vacancies
   FROM hotels h, hotels_with_amenity hwa
  WHERE h.id = hwa.hotel_id;

Returns all three hotels.
The question is: if the user selects multiple amenities, wheelchair_accessible and wifi for example, how would I query for hotels that have both? With this current set up, I couldn't do
WHERE a.key = 'wheelchair_accessible AND a.key = 'wifi'
Is there a better way of setting up these tables to make this query easier?
I'm new to relational databases and it's likely I'm missing something obvious here.


texts:
For one of my 11.2g databases, select * from dba_tablespace_usage_metrics returns no rows.

Why is that and how can I fix it?

Thank you in advance.


texts: How to know if anybody change the db_owner in SQL server

texts: I just want to know is that anyway we can check in SQL server if anybody change the db_owner of a database to other login ID


texts: Searching SSIS packages for the use of specific tables

texts: I have a number of SSIS packages, how can I search them to find the use of specific tables? I need to make tables changes and I am trying to assess the impact of these.


texts: Is there any other way to replace cursor for the following scenario

texts:
Table IA

Id Status  Details
1   open    aaa
2   close   bbb
3   open    ccc
4   open    ddd
5   open    eeee
6   open    ffff
7   open    gggg
8   close   iii
9   close   hhh
10  open    jjj
primary key ID

Table snapshot_IA

Id  Status  Details
1   open    aaa
2   close   bbb
3   open    ccc
4   open    ddd
5   open    eeee
4   open    ffff
5   open    sdsd
4   open    sdsdd

IA table data changes daily
Snapshot table does not have primary key
snapshot table should have new rows from table IA
snapshot table should get rows from IA table if IA table ID present in snapshot table but status is open in the snapshot table

eg.

select T1.id1,T1.status,T1.details from
IA T1,
snapshot_IA T2
where
T1.id1 = T2.ID1
and T2.status <> 'close'

One way of doing it is using cursor.

Can you suggest any other efficient way than this?


texts: Operators similar to 'in' that compares two sets

texts:
I want to compare two sets of values and see whether there are intersections. The first set of values are specified at the runtime and the second set of values are stored in a row of a database.

Here are some sample code:

CREATE TABLE #tab1 (var1 INT, var2 INT , var3 INT)

INSERT INTO #tab1
VALUES (1,2,3),(0,0,0),(0,4,0)

Select
a = (case when 1 IN (var1,var2,var3) OR
               2 IN (var1,var2,var3) OR
               3 IN (var1,var2,var3)
    THEN 1 else 0 end)
from #tab1

I feel that the code is too cumbersome, and am wondering whether there are some simple way to do it. Thanks!


texts: How to automatically append updated data

texts:
I am currently working with Azure Data Factory to create pipelines that feed from a dataset into tables located in Microsoft SQL Server Management Studio. My issue is that whenever new data is fed into my SQL Server database, both the updated and old data is being copied. This is causing my tables to be overpopulated with duplicate data.

Is there any way that I can either append new data onto an existing table or at least have SQL Server automatically overwrite an existing table whenever new data comes in?

I am relatively new to coding, so any expert help would be appreciated.

Thanks


texts: How to load more than one MS-Access-db tables with data in SQL-Server-DB

texts:
I'm just getting into SQL-server 2008 R2 ssis, so forgive me if this is a basic question. I'm building an application using integration service.

One of my computer folders contains more than one MS-Access db, want to load all of this access tables with data in SQL-Server db. To load all tables I want to use ssis. Which control of ssis will help me to load all access tables on my SQL-Server db?

I want to create (SSIS) package for importing an MS Access table into SQL Server, then running some stored procedures on this database and finally exporting the result to Excel.

I am comfortable in writing queries and working in Management Studio but I don’t know how to actually design an SSIS package. Need helping hand to do this?

If have any query please ask.


texts: Trigger SQL Agent job based on text in error log for an AG failover

texts:
I have a 2 node AG setup (Not an FCI). I need to configure an alert to trigger a job based on a string found in the error log showing that the node is now the new primary.
The string I want to use is:

  'primary_pending' to 'primary_normal'

This is what shows in the error log when the secondary node becomes primary. I want the alert to kick off a SQL job I created to automate our SSRS to use the new primary replica.

Below is the alert I setup, but it is not triggering properly. I do have the alert configured to execute the job when this alert is triggered.

When a failover happens, the job does not kick off. I'm guessing it may be due to the error number used. I found a blog post about using this, but can't seem to find it again. The error log does not display any error number in the line with this text, so I think that is the issue.

How can I get an alert to trigger based on text it finds in the error log, or is there a better way to do this?

I do have conditional logic to check for primary / secondary status in all my jobs, but I can't use that for this job as I want it to only execute 1x (when it first becomes primary).


texts: Solutions for reporting off of an EAV structured database?

texts:
Existing setup SQL Server 2005 with standard EAV table that users want to slice and dice with a BI tool (Cognos).

Is there any hope that we can transform this data into a format that we can report off of? Google has led me to believe there is no hope. I'd like to believe that some kind of solution is out there.


texts: Linked server from Azure managed instance

texts:
I have my Managed Instance (MI) setup and can connect from SSMS on-premises. There is limited functionality this way (you only see databases and security) - so I have a VM in the v-net where I can manage the MI from.

I have set up a linked server from one of our servers to the MI fine - I can query the MI from the server using 4 part naming convention.

How do I do it the other way? Using the management jump-box with SSMS on I have the option to create the linked server, but I cant connect to the on-premise  server.


texts: Durability and other limitations in mongoDB?

texts:
I've created web app that use mongodb, my mongo database currently has 4,05 GB , but every day it increase, next year it will probably have about 8 GB ,

I have python scripts that runs every 30 minutes , collect data from Clear Case system and store it in mongodb ,

problem is that server where is web app runned, often crashes and restarts, that cause data corruption in my mongo database, so my web app don't works good , when user type information that will run mongo query that will get corrupcted data.

I solve this problem with backups, every week I make backup and when I notice data corrupcion I replace my mongodb with backup, but this isn't good solution .

I tried repairDatabase , but I get error

{

"errmsg" : "exception: can't map file memory - mongo requires 64 bit build for larger    datasets",

"code" : 10084,

"ok" : 0

}

I'm newbie in mongodb, can someone tell me more about durability in MongoDB, and other limitations in mongoDB, e.g. How will size of mongo database affect to work on my web app , next year size will be at least 8 GB , maybe more ... Anyone has similar problems , how to solve it ....


texts: How to prevent SQL Server Job History deadlocks

texts:
Out of the ~50 instances I currently manage, I have two that each have one deadlock almost every night involving "INSERT INTO msdb.dbo.sysjobhistory" (always the victim) and "DELETE FROM msdb.dbo.sysjobhistory" called by "EXEC msdb.dbo.sp_purge_jobhistory", both sessions run by the SQL Server Agent service account. I have never had such a deadlock on any of the other instances, nor at any previous jobs. One each of these servers, the deadlocks occur at different times during the night (1:30 Saturdays run by a weekly schedule for sp_purge_jobhistory or 2:01 run by a step in the nightly schedule for syspolicy_purge_history).

There doesn't seem to be anything significantly different between these two "problem" instances and any of the others. One is a 2012 SP3 CU3 test server and the other is 2012 SP2 production instance.

Any ideas as to how I can prevent this?


texts: Always Encrypted Certificate clarification

texts:
I have configured AE using SSMS on the database server installed with SQL Server 2016. The certificate created under Local Machine option displays the validity as one year.

I just want to know what is the difference between creating a certificate using SSMS and creating a self-signed certificate using PowerShell.

What is a self-signed certificate means in the context of data encryption?


texts:
> CREATE TABLE test(foo FLOAT);
> INSERT INTO test VALUES(1.2899999);
> SELECT * FROM test;
+------+
| foo  |
+------+
| 1.29 |
+------+

While it's obvious that FLOAT is not a precise data type, it is still rounding this data too arbitrarily. Let's check if it's actually able to store it:

> ALTER TABLE test MODIFY foo DOUBLE;
> SELECT * FROM test;
+--------------------+
| foo                |
+--------------------+
| 1.2899998426437378 |
+--------------------+

Whoops, only the 7th digit got corrupted due format limitations. Is there a way to prevent overzealous rounding in this situation, short of converting the column to DOUBLE?


texts: If a query triggers a statistics update and times out are the statistics still updated?

texts:
I have a fulltext query which is usually very fast but may time out when it causes a statistics update since statistics updating is very slow on this database. Usually the query "recovers" to normal speed after the statistics have been updated but I have seen a case where the query always times out which I can only explain with the fact that the statistics are never updated (or so I think).

Unfortunately I cannot reproduce the problem at this point since we switched statistics updating to “Auto Update Statistics Asynchronously” to prevent the timeout from happening in the first place (slightly out of date statistics are not a problem for us).

So my question is

Is there a guarantee that statistics are updated on a query that times out if statistics updating is set to the (default) synchronous statistics updating (Auto Update Statistics Asynchronously = false)?

I found a reference that states otherwise - if that is the case I cannot explain the continuously timing out query case.


texts: Handling partial backups from many different databases

texts:
I've got an issue and I'm looking for best practice advice / whatever I can do to make this work.

I need to come up with a way to automate getting a backup from many offsite databases of unknown name, and there is one table that I cannot back up. From there then merging the data from all these backups into a single database and append a column stating where it is from.

The remote databases will all be running SQL 2016 standard, or sql 2012 Enterprise, or newer. They will not have SSIS. The local database server is running SQL 2016 Standard and can have SSIS.

After some looking around, my plan was to separate the one table I cannot back up into it's own filegroup. Then from there backing up the primary filegroup only, getting that sent to us, and then using SSIS or a service monitoring a folder for new backups. From there restoring the backup to a DB of name I can pre-set, to act as a staging area so I can merge the data as needed.

The reason for wanting the backup to go into a DB name I set here and not necessarily what is at the remote site is due to the fact that I can't guarantee what they name the databases and I'm worried about a conflict of a database we have on the server that will be handling these backups.

The issue I found with that method is that I can't seem to restore only the primary filegroup, it either complains about the name being different(even if I have with replace), or even if the name is the same it will complain that it did not back up a file from the second filegroup.  I get this error even if I include the "REPLACE" keyword: "The backup set holds a backup of a database other than the existing 'TestImport' database."

I have the ability to change the methodology as much as I need to during this stage, but receiving backups from an unknown number of databases of unknown name, and automating that into a centralized location are the items that are set. I'm open to any ideas that anybody has.


texts: How I can flatten a count table in postgresql that contains only 2 columns with crosstab?

texts:
In my database I have the following table:
Mytable
id SERIAL,
category VARCHAR,
value VARCHAR

And I execute the following query:
select category, COUNT(*) from mytable group by category

What I want is to generate a single row value containing the following:
category1 | category2 |  category3
1234      | 3456      |  12345

The table return the following results:
 category   | Value
`category1` | 1234
`category2` | 3456
`category3` | 12345

Do you have any idea how to do this?
I have looked the crosstab function but required an extra column named row_name that in my cases does not exist. Also using a second query seems like a waste to me.
The


texts: Why are some of my migrated tables showing "The given key was not present in the dictionary" in WebMatrix's interface?

texts:
I'm having an issue while using the SQL Server Migration tool to convert Access accdbs into SQL Server 2014 Express. I am converting the tables using SQL Server Migration Tool and I have a connection string to the SQL Server 2014 Express database in WebMatrix. All of the tables show up, but, for some reason, many of the tables, when I try to open them in WebMatrix, show an error "The given key was not present in the dictionary. Like so:

During the conversion, it shows "0" errors and proceeds as though everything is okay. This seems to be an issue with WebMatrix, because, not only are the tables openable in SQL Server Management Studio (showing all rows), but they are also queryable, meaning that the information is there:

It just won't show in WebMatrix's interface if you try to open the whole table.

I would like for this to work in WebMatrix and was wondering if anyone could help or has encountered this before. I try to search Google, but nothing relevant comes up. That's usually the case when trying to research WebMatrix specific problems.


texts: Users can create SSRS subscriptions but can't edit them

texts:
I'm using SSRS 2016. Users can successfully create subscriptions to reports but if they go back to update a parameter (change a date range for example), it throws a "delivery error has occurred" message when they try to save. When you look in the logs, you see the full error below:

  Microsoft.ReportingServices.Portal.WebHost!reportserverwebapp!49f!07/18/2017-07:44:41::
  e ERROR: [4cngitdf]: OData exception occurred:
  System.Web.Services.Protocols.SoapException:
  System.Web.Services.Protocols.SoapException: A delivery error has
  occurred. --->
  Microsoft.ReportingServices.Diagnostics.Utilities.DeliveryErrorException:
  A delivery error has occurred. --->
  Microsoft.ReportingServices.Diagnostics.Utilities.InvalidExtensionParameter:
  One of the extension parameters is not valid for the following reason:
  The account you are using does not have administrator privileges. A
  subscription cannot be created for xxxx@xxxx.com.

It appears they have valid permissions to create their own report subscriptions but not to edit their own subscriptions.

We have two security groups setup... Administrators with the Content Manager role and Users with the Browser role. I am in the Administrator's group and I can add and edit subscriptions just fine.

How can I make this so users can edit their own subscriptions (but no one else) without giving them the same permissions I have (I don't want users to be able to edit the reports or data sources so I can't make them Admin's too).

Or is this just maybe a bug in SSRS that I can't work around? Users can even delete their own subscriptions so I've been having them delete and recreate them, but it would be much nicer if they could actually edit them instead. Odd too that it allows them to add and delete but not edit.


texts: Return number of rows based on column value

texts:
We are administering a sweepstakes for a client where we have a GUID tied to a contestant and the number of entries the contestant has acquired. I want to be able to draw winners based on the chance of winning corresponding to the number of entries.

GUID | Entries
--------------
1    | 1
2    | 5
3    | 2
4    | 7

So the results would be:

GUID
----
1
2
2
2
2
2
3
3
4
4
4
4
4
4
4

Basically I plan put the results in a spreadsheet and then use a random number generator to pick a row number between 1 and n, where n is the total number of entries. But of course if there is a way to easily select the winner programmatically, I'm all ears (or eyes as the case may be).

Thanks in advance.


texts: How can I obtain the full text for a query that appears in sys.dm_pdw_exec_requests?

texts:
I'm trying to tune a DB in Azure SQL Datawarehouse for queries produced by Power BI, which can connect directly to Azure SQL Data warehouse. I'm interested in studying execution plans, tweak things like indexes and statistics, retry queries to test changes and so on. A typical way to do this is by looking at sys.dm_pdw_exec_requests system view to get the execution times of queries, their text and their ID, in order to then study their steps with sys.dm_pdw_request_steps. Some of these queries are longer than 4000 characters because of the typical automated query generation syntactic clutter.

Microsoft documentation states that "command" column holds up to 4000 characters, so any query bigger than that is truncated.

... So I cannot study a query longer than that

I have not been able to find a way to get the queries yet. Any ideas?


texts:
One of our Database - has been Error Spamming Event Logs for over 8 months stating the below Error:

  There is no column named id5093 in the table body_record. It is ignored for this operation.

The Column has been removed and re-added, the DB also has been refreshed/restarted, but we still keep getting the Error even though the Column exists.

Below code was used:

Use OM2;
alter table body_record add id5093 nvarchar(max);

Is there a way to fix this please?


texts:
I tried all three method explained here  to max_allowed_packet. But no one changes its value in my MySQL 5.6.

I use show variables like '%max_allowed_packet%' to see its current value. But it always is 12582912.

Only changing its value in my.ini is effective.

What is wrong?


texts:
I have a situation that I've never seen.  OSQL crashed in a scheduled job which has always worked.  Windows event viewer shows an exception code: 0xc0000005.
the version of OSQL.exe also showing in the same Windows Event Viewer error message is 2011.110.2100.60. -->  this corresponds to the SQL Server 2012 RTM.

When running @@version of the SQL Server it states that I have SP2 installed.

Is this normal? do I need to upgrade OSQL to SP2? If so, how to upgrade OSQL to SP2?

OSQL Windows Event Viewer output:

    Faulting application name: OSQL.EXE, version: 2011.110.2100.60, time stamp: 0x4f35eaf3
Faulting module name: ntdll.dll, version: 6.3.9600.18696, time stamp: 0x59153753
Exception code: 0xc0000005
Fault offset: 0x00000000000546fb
Faulting process id: 0x1bdc
Faulting application start time: 0x01d31d465269cf58
Faulting application path: \\SFSMSSQL\c$\Program Files\Microsoft SQL Server\110\Tools\Binn\OSQL.EXE
Faulting module path: C:\Windows\SYSTEM32\ntdll.dll
Report Id: 5bb47a06-895c-11e7-80eb-005056b410f0
Faulting package full name:
Faulting package-relative application ID:

@@Version output:

Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
                May 14 2014 18:34:29
                Copyright (c) Microsoft Corporation
                Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Added Note: I am aware that we should be using SQLCMD instead OSQL which will soon be depricated... it is in our road map. .

My research hasn't been fruitful.

Thank you


texts: MYSQL database replication Error MY-002061

texts:
I've been trying to get two databases in docker containers to replicate. I've been working off this article:
https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql
(and others).

The state I've been able to get to is that the master and slave dbs have loaded cnf files that set the server-ids, and after the DBs are stood up, I am able to run a few commands that should get the DBs to be connected:

MS_STATUS=`docker exec mysql-master sh -c 'export MYSQL_PWD=password; mysql -u root -e "SHOW MASTER STATUS"'`
CURRENT_LOG=`echo $MS_STATUS | awk '{print $6}'`
CURRENT_POS=`echo $MS_STATUS | awk '{print $7}'`

start_slave_stmt="CHANGE MASTER TO MASTER_HOST='mysql-master',MASTER_USER='replication_user',MASTER_PASSWORD='replicationPW',MASTER_LOG_FILE='$CURRENT_LOG',MASTER_LOG_POS=$CURRENT_POS; START SLAVE;"

start_slave_cmd='export MYSQL_PWD=password; mysql -u root -e "'

start_slave_cmd+="$start_slave_stmt"

start_slave_cmd+='"'

docker exec mysql-slave sh -c "$start_slave_cmd"

This sets the master_log_file and Read_master_log_pos to the same values in both the master and slave dbs.

The error I am getting is:

mysql-slave               | 2018-09-20T20:13:41.547886Z 11 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to master 'replication_user@mysql-master:3306' - retry-time: 60  retries: 1, Error_code: MY-002061

I've looked at the documentation, but I don't see any mention of that error code (2061). What am I missing?

I've named the replication user in both database the same... could that be the issue?


texts:
I have a table with two columns; EVENT_DATE date and RANG number
the first column holds a date for an event while the second column is for the period of that event. here is a sample of data

| EVENT_DATE | RANG |
|------------|------|
| 03/01/2015 |    1 |
| 09/04/2015 |    3 |
| 15/10/2015 |    2 |

is there any way to expand the EVENT_DATE by increment it based on the RANG value, so the output will be like,

| EVENT_DATE |
|------------|
| 03/01/2015 |
| 04/01/2015 |

| 09/04/2015 |
| 10/04/2015 |
| 11/04/2015 |
| 12/04/2015 |

| 15/10/2015 |
| 16/10/2015 |
| 17/10/2015 |


texts: MySql InnoDB tables are missing after restarting

texts:
I had a MySql database with a few InnoDB tables. After restarting the server, and starting MySql Server again, I connected to the MySql Server using MySql Workbench, and couldn't see the database at all (it even doesn't show up empty, it's just not there). It's important to note that the server was brutally shut down (I had no choice), so maybe MySql Server wasn't closed normally, and it caused a problem.

I am new to MySQL area with innoDB. Any help is really appreciated.

Thank you in advance.

Regards, Ron


texts:
I've been searching for possible duplicates before asking but can't find anything. The closest was Backup SQL Server To Nas but not exactly my case.

We have a brand new Synology NAS configured, due to some security needs is configured to use a dedicated user and pwd not included on the active directory or alike. Already tested and is ok, can login, see the files and all rights are ok.

On the other side, we have backups running using Ola Hallengren useful scripts, by running slqcmd.

Question: how I can provide user/pwd credentials to access the NAS? Have tried using -U -P but got following error. Then tried with -S but also error. What I'm doing wrong here? I guess should be something silly whatever I'm missing, but can't possible find it.

Sqlcmd: The -E and the -U/-P options are mutually exclusive.  Process Exit Code 1.  The step failed.


texts: VM snapshot taken after backup has different row count

texts:
I am verifying a VM backup, which contains a sql server instance. VM restore takes some time, and that means I can have mismatch between database at source VM with DB at destination VM.

To verify if all restore work, I am doing following

Create a backup on source location (that I will use in destination VM)
Crate a snapshot of db in source location. (idea is that snapshot DB should be exactly equal to destination VM after restore)
Take VM restore
restore Backup taken at step 1 on destination VM.
Stop any job running on destinaion VM, so that I don't corrupt data at destination VM.

As I have taken snapshot at source VM and source VM is production one, I am not changing any job at source.

Also to minimise any time lag, I clubbed Backup and create Snapshot steps i.e. step 1 and step 2, in a single sql server job.

so now when I am comparing destination VM data with source VM snapshot database, I am expecting to get all rows/columns to be same. However, I am seeing mismatch.

Am I doing something wrong?


texts: Can You please guide us on how we can change the time zone of the Database to UTC which is migrated from AWS which was in time zone UTC? Now the Postgres 12 is installed in Ubuntu 20 which shows (show timezone; Asia/Kolkata ). We also want to make sure all the existing data inside the database should be changed to the new timezone. Because It is creating issues for the operation team to query on DB as all other DB in the env are in UTC.


texts: Coding an accounting database from scratch?

texts:
My new medical practice is picking up fast and my homemade Libreoffice Calc spreadsheet is quickly becoming cumbersome to track patient visits, accounts receivable, overhead reconciliation, etc.  Most practices use some sort of practice management software to handle accounting as well as scheduling, paying bills, submitting insurance claims, etc. but my budget can't afford to pay for overpriced software.  Besides, I like to do things my own way, as well as support open source software.  (I've already determined that I don't need this function to come from my electronic charts)

Google searching didn't yield any templates and all of the accounting programs found were built for traditional businesses with purchasing, warehousing, human resource management, etc. that aren't adaptable for my needs and are unusable.  You wouldn't think that I would be the first physician to attempt something like this, but the only thing that I could find is a design plan here: http://www.databaseanswers.org/data_models/

So now, I'm considering importing my spreadsheet into Libreoffice Base and slowly developing a custom accounting program for myself.  However, I have no experience with databases and I don't want to spend a large amount of time on this if the amount or complexity of coding is beyond what I am willing to sacrifice from my study time.  After browsing through textbooks at the local B&N, the first 1/3 of most books on MySQL, Filemaker Pro, and Access seem applicable for what is needed (complex mathematical modeling and data analysis is overkill), but I'm still hesitant about heading into this endeavor if the work load is larger than I care to handle.

So, is it doable for someone with remote programming experience (qbasic, visual basic) and basic spreadsheet knowledge to code a simple custom accounting database?  If so, is LibreOffice Base sufficient? What program or textbook would you recommend?

Thank you!


texts: Is having relationships between separate databases bad practice?

texts:
I am working with a client that has multiple databases. There are several master level databases that have relationships back to them from instance level databases (application specific DBs). The relationships from instance to master are integer values that represent a primary key to tables in the master. The views and stored procedures in the instances are setup to load data from the master via these stored keys.

Obviously, there is no real referential integrity, but is that bad practice or should the data reside in read-only tables in the instance databases?


texts:
I'm looking for a simple database design for tags, where the tag-label can be translated (multilingual).

I'm currently looking at Toxi, which is a very simple and clean way of creating a tagging system.

https://stackoverflow.com/questions/10506181/tagging-system-toxi-solution-questions
https://stackoverflow.com/questions/8318365/best-database-schema-for-tagging-system-with-a-low-and-constant-number-tags
http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/

Normal toxi:

Bookmark(id,name,uri,description,time_created)
Tagmap(id,Bookmark_id,Tag_id)
Tag(Tag_id,name)

Would this be a 'correct' way to implement a multilingual toxi tagging scheme?

bookmark(id,name,uri,description,time_created)
tagmap(id,bookmark_id,tag_id)
tag(tag_id)
tagtranslation(id,tag_id,culture,name)


texts: SQL Server 2012 on a Windows 2008 R2 cluster, would like to upgrade Windows to 2012 R2

texts:
I need someone to help validate a strategy. I have a pair of Windows 2008 R2 server in a active/passive failover cluster with SQL Server 2012 installed.

I would like to upgrade the Windows OS to 2012 R2. I know that you cannot join a Windows 2012 R2 server to a 2008 R2 cluster. I thought about evicting a node and doing a fresh install of Windows 2012 R2 with cluster services, then detach the databases from SQL Server, destroy the 2008 R2 windows cluster, build the 2012 R2 cluster using the same name, IP, and SAN drives, then install SQL Server 2012 as a cluster using the same instance name and attaching the databases.


texts: SQL server moved, now can't use as linked server

texts:
So I have a SQL server database that was controlled by an outside vendor.  It is hosted remotely.  Then he needed to move the server to a new host.  Now, I'm able to connect to the server manually from SSMS and I can add it as a linked server, but I can't USE it as a linked server, or even browse the servers catalogs.

My instance is 2000, and I assume the remote server is 2008 or 2008 R2.

When I try to use the server (try to update a stored proc that points to the linked server) I get the following error:

Trying to browse the linked server's catalogs from SSMS throws this:

  Failed to retrieve data for this request.
  (Microsoft.SqlServer.Management.Sdk.Sfc)

  [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
  access denied. (Microsoft SQL Server, Error: 17)

A friend suggested I run Exec sys.sp_change_users_login 'Report', but it turns up no orphaned records, and anyway, I can login using the credentials, so that doesn't look like the problem.

EDIT: Can't connect at all with IP, but hostname connects then gives above error.  HUH?


texts: Avoid redundancy in a database for a tournament software?

texts:
I'm about to write a tournament software. Therefor I have two db-tables in mysql database:
players and matches.
In matches I save every single match played in the tournament:

ID_player1, ID_player2, Goals_player1, Goals_player2

I would like to sum up all goals from all matches of every player, so I can display a ranking, where won mathes and goals and countergoals are displayed. Now the question is, how to realize this in the database: I see several options:

Save goals and countergoals in the players table. Every time a match is entered into the database, the goals and countergoals are added to the related player. (but what about redundancy..?)
Save goals and countergoals in the players table. But every time a match is entered into the database, run a query over the matches database and sum up all the goals every time again and again. This way I would at least avoid possible inconsistency.
Don't save goals and countergoals in players table. But every time I would like to display the players rank with all goals and so, there would be a humangous database query running over the whole matches table for every single player. This is probably not very smart when it comes to performance, is it?

So what is the best way to store that information..?
I appreciate every help or hint.
greets and thanks in advance!


texts: How to query from a linked server via a linked server in sql server?

texts:
I have the following set-up:

SQL-Server 1  --linked-->  SQL-Server 2  --linked->  SQL-Server 3

The question is can I query SQL Server 3 from SQL Server 1 via SQL Server 2?

I have unfortunately several restrictions:

cannot use openquery
cannot use views on Server 2 (there would be many of them and they'd have to be maintained)
cannot create a link from Server 1 to Server 3 (due to firewalling issues)
servers are MS SQL Servers


texts: Naming The SSRS Report Name based on the Parameters Passed

texts:
I want to save my SSRS Report Based on the Parameters Passed. i.e. Am passing P1 And P2 as parameters so I want my SSRS
Report name as P1_P2_ReportName.Xtension.

Any suggestions?


texts: How do I convert cents retained as integers to dollars as decimals?

texts:
I have cent values, retained as integers, that I need to convert to dollars and show them as a decimals.

Example

A value is kept as 1925, so I need to be able to return 19.25.

This is what I had been doing in MySQL

format(price/ 100, 2) as amount

and when summing the value, I use

cast(SUM(price) / 100 AS DECIMAL(12, 2)) as total_amount

These seem to work fine, but when moving over to Redshift, I am not sure if I am using the right function.

Current attemtp

In Redshift, I try with

price :: DECIMAL(12, 2) as amount

and then

SUM(price)/ 100 :: DECIMAL(12, 2) as total_amount

In both instances, I get 4 decimal points in my results? Is using wrapping these functions with trunc(,2) a good way to solve this?


texts:
I am trying to fetch some records using INNER JOIN. The problem is that I am not sure what's the best (recommended) way of doing it.

What "pros" and "cons" have the queries shown below? All of them work correctly and do the same thing.

#This query seems to perform faster than the
#following one but is it ok to select all the columns?
#Needed is only one column (`id`)

SELECT book_name, book_type FROM books b INNER JOIN (
    SELECT * FROM authors WHERE user_id = ? AND male = ?
) a ON b.author_id = a.id;

SELECT book_name, book_type FROM books b INNER JOIN (
    SELECT id FROM authors WHERE user_id = ? AND male = ?
) a ON b.author_id = a.id;

SELECT book_name, book_type FROM books b INNER JOIN authors a
ON b.author_id = a.id WHERE a.user_id = ? AND a.male = ?;

I am also not sure about the functionality and performance. For example, in the first case, the subquery selects some records (there may be let's say five of them), then joins with the table to return the result set. In the last example (I suppose) the full tables will join (a lot more records) and then the records are filtered.


texts: How to GRANT permission to execute stored procedure?

texts:
I have an AD group that is setup as a Windows Authenticated SQL Login on my SQL Server. On DatabaseA it has the db_datareader and public roles. So the users of this AD group only have read access to the entities in the database.

Inside of DatabaseA I have a stored procedure dbo.Proc1 on which I granted the execute permission for the same AD group.

When a user of that AD group connects to this server, they don't see the entity dbo.Proc1.

Is there any other permission I need to provide to the Windows Authenticated SQL Login (AD group) that I'm missing?

I do see, if I look at the properties of DatabaseA, that there's an execute permission at that level which has NOT been granted yet.

(Note: I only want the users of this AD group to have the ability to execute dbo.Proc1. I don't want them to be able to do anything that isn't considered a "read-only" type of action.)


texts: Creating a Stored Procedure that will input data for each day between the date entered as input to or from the 1st of that month

texts:
i am trying to figure our sql syntax and my initial challenge was to call a stored procedure name GetPIValueAverageTime and write the float value to an table named actuals. and it created the row of data for that inparticular production date. in sql how can i iterate through two dates the production date entered to the 1st of that month and enter that date that the iteration is on into the exec of the stored procedure in order to populate date from the particular date ? this is just the first part i need to add all the float values i get from those dates populate and fill and month to date average but ill handle that later i just what to populate the table with data in an iteration from the prod date to the 1st of the month. this is what i have so far please excuse the mess. i tried to do this with a while loop but it inserts first row for the first day of the month then hangs on "executing query" and i have to force kill it.

USE [OperationsView]
GO
/****** Object:  StoredProcedure [Interface].[proc_GetChemicalDaily] Script    Date: 1/30/2017 4:03:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Daily Chemical>
-- =============================================
ALTER PROCEDURE [Interface].[proc_GetChemicalDaily]
-- Add the parameters for the stored procedure here

@ProductionDate As DateTime,
@Debug BIT = 1

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- pi sproc variables Declaration
DECLARE @result As Float
DECLARE @tag_in As varchar(80)
DECLARE @pi_server As varchar(50)
DECLARE @enddate As DateTime
DECLARE @FCChemGALPERMIN float
DECLARE @CAUSTICDAILYUSAGE As Decimal(16, 8)
DECLARE @constant As Decimal(16, 8)
DECLARE @BegMonth As DateTime

--Actual variables for insertion
DECLARE @Group As VARCHAR(200)
DECLARE @Item As VARCHAR(200)
DECLARE @Total As DECIMAL(20, 4)
DECLARE @Days As Int
DECLARE @Type As VARCHAR(1)

--Pi Sproc variable setting
   SELECT @tag_in = 'I-FC-835'
   SELECT @pi_server = 'valpi'
   SET @constant = (18318.0/2000.0)*.5
   SET @BegMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

   --Setting Actual insertion variables
   SET @Group = 'CHEMICAL'
   SET @Item = 'CAUSTIC CHEM'
   SET @Days = 1
   SET @Type ='a'
   --create temp table to grab decimal value
   create table #temp
   (
    float_col   float,
    percent_col float
   )
--Grabbing gal/min value from PI
While @productionDate >= @BegMonth

     SELECT @endDate = DATEADD(DAY, 1, @BegMonth)
     INSERT into #temp(float_col, percent_col)
     EXEC Interface.proc_GetPIValueAverageTime @result, @tag_in, @BegMonth, @endDate, @pi_server, @Debug
     SELECT @FCChemGALPERMIN = float_col FROM #temp

  -- daily dry ton usage calculation
  IF @FCChemGALPERMIN > .5
 SET @CAUSTICDAILYUSAGE = cast(@FCChemGALPERMIN*@constant as numeric(38,10))
  ELSE
 SET @CAUSTICDAILYUSAGE = 0.0000

--Inserting in Actuals table
IF @Debug = 1

begin
    INSERT INTO [OperationsView].[dbo].[Actuals]
            ([Group]
            ,[Item]
            ,[ProductionDate]
            ,[Days]
            ,[Total]
            ,[Type])
        VALUES
            (@Group
            ,@Item
            ,@ProductionDate
            ,@Days
            ,@CAUSTICDAILYUSAGE
            ,@Type);
end

DROP TABLE #temp
Set @BegMonth = DATEADD(DAY, 1, @BegMonth)
 END


texts: Need listing of all relationships of tables SQL Server 2017

texts: I have a ticket that I need to get a listing of all the relationships in one of my tables, I found that fairly easy here - I need to take it one more step - what my manager wants to see is all the tables that directly reference my main patient table, and all the ways those child tables reference all other tables.. Something like: Patient table -> address table then address table is linked to the shipping table, then the shipping table is linked to xx table... so its a huge spiderweb!  has anyone had to accomplish this before?


texts:
I have a table of 4GB(18 Million Rows) in MYISAM Engine on one of my slave server, and I want to convert it to INNODB Engine, the server is replicating from a master.
I tried to use the pt-online-schema-change tool which says that it will take more than 1 day, and server lag is increasing because there a lot of insertions in this table,so every query has to be executed twice because of the trigger that the tool creates so that no-downtime is faced.

But I can afford a downtime since it is a slave, is it advisable to just simply run the Alter Engine command ,and how much approximate time will it take to execute?

Also I fear that the replication may break,once the alteration takes place.
I am replicating from a 5.5 Master and My Slave is on 5.6.

What am I advised to do in this case, Any help would be appreciated since I am very new to this field.

Thank You


texts:
i am learning SSDT from this link
https://msdn.microsoft.com/en-us/library/jj819740(v=sql.120).aspx
on point 8 i stuck
before that i install DQS successfully and also connected to Data Quality service client
now when i check in MS SQL 2014 there is 3 database created DQS_MAIN,DQS_PROJECT,DQS_STAGING_dATA

now when  i follow point 8 and click on mapping tab this shows an error

any solutions please?


texts: Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that's in 2000 (80) compatibility mode

texts:
We have a very old database running on SQL Server 2008 R2. The database is running in 2000 (80) compatibility mode and has oodles of NON-ANSI joins in stored procedures, views and functions.

We need to update this database to at least 2005 compatibility mode to enable the use of CTE SQL, ROW_NUMBER OVER (PARTITION BY) and so on.

When we first upgraded to SQL Server 2008 R2 we obviously ran Upgrade Advisor 2008 R2 first which highlighted all SQL that had non-ansi joins, at the time we just ignored them and put the database in SQL Server 2000 (80) compatibility mode.

Our problem now is that SQL Server 2008 R2 Upgrade advisor will no longer work for us as the version isn’t supported (as we're already using that version), and 2000 (80) databases aren’t supported in the 2012 Upgrade advisor either.

My question, short of scripting the whole database and painstakingly going through it checking for *= and =* and *=* etc, is there a tool we can use to detect all procs, views and functions that contain these joins so we can get them re-written.


texts: Fastest key-value store for random disk reads?

texts:
I want a key-value store, with records stored on disk. (There are too many for memory.) I want to use a single machine.

What key-value store will provide the fastest random reads (off disk)?


texts: GV$locked_object showing object locked but kill session is giving ORA-00030: User session ID does not exist

texts:
I have tried to execute a stored procedure but its running for an hour. So I have checked for locks using below query but it doesn't return anything.

select session_id, a.object_id, xidsqn, oracle_username, b.owner owner,
b.object_name object_name, b.object_type object_type
FROM v$locked_object a, dba_objects b
WHERE xidsqn != 0
and b.object_id = a.object_id;

When tried to recompile the procedure result is same, So I have inspected if there is any compiling locks using below query ,

SELECT s.sid,
l.lock_type,
l.mode_held,
l.mode_requested,
l.lock_id1
FROM   dba_lock_internal l,
v$session s
WHERE s.sid = l.session_id
AND UPPER(l.lock_id1) LIKE '%SS_ORACLE%'
AND l.lock_type = 'Body Definition Lock'
order by  l.lock_id1;

It also didn't return any results. So I have tried below query and it return an session with ROW EXCLUSIVE lock

SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID,
S.serial#,
SUBSTR(O.OWNER||'.'||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,
DECODE(L.LOCKED_MODE, 0,'NONE',
1,'NULL',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE',
NULL) LOCK_MODE
FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S, sys.GV_$PROCESS P
WHERE L.OBJECT_ID = O.OBJECT_ID
and l.inst_id = s.inst_id
AND L.SESSION_ID = S.SID
and s.inst_id = p.inst_id
AND S.PADDR = P.ADDR(+)
order by l.inst_id  ;

But when i tried to kill the session I got below error message,

alter system kill session '122,44454' immediate
Error report -
ORA-00030: User session ID does not exist.
00030. 00000 -  "User session ID does not exist."
*Cause:    The user session ID no longer exists, probably because the
           session was logged out.
*Action:   Use a valid session ID.

Please guide how we can kill the session?


texts:
Scenario:

Sql Server Database routinely backed up with Backup Statement below.
The "Recovery Model" was set to "Full" (we usually have them set to "Simple").
The ldf file, which had gotten quite large, was deleted using Windows Explorer (after first stopping Sql Server).
ldf file deletion did not go into recycle bin because it was too large.

  BACKUP DATABASE MYDATABASE to disk = 'E:\BACKUPS\BACKUP.BAK' WITH
  FORMAT, MEDIANAME = 'MYDATABASE', NAME = ' FULL MYDATABASE BACKUP'

We are now trying restore one of our backups (200MB file size).  The restore progress proceeds at expected rate.  But it sticks at 100%, and the database status remains Restoring.  We notice that once the progress hits 100% the mdf file is approximately the right size.  And the ldf file shows 60GB filesize.  This was about the size of the file when it was deleted.  But obviously this file did not come out of the 200MB bak file.

Is there anything we can do?  I am not a Sql Server DBA, but I presume the problem was we were not backing up a "Full Recovery" database properly.


texts:
Essentially my question is: how does one do aggregate operations involving overlapping ranges in PostgreSQL 9.3 (or 9.4)? The specific problem I have at hand is that given a range, I want to find the maximum sum() of applicable overlapping ranges. A simple example:

create table event (
  event_id int primary key,
  event_type_id int not null,
  period tstzrange not null,
  quantity int not null
);

insert into event (event_id, event_type_id, period, quantity) values
(1, 1,'[2016-01-06 09:00:00+00,2016-01-08 17:00:00+00]',1),
(2, 1,'[2016-01-07 09:00:00+00,2016-01-07 11:00:00+00]',1),
(3, 1,'[2016-01-07 13:00:00+00,2016-01-07 17:00:00+00]',1),
(4, 2,'[2016-01-07 12:00:00+00,2016-01-07 17:00:00+00]',1);

Given a query with the following clauses:

select ...
where event_type_id = 1
and period && '[2016-01-07 00:00:00+00,2016-01-07 23:59:00+00]'::tstzrange
group by event_type_id

The desired result would be: 3, i.e. the maximum sum(quantity) where the ranges of the same event_type_id overlap within a given timestamp range.


texts:
Using PostgreSQL.

Had a trigger which does something after delete and made a rule which stopped delete from happening if something will be true.

My question is: will the rule prevent the trigger from occurring as well?

The trigger definition:

CREATE TRIGGER wypo AFTER INSERT ON "Wyp" FOR EACH ROW EXECUTE PROCEDURE funk_wyp();

The trigger function

CREATE OR REPLACE FUNCTION funk_wyp() RETURNS TRIGGER AS '
DECLARE
  dodatek real := (SELECT f.cena FROM filmy f WHERE f.ID = NEW.co);
BEGIN
    UPDATE "zyskiklient"
       SET "ile" = ile + dodatek
    WHERE kto = NEW.kto;
    UPDATE "istotnoscklienta"
       SET "poziom" = (SELECT poziom(NEW."kto"))
    WHERE "kto" = NEW.kto;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

The rule:

CREATE OR REPLACE RULE zasada_delete_konto
  AS ON DELETE TO "konto"
WHERE konto_zwrocone(OLD."ID") = FALSE
DO INSTEAD NOTHING;


texts: Modifying a part (one procedure) in oracle database package

texts:
Is it possible to modify an individual procedure inside an Oracle DB Package? instead of recreating (modification is not done using a SQL editor, so cannot modify a place and easily recompile the package) the package again every time we need to do a minor change?

Thanks.


texts:
Im currently working on a project that uses Oracle 10g where every action from the user is recorded in a auditlog table. The current table holds the following values:

AuditID
AuditDate
Username
Page Name
Type (Insert, Delete, Update, Login etc...)

Whenever a user does one of the above Types an audit log is saved.

What I would like to do is to get a count of each page for a particular time scale. For example Day, Month, Year and Quarter.

I have came up with some SQL to get the results for all of the pages together but i am having some trouble with getting the values for each individual page. Below i have given one of the SQL queries that i am using (Year).

SELECT (SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate)-1100, 'yy')) As Y_2009,
(SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate)-732, 'yy')) As Y_2010,
(SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate)-368, 'yy')) As Y_2011,
(SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate), 'yy')) As Y_2012,
(SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate)+368, 'yy')) As Y_2013
From Dual;

This SQL would give me the results of every single page for each year. However I need to find out the results for each page for each year. Is there anyway of doing this without creating an SQL statement like the one above for each page?

If you would like more information please ask.

Edit

The results i need would need to be in the following format:

PageTitle|   Oct_1    Oct_2     Oct_3    Oct_4    Etc...
----------------------------------------------------------
Page1    |   12       23        28       43        .....
Page2    |   23       64        21       18        .....


texts: "Oneway" Replication from Oracle DB to TimesTen

texts:
currently I'm really stuck into a problem and I not able to finde a nice way to realize that.

I need a suggestion to replicate from oracle db (some tables) to a TimesTen. A connection is only possible from Oracle DB to Timesten.

I tried the following possibilities and I think they won't work, because at no time I can realize a connection from TimesTen to Oracle Database.

Active/Passive with Subscribers (On the subscriber will be done a clone, so this connection is not possible) And overall, active passive is oversized.
TT replication with subscribers over replication sheme: could be possible. From what I saw there will be a connection initiated from master to subscriber. But, I don't know how to get the data from oracle db into TT without using Cache groups. So this possibility it out too, because from what I got that is not possible.

Has anyone a suggestion. I will be very thankful for any idea.


texts: PostgreSQL: restrict a GUC variable to only be set in proconfig?

texts:
If a C extension defines a custom GUC variable, is there any combination of GUC "context","source","flags", etc., that can be used to make the system only allow it to be set from the SET (i.e. proconfig) attribute of a function declaration, and not from an ordinary SET command?

Or, is there any way for a check or assign hook for that variable to tell whether the assignment is coming from a proconfig and otherwise reject it?


texts: How to select all data from specific tables for a given schema/prefix in MySQL Database

texts:
I have a muliti-tenant database structure where each tenant resides in a separate database.
So what I had wanted is to create a procedure that spools data of particular tables from all these databases having the same prefix.
Below is the code i have but seems to result to what i want.
SELECT CONCAT('SELECT * FROM ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'm_appuser'
AND TABLE_SCHEMA LIKE 'mifostenant%'

Result of the above is as below;

Your kind help is highly appreciated.


texts:
Company is implementing Database Mirroring Asynchronous with Snapshot. This provides some Disaster Recovery with Reporting area.  Server A Principal contains database which mirrors to Server B, Snapshot is taken on ServerB database.

To enable updated data, we will create database snapshots every 30 min on Mirror server databases. How do we redirect all SSRS/report stored procedure queries to latest snapshot? Our report queries are pretty short, general around 10 sec. Microsoft mentions programmatic solution below, not sure what that is?
So SalesDB1030, SalesDB1100, SalesDB1130, 24clock added after dbname

Report queries may be as

select * from SalesDbSnapshot.dbo.SalesTransaction st
inner join SalesDbSnapshot.dbo.Customer cs
    on st.CustomerId = cs.CustomerId
inner join SalesDbSnapshot.dbo.Product pr
    on st.ProductId = pr.ProductId

Create a Database Snapshot

"To use a database snapshot, clients need to know where to find it. Users can read from one database snapshot while another is being created or deleted. However, when you substitute a new snapshot for an existing one, you need to redirect clients to the new snapshot. Users can manually connect to a database snapshot by means of SQL Server Management Studio. However, to support a production environment, you should create a programmatic solution that transparently directs report-writing clients to the latest database snapshot of the database."


texts: Migrate oracle data to user where partition enabled

texts: We have a oracle database user which has old data set from 2017. This is a test database. This schema has daily partitions. Recently we find out database actually does not have partitioned scripts ran. Now we stuck with our test database which does not have daily partitions and we cannot do testing which involves in partitions.  Now we want to either enable partitions for old data or create database with partitions and migrate data. Our requirement is to get test database with enough data with partitions enabled. Is there a way we can create new user with partitioned enabled for relevant tables, then migrate our old database ( which has sufficient data set from 2017 ) ? Will migration put those old data to relevant partitions ? Anyone with similar situation ?


texts: I am counfused about when Oracle database won't do parsing

texts:
I am confused about when Oracle database won't do parsing?
In the AWR report, there is a metrics called "execute to parse", which means more SQL just execute without parsing when it increases.
But as the Oracle document describe:
"When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution. "
It seems that everytime a SQL statement is issued, parsing will be called.
So I wandering when Oracle won't do parsing and make the "execute to parse" become a larger number?
Or I just misunderstood?
What Oracle document said is:
SQL Parsing
The first stage of SQL processing is parsing.
The parsing stage involves separating the pieces of a SQL statement into a data structure that other routines can process. The database parses a statement when instructed by the application, which means that only the application, and not the database itself, can reduce the number of parses.
When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution.
https://docs.oracle.com/database/121/TGSQL/tgsql_sqlproc.htm#TGSQL178
So if "an application issues a SQL statement, the application makes a parse call", then how applications "can reduce the number of parses"?


texts:
I have a large chunk of json data associated with each id and I have to somehow get the data for my front end by querying through that large chunk.

My TaskDetails table has a structure something like:

id | data
----------------
1  | [{"name":"Roy","Country":"USA","Hobby":"Swim"},
     {"name":"Roy","Country":"USA","Hobby":"Cricket"},
     {"name":"Anam","country":"Greece","Hobby":"Polo"}]
2  | [{"Address":"Church Street","Sex":"M"},
     {"Address":"Amsterdam","Sex":"F"},
     {"Address":"MG Road","Sex":"M"}]

where id is of type integer and data is of type json. Each entry of json data has an array of records.

In the row with id=1 each record has keys (name,Country,Hobby) with respective values. Now this array can be larger, perhaps 10,000 records, all held in a single json row. Something like :

1 | [{"name":"Roy","country":"USA","Hobby":"Swim"},
    {"name":"Roy","country":"USA","hobby":"Cricket"},
    {"name":"Anam","country":"Greece","Hobby":"Polo"},
    {"name":"Roy","country":"USA","hobby":"Swim"},
    {"name":"Roy","country":"USA","hobby":"Cricket"},
    {"name":"Anam","country":"Greece","hobby":"Polo"} ,
    {"name":"Roy","country":"USA","hobby":"Swim"},
    {"name":"Roy","country":"USA","hobby":"Cricket"},
    {"name":"Anam","country":"Greece","hobby":"Polo"},
    {"name":"Roy","country":"USA","hobby":"Swim"},
    {"name":"Roy","country":"USA","hobby":"Cricket"},
    {"name":"Anam","country":"Greece","hobby":"Polo"}]

Now I want to do CRUD operations for the data in this single row.

If I query for all data which is has name="Roy" I should get all the records having name = "Roy" and the output should be:

{"name":"Roy","country":"USA","Hobby":"Swim"},
{"name":"Roy","country":"USA","hobby":"Cricket"},
{"name":"Roy","country":"USA","hobby":"Cricket"},
{"name":"Roy","country":"USA","hobby":"Swim"},
{"name":"Roy","country":"USA","hobby":"Cricket"},
{"name":"Roy","country":"USA","hobby":"Swim"},
{"name":"Roy","country":"USA","hobby":"Cricket"}

I am not able to form a query for this. I am trying with something like:

select *
from TaskDetails
where data->0->>'name'='Roy'

but it will not give me all the records. How should I query? I cannot use jsonb since our prod PostgreSQL DB is currently 9.3.4.


texts: How to write a SQL query to find the range from column values?

texts:
I have some fields in my database which contain values like 2483-5802 and 1245-3325.

Now I have a user input and I have to find it from this column.
Example if I have to search 2000 which will come in range 1245-3325.

So how I can write a query to find that 2000 belongs to 1245-3325 this range?


texts: SSIS OLEDB runs fine locally but fails when deployed and ran on server

texts:
I have an SSIS package that has a data flow task that runs fine when I run the package locally in SSDT 2017 but when I deploy the package to the SSISDB catalog and run it, I am getting data conversion errors.

The data flow task consists of a flat file source and an OLEDB destination.

I can see that the offending column(s) are instances where a DT_STR field from the flat file is trying to load into a DATE field in a SQL Server 2016 table source.

This is probably just matter of me going in and fixing the data types as needed so they match but between source and destination (unless DT_STR can flow ok into SQL Date field in which case no chang would be necessary).

The thing I can't wrap my head around is why the package runs fine and loads data from the file to the table without any issue when I run the package locally but when I run it on the server I deploy it to, I get the following conversion errors.

Only thing I could think of is if the version on server wasn't the same as the version I am running locally but I have already ruled that out by adding an insert statement that would only exist in the version I am deploying to server and I see it does the insert so I know I am running the same package.

From the error message I am guessing the package is failing when it does it's validation and not while in the process of importing any of the records.

Has anyone ran into anything similar to this or have any suggestions of further steps I can take to troubleshoot. As a lot resort I'll probably just delete and recreate the data flow task.


texts: Clustered index always better than Non-Clustered index?

texts:
When I have a clustered index on 'A', is it better to use this clustered index even if I have

selection of 'A=constant'? If not, why?

Thank you!


texts:
I have a Postgres 10.9 table that has ~200 million rows. This table has an index on one of the fields my_date which is obviously a Date. I'm executing the following query: select my_date from my_table.

The explain call yields the following:

Index Only Scan using client_purchaseorder_facility_alias_id_po_date_idx
on client_purchaseorder  (cost=0.57..5981683.41 rows=196100256 width=4)

An extraordinary amount of time is spend (60%) in the ClientWrite event which I see whenever I perform the following query:

select pid, wait_event_type, wait_event
from pg_stat_activity
where wait_event is NOT NULL;

30% is in the DataFileRead.

Postgres documentation describes ClientWrite as "Waiting to write data to the client." Is there a way to tell why it is waiting? I'm performing the query from PGAdmin on a machine where not much else is going on. Total time to complete is at least 5 to 10 minutes. I feel that this should be going much faster.

I checked to see when the table was last vacuumed and it says two days ago.


texts: Cannot login to Oracle11g with System username

texts:
I just installed Oracle 11g into my machine.
I was about to shut down a database instance, and Oracle asked me for username and password, I provided the credentials I used to login into the web system, and it said that the credentials were wrong.

I googled how to change credentials, so I logged in into SQL Plus and did the following:

   ALTER USER sys IDENTIFIED BY "my_new_password";

then I tried to shut down the database instance logging in with the user sys and the new password and oracle still complaint about wrong credentials. Then I logged off, and when I tried to log in with my username and password they didn't work. I tried to login with sys and the new password and it didn't work, and now I completely out of the database management web site, and I can't do anything.

I do not know where to go from here, I'm working in a Windows 7 Machine.


texts: Creating an index in a temporary tablespace

texts:
I am trying to create an index for a relatively large table (80M rows).

First I got an ORA-01652 error, then I searched in SO and found a related question.

However, trying to implement the steps mentioned there,I got an ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace error.

If index is a permanent object and cannot be created in a temporary tablespace, how should I understand this part of Oracle manual?

Thanks.


texts: How to select a row based on the maximum value of a column, when ties are expected

texts:
SUBSCRIBER  DATE         TIME            VALUE
aaa         23-04-2015   04:04:07        10
aab         23-04-2015   12:04:32        5
bbb         23-04-2015   01:04:05        20
bbb         23-04-2015   12:04:20        20
bbb         23-04-2015   12:04:31        20
ccc         23-04-2015   05:04:00        10

I need a query to get the subscriber details based on the maximum value (and the minimum time if there are ties on max(value)) for a particular subscriber.


texts:
Writing here because really can't figure out what to do. I have a site located at ftp server. And now it moves to a new server based on Linux/nginx. And everything is fine except SQL Server ODBC driver. I need to connect to a SQL Server database on my php code through odbc_connect function. I asked administrators of nginx to add required driver to nginx settings, gave them host, port and tds version and they added, but it doesn't work. giving me an error:

  Warning: odbc_connect(): SQL error: [unixODBC][FreeTDS][SQL Server]Unable to connect to data source, SQL state S1000 in SQLConnect in

I'm using the same connection string as earlier.

$odbc = odbc_connect("DSN=dsn_name; Host=x.x.x.x,port_number;", "login", "pass");

Maybe we need to do some specific things to make it work? Thanks.

We some some more odbc drivers and them work fine (Sybase for example). I use AquaData Studio on my local machine to access to datasources and found that authentication setting exists only on a SQL Server database...