texts:
I am testing SQL connectivity between a SQL server (windows server 2000) and client PC that has no SQL tools installed (windows 2000 SP4). The client PC runs a java application that needs to connect to the DB. from what I can tell port 1433 comes up ESTABLISHED and enters a TIME_WAIT state. working with the networking team we are trying to determine connectivity issues outside of the java application.
I discovered the UDL test file process (never heard of this before and not a SQL guy) and am wondering a few things. According to the stuff I have googled I should run the test using "SQL Server Native Client" but in my providers options that is not a choice. Will the test not be accurate if I were to use OLE DB Provider for SQL Server?


texts: Stored procedure in SSIS Lookup Transformation Editor

texts: Can you use a stored procedure as the source for Lookup Transaction because it seems to fail on the EXEC statement?


texts: DR Site availability if primary goes down

texts:
I have two SQL Servers at a production site (2 node WSFC) in an Availability Group, and one SQL Server at a DR site.

If I add DR site node to existing WSFC to configure AOAG:

If both servers at the primary site go down, will WSFC will be online or offline? Will my secondary replica still be available for read-only workloads?
I am using SQL Server 2014.

I am planning to create a file share witness for quorum configuration.

I just want to make sure if the whole production site is down, will my secondary replica at DR site still be useful for the users? Will it be read-only or read-write? As it is Async, will it require manual or forced failover?

All are SQL Server 2014 instances, and are stand-alones included in AOAG. Production site is synchronous and DR site is asynchronous.


texts:
I have 2 tables: Customer and Billing, Customer contains generic details (name, address, you get the idea) and Billing contains items that are billed.

The end date of when the item gets billed to is variable (some people are on a monthly cycle, others on a 3 months, 6 months 1 year etc.).

In the Billing Item the customer can have a number of packages - some of them are packages that mark them as a Reseller. My current SQL query looks like this:

select customer.addresses_0_firstname AS AdminFName,
       customer.addresses_0_lastname AS AdminLName,
       customer.addresses_0_organisation AS CompanyName,
       customer.addresses_0_address1 AS Address1,
       customer.addresses_0_address2 AS Address2,
       customer.addresses_0_city AS City,
       customer.addresses_0_postcode AS Zip,
       customer.addresses_0_country AS CountryID,
       customer.addresses_0_email AS Email,
       (CASE WHEN billing.lines_0_description like '%Package0%'
             THEN 'False'
             ELSE 'true' END) AS Reseller,
       max(billing.end_date)
from customer
INNER JOIN billing on customer._id = billing.customer_$id
where customer.name='example'
  and (billing.lines_0_description like '%package1%'
       or
       billing.lines_0_description like '%package2%'
       OR
       billing.lines_0_description like'%package3%')
group by customer.addresses_0_firstname,
         customer.addresses_0_lastname,
         customer.addresses_0_organisation,
         customer.addresses_0_address1,
         customer.addresses_0_address2,
         customer.addresses_0_city,
         customer.addresses_0_postcode,
         customer.addresses_0_country,
         customer.addresses_0_email,
         billing.lines_0_description

Which outputs the following:

AdminFName  AdminLName  CompanyName Address1    Address2    City    Zip CountryID   Email           Reseller    (No column name)
Test User   Testing     123         test        Rd          test    0   Test        Test@test.com   true        2012-03-23
Test User   Testing     123         test        Rd          test    0   Test        Test@test.com   true        2012-01-23

Which is a list of all the user data (that we want) but joined to each billing line, as opposed to selecting the end_date with the largest value.

I suspect I've got to do a nested select, but I'm not sure on the logic that I need here.

Suggestions?


texts: Merge multiple rows into one row with sql


texts: Statistics for AlwaysOn Availability DBs

texts:
Can you please help me understand why there is a need for SQL Server to maintain statistics in tempdb for DBs that are part of an Always On Availability Group?

Isn't "everything" that happens in the Primary replica, replicated to the Secondaries? If so, statistics changes that happen in Primary would be replicated into secondary isn't it?


texts: Order result by count of common array elements

texts:
Using Postgres 9.4, I'm interested in having an array of integers like user_ids_who_like and provide an array of users (like user_ids_i_am_following) to sort that intersection.

Something like:

select *
from items
where [there is an intersection between
       user_ids_who_like with user_ids_i_am_following]
order by intersection(user_ids_who_like).count

Is grouping and ordering by an array intersection possible?

Example data:

items
name          | user_ids_who_like
'birds'       | '{1,3,5,8}'
'planes'      | '{2,3,4,11}'
'spaceships'  | '{3,4,6}'

For a given user_ids_who_i_follow = [3,4,11], can I do something like:

select * from items
where <user_ids_who_like intersects with user_ids_who_i_follow>
order by <count of that intersection>

Desired result:

name          | user_ids_who_like  | count
'planes'      |  '{2,3,4,11}'      | 3
'spaceships'  |  '{3,4,6}'         | 2
'birds'       |  '{1,3,5,8}'       | 1

One possibility seems to be something like this:

select id, user_ids_who_like, (user_ids_who_like & '{514, 515}'::int[]) as jt
from queryables
where user_ids_who_like && '{514, 515}'
order by icount(user_ids_who_like & '{514, 515}'::int[]) desc;

But I can't tell if this style (using the intarray extension rather than native array functions and operators) is outdated; any feedback from more sophisticated users here? It's not clear to me how to do the intersection of two arrays using the methods and operators.


texts: In PostgreSQL is the row ordering preserved in functions and CTEs?

texts:
In SELECT statements the ordering of the returned rows is not guaranteed if the ORDER BY clause is not specified. This is true for "normal" tables.

Is this also true for ordered tables generated using WITH expressions (CTEs)? Ordered tables returned by functions? I assume that not. Is this somewhere explicitly stated in the documentation?

Specifically, can I assume that this (more efficient) query:

WITH ordered AS ( SELECT * FROM table1 ORDER BY col1 )
SELECT sum(col2) result FROM
generate_series(0,50) nr,
LATERAL (SELECT * FROM ordered LIMIT 100 OFFSET nr*100) a
GROUP BY nr ORDER BY nr;

will be equivalent to this query:

SELECT sum(col2) result FROM
generate_series(0,50) nr,
LATERAL (SELECT * FROM table1 ORDER BY col1 LIMIT 100 OFFSET nr*100) a
GROUP BY nr ORDER BY nr;

For functions:

If I have such function:

CREATE FUNCTION do_sort(name text[]) RETURNS TABLE(name text) AS $$
SELECT name.name FROM unnest($1) name ORDER BY name.name ASC;
$$ LANGUAGE SQL IMMUTABLE;

can I make such wrapper function and assume that the array will always be correctly sorted?

CREATE FUNCTION do_sort_returns_array(name text[]) RETURNS text[] AS $$
SELECT array_agg(name) FROM do_sort($1);
$$ LANGUAGE SQL IMMUTABLE;


texts: Cartesian product and Division in Relational Algebra

texts:
Let be the following database sketch:

Cineroom(Name, Hour, Name)
Film(Name, Director, Actor)
Saw(Spectator, Name)
Produce(Producer, Name)
Likes(Amateur, Name)

How to express, in relational algebra the difference between:

which actors played in a film they produced?
which actor produced a film?

To my mind it was the difference between Cartesian product and outer join:
ΠActor(σProducer=Actor(Film ⋈ Produce))
ΠActor(σProducer=Actor(Film*Produce))
Would it allow to join only the films the actor played and produced in the first case?


texts:
SQL Server 2016 Enterprise Edition SP1

Database Mail was working fine with no problem until a server patching was performed. I tried to do all the troubleshooting available but still all mails got queued and no records in the log.

Finally when I checked the database mail program I  didn't find the executable file in the Binn folder or any other folders!

My questions are how this happened and how to solve it ?


texts:
I am working on a project that involves converting production data from an Oracle 7 DB to our system, which use Oracle 11g. In our other projects, we use DB link to access the other system DB, which is usually Oracle 10g. Can we have similar solution for Oracle 7?

We need a solution that can minimize down time of system.


texts: Does the Sql Server Agent account need sysadmin permission?

texts:
I ran Brent Ozar's sp_blitz script, and one of the things it's complaining about is that my SQL Server Agent account has sysadmin permission. I removed the sysadmin permission, but then the agent wouldn't start. (See image below). There's nothing of note in the event log. When I restore the sysadmin permission the agent starts with no difficulty.

My account is a managed service account. None of the other managed service accounts I use (one for running reporting services, and one for running the SQL service) have sysadmin permission.

Is the sysadmin permission actually necessary? If not, what are the minimum permissions this account needs?


texts: implementing one to many relation between 2 dimensions in dataware house

texts:
I am designing a Dataware house for the Stackoverflow job section. I have two dimensions: job opportunity and company. every record of job opportunity is referenced to only one record of company dimension. there are two ways to implement this relationship.

first:

second:

I doubt which approach is better. can anyone help?


texts:
I noticed the following in the sql server log, is there any way to determine the login/user that initiated this change. It appears a database was set to single user mode and back to multi user. Here is the log snippet

Date       10/10/2017 8:55:27 PM
Log         SQL Server (Current - 10/11/2017 8:00:00 AM)
Source      spid139
Message     Setting database option MULTI_USER to ON for database 'DBNAME'


texts: why does this DO block have “syntax error near declare” in pgagent?

texts:
I have the following code:
DROP TABLE IF EXISTS pltest;
CREATE TABLE pltest (x jsonb);

DO $$
DECLARE startdate text := to_char(current_date - 1, 'YYYYMMDD');
BEGIN

EXECUTE format(
           'COPY pltest FROM PROGRAM ''curl "https://example.com/events/start_date=%sT000000Z"''',
           startdate
        );

END
$$ ;

It basically imports the URL with a parameterized COPY statement so it always imports the data of the last 24 hours, it runs perfectly in SQL shell, but when i tried adding a  pgagent job with that code, pgagent returns "syntax error near DECLARE".


texts:
The current setup of the server is Windows Server 2008 sp1, with SQL Server Reporting Services 2008 r2 installed. The databases are on a separate server which is using SQL Server 2008 r2.

I have a new server with Windows Server 2012 r2, I have installed SSRS 2016 on this server and configured it the same as the original server, restored the encryption keys, and connected to an exact copy of the DB server.

Locally on the new RS server I can access and run the reports using the web interface http://{servername}/reports/. However when I try to access the same url remotely I get redirected to http://{servername}/reports/#/browse, I can see the folders and reports, but when I run a report I get a blank report.


texts: Non-structured Query performs better than on clustered, hash-clustered and indexed?

