texts: DB Mirroring in async/High Performance mode: Available in SQL Server 2008 Standard Edition?

texts:
This should be easy, but the MS docs are not clear.

The docs:
http://msdn.microsoft.com/en-us/library/cc645993%28v=SQL.100%29.aspx

Indicate that in Standard edition, the only supported mirroring is "safety full only"

Taken together with this document:
http://technet.microsoft.com/en-us/library/ms191456(v=sql.105).aspx

The answer would seem to be:

  In SQL Server 2008, async, high performance mirroring is only available in Enterprise Edition

Correct?


texts: SSIS - DMA doesn't support migration assessment to SSIS in local SQL Server 2019

texts:
we will migrate from SQL Server 2008 R2 to SQL Server 2019 in near future, we already run DMA to analyze database, however DMA 5.2 doesn't support migration assessment type for intergration services (SSIS)  when the destination server is a local SQL Server, only supports Azure SQL Database ou Azure SQL Managed Instance,
As stated in "assessment for migrating SQL Server Integration Service (SSIS) packages to Azure SQL Database or Azure SQL Managed Instance, by using Data Migration Assistant."  from https://docs.microsoft.com/en-us/sql/dma/dma-assess-ssis?view=sql-server-ver15
Any alternative to evaluate SSIS code compatiblity with new version?
Only option i could find was executing DTexec with Validate parameter (and i don't know if is even similar to DMA):
/Va[lidate]: (Optional). Stops the execution of the package after the validatation phase, without actually running the package. During validation, use of the /WarnAsError option causes dtexec to treat a warning as an error; therefore the package fails if a warning occurs during validation.
Best Regards,
Carlos


texts:
I'm running an two node RAC database with ASM having two DGs one for data and one for redo. I have only one copy of redo and I have the need to have another copy of redo logs.
1. How to go ahead with adding mirror copies of redo?
2. Will it demand downtime?
3. Can I add the copies which I'm going to create in the data DG?
4. I also need to create a copy for the control file. How can I do that as well?

Thanks,
Tesla.


texts: SQL Server Analysis Services - who uses them?

texts:
We all know Sql Server Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications.

Who has used this service in their projects? I need some guidance on what this really involves?


texts: Definitive list of steps for SQL Server baseline testing?

texts:
Before running a performance test / baseline for an app that uses SQL Server, I want to be able to set the instance to a "clean" state, without restarting the instance. There are steps I tend to follow, but I want to build a definitive list that is in the correct sequence, and has no redundant steps.

Does this list of steps accomplish setting SQL Server to a "clean" state?

Is the sequence logical / correct?

Are there any redundant steps?

CHECKPOINT              -- Write all dirty pages

DBCC DROPCLEANBUFFERS   -- All should be clean after checkpoint?

DBCC FREEPROCCACHE      -- Clear the plan cache

DBCC FREESYSTEMCACHE    -- Is this necessary after FREEPROCCACHE?

DBCC FREESESSIONCACHE   -- May not be necessary if distributed queries aren't used, but want to catch all scenarios

EXEC SP_UPDATESTATS     -- Refresh stats

'BEGIN TESTING!'


texts:
A partial solution is to get all the Notes (VARCHAR) where there is an imbalance in < and >. But how can I write a query for that?

For example:

Input: Hello World
Output: Valid HTML

Input: <Hello World
Output: Invalid HTML

Input: <Hello World>
Output: Valid HTML (Marking it Valid is OK for my use case)
But hey isn't that a valid HTML? It could be <name attribute>

Are there any better ways to check for "invalid" HTML where "invalid" is defined as HTML content with < followed some characters that is not a valid HTML Element as per specification?

Initially I thought I could check for <name followed by a  (Space) but HTML Attributes have syntax as <name attribute and closed by > at the end.


texts: Index for ORDER BY on aggregate in MySQL

texts:
I'm working with the following query:

SELECT *, SUM(entries_count) AS entries_count FROM leads
WHERE promo_id IN (1, 2, 3)
GROUP BY user_id
ORDER BY entries_count DESC
LIMIT 25 OFFSET 100;

It's quite fast without the ORDER BY clause, but very slow with it. I have a few compound indexes, but it only seems to want to use the index on user_id and that's it. Here's the EXPLAIN output:

| 1 | SIMPLE | leads | index | index_leads_on_promo_id_and_user_id,index_leads_on_promo_id | index_leads_on_user_id | 4 | NULL | 219647 | Using where; Using temporary; Using filesort |

I find it a bit odd that it's using a key which isn't even listed under "possible keys". Anyways, is there any way to provide a better index to improve this query, or will I need to denormalize with pre-calculated rollups?

EDIT: Here's the CREATE TABLE statement:

CREATE TABLE `leads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `promo_id` int(11) NOT NULL,
  `entries_count` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_leads_on_promo_id_and_user_id` (`promo_id`,`user_id`),
  UNIQUE KEY `index_leads_on_user_id_and_promo_id` (`user_id`,`promo_id`),
  KEY `index_leads_on_user_id` (`user_id`),
  KEY `index_leads_on_promo_id` (`promo_id`),
  KEY `index_leads_on_user_id_and_entries_count` (`user_id`,`entries_count`),
  KEY `index_leads_on_user_id_and_promo_id_and_entries_count` (`user_id`,`promo_id`,`entries_count`)
) ENGINE=InnoDB AUTO_INCREMENT=413235 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

As you can see, I've added several indexes just to experiment and see if they'd improve the query's performance, but so far no dice.


texts:
I am trying to figure out how to replicate a postgres database with sensitive data content into a database in another server and would like to run some updates on some rows so that sensitive data are randomized. I didn't find any solution with Postgres' replication hot standby.

Is it possible to achieve this with only Postgres or I would need some external services like Slony-l?

Thanks


texts: How to determine actual memory occupied by Query Cache in MySQL?

texts:
How can we check if the MySQL server is utilizing high memory/RAM ?
How much memory is occupied in total by query cache ? How much memory is fragmented ?
Basically what is occupying xGB off memory by MySQL server ?
Currently running MySQL Server version 5.7.25.


texts: Print table documentation with comments?

texts:
I have a MySQL database, and I have used MySQL Workbench to insert text into the "comment" fields of many of the columns and tables; the plan was to have the documentation stored in the database.

However, I do not know how to either a) add a comment to a column or, more importantly b) retrieve this information.

I have looked through the limited MySQL documentation on Describe, Show Columns, and Show Extensions.

For example, I would like to return this:

mysql> describe dogs;

+----------------+-------------------------------+
| Field          | Comment                       |
+----------------+-------------------------------+
| id             | primary key                   |
| Name           | Dog's name                    |
| species_id     | Foreign key to look up species|

Instead of

mysql> describe dogs;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| name           | varchar (255)| YES  | MUL | NULL    |                |
| species_id     | int(11)      | YES  | MUL | NULL    |                |

Although this would do:

mysql> describe dogs;

mysql> describe dogs;
+----------------+--------------+------+-----+---------+----------------+-------------------------------+
| Field          | Type         | Null | Key | Default | Extra          | Comment                       |
+----------------+--------------+------+-----+---------+----------------+-------------------------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |                               |
| name           | varchar (255)| YES  | MUL | NULL    |                | Dog's name                    |
| species_id     | int(11)      | YES  | MUL | NULL    |                | Foreign key to look up species|

What are my options? Does MySQL workbench export such documentation?


texts: Fetch Records alternatively from database

texts:
I have following table where each record indicates either start time or end time of a shift for staff.

The first record is for start time, and the next immediate record is for end time.

SELECT *
FROM  `recorddata`
ORDER BY  `recorddata`.`Date_Time` ASC
LIMIT 0 , 30

What i want is all start and end time together as result of query.


texts: Secondary Database Replica in Reverting/InRecovery (Long time)

texts:
Reposted from: https://www.sqlservercentral.com/forums/topic/secondary-database-replica-in-reverting-inrecovery-long-time

Hi,

One of my AG secondary replica has been in reverting/in recovery for more than a day. The database size is about 700GB. I was trying to suspend/remove the database from the AG so I could apply the differential and log backups to get it up-to-date, but SQL Server wouldn't allow me to do so because it's running a HADR_DB_COMMAND that is blocking me. Is there a way to bypass this? Or, would it be best to allow SQL Server to do its thing?

Thank you.


texts: How to get count 0 using group by if record not exist in the same table?

texts:
I need some expert advice please.
I have a table with Employees and eCodes in SQL server. I want to get the count of eCodes for each employee but in some cases an employee doesn't have a certain eCode.
Where the eCode is missing for an employee I want to return zero.
SELECT Employee, eCode, Count(*) cnt FROM Test
Group By Employee, eCode
Order by Employee, eCode

The above SQL gives me following output

David is missing eCode X and Y, which Gary and Tony has. How can I return zero for its count.
Similarly Gary and Tony is missing F and G and I want to get zero for those two.
I can use some help here.


texts:
Is there a way to have WHERE clause in COUNT ? Or how to count with some clause ?

select
  a
  b,
  count(c where t1.u='UAE') as c1
from t1

I am using MS SQL Server 2014


texts:
i have a CASE statement, which classify my Customer-Revenue and set Flag, when Revenue <> 0 is. But my problem i should search also same month record on my case statement. Logic : if two records (Revenue & Credit) exist on same month and sum of those 2 records are zero then put flag.

my Script :

    SELECT CUSTOMER,
CASE WHEN SUM(FLAG) > 0 THEN 'X' ELSE 'Y' END FLAG
FROM (
    SELECT CUSTOMER, CALMONTH, SUM(INVCD_VAL),
    CASE WHEN INVCD_VAL <> 0 THEN 1 ELSE 0 END AS FLAG
     FROM
        (
        SELECT CALMONTH, CUSTOMER, INVCD_VAL FROM CUSTOMER_HISTORY
        UNION ALL
        SELECT LEFT(CALDAY, 6), SOLD_TO, REVENUE FROM CUST_HISTORY_MIS
        )
    GROUP BY CUSTOMER, CALMONTH, INVCD_VAL
    )
GROUP BY CUSTOMER
ORDER BY CUSTOMER

My Source and Target tables look like :

Thanks alot for your answer.


texts: Implementing audit trail ms sql using stored procedure

texts:
My application would be like having category and under category, products will be there.

Three types of admin will be there. super admin (more than one admin will be available), product admin (more than one product admin) and user.

Super admin and product admin can add/delete/update product. User can view that.

Super admin and product admin can change the user permission.

Super admin can add users.

For this I need to add audit trail.

I have referred some sites like best way to implement an audit trail in sql-server?

http://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL

http://techbrij.com/audit-trail-microsoft-sql-server-quickly

My understanding from this.
This will write the audit trail whatever (based on our setting) action done to the table
For each table we need to have separate shadow table.
I have planned for the following implementation

I have audit trail types with the predefined message. For example I am having the following tables AuditTrailActions and AuditTrailMessages. My message template would be like the following,

  Product {ProductName} [{PersonId}] has been created by {Username}
  [{PersonId}]

Whenever the product is added successfully, I will call a stored procedure from there will call audit trail messages table to get the message and replace the necessary tags and update in AuditTrail Table

I will do like the same thing for all the actions.

Will be a good implementation?
If not can anyone suggest good implementation for my need?


texts: Postgres function index doesn't work correctly with regular expressions

texts:
I have this Postgres function. It forces text to be lowercase and all nulls to become empty strings, so that I more cleanly perform searches for things that don't match something, etc:

