texts: Why is an empty rowset not equivalent to null when assigning a query result to a variable and how best to work around?

texts:
I have a cursor. Within my loop I select a single column and assign the result to a variable. When the select returns an empty rowset, nothing is assigned to the variable. In particular, the variable retains its current value which, in my case, is the value it held on the last iteration of the loop.

The following code illustrates this.

declare @testA int;
declare @testB int;

set @testA = 1;
set @testB = 2;

select @testA =         id from (values (3)) as testtable(id) where testtable.id = 4 ; -- empty rowset
select @testB = (select id from (values (5)) as testtable(id) where testtable.id = 6); -- empty rowset

select @testA as testA, @testB as testB;

@testA retained its value (1) while @testB was updated to null.

The only difference between the two assignment attempts was that in case B a subquery was used (via the addition of the parentheses and an additional select keyword).

This says to me a subquery (which always returns a single value) represents an empty rowset as null in that return value, but attempting to select a single column from an empty rowset does not return null. Rather, it kind of says "I have no value, so there is nothing to assign".

My understanding of the definition of null is that it represents an "undefined" value.

If I am saying "select 'id' where these conditions are met" and there are no 'id' values meeting such conditions, does this not imply the 'id' value I want is undefined?


texts:
I have a MongoDB collection db.articles,it has 2 keys ,"title" and "abstract".I want to do text search on these two keys.For example, the search text is "physics",and I want all the documents whose "title" or "abstract" contains keyword "physics" is returned.But how to create the text index to meet my command is really confusing me:

Should I create two separate text indexes for both of them like this:

db.articles.ensureIndex({title:"text"})
db.articles.ensureIndex({abstract:"text"})

or

should I create a index in a single command and give the equal weight:

db.articles.ensureIndex(
                     {
                       title: "text",
                       abstract: "text",
                     },
                     {
                       weights: {
                                  title: 1,
                                  abstract:1,
                                },
                       name: "TextIndex"
                     }
                   )

I am already get used to the operation find(),whose query granularity is key,that is ,you should  indicate the key you want to query on.But for text index, it seems like a document granularity,you cannot indicate the key you want to query on , instead ,you can only indicate the document name.So , what can I do if I want to do text search on a special key?


texts:
select DISTINCT B.`Product#`,
                B.`Contact Email Address`,
CASE when A.`Product#` IS NULL then 'Yes' ELSE 'NO' END  as New customer
from `Table B` as B
LEFT JOIN `Table A` as A
ON A. `product#`= B.`product#`

What wrong in my query. I see lot of product # is repeated. Also it says "Yes" when Product # is there in Table B but none in table A..


texts: Single SQL query to retrieve data in a particular order (not ASC/DESC)

texts:
I have a MySQL table, let's say called reviews
review_id, product_id, language_id, rating, review, author
language_id ranges from 1 to 10 for 10 different languages.

Let's say I determine that my visitor is browing the site using language whose id is 4

Is there a way to write a single query that:
1. First grabs the reviews for that product in current language
2. Next, grabs the reviews for that product in other languages sorted by language

So, basically does the job of the following 2 queries run one after the other:

SELECT * FROM reviews WHERE product_id = 723 AND language_id = 4;
SELECT * FROM reviews WHERE product_id = 723 AND language_id != 4 ORDER BY language_id ASC;


texts: Pass datatable as parameter in PostgreSQL

texts:
I have one datatable in asp.net C#. And I want to pass that datatable into PostgreSQL function as a table parameter. How is it possible?

Below example is same but it is in SQL Server. I need same thing but issue is that I have PostgreSQL as back-end, not SQL Server.

  Introduction

  SQL Server Stored Procedures support System.Data.DataTable as a
  parameter. We can pass the DataTable to the Stored Procedure using
  ADO.Net in the same way as we provided using the
  System.Data.SqlParameter class, but needs a few changes in the
  datatype.   Normally we provide DbType of SqlParameter for a normal
  parameter like varchar, nvarchar, int and so on as in the following
  code.

SqlParameter sqlParam= new SqlParameter();
sqlParam.ParameterName = "@StudentName";
sqlParam.DbType = DbType.String;
sqlParam.Value = StudentName;

  But in the case of a Table parameter, we do not need to provide a
  DbType as the parameter data type. We need to provide SqlType rather
  then DbType.

  Example

SqlParameter Parameter = new SqlParameter;
Parameter.ParameterName = "@PhoneBook";
Parameter.SqlDbType = SqlDbType.Structured;
Parameter.Value = PhoneTable;

  The following example receives a list of phone books and stores them
  in a database using ADO.Net. The example retrieves the phone book
  details from the list and stores them into the DataTable and passes
  this table to the Stored Procedure named NewPhoneBook as a parameter.

//Phone book list
List<PhoneBook> PhoneBooks
 //CReating Table
 DataTable PhoneTable = new DataTable();

 // Adding Columns
 DataColumn COLUMN=new DataColumn();
 COLUMN.ColumnName="ID";
 COLUMN.DataType= typeof(int);
 PhoneTable.Columns.Add(COLUMN);

 COLUMN = new DataColumn();
 COLUMN.ColumnName = "ContactNumber";
 COLUMN.DataType = typeof(string);
 PhoneTable.Columns.Add(COLUMN);

 COLUMN = new DataColumn();
 COLUMN.ColumnName = "ContactName";
 COLUMN.DataType = typeof(string);
 PhoneTable.Columns.Add(COLUMN);

 // INSERTING DATA
 foreach (UserPhoneBook UPB in PhoneBooks)
 {
    DataRow DR = PhoneTable.NewRow();
    DR[0] = UPB.UserName;
    DR[1] = UPB.ContactNumber;
    DR[2] = UPB.ContactName;
    PhoneTable.Rows.Add(DR);
 }
 //Parameter declaration
 SqlParameter[] Parameter = new SqlParameter[2];
 Parameter[0].ParameterName = "@PhoneBook";
 Parameter[0].SqlDbType = SqlDbType.Structured;
 Parameter[0].Value = PhoneTable;

 Parameter[1].ParameterName = "@Return_Value";
 Parameter[1].Direction = ParameterDirection.ReturnValue;
 //Executing Procedure
 SqlHelper.ExecuteNonQuery(this.ConnectionString, CommandType.StoredProcedure, "[NewPhoneBook]", Parameter);

You can find code reference here


texts: What is the best way to store a lot of user-encrypted data?

texts:
I'm about to develop an application that will mainly store user-encrypted data. Each piece of data being encrypted thanks to an AES key, itself encrypted with the public RSA key of each granted user.

Given that the volume of non-encrypted data is negligible (probably only users login, some dates and foreign keys), I wonder if "standard DBMS" such as PostgreSQL or MySQL would be a good choice.

Indeed, these DBMS are optimized to handle different types of data (text, integers, dates, ...), indexes, process calculus, aggregations and so on.

In my case, the vast majority of the data I will store would be large blocs of text (or maybe binary data). And most of the required calculus will be processed on the client side, after decryption.

Therefore, I won't either be able to parse my data. So even if I had a huge database, it wouldn't be qualified as "Big data". However, I wonder if MongoDb, MemSQL or whatever wouldn't be a more accurate choice. And if not, what would be the best DBMS and the best way to use it for my purpose ?

In other words, I think each DBMS have to make sacrifices in order to be more efficient on most important features. I also think the case detailed above is not that casual. So I assume there is a lot of features I don't need (or I can't use, such as FULLTEXT search for instance). On the other hand, I may need features that are discarded by "standard DBMS".


texts: MSSQL: Export Wizard, not possible to save SSIS Package

texts:
short question, is there a problem while trying to save a SSIS Package from the export wizard when the management studio is version 2016 and the SQL server is version 2014?

Errormessage is:

  In SQL Server Express, Web, or Workgroup, you can run the package that the Import and Export Wizard creates, but cannot save it. To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation.

SSMS Version: 13.0.15600.2
SQL Server Version: 12.0.4416.0

License Information via:

  SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Output:

  12.0.4416.0, SP1, Enterprise Edition: Core-based Licensing (64-bit)

If I try the same on the server, with ssms 12.0.4416.0, there isn't any problem.

Thank you in advance.

edit: found the solution:
https://msdn.microsoft.com/en-us/library/mt238486.aspx

  SSMS can only connect to SQL Server 2016 Integrated Services (SSIS 2016) instances.
  There is a known compatibility limitation with SQL Server Integration Services that prevents connecting to previous versions.
  As a workaround for this problem, you can connect to your SQL Server Integration Service instance by using the SSMS release aligned with your SSIS instance.


texts: Time taken for 3TB mysql database with Xtrabackup Command

texts:
I have 3TB mysql database 5.7 with InnoDB tables.
I want to migrate it to another server.
I have 64GB RAM and 6 CPU.
How much time will take to migrate and restore with Xtrabackup tools.
How much parallel and extra memory I should use to migrate it within same day?


texts: Help on improving the query performance of MySQL table