texts:
I am using the following query as a base line on some unstructured data (no indexes, no clusters etc) and the query is performing better on the unstrutured data than when I add indexes to the join columns or add the tables to a cluster or hash-cluster. I feel my baseline query isn't ideal, my task is to take a baseline query and find a structure that performs the best with it but all the structures I have tried perform worse than the unstructured. Is there anything I can do to my baseline query that will at least find one structure that has a noticeable improvement over the unstructured?

Query:

SELECT Cust_name, price
FROM Customer, Sales
WHERE price > 1000
AND num_sold > 10
AND Sales.Cust_id = Customer.Cust_id;

This query is returning 108k+ rows out of 150k.

Here are the traces from the testing:

Unstructured:

With index added on the join columns Sales.Cust_id and Customer.Cust_id:

With both tables added to a cluster where the cluster index is the Cust_id:

With Both tables added to a hash cluster with 10000 keys:


texts: Multiple "GROUP_CONCAT"s with WHERE clause

texts:
In a table as

id    name      type           info
1     BMW       car            yes
2     Reno      car            no
3     IBM       electronics    no
4     Sony      electronics    yes
5     Mazda     car            yes

I use GROUP_CONCAT to get the list of each type, but I want to separate the concatenated column to multiple columns categorized by the column info. It should be something like

SELECT type,
       GROUP_CONCAT(name) ORDER BY id ASC SEPARATOR ' ') AS list_with_info
       GROUP_CONCAT(name) ORDER BY id ASC SEPARATOR ' ') AS list_without_info
       FROM table1 GROUP BY type

How can I introduce WHERE clause or using other approach to return multiple concatenated columns?


texts: How to identify the top disk space consuming tables by file?

texts:
Is there a query I can run to see the top disk space consuming tables for a given file in a file group?
Need to free up some disk space for a given SAN drive and need to identify which tables are using the most space for a couple of data files on that drive.


texts: Would using timestamps solve consistency issues with snapshot isolation?

texts:
I'm considering using Snapshot Isolation for some large queries being send from an Access 2007 frontend. These queries are occasionally causing ASYNC_NETWORK_IO waits, and some blocking as well. I'm working to try to solve this with application design, but since I can't control how the Jet engine loads data from the server, I haven't been able to completely eliminate the problem.
I know that one major concern with using snapshot isolation is the problem of updating dirty records, such as expressed in this blog post.
In my database, all of the tables have timestamp fields, which I would think would prevent the problem of updating dirty records. It might cause some update conflicts, but that seems like an acceptable outcome, while getting incorrect data is not. Am I right that having timestamps is a solution for potential data integrity problems, or could I still run into trouble?
EDIT: Max Vernon asked for an example. In the article I linked to, Craig Freedman uses this as an example:

We can demonstrate this outcome using SQL Server.  Note that snapshot
isolation is only available in SQL Server 2005 and must be explicitly
enabled on your database:

alter database database_name set allow_snapshot_isolation on

Begin by creating a simple table with two rows representing two
marbles:

create table marbles (id int primary key, color char(5)) insert
marbles values(1, 'Black') insert marbles values(2, 'White')

Next, in session 1 begin a snaphot transaction:

set transaction isolation level snapshot begin tran update marbles set
color = 'White' where color = 'Black'

Now, before committing the changes, run the following in session 2:

set transaction isolation level snapshot begin tran update marbles set
color = 'Black' where color = 'White' commit tran

Finally, commit the transaction in session 1 and check the data in the
table:

commit tran select * from marbles

Here are the results:

id          color
1           White
2           Black

This is an issue without using any sort of rowversioning. My question is, do timestamps solve this problem? Do they leave similar issues outstanding? Or does SQL Server's rowversioning when you enable Snapshot Isolation solve the problem and make timestamps unnecessary? I'm assuming it doesn't, based on Freedman's example.


texts: Importing CSV file using SQL Server Import and Export Wizard (SQL Server Express)

texts:
So I am having a heck of a time importing a CSV file. The ultimate goal is to create a repeatable process to import the file. The only way I've been able to import the file is in a roundabout way using MS Access (Import into Access then export to SQL Server), which it happily and merrily does without issue.

But surely SQL Server (Express or otherwise) should be able to import it also.

The file is out in the wild here: http://ourairports.com/data/ and specifically: http://ourairports.com/data/airports.csv

It is a great data set to play with (real world data) and I have designs on normalizing it, bringing it into SSRS and what not, largely to learn and have something to 'show' a prospective employer.

But I'll be damned if the file will import without errors. Ideally the columns will import and convert appropriately, i.e. columns that are numeric should end up in the table defined as Int or whatever is most appropriate.

The most frequent error I am seeing is:

  Data conversion failed. The data conversion for column "wikipedia_link" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."

Ideally I'd like to import the file via T-SQL and Bulk Insert, but have similar issues.

Perhaps if someone can take a gander and see what's up, ideally I'd learn something here and not looking for just a quick answer, unless of course I'm just doing something stupid like forgetting a check box.

Thanks


texts: What is the performance impact of queries against DM objects using DMVs in SQL Server 2008?

texts:
Recently, I've been running queries against our production system on the DMVs for querying the plan cache.  In particular, here's the from clause of the query:

FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
CROSS APPLY stmt.nodes('.//IndexScan/Object[@Index=sql:variable("@IndexName")]') AS idx(obj)

There's roughly 28k rows in dm_exec_cached_plans.

When I do this, it's setting off alerts for PLE dropping very low and very high disk IO and latency (read/write stalls).

This comes as a surprise because I've never read about or experienced anything relating to performance issues running adhoc queries against the DMVs.  This server has 24 cores, 64gb RAM and SSDs.  I know there is always more to it than that, but I have a hard time believing these queries cause such a problem.

Can anyone explain exactly why this happens?  I'm happy to provide more info about our configuration, but I was hoping there would be a simple explanation as well.


texts:
I know the form
Select * into new_table from old_table

statement. Bur recently I found 2 posts here and here which use it like
SELECT * FROM NhlPlayer
INSERT INTO PlayerBackups

I find nothing in msdn, is that some new syntax or just a fault? I guess the later because
INSERT INTO PlayerBackups
SELECT * FROM NhlPlayer

would work fine.


texts: Non cluster indexing with combination or on single entity for multiple columns

texts:
I am new to db design I also read concepts of non cluster indexing and know about the combined non cluster indexing but in my scenario I am having user table with multiple search on column
table column as follows
 userName
 fatherName
 empId
 cardNumber
and so on. (above all 4 no one column is PK)

I am having around 50 millions records over here due to large data search take huge time due to that I am thinking to create indexes in table but these all 4 searches are optional means possible user can fill all four or may be one or two of them but I was confused if I make all four separate index and one for all 4 then it might be creating issue on performance of while entering/inserting the data I created even a single index for all 4 column in combined but while I am searching with only card number then it takes huge time shall I created all four separate indexes would this work in combined search of  empId and cardNumber
If I created separated once then what about combined searches If I create combinations then indices are 15 which obviously not a good way for insertion...
lot of confusion I have can anyone help me thanks in advance


texts: How can I store user generated content in postgresql or rdbms?

texts:
**In this Image I show My actual problem occurs in usr_post means how I can simplify the database table for user-generated content or how can I design the table for user_generated content, Note I want to store every individual user-generated content **
Note that I am using Django framework and its backend support by PostgreSQL database means I am using PostgreSQL database
One to Many Relation will not work in my case here is a screenshot of unexpected output

here is my model configuration
class User(models.Model):
first_name = models.CharField(max_length=40)
last_name = models.CharField(max_length=40)
usr_name = models.CharField(max_length=40)
status = models.IntegerField(default=00)
verify_type = models.CharField(max_length=10, default='E')
login_value = models.CharField(max_length=50, default='NA')
login_alternate = models.CharField(max_length=15)
country_code = models.CharField(max_length=15)
area_code = models.CharField(max_length=15)
password = models.CharField(max_length=200)
created_at = models.DateTimeField(auto_now=True)
usr_post = models.ForeignKey(UserPost, blank=True, null=True, on_delete=models.SET_NULL)


texts: Retrieve by inserting ORDER of Merged table in SQL Server

texts:
I have a 3 tables such as "BASE_Customer", "BASE_Invoice" and "BASE_Payment" the below structure,

Query:

CREATE TABLE BASE_Customer
(
    CustomerId INT IDENTITY(1,1),
    CustomerName VARCHAR(45)
    PRIMARY KEY(CustomerId)
)
INSERT INTO BASE_Customer (CustomerName) VAlUES ('LEE')

CREATE TABLE BASE_Invoice
(
    InvoiceId INT IDENTITY(1,1),
    InvoiceDate DATE,
    CustomerId INT NOT NULL,
    InvoiceMethod VARCHAR(45) NOT NULL, -- CASH or CREDIT
    Amount MONEY,
    PRIMARY KEY(InvoiceId)
)
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, InvoiceMethod, Amount) VAlUES ('2020-01-16', 1, 'CASH', 1000);
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, InvoiceMethod, Amount) VAlUES ('2020-01-16', 1, 'CREDIT', 2000);
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, InvoiceMethod, Amount) VAlUES ('2020-01-17', 1, 'CREDIT', 500);
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, InvoiceMethod, Amount) VAlUES ('2020-01-18', 1, 'CREDIT', 2000);
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, InvoiceMethod, Amount) VAlUES ('2020-01-18', 1, 'CASH', 150);
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, InvoiceMethod, Amount) VAlUES ('2020-01-19', 1, 'CREDIT', 1000);
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, InvoiceMethod, Amount) VAlUES ('2020-01-19', 1, 'CREDIT', 3000);
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, InvoiceMethod, Amount) VAlUES ('2020-01-19', 1, 'CASH', 1000);
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, InvoiceMethod, Amount) VAlUES ('2020-01-20', 1, 'CREDIT', 2250);

CREATE TABLE BASE_Payment
(
    PaymentId INT IDENTITY(1,1),
    PaymentDate DATE,
    CustomerId INT NOT NULL,
    InvoiceId INT NULL,
    PaymentMethod VARCHAR(45) NOT NULL, -- CASH or CREDIT, ADVANCE
    Amount MONEY,
    PRIMARY KEY(PaymentId)
)
INSERT INTO BASE_Payment (PaymentDate, CustomerId, InvoiceId, PaymentMethod, Amount) VAlUES ('2020-01-16', 1, 1, 'CASH', 1000);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, PaymentMethod, Amount) VAlUES ('2020-01-16', 1, 'CREDIT', 500);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, InvoiceId, PaymentMethod, Amount) VAlUES ('2020-01-18', 1, 4, 'ADVANCE', 500);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, PaymentMethod, Amount) VAlUES ('2020-01-18', 1, 'CREDIT', 2000);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, InvoiceId, PaymentMethod, Amount) VAlUES ('2020-01-18', 1, 5, 'CASH', 150);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, PaymentMethod, Amount) VAlUES ('2020-01-18', 1, 'CREDIT', 5000);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, PaymentMethod, Amount) VAlUES ('2020-01-19', 1, 'CREDIT', 1200);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, InvoiceId, PaymentMethod, Amount) VAlUES ('2020-01-19', 1, 7, 'ADVANCE', 500)
INSERT INTO BASE_Payment (PaymentDate, CustomerId, InvoiceId, PaymentMethod, Amount) VAlUES ('2020-01-19', 1, 8, 'CASH', 1000);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, InvoiceId, PaymentMethod, Amount) VAlUES ('2020-01-20', 1, 9, 'ADVANCE', 750);