CREATE OR REPLACE FUNCTION magic_text(txt text) RETURNS text
IMMUTABLE PARALLEL SAFE
LANGUAGE SQL AS $$
  SELECT lower(coalesce(txt,''))
$$;

This function is heavily used in many different query types, so I created all of the varieties of text index for it:

CREATE INDEX index_magic_cards_on_oracle_text_magic
ON magic_cards
USING BTREE (magic_text(oracle_text)) WITH (fillfactor = 100);

CREATE INDEX index_magic_cards_on_oracle_text_magic_text_pattern
ON magic_cards
USING BTREE (magic_text(oracle_text) text_pattern_ops) WITH (fillfactor = 100);

CREATE INDEX index_magic_cards_on_oracle_text_magic_gist_trgm
ON magic_cards
USING GIST (magic_text(oracle_text) gist_trgm_ops);

CREATE INDEX index_magic_cards_on_oracle_text_magic_gin_trgm
ON magic_cards
USING GIN (magic_text(oracle_text) gin_trgm_ops)

These indexes are interfering somehow with some (but not all) kinds of complex regular expression searches. I haven't been able to determine if specific regex symbols or features cause the problem.

Here's an example (explain.depesz):

SELECT card_name
FROM magic_cards
WHERE magic_text(oracle_text) ~ '***:(?n)eldrazi\ (?!scion)';

This returns nothing, and according to the query planner, it performs a  bitmap index scan on index_magic_cards_on_oracle_text_magic_gin_trgm.

This expanded query also scans the same index, and fails to find anything (explain.depesz):

SELECT card_name
FROM magic_cards
WHERE lower(coalesce(oracle_text, '')) ~ '***:(?n)eldrazi\ (?!scion)';

However, if I force Postgres to not use my index, this query has results! (explain.depesz) The following has multiple rows of results and performs a sequential scan on the table. All I did below was change the coalesce fallback to ⌘, which shouldn't affect the results:

SELECT card_name
FROM magic_cards
WHERE lower(coalesce(oracle_text, '⌘')) ~ '***:(?n)eldrazi\ (?!scion)';

Why does using the index change the results for regular expression searches?

(Postgres 9.6.1, pg_trgm 1.3)


texts: Is there any way to connect to one server and utilize a linked server using Windows authentication and NTLM?

texts:
I currently have SQL Server 2005 (Server A) that has a SSAS 2005 linked server (Server B). The powers that be do not want to enable Kerberos authentication, so I'm stuck with NTLM.

The problem is that when developers connect from their local workstation to Server A and try to run queries against the linked server, Server B, they are not able to. They are only able to if they RDP into Server A and then run queries against the linked server, Server B. My understanding is that this is due to constraints of NTLM.

Is there any way at all I can enable the developers to connect to Server A on their local workstation and be able to run queries against the linked server?

To clarify, the user has access to both the SQL Server instance containing the linked server, as well as the SSAS catalog that the linked server points to.


texts: Create a linked server with SQL Server 2012 x64

texts:
How can I create a linked server to a folder having fox pro dbf files?

I can access the dbfs inside Visual Studio 2010, creating a connection with this properties

Connection string

Dsn=Legacy;dbq=\\192.168.48.29\dbf;defaultdir=\\192.168.48.29\dbf;driverid=533;fil=dBase 5.0;maxbuffersize=2048;pagetimeout=5

Provider

.NET Framework Data Provider for ODBC

But I don't know how to use this information with sql server 2012 express x64

I'm trying to use

EXEC master.dbo.sp_addlinkedserver @server = N'LEGACY', @srvproduct=N'Legacy', @provider=N'MSDASQL', @datasrc=N'Dsn=Legacy;dbq=\\192.168.48.29\dbf;defaultdir=\\192.168.48.29\dbf;driverid=533;fil=dBase 5.0;maxbuffersize=2048;pagetimeout=5', @provstr=N'.NET Framework Data Provider for ODBC'

in SQL Server Management Studio

Error message about not finding the dsn ...


texts: Span a record on multiple pages in MS Access

texts:
MS Access has a limitation in its Reports functionality that is max Section height is  22.75 in. (57.79 cm). I want to create a report that will use data from a single record to span on 12 pages in a specific formatting. Right now I can not put more than 2 A4 pages in Design View. Is there someway to achieve this?
Thanks


texts: Simple MySQL table copy from one server to another?

texts:
this question has kindof been answered in a number of ways. Nothing that quite fits the bill (as far as I understand it).

I have MySQL Table X on Server A. I would like an exact copy of this table, to be cloned on 3 other servers. Table X is quite big, and will get bigger. Currently 500,000 records.

Nothing gets deleted from that table, only added. So, don't really want to copy the whole big table across every time. Some kind of incremental solution, to pull across only the new record data on Table X, to Servers B, C and D. Is that possible?

I am running PHP on both servers. If it makes a difference - Server A is PHP4, and Servers B,C and D are PHP5.

If it were being copied over to only 1 other server - I could somehow mark records on Server A, as "already copied" - and only copy across records not flagged as such. I guess, I could add fields on Table X, to say "copied to Server B", "to Server C" etc. But, considering all this extra data will need to be copied to all other Servers too - it seems like a lot of dead data hanging around. Hopefully there is a better way.

I do not have root access or command line access. Some suggestions on Stackoverflow had solutions involving these.

Our old programmer, set up something across these servers before - where any change on Server A mysql, would immediately be mirrored on mysql on Server B. But, that was for the whole database, not 1 single table. And, he had access to command line etc. If there is a good solution there, I could speak to the server hosts and ask them to help me set it up?

Apologies if the question seems a bit dumb, I've never done this before (obviously). :) Any pointers or guidance you can offer will be appreciated.

Many thanks.


texts:
How, w/o rounding up, do I get 'Decimal Day' into HH:MM:SS format using SQL?
Using MariaDB 5.x, one of the DB fields contains a pseudo date-time, as day.time.

DB 'Time' col
Day
Decimal 24-hour time
HH:MM:SS Time

0.1234567890
0
.1234567890
02:57:46

1.4567890123
1
.4567890123
10:57:46

2.8901234567
2
.8901234567
21:21:46

I'm able to pull the day out from the TIME column using:  SELECT dbTime - MOD(dbTime, 1) AS 'Day'
And I can separate the decimal time using:  MOD(dbTime, 1) AS 'Decimal Day'
I can't figure out how to get 'Decimal Day' into HH:MM:SS format? I have played around with two different numbers using the following SQL, which gives me the individual HH:MM:SS numbers, but the numbers always round up whenever I try using format, cast, or convert.

Decimal Day
HH:MM:SS Time

.51923075705577
12:27:41

.52923075705577
12:42:05

SELECT .51923075705577 * 24 AS 'hour',
       mod((.51923075705577 * 24), 1) * 60 AS 'minute',
       mod((mod((.51923075705577 * 24), 1) * 60), 1) * 60 AS 'seconds';

The times shown above are the desired result.
Thanks for any assistance on this one.


texts: PostgreSQL 11: Replace function body by users from the same group

texts:
In the documentation of CREATE FUNCTION is stated that ''You must own the function to replace it (this includes being a member of the owning role).''

Two questions:

I don't understand what exactly the ''this includes being a member of the owning role'' part means.
Is it possible to have multiple users (non superusers) that can modify the body of the same set of functions? I have a user which creates the functions, tables etc (Liquibase user) and I want to add a debug user which should have the permission to modify functions created by the first user in order to test some assumptions or hotfixes?


texts: Which databases can have row and column level security based upon Active Directory?

texts:
I have various database types, Oracle 11g, MySQL 5, Sql Server 2012.  I have direct access.  I can have other databases if I need them such as PostgreSQL.

What I'm doing is protecting data at the database and table level as opposed to the application level though I there will be some mixing.

What I'm hoping to do is control all the security from Active Directory.  I don't want to do security in MySQL, security in Oracle, etc.  That is a lot of duplicate entry.

I will have a webpage send credentials to the database engine to determine which bits of data the user can see.

Thanks for ideas.


texts: SQL - Trigger to update a column as NULL when another column has a fixed value

texts:
I have a table for Seller. If seller type is 'organization', I want the MidName of Seller to be blank (NULL).
Is it possible to create a trigger that does so after insert?

Column names in table:

SellerID
Type(fixed value - either Org or Ind)
FirstName
MidName
LastName
Address
PhoneNo

If Type is "Org" then MidName must be updated to blank (even if some value is entered during Insert)


texts: How do I remove duplicate records in a join table in PostgreSQL?

texts:
I have a table that has a schema like this:

create_table "questions_tags", :id => false, :force => true do |t|
        t.integer "question_id"
        t.integer "tag_id"
      end

      add_index "questions_tags", ["question_id"], :name => "index_questions_tags_on_question_id"
      add_index "questions_tags", ["tag_id"], :name => "index_questions_tags_on_tag_id"

I would like to remove records that are duplicates, i.e. they have both the same tag_id and question_id as another record.

What does the SQL look like for that?


texts: Should I use an Index when the "name" column never changes but the "views" column is frequently updated?

texts:
I have a database table with 9254 rows in it.

These rows are all searched primarily using the "name" column on a website using php.

I was thinking of using an index on the name column to reduce load but I also have a "views" column that is updated whenever a name is searched on the front end.

So if someone does a SQL query of:

SELECT * FROM namestable WHERE name = "John Smith"

then an immediate second query is run:

UPDATE namestable SET views = views + 1 where name = "John Smith"

But also, on the result page for that query. This is then run as a seperate php file call (MySQL Full Text Search):

$sql = "SELECT name, id,
           MATCH (name,description) AGAINST ('$nameplus') AS score
      FROM namestable
      WHERE MATCH(name, description) AGAINST('$nameplus')
        AND name != '$name'
      group by name
      order by score desc LIMIT 8";

Would this directly impact indexing the name table as I read you aren't supposed to do it on rows that are frequently updated or that use MySQL Full Text Search.

I also have a page that calls almost every column in the database (to be used as front-end filters for results). So I'm honestly not sure if I should index each one of these columns as most of the content in them will remain static once set. The only one that updates constantly is the views column.

EDIT: Added more info.


texts: Fill Database With Recursive Factorial Function

texts:
I want to calculate table's all of the rows's factorials value. And then i want to insert to each row's factorial value in table. so that i create table like as below:

TABLE:FACTORIALS
------------------------------
| NUMBERS | FACTORIALS       |
|----------------------------|
| 3       |                  |
| 5       |                  |
| 6       |                  |
| 9       |                  |
| 7       |                  |
| 2       |                  |
| 1       |                  |
------------------------------

First, table has only numbers. I fill the factorial fields with call recursive function fact. Factorial function like as below.

CREATE OR REPLACE FUNCTION FACT(x IN number)
RETURN NUMBER
IS
   f NUMBER;
BEGIN
   IF x=0 THEN
      f := 1;
   ELSE
      f := x * FACT(x-1);
   END IF;
RETURN f;
END;
/

Factorial function works. And i want to select all table numbers in array. Then call factorial function with array's elements. After all i want to fill table factorials's field. For all of them i write sp like as below:

DECLARE
COUNTER INTEGER := 0;
CURSOR C_NUMBERS IS SELECT  NUMBERS FROM FACTORIALS;
TYPE N_LIST IS VARRAY(7) OF FACTORIALS.NUMBERS%TYPE;
TYPE F_LIST IS VARRAY(7) OF FACTORIALS.FACTORIALS%TYPE;
NUMBER_LIST N_LIST := N_LIST();
FACTORIAL_LIST F_LIST := F_LIST();
BEGIN