texts:
My team handles a tool that automatically detects and categorizes images.We have a Mysql DB (InnoDB engine) used by our tool in production where we store information about each image processed.
The table is poorly designed by someone long before I joined the team. It was all working well as there was very less data in Db till now. Recently we launched the tool to a wider audience and the Db is now having huge data. The 'select' query speed is very slow and takes days to get result.
I am not an expert in Databases. Please help on suggestions to improve the performance. I am thinking of options like creating indexes. We cannot have downtime for the system.Th Db is write heavy (around 200 insertion per second).  I am afraid of creating table locks if i try out index creation.
Table schema:
no primary Key, no indexes
CREATE TABLE `ImageDetails` (
  `BOOKNAME` varchar(11) DEFAULT NULL,
  `PREV_ID` varchar(11) DEFAULT NULL,
  `TIMESTAMP` varchar(100) DEFAULT NULL,
  `IMAGE_FILENAME` varchar(1000) DEFAULT NULL,
  `POSITION_ID` varchar(1000) DEFAULT NULL,
  `PRECEDING_TEXT` varchar(3000) DEFAULT NULL,
  `WIDTH` float DEFAULT NULL,
  `HEIGHT` float DEFAULT NULL,
  `IMAGE_ORIENTATION` varchar(100) DEFAULT NULL,
  `COMPUTED_WIDTH` float DEFAULT NULL,
  `COMPUTED_HEIGHT` float DEFAULT NULL,
  `SRC_STYLE_WIDTH` varchar(100) DEFAULT NULL,
  `SRC_STYLE_HEIGHT` varchar(100) DEFAULT NULL,
  `LAYOUT_TYPE` varchar(30) DEFAULT NULL,
  `COVER` varchar(10) DEFAULT NULL,
  `TYPE` varchar(250) DEFAULT NULL,
  `TYPE_SCORE` float DEFAULT NULL,
  `CATEGORY` varchar(500) DEFAULT NULL,
  `CATEGORY_SCORE` float DEFAULT NULL,
  `HISTORIC_SCORE` float DEFAULT NULL,
  `QUALITY_SCORE` float DEFAULT NULL,
  `NEW_WIDTH_IN_PERCENT` float DEFAULT NULL,
  `NEW_MAX_WIDTH_IN_POINTS` float DEFAULT NULL,
  `NEW_HEIGHT_IN_PERCENT` float DEFAULT NULL,
  `NEW_MAX_HEIGHT_IN_POINTS` float DEFAULT NULL,
  `ORIGINAL_WIDTH` float DEFAULT NULL,
  `ORIGINAL_WIDTH_UNIT` varchar(100) DEFAULT NULL,
  `ORIGINAL_HEIGHT` float DEFAULT NULL,
  `ORIGINAL_HEIGHT_UNIT` varchar(100) DEFAULT NULL,
  `CONTAINER_HIERARCHY_TYPE` varchar(1000) DEFAULT NULL,
  `ERROR_CODE1` varchar(1000) DEFAULT NULL,
  `ERROR_CODE2` varchar(1000) DEFAULT NULL,
  `STATUS` varchar(1000) DEFAULT NULL,
  `CLIENT` varchar(100) DEFAULT NULL,
  `SAMPLE` tinyint(1) DEFAULT NULL,
  `T_ID` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

EDIT:
Mysql Engine version- 8.0.17
200 insert statements per second
The select query are of the form:
-- 1.
select count(BOOKNAME)
from table
where timestamp between time1 and time2.

-- 2.
select count(BOOKNAME)
from table
where (timestamp between time1 and time2)
    and status="success";

-- 3.
select count(BOOKNAME)
from table
where (timestamp between time1 and time2)
    and (type like "%landscape%" )
    and status="success";


texts:
I have to store .pdf files in a table.

I have a table, state, with columns:

id_state,
name,
pdffile (bytea)

I want to store the pdf files in the pdffile column.

How can I do this?


texts: Running the DBT2 test results in 0.00 NOTPM

texts:
I'm trying to run the MySQL DBT2 Benchmark Tool, I have unzipped the archive and run the following commands:

./configure --with-mysql
make
make install
mkdir /tmp/dbt
datagen -w 3 -d /tmp/dbt --mysql
scripts/mysql/mysql_load_db.sh --path /tmp/dbt --mysql-path /usr/bin/mysql
scripts/run_mysql.sh --connections 20 --time 300 --warehouses 3

But got the following output:

...

Stage 3. Starting of the test. Duration of the test 300 sec
STARTING DRIVER COMMAND:   /root/dbt2-0.37.50.3/src/driver -d 127.0.0.1 -l 300 -wmin 1 -wmax 3 -spread 1 -w 3 -sleep 300 -tpw 10 -outdir /root/output/14/driver -p 30000 -warmup 0 -cooldown 20 -intermediate_timer_resolution 0
Shutdown clients. Send TERM signal to 5154.
Shutdown driver. Send TERM signal to 5190.

Stage 4. Processing of results...
                         Response Time (s)
 Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
    Delivery   0.00          N/A                      0                0  100.00
   New Order   0.00          N/A                      0                0  100.00
Order Status   0.00          N/A                      0                0  100.00
     Payment   0.00          N/A                      0                0  100.00
 Stock Level   0.00          N/A                      0                0  100.00

0.00 new-order transactions per minute (NOTPM)
0.0 minute duration
0 total unknown errors
0.00 rollback transactions
0 second(s) ramping up

scripts/run_mysql.sh: line 632:  5154 Killed                  nohup $CLIENT_COMMAND > $OUTPUT_DIR/client.out 2>&1
scripts/run_mysql.sh: line 632:  5190 Killed                  nohup $DRIVER_COMMAND > $OUTPUT_DIR/driver.out 2>&1
Test completed.

During the execution, SHOW PROCESSLIST did show that 20 clients were connected, all sleeping.

Any idea why no transactions are executed?


texts:
I saw in the documentation the difference between count(*) and count(pk).  I had been using count(pk) (where pk is a SERIAL PRIMARY KEY) not knowing about the existence of count(*).

My question is about Postgres' internal optimizations. Is it smart enough to pick up that a SERIAL PRIMARY KEY is going to exist in every row and never be false and just count rows or will it do redundant predicate checks for each row?  I agree that this is probably too much of a pointless optimization but I'm just curious.

I took a look at the output of EXPLAIN and EXPLAIN VERBOSE for count(*), count(id) and count(id > 50) to see if EXPLAIN mentioned checking the predicates in its output. It doesn't.


texts:
I came across the following sentence in this blog post:

  Know your data - this helps you make the right decisions in terms of
  data types/nullability and churn helps with long term maintenance
  goals (and initial maintenance plans)

I can't figure out what is meant by "churn." What is this? I only find plenty of articles talking about "churn" without saying what it is.


texts: Any way to verify user password from PL/PGSQL?

texts:
I need to verify user password, but this user may be "local" (with md5 method in pg_hba.conf) or LDAP user. This works fine and "local" and LDAP users can login, but I need to verify their password in PL/PGSQL in some function. Is it possible? To call some Postgres system function like verify_login(userName TEXT, userPassword TEXT) which returns BOOLEAN and it knows how to check the password of the user when it is local/LDAP/etc. user.
PS. Little bit strange idea, but it's the requirement of my task.


texts: What is the difference between select count(*) and select count(any_non_null_column)?

texts:
I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table.

What are the differences between these two statements, if any?


texts:
Looking at

update MyTable
set Status = 1
where Status = 0

of course- the where is calculated before the actual update.

but does this filtering (where Status = 0) is also inside the lock ?

I mean where is the lock ?

here : update ...

or here :  filter and update...


texts: IntelliSense not working but it is enabled

texts:
This problem is really proving to be a tricky one (and quite annoying).

In SQL Server Management Studio 2008, up until a few days ago my IntelliSense was working great.  Then all of a sudden it stopped.  The icon as it as enabled on the toolbar menu, and under Tools -> Options -> Text Editor -> T-SQL -> IntelliSense it says it is enabled there.

I have tried refeshing the IntelliSense cache with Ctrl-Shft-R but that doesn't work either.

Any ideas what happened to my IntelliSense and what I need to do to get it back?


texts:
My database is larger than 250GB. I make scheduled backups with a third party Tool.

Are schedule database backups the best way to protect SQL Server database from corruption?   Or can you recommend something else?


texts: HADR solutions for SQL Server 2016 Standard Edition

texts:
I'm looking for some HADR solutions for our organization. I know we should have Enterprise edition to have the full fledged features but what we have is Standard Edition of SQL Server. I'm new to this organisation and in my previous endeavours I have set up HADR solutions only in Enterprise edition.

While I researched I found we can have Basic AG with Standard edition but I cant have more than one DB in the AG which makes the HA plan disastrous for our applications. Transaction Replication is not allowed in standard edition.

Is there any simple HA Plan any of you have configured with standard edition? I'm looking for the best options I'm left with before proposing Enterprise edition. Please advise.


texts:
I have this scenario:

DB Log with initial size of 1193 and auto growth by 100 MB unlimited
Everyday at 8am, I have 20 auto-growth events firing per second which
is my problem

Is the correct solution here is to adjust the size of log file so that auto growth will not fire frequently?  After adjusting the db log file, should I also change the auto-growth size to like 500mb to at least limit the number of auto-growth event when the log file reach the max file size?


texts: Wordcount in a field (all and unique) - is there a more elegant/optimal way?

texts:
Answering this question,
Given this table (constructed from the question):
CREATE TABLE wordcount (id SERIAL NOT NULL PRIMARY KEY, description TEXT NOT NULL);

INSERT INTO wordcount (description) VALUES ('What a great day');
INSERT INTO wordcount (description) VALUES ('This is a product. It is useful');

produce this result:
     id  | word_count | unique_word_count |  Description
---------+------------+-------------------+---------------
     1   |  4         | 4                 | What a great day
     2   |  7         | 6                 | This is a product. It is useful

I gave the (correct) answer, which you can find here.
However in a comment, the OP then asked a further question - what if the string in question was  ['a', ' ', ' ', 'b'] and my solution broke down completely - for starters, the string wouldn't even INSERT into the table.
So, the question now is, how does one deal with strings like this - i.e. with apostrophes, square brackets &c. I'm going to give my own answer and also offer a bonus for a more elegant solution.
Solutions with multiple options will be highly prized as will those which show evidence of "thinking outside the box" (sorry for the cliché - but it fits here! :-) ). I'm also going to give a detailed explanation of my reasoning - that will earn kudos also! Options which mention other servers will also gain merit. Obviously, I can only award the bonus to one person, but I will upvote all decent answers.
I can only offer a bonus in two days - so I'll post my answer and will offer the bonus (+100) when I'm allowed to. Also, any solutions which deal with strings which my own can't deal with - haven't exhaustively tested yet.


texts:
I am a little bit confused about naming an index if it's required or not. In stackoverflow and here, I saw adding an index like this: (through coding)

    ADD INDEX index_name (column_name, ...)
    //whether a per-column or multiple-column index

But, when I tried to add an index using phpMyadmin interface, I got this:

As you can see there is no name while adding the index here, so my problem here is naming an index required? If so, does the image above gives a default name for an index if nothing is given by an administrator? And what it's default name?


texts: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 “No such file or directory”) when configuring cluster

texts:
I have 3 MariaDB nodes.
Want to configure cluster.
Specified in gcomm:// in my.cnf ips of all members in cluster according to this.

And added ?pc.wait_prim=no at then of gcomm://ip1,ip2,ip3

Then mysqld process starts on each VM, but when i try to it connect with mysql -uroot -ppass in order to run

SET GLOBAL wsrep_provider_options="pc.bootstrap=1";

error pops:

  Can't connect to local MySQL server through socket
  '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

error.log of each process shows:

2018-02-01 15:13:23 140529975699392 [Note] WSREP: gcomm: connecting to group 'my_wsrep_cluster', peer '10.0.0.42:,10.0.0.44:,10.0.0.43:'
2018-02-01 15:13:23 140529975699392 [Note] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') connection established to 71d7b5ed tcp://10.0.0.44:45
2018-02-01 15:13:23 140529975699392 [Warning] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') address 'tcp://10.0.0.44:4567' points to own listeddress, blacklisting
2018-02-01 15:13:23 140529975699392 [Note] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') connection established to 6ac0d002 tcp://10.0.0.42:45
2018-02-01 15:13:23 140529975699392 [Note] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers:
2018-02-01 15:13:23 140529975699392 [Note] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') connection established to 6bd1b1f9 tcp://10.0.0.43:45
2018-02-01 15:13:23 140529975699392 [Note] WSREP: gcomm: connected
2018-02-01 15:13:23 140529975699392 [Note] WSREP: Changing maximum packet size to 64500, resulting msg size: 32636
2018-02-01 15:13:23 140529975699392 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
2018-02-01 15:13:23 140529975699392 [Note] WSREP: Opened channel 'my_wsrep_cluster'
2018-02-01 15:13:23 140529975699392 [Note] WSREP: Waiting for SST to complete.
2018-02-01 15:13:23 140529635424000 [Note] WSREP: declaring 6ac0d002 at tcp://10.0.0.42:4567 stable
2018-02-01 15:13:23 140529635424000 [Note] WSREP: declaring 6bd1b1f9 at tcp://10.0.0.43:4567 stable
2018-02-01 15:13:23 140529635424000 [Warning] WSREP: no nodes coming from prim view, prim not possible
2018-02-01 15:13:23 140529635424000 [Note] WSREP: view(view_id(NON_PRIM,6ac0d002,2) memb {
        6ac0d002,0
        6bd1b1f9,0
        71d7b5ed,0
} joined {
} left {
} partitioned {
})
2018-02-01 15:13:23 140529627031296 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 2, memb_num = 3
2018-02-01 15:13:23 140529627031296 [Note] WSREP: Flow-control interval: [28, 28]
2018-02-01 15:13:23 140529627031296 [Note] WSREP: Trying to continue unpaused monitor
2018-02-01 15:13:23 140529627031296 [Note] WSREP: Received NON-PRIMARY.
2018-02-01 15:13:23 140529975253760 [Note] WSREP: New cluster view: global state: :-1, view# -1: non-Primary, number of nodes: 3, my ind protocol version -1
2018-02-01 15:13:23 140529975253760 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2018-02-01 15:13:26 140529635424000 [Note] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') connection to peer 71d7b5ed with addr tcp://10.0.0.44timed out, no messages seen in PT3S
2018-02-01 15:13:26 140529635424000 [Note] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') turning message relay requesting off