Customer table should Join to both tables "BASE_Invoice" and "BASE_Payment" (CustomerId is Foreign Key on both tables)
when inserting a data into "BASE_Invoice" table if invoice method column CASH at the time Invoice Payment inserting with payment method CASH,
when inserting a data into "BASE_Invoice" table if invoice method column CREDIT some times we insert payment with payment method ADVANCE or otherwise we don't insert payment on payment table.
on payment table only can insert CREDIT payment methods

I need to merge all the tables once and retrieve the ORDER of output such as below screen shot,

The issue is how to merge tables and get the Order of transaction


texts: How to restore SQL Server 2008 R2 database to newly installed SQL Server 2008 R2 instance?

texts:
Our SQL database server recently suffered a serious OS failure and had to be reinstalled from scratch. We are now attempting to associate the restored database and transaction log files into the new instance of SQL Server.

Our backups are up to date and contain the following two files:

Database.mdf (Primary data file: 170 MB)
Database.ldf (Transaction log file: 837 MB)

I know this is quite a basic question but I believe it is not a duplicate, is relevant and could be of great value to anyone browsing this site looking for similar answers.

The server is running SBS 2003 SP2 (x86).

Please let me know if I need to update this question with more information.


texts: How to select maximum date_created value from all tables matching certain pattern?

texts: Given a MySQL DB with a hundreds of tables with the ones of interest being named ap_form_* where * = a random value. How do I loop through that list of tables to display the MAX(date_created) for each individual table queried.


texts: SSIS - Iterative Data Flow Task with Schema Differences

texts:
I am new to SSIS and am trying to change a current process that moves data from one server to another using a linked server to use an SSIS package instead.

Currently, I have am getting a list of the 160 tables and setting the output to an Object variable.  Then I am using a Foreach Loop Container to iterate through each of the 160 tables along with a Data Flow Task to move the data from the source to the destination.

The part I am having issues with is that some of the 160 tables have schema differences i.e. the destination table does not have all of the columns contained in the source table.

For each table, I can get a list of the columns from the destination table and assign the output to an Object variable but I'm stuck at that point and am not sure to proceed conceptually.

So far, I have eliminated the option of converting the Object variable into a string by using an expression.  I read that expressions can't handle Object variables.

I was thinking of using another Foreach Loop Container to iterate through the column names in an effort to build a comma-separated string which I could use in an expression to turn into a SELECT statement but I'm having difficulties building the expression to concatenate the column names to same string variable.

Does anyone have conceptual suggestions on how I can solve the schema differences issue?

I am developing the SSIS package inside of Visual Studio 2013 for use on a server running 64-bit SQL Server 2014 Standard Edition.


texts: Postgresql: When using SSH tunnel, I receive the fe_sendauth: no password supplied error

texts:
I am SSH Tunneling to my postgreql server. I have previously successfully configured 9 + win XP dev PCs to connect to this postgresql server, those PCs still connect successfully.

I am now setting up a Windows 7 64bit Dev PC, using all the same putty configurations for the connection, the same version of pgAdmin, however when I try to add the posgresql server to the servers list in pgAdmin it returns the dreaded Postgresql: 'fe_sendauth: no password supplied' error. ???

There is no password and all the previous XP PCs successfully connect using No Password.

I am using postgresql 9.3, pgAdmin 1.18, and I've tried ver 1.16 with same result.

Has anyone else experienced this issue and possibly found a solution?


texts: SQL Server database backup on Remote site

texts:
We have 4 different sites and each site has a SQL Server.

Our requirement is to have a SQL Server backup or database (Mdf files) of site 1, 2, 3 in site 4.

In case of disaster on any of the site  we need most recent database of the site.

each of the site is connected in network with very limited bandwidth.
As the database size is more it is not possible to take complete backup every time.

For the above scenario can any one suggest me a better solution?


texts:
I've googled. I keep getting answers that have to do with a PHP function named "serialize". I won't be working with PHP, so that solution won't work for me sadly.
I worked with a NoSQL db (MongoDB I think) before, and I basically define a collection as such
images [
    {"title": "Cute puppy", "file"="cute.png", "tags": ["cute", "puppy", "summer", "pool"]}
]

I'm wanting to store multiple values in one row for the tags column (honestly I don't know if I said that right, just began searching about MySQL)
+------------+----------+--------------------------+
| title      | file     | tags                     |
+------------+----------+--------------------------+
| Cute Puppy | cute.png | cute,puppy,summer,pool   |
+------------+----------+--------------------------+

The only idea that's coming to me is if I were to use MySQL to read the row, then just split the tags by a comma, that'd work most likely, but I'd be working with many more "tags" possibly in the millions.
My question really is, is MySQL a good choice if you want to store multiple values in one column?


texts:
its my first question, please bear me.
I have following tables

Item

id
name
description

itemPurchaseDetail

id
item_id(FK)
branch_id(FK)
purchaseRate
date

Stock

id
item_id(FK)
branch_id(FK)
quantity

I have to get all the items in specific branch(let suppose branch_id=1), its quantity in stock, its name, its description and its last purchase rates. This bold part is smashing my head.

LINK to fiddle: fiddle


texts:
I have set the environment variable SQLCMDEDITOR=gvim to invoke an vim with the ED command of SQLCMD. From my old Windows 7 computer, it used to work.

I have now a new laptop (windows 10) and, when attempting the same thing I get, on the same server:

C:\>sqlcmd -S <serverName>
1> SELECT GETDATE()
2> GO

-----------------------
2019-12-13 09:11:47.493

(1 rows affected)
1> ED
Sqlcmd: Error: Internal error at ExecProcess (Reason: The filename, directory name, or volume label syntax is incorrect).

I have checked that gvim is in the PATH. I have tried to set the variable to: gvim; gvim.exe; C:\Progra~2\Vim\vim81\gvim.exe; notepad.exe. Always the same.

Why is that ?


texts: How to use the result of an insert as foreign key in another insert?

texts:
I have a temporary table with lots of data. I want to populate two persistent tables with excerpts of that data, preferably in one go.

Problem: after insert on tableA, I want to reuse the generated id as foreign key and add it to the insert for tableB. But is that possible at all?

Pseudocode for separated inserts:

INSERT INTO tableA (id, col1, col2, col3)
    SELECT(col1, col2, col3) FROM tmp_table
    ON DUPLICATE KEY UPDATE id = last_insert_id(id);

INSERT INTO tableB (fk_id, col4)
    SELECT (fk_id_from_above, col4) FROM tmp_table
    ON DUPLICATE KEY UPDATE...

These are my tables:

//a simple temp table with lots of data
CREATE TABLE tmp_table (
 id bigint(20) NOT NULL AUTO_INCREMENT,
 col1 varchar(20) NOT NULL,
 col2 varchar(20) NOT NULL,
 col3 varchar(20) NOT NULL,
 col... varchar(20) NOT NULL,
 colN varchar(20) NOT NULL,
 PRIMARY KEY (id)
)

//this table should be filled from tmp_table and return the generated id for each row
CREATE TABLE tableA (
 id bigint(20) NOT NULL AUTO_INCREMENT,
 col1 varchar(20) NOT NULL,
 col2 varchar(20) NOT NULL,
 col3 varchar(20) NOT NULL,
 PRIMARY KEY (id)
)

//this table should reference the id from tableA (foreign key)
CREATE TABLE tableB (
 id bigint(20) NOT NULL AUTO_INCREMENT,
 fk_id bigint(20) NOT NULL,
 col4 varchar(20) NOT NULL,
 PRIMARY KEY (id)
)

I want to create a single INSERT, that first inserts into tableA, then reuses the generated id and persist it together with only some other columns in tableB.


texts: Table size increasing drastically in sql server

texts:
When I ran:
SP_Spaceused TEST. I got this output which is very strange for 20918 rows.
Output:
name    rows    reserved    data       index_size   unused
TEST    20918   6240816 KB  6235480 KB  2304 KB     3032 KB

I Try to gess The reason.
I copy These new records to another table.
select * into TEST1 from TEST

But The result was more strange : on new table the size of these record was
name    rows    reserved    data    index_size  unused
TEST1   20918   5704 KB    5456 KB     8 KB     240 KB

I tried rebuilding the Index on TEST table, but it was not helpful.
Can someone please explain what could be the reason for this?


texts: Possibly a silly question: Can I install MySQL on an External HDD connected to my Router?

texts: I have a 1TB Western Digital External HDD and I was wondering if I can turn it into a MySQL Database? The HDD itself is connected to my router via USB (BT Home Hub 3) and I would like to experiment with some database ideas at home. Is there any special steps I can take to do what I seek, or is it a hopeless endeavour?


texts: No connections to mongodb replset member post outage

texts:
I have a weird issue in MongoDB. Let me explain.

Suppose, I have following components of mongodb:

A shard with a replset containing 3 members, with 1 primary(say A) and 2
secondary nodes (say B and C)
1 mongo-config server
1 mongos server

I am reading the data from "mongos" server with "secondary" read preference.

Lets assume, I somehow created "4 read connections" on mongos server. Now, the connections are divided between secondary replset members.

No. of Connections:

A (primary): 0 connections
B (Ist secondary): 2 connections
C (IInd secondary): 2 connections

Now the real issue starts when I restart mongod service of B (secondary) to replicate the outage type situation, all the read query connections are lost after that even after mongod got re-started.

A (primary): 0 connections
B (Ist secondary): 0 connections
C (IInd secondary): 4 connections

But to solve this, I always have to restart "mongos" service to get back B again working which is painful.

I don't find any feasible solution for this. Can any one help me in sorting this situation.


texts: Get the quantity of sold products on a specific day, keeping all products

texts:
I'm building a database for daily sales report, for example:
2 apples sold for 2.50 on 2021-02-12
1 apple sold for 2.00 on 2021-02-12
6 bananas sold for 3.50 on 2021-02-12
0 pears sold on 2021-02-12
Notice the "0 pears sold"

I have these tables:
Products

id (PK)
name
deleted_at

2
Apple
null

5
Banana
null

6
Pear
null

Orders