FOR N IN C_NUMBERS LOOP
      COUNTER := COUNTER + 1;
      NUMBER_LIST.EXTEND;
      NUMBER_LIST(COUNTER) := N.NUMBERS;
 END LOOP;

FOR I IN 1 .. NUMBER_LIST.COUNT LOOP
      FACTORIAL_LIST.EXTEND;
      FACTORIAL_LIST(I) := FACT(NUMBER_LIST(I));
END LOOP;

FOR K IN 1..FACTORIAL_LIST.COUNT LOOP
      UPDATE FACTORIALS
      SET FACTORIALS = FACTORIAL_LIST(K)
      WHERE FACT(NUMBER_LIST(K)) = FACTORIAL_LIST(K);
END LOOP;

END;
/

But this code fill all the factorials fields with same value. This same value is table's last elements's factorial value.The table like as below :

* FACTORIALS TABLE AFTER THE SP IS RAN
------------------------------
| NUMBERS | FACTORIALS       |
|----------------------------|
| 3       |    1             |
| 5       |    1             |
| 6       |    1             |
| 9       |    1             |
| 7       |    1             |
| 2       |    1             |
| 1       |    1             |
------------------------------

FACTORIAL table's last element 1. Code insert all field last element's factorial value namely 1. How can i insert factorial values which i calculate right place in FACTORIALS table.


texts: Jump to a given line number in pgAdmin III

texts:
I wonder whether there is any way to jump to a given line number in the "edit data" window in pgAdmin III.

I am aware that I can use the sort/filter window to add filters, but I just want to jump.


texts: Finding all date ranges that overlap a target date range

texts:
Say I wish to store when staff are on holiday (FromDate, ToDate) and then I wish to find all staff that are on holiday between two given dates (QFromDate, QToDate).

Now assume that I have many such records (more than will fit in the server’s RAM) and need to do this query often.

Now assume that I also have sick_leave tables, shift_pattern tables, pay_rate tables, etc – all that have FromDate and ToDate with the requirement to join them based on overlapping dates.

How should I store the date ranges and how can the queries be written to run fast?

(The choice of RDBMS is not fixed, but being able to run on any “standard” RDBMS is of value unless doing so has a large adverse effect.)

I have posted some answers that I have considered, but don't like!   However they may help other people.


texts: Query Simplification for better execution

texts:
Select a.item_no,
       a.item_type,
       a.item_group,
       a.uom_group,
       a.unit_price,
       c.service_category,
       c.item_no,
       b.manufacturer,
       b.issue_method,
       b.item_no,
       d.preferred_vender,
       d.mfr_catlog_no,
       d.purchasing_uom,
       d.items_per_purchase_unit,
       d.quantity_per_package,
       d.item_no,
       e.sales_uom_name,
       e.item_per_sales_unit,
       e.package_uom_name,
       e.quanti_per_package,
       e.item_no
  from Item_Master_Details a,
       Item_Master_General_Material b,
       Item_Master_General_Service c,
       Item_Master_Purchasing_Data d,
       Item_Master_Sales_Data e
 where a.item_no = b.item_no
   and b.item_no = c.item_no
   and c.item_no = d.item_no
   and d.item_no = e.item_no;

I want to simplify this query..because it gets more time to execute in database.


texts: What connection string to use to connect to network server?

texts:
I have a database on my computer running on SQL Server Express 2014, and I'd like other computers connected to my network to be able to connect to it using a C# program I wrote.

I've done the following:

1. turned off windows firewall. I know I don't need to do this completely, but I'm just debugging.

2. enabled TCP/IP in SQL Server Configuration Manager on the machine running the instance.

I can't change this for all machines. On the computer I'm trying to connect from, but not the computer hosting the database, I can't enable this. I'm not sure if that's a problem. However, on my machine I've enabled pretty much everything and restarted everything.

3. Remote connections are allowed

Using a .UDL file to test the connection, I found that on my computer, if I select MyComputerName as the server, I don't see my database listed. But if I enter MyComputerName\SQLEXPRESS, I do see the database and can connect to it with integrated windows security.