Why does it happen and what's wrong?


texts: How can I reduce the Initial Size of a LOG file?

texts:
I've inherited a SQL Server 2008r2 database; the Data file was created with an initial size of 1,000 mb, but the Transaction Log has an initial size of 28,016 mb.  No backups were being performed.

I've implemented full and transaction backups to match our recovery needs, and as a result the transaction file is always ~99.9% empty, never exceeding 30 or 40 mb used.  I'd like to reduce the initial size so I can shrink the log down to a more apprpriate size, 200 mb or so.

Does anyone know how I can redefine the Log file with an Init size of 200 mb?


texts: How to retrieve data from 3 tables joined by a record identifier?

texts:
I have 3 tables.

I need to check whether a specific identifier is present in those tables using a specific WHERE condition. I need to fetch data from all 3 tables.

I have tried LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN but I couldn't get the values of the middle table.

My query attempt is:

Select t1.col1, t2.col2, t3.col3
from t1
left outer join t2
on t1.id=t2.id
full outer join t3
on t1.id=t3.id
where t.id= '101';

I couldn't get the values which are present only in t2 table.


texts: Find out who ran a query using transaction log backups

texts:
We have a SQL Server 2012 Enterprise database running in Full Recovery Model with transaction log backups running every minute. A query was run last night which means we need to recover data from a certain point in time - so we restore transaction logs to that point in time, recover the data we need, all good.

What we want to know, is there a way that we can tell what login ran a query using the transaction log backups?


texts: Possible to get log file with only statements sent to Postgres from my app (and not from psql prompt)?

texts:
I'm using an ORM in my app and would like to see all statements sent to Postgres so that I can figure out exactly what the ORM is doing. In the Postgres config file I added log_statement = 'all' to get logging of statements to work, but it puts them in the same log file as all the other things like CET ERROR, CET DETAIL, etc. Is it possible to get a separate log file showing only the statements that Postgres receives?

Even better is it possible to exclude statements sent to Postgres from command like psql, so that I can see exactly what my app is sending to the DB?

Also I'm not sure what exactly is being sent to Postgres, because sometimes in the log file statements appear prefixed with CET STATEMENT: and other times with CET LOG:  statement:. What's the difference between these? Sometimes they appear one after another with the same SQL statement so I'm not sure if that means it was run twice or just once.


texts: SymmetricDS Setup - SQL Server 2008 to MySQL

texts:
I need to replicate just a couple of tables from a Microsoft SQL Server 2008 database to a MySQL 5.7 database (one-way only). I've been struggling to find a simple solution.

I've ultimately decided to try SymmetricDS, but the setup is confusing: Assuming I've got SQL Server running on Computer1, and MySQL on Computer2, and I just need Table1 and Table2, how am I supposed to set this up?

As I understand already, I need to add four additional tables to the Master Node (SQL Server in my case), do I add these tables directly to the database I need to copy? This may prove problematic as this database is managed by a very temperamental third-party application and we really don't want to touch it, should we replicate the specific data we need into a local copy on the SQL Server and then "sym" that entire db over to MySQL?

I got SSIS talking my MySQL instance, if it's possible to set that up with a trigger of some kind to automatically run the procedure that would probably work as an alternative.

However, to add, I'm pretty sure I understand the process of creating the .properties files, what I don't fully follow is the Configuration settings, or more specifically, how to set these up properly so the connection is only one-way (the SQL Server should be essentially read-only).


texts: I am trying to get the string between hyphen (-) which is before (+) upto next colon (:)

texts:
03/02/2020, 7:41 pm - +91 9999999999: At 7:10;
03/02/2020, 7:41 pm - + abcd : At 7:10;

Note: The string in between - and : is not of same length in each record. Please, help me with a solution applicable in such case.


texts: data_precision and data_scale Missing from Oracle's all_tab_columns

texts:
I need to know the data_precision and data_scale values for the fields in a view in Oracle 12c. I am not the owner of the view and I do not have access to the underlying tables.

When I query all_tab_columns for this view, all of the view's columns report null for data_precision and data_scale.

Yet, I can see in the view that many fields are numbers such as number(12,6) or whatever. For example:

Yet we can see that all_tab_columns describes this column as:

(If the picture's are not clear, we have a column with values like 0.0003, yet all_tab_columns is null for data_precision and data_scale.

Basically, all_tab_columns would lead me to believe that the field is a number(22), which is wrong.

How can I find out the actual data-type of the columns in this view? Why are integer types indistinguishable from decimal types in all_tab_columns?

Note: I tried to reproduce this with a trivial example, but instead of reproducing the issue, I got the expected/desired behavior.

e.g., this works fine:

create table foo
(
    bar number(19,6)
)
;

create view baz
as
select bar from foo
;

select
*
from all_tab_columns
where table_name in ('FOO', 'BAZ')
;

OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_TYPE_MOD,DATA_TYPE_OWNER,DATA_LENGTH,DATA_PRECISION,DATA_SCALE
LD_PRICING,BAZ,BAR,NUMBER,,,22,19,6
LD_PRICING,FOO,BAR,NUMBER,,,22,19,6


texts: Backup a database from Azure SQL Managed Instance and restore to on premise SQL Server

texts:
I have three databases that I am migrating to Azure SQL Managed Instance. One is a Utility database, just a small collection of generic functions and procedures. One is the OLTP database for a web application. The third is a read-only archive of the OLTP database containing historic data for analysis and reporting as well as audit tables. I need to be able to restore these databases to on premise development and test servers. So far, I have been unsuccessful.

My first attempt was to use backup to Azure Blob Storage. But no matter what version of SQL Server I use locally, I always get an error that the Copy Only backup is from a newer version and restore is not supported.

Next, I tried Export Data tier application from SSMS v.18.1. Unfortunately, it never gets past the export. I get dozens of errors that read External references are not supported when creating a package from this platform. One of the greatest strengths of Managed Instance over Azure SQL DB is the ability to do cross-database queries. I have a number of views and procedures that link tables in the two databases. Additionally, some procedures in the OLTP database call the functions in the Utility database. Since MI is not Azure SQL database, you would think this wouldn't be a problem!

Searching Google, I found this question on Stack Overflow. So I tried the Visual Studio approach proposed as an answer. Although it appears to work, meaning it creates a multi-gigabyte file on my local machine, attempting to import that file using SSMS gives me an error that reads No data is present in this file. So then I tried the second possible solution using sqlpackage.exe. But that solution doesn't work for me either as it only references exporting the schema. And if I try to export the whole database with /Action:Export I get the same error message as SSMS gives.

I also examined this Database Administrators question. No help - that is what I originally attempted. If anyone has any other thoughts I would greatly appreciate hearing them!


texts: Optimize architecture for SQL Server projects

texts:
I often find myself hacking together multiple .sql files for a project and calling them one after the other through Task Scheduler. Everything I know about SQL is self-taught, so I don't know anything about useful shortcuts and best practices. I imagine there has to be a more elegant way to do what I do.
Example
Right now I work on a project in which I map GPS data from my camera photos to the GPS data of some addresses which are stored on a different SQL Server:

Import all new Address-Strings from a different SQL Server on the network (insert into..select from)
Fix the spelling of those strings (stored procedure with 50+ SET @str = REPLACE(@str, yada, yada))
call wget http://maps.googleapis.com/maps/api/geocode/json?address= [adress string] with xp_cmdshell and store the result in a text file
read it with SELECT BulkColumn FROM OPENROWSET(BULK N'D:\resultfile.txt', SINGLE_CLOB) AS Contents) to later extract Latitude and Longitude and store them in the address table
run exiftools.exe against new photos to get their GPS data too, store results in csv, BULK INSERT this CSV file into the pictures table
map pictures to addresses
create directories and move photos with xp_cmdshell

These actions are all accompanied by many layers of if xyz is not null BEGIN END", REPLACE(xyz, CHAR(10), '') and so on.
I also have three views, which are used by xampp/mysql on a local website to

show all address-photo-mappings in a tree-structure, including links to those photos
show all photos which were taken without GPS data or couldn't be mapped to a known address
show all addresses which couldn't be resolved by Google

All in all, I have:

6 .sql files
6 .cmd files which all do sqlcmd -S server\instance -i yada.sql
a scheduled task which calls these 6 .cmd files
3 views

Every little change I want to introduce makes me

re-check nearly all of these .sql files for compatibility
disable scheduled task while testing changes, reenable afterwards
alter view as.. because I can't just sync views via github

It is very tedious. Do you have any tips or something I should Google? I don't mind reading a bit on my own. Thanks!


texts: How to backup/restore SQL Server database from command line similar to mysql

texts: I need to automate the migration for some databases from an older server. I know that script a tool using C# for example can be a possibility but I need a simple, fast and effective solution in order to build a batch file to do the job.


texts: Can I design a table that will use some columns just in certain times?

texts:
I have a User with one or more saved Documents, but each Document has specific attributes in Brasil for example we have:

RG(General Register) have the following attributes:

A number
Expedition Date
Emission Org

CPF(Like a Social Security Number) have the following attributes:

A number

CNPJ(Like a National Registry of Legal Entities) have the following attributes:

A Name (of a company)
A Number