id (PK)
sale_date (DATE)

1
2021-02-12

2
2021-02-12

ProductOrders
|order_id (FK)|product_id (FK)|quantity|price|
|1 | 2 (it is "Apple") | 2 | 2.50 |
|1 | 5 (it is "Banana") | 3 | 3.50 |
|2 | 2 (it is "Apple") | 1 | 2.00 |
|2 | 5 (it is "Banana") | 3 | 3.50 |
I would like to get the products sold on 2021-02-12 grouped by price, keeping products that have not been sold so that I can write "0", as in the example.
I tried this query:
SELECT
    p.id AS prod_id,
    p.name AS prod_name,
    sum(po.quantity) AS quantity_sold,
    po.price AS price
FROM products p
    JOIN ProductOrders AS po ON p.id = po.product_id
    JOIN Orders AS o ON po.order_id  = o.id
WHERE o.sale_date = THE DATE
GROUP BY p.id, po.price
ORDER BY p.name, po.price

With this query I get:
2 apples sold for 2.50 on 2021-02-12
1 apple sold for 2.00 on 2021-02-12
6 bananas sold for 3.50 on 2021-02-12
Without the "0 pears sold", because the query discards the products that have not been ordered in "that date"
How can I to edit this query to get all products, also if they has not been sold in the date? Because I need to write 0 for the products has not been sold in "that date"
Thank you in advance and sorry for my bad english
EDIT
I forgot to say that I tried to use LEFT JOIN, but the problem is that in this way, it will keep all orders
SELECT
    p.id AS prod_id,
    p.name AS prod_name,
    sum(po.quantity) AS quantity_sold,
    po.price AS price
FROM products p
    LEFT JOIN ProductOrders AS po ON p.id = po.product_id
    LEFT JOIN Orders AS o ON po.order_id  = o.id AND o.sale_date = THE DATE
GROUP BY p.id, po.price
ORDER BY p.name, po.price

This query will keep all "ProductOrders" so it will returns many many rows, so I can't use this solution
SOLUTION
Thanks to the user nbk for this solution (I just added the WHERE clause to discard deleted products that have not orders on "THE DATE"):
SELECT
    p.id AS prod_id,
    p.name AS prod_name,
    p.deleted_at,
    sum(po.quantity) AS quantity_sold,
    po.price as price
FROM products p
    LEFT JOIN (ProductOrders AS po
        INNER JOIN (SELECT * FROM Orders WHERE sale_date = '2021-02-12') AS o ON po.order_id = o.id
    ) ON p.id = po.product_id
WHERE (p.deleted_at IS NULL OR (p.deleted_at IS NOT NULL AND po.product_id IS NOT NULL))
GROUP BY p.id, p.name, p.deleted_at, po.price
ORDER BY p.name, po.price;


texts: Transferring large amount (84 million rows) of data efficiently

texts:
I have about 84 millions rows.  Of those all of them needs to be transferred to a separate database on the same server, then I delete to delete about 60 millions rows from the source database.

The 84 million rows are all in the same table.  That table alone accounts for 90% of the whole database.

So...
Source: 84 million rows -> 24 millions rows
Destination: 0 rows -> 84 million rows

Source is running full recovery mode, destination will be running simple.

I am wondering what would be the most efficient way to do this?

Plan A:

1) INSERT INTO destination SELECT * FROM source

2) TRUNCATE source

3) INSERT INTO source SELECT * FROM destination WHERE keep_condition = 1

Plan B:

1) Restore a backup of source database as the destination database

2) Drop every tables except the one needed on the destination database

3) TRUNCATE source

4) INSERT INTO source SELECT * FROM destination WHERE keep_condition = 1

Plan C:

1) INSERT INTO destination SELECT * FROM source

2) DELETE source WHERE keep_condition = 0

or something else?

Thanks


texts: SQL query to count number of payments made in a time period with data from two different table

texts:
Could someone please tell me how to write sql query for the following question? I am not able to get how to write the condition where it selects those user_id which is present on the said date and also made a payment in the last 90 days. The question is as follows:

Every day a player comes in the game, his/her user_id and date when he/she entered the game is   recorded in a table say Table DAU. It has two columns. 1. user_id 2. date

On a particular date, for a player there can be only one entry (No multiple entries on same day). However, the same player can come on different dates.

This table has data from 1/1/2015 (i.e January 1st) onwards. Below are sample entries from this table.

user_id date
1001    6/1/2015
1002    6/1/2015
1003    6/1/2015
1001    6/2/2015
1002    6/2/2015
1004    6/2/2015
1002    6/3/2015
1003    6/3/2015
1005    6/3/2015

Table Payment

Also, every time a player makes a payment in the game, his/her user_id and timestamp when he/she made the payment is recorded in a table, say Table Payment. It has two columns.       1. user_id 2. payment_timestamp

A player can make multiple payments on the same day and across days also.

Below are sample entries from this table.

user_id payment_timestamp
1001    6/1/2015 07:00
1001    6/1/2015 07:30
1002    6/1/2015 10:00
1001    6/2/2015 13:50
1002    6/2/2015 08:00
1004    6/2/2015 09:00
1003    6/3/2015 10:00
1005    6/3/2015  08:30

We define 90-Day Payer DAU for a particular date, say 6/1/2015 as follow:

Count (Player who came on 6/1/2015 and has made at least one payment in the game in last 90 days i.e from 3/4/2015 to 6/1/2015)

Similarly, 90-Day Payer DAU for 6/2/2015 will be:

Count (Players who came on 6/2/2015 and have made at least one payment in the game in last 90 days i.e from 3/5/2015 to 6/2/2015)

Question:

Using the DAU Table and Payment table, please write a SQL query to produce the below table.

Date    90_Day_Payer DAU
6/1/2015
6/2/2015
6/3/2015
6/4/2015
6/5/2015
6/6/2015
6/7/2015


texts: Restoring SQL Server 2000 database to SQL Server 2008

texts: Is it necessary to restore system databases of SQL Server 2000 to SQL Server 2008, if I am restoring my other databases from 2000 to 2008?


texts: How to check if subscriber replication initialization has timed out

texts:
I've added merge replication to a SQL Server 2005 database and have added a SQL Server 2000 subscriber. Creation of the publisher, subscriber and snapshot creation went fine, but there seems to be a problem with the initialization.

After about 1 hour and 40 minutes the following error appeared in replication monitor during the initialization stage

  The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

As far as I can tell this process is still running as sp_who2 gives the following:

SPID  Status                         Login HostName BlkBy DBName   Command     CPUTime DiskIO LastBatch      ProgramName                                  SPID
----- ------------------------------ ----- -------- ----- -------- ----------- ------- ------ -------------- -------------------------------------------- -----
56    RUNNABLE                       sa    HOST   .   database BULK INSERT 27126   287567 09/26 10:03:16 HOST-database-database_pub-REMOTEHOST-6 56

and select * from sysprocesses gives

spid   kpid   blocked waittype waittime    lastwaittype                     waitresource dbid   uid    cpu         physical_io          memusage    login_time              last_batch              ecid   open_tran status                                                                                nt_username                                                                                net_address  net_library  loginame
------ ------ ------- -------- ----------- -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ------ ----------- -------------------- ----------- ----------------------- ----------------------- ------ --------- --------
56     5392   0       0x0042   31          OLEDB                                          7      0      27266       288945               0           2011-09-26 10:02:29.717 2011-09-26 10:03:16.780 0      2         runnable                                                                                000C29092F59 TCP/IP       sa

Is the process still running or has it got stuck and are there any tools I can use to see what's going on here?

Thanks,

Tom

Update

The process described above was no longer visible from sp_who2 or sysprocesses around 6 hours later. There don't seem to have been any errors in the logs, and it doesn't look like the subscriber was correctly initialised as the agent attempts to initialise it again on synchronization. Does anyone have any further ideas?

Kind regards,

Tom


texts: Compile a PLSQL package code that was passed to an sqlplus session via a shell script?

texts:
I have two files: (1) and (2)

(1) A Shell script - wrapper.sh

#!/bin/sh
echo "start of shell script"

FILE_NAME="XX_PKGb.sql"

sqlplus user/password @sql_deploy.sql $FILE_NAME

echo "end of shell script."

exit $RETURN_CODE

(2) A plsql script - sql_deploy.sql

set serveroutput on

set term on

set echo off

set feedback off

set define on

set verify off

define SQL_SCRIPT=&1

declare

  v_sql            varchar2(240);

begin

    v_sql := 'ALTER SESSION SET CURRENT_SCHEMA = XXEOM';

      --alter the schema (this works)

      EXECUTE IMMEDIATE v_sql;

      --compile the package (this throws an error)

      @@'&SQL_SCRIPT';

end;
/

exit;

The file XX_PKGb.sql is also present in the same location as the above two files. When I run the shell script, i see that the sqlplus session executes the sql_deploy.sql script, it was able to alter the schema, however, while compiling the package, it threw the below error. How do I compile the piece of code in this sql file please:

CREATE OR REPLACE PACKAGE BODY XXG_PKG
*
ERROR at line 15:
ORA-06550: line 15, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge


texts: Oracle SQL count occurances of character in VARCHAR

texts: Is is possible to count the number of occurrences of a character in a VARCHAR in oracle? Something that would allow me to determine '11001110' has 5 ones?


texts: Problem with replicate-rewrite-db not working

texts:
I have replication setup that has been working great. We have a master (prod) and a slave (reporting) that have been running for several weeks. We are changing the slave database through replication and that has been running well. That is until an ALTER TABLE statement was replicated.

As you can see in the error statement, it looks like the --replicate-rewrite-db option was not applied and that caused the failure. But why would this work for months and fail with just the ALTER TABLE statement. Also what do I need to do with the replication options so this will work properly?

Slave SQL: Error 'Table 'prod.user' doesn't exist' on query.
Default database: 'reporting'. Query: 'ALTER TABLE `prod`.`user`
ADD COLUMN `new_col` TINYINT(1) NOT NULL DEFAULT '0'', Error_code: 1146

replication settings from my.cnf:

replicate-wild-ignore-table = mysql.%
replicate-rewrite-db = prod->reporting
replicate-do-db = reporting
replicate-ignore-table = reporting.user


texts: A Postgres monitoring script, check_postgres.pl, is warning me that a large number of rollbacks are occurring on a database server. How can I investigate what queries are being rolled back? I have tried searching the Postgres log file but it doesn't contain any instances of the word "rollback" and I can't see anything in the Postgres manual about enabling rollback logging.


texts: Performance counter MSSQLInstance\SQL Errors\Errors/sec\Info Errors whats in it?

texts:
Im wondering what kind of errors would show up in the:

  SQL Errors\Errors/sec\Info errors