But if I log on another computer and use a .UDL to test the connection. I can connect if I enter MyComputerName as the server, but if I try to enter MyComputerName\SQLEXPRESS as the server, it tries really hard but then tells me

  [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Again, on my computer using the following connection strings work

Server=MyMachine\SQLEXPRESS;Database=MyDatabase;Integrated Security=True;
Server=MyMachine\SQLEXPRESS;Database=MyDatabase;Trusted_Connection=True;

But on another computer on the network, I get the error

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

But I know that the the server is allowed remote connections and that the instance name is running, so I'm stuck. Any ideas of where to go from here?


texts:
How do I reconnect the open files in SSMS to their network locations for change tracking?

After disconnecting my laptop from my network at work and then reconnecting through a VPN the sql script files that I have open in SSMS no longer recognize change tracking. If I want I can close and re-open the files, or save them again, and they re-establish their connection with the network path, and will then notify me if it's updated outside of my instance of SSMS, but is there a better way to just reconnect the file to the network so that it will resume it's change tracking?


texts:
When I do this:

DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.open( operation => 'EXPORT', job_mode => 'TABLE', job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'tab1.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.add_file( handle => hdnl, filename => 'exp.log', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''USERS'')');
DBMS_DATAPUMP.METADATA_FILTER(handle => hdnl,name => 'NAME_EXPR',value => 'IN (''CA_FILE'')');
DBMS_DATAPUMP.start_job(hdnl);
END;
/

And I read:

select * from table(RDSADMIN.RDS_FILE_UTIL.READ_TEXT_FILE('DATA_PUMP_DIR','tab1.dmp'));

It gives me following:

Starting "AUSER"."SYS_EXPORT_TABLE_01":
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-31655: no data or metadata objects selected for job
Job "AGSUSER"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Tue Jan 26 20:28:37 2016 elapsed 0 00:00:01

I am trying to EXPORT 1 table. What's wrong?


texts: So I have an university project that has to implement some kind of a recursive relationship like (for example - employee may be managed by other employee(https://www.sqa.org.uk/e-learning/SoftDevRDS02CD/page_49.htm)). Anyway I found it hard to think of an appropriate domain problem. Can you give me some examples? (Examples: categories of an online shop etc...)


texts:
I'm fairly green when it comes to this field. I'm a data analyst at a small hospital 8 months now, out of a 2 year degree in a new career field for me.

Situation

We have a Cobol "database" (I use the term database lightly...). It's a proprietary Hierarchical Database. We have a secondary software solution that provides ODBC SQL layer on top of it for outside access via a standard'ish interface. It's got what I think is SQL 99 compliant commands.

COBOL Database to SQL leaves a lot to be desired. Index's are set in stone. So if I want to do any query that isn't keyed on the one or two fields that are indexed (Index = Patient numbers. So if I want to query on Admit/Discharge Dates... forgetaboutit)... it's dog slow. Joins turn a 5 minute query into a 45 minute query. To many queries slow the REST of the system down causing bad things.

While the system DOES have an internal query system, it is by no means something I'd consider complete. It has it's strengths and it's weaknesses, but the main problem is co-operation with the outside world that I need to send reports too.

Question

What I'm trying to build... is a TSQL Data Warehouse. I want to create a process that pull the data into a SQL environment that I can control (add index's, use modern stuff like case statements, doesn't take 15 minutes for "simple" queries).

I'm wondering what are some good resources, tutorials, tools that the experts would suggest. My ideal situation would be a "historical" load of all data into a T-SQL database, followed by weekly loads of new data. It wouldn't be ideal for "real time" reporting, but the bulk of my reporting needs would be met with "slightly" old data.

I've been messing with ##Temp Tables, Merge into, update, etc trying to see what the times are for loads and updates and what not... but before I get too deep, I'd like to just get some general advice.


texts: Backup without using SQL Server Management Studio

texts: I am trying to take a backup on a Windows Server 2003 and SQL Server 2005 installed on it. But, there is no SQL Server Management Studio on it. How can I take full backup of a particular database.


texts: Displaying number before decimal points in oracle

texts:
I have a logic where I want to calculate and display numbers based upon some operations. The operations are below
V_CALPERCENT nvarhcar(100), v_yearMSA1 nvarchar(100),
So I want to calculate V_CALPERCENT and I have one value for v_yearMSA1 = 2
SO here goes my calculation,
V_CALPERCENT :=  (v_yearMSA1  * 2.25) / 100

Its returning me as .05
where as I want it should also display the number before decimal point. How to get it please help


texts: How to efficiently count rows in a query by limiting the number or found rows?

texts:
I have a website where I output a pagination with a limited number of pages, let's set it to 50 for example. That is, if there are actually 51 or more pages of content the 50th will be the latest that can be viewed by the users.

If a user requests a page that goes beyond the actual limit of available pages (I know it because the page's number is more than 50 or because the query for that page returns 0 rows) I want to display the last available page instead. So in this case I'll display the 50th page if there are 50 or more pages available, or I'll just calculate how many pages that are and show the latest.

Right now to do this I count how many rows are found in the query, for example:

SELECT COUNT(*) FROM my_table WHERE codition1 > X AND condition2 > Y

The problem here is that this query may even returns a count of 200,000 rows, when I actually just need to know if there are enought rows for the 50th page, or if there are less pages I need to know what's the latest.

Sometimes this query may take quite some time to iterate across the whole database to return a number that I actually not entirelly need, so I'm assuming that just counting the first (50*rows_per_page) matched rows would be a lot more efficient.

To accomplish this I've tried:

SELECT COUNT(*) FROM my_table WHERE codition1 > X AND condition2 > Y LIMIT 50*rows_per_page

But I actually get the same result of the first query.

What's the correct way, and possibly the most efficient way, to do this?


texts:
I'm supporting an existing data interface that is a series of SSIS packages (SQL 2008 R2) for sending and receiving data with a third party.  The communication is being done through FTP, and it uses a SSIS Script Task to call the FtpClientConnection objects.

This has been working fine for years, and then a few days ago it suddenly started randoming zero-length files.  It runs every 15 minutes, and if it's sending 10 files, 2 or 3 or 5 of them will be blank.  The source files that it is trying to send on our side is good, and if we resend it through another FTP client (like FileZilla), they transmit fine.

Again, this just started happening at a specific time and has been happneing since then.  No updates or installations were done on that server before then (the server has been rebooted after that, but the problem started before the reboot and it had not been rebooted for a week prior to that).

    Dim FtpServer As String = Dts.Variables("FtpServer").Value
    Dim FtpUser As String = Dts.Variables("FtpUser").Value
    Dim FtpPass As String = Dts.Variables("FtpPass").Value
    Dim FtpInbound As String = Dts.Variables("FtpInbound").Value
    Dim LocOutbound As String = Dts.Variables("LocOutbound").Value
    Dim FtpFile As String = Dts.Variables("FtpFile").Value
    Dim DistributorID As Integer = Dts.Variables("DistributorID").Value
    Dim UploadFile As String = LocOutbound & FtpFile

    Try
        Dim oCnMgr As ConnectionManager = Dts.Connections("FTP")
        Dim oFtp As FtpClientConnection = New FtpClientConnection(oCnMgr.AcquireConnection(Nothing))

        oFtp.ServerName = FtpServer
        oFtp.ServerUserName = FtpUser
        oFtp.ServerPassword = FtpPass
        oFtp.Retries = 5
        oFtp.UsePassiveMode = True

        If File.Exists(UploadFile) Then
            Dim aUp As New ArrayList
            aUp.Add(UploadFile)

            oFtp.Connect()
            oFtp.SetWorkingDirectory(FtpInbound)
            oFtp.SendFiles(aUp.ToArray(GetType(String)), FtpInbound, True, True)
            oFtp.Close()

            PostFtpFile(" UPDATE FtpUpload SET Uploaded=1 WHERE Filename='" & FtpFile & "' AND DistributorID=" & DistributorID.ToString & " ")
        End If
        ...

The FTP client connection is set up with a 60-second timeout, passive mode, and 5 retries.  It was originally set to a chuck size of 1, but I also tried 10 and 100 chunk sizes with no improvement.  The average file is is between 600 bytes and 2 KB.

Is there any additional way to troubleshoot this?  The task thinks it's successful, but I can't find any way to get detailed diagnostics from the SSIS component.  Is there a log file that gets generated anyhere, or a way to get back from the FTP component how many bytes it actually sent?  Many other FTP component I've worked with outside of SSIS had options to record detailed logs of the transmission.

Any help or guidance would be appreciated.  This system is scheduled to be rewritten in the next few months, but as of now it has brought my clients ecommerce business to a halt.

Thanks


texts: MongoDB service not starting on RedHat - missing 'service_uri'

texts:
I'm on a Red Hat Enterprise Linux Client release 5.4 (Tikanga) machine

I created my /etc/yum.repos.d/10gen.repo like this:

[10gen]
name=10gen Repository
baseurl=http://downloads-distro.mongodb.org/repo/redhat/os/x86_64
gpgcheck=0
enabled=1

I installed MongoDB with: sudo yum install mongo-10gen mongo-10gen-server

but when I run: sudo service mongod start

I get: Missing SERVICE_URI environment variable

help! :)


texts: Performance of a View with Hundreds of Left Joins

texts:
I have a table with 100+ columns, each containing a numeric value that maps to a string value in another table. When trying to create a view for the table that contains these string values, I'm getting a significant performance hit from all the joins. Given that table structures can't be changed, is there a better way to map all these strings in from the other table?

--MyTable--
Id  Name    attr1   attr2   attr3   ... attr200
1   First   1       2       4           2
2   Second  3       3       5           1

--StringMap--
AttrName    Value   Label
attr1       1       Small
attr1       2       Medium
attr1       3       Large
attr2       1       A
attr2       2       B
attr2       3       C
...
attr200     1       Purple
attr200     2       Blue
attr200     3       Green

select
  t.Name,
  s1.Label as attr1name,
  s2.Label as attr2name,
  s3.Label as attr3name,
  ...
  s200.Label as attr200name
from MyTable t
  left join StringMap s1 on s1.AttrName = 'attr1' and s1.Value = t.attr1
  left join StringMap s2 on s2.AttrName = 'attr2' and s2.Value = t.attr2
  left join StringMap s3 on s3.AttrName = 'attr3' and s3.Value = t.attr3
  ...
  left join StringMap s200 on s200.AttrName = 'attr200' and s200.Value = t.attr200


texts: is it safe to grant permission to all users in Microsoft sql server

texts:
I have a global production server and database, as I all users should have access to it by remote desktop.
Is it possible to grant NTFS permission on all database files (mdf+ldf files) to all users? something like

everyone can full access to it.

note that : we didn't need to share anything by file sharing service.
our app admins can remote and then if needed they all can detach-attach all database if they want. then they want to move or change location of mdf or ldf if they want
my suggestion is we can grant full access to system server admins.
but our dba said:
we grant permission to everyone and it didn't have any risk
is this safe?


texts: Why aren't some spid values showing up in sys.dm_exec_requests?

texts:
I am executing the following dmv query against SQL Server 2014 to find all activity on a server and any blocking activities:

SELECT Distinct
   ...
FROM sys.dm_exec_requests er
    OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
    LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
    LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
WHERE st.text IS NOT NULL And DB_Name(er.database_id) IN ('Vincent', 'Vincent_Audit')
ORDER BY BlockingSessionId Desc, SessionId

Sample results of this query are as follows:

You can see that session 120 is blocked by 112 and 112 is blocked by 69. However, process 69 doesn't appear because it doesn't exist in sys.dm_exec_requests.

So, the following query returns no results:

SELECT *
FROM sys.dm_exec_requests
Where session_id = 69

Anyone know why this would be?


texts: MSSQL - How to support aggregate pushdown on indexed view with SUM(decimal)

texts:
I have an indexed view with non-clustered columnstore index, e.g.

CREATE VIEW [dbo].[SalesAggregated] with schemabinding AS
        SELECT
            BusinessDate,
            ItemId
            ,COUNT_BIG(*) AS NumberOfRows
            ,SUM(Gross) AS Gross
        FROM [dbo].[myTable]
        GROUP BY BusinessDate, ItemId

Where Gross in underlying table is defined as decimal(18,6), so it supports aggregate pushdown. But result of the SUM in the view is decimal(38,6), which is too big for aggregate pushdown.

I can't cast it by using CAST(SUM(Gross) as decimal(18,6)) because that's not supported in indexed views, I get this error when I try to create unique clustered index:

  Cannot create the clustered index 'UX_SalesAggregated' on view
  'DemoDB.dbo.SalesAggregated' because the select list of the view
  contains an expression on result of aggregate function or grouping
  column. Consider removing expression on result of aggregate function
  or grouping column from select list.

Converting fields to money datatype in underlying table seems like one of the possible solutions, but I can't do that with all fields as some of them requires higher precision than 4 decimal points.

I also need the automatic indexed view matching working, so that the indexed view is automatically used if possible when the underlying table is queried. That probably wouldn't be possible when I would create a view that casts decimals to money in between the table and indexed view.

For example, I want to achieve that queries like

SELECT SUM(Gross)
FROM myTable
WHERE BusinessDate = '2019-08-14';

Will use columnstore index scan on the indexed view and that the aggregation will be done within this scan. That isn't now possible because the Gross in SalesAggregated is decimal(38,6) which doesn't support aggregate pushdown. As a result, query optimizer (incorrectly) rather scans columnstore from unaggregated table where aggregate pushdown is possible.

Is there any solution how to achieve that?

UPDATE

So the problem is only when the PK column from the underlying table isn't part of the GROUP BY in indexed view definition. Example below from Randi works, but following code fails on the error mentioned above:

CREATE TABLE [dbo].[myTable](
    [ItemId] [int] PRIMARY KEY NOT NULL,
    [BusinessDate] [date] NULL,
    [Gross] [decimal](18, 6) NULL
);

GO

CREATE VIEW [dbo].[SalesAggregated] with schemabinding AS
        SELECT
            BusinessDate
            ,COUNT_BIG(*) AS NumberOfRows
            ,CAST(SUM(Gross) as [decimal](18, 6))  AS Gross
        FROM [dbo].[myTable]
        GROUP BY BusinessDate
GO

CREATE UNIQUE CLUSTERED INDEX CX_BusinessDate ON dbo.[SalesAggregated](BusinessDate);

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesAggregated] ON [dbo].[SalesAggregated]
(
    [Gross],
    BusinessDate
);


texts: MongoDB: Can I perform a second query if the first doesn't match

texts:
When querying MongoDB is it possible to perform a query and if it isn't matched perform a second query. I could perform an $or which will do booth queries and return the results for booth but I only need to perform the second query if the first doesn't match.

Let say my BSON documents look like:

[
  {"id": 1, "value": "foo"},
  {"id": 2, "value": "bar"}
]

I need to return "id": 1 but if it doesn't exist I need to return "id": 2


texts:
I am using Jamf to manage 25K Macs and 13K iOS devices. Every time I try to update iOS apps or configuration profiles for a large number of devices my database gets overwhelmed. I have MySQL 5.6 running on Windows Server 2008R2 with 16 cores and 32GB RAM. I have tried adjusting things like query_cache or thread concurrency without any success. Any help would be greatly appreciated.

innodb_buffer_pool_size = 25G

--------------
ROW OPERATIONS
--------------
72 queries inside InnoDB, 0 queries in queue
71 read views open inside InnoDB
Main thread id 2572, state: flushing log
Number of rows inserted 269699, updated 352837, deleted 170748, read 2807450339
9.11 inserts/s, 8.43 updates/s, 6.83 deletes/s, 64676.96 reads/s


texts:
I am trying to access a SQL Server 2014 database from cmd. I am using the literal command below where the only thing I am changing is the servername:

sqlcmd [ /E ] [ /S servername ]

However I get an error:

  Sqlcmd: '[': Unknown Option. Enter '-?' for help.

Any idea what I am doing incorrectly?


texts: Calculate Quantity Based on First in, First Out (FIFO)

texts:
I'm trying to get result of quantity based on FIFO, with 2 tables below:

Table Purchase:

| PO    | Date         | Quantity | Item |
|-------|--------------|----------|------|
| PO001 | 01-Jan-2016  | 3        | AO21 |
| PO002 | 10-Jan-2016  | 7        | AO21 |
| PO003 | 01-Feb-2016  | 3        | AO21 |

Table Stock:

| SO    | Date        | Quantity | Item |
|-------|-------------|----------|------|
| SO001 | 02-Jan-2016 | 2        | AO21 |
| SO002 | 11-Feb-2016 | 8        | AO21 |
| SO003 | 12-Feb-2016 | 6        | AO23 |

I want output to be like this :

| SO    | PO    | Quantity |
|-------|-------|----------|
| SO001 | PO001 | 2        |
| SO002 | PO001 | 1        |
| SO002 | PO003 | 7        |

Do you have any idea of query for view this output? Result from calculated SO and PO in row. some more explanation:

  Where do the 2, 1, 7 come from in the wanted result?

From stock and purchase. The first (by date) stock value for item A021 has 2 and the first purchase (PO001) needs 3, so stock sold 2 and we get this row in the result:

| SO001 | PO001 | 2        |

We still need 1 more for the purchase and then next stock value is 8. So this purchase is completed and we get 1 (and 7 left in stock):

| SO002 | PO001 | 1        |

The next purchase (PO002) needs 7 and we have exactly 7 left, so the purchase is completed (and 0 stock left for that item). We get:

| SO002 | PO003 | 7        |

Purchase PO003 needs 3 but there is no stock left, so we get no rows in the result for that purchase.


texts: Formatting the output of SQL Server query

texts:
I am new to SQL Server and SSRS...and am trying to get a matrix report formatted correctly.

My query is:

select e.name, ea.element,
    case when ea.element = 'projected_status' then 'Projected Status'
         when ea.element = 'projected_spstatus' then 'Projected Sp Status'
         when ea.element = 'day1_status' then 'Day 1 Status'
         when ea.element = 'day1_spstatus' then 'Day 1 Sp Status'
    end as FIELD,
    el.value
from employeelocation el
join employee e on e.employeeid = ea.employeeid
join employeeattribute ea on ea.attributeid = el.attributeid
where ea.attributeid in (888,899);

and I am getting this information returned:

+--------------+-----------+-----------+-----+
| Barry Green  | Projected | Status    | 484 |
| Barry Green  | Projected | Sp Status |  29 |
| Barry Green  | Day 1     | Status    | 589 |
| Barry Green  | Day 1     | Sp Status |   2 |
| George Cart  | Projected | Status    | 324 |
| George Cart  | Projected | Sp Status |  12 |
| George Cart  | Day 1     | Status    | 299 |
| George Cart  | Day 1     | Sp Status |   0 |
+--------------+-----------+-----------+-----+

What I want to create is an output like this:

I can create the basic matrix report in SSRS, but I don't know how to format the groupings.  I don't know if this is something that I need to do in my query or if there's a way to achieve this through format options in SSRS.

I just need to be pointed in the right direction.  Any assistance is greatly appreciated.

Thank you!


texts:
I'm using PostgreSQL 9.6 on Linux.
I got an error when I do a test on chr() function.

postgres=# select chr(1199111);
ERROR:  requested character too large for encoding: 1199111
postgres=# select chr(55296);
ERROR:  requested character not valid for encoding: 55296
postgres=# select chr(100000);
 chr
-----

(1 row)

Here, as you can see with value 100k it worked (didn't raise error), however others didn't. I'm curious about that. Could someone please explain to me why ?

I attach test script

do
$$
declare
str text ;
begin
for i in 1..1200000
loop
begin
select chr(i) into str;
exception when others then raise notice '=> i: %     => str: %', i , str ;
#exit; -- you can uncomment this star key
end ;
end loop;
end
$$ ;

UPDATED

postgres=# show server_encoding;
 server_encoding
-----------------
 UTF8
(1 row)


texts: Downgrading from AWS Aurora MySQL 5.7 to AWS Aurora MySQL 5.6

texts:
We are trying to downgrade from AWS Aurora MySQL 5.7 to AWS Aurora MySQL 5.6 due to some issues. But AWS RDS doesn't allow Aurora MySQL 5.7 snapshot restore to Aurora MySQL 5.6 instance/cluster. Does anyone face same problem? How can we restore it to old version?

Thanks,
Sagun


texts: What means Command column in sp_who2 and text column in sys.dm_exec_sql_text?

texts:
I have a script that shows commands being fired in SQL Server 2008 database as follows:

--shows who is active and executed commands
declare @SPID int=null,
    @Status varchar(max) = null,
    @LOGIN varchar(max) = null,
    @HostName varchar(max) = null,
    @DBName varchar(max) = NULL,
    @Command varchar(max) = null

declare @sql varchar(max)=''

set @sql='use master;DECLARE @Table TABLE(SPID INT,Status VARCHAR(MAX),LOGIN VARCHAR(MAX),HostName VARCHAR(MAX),BlkBy VARCHAR(MAX),DBName VARCHAR(MAX),Command VARCHAR(MAX),CPUTime INT,DiskIO INT,LastBatch VARCHAR(MAX),ProgramName VARCHAR(MAX),SPID_1 INT,REQUESTID INT);INSERT INTO @Table EXEC sp_who2;

SELECT  w.SPID,w.Status,w.LOGIN,w.HostName,w.BlkBy,w.DBName,w.Command,r.estimated_completion_time/60000.0 as remaining_minutes, r.percent_complete,r.total_elapsed_time/60000.0 as minutes,r.reads,r.writes,t.text,w.CPUTime,w.DiskIO,w.LastBatch,w.ProgramName,w.SPID_1,w.REQUESTID    FROM @Table w
inner join sys.dm_exec_requests AS r on w.SPID=r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t WHERE 1=1'
    if @SPID is not null
        set @sql=@sql+' and SPID='+@SPID
    if @Status is not null
        set @sql=@sql+' and upper(Status) like ''%'+upper(@Status)+'%'''
    if @LOGIN is not null
        set @sql=@sql+' and upper(LOGIN) like ''%'+upper(@LOGIN)+'%'''
    if @HostName is not null
        set @sql=@sql+' and upper(HostName) like ''%'+upper(@HostName)+'%'''
    if @DBName is not null
        set @sql=@sql+' and upper(DBName) like ''%'+upper(@DBName)+'%'''
    if @Command is not null
        set @sql=@sql+' and upper(Command) like ''%'+upper(@Command)+'%'''

exec (@sql)

I have noticed that there are DDL commands for creating a procedure, but I am not sure if the procedure is really being created or if this is just an information to tell which procedure was called.

Does anyone know what UPDATE really means in Command column?
And the text column from sys.dm_exec_sql_text: is ist a called procedure or a procedure being altered/created?


texts: Chaning tempdb settings on production database

texts:
I have to change the initial size and autogrowth of my tempdb on production. Along, with that I have to add new files to tempdb. What is the right way to do it? As it is production and I don't want to take any risk.
Can I directly go to temdb properties and can change the settings/T-SQL or do I have to consider other things also?


texts:
I am working on making a database that queries the daily balance of a stock portfolio. The balance would be calculated as (sharesA x priceA)+(sharesB x priceB), etc. for each day.
I have a pricesclose table like below:
date       | ticker | close
2020-01-02 | IVV    | 326.32
2020-01-03 | IVV    | 323.81
2020-01-06 | IVV    | 325.09
2020-01-07 | IVV    | 324.20
2020-01-02 | IEF    | 110.73
2020-01-03 | IEF    | 111.47
2020-01-06 | IEF    | 111.35
2020-01-07 | IEF    | 111.19

And a transactions table:
id | ticker | transaction_type | shares | price  | transaction_date
1  | IVV    | buy              | 18     | 324.98 | 2020-01-02
2  | IEF    | buy              | 36     | 110.69 | 2020-01-02
3  | IEF    | sell             | -6     | 111.35 | 2020-01-06

What I want is:
date       | ticker | shares | close  | totalshares | marketvalue
2020-01-02 | IEF    | 36     | 110.73 |          36 | 3986.28
2020-01-02 | IVV    | 18     | 326.32 |          18 | 5873.76
2020-01-03 | IEF    |        | 111.47 |          36 | 4012.92
2020-01-03 | IVV    |        | 323.81 |          18 | 5828.58
2020-01-06 | IEF    | -6     | 111.35 |          30 | 3340.5
2020-01-06 | IVV    |        | 325.09 |          18 | 5851.62
2020-01-07 | IEF    |        | 111.19 |          30 | 3335.7
2020-01-07 | IVV    |        | 324.2  |          18 | 5835.6
2020-01-08 | IEF    |        | 110.93 |          30 | 3327.9
2020-01-08 | IVV    |        | 325.85 |          18 | 5865.3

But this is what I'm getting:

Here is my query.
SELECT
  pricesclose.Date
 ,pricesclose.ticker
 ,transactions.shares
 ,pricesclose.close
 ,(pricesclose.close * transactions.shares) AS mktvalue,
 ,SUM(shares) OVER(partition by pricesclose.ticker order by pricesclose.date)
FROM
  pricesclose
LEFT JOIN
  transactions
    ON pricesclose.ticker = transactions.ticker
        AND pricesclose.Date = transactions.transDate
ORDER BY
  pricesclose.Date

For some reason, the SUM OVER isn't totaling each ticker properly. My other issues is getting the mktvalue column to reference the sum column. I am also unsure of the best way to have the total balance in one line instead of six like it's currently doing.
Please let me know what is wrong with my SUM OVER. Or if there is a better way to execute this, I am open to that as well.


texts: Group by but preserve statistical probability of value in limit

texts:
Consider a table in which a specific column is not unique:

name
--------
john
paul
paul
george
ringo
paul
paul
paul
paul

How might one return N users by random, but preserve the statistical likelyhood that the user with the most entries is most likely to be returned? This would be similar to actually drawing N names by way of slips of paper from a hat, with the stipulation that if I draw a slip with the same name as one already drawn that I would discard it and draw another.

If I use GROUP BY and LIMIT, then MySQL first removes the dupes, then performs the limit. Note also that there are many fields returned, so using DISTINCT on the field list is not an option.

Of course, I would prefer to do this is a single query and not loop over a query. Note also that the database might contain less than N entries, in which case obviously each unique user will be returned.


texts: From RAC to Standby RAC With Active Dataguard

texts:
I have oracle  database 11.2.0.3 with RAC feature  , with 2 node on oracle linux 6.4 :

ol6-112-rac1 and ol6-112-rac2

I want to create active data guard with RAC feature from this database , these active dataguard with rac option has 2 node :

ol6-rac1-guard,ol6-rac2-guard

I want to use this document for creation active data guard from rac database to rac database :

http://www.oracledba.org/11gR2/dr/11gR2_dataguard_RAC_to_RAC.html#Create_Physical_Standby

I do the steps in this document , all of things is ok but in step " Start
the Managed Recovery Process "  after i want to switch log in primary ,
switch log is not occurred on standby database .
After " tail of alert log " on standby database and  executing " Switch log " on primary , this error is occurred :

tail -f  /ora01/app/oracle/diag/rdbms/racguard/racguard1/trace/alert_racguard1.log
Wed Feb 17 04:37:57 2016
Media Recovery Waiting for thread 1 sequence 76
Error 12154 received logging on to the standby
FAL[client, USER]: Error 12154 connecting to rac for fetching gap sequence

This is my mistake , can any one help me ?


texts:
I'm working on a project for a database that contains, in the best way, the nutritional values of foods. They are divided according to type of food, eg Breakfast, Lunch, Dinner. For portions of the food, for example ml, g, etc. oz. That have the name of each food and all of its nutritional values.

Like this.

Food
==========
ID
NAME

NUTRITIONAL_FACTS
==========
ID
NUTRITIONAL_FACTS_FOOD_ID
KCAL
FAT
SUGAR
PROTEINS

PORTIONS
==========
ID
PORTION
FOOD_ID

TYPE_FOOD
=========
ID
TYPE_FOOD
FOOD_ID

Could someone tell me which solution would be most appropriate for the design of this database?


texts: Date format on the output of cmdshell dir C:\ /TC

texts:
I would like to ask you guys if anyone of you had this trouble before.

In SQL I read the create time of the file like

exec master.dbo.xp_cmdshell 'dir C:\ /TC'

which gives me;

07/14/2009  04:20    <DIR>          Windows

As you noticed the date format of this is mm/dd/yyyy although it's set to dd/mm/yyyy format in region settings.

Same script on different PC returns

14/07/2009  04:20    <DIR>          Windows

Is there any setting for this you guys aware ?
And is there any method to change the format of create time of the
command ?


texts: Do I add read committed after SET TRANSACTION ISOLATION LEVEL SERIALIZABLE?

texts:
Inside a stored procedure, I have the following : ( sql server 2008 )

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 BEGIN TRANSACTION getStuff
 BEGIN TRY
    /*  some selects, updates, etc, etc. */
    ....
    COMMIT TRANSACTION getStuff
 END TRY
 BEGIN CATCH
   ...
 END CATCH

Since this is transaction based, my thought was the rest of the database connections will not be affected by the SERIALIZABLE.

Do I need to implicitly set isolation level to read committed after my commit? Will this adversely effect other connections between my application server and database server?


texts: mysql multi-source replication to single slave from Debian repo's?

texts: I've been experimenting with mysql 5.7's feature to backup multiple sources to a single slave, but have legacy db's on source servers where I'm not sure if upgrading to 5.7 will break things, and also I want to use standard packages in Debian repositories to more standardized support. Does anyone have experience doing something like this, and/or is there a better way to approach it?


texts: Sql server snapshot isolation overhead when read_committed_snapshot is enabled

texts:
When SQL Server's read committed snapshot is on (and thus some amount of row versioning is necessary), is there any additional overhead caused merely by enabling snapshot isolation?

Snapshot isolation causes some overhead by making rows a little larger and requiring some tempdb to store previous versions until commit, even for non-snapshot transactions.  As I understand it, this infrastructure is also necessary for read-committed-snapshot.  Can I assume therefore that enabling snapshot isolation has no additional overhead for non-snapshot transactions whenever read committed snapshot is already on?


texts:
I have a collection that I've been trying to shard, but I can't get it to shard with the index that I gave it.

The collection in question uses a hashed index, which I created using:

db.bar.ensureIndex({"_id": "hashed"}, {"background": 1})

But when I try to shard said collection, using:

sh.shardCollection("foo.bar", {"_id": "hashed"})

I get an error saying:

command failed: { "ok" : 0, "errmsg" : "shard keys must all be ascending" }

Does this mean that the data I have in collection bar needs to be ascending or that the version of Mongo that I'm using only supports range shard-keys?


texts: Table column references partial key of table B

texts:
In my database (MS SQL Server) I use language specific texts. So I defined table Texts as follows:

 CREATE TABLE [dbo].[Texts](
    [Id] [int] NOT NULL,
    [language] [nchar](5) NOT NULL,
    [text] [nvarchar](max) NULL,
 CONSTRAINT [PK_Texts] PRIMARY KEY CLUSTERED
(
    [Id] ASC,
    [language] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[Texts]  WITH CHECK ADD  CONSTRAINT [CK_Texts_language]
CHECK  (([language]='en-US' OR [language]='de-DE'))

ALTER TABLE [dbo].[Texts] CHECK CONSTRAINT [CK_Texts_language]

These texts should be used anywhere in my tables, e.g. here:

CREATE TABLE [dbo].[MyObject](
    [id] [int] NOT NULL,
    [name] [int] NOT NULL,
    [comment] [int] NOT NULL,
 CONSTRAINT [PK_PlantDescription] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

How can I specify the constraint that a value of MyObject.name or MyObject.comment should be a value of the partial key Texts.Id?

There may be several texts for one id, with different languages. So (Id, language) is unique, not Id itself.

EDIT:
Here is an example for table Texts:

Id  language    text
1   de-DE   Holunder
1   en-US   elder
2   de-DE   Von diedem Busch kann man sowohl die Blüten als auch die Früchte verwerten.
2   en-US   From this bush you can use both flowers and fruits.

and table MyObject:

Id  name    comment
512 1       2

Together with UDF

CREATE FUNCTION [dbo].[GetText]
(
    @id int,
    @language nchar(5)
)
RETURNS nvarchar(MAX)
AS
BEGIN
    Declare @result nvarchar(max);

    Select @result = text from dbo.Texts where id = @id AND [language] = @language;
    if @result is NULL
    BEGIN
        Select TOP 1 @result = text from dbo.Texts where id = @id ;
    END
    return @result;
END

With these definitions and the following query

SELECT [Id]
      ,[dbo].[GetText]([name], 'en-US') AS Name
      ,[dbo].[GetText]([comment], 'en-US') AS Comment
  FROM [dbo].[PlantDescription]

I get the result:

Id  Name    Comment
512 elder   From this bush you can use both flowers and fruits.

You see: When I delete row (Id = 1), there will be orphaned rows that I have to delete by a trigger.

Or is there a standard way for language specific texts that I didn't find in the inetrnet?


texts:
I currently have a master postgresql 9.4 server containing many databases on Ubuntu 14.04.

I tried to use Barman to setup backup, but would like to set up streaming replica so that when the master goes down, I can promote the standby replica to be the new master. I looked in to repmgr, but this will only produce a read-only hot standby server. Does this mean I should go with multi-master solution if I want to promote standby to accept write transaction as well?

I am envision the system to be

 M1 (master)  -------------via ???-------------- S1 (standby/another master)
   |                                                 |
   |                                                 |
   |via Barman                                       | via Barman
   |                                                 |
 B1 (backup server#1)                            B2 (backup server#2)

Ideally, after the M1 goes down, I can promote S1 to be the new master and it can accept read/write transaction not just read-only. Once we bring back M1, M1 can stays as standby but will streaming with S1.

Is postgres 9.4 BDR (Bi-Directional Replication) a good solution to stream between M1 and S1? Or is there any commercial product can do this?

I am not a DBA, and would really appreciate your opinions.

Thank you so much!


texts:
I'm moving a database to SQL Azure and with that I lose the maintenance plans so I am going to use a Worker Role to schedule them. When I open the properties of the Rebuild Index Task and click on View T-SQL I noticed that it generates the T-SQL on the fly for all the indexes in the Database. If I copy the generated T-SQL I will have to maintain it every time I add a new Index.

Is there a way to do the same that the Task is doing by using T-SQL? Iterating over all indexes and creating the T-SQL for Rebuilding the index on the fly?


texts: Delete from table where multiple fields match select subquery from other table

texts:
I want to delete an entry in a table where multiple fields match the results of another select subquery which takes data from another table.

This is what I have so far, though it doesn't work:

DELETE FROM table1
WHERE table1.id IN
   (SELECT id
   FROM table1 a JOIN table2 b
   ON a.field1 = b.field1
   AND a.field2 = b.field2
   AND a.field3 = b.field3
   AND b.id = ?
   )


texts: How different clauses treat Null/Unknow in SQL Server 2012?

texts:
(relatively new to SQL and SQL Server)
I am reading SQL Server 2012 T-SQL Fundamentals by Itzik Ben-Gan. Autor states that:

  SQL has different treatments for UNKNOWN in different language
  elements

So, I went through SQL Server documentation, and in particular WHERE clause, but could not find anything specific on how NULL/UNKNOWN is treated.

Could someone point me to correct document or point me to what I am reading wrong in the documentation ? (I highly suspect that answer to my question is buried deep somewhere in the SQL Standard, but it might be way too complex for me, at least at this point.)


texts: How to design a database for non-time invariant data

texts:
I am looking at designing a database where entries will probably not be time-invariant. For example, it contain entries of a customer company. The problem with a customer company is that many things can happen to it. The company can split, merge, move, go bankrupt, be bought, buy another company and so on. If I want to track interaction with this company over time, how should I design the database to be robust against all these things? I will also most probably have people in the database. These are also volatile - they resign, change job and so on.

So what is the best practice for working with non-time invariant data in a relational database? Or should I use some other database scheme?


texts: Trying to optimize query for performance (MySQL)

texts:
So I have this table that has a lot of foreign keys but when I will a record, I need to join all on those tables because I generally need those fields for the record to make any sense.  The basic query looks like:

SELECT i.id, i.`key`, i.title, i.description,
CONCAT(ru.firstName, ' ', ru.lastName) as `name`,
CONCAT(au.firstName, ' ', au.lastName) as `name`,
p.title, pc.title, pva.title, pvo.title, pvf.title, i.durationEstimate,
i.storyPoints, i.dueDate, isl.title, i.rejectionCount,
CONCAT(uc.firstName, ' ', uc.lastName) as `name`,
i.createdTimestamp, i.updatedTimestamp, it.title, isss.title
FROM ProjectManagement2.Issues i
INNER JOIN Users ru ON ru.id = i.reporterUserUsername
INNER JOIN Users au ON au.id = i.assignedUserUsername
INNER JOIN Projects p ON p.id = i.projectTitle
INNER JOIN ProjectComponents pc ON pc.id = i.projectComponentTitle
INNER JOIN ProjectVersions pva ON pva.id = i.affectedProjectVersionTitle
INNER JOIN ProjectVersions pvo ON pvo.id = i.originalFixedProjectVersionTitle
INNER JOIN ProjectVersions pvf ON pvf.id = i.fixedProjectVersionTitle
INNER JOIN IssueSecurityLevels isl ON isl.id = i.issueSecurityLevelId
INNER JOIN IssueTypes it ON it.id = i.issueTypeId
INNER JOIN IssueStatuses isss ON isss.id = i.issueStatusId
INNER JOIN Users uc ON uc.id = i.creatorUserUsername
LIMIT 50000, 100;

This query takes about 880ms to run after I run it the first time (which take a few seconds).  The EXPLAIN of this query shows:

1   SIMPLE  pvo index   PRIMARY title   98  NULL    22  Using index
1   SIMPLE  i   ref     reporterUserUsername,assignedUserUsername,projectTitle,projectComponentTitle,affectProjectVersionTitle,originalFixedProjectVersionTitle,fixedProjectVersionTitle,issueSecurityLevelId,creatorUserUsername,issueTypeId,issueStatusId
       originalFixedProjectVersionTitle 5   ProjectManagement2.pvo.id   1136    Using where
1   SIMPLE  isl ALL     PRIMARY NULL    NULL    NULL    5   Using where; Using join buffer
1   SIMPLE  isss eq_ref PRIMARY PRIMARY 4   ProjectManagement2.i.issueStatusId  1
1   SIMPLE  pc  eq_ref  PRIMARY PRIMARY 4   ProjectManagement2.i.projectComponentTitle  1
1   SIMPLE  pva eq_ref  PRIMARY PRIMARY 4   ProjectManagement2.i.affectedProjectVersionTitle    1
1   SIMPLE  p   eq_ref  PRIMARY PRIMARY 4   ProjectManagement2.i.projectTitle   1
1   SIMPLE  pvf eq_ref  PRIMARY PRIMARY 4   ProjectManagement2.i.fixedProjectVersionTitle   1
1   SIMPLE  ru  eq_ref  PRIMARY PRIMARY 4   ProjectManagement2.i.reporterUserUsername   1
1   SIMPLE  au  eq_ref  PRIMARY PRIMARY 4   ProjectManagement2.i.assignedUserUsername   1
1   SIMPLE  uc  eq_ref  PRIMARY PRIMARY 4   ProjectManagement2.i.creatorUserUsername    1
1   SIMPLE  it  eq_ref  PRIMARY PRIMARY 4   ProjectManagement2.i.issueTypeId

The big issue is when I add a order by to the query, then it take several seconds after the first query.

Is there anything I can do to optimize this query (if you need any more information please let me know)?


texts: How to add a missing database to an AlwaysOn Availability Group

texts:
Setup:
Microsoft SQL Server 2012, and we have 3 production servers(prod1, prod2, prod3) with AlwaysOn.

We recently added a new database and the new database was added to our Availability Group(named SQG01) on servers prod1, prod3.  For whatever reason the new database was not added on prod2.  All other existing databases on prod1,2,3 are all setup and running correctly.

Before adding the new database to the AG on prod2, should I do a restore to "catch up" the new database on prod2 so that it's at least close to the other 2.  We do nightly backups of our production(for restoring to dev/qa environments), so I would use the previous night and it shouldn't miss too many records.

Or should I just add it in and let AlwaysOn sync it up.

I'm not too familiar with AlwaysOn.


texts: Rename interval partitioning system generated partition names

texts:
I have an existing table which I partitioned (interval partitioning on a daily basis) using dbms_redefnition.

PARTITION BY RANGE ("TIME_ID") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(PARTITION "p1_1"  VALUES LESS THAN (TO_DATE(' 01-jan-2013','DD-MON-YYYY' )))

This gives me a new system generated partition names every day (like SYS41, SYS42, ...).

I have two questions:

I want a script which should run daily and change this system generated name to custom name (like "01-jan-2013" and so on for every date). This script should be run daily through dbms_scheduler. I have a script which give me the name of latest created partition.

SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS A
WHERE PARTITION_POSITION = (
  SELECT MAX(PARTITION_POSITION)
  FROM USER_TAB_PARTITIONS B
  WHERE A.TABLE_NAME = B.TABLE_NAME
    AND B.TABLE_NAME = 'IPART'
);

This may help you to give the answer.
I want a script which should delete the old partitions (say 3 months) automatically. This should also run daily. And that 3 months time should be configurable.

How can I achieve this?


texts: DB_RECOVERY_FILE_DEST_SIZE automatically decrease size after shutdown

texts:
I am using oracle database 11gR2.

I increased db_recovery_file_dest size with alter command

alter system set db_recovery_file_dest_size=60G scope=both;

When I shutdown database and again open database then db_recovery_file_dest_size automatically changed to default size 10G.

Now I am getting error

the database is not available due to the following conditions stuck archiver

How to solve this error


texts: Why are the passwords in MySQL (5.x) not salted?

texts:
I'm working with a legacy application that is connected to a MySQL database (5.x). When I created two different users for the MySQL database with the same password, the hash of those passwords are the same.

Is there an option to enable salting of the passwords in mysql.user?


texts:
I am sure a very simple question for people out there but my brain has shutdown.

I have joined two tables with a left join which has worked as hoped as I do need to bring back the NULLs in one of the tables as some of the rows will not have cosponsoring matches.

I need to put a WHERE clause on one of the columns that 'Could' be NULL and I want to bring back all rows that have 0 and the NULLs

Simply put like this so I want to bring back on rows with 0 or the NULL / Blanks
WHERE Expired = '0'


texts: Having a subquery somehow do the same group by as the outer query

texts:
i'm pretty decent with SQL but it's not my main experience of expertise. A colleague asked me a stumper of a question the other day. I was wondering if you guys could provide assistance.

Basically we're doing an over all count and grouping these counts by days... but we'd also like to kind of subquery those counts to get a specific type of count.

This is what I have so far that's not quite right.

select d.start_date,
       count(a.status),
       (   select count(checked_ins) as checked_ins
           from (   select distinct(a.id) as checked_ins
                    from schema1.employee_se_assignment a,
                         schema2.se d
                    where a.se_id= d.id
                      and a.status not in ('unassigned')
                      and d.customer_name in (‘Test Client’)
                      and a.status = 'checked_in'
                ) src
       )
from schema1.employee_se_assignment a,
     schema2.se d
where a.se_id= d.id
  and a.status not in ('unassigned')
  and d.customer_name in (‘Test Client’)
  and a.status = 'checked_in'
group by d.start_date
Order by d.start_date

that yields the following results. which is almost what he wants. but it's clearly yielding the total of all of a certain type of status when we want it broken down by day. EG (see below):


texts: How does table compression affect SQL Server's buffer pool?

texts:
I'm curious as to how SQL Server's buffer pool is affected by compressed data tables.

Is the data in memory compressed, just as it is on disk, or is it fully decompressed?

If the data is stored compressed in the buffer pool, how much of the transient decompressed data is held in memory at a given time when executing a statement; a row/page, the entire table (assuming there is sufficient free pages to hold the data), or "it depends"?


texts: Need help with Oracle Query for totaling fields with separate clauses, any feedback highly appreciated!

texts:
I am stuck on an issue that has been causing my mind to ramble for the last couple of days.

DETAILS ABOUT REPORT

This report is to identify the following details based off of the CUSTOMER_ID

new customer sales total in the NEWCUSTSALES field
existing customer sales total in the EXISTINGSALES field

THE FOLLOWING COLUMN NEEDS TO BE ADDED TO REPORT

Calculate the percentage of sales that was represented by new business.
(NEWCUSTSALES) / (EXISTINGSALES)

the FY in the query represents FISCAL YEAR

The query executes but does not provide the data I am looking for.

Currently, it is summing all the sales for the date range (65 Million) and dumping that number into the NEWCUSTSALES without taking into consideration I only want the SUM of sales for that CUSTOMER_ID in that row...

Here is a screenshot of the export

SELECT A.SITE
     , A.CUSTOMER_ID
     , A.NAME
     , A.CUST_GRP
     , A.REGION_CODE
     ,         (CASE WHEN REGION_CODE
               IN ('132','137','133','122','156','155','154','158','157')
             THEN 'Rep1'
             WHEN REGION_CODE
               IN ('109','115','110','116','118','117','113','114')
             THEN 'Rep2'
             WHEN REGION_CODE
               IN ('125','144','140','123','141','126','124')
             THEN 'Rep3'
             WHEN REGION_CODE
               IN ('127','131','129','128','112','130')
             THEN 'Rep4'
             WHEN REGION_CODE
             IN ('149','150','142','138','153','147','146','148','143','145','139')
             THEN 'Rep5'
             WHEN REGION_CODE
             IN ('107','105','101','102','108','104','151','111','152','106','103')
             THEN 'Rep6'
             WHEN REGION_CODE
             IN ('121','120','119')
             THEN 'Rep7'
             ELSE 'Unassigned'
             END) AS REP
     , A.CREATION_DATE AS STARTING_DATE
     , SUM(A.SALES) AS AMOUNT
     , SUM(A.COST) AS COSTS
     , (SUM(A.SALES) - SUM(A.COST)) AS MARGIN
     , (CASE WHEN B.FIRSTINVDATE BETWEEN TO_DATE('&FY_startDate','mm/dd/yyyy') AND TO_DATE('&FY_end_date','mm/dd/yyyy')
             THEN (SELECT SUM(SALES) FROM IFSINFO.HB_INVOICING_ALL WHERE INVDATE BETWEEN TO_DATE('&FY_startDate','mm/dd/yyyy') AND TO_DATE('&FY_end_date','mm/dd/yyyy'))
             ELSE NULL
             END) AS NEWCUSTSALES
     , (CASE WHEN B.FIRSTINVDATE NOT BETWEEN TO_DATE('&FY_startDate','mm/dd/yyyy') AND TO_DATE('&FY_end_date','mm/dd/yyyy')
             THEN (SELECT SUM(SALES) FROM IFSINFO.HB_INVOICING_ALL WHERE INVDATE BETWEEN TO_DATE('&FY_startDate','mm/dd/yyyy') AND ADD_MONTHS(TRUNC(TO_DATE('&FY_startDate','mm/dd/yyyy'),'mm'),-11))
             ELSE NULL
             END) AS EXISTINGSALES
FROM IFSINFO.HB_INVOICING_ALL A
    JOIN (SELECT COMPANY CO, DELIVERY_IDENTITY ID, MIN(FIRSTINVDATE) FIRSTINVDATE
          FROM IFSINFO.HB_CUST_FIRST_INV
          GROUP BY COMPANY, DELIVERY_IDENTITY) B
      ON (A.COMPANY = B.CO) AND (A.CUSTOMER_ID = B.ID)
WHERE A.COMPANY = '&COMPANY'
AND A.SITE = '&STARTING_SITE'
AND TO_DATE(A.INVDATE) BETWEEN ADD_MONTHS(TRUNC(TO_DATE('&END_DATE','mm/dd/yyyy'),'mm'),-11) AND TO_DATE('&END_DATE','mm/dd/yyyy')
GROUP BY A.SITE, A.CUSTOMER_ID, A.NAME, A.CUST_GRP, A.REGION_CODE, A.CREATION_DATE, B.FIRSTINVDATE


texts: SSIS data flow to update source table rows after copying to destination

texts:
I have a simple data flow that copies a subset of data from a source table on an internal database to a table on web-facing database.

If there is a problem, the error is outputted to an errors table.

That's all fine.

In the source table there is a bit column for SSIS_TRANSFERRED that I wish to set to 1 when the copy process completes. However, I'm unsure how to approach this.

My instinct is to craft an SQL Statement that runs against each Unique ID for every row successfully transferred as part of that package - is there a simple approach to this (i.e. as part of the data flow) or do I need to create a new Control Flow with OLE DB Command that queries the web-facing table and marks the corresponding internal rows as 'transferred' accordingly?


texts: Postgres crosstab query on multiple columns

texts:
I am trying to build a crosstab query on multiple columns.

To accomplish that I first created a type:

create type product_status as (product varchar(255), status varchar(255))

Then I created my query:

select pivot.*
from crosstab(
    $$
    select
        uniqueid,
        (product, status)::product_status,
        count(*)
    from policycase
    inner join policy
    on policy.id = policycase.selected_id
    group by 1,2
    order by 1,2
    $$,
    $$
    values(
--          ('(HOUSEFIRE,HISTORICAL)'::product_status),
--          ('(HOUSECONTENT,HISTORICAL)'::product_status),
         ('(LIABILITY,ARCHIVED)'::product_status)
    )
    $$
) as pivot (
    uniqueid bigint,
--     housefire_hist smallint,
--     housecontent_hist smallint,
    liability_arch smallint
)

Which works and gives me correct results back. However when I uncomment the commented lines in the example above, I get the following error message:

  [Exception, Error code 0, SQLState 42601] ERROR: provided "categories"
  SQL must return 1 column of at least one row Line 1, column 1

Two tables are used:

The policycase has a id, selected_id and a status

The policy has a id, uniqueid and product


texts: SQL Server Network Configuration node missing from Configuration Manager after install

texts:
I have a server running Windows 2012 R2 Datacenter 64-bit. I was using SQL 2016 RC3 with no issues and I want to replace it with the RTM build we have from MSDN.

After uninstalling RC3 and then installing RTM, I was unable to connect remotely to the server. Upon inspection, I see within Configuration Manager that there is no node for "SQL Server Network Configuration". The 32-bit versions are listed, and TCP/IP is enabled. How do I get this missing node installed?

I have started the SQL Browser service and attempted to repair the 2016 installation. Nothing seems to revive this missing component.

I find interesting that there are pieces of the RC3 and RTM installs left behind that I can't uninstall. I'm not sure if this is relevant. I followed Microsoft instructions on uninstalling SQL Server so I feel like I've done everything right.


texts: Oracle 12c - Create materialized view results in ORA-00942

texts:
I have a view that I can access through a database link as such:

SQL> select count(*) from REMOTE_SCHEMA.REMOTE_VIEW@REMOTE_DB;

COUNT(*)
--------
110

I can create and successfully query the remote view through a synonym:

SQL> create synonym REMOTE_VIEW for REMOTE_SCHEMA.REMOTE_VIEW@REMOTE_DB;

Synonym created

SQL> select count(*) from REMOTE_VIEW;

COUNT(*)
--------
110

If I try to create a materialized view using the synonym however, it fails:

SQL> create materialized view REMOTE_MV
build immediate
refresh complete
next sysdate+1
with ROWID
as
select * from REMOTE_VIEW;

ORA-00942: table or view does not exist

Creating the materialized view without the synonym succeeds:

SQL> create materialized view REMOTE_MV
build immediate
refresh complete
next sysdate+1
with ROWID
as
select * from REMOTE_SCHEMA.REMOTE_VIEW@REMOTE_DB;

Materialized view created

Any ideas why using the synonym fails? Ideally I would like to use the synonym to insulate the materialized view from the database link. The database link could differ based on environment and I want to ensure the materialized view query remains the same regardless.


texts: Multiple databases or one large database?

texts: My company has a framework where different areas of a region are created as separated databases - we call them datamarts. All of the databases are exactly the same and in some regions we have 20 -30 databases where some or all are included in a particular query. Is there a performance penalty when we perform queries that join these databases or does SQL Server join these cross database queries seamlessly. Some of the databases may have a few million transactions.


texts:
I have 3 servers, say A,B &C. All have the equivalent data(same data). Now what I want is to make A as the master and B,C the slaves.

But the scenario is that I only want to replicate some of the tables from a single database of the master (server A).

All the servers are running with the same data. So no need to take the dump from the master and restore on the Slaves.

Just to set the replication for some tables from a single database.

Is there any harm in doing this? And what step(s) should I follow to achieve this?


texts:
Goal:

Delete several billion records old records (roughly 600GB) across three tables with zero downtime.

Methodology and Background:

I plan to delete batches of records associated with 1 Million MyIDs at a time (ie MyID BETWEEN 1 AND 1000000). While the delete is running, two of the tables will be very hot but activity on the third table can be safely suspended. In the two hot tables, MyID is the clustering key. In the third, cold, table I have a non-clustered index on MyID.

Besides the DELETE operation, the other activity in the hot tables will consist of INSERT, probably several per second. MyID is an IDENTITY and the newly inserted records will not be in range of any of the DELETE batches.

If it helps understand potential performance, SELECT of an aggregate of a sample batch of these rows takes < 1 second on the tables clustered by MyID and about 2 seconds on the non-clustered table. I do not have a prod replica for this particular database to play in so I cannot say how long the deletes will take, however once I deploy this to DEV I'll have a better idea.

Side note: To address log size, I am wrapping these batches in transactions and upping the TLog backup frequency from 15 to 5 minutes. I have 150GB of available log space.

The Question

I read in the MS docs:

  By default, a DELETE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes.

I am very wary of using locking hints, but in this case can I safely use one to avoid TABLOCKX? Are there other ways besides a locking hint to avoid TABLOCKX?


texts: Finding the reason of the lost data on oracle database

texts:
My application uses Oracle database. At the same time Oracle ERP application and another Windows application (like mine) use same database. Everything looks fine and works synchronized but sometimes the Survey table lose data because of unknown reason!

I thought I delete it mistakenly in a case and added a backup query onto my delete query. Before run delete query, I copy the data to SurveyBackUp table. Yesterday the problem occurred again and I checked my backup table but it was empty! So, it looks like; the lost data is not deleted by my query. Is there any way to detect which query deletes the data?


texts: ORACLE restore - how to restore specific .bkp file?

texts:
I just started working with oracle and I have some issue.
My database is NONARCHIVELOG mode.
I made two backups:
they are at locations:

/opt/oracle/flash_recovery_area/CTGINST1/backupset/ol_mf_____20130408.bkp

/opt/oracle/flash_recovery_area/CTGINST1/backupset/ol_mf_____20130407.bkp

I want to restore second file from 7th April.
I read some guide on:
http://orafusion.com/art_rman3.htm

but I can not find command how to specify restore command with specific backup files from these two (or more). It does not say on this link and on other links I read.
I want to use it with RMAN  commands.
what will be the commands to restore my older backup?

I found

RMAN> restore database;

but how it will know what file to restore?
Also does it always need to restore  spfile and control file because I did not create some additional backups (just ran command backup database)

Thank you!!


texts:
Sample data

Excepted output

Math Operation

Sum all Period time for each EmpName as Total Employee Time
Sum Similar Tasks For EmpName, divide it by Total Employee Time and multiply by 100%

please help me

I am using SQL Server Management 2008 and here is my try:

select EmpName,Task,Period_Time,
sum(Period_Time)/(select sum(Period_Time) as total from dbo.TableName)
as percentage
from  dbo.TableName
group by EmpName,Task,Period_Time
order by EmpName


texts: Is db_executor a standard role in SQL Server 2012 R2?

texts: In trying to figure out the exact permissions I need to give a user to execute all stored procedures I ran across many pages saying to create a role of "db_executer" and give that role the GRANT EXECUTE permission, then assign users to that role. When I when to my sever to make that role I found out it already exists. Since I inherited this server I am not sure if it was made by the last admin, a program, or is a built in. Searching Google I am not finding any relevant hits newer then 2010 and those all talk about how to make the role. Can anyone tell me if this is a default role in 2012 or if someone/thing else made it?


texts: What is the fastest way to insert large numbers of rows?

texts:
I have a database where I load files into a staging table, from this staging table i have 1-2 joins to resolve some foreign keys and then insert this rows into the final table (which has one partition per month). I have around 3.4 billion rows for three months of data.

What is the fastest way to get these rows from staging into the final table ? SSIS Data Flow Task (that uses a view as source and has fast load active) or an Insert INTO SELECT .... command ? I tried the Data Flow Task and can get around 1 billion rows in around 5 hours (8 cores / 192 GB RAM on the server) which feels very slow to me.


texts: Exception when calling Postgresql function returning a local numeric from C#

texts:
I wrote a Postgresql function to insert a row and return the value generated from the identity column.  I receive the following exception when trying to call it from C#.
Npgsql.PostgresException: '42601: query has no destination for result data'
I have looked around for an answer, and it seems ExecuteScalar has worked for others, but in all the examples I've seen, it's usually when using RETURN QUERY, not a local variable.  What am I missing?
Here is the function:
CREATE OR REPLACE FUNCTION public.func_insert_item(_name character varying)
 RETURNS BIGINT
 LANGUAGE plpgsql
AS $function$
DECLARE
    _item_id BIGINT;
BEGIN

    INSERT INTO public.items
    (
        name
    )
    VALUES
    (
        _name
    )
    RETURNING _item_id;

    RETURN _item_id;
END;
$function$

Here is the C#:

        static NpgsqlParameter CreateParameter(string name, ParameterDirection direction, string value)
        {
            var parameter = new NpgsqlParameter(name, NpgsqlTypes.NpgsqlDbType.Varchar, value.Length);
            parameter.Direction = direction;
            parameter.Value = value;

            return parameter;
        }

        static void Main(string[] args)
        {
            using var connection = new NpgsqlConnection(connectionString.ToString());
            connection.Open();

            using var command = new NpgsqlCommand("func_insert_item", connection);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(CreateParameter("_name", ParameterDirection.Input, name));

            object itemId = command.ExecuteScalar();
        }


texts: Ola Hallengren’s SQL Server Maintenance Solution using a different user

texts:
I am looking at using Ola Hallengren’s SQL Server Maintenance Solution to create a backup plan.

However, I cannot figure out how to run the Agent Job (T-SQL script) as a different user as the current user does not have access to the network backup folder.

I have created a Credential and Proxy, but this only seems to be available for Agent Jobs running SSIS jobs. I have also tried setting the Job Owner as the domain user, but this did not work.


texts:
Hello i'd like some help understanding the following execution stats.

For this query

db.family.find({ "_id" : ObjectId("5c87890b3adcde7da566f0d7")},{_id:1}).explain("executionStats")

        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 1,
        "totalDocsExamined" : 1,

Specifically not sure why totalDocsExamined is 1 because I would have imagined that sine there is an index on ObjectId, and only that is returned , no documents need to be examined?

Kindly help.


texts: Why is my Availability Group in an unhealthy state and showing synchronizing?

texts:
I have an active directory and 3 SQL Server nodes in an availability group.

The primary and secondary node are on different subnet to active directory: 104.92.173.x

The third node which is in async commit mode, is on the same subnet as active directory: 104.92.174.x

I don't see anything in the error log on SQL Server, but my availability group is in an unhealthy state and availability replicas are stuck at synchronizing state.

I get the following error:


texts: Shell script - psql run multiple statements from a variable one by one in one transaction

texts:
Im preparing a shell script to generate analyze command and all the commands will go to a variable.

echo $analyze_query

analyze tbl1;
analyze tbl2;
analyze tbl3;

Now I want to run one by one, and Im able to achieve that by using the below command.

for $i in $analyze_query
do
psql -h host -U user -d db -c"$i"
done

But this query will close and open a new connection for every command, Is there any way that I can run the one by one but in one session?


texts:
I have a table in bigquery with some formulas and would like to replace few things from it.
SELECT FORMULA,element.name,FUNCTIONS.fun_temporal FROM `test-project.FORMULAS.FORMULA_HISTORY`
,unnest(COUNTER_GROUP_INFO) as element
where SOURCE_KPI_NAME in ('A01_ACD_OUT','A01_ANSW_ERL_OUT','ETH_PORT_TRAF_BYTES');

This results above list of formulas which I would like to convert as below: ie a name column equivalent should be replace with fun_temporal and rest other character should also get replaced or removed.
actual -   ([`A01`].[`Answer_time_unit_second`]/60)/[`A01`].[`Answer_number`]
expected - safe_divide((sum(Answer_time_unit_second)/60),sum(Answer_number)) as A01_ACD_OUT

actual - (([`A01`].[`ANSWER_Traffic_UNIT_ERL`]))
expected - sum(ANSWER_Traffic_UNIT_ERL) as A01_ANSW_ERL_OUT

actual - [`a02`].[`INOCTETS`]+[`a02`].[`OUTOCTETS`]
expected - sum(INOCTETS)+sum(OUTOCTETS) as ETH_PORT_TRAF_BYTES

Is it possible to do such conversion in BQ or using shell?
I was trying something but didn't worked
SELECT REGEXP_REPLACE(REGEXP_REPLACE(FORMULA, r'([\`\[\]\"])', ''),element.name,FUNCTIONS.fun_temporal) AS new_foo FROM `test-project.FORMULAS.FORMULA_HISTORY`
,unnest(COUNTER_GROUP_INFO) as element
where SOURCE_KPI_NAME in ('A01_ACD_OUT','A01_ANSW_ERL_OUT','ETH_PORT_TRAF_BYTES');


texts:
I am migrating a number of databases to UTF-8, and just discovered phenomena that I was not aware of. When selecting data out to the terminal, additional bytes are added to the output. Example:

~]$ x=$(db2 -x "values 'a'")
~]$ echo "${x}b"
a b

One additional space after a

~]$ x=$(db2 -x "values 'aa'")
echo "${x}b"
aa  b

Two additional space after aa

It does not seem to matter how many bytes a character occupies in utf8:

~]$ x=$(db2 -x "values ''")
~]$ echo "${x}b"
 b

One additional space after g-clef

~]$ x=$(db2 -x "values ''")
~]$ echo "${x}b"
  b