CNH(Driver's License) have the following attributes:

A Name (Of a person)
A Number
Expedition Date
Valid Date

Then I designed the following model:

FK Document Type will control what attributes of table will be null, for example ifFK Document Type appoint to CPF  the Valid Date attribute will not be used.

That's the correclty way to design a system with more than one type of document? I think that is not good make one table to each document type.


texts: How do you copy a table from MySqlServer_A to MySqlServer_B?

texts:
I have a daily task that collects data from 3 MySql Servers and then pushes that aggregate data back out to each of the servers deleting the previous days data. All servers are MySql. I would like to put this into a stored proc.

I have been searching for a way to connect from one MySql Server to another, run a query, next server - rinse and repeat. Besides not finding any examples of a stored procedure that connects to another MySql server, my immediate impression is that this is not really acceptable, nor intended.

I can select into an outfile which creates a CSV. Is there any way to force an Xml document instead? I don't want to have to read into a datatable just to get Xml. Of course, Xml won't work with the Bulkload method, will it?

What are my realistic options? One concern about selecting into an outfile is the use of weird characters: ' ,  \ \\ & @ `` / // etc...how will those characters affect the Bulkload process?


texts: I need to know if a JOIN query is possible

texts:
I have one table like this:

And one like this:

What I want to do is select that "1" from status in the second table if the product_id number matches the number in the query column in the first table and is prefixed by product_id=. category_id and others should be ignored. Is something like this even possible?


texts: Group records by similarity in Postgresql

texts:
I have a large table with Tags records. It has a varchar field name. I need to review all those tags and remove near duplicated rows: singular/plural forms, misspellings, a/the, separated or joined words, abbrs, word forms, letter cases etc.
Ball, balls, the ball, a ball
MP3, mp-3, mp 3, pm3
Youtube, Youtube.com, You Tube, youtube

I guess it can be done in an automated way. How can I build some kind of visualisation of such groups? I need to generate a human readable report, where an editor will make decisions which records should be removed or merged into other ones.


texts: SQL Server: How to create a table that fills one 8 KB page?

texts: Is there a way to calculate the amount of data that fills one 8 KB page in SQL Server and create a table that fills one page entirely? This kind of table is presented in in this blog post.


texts:
I have a table called staff_leave, and I want to split date range to days per row.
is it possible. I am a newbie please take it easy .

like: there is a date range 01/01/2013 - 05/01/2013

and i want to split it to 5 days and store each day in each single row of another table.

CREATE TABLE IF NOT EXISTS `staff_leave` (
  `id_staff_leave` int(11) NOT NULL AUTO_INCREMENT,
  `staff_id_staff` int(11) NOT NULL,
  `leave_type_id_leave_type` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `joining_date` date NOT NULL,
  `is_half_day` date DEFAULT NULL,
  `approved` int(11) DEFAULT NULL,
  `approved_date` date DEFAULT NULL,
  PRIMARY KEY (`id_staff_leave`),
  UNIQUE KEY `id_staff_leave_UNIQUE` (`id_staff_leave`),
  KEY `fk_staff_leave_staff1` (`staff_id_staff`),
  KEY `fk_staff_leave_leave_type1` (`leave_type_id_leave_type`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


texts: After changing engine from myisam to innodb tables are larger in size, is this an expected behaviour?

texts:
I updated a WordPress database from myisam to innodb on a server using apache and mysql, and I noticed that some tables, such as wp-posts grew in size after running the ALTER TABLE wp_posts ENGINE=InnoDB; command, about 20MB. So, this is the first time I've done this, and I'm wondering if this is an expected behaviour?
Thanks,


texts:
I gave been trying to solve this for few days now, and cant seem to figure this out.

I have been trying to set log rotation for mysql slow using linux logrotate feature, and my script seems to get stuck on mysqladmin flush-logs.
So I logged into mysql and just entered FLUSH LOGS;. Not surprisingly I get an error:

mysql> flush logs;
ERROR 1105 (HY000): Unknown error

Now I have been going around asking for ideas, but none seem to work, its not permissions, not owners, not SELinux

I know that there used to be some bug, like in 2005 or 2008 ... but its 2012, I assume its fixed.

So if anyone has any ideas, would be much appreciated.


texts: PostgreSQL cannot work when opens SSL on Windows 10

texts:
I am not sure this topic will fit here but I don't know ask it where.
I am trying to open the SSL of PostgreSQL 10.16 on Windows10.
I read a lot of documents about creating an SSL certificate to PostgreSQL but documents spend on Windows 10 are rare and don't detail.
These are the steps which I did:
Step 1, I download the OpenSSL version of Windows from
https://slproweb.com/products/Win32OpenSSL.html
and install it with the path C:\OpenSSL-Win64, setting system variable.
Step 2, I use cmd run as an admin to create server key with command line:
genrsa -out server.key 4096

, setting appropriate permission and owner on the private key file (here https://stackoverflow.com/a/51463654)
icacls server.key /reset

icacls server.key /inheritance:r /grant:r "CREATOR OWNER:F"

and I got a response from cmd.exe in this command-line
C:\WINDOWS\system32>icacls server.key /reset
processed file:  server.key
Successfully processed 1 file; Failed processing 0 files

C:\WINDOWS\system32>icacls server.key /inheritance:r /grant:r "CREATOR OWNER:F"
processed file:  server.key
Successfully processed 1 files; Failed processing 0 files

continue to create the server certificate:
req -new -x509 -days 1826 -key server.key -out server.crt

Step 3, since I am self-signing, I use the server certificate as the trusted root certificate, so I have 3 files: server.key, server.crt, and root.crt (this is a copy of server crt)
I move these three files to C:\Program Files\PostgreSQL\10\data
Step 4, I am setting postgresql.conf:
listen_addresses = '*'
port = 5432
ssl=on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'

and add command-line to the end of pg_hba.conf:
# IPv4 remote connections for authenticated users
hostssl   all     postgres             0.0.0.0/0            md5 clientcert=1

Finally, I get an error as below when I restart my PostgreSQL
The PostgreSQL -x64-10 -PostgreSQL Server 10 service on the Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.

and in my log of PostgreSQL:
2021-03-28 16:35:44.735 +07 [7624] LOG:  database system was shut down at 2021-03-28 16:34:51 +07

2021-03-28 16:35:45.099 +07 [7044] LOG:  database system is ready to accept connections

2021-03-28 17:39:37.827 +07 [7044] LOG:  received fast shutdown request

2021-03-28 17:39:37.834 +07 [7044] LOG:  aborting any active transactions

2021-03-28 17:39:37.839 +07 [7044] LOG:  worker process: logical replication launcher (PID 7972) exited with exit code 1

2021-03-28 17:39:37.843 +07 [7880] LOG:  shutting down

2021-03-28 17:39:37.877 +07 [7044] LOG:  database system is shut down

I suspect PostgreSQL did not read the 3 files that I put in its data directory.
I have referenced these documents
https://www.howtoforge.com/postgresql-ssl-certificates
https://stackoverflow.com/questions/43959324/chmod-og-rwx-server-key-in-windows/51463654#51463654
https://www.postgresql.org/docs/10/ssl-tcp.html
I have been messing with it for many days and I don't know how to solve this problem.


texts: How is the MongoDB backup snapshot transferred from Ops Manager to file system store?

texts:
I am using Ops Manager for backup and restore.

In docs, it is mentioned that the initial sync between deployment database and Ops manager happens via streaming 10 MB compressed files.
https://docs.opsmanager.mongodb.com/current/core/backup-overview/

however, I am looking for how the snapshot is being transferred from Ops Manager to file system store.


texts:
I have a table with two columns of interest, item_id and bucket_id.  There are a fixed number of values for bucket_id and I'm okay with listing them out if I need to.

Each item_id can appear multiple times, but each occurrence will have a separate bucket_id value.  For example, the item_id of 123 can appear twice in the table, once under bucket_id of A, once under B.

My goal is to determine how much overlap exists between each pair of bucket_id values and display it as an N-by-N matrix.

For example, consider the following small example table:

item_id     bucket_id
=========   ===========
111         A
111         B
111         C

222         B
222         D

333         A
333         C

444         C

So for this dataset, buckets A and B have one item_id in common, buckets C and D have no items in common, etc.

I would like to get the above table formatted into something like the following:

        A       B       C       D
===================================
A       2       1       2       0
B       1       2       1       1
C       2       1       3       0
D       0       1       0       1

In the above table, the intersect of a row and column tells you how many records exist in both bucket_id values.  For example, where the A row intersects the C column we have a 2, because there are 2 records that exist in both bucket_id A and C.  Because the intersection of X and Y is the same as the intersection of Y and X, the above table is mirrored across the diagonal.

I imagine the query involves a PIVOT, but I can't for the life of me figure out how to get it working.

Note: the actual data I'm working with has a few hundred million entries but only a dozen or so buckets.  I'd rather not join the table to itself if at all avoidable.


texts: Web tier not starting in Oracle Webcenter Portal


texts: How to deal with a chain of several linked 1:n:n*m:n*m*k "join" explosion?

texts:
I have a domain model, where I have a "manager" which has 1:n relation with another resource (A). A has 1:n with B, and B has 1:n with C (it makes sense as a relation).

However I'll need a lot of queries fetching B/C rows based on a manager id which requires a lot of joins. Is that ok, or should I add the manager explicitly to B and C?


texts:
SSIS: Is it possible to process excel file with dynamic column headers.

For instance:
Excel file has 5 columns and we process that and save data in a table for all the 5 columns.
Now, the scenario is: file starts to have additional columns let's say 7 or sometimes the file has 3 columns.
So, could anyone please help if it is possible in SSIS to work with dynamic columns in excel. If yes, how?


texts: Can't DROP GROUP, cannot be dropped because some objects depend on it

texts:
RDS Version: PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1161

I've been wrestling with this issue for a few days now, and all solutions I've found here and the AWS Forums aren't working for me.

Basically I'm trying to drop a group. This group has no users in it (though per the AWS docs that shouldn't matter).

I've revoked all privileges on database, schema, tables, and functions. They are not an owner nor part of an ACL in pg_tables, pg_views, pg_namespace, or pg_database.

They are also not a part of the ACL in pg_default_acl.

When I run DROP GROUP my_group; I receive the following error:

[Amazon](500310) Invalid operation: group "my_group" cannot be dropped because some objects depend on it Details: privileges for default privileges on new relations belonging to user dwowner in schema changehistory privileges for default privileges on new relations belonging to user dwowner in schema stage privileges for default privileges on new relations belonging to user dwowner in schema public 5 object in database prod;

Running:

ALTER DEFAULT PRIVILEGES
    IN SCHEMA stage, public, changehistory, admin
    REVOKE ALL ON TABLEs FROM GROUP my_group;

Doesn't result in changes to the pg_default_acl table (because they weren't there in the first place) and the above error continues when trying to drop the group.

A few questions:

dwowner is in fact the owner of those schemas, but what does that have to do with my_group?
the end of the error references the prod database. I ran this query in dev. I've run the same revoke statements on prod as well. Running the DROP GROUP my_group; on prod results in a similar error, this time referencing the dev database.

There's obviously some object permission relationship that I'm missing somewhere but I have absolutely no clue at this point


texts: What's the formula for calculating Key Efficiency, Key Buffer Used, and Query Cache Hitrate in MySql?

texts:
Now, I have retrieved some dates from table of 'GLOBAL_STATUS' and 'GLOBAL_VARIABLES' by MySQL DB named information_schema .
I can retrieve

key_buffer_size

key_cache_block_size

query_cache_limit

Query_cache_size... and so on...
But, I don't know how to calculate the rate of Key hitrate, Key buffer used,and   query cache hitrate

so, someone can get the formula about those, such as
? / ? * 100% = ?


texts: SQL SERVER 2014 - Is there a way to know last time of trigger execution ?


texts:
My scenario is I look after an old system with a VB6 based front end and a SQL back end (good news, the replacement is being developed but in the meantime I need to keep this up). Now the application is used at a dozen or so sites most of which are on SQL2000, one is on SQL2008 and one on SQL2008R2 (all Standard). Changing versions/editions would be a big ask.

The one hosted on SQL2008R2, as it happens, is also the largest site. We now have a table with over two billion rows and 700GB of data. It's a small number of columns, one being a 256 char varchar used to store serialised blob data. This is now of a size that when people try to insert, delete and update to it (via the app) users occasionally lock each other out. This can only get worse as time goes on. Also however, the next largest is on SQL2000..

I need to get the size of this table down. What are some methods or techniques I could use to achieve this? Ideally the technique would be compliant with SQL2000 Standard edition so I could use it at all sites. However just fixing the biggest offender on SQL2008R2 Standard would be something.

The table does have an identity column and as the table definition is controlled by the app this can not be changed (I mention this as I believe this rules out a partitioned view- the rows are INSERTed, UPDATEd and DELETEd by the app).

My current thought is to delete all data past date X months in the past (50% of total) and hive it off to a table not exposed to the user or the app. Then in the app I can put a method for a user to request items of data to be copied back across and this would be done via a scheduled job (overnight possibly). This feels clunky to me however.


texts: MS Access fail to recognize correct field width

texts:
I am trying to import a very large text file (200 fields +) into MS Access.

The text file is not comma separated. I am not sure if it can be called fixed length, since each field has it own length.

MS Access fail to recognize correct filed width. For example, correct field 1 start from 1 to 6 but MS Access believe it starts from 1 to 46. Am I going to manually correct this problem?

I plan to import to MS Access first and then import to SQL server. Since it does not correctly recognize right width in SQL server wizard

Update:

Sample data. The break line is added by me manually.


texts: In stored procedure %Rowtype is not working while deploying

texts:
CREATE OR REPLACE PROCEDURE CAS.ROWTYPECHECK ( P_APPLICATION_REC  TAPPLICATION%ROWTYPE )
IS
BEGIN
  INSERT INTO "CAS"."VAPPLICATION"
    VALUES P_APPLICATION_REC;
END;

But I'm getting one error while deploying.

Deploy CAS.ROWTYPECHECK

  Running
  CAS.ROWTYPECHECK - Deploy started.
  Create stored procedure returns SQLCODE: -104, SQLSTATE: 42601.
  CAS.ROWTYPECHECK: 1: An unexpected token "%" was found following "ON_REC
  TAPPLICATION".  Expected tokens may include:  ",".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.16.53
  An unexpected token "%" was found following "ON_REC  TAPPLICATION".  Expected tokens may include:  ",".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.16.53
  CAS.ROWTYPECHECK - Deploy failed.
  CAS.ROWTYPECHECK - Roll back completed successfully.

DBMS is DB2 V9.1.

I tried using:

CREATE OR REPLACE PROCEDURE <Sechma>.<ProcedureName>
  (<Record> <table/VIEW>%ROWTYPE)


texts: Best practice try/catch throw and XACT_ABORT sql server

texts:
I've previously worked with software that generated ETL scripts for us. The scripts have the following structure, but this appears to run contrary to what I've read in msdn. Can anyone offer any thoughts on the following structure?

SET XACT_ABORT OFF

SET NOCOUNT ON

BEGIN TRY

'DO STUFF'

BEGIN CATCH

SET @return_msg = 'procedure name FAILED'

  + '. Step ' + CONVERT(VARCHAR,ISNULL(@v_step,0))

  + '. Error Num: ' + CONVERT(VARCHAR,ISNULL(ERROR_NUMBER(),0))

  + '. Error Msg: ' + ERROR_MESSAGE()

  SELECT @return_msg

END CATCH

IF XACT_STATE() <> 0

  BEGIN

   ROLLBACK TRANSACTION

  END

  RETURN 0

END


texts: Oracle - execute DML query as different user

texts:
I would like to be able to run DML queries (INSERT,UPDATE,DELETE) by executing a procedure on a given schema. Those queries need to change data on different schemas on the same instance. So, basically, I need to run this procedure with all the privileges of a user that it needs to be ran on. I put the

AUTHID CURRENT_USER

clause in the package header as well as

EXECUTE IMMEDIATE ('ALTER SESSION SET CURRENT_SCHEMA =B')

clause in the body of procedure. This works perfectly fine for non-DML/DDL queries like SELECT, unfortunetely when I run the DML procedure there's an error about insufficient privileges. When I grant privileges to user A (the one which the package is stored on) the queries run with no problems. So clearly the procedure is ran as user A (package user) and not user B (user I want to run DML queries on).

Is there anything I can do to make this happen? I want to keep all the code on user A without manually granting any privileges on all the users.


texts: MySQL innodb_log_file_size change is not being recognized

texts:
I am trying to set the innodb_log_file_size option in a MySQL 14.14 (distrib. 5.6.30) installation on Ubuntu 15.10 to solve an issue (importing a database with large BLOB fields)
 however the log size keeps defaulting back to 48MB. I have read a number of posts about this but have not yet found a solution. Any help is appreciated.

My process:

SET GLOBAL innodb_fast_shutdown = 0; (in a MySQL session)
/etc/init.d/mysql stop
rm /var/lib/mysql/ib_logfile0 and ib_logfile1
(edit my.cnf)
/etc/init.d/mysql start

In /etc/mysql/my.cnf (which is a symlink to /etc/alternatives/my.cnf which is a symlink to /etc/mysql/my.cnf.migrated):

[mysqld]
...
innodb_buffer_pool_size = 1000M
innodb_force_recovery = 0
innodb_file_per_table = 1
innodb_file_format = barracuda
innodb_read_io_threads = 16
innodb_log_file_size   = 1G
innodb_log_files_in_group = 4

I also set this variable in /etc/mysql/mysql.conf.d/mysqld.cnf in case that was taking priority.

The log file reads:

2016-05-19 09:25:23 24141 [Note] Plugin 'FEDERATED' is disabled.
2016-05-19 09:25:23 24141 [ERROR] Function 'innodb' already exists
2016-05-19 09:25:23 24141 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2016-05-19 09:25:23 24141 [ERROR] Function 'federated' already exists
...
2016-05-19 09:25:23 24141 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-05-19 09:25:23 24141 [Note] InnoDB: The InnoDB memory heap is disabled
2016-05-19 09:25:23 24141 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-05-19 09:25:23 24141 [Note] InnoDB: Memory barrier is not used
2016-05-19 09:25:23 24141 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-05-19 09:25:23 24141 [Note] InnoDB: Using Linux native AIO
2016-05-19 09:25:23 24141 [Note] InnoDB: Using CPU crc32 instructions
2016-05-19 09:25:23 24141 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-05-19 09:25:23 24141 [Note] InnoDB: Completed initialization of buffer pool
2016-05-19 09:25:23 24141 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2016-05-19 09:25:23 24141 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2016-05-19 09:25:23 24141 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2016-05-19 09:25:23 24141 [Warning] InnoDB: New log files created, LSN=2798779859498
2016-05-19 09:25:23 24141 [Note] InnoDB: Highest supported file format is Barracuda.


texts: Derby/SQL - How to increase performance when left joining and retrieving varchar columns

texts:
Through trial and error I found that if I do a left join such as:

SELECT
    firsttable.id,
    secondtable.id,
    secondtable.varcharColumn
FROM
    firsttable
LEFT JOIN
    secondtable
ON
    firsttable.id=secondtable.id

The performance is terrible for larger tables. If I EITHER remove secondtable.varcharColumn as a column in the resultset OR change the type then the performance is about an order of magnitude better. The column is a varchar 255, so it should not make that much of a difference. If I change the type of column to integerColumn or dateColumn than I also see an order of magnitude difference in the performance. Even a varchar of a few characters seems to result in the same performance degradation.

Please note that the WHERE clause is NOT the issue here. I've omitted it because the issue is the same whether or not a WHERE clause is included. The issue has to do with the type of data in the column and not the quantity of data returned. I could also use LIMIT 1000 and have the very same issue.

Indexing the column will not help as the column is not used for joining. It is also not part of the where clause if there was one.

Any suggestions on how to improve the performance would be greatly appreciated!!


texts: Right Tool for Data Mapping in Relation to Business Intelligence

texts: When you are working as a BI developer you also need to create a data mapping between dimensional modeling and source system. What kinda application to visualize a data mapping?


texts: Update table based on reference values from another table

texts:
I have two tables structured like so:

Table1 (reference table)
| Name UNIQUE | Value(Double) |

Table2 (data table)
| Name1 | Name2 | Number(Int) |

I want to add 10 to the number in each row of Table2 where the value of Name1 (from Table1) is greater than the value of Name2 (from the Table1). How can this be done in Sqlite3?


texts: Query performance of index scans slower than parallel seq scan on Postgres

texts:
I've been trying to debug a performance problem on PostgreSQL 10 on AWS Aurora.
Specifically I'm trying to understand performance problems on "cold" queries where the buffer cache does not contain cached pages.
I have a table with an index and when the planner uses the index the query is substantially slower (spending more time on IO from what I can tell). However, when I drop an index the query is faster, it uses a parallel sequential scan.
The Schema of the table:
CREATE TABLE max_test_2018_3
(
   timestamp_         timestamp,
   person_alias       varchar(128),
   visitor_id         varchar(128),
   session_id         varchar(24),
   page_title         varchar(1024),
   location_host      varchar(256),
   location_path      varchar(1024),
   location_query     varchar(1024),
   location_fragment  varchar(1024),
   referrer_host      varchar(256),
   referrer_path      varchar(1024),
   referrer_query     varchar(1024),
   referrer_fragment  varchar(1024),
   duration           integer,
   location_secure    boolean,
   referrer_secure    boolean,
   id                 uuid,
   person_id          uuid,
   custom_properties  jsonb
);

The index DDL is:
CREATE INDEX max_test_2018_3_ix   ON max_test_2018_3
  USING btree  ( location_path varchar_pattern_ops);

And the query is:
explain (analyze, buffers)
SELECT O.person_id, 1 as step1, min(timestamp_) AS timestamp_
FROM max_test_2018_3 O
WHERE O.location_path  like '/tax-calculator%'
GROUP BY O.person_id

Check the following two explains - note that both explains show minimal or no buffer hits. One shows the execution with the index and the other shows execution after the index has been dropped:
No index -> https://explain.depesz.com/s/N4uo
With index -> https://explain.depesz.com/s/69WS
The plan that includes the index scan takes 193 seconds vs 43 seconds for a sequential scan.
Why is step 2 in the "With-index" plan so slow?  It reads in only 118372 buffers (about 118 MB of data), yet this step took 193 seconds.  Why?  When the buffer cache is warmed up the same with-index query takes a couple seconds.  So we must concede that the IO is responsible for this extra time.
I've run this experiment several times on multiple similar datasets.  Here is another example that demonstrates index scan operations that require IO and do not benefit from buffer cache.
No index -> https://explain.depesz.com/s/g6JU
with Index ->https://explain.depesz.com/s/Sw6Q
Again, you can see that the index scan is unreasonably slow.  The planner attempts to use the index because it believes it will speed up the query, yet such queries are consistently slower.
With regard to parameters that affecting the planner I've set the following two:
random_page_cost = 1
seq_page_cost = 1

The rationale being that for an SSD drive the random_page_cost does not require seeking and so can be set to the same value as seq_page_cost.
The table is vacuumed with statistics up-to date.  The table max_test_2018_3 contains approximately 18 million rows and is about 9GB on disk.  The above index on that table is 440 MB.
Why do the plans that use an index scan perform so poorly?
I/O Timing enabled Plans

with index             https://explain.depesz.com/s/84kE no
no index(parallel scan) https://explain.depesz.com/s/CRBp

Tweeking parallelism settings
The explains above have default parameter settings with respect to parallelism. The only two exceptions are:
max_parallel_workers = 8
max_parallel_workers_per_gather = 4

As per the suggestions, I've experimented with with the following settings:
parallel_setup_cost = 10
parallel_tuple_cost = 0.001

Defaults for these are 1000 and 0.1 respectively.  These changes did not affect the plans and PostgreSQL still chose to use the index even though a parallel sequential scan is faster.
The index size is 440MB, far larger than the default min_parallel_index_scan_size of 512kB


texts: Date picker is dynamic in SSDT but not in SSRS

texts:
Running SQL Server 2012 SP1 and SSDT from same distribution. (VS2010)
When I display a report in SSDT the date selector on a date parameter is dynamic or responsive. When you click on the date (month/year)  the date picker can zoom in/out. Or drill in/out (Apologies if I'm using the wrong terminology).

However looking at same report in SSRS, the date picker doesn't have the same functionality.  You can click left and right arrows to get last/next month but clicking on the date does nothing.

Does this only work in SSDT? or should it also work in SSRS?

If it should work in SSRS, any suggestion of where to start looking would be greatly appreciated. Is it report setting? an SSRS setting? a browser setting?

If there is a better description of this feature please let me know and I'll update my question.

Thanks

Peter


texts: how to add a user to local sql server when one has only windows admin rights on the server

texts: I'm in the admin group of a server, but yet I don't have access to the sql server, is there anyway to add myself without the need to reinstall the sql server?


texts: How to fetch last row order by date and group by id

texts:
I am trying to skip duplicate history_id and take only last update row

SELECT products.products_id, products.products_date, products.products_link,
       history_products.history_products_name, history_products.history_products_update,
       history_products.history_products_stock, history_products.history_products_status

FROM products JOIN history_products
                ON products.products_id=history_products.history_products_id

WHERE products.products_author_id=:products_author_id
   && products.products_store_id=:products_store_id

GROUP BY history_products.history_products_id

ORDER BY STR_TO_DATE(history_products.history_products_update, "%d-%m-%Y %h:%i:%s%p") DESC


texts:
I'm having one heck of a time trying to compare and move schemas for a new database. I've tried Red-Gate, ApeXSQL etc. and every time I turn around it's not working. I have a customer who has 5 databases in which all 5 have similar schemas. Some have additional stored procs etc. Let me take a step-back and state that Red-Gate does work, however, because I have 73 schemas to filter by I cannot enter every single schema into Red-Gates filtering. So, I thought about trying SSIS, which I've never used before. My objectives and end goals are outlined below. Note: I'm using Visual Studio 2017 and SSDT for VS 2017

If using SSIS I need a way to skip\continue the process if an object is already present.

Objectives:
* To compare\copy the schemas between Database-1 to Database-2 in which only the 73 pre-determined schemas will be created on Database-2.
* To create the tables, stored procedures, functions etc. on Database-2 but, only the ones that belong to the 73 schemas.

End Goal:
* To have a new database that contains all objects for the 73 pre-determined schemas.


texts: Delivering replicated transactions in SQL Server transactional replication

texts:
I had seen this message in no. of replication cases but never got to know what is the exact cause behind this message and what troubleshooting to be done to know what is going on. What I want to know is a definitive guide of what needs to be done in such cases.

What scripts to run to check what command is executing?
What agent parameters to change?
How to check if there are any big transactions causing this?

I searched many blogs but didn't get any concrete answer. Hoping that will get it here.


texts: Querying 'random' Oracle data via openquery in SQL Server returning the same data for multiple runs

texts:
I'm asked to help a developer figure out why the "random" data we should be getting from Oracle is exactly the same results each time you run it, rather than being actually random.
SQL 2012, Oracle 11g, linked server is using OLE DB connector type.

When they run the following query directly in the Oracle DB, they get proper "random" results. Through the linked server in SQL, they'll always get the same rows (and I've verified this by running the linked server query myself).

select * from openquery(ORA_DB, 'select XX_NUMBER, XX_CODE, UNIT, XX_serial, XX_date, XX_area, building, location, XX_status, owner_code, qa_XX_no
                               from qa_eligible_XX
                               where qa_XX_no = 4
                               and owner_code <> ''G''
                               and last_trans_code = ''OL''
                               and XX_cycle = ''90 - 120 Periodic''
                               and XX_date < sysdate - 90
                               and rownum <= 200
                               order by dbms_random.value')

Note: I'm not an Oracle DBA but I work with one; he didn't see an issue with the Oracle part of the above query. Also, I edited a lot of stuff out and replaced with XX for privacy.

Each time we run this query in SSMS, we get the same 200 rows. I even connected from another SQL server that has the same linked server and ran this query, and got the same 200 rows (the same Oracle user is used for both connections). This leads me to believe Oracle may be caching the results; but somehow it's not doing this when the query's run in Oracle directly. Is there a way to force it to return new results through openquery? (so that the 200 rows it returns are a truly random sampling each time)


texts: How can I determinate the pc specs I need for my database?

texts:
I am writing an application for a client that says he will have 20 orders / day and that will increase to 200 within 2 years.
He wants to have his own local server for that and wants me to suggest him a PC specification that may supply his needs for N years say 7~10.
The database will be mariadb.
What data do I need to look at to evaluate that?
My initial idea is to generate data to cover for 300 daily records for a year and then calculate based on that.
I will be writing an app that writes dummy data to the db using N connections to emulate concurrent insert, reads, etc, not sure if this is a good way to do this either. What are your advice?
But I am not sure how I would calculate memory, cpu and disk io into the equation only the disk space.
How can I calculate the memory cpu and disk io for this purpose to know say what sort of HD/SSD/M2 or cpu and memory that it would require?


texts: Should I use select count(*) for counting records or having a field like comments_no for tables like post, comments?

texts:
In my recent project which is about social networking for an Asian country, I'm in doubt whether I use the below SQL statement for counting records:

SELECT COUNT(id) shareLikeNo FROM user_share_like WHERE jusid=$jusid

Or update a field like comment_no in shares(posts) table?
I have fields like shares_no,comments_no,likes_no. Everytime a user click like button the likes_no field will be incremented and other fields as I explained.
This way I have to update every record each time a user post a comment or shares a post or likes a post.
Should I use SELECT COUNT(id) FROM tblname and so on for getting the record count or just updating a record.
There will about 10 to 20 posts each second. Site's traffic will rose.
Which approach should I use? Which one is more expensive?

EDIT:
I've used use index(PRIMARY) as below to speed things up:

SELECT COUNT(id) shareLikeNo FROM user_share_like USE INDEX(PRIMARY) WHERE jusid=$jusid

Another approach is:

SELECT SUM(1) shareLikeNo FROM user_share_like WHERE jusid=$jusid

Is this a better approach?


texts: Improve the insertion rate of mongo sharded cluster

texts:
I can't disable the write concern while i mongorestore with .bson file. It totally damages the insertion rate (insert/sec).

This is my sharding status.

mongos> sh.status()
--- Sharding Status ---
sharding version: {
"_id" : 1,
"version" : 4,
"minCompatibleVersion" : 4,
"currentVersion" : 5,
"clusterId" : ObjectId("546bd498f0531e27d40544b2")
} shards: { "_id" : "shard2", "host" : "shard2/hadoop5.xx.com:27018" } databases: { "_id" : "admin", "partitioned" : false, "primary" : "config" } { "_id" : "deneme6", "partitioned" : false, "primary" : "shard2" } { "_id" : "seref", "partitioned" : true, "primary" : "shard2" }

This is my shard. I already changed the property of getLastErrorDefaults w:0 for disable write concern.

shard2:PRIMARY> show tables; me oplog.rs startup_log system.indexes system.replset          shard2:PRIMARY> db.me.find() { "_id" : ObjectId("546bd2869e14c01f13708886"), "host" : "hadoop5.xx.com" }
shard2:PRIMARY> db.system.replset.find() { "_id" : "shard2", "version" : 2, "members" : [ { "_id" : 0, "host" : "hadoop5.xx.com:27018" } ], "settings" : { "getLastErrorDefaults" : { "w" : 0 } } }
shard2:PRIMARY>

This is my mongorestore command which load data via query router ( mongos )

mongorestore --db seref --collection can11 --noobjcheck --w 0 --noIndexRestore --host       10.21.12.21 --port 27017 /tmp/iabc_kwd.bson

When i try to mongorestore on sharded cluster via mongos the insertion rate is 300k  byte / sec , when i try to mongorestore on single mongodb the insertion rate is 12m  /sec.

How can i speed up the insertion rate of restoring mongodb sharded cluster?

-- Sharding Status ---
  sharding version: {
    "_id" : 1,
    "version" : 3,
    "minCompatibleVersion" : 3,
    "currentVersion" : 4,
    "clusterId" : ObjectId("546ca29960e36f8983130015")
}
  shards:
    {  "_id" : "shard0000",  "host" : "hadoop5:27018" }
    {  "_id" : "shard0001",  "host" : "hadoop6:27017" }
    {  "_id" : "shard0002",  "host" : "hadoop4:27017" }
  databases:
    {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
    {  "_id" : "test",  "partitioned" : false,  "primary" : "shard0000" }
    {  "_id" : "seref",  "partitioned" : true,  "primary" : "shard0000" }
    {  "_id" : "abc",  "partitioned" : false,  "primary" : "shard0001" }
    {  "_id" : "mahmut",  "partitioned" : true,  "primary" : "shard0002" }
    {  "_id" : "incee",  "partitioned" : true,  "primary" : "shard0002" }
        incee.onur
            shard key: { "_id" : "hashed" }
            chunks:
                shard0000   1
                shard0002   501
            too many chunks to print, use verbose if you want to force print

All shards computer have 8 GB RAM , and they have 1.3 TB HDD.


texts: Select all rows with same ID when the last record of that ID meets the criteria

texts:
I need help with a SQL query.
I want to return all rows when the last record (date) of the ID match the criteria.
Here is an example :
Let's call this table "Test"

ID
date
status

1
10/01/2021
Closed

1
09/01/2021
Resolved

1
08/01/2021
Awaiting Input

1
01/01/2021
Open

2
15/01/2021
Open

2
16/01/2021
Awaiting Input

2
17/01/2021
Open

3
07/01/2021
Open

3
08/01/2021
Awaiting Input

What i want to do is to return all the rows with the same ID if the last record (date) of that ID match the criteria.
I tried :
 SELECT * FROM test AS r
 WHERE r.ID in (SELECT
    ID FROM test
    WHERE status = 'Open');

But that will return all the records (ID) having "Open" in the database.
What I want is all the rows (ID) with status"Open" (or whatever condition) and all their records if the last record meets the conditions..
For example for status "Open" the result should be:

ID
date
status

2
15/01/2021
Open

2
16/01/2021
Awaiting Input

2
17/01/2021
Open

Indeed, the last record (ID) 17/01/2021 as status 'Open' so it match the condition. --> It could be order by date desc or asc ... It's just an example.
How can I dot it?
It seems easy on paper but i'm struggling with this one.
Note that I'm using postgresql.
Thanks for your help.


texts:
How can we drop all databases based on age.

If i go with today's date with 2 months retention the list will come with below query , now i want to drop all the databases with query output.

SELECT name as [database] , database_id, create_date
FROM sys.databases where create_date  <= '20190726' AND name NOT IN ('Master','Model','MSDB','tempdb')

Error

The below script not working with errors being:

  DROP DATABASE VM_P
  Msg 2812, Level 16, State 62, Line 15
  Could not find stored procedure 'DROP DATABASE VM_P.

Script

DECLARE @DBName VARCHAR (64)
DECLARE @SQL VARCHAR (255)
DECLARE DROPDB CURSOR FOR
SELECT name FROM sys.databases
WHERE create_date  <= '20190726'
AND name NOT IN ('master','model','msdb','tempdb','distribution','ReportServer','ReportServerTempDB')

OPEN DROPDB
FETCH next FROM DROPDB INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'DROP DATABASE ' + @DBName
            PRINT @SQL
    EXEC @SQL
    FETCH next FROM DROPDB INTO @DBName
END
CLOSE DROPDB
DEALLOCATE DROPDB


texts: Can I just restore the .diff file over full backup to speed up restore?

texts:
I'm doing a server migration, and i'm looking at ways to speed up restore.
I have a 1TB full backup file which takes about 2 hours to restore
RESTORE DATABASE [MyDb] FROM  DISK = N'C:\db\202010152000.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5

This file takes a while to create, and copy to new server, so I also need to include a DIFF which gets the latest data and copy over quickly,
I would include the diff in the restore like so:
RESTORE DATABASE [MyDb] FROM  DISK = N'C:\db\202010152000.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
RESTORE DATABASE [MyDb] FROM  DISK = N'C:\db\diff-final.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

However, this takes about the same time to restore (a little longer)
I was wondering if there was a way to restore the Full backup first, then just apply the diff and it restore just the diff bits, rather than full and diff in one go.
is this even possible? if so I could dramatically reduce downtime.
Database is in simple recovery model so i'm stuck with full and diff backups.
(Sql server 2017 Ent)

EDIT
So based on the first answer I can just do this
alter database [MYDB] set single_user with rollback immediate
go

USE [master]
RESTORE DATABASE [MYDB] FROM  DISK = N'C:\db\Full.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
GO

this takes around 2 hours, then I can do my final diff, and just run this:
USE [master]
RESTORE DATABASE [MYDB] FROM  DISK = N'C:\db\diff2final.bak' WITH  FILE = 1,  RECOVERY,  NOUNLOAD,  STATS = 5
GO

alter database [MYDB] set MULTI_USER
GO


texts: Executing the query "UPDATE STATISTICS [dbo].[Contact] WITH FULLSCAN ..." failed

texts:
My maintenance plan fails with the following error on one of my databases does anyone know how I can fix this problem? is is a corrupt table?

Executing the query

UPDATE STATISTICS [dbo].[Contact]
WITH FULLSCAN
...

Failed with the following error:

  A severe error occurred on the current command.  The results, if any,
  should be discarded.". Possible failure reasons: Problems with the
  query, "ResultSet" property not set correctly, parameters not set
  correctly, or connection not established correctly.


texts: Permission Requirement to migrate MongoDB to ApsaraDB for MongoDB

texts:
I want to migrate my existing MongoDB 3.6 to ApsaraDB for MongoDB. What type of permission required for Migration.
Is there any steps defined for MongoDB migration


texts: Problem when add administrator for Analysis Server ( SQL Server 2008 R2 )

texts:
I'm trying to add a user as an administrator in Analysis Server, doing this:

1 - Connect - Analysis Server - Right click on the Instance - Properties - Security - ADD :

This number was already there, So when I click ADD  and insert the user to be administrator, I see this error:

It says in English:

  an error occurred in the system ( microsoft.AnalysisServices)

But what error? I can't add anyone. I can't even add myself ( I'm AD user of all servers ). Can't even add, too, the account I use to start services ( It has domain admin full privileges ).

Obs: This is our test server. We need to create analysis services objects via software. I'm trying to add the software user as administrator here to be able to create a cube.

EDIT1:

Trying to trace the error, I only get this:

The same useless information.


texts: How can I tell what database/procedure is using a linked server?

texts:
I have 3 servers, one of which has a linked server configured that points to the other - I'll call this Server A. Server A has over 100 user databases for various purposes. Server B is running SQL 2005 which we're trying to eliminate. Server C has copies of some of the databases from Server B, and we're migrating applications from Server B's database copies to Server C's.

When I'm on Server B, I can see connections from Server A to a certain database but I don't know how to tell what procedure, task, or job from Server A is using that linked server connection to Server B.

In order to retire the database on Server B, I need to re-point Server A's connections to a database on Server C; but in order to do that, I need to know what procedures, tasks, or jobs on Server A are using that connection so they can be updated.

Is there a way to see the dependencies on a linked server without disabling the linked server to see what starts failing?


texts: All my database on the fail over server showing in "Synchronizing" state

texts:
Current Configuration: I have two SQL Servers, which are connected by the failover mechanism, meaning one is primary and one is secondary.
I restarted both services on both servers sequentially.
On the secondary server, all the databases are in the "Synchronizing" state.
AG replication mode is synchronous. It has been 12hr it's still showing "Synchronizing" state.
What I did so far to resolve
Drop the database from the secondary server and remove the database from AG group on the primary server and add it back.
Any insight on this, I am using SQL Server 2017.


texts: Is there a developer edition of Microsoft SQL Server 2017 for Linux?

texts:
I have SQL Server 2016 Developer Edition installed on Ubuntu, whis is free for development purposes:

  SQL Server 2016 SP1 Developer is a fully featured free edition licensed for use as a development and test database in a non-production environment.

I wanted to install SQL Server 2017 on Ubuntu as well. For Windows, on the SQL Server download page, there are three options - a trial evaluation, SQL Express (free but limited), and SQL Developer (free for non-production environments).

However, for Linux, there's just links for installing on Red Hat, Ubuntu, or SUSE.

When installed on Ubuntu, SQL Server 2017 (v.Next) says:

Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...

This is an evaluation version.  There are [174] days left in the evaluation period.

Is there a SQL Server 2017 Developers Edition for Linux? If so, where so I go to access it?

Note: This isn't a question about licensing, per se; it's about whether a 2017 version of the product I'm alreasdy using is available.


texts: Where I run mysqldump or pg_dump, Both services (mysql or postgres) freeze?

texts:
I need to create a database without stopping the services and I don't know this answer.
I need my web pages to continue working properly in that time.
Can someone answer me?


texts: How to check the default schema of WINDOWS_GROUP?

texts:
I am using the following query to check if the default schema is set to dbo.

SELECT DB_NAME() AS [database]
      ,[name]
      ,[type_desc]
      ,[default_schema_name]
FROM [sys].[database_principals]
WHERE [type] IN ('U', 'G')
    --AND [default_schema_name] <> 'dbo';

If not set to dbo and someone create an object without specifying a schema identifier, a new schema is created with his/her windows credentials.

So, I wanted to write a script to check (on certain period of time) if everything is set OK (because new machines and databases are restored/created on daily basis).

The issue with the script above is that I am able to see that my user's defaults schema is not dbo, but when executed with other account, my user is not listed.

I supposed that's because of the WINDOWS_GROUP that we are all member of, so I need to check its settings, but always get NULL value for groups.


texts:
I am attempting to export a large database and import it to another PC over the Internet.

Sometimes my Internet connection fails resulting in a database export error, causing me to have to restart the export from the beginning.

Is it possible to resume the export from point where connection failed?

I am using Microsoft SQL Server 2008 R2.


texts:
Which statement is correct about the parallel executions plan?

A. The CUSTOMERS and SALES tables are scanned simultaneously in parallel and then joined in parallel.
B. First, the CUSTOMERS table is scanned in parallel, then the SALES table is scanned in parallel, and then they are joined serially.
C. First, the SALES table is scanned in parallel, then the CUSTOMERS table us scanned in parallel, and then they are joined in parallel.
D. The CUSTOMERS and SALES tables are scanned simultaneously in parallel and thenjoined serially.
E. First, the CUSTOMERS table is scanned in parallel, then the SALES table us scanned in parallel, and then they are joined in parallel.

I think E is correct but the correct answer is A. Why?


texts: How can I add back Identity to a column?

texts:
I have a table:

CREATE TABLE [dbo].[Question] (
    [QuestionId]       INT              IDENTITY (1, 1) NOT NULL,
    [QuestionUId]      UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL
);

QuestionId was a primary key and when I dropped the primary key then the identity seemed to drop also. Is there a way that I can get back (add) the identity as a default for this column and have it so the new value starts at a value greater than the existing values ?


texts: Is there a way Azure data studio to find and add local databases under a list automatically?

texts:
When I create a database locally in SSMS it does not show up automatically in ADS. I have to add it as a connection each time.

Generally I use SSMS but am trying to switch away (or just try for fun) from it to ADS.


texts: If I use varaibles in the order by clause, is there any guarantee the result will be sorted?


texts: I was using Xampp out of the box and was doing some development work. Someone deleted my Xampp directory but full mysql data folder with ibdata1, iblog, db folders is present. Db folders only have ibd file and no frm file. I installed a new version and pasted whole data folder and upon mysql startup I can see the database but there are no tables in it . I also confirmed that InnoDb is actually referring to ibd files of these tables by changing the db folder name and checking the logs. How can I see the tables? i have searched everywhere and every method suggests using frm files which I don't have.


texts: How to Simulate deadlock with more than 2 processes in SQL Server test environment? Can someone help me with the code? Just to clarify my question again, I don't want deadlock simulation with only 2 processes. I know how to produce deadlock with 2 processes but am unable to produce it with more than two processes.


texts: Postgres Can't Add Foreign Key Constraint

texts:
I have a table with about 220 million records :( and I need to add a foreign key constraint.

My command looks something like this:

ALTER TABLE events
ADD CONSTRAINT events_visitor_id_fkey
FOREIGN KEY (visitor_id)
REFERENCES visitors(id)
ON DELETE CASCADE;

It's been running for probably an hour now.

I ran this before hand:

set maintenance_work_mem='1GB';

What's the fastest way to do this, and about how long should it take. The table it references is only 25 million.

I'm running it on an RDS instance of db.r3.large (15 GB of RAM).

EDIT:

Just cancelled the command and got this:

ERROR:  canceling statement due to user request
CONTEXT:  SQL statement "SELECT fk."visitor_id" FROM ONLY "public"."events" fk LEFT OUTER JOIN ONLY "public"."visitors" pk ON ( pk."id" OPERATOR(pg_catalog.=) fk."visitor_id") WHERE pk."id" IS NULL AND (fk."visitor_id" IS NOT NULL)"


texts: What are the disadvantage/consequences of moving updated column to separate table to prevent table lock on the original table?

texts: I have a table p with a column points. There are a couple of million of records in the table and i need to regularly calculate and update the points for each row. This process takes a couple of minutes and it locks the table. I need to be able to insert and update records in p in the meanwhile. One solution I was thinking of is to create a second table and add a foreign key to p and the points column there. If this table is locked for a couple of minutes that's fine. Are here disadvantages/consequences that I should keep in mind with this solution?


texts:
I'm currently creating an index on a mongo database and so far it has taken about 52 hours. I've worked with large(r) Mongo databases before but creating an index has never taken this long. My collection has 1 billion rows, and I'm creating the index on a single string field which always has a length of 12.

Should I just leave it running, or is it potentially broken and should just be stopped?

Thanks in advance!


texts: Non-Unique Clustered Indexes Administration

texts:
I have been recently assigned to administer a database in SQL Server 2016 and I have discovered that there are many tables in the database with Non-Unique Clustered indexes resulting in Non-Clustered Primary Key indexes.

I know that the above is apparently allowed but is not ideal (specially for large tables) and maybe for some few cases, that would make sense, but the number of such tables tells me that this was more like a result of careless table definitions.

For example, a table named Transaction will have a Clustered Index with the following keys: date, is_deleted. Also, the table will have a Non-Clustered Primary key on a column named id.

Now, some of these tables are pretty large and have many foreign key references on them, so I came up with the below steps to change all these tables with the help of Aaron's answer in this thread Unable to drop non-PK index because it is referenced in a foreign key constraint:

Drop the Clustered Index of the table
Drop all foreign key constrains that reference the table
Drop current Primary Key constraint of the table
Create Primary Key Clustered constraint using the id column
Create all foreign key constraints that were previously deleted
Optionally - Create the previously Clustered index as a Non-Clustered index

I know that for the large tables I need to find a maintenance window to perform the change but do you find any problems or gotchas with the above solution what I didn't think of? Will this method cause fragmentation on the rest of Non-Clustered Indexes?

There are a lot of tables involved and I want to make sure that I'm not triggering any side effects.


texts: MSSQL - what is the best isolation level for a website

texts:
My website is mostly informative,
Users cannot Update or Delete, they can perform Insert but that almost doesn't happen.

Most of the site uses SELECT queries and the information doesn't change often so I don't mind of if user reads information that was published even a day ago.

Now the isolation level is the default which is Read Committed which seems a bit overkill for my website I think.

After a bit research I think about changing it to Snapshot or read_committed_snapshot.

What do you suggest?


texts: Pass Parameters to trigger function to execute dynamic SQL

texts:
I have this code which was solved by Erwin:
Execute dynamic INSERT ON CONFLICT DO UPDATE in plpgsql function

Now I want the trigger to call the trigger function insert_data_func with parameters (model_cd, processdate).

There are different models, that's why I'm trying to simplify things by using dynamic SQL.

Function:

CREATE OR REPLACE FUNCTION insert_data_func()
RETURNS TRIGGER AS
$func$

DECLARE
 model_cd text;
 processdate text;

BEGIN
 model_cd := TG_ARGV[0];
 processdate := TG_ARGV[1];

 EXECUTE
  $x$INSERT INTO tb_moldsummary AS t
     SELECT $1.machine
          , $1.model
          , split_part(lot, '_', 1)
          , right(lot, position('_' IN lot) * -1)
          , COUNT(lot)
          , $1.datetimestamp
     FROM   model$x$ || to_char(now(), 'YYYYMM') || '
     WHERE  lot = $1.lot
     AND    machine = $1.machine
     GROUP  BY machine, model, lot
     ON     CONFLICT ON CONSTRAINT tb_summary_unique
     DO     UPDATE
     SET    machine = $1.machine
          , totalshots = t.totalshots + 1
          , datetimestamp = $1.datetimestamp'
 USING NEW;
 RETURN NEW;
END
$func$
LANGUAGE plpgsql;

Trigger:

DO$$
BEGIN

EXECUTE $x$CREATE TRIGGER insert_data_trigger
           AFTER INSERT ON modelsample$x$ || to_char(now(), 'YYYYMM') ||
           ' FOR EACH ROW EXECUTE PROCEDURE
             insert_data_func' ||
            (''modelsample''' || ',' || to_char(now(), 'YYYYMM') ||  ')';
END
$$;

The trigger passes the model and process date parameters to the trigger function insert_data_func(). But I get this eror msg.:

  ERROR:  relation "model_cd201707" does not exist
  LINE 9:          FROM   model_cd201707

How to properly use the "$x$" on the insert_data_func() function because I think that's the reason why the model name does not get the value from model_cdvariable.


texts:
I have 4 tables:

event:
event_id(p.k) | uid | circle_id(f.k)

activity:
uid | performed_activity_id(f.k->event_id) | activity_type_id

follow:
follower_id | circle_id(f.k)

notification:
sender_id | receiver_id(follower_id of follow table)

I want to create a trigger which inserts values into the activity and notification tables whenever there is an entry for event table.
I am able to insert values into the activity table because it is directly connected to event table.

However, I am not able to insert into the notification table because the receiver_id field in the notification table is coming from the follow table which is connected to event table by circle_id.

Here I am using select in trigger which is actually wrong.

DROP TRIGGER IF EXISTS  `InsertToActivity` ;

CREATE TRIGGER `InsertToActivity` AFTER INSERT ON  `event`
FOR EACH ROW
begin
    INSERT INTO activity( uid, performed_activity_id, activity_type_id )
        VALUES (new.uid, new.event_id,  '1');

    select follower_id from folow where circle_id=new.circle_id;

    insert into notification_table (sender_id,object_id,receiver_id)
        values (new.uid,new.event_id,new.follower_id);
end;

Is it good way to do this type of work using TRIGGERS?


texts:
I'm already too tired of trying so many different things and I can't even bypass an error which is running
# mysql -u root -p
when it asks for password, no matter what I type it shows
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 430
Server version: 10.3.23-MariaDB-0+deb10u1 Debian 10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

show databases; only shows
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.002 sec)

when I type
MariaDB [(none)]> use mysql;
ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'mysql'

even if I type the right password. I have another user but I can't change its password.

MariaDB [(none)]> select user, plugin from user;

shows
ERROR 1046 (3D000): No database selected

I feel like I really tried everything, I mean I've been like this for so many hours now, and I've changed mysql passwords many times in the past, using console in windows and linux, I have no idea what's going on!
EDIT:
As suggested in comments here's the output to > SHOW GRANTS;
MariaDB [(none)]> SHOW GRANTS;
+---------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `root`@`%` IDENTIFIED VIA unix_socket USING '*227A46552108541F24FF4915F91'              |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

here is the output for SELECT user, host, plugin from mysql.user;
MariaDB [(none)]> SELECT user, host, plugin from mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'root'@'localhost' for table 'user'


texts:
i have this query ..
214 Execution/min , 44.42 Avg CPU(ms)
is there a way to make it much faster

SELECT        P.Id id0,
              P.ProgramId ProgramId1,
              P.ProgramName ProgramName2,
              P.ProgramLevel ProgramLevel3,
              P.Department Department4,
              P.Track Track5,
              P.AcademicYear AcademicYear6,
              P.StartTerm StartTerm7,
              P.Delivery Delivery8,
              P.Fee Fee9,
              P.City City10,
              P.STATE State11,
              P.StartDate StartDate12,
              P.Deadline Deadline13,
              P.DeadlineDisplay DeadlineDisplay14,
              P.ProgramType ProgramType15,
              O.Id as OrganizationId16,
              O.NAME OrganizationName17,
              P.ApplicationType ApplicationType18,
              P.Concentration Concentration19,
              P.ZipCode ZipCode20,
              P.Campus Campus21,
              P.WADisplayName WADisplayName22,
              P.UpdatedDate updateDate23,
              AF.Id InstanceId24,
              RD.Id stateId33
INTO #TempGetFullProgramSelectionInfo
FROM    unicas_config..applicationForm AF
  INNER JOIN UNICAS_CONFIG.. AcademicInstitution AI
           ON AF.casid=AI.casid
  INNER JOIN UNICAS_CONFIG..Organization O
           ON O.academicInstitutionid=AI.id
  INNER JOIN UNICAS_CONFIG..AssociationOrg asOrg
           ON asOrg.FormId=AF.id
  INNER JOIN  UNICAS_CONFIG..Program P
           ON P.AssociationOrgId=asOrg.Id and asOrg.OrganizationId=O.id AND AF.Id = 6286
  INNER JOIN  unicas_config..ReferenceData RD
                 ON P.STATE = RD.ValueId AND RD.ValueAbbr ='US'
  INNER JOIN  unicas_config..ReferenceDataSet RS
           ON RD.ReferenceSetId = RS.SetId AND RS.NAME = 'LK_States'

Now this  if i split  to 2 queries

select RD.ValueId, RD.id into #temp1
from   unicas_config..ReferenceData RD
INNER JOIN  unicas_config..ReferenceDataSet RS   ON RD.ReferenceSetId= RS.SetId AND RS.NAME= 'LK_States'
where RD.ValueAbbr='US';

SELECT        P.Id id0,
              P.ProgramId ProgramId1,
              P.ProgramName ProgramName2,
              P.ProgramLevel ProgramLevel3,
              P.Department Department4,
              P.Track Track5,
              P.AcademicYear AcademicYear6,
              P.StartTerm StartTerm7,
              P.Delivery Delivery8,
              P.Fee Fee9,
              P.City City10,
              P.STATE State11,
              P.StartDate StartDate12,
              P.Deadline Deadline13,
              P.DeadlineDisplay DeadlineDisplay14,
              P.ProgramType ProgramType15,
              O.Id as OrganizationId16,
              O.NAME OrganizationName17,
              P.ApplicationType ApplicationType18,
              P.Concentration Concentration19,
              P.ZipCode ZipCode20,
              P.Campus Campus21,
              P.WADisplayName WADisplayName22,
              P.UpdatedDate updateDate23,
              AF.Id InstanceId24,
              RD.Id stateId33
INTO #TempGetFullProgramSelectionInfo
FROM    unicas_config..applicationForm AF
  INNER JOIN UNICAS_CONFIG.. AcademicInstitution AI
           ON AF.casid=AI.casid
  INNER JOIN UNICAS_CONFIG..Organization O
           ON O.academicInstitutionid=AI.id
  INNER JOIN UNICAS_CONFIG..AssociationOrg asOrg
           ON asOrg.FormId=AF.id
  INNER JOIN  UNICAS_CONFIG..Program P
           ON P.AssociationOrgId=asOrg.Id and asOrg.OrganizationId=O.id AND AF.Id = 6286
  INNER JOIN    #temp1 RD ON P.STATE= RD.ValueId;

using the query recommended by Frisbee

Using Hash join