performance counter. I read the TechNet article it just states:

  Information related to error messages that provide information to
  users but do not cause errors.

Anyone can shed some light on this, examples of what errors might show up here is more than welcome.


texts: Migration DB postgresql in windows to Red Hat(linux)

texts:
I would like to know how I could migrate a postgresql database which is currently in a Windows operating system to Red Hat? I'm a beginner and I don't know where to start.

Thanks .


texts: Converting SQL to Relational Algebra / Calculus

texts:
I have designed a schema and generated a few SQL queries. I'm using PostgreSQL.

For example:

CREATE TABLE train
(
  train_code SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE journey
(
  journey_id SERIAL PRIMARY KEY,
  int INTEGER,
  train_code REFERENCES train(train_code)
);

CREATE TABLE price
(
  journey_id REFERENCES journey(journey_id),
  price INTEGER
);

Give me all train names that commence from train_code NYC or SFO that are priced $50 or more. (assume the price table is in $ already).

SELECT train.name
JOIN   train
JOIN   journey on train.train_code = journey.train_code
JOIN   price on price.journey_id = journey.journey_id
WHERE  price.price >= 50
AND    journey.train_code IN ('NYC', 'SFO')
AND    journey.int = 1 -- (first train of the journey)

I am unsure how to convert this into a relational algebra and/or calculus query. I have looked at a few converters e.g. http://dbis-uibk.github.io/relax/calc.htm but in this calculator 'join' for example and 'in' is not allowed.


texts:
I want to order a table according to a char field using Levenshtein distance or similar because some entries may contain underscores or grammar errors.

It is a table with all the books I plan to read and it contains repeated entries I need list all the entries ordered by the field name, It should take the previous row and find the closest text.

This is a failed attempt I made:

SELECT name,
levenshtein(substring(name from 1 for 200), lag(name) OVER (PARTITION BY name))
FROM books WHERE name <> '';

Thanks


texts: How many requeries/second can my server handle?

texts:
I have a dedicated server and my provider doesn't want to tell me how many requeries/second MySQL can handle.

Is this just in relation with MySQL or also with other things?

Operating system: CentOS 6.5 with Parallels Plesk 12 (64-bit)
server: UNIX socket
version: 5.1.73

I can provide more info if asked, thank you.


texts: How to get sql server login name and their roles in a query

texts:
I Need to create a query in SQL Server that will return login name and role.

However there's connection between the tables


texts: System Stoplist empty after installing SQL Server 2016 Express Edition SP1

texts:
I recently installed SQL Server 2016 Express Edition and then applied SP1. But I see my system stoplist empty for fulltext search.

Select * from sys.fulltext_stoplists;

This does not return any rows. I remember it worked for me in SQL Server 2014 Express Edition.

Can somebody please help?


texts: Database design : multiple category of users login into same system

texts:
In our new system we have three type of people

Business owner
Assistant of business owner
Staff (a skilled employee)

I am having following entity

[Business] - include detail about business owner
[Assistant] - detail about the assistant
[Staff] - detail about the staff

Business rules

There will be only one business owner for a given business, means there can be only one login for business owner. He is like a super user for his business.
Business owner can be staff as well.
A single business can contains more then one staff and each staff have different kind of rights/permissions can be configured by business owner.
Only one assistant allowed per business and rights/permissions can be configured by business owner.

What would be right way to go?

Any help/suggestion appreciated!!!


texts:
I have an Oracle RAC (12.1.0.2) installed on ESXi 6.5. RAC nodes are hosted on different physical servers and there is a storage hosting their shared disk.

Here is my problem:
Trying on Toad, SQL*Plus, Weblogic Creating data source does not matter... On every method,  Oracle gives ORA-01017 error almost 50% of my tries.

I can't run my software, UIs, services, etc. since I usually fail establishing a connection.

I suspect entropy value but I really dont know what to modify.

cat /proc/sys/kernel/random/entropy_avail generally outputs around 3000

Please find my samples of connection to SQL*Plus:

[oracle@buscordb-nd01 ~]$ sqlplus testuser/testpassword@buscordb

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 20 23:10:47 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Jan 20 2020 23:10:28 +02:00

Connected to:
Oracle Database 12c ...

SQL> exit
Disconnected from ... 12.1.0.2.0 - 64bit Production

[oracle@buscordb-nd01 ~]$ sqlplus testuser/testpassword@buscordb

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 20 23:10:50 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Jan 20 2020 23:10:47 +02:00

Connected to:
Oracle Database 12c ..

SQL> exit
Disconnected from Oracle...

[oracle@buscordb-nd01 ~]$ sqlplus testuser/testpassword@buscordb

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 20 23:10:56 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

As you can see, I get this error with same user (on the database node itself, it is not event a remote machine).

Waiting your valuable suggestions.


texts: Managing static (or reference) data between different environments?

texts:
This is a very specific question to a scenario I am working on, but also likely a common problem among SQL developers/database administrators. I am lucky enough to be able to change the current database processes in my job, and I would greatly appreciate any input

Current Database Use: Our company's website is database driven and requires a large set of static data to be accurate on each environment (testing, staging, production) for the site to function correctly. The data in these tables determines page text, ability to save responses, and what portion of data is loaded onto the page.

Environment Setup: all of our developers use a shared database where they drop in, modify, and remove particular rows from tables holding static data. This allows their HTML/code changes to function properly.

Problem: I am then required to capture and "migrate" these changes to staging and production. I've currently been using Red Gate's Data/Schema Compare, but it has been increasingly difficult for me to identify which changes were meant to be migrated and what was test/experimentation. Thus our team decided to use Visual Studio Database Projects for schema, since developers can now commit their own changes

This still poses an issue for data. Red Gate data compare captures all of the changed data on the testing environment, but there is no confirmation whether that data is correct and if there are any data modifications that should not be pushed to staging. Each build cycle, there is around 400-600 INSERTS/UPDATES/DELETE rows (all coming from the same ~15 tables that the website uses to function).

Solution? I've looked into several means of managing these data changes:

Continue using Red Gate data compare. This doesn't seem like a great option due to: a) being unable to determine which changes are correct and b) there isn't any database versioning since all data is being compiled and pushed between environments.
Require each developer to commit data changes and sequence them in order of arrival in GIT source control (001_,002_, etc). During each build to the next environment, execute schema changes followed by the data changes in the order they were committed/created. Disadvantage: the scripts needs to be written well and should be able to run multiple times without affecting data adversely. Also, time consuming for developers and myself (reviewing each data script to make sure it will function correctly).
Before a build, create MERGE statements for each of the ~15 tables. This will work similar to #1, except I would be able to store what the data looked like during X build (thus there would be some sort of database versioning). Disadvantage: no one is confirming changes, I am simply assuming all changes are accurate.
?


texts: ORA-00604 (error occurred at recursive SQL level string) hides the real error

texts:
My multithreaded program inserts records into a table. ORA-00001 (unique constraint violated) should not be fatal.

But prod environment has a complex configuration with read-only schema and the table for my inserts being a synonym for a remote table, which is partitioned.

Inserts work fine until PK violated, then instead of SQLCODE=-1 I'm getting SQLCODE=-614 and a multiline description:

insert into CDRGPRSIMEI (ms_id,startdatetime,calltype,imei) values (:b0,to_date(:b1,'YYYYMMDDHH24MISS'),:b2,:b3)
^
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
ORA-06512: at line 120
ORA-16000: database open for read-only access
ORA-00001: unique constraint (CDRIMEI.PK_CDRGPRSIMEI) violated
ORA-02063: preceding line from CDRIMEI_PRIM_PPB

What can cause this? Is it always related to triggers? If DBA can't fix it on prod, how to reproduce this on my test env. (e.g. by writing a special trigger)?


texts:
First table:

id | username | firstname |  surname  | address
---+----------+-----------+-----------+---------
1  | johnie   | john      | something | blabla
2  | alice    | something | something | blabla
3  | bar      | cola      | something | blabla
4  | whiskey  | whiskey   | something | blabla

Second table:

aincrement | id | user_id
-----------+----+---------
1          | 1  | 3
2          | 1  | 4
3          | 2  | 3

If I do

SELECT username, firstname, surname
FROM first_table
WHERE username != "johnie"

I get all the rows except johnie...

If I do:

SELECT DISTINCT T2.user_id
FROM second_table AS T1
JOIN second_table AS T2 ON T1.id = T2.id
WHERE T1.id = 1 AND T2.user_id <> 1

I get 3,4

But i would like to get 3 and 4 with all the others details (username, firstname, surname....)

My expected output

id | username | firstname |  surname  | address
---+----------+-----------+-----------+---------
3  | bar      | cola      | something | blabla
4  | whiskey  | whiskey   | something | blabla

I have to use where username="johnie" (first_table). That user has id 1. Then I need to obtain user_id(s) from second_table (3, 4) and in output I need only basic info for users with id 3 and 4.


texts: MySQL turn rows into columns without naming each single row in the syntax

texts:
I have a table that looks like this (d could be the day, and m1, m2, ..., m1440 the values of the single minutes of the day):

d  m1   m2   m3    ... m1440
1  1    2    3     ... ...
2  1441 1442 1443  ...
3  2881 2882 2883  ...

and I what an output like this (one row with all the minutes' values):

d m value
1 1 1
1 2 2
1 3 3
...
1 1440 1440
2 1 1441
2 2 1442
2 3 1443
...
2 1440 2880
3 1 2881
3 2 ...
3 3 ...

Is there a way do this with MySQL?

Having to address every single column in the syntax should be avoided, because this is supposed to work for 1440 columns.


texts: Using the same primary key as a foreign key for every table?

texts:
I'm trying to design a database to hold information that is compiled from different lists. I'd like to record each person listed and assign an ID but due to the nature of the lists and their lack of accuracy, a person could be listed as having multiple names, aliases or misspellings, also they may have no date of birth listed or multiple dates of birth. There are about 8 different attributes that can have no information for one person person can have multiple records for another person.

My question is.. if I have one table that lists the person's ID and where I got the information, would it make sense to have 8 other tables that use that ID as a foreign and primary key and also have columns such as the date(s) of birth associated with that person? Or is this overusing the primary key from the other table?

For example

person
person_id | list_name | list_source

date_of_birth
person_id | year | month | day

name
person_id | first | middle | last

I'm very new to all of this and don't know enough to even start my google search on different methods of setting up a database or what a database of this type would be called (if that's a thing?) so even proper terminology for what I'm doing what be appreciated!


texts: SSIS File System Task (Copy File Operation) OverWriteDestination functionality

texts:
I was unable to find any documentation on the SSIS File System Task component OverWriteDestination property functionality  for a Copy File Operation.

I understand what it is used for but how does it actually cause the component to behave when the OverWriteDestination is set to false?

If set to False, will the component simply skip the file and not try to copy it or will it copy it with a different name, perhaps a number appended to the end of the file name being copied (for example filename(1).text)?


texts: What are some likely causes of high Client:ClientRead AAS in AWS RDS?

texts:
I have a particular query, its a simple select with an inner join. Seems to be pretty well indexed. But it's registering as a worst offender in performance insights. With a 0.13 on CPU and a 4.5 on Client:ClientRead.
All the documentation and articles I can find indicate this is common for bulk data copying. But I don't think this is the case. Since it's such a basic select. It's being called around 70 times per second. Which isn't even that high compared to others I have in the 500 calls per second, and it's only serving back about 7 rows, 10 columns each.
So other than bulk data copying, what other factors might be giving me a high AAS for clientRead?


texts: Database schema tracking patient stats over time

texts:
I want to create a database which allows to record patient reading data points like: weight, height, pressure, etc., over time.

I found a database schema like the one discussed in this Q & A which is looks similar to my needs. I'm wondering if such design offers a good performance for a large number of users?

For example, if we're planning to track 10 parameters for 10 000 users at least 20 times a year - it's around 2 000 000 rows/per year.

Available engine - InnodDB, MyISAM.

DB healthstats
TABLE user
    memberID (int*, auto increment, unsigned, primary key)
    name (varchar, 50)
    gender (char, 1)
    birthdate (date)

TABLE reading
    readingID (int*, auto increment, unsigned, primary key)
    memberID (int*, FK: TABLE user)
    date (datetime)

TABLE stat
    statID (int*, auto increment, unsigned, primary key)
    readingID (int*, FK: TABLE reading)
    type (varchar, 3)*
    value (decimal 4.1)*


texts: what oracle data dictionary view can be used to decribe types defined in a plsql package?

texts: What oracle data dictionary view can be used to describe types / subtypes defined in a plsql package?


texts: Import only selected column from tsv into mongodb

texts:
I have a very large tsv file. I need to upload some data from the file into MongoDB (just two columns, title and body).

I don't want to import the full file, as I only really need the two columns.

I know how to upload the full file into MongoDB:

mongoimport --db xyztsv--c review1 --type tsv --file /Reviews1.tsv -f member_id,product_id,date1,no_helpfeedback,no_feedback,rating,title,body

Is there any way to only upload the last two columns (title and body) using an existing command or utility? Or, do I have to write a JSP function for it?

I know it can be done through cygwin on windows but Is it possible to do it directly while importing on MongoDB?


texts:
I need to delete a schema in my PostgreSQL database. I would like to make sure that the schema is not used in another schema (for instance : this schema is used for a view in another one). Is there a way to list all the dependencies in the rest of the database ?

I have tried DROP SCHEMA mySchema ; but the given list of dependencies is partial...

Many thanks for help !


texts:
I'm on a test VM based on VMWare with MariaDB + nginx installed; when looking with htop this is the situation:

So we have 26 threads under the main process.

I know that inactive threads doesn't impact so much in memory / performaces, but for my "know how", is there a way (parameters?) to reduce the number of child threads under MariaDB / MySQL?

Googling around I've found something related to max_connections and thread_concurrency parameters that I've set both to 5 but once restarted nothing changed.

Thank you very much for your answers!


texts: SQL Server CONTAINS ignores single quote before asterix

texts:
Let's say I have table NamesTable, which has text index on Name column

DECLARE @String2 NVARCHAR(20) = '"SomeName''*"'
SELECT * FROM NamesTable
WHERE CONTAINS(Name, @String2)

What does single quote in CONTAINS string mean? Why is it ignored?


texts: How to enforce lowercase for all data in specific column?

texts:
I have this table.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT
);

I'm looking for a way to enforce lowercase for the name values.

For example, when I save a record using this command:

INSERT INTO users (name) VALUES ('JOHN');

or:

INSERT INTO users (name) VALUES ('John');

I want it to be saved as john without converting the case beforehand using my programming language i.e. JavaScript, Ruby and etc.

Is it possible to make use of LOWER() function inside this CREATE TABLE command?


texts: Error 1242(21000) after creating following trigger and performing delete operation

texts:
I want to create a trigger which copies the record from maintable to undostack before deleting it in maintable and undostack table acts a stack of 5 records space and this trigger performs a push operation.
This is my trigger
CREATE TRIGGER undo_op
BEFORE DELETE ON maintable FOR EACH ROW
BEGIN

When undostack is not full
IF (SELECT COUNT(*) FROM (SELECT * FROM undostack)u)<5 THEN
INSERT INTO undostack VALUES(((SELECT COUNT(*) FROM undostack)+1),
(SELECT id FROM maintable WHERE id=OLD.id),
(SELECT descp FROM maintable WHERE descp=OLD.descp),
(SELECT cat FROM maintable WHERE cat=OLD.cat),
(SELECT dat FROM maintable WHERE dat=OLD.dat),
(SELECT amt FROM maintable WHERE amt=OLD.amt));

When undostack is full and has to delete the oldest record entered by updating. here undo_no acts as pointer.
ELSE

1<-2
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=2),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=2),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=2),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=2),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=2)
WHERE undo_no=1;