Two additional space after g-clef g-clef

db cfg:

Database territory                                      = SE
Database code page                                      = 1208
Database code set                                       = UTF8
Database country/region code                            = 46
Database collating sequence                             = SYSTEM_819_SE

The terminal has encoding UTF8 (tried terminator and gnome-terminal), and before connecting to the database I did:

export LC_CTYPE=sv_SE.utf8

The above is of course just silly examples, but I have a fair amount of tests in scripts similar to that:

dbtype=`db2 -x "values nya.get_db_type()"`
if [ "${dbtype}" = "N" ]; then
    ...

where I need to change the test one way or another.

Any thoughts on a configuration, that would get rid of the extra bytes?

~]$ uname -a
Linux nya-ladok3-release 3.10.0-1062.9.1.el7.x86_64 #1 SMP Mon Dec 2 08:31:54 EST 2019 x86_64 x86_64 x86_64 GNU/Linux
~]$ db2level
DB21085I  This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL11050" with level
identifier "0601010F".
Informational tokens are "DB2 v11.5.0.0", "s1906101300", "DYN1906101300AMD64",
and Fix Pack "0".
Product is installed at "/opt/ibm/db2/V11.5".


texts: Difference between db_owner and having all other database roles except the 2 containing the deny keyword

texts:
What is the difference between having only the db_owner database role and having all other except db_denydatareader and db_denydatawriter?

db_securityadmin +
db_accessadmin +
db_backupoperator +
db_ddladmin  +
db_datareader +
db_datawriter

Versus

db_owner

I've read that when you have db_accessadmin and db_securityadmin roles, you can change database role of another user into the database but I tried and it seems that I don't have the permission to change any permission while logged with this user. But if I give to this user the db_owner role, now I can change permission to any other user.