2<-3
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=3),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=3),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=3),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=3),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=3)
WHERE undo_no=2;

3<-4
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=4),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=4),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=4),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=4),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=4)
WHERE undo_no=3;

4<-5
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=5),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=5),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=5),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=5),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=5)
WHERE undo_no=4;

5<-update with the values which are to be deleted from maintable
UPDATE undostack SET
id=(SELECT id FROM maintable WHERE id=OLD.id),
descp=(SELECT descp FROM maintable WHERE descp=OLD.descp),
cat=(SELECT id FROM maintable WHERE cat=OLD.cat),
dat=(SELECT id FROM maintable WHERE dat=OLD.dat),
amt=(SELECT amt FROM maintable WHERE amt=OLD.amt)
WHERE undo_no=5;
END IF;
END

The trigger has no errors
but when I execute
DELETE FROM maintable WHERE id=10;

There is following error
ERROR 1242 (21000):Subquery returns more than 1 row
Please help me with this error
I'm using MySQL 5.7.3


texts: Does SQL server 2008 log instance level security permission changes

texts:
I just recently logged onto one of our Test environments to find i did not have permissions, turns out my instance security Login had been disabled

I have a feeling towards whom may have done this but i need to see if i can see this in the logs,

Is it possible to see when and possibly whom altered these permissions in the SQL logs somewhere and if so how

Unfortunately being a test environment the only database on the instance has probably been overwritten a number of times in the meantime.

for clarity i want to know whom disabled the login


texts: Restrict range of dynamic ports available to SQL Server

texts: Is there a way to limit the range of dynamic ports available to SQL Server from the database side, or database server side? Our SOP is to use static ports across the network firewall and a vendor is having trouble locking down their ports. Theoretically, if we allowed a range of 1000 ports inside the dynamic range (49152–65535) across the firewall how would I limit SQL Server to only assign a dynamic port inside that range?


texts: SSRS - : How to make a conditional formatting for matrix by group?

texts:
I currently have a report produces a matrix, displayed per user group.  (1 user group per page, with 35 user groups/pages)
I want to highlight the row in pink in this condition:
=IIf(Fields!CountFailures.Value>=1,"Pink","White")

However, it is not showing any colour.  Please help! (I am using PowerBI Report Builder... should be same as SSRS)


texts:
If a write on log shipping transaction backup gets corrupted on one of the transaction file due to network or media issue, and hence the log shipping chain gets broken, is there any other way to restore the database to the point in time ? I understand one could do full backup or diff backups but to restore to point in time, how can we get the consecutive tran logs to be restored?
Any pointers on this will be beneficial.
Thanks
(Even if the solution is to contact Microsoft or only God can help you, I will buy that :-))


texts: Multiple Functional Dependency to the same dependency

texts:
I am looking for the 3NF form of the following relation:

1. emp_no -> phone_no, office_no, dep_no, proj_no
2. emp_no, date -> job, salary
3. phone_no -> dep_no, office_no, area
4. proj_no -> dep_no, p_buject
5. dep_no -> mgr_emp_no, d_budget
6. mgr_emp_no -> dep_no

I can only derive the first dependency to:

1. emp_no -> phone_no, office_no, proj_no

because proj_no and phone_no are depended by dep_no. Then the rest is not decomposable, therefore they are copied over on my sheet.

As you can see, 5 and 6 are dependent on each other. I looked through resources on the Internet, I have never seen such relationship before (being dependent and parent of each other). How can I make these functional dependencies to 3NF format?


texts:
I have an Oracle Enterprise Edition as a source and taken the dump using the oracle data pump(expdp) and the dump is imported using (impdp) to an Express Edition of Oracle. The import was working perfectly without any error if the target database is Express Edition ( 11.2.0.2.0). But when i import the same data into the oracle Standard Edition then i'm getting the following error.

  ORA-31693: Table data object "SchemaName"."Table_Name"
  failed to load/unload and is being skipped due to error:
  ORA-02354:
  error in exporting/importing data
  ORA-26097: unsupported conversion
  for column SYS_NC0000500006$ (from type 58 to type 23)

The table has a column of custom type which is made up of table of number.

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 APP_ID                                    NOT NULL  NUMBER(38)
 DATE                                      NOT NULL  DATE
 P_ID                                      NOT NULL  NUMBER(38)
 D_ID                                                VARCHAR2(200)
 I_ARRAY                                             NUMBER_ARRAY

The error was caused because of the column I_ARRAY. When i insert the query like following it works perfectly.

insert into PAS_IS_ALERT_DETAIL_LOG values(2, '17-FEB-14',131, 'duo_duo', NUMBER_ARRAY(16183, 16345));

Can some-one help me out to figure the issue. How does it's not working only with the Oracle Standard Edition of 11.2.0.1.0. Is changing the version of SE to other release like 11.1.0.1.0 will solve this issue?

Note : There is no way to change the dump file.


texts: Inserting fetched records into new table

texts:
I'm fetching some records with below query:

;with tb1 as (select * from tb where condition), select ... from tb1 join tb2 condition

I want to insert the records fetched with the above query to a new table. How can I do that?

I tried this:

select * into NewTable from (followed by above query)

and this:

;with Tb1 as (select * from tb where condition), select * into NewTable from (select ... from Tb1 join tb2 condition)

But it doesn't work. Any tips? Thanks.

EDIT:

Acutal QUERY which I need to insert into New Table:

; with dub as
(select docnumber, sum(amount) summedamount from stg_invoice_cust_vw where year(crmcreateddatetime)=2017
group by docnumber
having count(docnumber)>1)
select dub.docnumber, dub.summedamount, sup.slamount SLAmount, (dub.summedamount - sup.slamount) diff from STG_INVOICE_SUP_VW sup inner join dub on dub.docnumber = sup.docnumber
where (dub.summedamount - sup.slamount) <> 0


texts: MySQL table replication on the same server

texts:
I have one the same server (Linux) two databases: db1 and db2 and I want to replicate db1.table1 to db2.table1

I added the following lines to my.cnf and restarted mysql service

replicate-do-table = db2.table
replicate-rewrite-db  = database1->database2

but it's not working. Are those commands correct ? Do I need to set permissions on both databases ?

mysql version: 5.5.25a

Thanks in advance


texts: Getting deadlock on insert and select statement(sql server 2014) in java application

texts:
In my application there are many threads which uses insert and select statement to store/retrieve data from database.

I am getting deadlock in my production environment, I have observe deadlock from application log file.

I am not able to reproduce same situation on my development environment.

Please give me suggestion to:

How to resolve this kind of deadlock?
How to get more clear understanding to capture deadlock?

Thank you


texts: SQLPackage resets database recovery model

texts:
I am using SQLPackage.exe with database publish profiles to deploy database changes to DEV and QC instances. I have databases in simple recovery mode. But when I deploy changes using SQLPackage, it reverts them to full recovery mode.

This is what I am using,

  "C:\Program Files (x86)\Microsoft SQL
  Server\110\DAC\bin\SqlPackage.exe" /Action:Publish /SourceFile:"FILE
  PATH TO .DACPAC" /Profile:"PUBLISH PROFILE.XML"

If I use Visual Studio to deploy project changes using same publish project, same happens.

What am I missing here? As far as I can tell, there is no such flag in Profile. Is this expected behavior of SQLPackage?


texts:
I've got some SSIS packages stored as varbinary, and I can load them into a temp table like so:

    Use SSISDB

    if object_id('tempdb..#package') is not null begin drop table #package end

    create table #package ( package varbinary(max))

    insert into #package (package) EXEC SSISDB.catalog.get_project 'my_folder','my_project'

    select * from #package

this returns a very long varbinary string which starts with 0x

Problem: when I try and convert the varbinary to nvarchar, it is still the same 0x... varbinary. I think it is encrypted.

Question: How can I decrypt these varbinary strings and convert them to a searchable format?

I think I need a key, but I'm not sure where those are stored.


texts: Oracle second date range comparison ignored in where clause

texts:
I have the query below and am receiving an unexpected dataset returned back. I was wondering if you all might be able to explain what is going on for me? I am using Oracle 11g.

I understand having the sysdate check and the date range condition of the where clause is redundant, but as for now, I do cannot change the query without having a rationalization as to why we are receiving the data we are receiving. When I run the query below, I receive 25 records with a created_date of 17-Feb-04 and only 1 with 22-Sep-17. There are many more records in this database with other dates, but for some reason, this is what we are receiving.

SELECT created_date
FROM tableName LOAN
WHERE
loan.created_date > sysdate - 11000
AND loan.SYSTEM_ID ='ALS'
AND loan.BANK_NUM = '021'
AND loan.short_name like 'NAME%'
AND trunc(loan.CREATED_DATE) BETWEEN TO_DATE('09-01-2017', 'MM-DD-YYYY')
AND TO_DATE('10-13-2017', 'MM-DD-YYYY')

If I place sysdate at the end of the query, I receive the expected results (1 transaction with created_date of 22-Sep-17). If I trunc(loan.created+date) > sysdate - 11000 and leave everything else the same, I receive the correct expected result. If I remove the trunc from AND trunc(loan.CREATED_DATE) BETWEEN TO_DATE('09-01-2017', 'MM-DD-YYYY') AND TO_DATE('10-13-2017', 'MM-DD-YYYY') to AND loan.CREATED_DATE BETWEEN TO_DATE('09-01-2017', 'MM-DD-YYYY') AND TO_DATE('10-13-2017', 'MM-DD-YYYY'), I receive the expected results. However, I do not understand why those configurations of the same where clause (or slightly different) produce those results.

I understand that when trunc is used on a date field, it essentially rounds up to the beginning of that date. Therefore, I may see a difference in results when comparing created_date field against sysdate due to the timestamps of those fields, but the results I am seeing do not vary by minutes or hours, but years. Somehow, the second date condition is either being ignored or there is an issue with how I am comparing the sysdate and the date comparison.


texts: Policies RE database triggers in well-designed applications?

texts:
I've heard all kinds of horror stories around gremlins living in database triggers, and--worse--systems being brought down by the addition of a trigger that caused a chain of cascading ones.

I'm considering implementing a strict policy about the use of database triggers. For separation of concerns, the initial thought is to say:

  "Database triggers shall be used only for the purpose of capturing and maintaining the audit trail."

Does anyone have similar policies?
Are there any strong arguments against such a policy?


texts: How to get rid of ORA-28002 message the password will expire within 6 days?

texts:
I have a user getting an ORA-28002 indicating that the password will expire within six days.  I ran the following:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

But when I try to log in as the user, the message is still there. Executing this:

select * from dba_profiles where RESOURCE_NAME LIKE 'PASSWORD_LIFE_TIME';

shows that the values was really changed to UNLIMITED.


texts:
I have been tasked to improve the performance of a SQL Server 2008 R2 query (within a stored procedure) shown in the query execution plan link below.
It currently executes in around 7 seconds and needs to complete in 1-2 seconds if possible. Each execution of the slow query is always around 7 seconds. The results of the query trickle through. The tables being queried are large but not billions of rows.
Row counts

Depending on the parameters being passed the results can either be a few hundred rows (which executes sub second) to > 300K rows (this is slow).
I have included the slow query execution plan and indices that are being utilised by the optimiser. This is the plan for the quicker execution and STATISTICS IO, TIME:

There are two queries being executed. The first one is not the issue. It's the second one that I need assistance with. Although needed, removing the non SARGable predicate (AND FT.TripDistance < ( CONVERT(NUMERIC(10,0),FT.TripTime) * 83.33 )) makes slight difference to the speed

Removing the function [FN_GetLocalTime_FromUTC_BasedOnTZId] makes slight difference to the speed, here's the STATISTICS IO TIME removing both non SARG where clause and function: Here's the execution plan

Here's the output for the unchanged query except for adding a LOOP JOIN hint. This is slower.

I have noticed that in the slow unchanged query plan, the actual number of rows (300330) for the Index Seek on the FACT_trip_Statuses table is close to the final output (299887). However, the actual number of rows (4.87m) on the index seeks on the xFactTrip_Annex, FACT_Trip, and FACT_Trip_Attributes is way out. How do I fix this?  Adding OPTION RECOMPILE make little difference
I tried adding trace flag 4199 DBCC TRACEON (4199, -1); and retrying with and without JOIN hints, didn't help.
HASH join hint and TF 4199

LOOP join hint and TF 4199

No join hints and TF 4199

Indices
CREATE NONCLUSTERED INDEX IDX_FACT_Trip_StartDateUTC_VehicleKey_Includes ON dbo.FACT_Trip
(
    StartDateUTC ASC,
    VehicleKey ASC,
    EndDateUTC ASC
)
INCLUDE (   DriverKey,
    DrivingTime,
    TripDistance,
    TripTime)

CREATE NONCLUSTERED INDEX IX_xFactTrip_Annex_StartDateUTC_MonthlyProcessing ON dbo.xFactTrip_Annex
(
    StartDateUTC ASC
)
INCLUDE (   VehicleKey,
            Spd20Count, Spd20Distance,
            Spd30Count, Spd30Distance,
            Spd40Count, Spd40Distance,
            Spd50Count, Spd50Distance,
            Spd60Count, Spd60Distance,
            Spd70Count, Spd70Distance,
            SpdCat1,SpdCat2,SpdCat3,
            TotalIdling,
            PTOTime,
            TripFuel)

CREATE NONCLUSTERED INDEX IX_FACT_Trip_Attributes_StartDateUTC_VehicleKey_Includes ON dbo.FACT_Trip_Attributes
(
    StartDateUTC ASC,
    VehicleKey ASC,
    EndDateUTC ASC
)
INCLUDE (   Attribute0Distance,
    Attribute1Distance,
    Attribute2Distance,
    Attribute3Distance,
    Attribute4Distance,
    Attribute5Distance,
    Attribute6Distance,
    Attribute7Distance,
    Attribute8Distance,
    Attribute9Distance,
    Attribute10Distance)

CREATE NONCLUSTERED INDEX IDX_FACT_Trip_Statuses_StartDateUTC_VehicleKey_Includes ON dbo.FACT_Trip_Statuses
(
    VehicleKey ASC,
    StartDateUTC ASC,
    EndDateUTC ASC
)
INCLUDE (   HarshAccelerationCount,
    HarshBrakeCount,
    HarshBumpCount,
    HarshCorneringCount,
    ExcessIdleDuration,
    ExcessIdleCount,
    OverspeedCount)

CREATE NONCLUSTERED INDEX IX_StartDateUTC_VehicleKey_IsBP ON dbo.FACT_TripComments
(
    StartDateUTC ASC
)
INCLUDE (   VehicleKey, IsBusinessPrivate)


texts:
What will be the MySQL code for below code in SQL Server.

 SELECT @cols = STUFF((SELECT ',' + QUOTENAME(col+cast(rn as Nvarchar(max)))
   from
    (
     SELECT row_number() over(partition by
    TPID,EAPID,locale,Origin_airport,
    Origin_multicity_gaia_id,dest_multicity_gaia_id_1,
    dest_multicity_gaia_id_2,dest_multicity_gaia_id_3,dest_airport_1,
                   dest_airport_2,dest_airport_3,test_keys
                   order by Origin_airport,test_keys) rn
                   from ##New_Generic
          ) d
                cross apply
                (
                  SELECT 'dest_city_1_image_url_1', 'First' union all
                  SELECT 'dest_city_2_image_url_1', 'Second' union all
                  SELECT 'dest_city_3_image_url_1', 'Third'

                ) c (col, so)
                group by col, rn, so
                order by rn, so
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')


texts:
Side By Side Upgrade and Migration from 2008 to 2016

Old Server Name Test1

New Server Name Test1

Default Instance Test 1

My question is when the new server be named as the old one 'Test1" and it will try to register the dns while the old server is online wont be there a problem as the name is already being registered. In that case what will be the workaround since it is a side by side upgrade and migration I need to use the same server name and need to use  both the servers in order to upgrade and mirgate and then turn off the old server after the switch over


texts:
I am currently trying to see how the object oriented features come in play in Postgres. During a few tests I noticed that when you insert in or delete from inheriting tables that those changes also happen to the supertable and vice versa.

Question 1: How is data physically stored if you have 2 tables and table 1 has no constraints but table 2 has an identity primary key column and also inherits columns from table 1.

Question 2:
Since one table inherits columns from the other and inserts in table 2 result also in population of table 1, is the data stored completely isolated and redundant in two different tuples on different pages or is are the tuples linked somehow? And if its separated, does that mean both tuples get locked in case of a writing operation?

Question 3:
How does an index affect table inheritance?

Question 4: What's an actual use for object oriented features in Postgres?

The tables used:

create table person (
    nachname varchar(30),
    age int,
    eigenschaft varchar(30)
    )

create table praktikant (
    praktikant_id int generated always as identity primary key,
    klassenstufe int) inherits(person);

the insert:

insert into praktikant(nachname, age, eigenschaft, klassenstufe)
values('Schmidt', 16, 'fleißig', 9)


texts:
I have a Kubernetes cluster running with MySQL app (5.7.32 MySQL version). I'm trying to make MySQL app be in a consistency mode, using this command:
FLUSH TABLES WITH READ LOCK; SET_GLOBAL read_only = ON;

When I run this command from inside the app - it works (getting error when trying to write when the DB is locked):
kubectl exec -ti mysql-0 -c mysql -- mysql

mysql> FLUSH TABLES WITH READ LOCK; SET_GLOBAL read_only = ON; INSERT INTO mydb.mytable VALUES('hello');

But When I run this command using kubectl exec command - it doesn't work (this command doesn't fail, and it is able to write to the DB):
kubectl exec -ti mysql-0 -c mysql -- mysql -h mysql \
-e "FLUSH TABLES WITH READ LOCK; SET_GLOBAL read_only = ON; INSERT INTO mydb.mytable VALUES('hello'); "

I also try using a grant privileged user, it didn't help:
kubectl exec -ti mysql-0 -c mysql -- mysql -h mysql \
-e "CREATE USER $USER@'$HOST' IDENTIFIED BY '$PASS'; GRANT ALL PRIVILEGES ON * . * TO $USER@'$HOST';"

kubectl exec -ti mysql-0 -c mysql -- mysql -h mysql -u $USER -p$PASS \
-e "FLUSH TABLES WITH READ LOCK; SET_GLOBAL read_only = ON; INSERT INTO mydb.mytable VALUES('hello'); "

How can I bring my MySQL app to be in a read-only state without running the command from inside the application?


texts: Hive. Over partition by vs where conditions

texts:
I have a table partitioned this way.

# Partition Information
# col_name              data_type

year                    string
month                   string
day                     string
hour                    string

I want to run the query on a whole month. I understand that I could do

where day > 01 and day < 31

But the table is VERY big. I was told it would take too much time and that I should run it separately on every day. I thought about over partition by. Like

select col1, col2, sum(col3) over (partition by day)
from table

but I am not sure how this will work. Would the query work on every day successively. Would it be easier on the cluster? Would col1 and col2 be grouped automatically or would I need to add group by?

Table
col1 col2 col3 month date....
1    s    4    01    01
1    s    3    01    01
1    q    5    01    01

What I want in the result

col1 col2 col3 month date....
1    s    7    01    01
1    q    5    01    01


texts: PL/SQL Procedure: limit number of INSERTS

texts:
I have a procedure that is supposed to take an employee from a certain table and insert it into another table that records who is in a leadership role for a certain period.

The thing is, there can only be a maximum of two employees in leadership roles for a given period.

The primary key in the table is the pair (employee,month) and there is no manual insert, so it's all done trough the procedure.

How do I structure it so that concurrent accesses can't register more than 2 employees in a leadership role for the same month?

I thought about getting a COUNT(*) on the number of rows for that month, but if an insert occurs after the count is registered, it would no longer be valid. Is there any way to control this right on the procedure or do I need to resort a trigger to check after each insert and rollback if the number exceeds 2?

I cannot change the structure of the table, just the procedure.


texts:
I am trying to change the binary log rotation from days to hours. By default there is an option only for days in the MySQL config file: expire_logs_days.

Is there any way to change the binary log rotation from days to hours or even minutes?

I am running MySQL version 5.7.23 on Ubuntu 16.04.


texts: php/mysql merge duplicate records in table and create a new table

texts: please with respect to username and storetype, how can I merge rows to achieve Final table? storetype should be unique per username. There are thousands of usernames and records so php/mysql would help. thanks


texts: best practice for 'staging' relation in stored procedure: use temp table, or view?

texts:
When I write stored procedures that take relation A and output relation Z, I like to create 'staging' relations along the way, so I can inspect the state of the data after each step in the programming logic is applied.

So far, I have been using temp tables to instantiate these staging relations.

Should I be using views instead? If this is the case, then what happens when different views are being joined in the same query, and these different views are hitting the same base tables? Does performance suffer?

Overall, which approach leads to best overall performance? Is there a way to give a general answer that applies to most scenarios, without having to run traces on specific implementations?


texts: Do I need to disable logins while upgrading SQL Server?

texts:
I need to do an in-place upgrade for one of our critical servers: SQL 2012 to SQL 2016. This server has 200+ logins.

Is it advisable to DISABLE these logins for the upgrade? Or, SQL Server will take care of it.

I don't want anyone to login and screw-up something during the upgrade.


texts: Manage multiple short-term tables with same schema or use single table for short-term data?

texts:
My team has proposed to store session state data for each open client session in a table in the database.  The application server creates a new table for each client session as the user logs in via web browser.  The data that needs to be kept for the session is created along with the table, and is not modified (except in rare circumstances).  When the user logs out, the table is dropped.  Orphan tables are purged regularly.

The application server manages these short-term tables.  There won't be any database code that manages the tables or the data.

The data in these tables has relational links to other tables in the database.  The tables will be joined in queries to other tables in the database.

I'd like to find out the pro's and con's of this design, in contrast to others.  For example, all of the same functionality could be implemented in a single table that stored state data for all sessions.  If done this way, there would be one clustered index on the session ID.  Each access to the state data would read all of the data for exactly one session ID.

What are the scalability concerns for creating/dropping multiple tables rapidly?  Assume this occurs on the order of 1K-10K time per hour.


texts: Is there a way in postgres to SELECT the current clients name? I wish to create an audit field using the clients computer name.


texts:
As I gain more experience with PostgreSQL I start to question the existence of PLPython. It's considered an "untrusted" language https://www.postgresql.org/docs/10/plpython.html
What I am wondering is, when or why would anyone need to use this? PLPGSQL is already quite a strong language that allows you to do a lot of things. Has anyone here had the need to use it, and if so, for what?


texts: How to select duplicates into new columns?

texts:
I am trying to select distinct data in new columns!

My Table

productID | imageURL
1         |    a1.jpg
1         |    a2.jpg
2         |    a3.jpg

Target Table

productID | imageURL1    | imageURL2    | imageURL3
1         |    a1.jpg    | a2.jpg       | null
2         |    a3.jpg    | null         | null

I only want 3 images even if I have 100 in my table.


texts: Is there any reason to stop transaction log backups during a maintenance window?

texts:
We have recently had an issue where the transaction log grew and maxed out the storage space. This happened during a maintenance window (Ola Hallengren index Optimize scripts are being used.)  For some reason, someone in the past has set the transaction log backups to stop for 3 hours, it seems to be while the indexing jobs are running (they are set to run for 3 hours).

My organization recently migrated to sql server 2016 from 2008r2. The person who worked on the migration changed the window of not doing transaction logs to be 30 min shorter, so they now start at 5:30am.  I think he believed they were being stopped for the full backup.  But I believe they were stopped to let the index maintenance run.  Is that necessary in either case?  It seems like stopping tlog back ups would be doing more harm than helping.

The sql server 2008r2 schedule was:

3:00am: Transaction log backups are stopped
3:00am: Weekly Index
Optimize Job starts (this failed after 2 hours 30 min due to log
space)
4:00am: Full backup
6:00am/Changed to 5:30am: Transaction log backups start (but the log remains full)

Additional info:

My organization has recently upgraded to sql server 2016 from 2008r2 in 2008r2 we had never experience this (both are enterprise edition).  Could it just be a coincidence or could the upgrade have changed something? Can any one offer advice on what to check for as to the cause? The Server admin has set the jobs back to the original schedule; no tran log back ups from 3am to 6am believing that the overlap of index maintenance and transaction log backups is causing the issue, could that be true? The more research I do on the subject of transaction logs, the more it seems like we should actually be doing the log backups during this maintenance window not shutting it off.  I'm a bit confused as to why they are being stopped in the first place, the original Admin is no longer with the company. I'm even more confused as to why it seems to be an issue after migration.


texts:
Trying to update the "size" column with the value of "file" column.

BEGIN
  set NEW.size=SUM(LENGTH(file));
END

Executing:

UPDATE `table`.`library` SET `_by`='Danis555, Vovan244' WHERE `id`='20';

  Operation failed: There was an error while applying the SQL script to the database.

  ERROR 1111: 1111: Invalid use of group function
  SQL Statement:
  UPDATE table.library SET _by='Danis555, Vovan244' WHERE id='20'


texts: Can I update old data and insert new data if not exists currently in a single query

texts:
I was trying to wrap my head around how to go about doing an update to a table if an updated list of the same table type does not have one or more of its original rows. The list of services comes from an API and is usually the same set of services, however every now and again the list may be updated and services become inactive and no longer show up in the results from the API call so these services should be updated in our database to reflect active as FALSE. Likewise is a new service comes onboard it should be added to the current list of available services. I had in mind to just drop all records and add the new ones fetched from the API however considering I have used the Ids for the existing services in other tables and I would still need to reference them I threw that idea out the window and now I am in a bit of a bind.

Services - (Currently in DB)
Name | Id | Active
Test1   3     true
Test2   4     true
Test3   5     true

I wanted to have a query or trigger of some sort to run when trying to insert duplicate data to table Services where constrained by the following:

If an existing 'Name' is found skip the insert and move on to the next item in the array
If a new 'Name' comes up not found in the DB add it as a new row. Example [Test4  5   true]
If the newly fetched list from an API does not have one of the existing 'Name's, that is Test1,Test2 or Test3 update that existing row to set the Active column to false. So if the new list does not have Test3 the existing Test3 row would be updated to show Active as false.


texts:
My query is like this

select * from mytable where rank <= 10000 and priority = 1

I want to rewrite it like this.

Select records which are under a specific value and priority is one.
If no records found with priority = 1, then run the query without priority.

select * from mytable where rank <= 10000

I know it can be done with php but can it can done in one query instead using php ?

thanks


texts: How to use max function in such condition?

texts:
My query is

select * from guestuser union select * from olduser ORDER BY userid DESC LIMIT 1;

Is that correct query to find last userid from different table have different userid?
can i change query using max function. or optimize it?

table1 - userid -  1, 4, 5... etc

table2 - userid -  2, 3, 6...etc