texts: Database restore using SSMS leaves source database in restoring state

texts:
I am using SQL Server 2016 (Developer edition) and wanted to create a copy of a database in my Server instance. I did a simple backup and then a restore by right clicking the database again -> Tasks -> Restore.

By changing the name of the database, the database indeed gets restored in a new created database. So far, so good. However, the original database, that I started with, and where I started the restore from, gets a (Restoring...) state which is impossible to change in a normal way.

Of course I know how to restore a database differently without creating this problem and, I have my original database working again, but my question is this:

Is this normal behavior? I think I stumbled upon a bug.


texts: Proper use of FULLTEXT index for a column storing unique hash value

texts:
We have an InnoDB table called user, where we have a column called token (varchar(128)) where a unique md5-like value that represents whether a user is authenticated or not, is stored. This column can be filled or empty/NULL, depending on whether the user has authenticated yet or not.

Whenever an API is being called to make a change to other data, we always check whether the token sent with the API is valid or not. The query would be something like:

select *
from `user`
where token ='96e1e3c72ce665bc7aeb93a357801fa3296e500b1aed8016e5342e744990cd0094c98857ae5f4b0a73cc6dfebd11a8491a5ba8448e6ddff8a631eb8213c254d0'

As you can imagine, this query runs into seconds, because it is a query on a varchar field. Now I'm trying to fix the query speed.

I tried adding a FULLTEXT on the token field, but it won't help in the = query. So I'm trying to use MATCH() AGAINST() query as follows:

select *
from `user`
where MATCH(token) AGAINST('96e1e3c72ce665bc7aeb93a357801fa3296e500b1aed8016e5342e744990cd0094c98857ae5f4b0a73cc6dfebd11a8491a5ba8448e6ddff8a631eb8213c254d0')

But this query does not return any result. When I check use EXPLAIN, I get:

id   1
select_type  SIMPLE
table    user
type fulltext
possible_keys    token
key  token
key_len  0
ref  NULL
rows 1
Extra    Using where

Here, the key_len being 0, and rows saying 1, even though no results were returned, are suspicious to me.

Now I'm thinking even FULLTEXT isn't the solution - it doesn't seem to be working for completely unique values with no common words - not usual text.

Can you help me understand:

Why FULLTEXT index with MATCH() AGAINST() is not bringing any results?
What is the best way to index the token field and reduce query time?


texts: Slow Sum/Group By over large amount of rows

texts:
Please can you help with a slow query that we have with a large amount of data (~500K rows). We're on Postgres 10.

SELECT mike_romeo, SUM(hotel) as counts
FROM whiskey_lima
WHERE
    yankee = 'kilo'
AND
    whiskey_india = 'a_value'
AND
    echo = 'romeo_november'
GROUP BY
    mike_romeo;

And my table looks like this:

    Column     |            Type             | Collation | Nullable |                        Default
---------------+-----------------------------+-----------+----------+--------------------------------------------------------
 id            | bigint                      |           | not null | nextval('id_seq'::regclass)
 yankee        | mike_hotel.quebec           |           | not null |
 whiskey_india | character varying           |           | not null |
 echo          | mike_hotel.romeo_yankee     |           | not null |
 mike_romeo    | character varying           |           | not null |
 oscar         | timestamp without time zone |           | not null |
 hotel         | integer                     |           | not null |
 papa          | timestamp without time zone |           |          |
Indexes:
    "whiskey_lima_pkey" PRIMARY KEY, btree (id)
    "index_unique" UNIQUE, btree (echo, mike_romeo, yankee, whiskey_india, oscar)
    "index_query" btree (yankee, whiskey_india, echo)
    "index_on_oscar" btree (oscar)

Our EXPLAIN (ANALYSE, BUFFERS) looks like this: https://explain.depesz.com/s/4lU

GroupAggregate  (cost=77294.010..78398.740 rows=38882 width=69) (actual time=56343.468..59194.302 rows=527807 loops=1)
    Group Key: mike_romeo
    Buffers: shared hit=61428 read=68664, temp read=23413 written=23450
  ->  Sort  (cost=77294.010..77532.650 rows=95455 width=65) (actual time=56343.461..58848.379 rows=530929 loops=1)
          Sort Key: mike_romeo
          Sort Method: external merge  Disk: 111488kB
          Buffers: shared hit=61428 read=68664, temp read=23413 written=23450
        ->  Index Scan using mike_quebec on whiskey_lima  (cost=0.560..67104.720 rows=95455 width=65) (actual time=1.647..38814.509 rows=530929 loops=1)
                Index Cond: ((yankee = 'kilo'::mike_hotel.quebec) AND ((whiskey_india)::text = 'juliet'::text) AND (echo = 'romeo_november'::mike_hotel.romeo_yankee))
                Buffers: shared hit=61425 read=68664

Questions

What should we focus on - are there any obvious solutions?

Can we avoid the sort?

Why has the query planner underestimated the rows - could this be causing some slowness? We're trying to enable extended statistics

We're looking into increasing work_mem so that the disk sort is mitigated - would this make the whole query performant?

What else am I missing?

Thanks a lot for your help


texts: postgresql pgadmin3 queryconnect attribute does not work

texts:
I'm using Fedora 17 and pgadmin3 to auto-connect to a postgresql database with one button click.

Every time I want to run an SQL statement, I have to do 4 steps, (open pgadmin3), (connect to the server), (connect to the database), (open a query window), (open a previous file) then FINALLY I can run my SQL statement.  I want this all to be done with one click.

pgadmin3 appears to have features to do do all this work for you on the commandline:

el@defiant ~ $ pgadmin3 --help
Usage: pgadmin3 [-h] [-s <str>] [-S] [-Sc <str>] [-q] [-qc <str>] [-f <str>] [-cm <str>] [-ch <str>] [-cp <str>] [-c <str>] [-t]
  -h, --help                            show this help message
  -s, --server=<str>                    auto-connect to specified server
  -S, --serverstatus                    open server status window
  -Sc, --serverstatusconnect=<str>      connect server status window to database
  -q, --query                           open query tool
  -qc, --queryconnect=<str>             connect query tool to database
  -f, --file=<str>                      file to load into the query tool in -q or -qc mode
  -cm:<str>                             edit main configuration file
  -ch:<str>                             edit HBA configuration file
  -cp:<str>                             edit pgpass configuration file
  -c:<str>                              edit configuration files in cluster directory
  -t                                    dialog translation test mode

But I can't get it to work, I can either connect to the database or open the file I want.  It doesn't seem to allow me to simultaneously connect to a server, connect to a specific database, open a query window, and open a previous sql file.

What are the proper commands to connect to server, database and open a query editor and load a file into it?  I've tried a hundred variations, but pgadmin3 either just does not do anything and does not give an error message, or it does only part of what I told it to do with no error message.

This is the command I'm using, it does open my file, but it connects to the wrong database, and I have to manually change it.  The command line option to choose the database does not work.

pgadmin3 --file=/home/el/Desktop/sqlfile_for_postgresql.sql --query --server=defiant


texts:
I created a user named 'client', witch refuses to connect.
Here's my client:

CREATE ROLE client LOGIN
  ENCRYPTED PASSWORD 'md5d5bc32b75da65fe60067f501a4bb6665'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL '1970-01-01 00:00:00';
GRANT viewers TO client;

Here's how I changed his password:

ALTER USER client PASSWORD 'client';

Here's how I fail to connect:

psql -h localhost -d core -U client
Password for user client:
psql: FATAL:  password authentication failed for user "client"
FATAL:  password authentication failed for user "client"

What have I done wrong? (I did tried restarting)

Just in case there are some more dumps:

viewers group:

CREATE ROLE viewers LOGIN
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL '1970-01-01 00:00:00';

core database

CREATE DATABASE core
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       CONNECTION LIMIT = -1;
GRANT CONNECT, TEMPORARY ON DATABASE core TO public;
GRANT ALL ON DATABASE core TO postgres;
GRANT CONNECT, TEMPORARY ON DATABASE core TO viewers;


texts: Performance of using a non-clustered index as primary key

texts:
Background

I am designing a database for a record management system. It's early stages as I am considering what to use as a primary key: INT or UNIQUEIDENTIFIER.

The reason for considering UNIQUEIDENTIFIER is because there is a good chance we will want a feature that allows records to be moved to other systems (foreign database), and handling all relationships with GUIDs is going to make that so much easier.

Having done some research, there are plenty of debates on the pros and cons of each, the main one I am concerned with is performance. The outcome of my research is that using a UNIQUEIDENTIFIER as the PK will not be a problem as long as it isn't also used as the clustered index for the table.

Ok, so the obvious solution would be to split them into two separate columns:

TableA

ID: INT, Identity, Clustered Index
PK: UNIQUEIDENTIFIER, Non-clustered Index

Question

My concern here is when I am defining relationships, how will this effect join performance? Take for example, this 'child' table:

TableB

ID: INT, Identity, Clustered Index
PK: UNIQUEIDENTIFIER, Non-clustered Index
FK: UNIQUEIDENTIFIER, Non-clustered Index

Should I be concerned with any performance loss, particularly with joining tables, by using this approach?

For example, using a query such as:

SELECT * FROM TableB JOIN TableA ON TableA.PK = TableB.FK

Basically, is this the best design for supporting a GUID based PK, or is this going to lead to bad performance? Are there any significant disadvantages to using the approach I suggested?


texts: What's best practices for changing a user's email in an application?

texts:
I'm working on a crud for an email application, and I'd like the ability to change "everything". Right now, however, it matches the JSON string to the Database based on the email address.

Problem: Under this model, a user can never change their email. A new email address = a new user.

    declare @email varchar(255),
    @ID int

    insert into announcement_jsonlog (json_string)
    values (@json)

    select @email = email from
            openjson(@json)
            WITH
            (email varchar(255)         '$.Email')

    insert into announcement_contacthistory (
    ID, Email, Prefix, FirstName, MiddleInitial, LastName, Suffix, Title, Company, Address1, Address2, City, State, Zip, Zip_4, Phone, Extension, Unregistered, AddDateTime, LModifiedDateTime,
    longkey, shortkey, History_addDateTime)

    select *, current_timestamp from announcement_contact
    where email = @email

Question: how would you design a CRUD that lets you change the email address, too?


texts: Is it impossible to iterate table rows in Oracle PL/SQL without using a cursor?

texts:
I wanted to figure out if it was possible to avoid using cursor to iterate the table rows in PL/SQL and tried the following script:

set serveroutput on;

declare
    v1 tbl_plant.plant_id%type := 0; -- number(10,0)
    v2 tbl_plant.name%type := ''; -- varchar2(50)
begin
    while true
    loop
        select plant_id, name
        into v1, v2
        from tbl_plant
        where rownum = 1
            and plant_id > v1
        order by plant_id;

        exit when sql%rownum = 0;

        dbms_output.put_line(v1 || v2);
    end loop;
end;

So I figured that compiler optimizes this into an implicit cursor as while loop is used, and tried to circumvent:

set serveroutput on;

declare
    v1 tbl_plant.plant_id%type := 0;
    v2 tbl_plant.name%type := '';
begin
<<divein>>
        select plant_id, name
        into v1, v2
        from tbl_plant
        where rownum = 1
            and plant_id > v1
        order by plant_id;

        if sql%rownum = 0 then
          goto bailout;
        end if;

        dbms_output.put_line(v1 || v2);
        goto divein;
<<bailout>>
    null;
end;

But in both cases I got exactly the same error:

PLS-00207: identifier 'ROWNUM', applied to implicit cursor SQL, is not a legal cursor attribute

But if I commented out either while or goto and the labels, eliminating either type of loop, the same select worked fine and returned the first id/name in tbl_plant by plant ID.

Is it at all possible to iterate w/o using a cursor?


texts: Implementation of Rollback in PostgreSQL

texts:
How is the rollback command implemented within the PostgreSQL database?

I wanted to know the implementation details and what exactly happens underneath when a rollback is executed, i.e. state of database storage, changes in write-ahead logs (WAL).

Scenario: I have a table, called employees, having disk size of, say, 4GB on the database. When I open a transaction on this table to update a single row, and then rollback the changes, what would happen inside the database? Would a new version of the table created on the disk but remain inactive due to rollback?


texts: PostgreSQL: import several .csv files through sql copy statement

texts:
In the server hosting the PostgreSQL server, there is a very specific directory where a series of .csv files will be loaded regularly to update one of the databases. I want to make the process of uploading the data contained in these files as automatic as possible, and I have thus created a .sh script to do this. It is a simple for loop iterating through the set of .csv files in that directory and passing their names as parameters to a \COPY sentence.

Now, since the server administrator are being a little privy of their server, they would like to give us access only to the SQL server and not to the underlying unix server. So, here goes the question:

Is there a way to accomplish the task described above through a stored procedure executed from inside the database? Can you really read and access a path and its contents in that way from the database? The whole set of .csv files could vary so I don't think a hard-coded solution would work, plus it would look rather dirty (although, if that is the only way I can make it work, so be it).

My guess is that you cannot but... you never know.


texts: Can I let PostgreSQL report the offending rows when a multi-row INSERT fails?

texts:
When inserting multiple rows into a table by a single INSERT INTO invocation, and the INSERT statement fails due to reasons specific to some individual rows (e.g. a value has the wrong type for the target column, or a NOT NULL constraint is violated), can I let PostgreSQL report the content of the offending rows?

For UNIQUE constraint or exclusion constraint violation errors, it seems I could use the ON CONFLICT clause to access the offending rows, but is there something similar for other row-specific violations?


texts:
I have an embedded device running a MySQL database with a web based application.

The problem is when I shutdown my embedded device it just cuts off the power, and I can not have a controlled shutdown. Given this situation how can I configure MySQL to prevent it from failures and in case of a failure, I should have maximum support to recover my database.

While searching this, I came across InnoDB Engine as well as some configuration options to set like sync_binlog=1 & innodb_flush_log_at_trx_commit=1. I have noticed my default Engine is InnoDB and binary logs are also enabled.

What are other configurations to make for best possible failure & recovery support?

I have absolutely no experience with databases and MySQL.


texts: How i can create backup script from diagram in SQL Server?

texts:
I use SQL Server 2012 and SQL Server 2008 R2.

I create a script from all object (tables / trigger / stored procedure / function ...) in my database.

I generated this script from SQL Server Management Studio.

I can recreate my database with this scrips on the other server.

But I miss all diagrams of my database after run my script for create another database.

Therefore, I need create backup script from all diagrams that exist in my database.

I need execute this script on the destination database for recreating all my diagrams.

I found this Link. but i need some thinks that create all script (Insert Command) automatically.


texts:
I have a Oracle 11g R1 Standard Edition database on production server working on Windows 2008 R2 SE. Unfortunately server it not working properly (os/hardware failure) and I need to move it to temporary machine for a few days.

My first idea was to create full backup on production server and restore it on temporary. Second was, to duplicate database using RMAN. Both methods works fine, but it takes around 4 hours to complete all operations. Database should go back to production sever in few days, so again system will be stopped. It gives 8 hours of system unavailability.

Datafiles are around 60 GB and database is running in archivelog mode

Can you suggest me how can I minimize this time without violating Oracle SE license?

Thanks in advance


texts: Log Sequence Numbers in Full BackUp and Log Backup

texts:
My full back-up starts daily at 02:00 AM. It takes about 3 hours.
I also take T-Log back-ups every 15 minutes (at 15,30,45 and 00 hours).
I can verify the LSN sequences of T-log backups when I "restore headeronly".
However, there seem to be a gap between the last LSN of the full backup and the T-log backup taken immediately after backup had started (i.e 02:15)

Below is restore headeronly output from the full backup started at 02:00

FirstLSN: 76583000073086500011
LastLSN: 76595000084143900001

...and this is the restore headeronly output from the log backup started at 02.15

FirstLSN: 76570000086250400001
LastLSN:  76571000009693900001

Shouldn't the last LSN of the full back up and first LSN of log back up be the same?
I compared the LSNs of the log back up taken at 05:15 and 05:00 (when the full back up has finished) but there is no sequence again.
I also checked RESTORE FILELISTONLY of the full backup and CreateLSN, DropLSN of log file is 0.
What is it that I might be missing here?
Thank you.


texts: Problem with SSIS Variable Expression containing getDate()

texts:
Brief Architecture

My SSIS package is generating dynamic excel files through Data flow. The file name format is "XYZ_Year_Month_Day_Hour_Minutes.xlsx". So I am using variable with expression which uses getDate() to generate above mentioned format.

Problem

I am writing file, using variable name generated through getDate(). Example: "XYZ_2014-8-1_03_30". In control flow I want to access same file in File System task to copy it to FTP.
But by time the variable changes its value to "XYZ_2014-8-1_03_32", so unable to locate file.

I tried with System::StartTime, but it stays same throughout package execution and all files get same name.

Is there any way we can store DateTime in a variable & it won't change further?


texts: how to enalbe MySQL 8 innodb_dedicated_server option

texts:
Initially, I was looking for tuning the MySql 8 instance then I found that there is something like autoconfiguration feature added with the 8th version. Then I visited the official MySQL website for documentation and found this but was unable to wrap my head around this.

As given in the official documentation when this option is enabled MySql autoconfigure certain cache and butter options so that I don't have to change the config when Scale the Server Instance.

When I tried to add innodb_dedicated_server = ON to my.cnf and mysqld.cnf, it
resulted in being unable to start the server.

got nothing in /var/log/mysql/error.log
and found following via journalctl -xe

Jan 08 17:41:50 fa-b2b-mysql mysql-systemd-start[31493]: Once the problem is resolved, restart the service.
Jan 08 17:41:50 fa-b2b-mysql systemd[1]: mysql.service: Control process exited, code=exited status=1
Jan 08 17:41:50 fa-b2b-mysql systemd[1]: mysql.service: Failed with result 'exit-code'.
Jan 08 17:41:50 fa-b2b-mysql systemd[1]: Failed to start MySQL Community Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit mysql.service has failed.
--
-- The result is RESULT.
Jan 08 17:41:50 fa-b2b-mysql systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Jan 08 17:41:50 fa-b2b-mysql systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
-- Subject: Automatic restarting of a unit has been scheduled
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Automatic restarting of the unit mysql.service has been scheduled, as the result for
-- the configured Restart= setting for the unit.
Jan 08 17:41:50 fa-b2b-mysql systemd[1]: Stopped MySQL Community Server.
-- Subject: Unit mysql.service has finished shutting down
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit mysql.service has finished shutting down.
Jan 08 17:41:50 fa-b2b-mysql systemd[1]: mysql.service: Start request repeated too quickly.
Jan 08 17:41:50 fa-b2b-mysql systemd[1]: mysql.service: Failed with result 'exit-code'.
Jan 08 17:41:50 fa-b2b-mysql systemd[1]: Failed to start MySQL Community Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit mysql.service has failed.
--
-- The result is RESULT.


texts: SQL Server 2016 Standard Version Features Evaluation

texts:
We're looking to test the new Basic Availability Groups (BAG) feature in SQL Server 2016 Standard Edition. So far, I have not been able to find an evaluation copy of Standard.

Is there a way we can get our hands on one to test if the BAG would suffice our needs rather than having to purchase the Enterprise Edition.

If not, is there a way we can set up the Evaluation Enterprise Edition to be just like how the Standard Edition will be once bought? We don't want to be running into any surprises later on and have thorough understanding of the differences the Standard Edition has with the Enterprise Edition.


texts: Troubles with an attribute that depends on the value of another attribute

texts:
I'm redesigning a health insurance costs database (actually they're not really insurance plans in my country
but I think that for the sake of the example it doensn't matter). We're the health plan consulting firm so we don't provide health plans, we manage health plans on our client's companies.
My problem lies specifically  on the relationship between the insured and his or her dependants.

I've chosen to model the relevant situation as follows:

The Insured attribute named type is important here. It identifies  if  the insured person in question is the 'primary plan member' or a 'dependant'. But, for dependants, and dependants only, we need a relationship_type attribute ('spouse' or 'child').

My considerations

If I simply add an relationship_type attribute to the insured entity type (and a column the corresponding logical table), there'll be multiple null marks when the insured person is a 'primary plan member'.
If I create a relationship_type entity type and the respective logical table, the foreign key column will retain null marks on 'primary plan member' rows in the Insured table.

Both options include a lot of nulls and I've read that that's not a good sign.

The question

relationship_type is an attribute that depends on the value of another attribute and I don't know how to implement that. Could you share some insights about how to model this situation?


texts:
I have a table set up like this:
The DB has 10B rows.
CREATE TABLE [Tick].[X_H](
       [utcDT] [datetime2](7) NOT NULL,
       [Symbol] [nvarchar](50) NOT NULL,
       [Bid] [float] NULL,
       [Ask] [float] NULL,
       [BidSize] [float] NULL,
       [AskSize] [float] NULL
) ON [PRIMARY]

ALTER TABLE [Tick].[X_H] ADD  CONSTRAINT [PK_Master] PRIMARY KEY CLUSTERED
(
       [utcDT] ASC,
       [Symbol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

I want to select a single day's worth of data for a particular Symbol. I am using:
SELECT *
FROM [Tick].[X_H]
WHERE [Symbol] = 'DONKEY'
AND CONVERT(Date, [utcDT]) = CONVERT(Date,'2011-01-02');

This takes 3 times longer than reading the same data from a binary file and deserialising it. Binary read is 42ms and SQL read is 115ms.
What might I try to speed it up?

The suggestion was made to create a separate [Date] column, which I have and I added 2 indexes.
ALTER TABLE [Tick].[FX_HS] ADD [Date] DATE NULL;
UPDATE [Tick].[X_H] SET [Date] = CONVERT(Date,[utcDT])
CREATE INDEX I_Date ON [Tick].[X_H] ([Date]);
CREATE INDEX I_SymbolDate ON [Tick].[X_H] ([Symbol],[Date]);

And I am using query:
SELECT *  FROM [Tick].[X_H] WHERE [Symbol] = 'DONKEY' AND [Date] = '2011-01-02';

But it takes approximately the same amount of time!

Execution plan
Paste the Plan
Query 1: Query cost(relative to batch): 100%
SELECT * FROM [Tick][X_H] WHERE [Symbol]=@1 AND [Date]=@2

As Martin Smith suggested:
SET STATISTICS TIME ON

DECLARE @utcDT DATETIME2(7)
DECLARE @Symbol NVARCHAR(50)
DECLARE @Bid FLOAT
DECLARE @Ask FLOAT
DECLARE @BidSize FLOAT
DECLARE @AskSize FLOAT
DECLARE @Date DATE

SELECT @utcDT = utcDT,
       @Symbol = Symbol,
       @Bid = Bid,
       @Ask = Ask,
       @BidSize = BidSize,
       @AskSize = AskSize,
       @Date = Date
FROM   Tick.X_H
WHERE  ( Symbol = 'DONKEY' )
       AND ( Date = '2011-01-02' )

This takes:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 43 ms.


texts: performance of Replace vs If exists update else insert vs update if row_count()=0 insert

texts:
Structure of my table is:

Id           int(11)        NO     PRI      auto_increment
Mobile       varchar(10)    NO
LoanAmount   int(11)        NO

I want to UPDATE if existing Mobile else INSERT new entry.

What have I tried?

Using REPLACE as there is no foreign key constrain
IF EXISTS (SELECT 1 FROM table WHERE Mobile='SomeValue')
UPDATE ...
WHERE Mobile=v_mobile
ELSE
INSERT INTO table ...
UPDATE table SET (...) WHERE Mobile='SomeValue'
IF ROW_COUNT()=0
INSERT INTO table.person (...) VALUES (...)

Which would be best in terms of performance or future maintenance?


texts:
I would like to achieve the following:

Users log into a web app, using their credentials from the users table.

After inserting a row into the inserttest table, a trigger activates in which it creates a new row in the log_audit table, with one of the values as the userid of the user who is currently logged in.

Is it possible to script this entirely in T-SQL? I can't seem to get my trigger to work properly.

Webapp connects via service account. It only uses one login in SQL Server (the one that is used in the connection string in the webconfig).

This is what I have:

CREATE TABLE inserttest (
  id INT NOT NULL PRIMARY KEY IDENTITY,
  name VARCHAR(20) NULL,
)

CREATE TABLE users (
  id INT NOT NULL PRIMARY KEY IDENTITY,
  name VARCHAR(20) NULL
)

CREATE TABLE log_audit (
  logid INT PRIMARY KEY IDENTITY,
  userid INT NOT NULL,
  logdate DATE NULL
)

CREATE TRIGGER tgr_insert ON inserttest
AFTER UPDATE, INSERT
AS BEGIN
  INSERT INTO log_audit (userid, logdate)
  VALUES (@USERIDofCURRENTAPPUSERinUSERSTABLE, GETDATE)
END -- trigger script will not work of course


texts:
I know what PostgreSQL checkpoint is and when it is happening.

I need some additional information about the logs produced by the log_checkpoints = on parameter, so please explain some points of it to me:

2017-09-09 16:31:37 EEST [6428-6524] LOG:  checkpoint complete: wrote 30057 buffers (22.9%); 0 transaction log file(s) added, 0 removed, 47 recycled; write=148.465 s, sync=34.339 s, total=182.814 s; sync files=159, longest=16.143 s, average=0.215 s

I know that 22.9% of shared buffers are written (I have 1024 MB shared_buffers so that means 234 MB are written out).
I know that 47 WAL files are recycled, i.e., they are not needed anymore for crash recovery, because the real data from them is already on disk.

Question A. But what about write=148.465 s and sync=34.339? What is the difference? What is write and why its time is far more than the fsync() operation?

Question B. What are sync files? Which files: WAL files? Why sync files are 159, but there are only 47 recycled files? What is the relation between these?

Thank you!


texts: Trouble with differential backup - full backup not being recognized

texts:
I have recently implemented backups using https://ola.hallengren.com/.

Backups

EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES, -%Archive', @Directory = N'H:\sqlbackup', @BackupType = 'FULL',@Verify = Y', @CleanupTime = 360, @CheckSum = 'Y', @LogToTable = 'Y'

EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES, -%Archive', @Directory = N'H:\sqlbackup',@BackupType = 'DIFF',@Verify = 'Y',@CleanupTime = 192,@CheckSum = 'Y',@LogToTable = 'Y'

EXECUTE [dbo].[DatabaseBackup]@Databases = 'USER_DATABASES',@Directory = N'H:\sqlbackup',@BackupType = 'LOG', @Verify = 'Y',@CleanupTime = 48,@CheckSum = 'Y',@LogToTable = 'Y'

Schedule:

Full - once weekly
Diff - daily
log - hourly

Servers:

2 X sql server 2016, compatibility level set to (sql server 2012)
1 X sql server 2016, compatibility level set to (sql server 2016)

PROBLEM:

Suddenly, differential started failing on 2 servers.

Error is :
Cannot perform a differential backup for database "aspnetdb", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.

However, the DB is registering the last full backup. This is a full backup I manually took after last failure like this. Recovery mode is "simple"

This occurs on 1 server with compatibility 2012, and on 1 with compatibility 2016

on 1 server, differential does not fail but size = to full backup

That same time the job starts failing on the other 2 server, on this server the job completes but the file size suddenly jumps to be the same as full backup.

Clues

when I kick off the full backup job manually, then differential can be run manually. Then it runs OK next night, then next day fails in the way to describe
I am not excluding simple-recovery model DBs from Log backup explicitly
I have changed some DBs to simple recovery model since backup was setup, but DB server was not restarted
I modified the olg script abit, to have different clean up time per backup type
log backups are working OK on the servers where diff fails
log backups are showing as completed on the server where the diff completes but file is large, but the log backup files are not on disk - silent failure. The last full backup is just before the 1st differential backup that generates the large file

Examining backup history for 1 DB:
the "is copy" & "is_snapshot" backups are triggered by azure backup services of the actual VM where the sql server runs

SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
msdb.dbo.backupset.is_copy_only,
msdb.dbo.backupset.is_snapshot,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date

ANY CLUES FOR ME ?


texts:
I'm want use Postgresql 9.3 in CentOS 7 and add extention oracle_fdw.
but when use this extenstion i get error

OCIEnvCreate failed to create environment handle after installing oracle_fdw.

my roadmap:

install postgresql 9.3 from postgres rpm.
install oracle instant client 11.2g with rpm files from oracle site.
install  oracle-instant client11.2-basic & oracle-instantclient11.2-sqlplus  &
oracle-instantclient11.2-devel.

I use this question : OCIEnvCreate failed to create environment handle after installing oracle_fdw

my variables :

/etc/profile :
export PG_HOME=/usr/pgsql-9.3
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$ORACLE_HOME/bin:$PG_HOME/bin/:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME:$PG_HOME/lib:$LD_LIBRARY_PATH

tnsnames.ora :

I created /usr/lib/oracle/11.2/client64/network/admin/tnsnames.ora:

BEH =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.102.107)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

When I use the sqlplus command line, I can connect using sqlplus user/pass@BEH and can select from tables.

Using psql in my Postgres database:

CREATE EXTENSION oracle_fdw;
      resutlt : Query returned successfully.

CREATE SERVER beh FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver 'AMI_BEH');
   OR
   CREATE SERVER beh FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//172.18.102.107:1521/ORCL');
      result : Query returned successfully.

CREATE USER MAPPING FOR postgres SERVER beh OPTIONS (user 'ORACLE_USER',password 'ORACLE_PASS');
      result : Query returned successfully.

CREATE FOREIGN TABLE bl
   ("OLD_DYDJ" character varying(20) ,
    "DYDJ" character varying(20) ,
    "MC" character varying(128) )
   SERVER beh
   OPTIONS (table 'DYDJ_DY');
      result : Query returned successfully.

SELECT * FROM bl;
      result : error connecting to Oracle: OCIEnvCreate failed to create environment handle.

I checked that I can use sqlplus as the postgres user (su - postgres) and I can connect to oracle with the sqlplus command.

I found this link http://permalink.gmane.org/gmane.comp.db.postgresql.general/166914 , so I checked:

SELECT pg_backend_pid(); result : 3854
ps -p3854 -oppid=        result : 1902
cat /proc/1902/environ | xargs -0 -n1
   result :
      PG_GRANDPARENT_PID=1
      USER=postgres
      PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin
      PWD=/
      PGLOCALEDIR=/usr/pgsql-9.3/share/locale
      LANG=en_US.UTF-8
      PGSYSCONFDIR=/etc/sysconfig/pgsql
      SHLVL=1
      HOME=/var/lib/pgsql
      LOGNAME=postgres
      PGDATA=/var/lib/pgsql/9.3/data
      _=/usr/pgsql-9.3/bin/postgres


texts: Does Log-Shipping need Transaction Log Backup from Primary Server?

texts:
In this video tutorial it is explained that we should use the transaction log backup of the primary server database and restore it into the secondary server database for log shipping.

I was doing a sample practice to check at my machine and got to know that we don't need to take a backup of the transaction log from the primary database server. Just take a full backup and restore it to the secondary server.

By doing this it will automatically copy the transaction log file to the secondary server.

Can you please confirm if this is the right approach or if it is mandatory to include the transaction log backup from the primary server?


texts:
I'm wanting to find a way to how I can implement a booking system that lets users put a start date on the booking and generate the end date whenever.

Lets take a house renting example. Say I wanted to book a house from a particular starting date, but I'm not sure when I'll leave, the maximum stay for users who don't put a end date is 3 days, how could I do build my database so there aren't any booking overlaps?

The current structure I have is:

booking

booking_id      | integer | not null default nextval('booking_booking_id_seq'::regclass)
houseId | integer |
user_id         | integer | not null

booking_date

 booking_date_id | integer                     | not null default nextval('booking_date_booking_date_id_seq'::regclass)
 book_from       | timestamp without time zone | not null
 book_to         | timestamp without time zone | not null
 booking_id      | integer                     |

I'm wondering whether I store an extra field in the booking entity is_stayAndLeaveWhenever

and then in the booking_date I'd just store the book_to with the book_from + 3 days (max stay without choosing leave date)

Is that a solid solution?


texts: Are queries called parallel or in sequence?

texts:
Given this table:

id | field
----------
 1 |   2

Would it be possible that  the column field is only incremented once, if two processes run in parallel this query:

  UPDATE table SET field = field + 1 WHERE id = 1

In other words, if the above query would be executed twice, would it be possible to end up with the following database?

id | field
----------
 1 |   3

I searched at https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html but I was not able to find an answer there.


texts: Basic company and user registration database - Pulling my hair out

texts:
I have now created a partially working database, although I feel I have over complicated things, and also, not sure where to add the extra tables/relationships required.

First off all, I have a simple website where you can register a new company (these details go into my company table) Also, the person registering the company will enter their personal details, and these will go into the User table, so at the time of registering a company, there will be at least one user, and the filed IsCompanyAdmin will be set as true.

Now, this admin can log in online, and add new users to the system (users that must work at that company)

The User authentication table handles all authentication for that user. A company admin user will have the authentication type password, and they can log in online, however, all new users that an administrator create for the company will be smart card, and can not log into an online account.

When an administrator is logged in for the company, he can set up a new user, and assign a smart card (have not added this yet, not sure where to add this) and the account would be in a pending state, until the user activates their smart card (this functionality is in an end user client desktop application).

I have tested my concept and design in Microsoft Access, and it seems to work OK, however, I am not sure where or how to handle the passwords, smart cards etc for different log in types.

So, I am hoping to get a little help on the following points.

Is the schema so far OK, or have I over complicated things?
Where/how should I add passwords, smart card data etc for authentication?

Thank you again for any help/advice


texts: Oracle 10.0 package shows INVALID status in reference on oracle 11g on import with IMPDP

texts:
I have imported dump of oracle 10 version with tables, procedures and packages to oracle 11g version.

While am not facing any issues with tables and procedures, certain body of packages compiles with error.

On checking the same packages in old oracle version, i ve noticed that the reference status of package body is VALID. But in 11g version, it is INVALID

Also there is a mismatch in dependencies of package from old version and new version.

Cud someone shed light on what am i missing?

I wana compile the procedure without any error in 11g version!


texts:
SOLUTION: MariaDB doesn't allow - like Akina told - to edit old data with a trigger or from a trigger called procedure. Possible alternatives are time-based procedures.

I have this few lines testing each insert on contact_message if there are already more than 3 messages and deletes the oldest. The question is why it doesn't do that. The logic itself should be correct; it was tested and returned the expected result.

Is there a rule for procedures I don't know?

-- This function gets the id and is proved working correctly
DELIMITER $$
CREATE FUNCTION get_id_of_oldest_message_by_mail(mail text) RETURNS int
BEGIN
    RETURN (
        SELECT      cf.id
            FROM    contact_message cf
        WHERE       cf.email = mail
        ORDER BY    cf.date ASC
        LIMIT       1
    );
END$$
DELIMITER ;

-- This procedure deletes the oldest message from given mail
-- if there are more than 3 messages send
DELIMITER $$
CREATE PROCEDURE delete_oldest_message_by_mail(IN mail text)
BEGIN
    DECLARE c int;
    SET c = (SELECT     COUNT(cf.id)
                FROM    contact_message cf
             WHERE      cf.email = mail);
    IF c > 3 THEN
        DELETE FROM     contact_message
        WHERE           id = get_id_of_oldest_message_by_mail(mail);
    END IF;
END$$
DELIMITER ;

-- This trigger calles delete_oldest_message_by_mail (proved working)
DELIMITER $$
CREATE TRIGGER delete_old_messages
    AFTER INSERT ON contact_message
    FOR EACH ROW
    BEGIN
        CALL delete_oldest_message_by_mail(NEW.email);
    END$$
DELIMITER ;

Thank you for any idea!


texts: Confused about sys.dm_io_virtual_file_stats num_of_reads/writes column

texts:
I'm trying to use the sys.dm_io_virtual_file_stats DMF to generate so IO wait information across a number of my servers.  I came across this post by Paul Randall: http://www.sqlskills.com/blogs/paul/survey-tempdb-and-transaction-log-io-latencies/.  In it he asked people to respond with the results of this query:

SELECT
    [database_id],
    [file_id],
    [ReadLatency] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END
FROM
    sys.dm_io_virtual_file_stats (NULL, NULL)
WHERE
    [file_id] = 2 OR [database_id] = 2;
GO

My question is why is he using io_stall_read_ms / num_of_reads instead of io_stall_read_ms / num_of_bytes_read in order to get the average.  I would think ms/byte would be more informative than ms/read.  Which at least in part leads me to believe I don't understand what the num_of_reads/writes columns actually represent.

BOL just says

  Number of reads issued on the file.

which doesn't really help me much.  I'd appreciate any clarification I can get.


texts: How to get index last modified time in Oracle?

texts: Given an index name, I want to obtain its last modification time and the author of modification in Oracle 11g. Is there any query to achieve this?


texts:
I'm trying to shrink a LOB whose owner is TEST and the tablespace it resides is on USERS.

EDIT: Currently, the default tablespace for TEST schema is tablespace called TESTTBL.

I have enabled row movement on the table.

If I try to shrink the particular LOB using:

ALTER TABLE test MODIFY LOB (image) (SHRINK SPACE);

...will it shrink and release space, becuase the schema TEST has default tablespace TESTTBL and data resides in USERS.


texts: Does anybody know how i could use a linked server as a data source for a report model in SQL server 2008 r2? or how i could make this linked server a data source on a report model? Please help. Thank you.


texts: Grouping records based on intervals of time

texts:
I have a table with the following schema, and I need to define a query that can group data based on intervals of time (Ex. records per minute) and then provide the sum of the changes to the SnapShotValue since the previous group. At present, the SnapShotValue always increments so I only need the sum of differences. Can anyone help with a SQL Server T-SQL query that might do this? I am open to changing the schema, but this is what I currently have.

Schema

CaptureTime   (datetime)
SnapShotValue (int)

Sample data

1 Jan 2012 00:00:00,   100
1 Jan 2012 00:00:30,   125
1 Jan 2012 00:01:00,   200
1 Jan 2012 00:01:30,   300
1 Jan 2012 00:02:15,   400
1 Jan 2012 00:02:30,   425
1 Jan 2012 00:02:59,   500

Desired Query Result

1 Jan 2012 00:01:00,   225 -- Sum of all captured data changes up to the 1 minute mark
1 Jan 2012 00:02:00,   500 -- Sum of all captured data changes up to the 2 minute mark
1 Jan 2012 00:03:00,   125 -- Sum of all captured data changes up to the 3 minute mark


texts: Where are the RETURN TABLE columns in the information schema?

texts:
Say I have a function

CREATE FUNCTION foo (_id INT) RETURNS TABLE (id INT, my_name TEXT, adt_date DATE) ...

I desire to return

_________________
id      | INT
my_name | TEXT
adt_date| DATE

So far I have the following query:

SELECT *
FROM information_schema.routines AS r
LEFT JOIN information_schema.parameters AS p ON r.specific_name = p.specific_name
ORDER BY r.routine_name
       , p.ordinal_position;

To my chagrin, I was unable to find the column names in this query for RETURN TABLE functions.
What am I missing here?

Not that it probably matters in this case, but I am running PG 9.6


texts: MongoDB 3.2 replica set with arbiter version 2.6

texts:
I have to set up a MongoDB replication set with 3 nodes, 1 of them arbiter. Authentication has to be enabled. Node1 and Node2 are fine. On the arbiter I have a 2.6 installation and it is arbiter for another configuration. I started a new mongod on that server on a different port and tried to add it to the 3.2 replication set. It is added but it says "not reachable".

From any node I can connect to the new instance in the arbiter host so communication between servers is not a problem. I don't see anywhere in the documentation that all the nodes must be the same version.

Any ideas what am I doing wrong? I could add a 3.2 arbiter with no problem and the replica set is working perfectly, but for some reason I cannot get it to work with a 2.6 arbiter.


texts:
I am purging a table of size 1.2T using pt-archiver. In the show processlist i can see the query stuck for 650 secs in Sending data state. The query is quick when I execute independely. Any help will be appreciated.
[shell]# pt-archiver  --source h=localhost,D=dsm,t=subscriber_event -u XX -pXXX    --where="created <= DATE_SUB(now(), interval 1 year)" --limit 10000  --purge --statistics --progress 10000 --max-lag=100 --check-interval=15 --check-slave-lag h=X.X.X.X,S=/data/mysqllog/mysql_logs/mysql.sock --no-check-charset --why-quit  --primary-key-only --retries=5
TIME                ELAPSED   COUNT
2021-01-16T00:34:27       0       0
DBD::mysql::st execute failed: Lost connection to MySQL server during query [for Statement "SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dsm`.`subscriber_event` FORCE INDEX(`PRIMARY`) WHERE (created <= DATE_SUB(now(), interval 1 year)) AND (`id` < '3873782070') ORDER BY `id` LIMIT 10000"] at /bin/pt-archiver line 6631.

Explain of the query
mysql> explain SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dsm`.`subscriber_event` FORCE INDEX(`PRIMARY`) WHERE (created <= DATE_SUB(now(), interval 1 year)) AND (`id` < '3873782070') ORDER BY `id` LIMIT 10000;
+----+-------------+------------------+------------+-------+---------------+---------+---------+------+------------+----------+-------------+
| id | select_type | table            | partitions | type  | possible_keys | key     | key_len | ref  | rows       | filtered | Extra       |
+----+-------------+------------------+------------+-------+---------------+---------+---------+------+------------+----------+-------------+
|  1 | SIMPLE      | subscriber_event | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL | 1450620612 |    33.33 | Using where |
+----+-------------+------------------+------------+-------+---------------+---------+---------+------+------------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

same query
mysql>SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dsm`.`subscriber_event` FORCE INDEX(`PRIMARY`) WHERE (created <= DATE_SUB(now(), interval 1 year)) AND (`id` < '3873802696') ORDER BY `id` LIMIT 10000;
| 3205169561 |
| 3205169562 |
| 3205169563 |
| 3205169564 |
+------------+
10000 rows in set (0.10 sec)

Please help me finding out the exact cause.


texts: The smallest backup possible ... with SQL Server

texts:
Daily we ship our SQL Server backups across the WAN. We need to minimize the size of these backups so it does not take forever.

We don't mind if our backup process takes a bit longer; as it stands we need to move 30gigs of compressed backup across the WAN that takes over 10 hours.

There are 2 options we have to get smaller daily backups.

Log shipping, which would mean we would have to restructure DR process.
Strip information out of the db and rebuild on the other side (drop non clustered indexes, pack clustered indexes at 100% - rebuild on the other side)

Both would involve a fair amount of work from our part. We are using SQL Server 2008 pro, all backups are compressed.

Are there any commercial products that can give us similar backup size to option (2)?

Is there a comprehensive script out there that will allow us to accomplish (2)? (handling indexed views, filtered indexes, foreign keys and so on)


texts: How to link my Azure VM running SQL server to my on-premise domain or Active Directory?

texts:
I'm kinda new to cloud/azure. I have set up a VM running SQL Server using default templates and settings like dynamic IP and name etc.

Can I connect it to my on premise domain or AD?
What are the exact steps and prerequisites for it?


texts: SQL Server Full-Text Index with Stoplist

texts:
I've searched this site and the internet in general and can't find any information which answers my question, so I thought I'd post it here. I have Full Text search operating on a database in SQL server 2008 and I have 2 questions relating to the index and stoplists:

Do stoplist stopwords prevent the tokenised word from actually being inserted into the index or do they just filter results returned to queries?
Can patterns be used in a stoplist to filter, for example, all numbers?

The reason I ask, is we have lots of documents with tables of numbers which we don't really want indexed or searched for but when I examine our FTS index, over half of the 4 million rows are numbers such as 1, 76.01, -0.1036886733 etc.


texts: Does daily pg_dump mess up postgres cache?

texts:
I migrated my geospatial Postgres 12.5 database to another cloud provider. I use postgis and I have around 35GB of data and 8GB of memory.
Performances are way worse than on my previous provider, and new provider claims this is because the pg cache has to been "warmed up" everyday after automatic pg_dump backuping operations occuring in the night.
Geospatial queries that would normally take 50ms sometimes take 5-10s on first request, and some that would run in 800ms take minutes.
Is there something else looming or is the technical support right ?
If so, should I disable daily backups ? Or can I somehow use a utility function to restore the cache ? (pg_prewarm ?)


texts: How to "properly" back up a MySQL/MariaDB database?

texts:
I have a small (<10GB) database backing an Intranet WSGI website. In the Apache logs I frequently see timeout errors whose timestamp coincides with the daily backup cron job. I'm running incremental backup on /var/lib/mysql using duplicity. My questions:

1) Is this a coincidence, or is it possible to have interference here?

2) Is there a better way to do a database backup?

Host is RHEL7


texts:
I have the following query that returns duplicate values:

  SELECT
    [t].[Field1], [t].[Field2], COUNT([t].[Field2])
  FROM [dbo].[Table1] AS [t]
  WHERE [t].[Field2] <> ''
  GROUP BY [t].[Field1], [t].[Field2]
  HAVING COUNT([t].[Field2]) > 1

Given that statement, I'd like to write an update statement to clear Field2 under these conditions.  How would I write this?


texts: What's the relationship between Documents, XSDs and Collections?

texts:
I have a number of XML Schema Definitions describing business objects. It's unclear to me what the correct way to deploy these is if my intention is to make typed xml columns. The name Xml Schema Collection implies that more than one Xsd Document definition can exist in a collection, but any example I look at shows a Collection as the specific constraint on the column, not a document within it.

Do I create a schema collection called 'BusinessObjects' and create individual Xsd Documents inside it? Do I make one Collection for each Document?


texts: Mysql to MS SQL Migration and synchronization

texts:
Database migration from MySQL to MS SQL using SSMA tool is very nifty, however,  I have one question around this.
I have 2 MySQL Db's want to migrate to MS SQL, one sizes around 850 GB and another one around 450 GB, there are few tables have millions of records and migration take a day only for such tables.
I was searching for a solution, if there is any way we can keep synching the data from source to destination after doing the first full pull migration.
Your guidance is much appreciated.
Thank you,
Indra


texts: How do I view operators in a Notify Operator task in a Maintenance Plan?

texts: In the past, we have added each DBA to a Notify Operator task on failure in Maintenance Plans ranging from scheduled backups to indexing and occasionally one-off operations. One of our DBAs recently left the company and we removed her email account. We also removed her from the list of operators in SQL Agent. However, we have a number of SQL Server servers and instances installed and it would be tedious to go through each maintenance plan on each of those instances to find if she is on the list manually. Is there a way to query each maintenance plan from either within SSMS or PowerShell? We're using SQL Server 2008R2 thru 2014.


texts: What is the difference between sys and system accounts in Oracle databases?

texts:
There are two ways to connect to Oracle as a administrator using sqlplus:

sqlplus sys as sysdba
sqlplus system/manager

These accounts should be uses for different purposes, I suppose.

Which tasks are these two schemas meant for? When should I use one or the other among them?


texts: Inserts via Entity Framework on server take about double the time than locally

texts:
I'm inserting lots of data into SQL Server with many of them having foreign key dependencies. Meaning if I insert 1 row in my Person table, I'm also inserting 1 row in 8 other related tables.

This import takes a lot of time already on my local machine (about 4h30min for 300k (x9) rows), but it takes almost exactly double the time to insert this data on various servers. All of the servers are more powerful than my local machine, so it most likely is not a hardware problem.

What could be the bottleneck here? How do I best go about finding out the cause?
I'm using SQL Server 2017, but we've had this issue since SQL Server 2015 (the one we started the project)


texts:
I need to optimize the following query:

SELECT DISTINCT column1, column2
  FROM tbl_name
 WHERE column3 = 'x'
   AND TRIM(UPPER(column4)) = TRIM(UPPER('y'))
   AND TRIM(UPPER(column5)) = TRIM(UPPER('z'))
 ORDER BY column2

Does not have primary key. It contains only 5 columns.

It is taking about 2 mins when I am using JDBC statement objects for executing this query.

Following are the statments in java:

sql="SELECT DISTINCT column1, column2 FROM tbl_name"+
     "WHERE column3 = 'x'"+
       "AND TRIM(UPPER(column4)) = TRIM(UPPER('y'))"+
       "AND TRIM(UPPER(column5)) = TRIM(UPPER('z')) "+
     "ORDER BY column2"
stmt = conn.prepareStatement(sql);
stmt.executeUpdate();

Table Definition:

CREATE TABLE "NAME"."tbl_name"
  (
    "column1"   VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "column2" VARCHAR2(100 BYTE),
    "column3"         VARCHAR2(50 BYTE),
    "column4"           VARCHAR2(25 BYTE),
    "column5"         VARCHAR2(50 CHAR)
  )
  SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "DATA" ;
CREATE INDEX "NAME"."tbl_name_IDX" ON "NAME"."tbl_name"
  (
    "column3",
    UPPER(TRIM("column4")),
    UPPER(TRIM("column5"))
  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "DATA" ;

Please suggest me.


texts: Are SQL Server's temporal tables here to stay?

texts:
We're considering different approaches to save records' history in an application.
Some of the managers in charge are concerned that as this is a relatively new feature of SQL server, and Microsoft may cease to support it.
Are these concerns justified?
Is there a roadmap to this feature that says what Microsoft intends to do with this feature in the future?


texts: Change Storage Engine on Referenced Tables

texts:
i need to change storage engine from 'InnoDB' to 'MyISAM' on some tables of different databases.
So i made a script file to do so:

#!/bin/bash
DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select distinct(table_schema) from information_schema.tables where table_schema not in ("mysql","information_schema","performance_schema")')
for D in $DATABASES
do
   TABLES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select table_name from information_schema.tables where table_schema="'$D'" and engine="InnoDB"')
   for T in $TABLES
   do
      echo "ALTERING TABLE $T"
      mysql -u XXXXX -pXXXXX -e "USE $D; ALTER TABLE $T ENGINE=MYISAM"
   done
done

It works on some tables, but on those with foreign keys, displays the following error message:

ERROR 1217 (23000) at line 1: Cannot delete or update a parent row: a foreign key constraint fails

Is there a way to change storage engine on those tables?


texts:
i have written the function below containing a cursor while executing it an error appears . what' wrong please

CREATE PROCEDURE processorders()
BEGIN
   DECLARE ordernumbers CURSOR FOR
     SELECT * FROM psdms_feature_product

   dept_rec ordernumbers%ROWTYPE
   o ordernumbers%ROWTYPE

   OPEN ordernumbers
   REPEAT
    FETCH ordernumbers INTO o

    IF (o.id_product ) THEN
      IF (o.id_feature  == 510) THEN
        update    psdms_feature_value_lang t1
        LEFT JOIN psdms_feature_product t2
        ON        t1.id_feature_value = t2.id_feature_value
        SET       value ='95090€'
        WHERE     t2.id_product = 1
        AND       t2.id_feature = 510
      ELSE
        insert into psdms_feature_value ( id_feature ) values (510);
        insert into psdms_feature_product ( id_feature, id_product , id_feature_value)
        values (o.id_feature , o.id_product , o.id_feature_value )
      END IF
    END IF

   UNTIL done END REPEAT
   CLOSE ordernumbers
END
END


texts: Waitresource format explanation in deadlock xml

texts:
I have a deadlock xml where two transactions are deadlocked on two resources. waitresource attributes of the process elements have values "OBJECT: 8:1269579561:4" and "OBJECT: 8:1269579561:0".

It looks like table objects locks but what are the third parts in the object description: ":4" and ":0"? Where can I find detailed explanation of the waitresource attribute?


texts: Oracle DBA - what's the difference between the connection strings?

texts:
I'm working on a .NET application that is supposed to connect to any OracleDB but I'm a bit stumped, trying to get it to work on different versions.

I originally started with Oracle 11g (Express), trying to connect was actually rather easy, I simply used the following Connection String and it worked:

Data Source=hostname:port;User Id=myUsername;Password=myPassword;

I then tried to connect to a 12c and it told me that I didn't specify the service name. This confused the shit out of me because I never had to specify the Service Name in 11 but I looked online and found another Connection String in this format:

SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))
(CONNECT_DATA=(SERVICE_NAME=MyOracleSID))); uid=myUsername; pwd=myPassword;

It still didn't work. A colleague gave me the tip to use the "Global Database Name" instead of the SID (ORCL.domainname.local instead of ORCL) and it worked after we added ORCL.domainname.local to our Domain Controller's DNS Server's forward lookup zones.

I then went back to 11g did the same things there but turns out using the new Connection String there doesn't work.

While we could probably implement some kind of "trying until it works"-feature in our connection string builder (user specifies the data) this seems like a terrible solution and I don't think it's a good idea. Also at this point we really want to know WHY so we can tell if it's a configuration issue or an absolute version issue.

Anyone care to give me some insight?


texts: Grant permissions to a user to grant select to specific tables in several schemas in Oracle

texts:
I administer a small datawarehouse in Oracle.
My user dwhmanager was given the SELECT ANY TABLE privilege:

GRANT SELECT ANY TABLE TO dwhmanager WITH ADMIN OPTION;

The idea was to have the ability to grant access to tables in several schemas to the developers in the datawarehouse.

Example:

GRANT SELECT ON DWHFINANCE.SALES TO johndeveloper;

However, I found my self getting the infamous ORA-01031: insufficient privileges

I can however, give "johndeveloper" the SELECT ANY TABLE privilege as well, and from that, he could select on any table on the datawarehouse, however, that's too much, not my intend.

So, what permission to I need to ask to the main DBA to grant me in order to have the power to give permissions in individual tables in other schemas?


texts: PL/SQL block running without printing any output

texts:
I'm trying to exexcute a pl/sql block using dbms_output to obtain a console output but without reason the unique answer by SQL developer is 'Anonymous block finished'.

declare
    v_count integer;
    lines dbms_output.chararr;
    num_lines number;
 begin
    dbms_output.ENABLE;
    num_lines := 0;

    for r in (select table_name, owner from all_tables
              where owner = 'SYSAME'
              and table_name like 'SYZ%'
              and table_name not in (select distinct table_name from all_tab_columns
                                      where OWNER = 'SYSAME'
                                      and table_name like '%SYZ%'
                                      and column_name not like '%FL_IDE_FILIALE%'))

    loop
        execute immediate 'select count(*) from ' || r.table_name || ' where fl_ide_filiale = 12'
            into v_count;
        dbms_output.Put_line(r.table_name ||';'|| r.owner ||';'|| v_count ||';'|| SYSDATE );
        num_lines := num_lines + 1;
    end loop;

    dbms_output.get_lines(lines, num_lines);
    dbms_output.Put_line(num_lines);

    FOR i IN 1..num_lines LOOP
      dbms_output.Put_line(lines(i));
    END LOOP;

end;

Any idea why i don't obtain any output ?

Q2: is there any way to plot the output not with a string line and in a table-like way. In this case someting like a select with this values :
r.table_name, r.owner, v_count, SYSDATE


texts: no performance improvement with pgpool query result caching

texts:
I'm trying to add query result caching to a PostGIS database that's frequently hit with the same (expensive) spatial queries. Looks like pgpool-II is currently the only well-developed solution.

Testing locally, I found that results are great for small responses. An expensive SELECT COUNT(*) takes 0.08s with the pgpool cache, and 9s without it.

Once I try my spatial queries though (responses as big as 300MB), I see no measurable speed improvement. Pgpool's SHOW POOL_CACHE command tells me the cache is indeed used for my query:

-[ RECORD 1 ]---------------+-----------
num_cache_hits              | 1
num_selects                 | 1
cache_hit_ratio             | 0.50
num_hash_entries            | 1048576
used_hash_entries           | 1
num_cache_entries           | 1
used_cache_entries_size     | 4523460
free_cache_entries_size     | 2142960192
fragment_cache_entries_size | 0

Here's my pgpool cache config:

memory_cache_enabled = on
memqcache_method = 'shmem'
memqcache_total_size = 2151677952
memqcache_max_num_cache = 1000000
memqcache_maxcache = 536870912
memqcache_cache_block_size = 536870913

Am I doing something wrong, or is this pgpool's cache implementation? Also open to other ways to add server-side query result caching.

EDIT: for the record, here's the test query:

SELECT way,
       highway,
       route
FROM   planet_osm_line
WHERE  ( "highway" IN ( 'motorway', 'motorway_link', 'primary', 'primary_link',
                        'secondary', 'secondary_link', 'trunk', 'trunk_link',
                        'tertiary', 'tertiary_link', 'residential',
                        'unclassified',
                                 'pedestrian' )
          OR "route" IN ( 'light_rail', 'railway', 'tracks', 'train' ) )


texts: Can I use hypertables in timescaledb merely to get better insert rates?

texts:
I have a PostgreSQL db that I have a large bulk load running into. I wish this load to be as fast as possible. I'm already using the copy command etc.

I have been reading about timescaledb and how it offers improved insert performance. However, I wonder if there is any downside to using hypertables instead of regular tables, if I only care about insert performance?


texts: Most efficient way to retrieve data by timestamps

texts:
I'm using PostgreSQL 9.2.8.

I have table like:

CREATE TABLE foo
(
    foo_date timestamp without time zone NOT NULL,
    -- other columns, constraints
)

This table contains about 4.000.000 rows. One day data is about 50.000 rows.

My goal is to retrieve one day data as fast as possible.

I have created an index like:

CREATE INDEX foo_foo_date_idx
ON foo
USING btree
      (date_trunc('day'::text, foo_date));

And now I'm selecting data like this (now() is just an example, i need data from ANY day):

select *
from foo
where date_trunc('day'::text, now()) = date_trunc('day'::text, foo_date)

This query lasts about 20 s.

Is there any possiblity to obtain same data in shorter time?

EDIT:

EXPLAIN ANALYZE of select above:

"Index Scan using foo_foo_date_idx on foo (cost=0.01..20762.83 rows=10421 width=1534) (actual time=0.180..249.126 rows=19773 loops=1)"
"  Index Cond: (date_trunc('day'::text, now()) = date_trunc('day'::text, foo_date))"
"Total runtime: 251.883 ms"

I have changed a day for 15-12-2015. What is more, I have replace * with only specific columns. Explain plan:

"Index Scan using foo_foo_date_idx on foo (cost=0.00..57876.52 rows=29184 width=62) (actual time=22.384..28243.356 rows=33153 loops=1)"
"  Index Cond: ('2015-12-14 00:00:00'::timestamp without time zone = date_trunc('day'::text, foo_date))"
"Total runtime: 28251.876 ms"

Last query could cached i think then... Any other explaination?


texts: Transactional Replication issue due to big Transaction Log in sql server 2008 r2

texts:
I want an answer of question and that would be highly appreciated

Scenario:

First of all i am an IT guy from Sports Field and using SQL Server 2008R2. I have 2 production servers server 1 and Server 2 both are using SQL Server Transactional Replication [whereas Server 1 is Processing Major Database Operations crawlers and xml parsers data translation and many more stored procedures. it’s a publisher]
[Server 2 mostly contains Websites that are used to show sports data on website and it’s a subscriber]

Actual Data file size is around 3 GB on server 1
Actual Data file size is around 1 GB on Server 2

The problem is that Server 1’s Log file size is increasing with uncertainty let say it grows over the night and jumps to 14 – 15 GB normally if i do not shrink the size with simple recovery model. Usually i am using Full Recovery model because of Replication. I know that it’s pretty much load but that thing was not growing until i tried to make DB’s errors rectified.

Due to log size increase replication between both servers struck.
some of my queries also contain nolock because previous problems until my joining was deadlocks in that DB. Now it’s around 85% less. :)

I already took and implement a Transaction log because someone posted that it might be helpful to take Log backup it helps to reuse the free space of log but there is no success indeed suppose a backup log which was using around 2 Gb log Once and had around 1600 MB free so the log backup which i initiated was around 400MB. but still the space 1600 MB was enaged to log.

please help / suggest a solution to automaticaly clear DB’s Transaction log.
Summary.

Irregular / uncertainty in growing of Transaction Log and not releasing space
Transactional Replication between both servers stopped due to Transactional log (I think)

One more thing : i got an errors from log reader.

  Error codes are (355, 298, 165, 382, 18456, 4060).

There are only 2 users sa, sqladmin


texts:
When I start the MySQL bash client, I have been starting it with --local-infile=1 so that I can import data from a .csv file.

Is there any way of doing this within MySQL Workbench?


texts: Why DBCC CHECKDB is multiple times in error log

texts:
I have an issue with dbcc checkdb command . In the errorlog file I find the dbcc checkdb command multiple time within the interval of 30-45 minutes. I have scheduled a job which grep keyword error from the errorlog file but due to dbcc checkdb all the dbcc checkdb command my mail is simply full with it.

Can anyone please help me on this that why we use dbcc checkdb command and what is the use of it. Is this behavior is ok or we need to take some action over it.

Please help me out.

Thanks,

Nitesh Kumar


texts: What are the practical uses for relational calculus?

texts:
In my Database Design course we are learning both Relational Algebra and Relational Calculus. I can see where Relational Algebra could be useful since it is closely tied to SQL.

Our professor said that Relational Calculus was used as an alternative to SQL in some RDMBS, most of which are not around any more. Is there still a practical use for Relational Calculus, or is much of this theoretical?


texts: Unable to install pl/python for Postgresql12

texts:
To get the obvious out of the way, my question is posted after reading many other questions like this one
https://stackoverflow.com/questions/60683776/install-pl-python-on-windows-for-postgresql-12
But I ve been stuck for about a month and half now when trying to use pl/python.I'm using Postgresql 12 with the enterprisedb package including pgadmin4 as a gui and the language pack  . So PL python is supposed to be included.When i ran "CREATE EXTENSION plpython3u;"
I got the following message ERROR: ライブラリ"C:/Program Files/PostgreSQL/12/lib/plpython3.dll"をロードできませんでした: The specified module could not be found. SQL state: 58P01which is  the classic "ERROR: could not load library "C:/Program Files/PostgreSQL/12/lib/plpython3.dll": The specified module could not be found."
My understanding, based on reading multiples posts and articles, is that 2 elements are required
1. the proper python interpreter
Python runtime needs to match the PostgresSQL version The documentation within the PostgreSQL folder indicates The procedural languages pl/Perl, pl/Python and pl/Tcl are included in this distribution of PostgreSQL. The server has been built using the LanguagePack community distributions of those language interpreters. To use any of the these languages from within PostgreSQL, download and install the appropriate interpreters and ensure they are included in the PATH variable under which the database server will be started. The versions used are shown below - newer minor (bugfix) releases may also work, but have not been tested:
* Python 3.7

Also when running plpython3.DLL found in C:\program files\postgresql\12\lib through the software "dependency walker" I get the following result :

Which would mean that
-it's indeed python 3.7 which is required as mentioned in the doc.
-the dependency  was found.
2. setting the environment variables PATH and PYTHONHOME
Now both the documentation
https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/language-pack-guide/12/EDB_Postgres_Language_Pack_Guide.1.09.html#
and a forum help administrator
https://postgresrocks.enterprisedb.com/t5/PostgreSQL/unable-to-install-pl-python-extension/td-p/4059/
mentions the needs to change environment variables PATH and PYTHONHOME.
something along the lines of
set PYTHON_HOME=c:\ edb\languagepack\v1\Python-3.7

and set PATH=C:\edb\languagepack\v1\Python-3.7;C:\edb\languagepack\v1\Perl-5.26\bin; C:\edb\languagepack\v1\Tcl-8.6\bin;%PATH%
the forum mentions you need to change those with the command prompt.
the doc isn't specific as to how you re supposed to do that but i figured it meant the python interpreter itself
either way it doesnt work.
when I use the command prompt there is no answer: no error, no nothing. It just  starts new on another line.
in python interpreter, the syntax is invalid.
and when I use the query in pgadmin I have the same error.
I saw other possible methods like a batch file or windows settings  but it seems completely different.
I really want to move forward after being stuck so long and would much appreciate some insight.


texts: Converting MySQL 5.6.10 - 32 bit to 64 bit version

texts: I am currently using MySQL DB Version 5.6.10 for 32 bit running in 64 bit Window OS.I want to change it to 64 bit without taking any dump..Can anyone help in this?


texts: Item availability in row or another table

texts:
I'm a database newbie, so please bear with me.

Say I'm creating a library database. I have a table containing information about books. Do you put the book availability as a row in this table or would you create another table containing only the book (using a foreign key) and the availability?

Any pointers would be gratefully received.


texts: EXECUTE AS LOGIN failed with local windows group login

texts:
I'm troubleshooting a permission issue that I tried to perform the query below
EXECUTE AS LOGIN = 'ComputerName\Windows Group Name';

SELECT * FROM fn_my_permissions(NULL, 'SERVER');

SELECT * FROM fn_my_permissions (NULL, 'DATABASE');

SELECT *
FROM sys.login_token
WHERE principal_id > 0;

revert;

It throws error at the first line with the message below

Cannot execute as the server principal because the principal
"ComputerName\Windows Group Name" does not exist, this type of
principal cannot be impersonated, or you do not have permission.

I confirmed the windows group and the login exists.
I search the error message and it seems people who faced the same error are not run into this while using windows group login.
Is it some native limitation or there is something I missed?


texts: Why can vectorization reduce the use of virtual functions?

texts:
Vectorized Query can be very helpful to get a high-performance analytical systems.

I know that vectorization can execute all operations on blocks of values (SIMD instructions) instead of interpreting a query expression tuple at a time.
But how can I understand ,the vectorization can reduce the overhead of virtual functions?

This question came to me when I read a paper from TU Munich:
Data Blocks: Hybrid OLTP and OLAP on Compressed Storage using both Vectorization and Compilation

  The jump in query evaluation efficiency is typically achieved by using vectorized execution where, instead of interpreting query expressions tuple at a time, all
  operations are executed on blocks of values. The effect is reduced interpretation overhead because virtual functions implementing block-wise operations handle thousands of tuples per function call, and the loop over the block inside these function implementations benefits from many loop-driven compiler optimizations including the automatic generation
  of SIMD instructions.

Such formulation could also be found in other DBMS papers, because vectorization is a popular way.

This question is related with general DBMS and the Paper from TUM is related with HyPer, which is a not so "famous" DBMS. I would be glad to receive any kinds of examples, explanations. If I have wrong with formulation, just feel free to edit it.


texts:
I have a table which I am trying to return only records of rendition_type Interactive and no other associated rendition_type. If a records has more than one rendition_type, I need to exclude it from the list.

DOC_ID    RENDITION_ID    RENDITION_TYPE
001       001             Interactive
001       002             Web PDF
002       003             Interactive
003       004             Interactive
003       005             Print PDF
004       006             Interactive
005       007             Interactive
005       008             Web PDF

I'm looking for a query that will return only rows where document_ID has Rendition_type type of only 1 and that type is Interactive.


texts: NVARCHAR in SQLServer to VARCHAR in AWS Redshift

texts: I am trying to move a table from SQL server to AWS Redshift. While trying to move NVARCHAR fields, I read that Redshift converts NVARCHAR to VARCHAR internally. So, a NVARCHAR(100) in SQL Server will be equivalent to VARCHAR(100) or Varchar(200) in Redshift ? If I want to define the NVARCHAR fields from SQLServer directly as VARCHAR in Redshift, do I need to double the length of fields?


texts: SSRS: textbox format hide complete row (group header)

texts:
I get a weird behavior formating a textbox in a group header. If the textbox is set to default shows the data, but so soon how set the format to percentage (or anything else) the complete row (group header) won't show.

With the textbox in default:

Set to percentage:

And will hide the complete row:


texts: Oracle 12cR1 - Duplicate Database With No Resetlogs

texts:
Database version: Oracle 12cR1
Operating system: Oracle Linux 7.x
ASM: yes

Duplicate script:

cat duplicatetodisaster.sh

ORACLE_UNQNAME=xxx
ORACLE_SID=xxx
ORACLE_HOME=xxx

rman target sys/***@PROD auxiliary sys/***@DRC <<EOF
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby1 type disk;
allocate auxiliary channel stby2 type disk;
allocate auxiliary channel stby3 type disk;
allocate auxiliary channel stby4 type disk;
duplicate target database to 'db_name' noopen from active database;
}
exit;
EOF

We are trying to create a disaster database with the log shipping method. Basic logic; production database will be duplicated and a disaster database will be created that continues in mount mode. Then, archivelogs transferred from production database will be recovered by disaster database and their continuity will be ensured.
As you can see in the rman script I shared; I tried to prevent the database from doing resetlogs by adding the noopen parameter, but still, after the restore and recover successfully finished, the controlfile created and resetlogs the database. Because of this, all incarnations in the database, DBID parameter and everything to keep it in sync with the production database are broken. Based on this, the transferred archivelogs cannot be recovered and continuity cannot be maintained. How can I prevent this situation?
Below, I share where resetlogs does exactly from the alert log:
Completed: CREATE CONTROLFILE REUSE SET DATABASE "DB_NAME" RESETLOGS ARCHIVELOG
  MAXLOGFILES    192
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES    32
  MAXLOGHISTORY     4382
 LOGFILE
  GROUP  11  SIZE 1536 M ,
  GROUP  12  SIZE 1536 M ,
  GROUP  13  SIZE 1536 M
 DATAFILE
  '+DATA/.../.../system...'
 CHARACTER SET WE8ISO8859P9

Another issue; there are about 250 datafiles, why did this happen for a single datafile...
Best regards,


texts: Consume data from other database motor from stored procedure in Oracle 12c

texts:
I have a project in which you must develop services in Oracle OSB which will call a procedure stored in Oracle 12c. The issue is that I also need data from an Informix database to build those services.
The initial idea was to make a DBlink from Oracle to informix, but it is very expensive, because it needs licensing. I need alternatives to this problem.

Regards!


texts: Migrate data from multiple disparate databases into 1

texts:
I have a SQL Server instance with 5 different databases.  For a few reasons, we would like to combine these into just 1 database, separating out each database into it's own schema.  I've built the schema/tables into the 'master' database already, but i'm having trouble building a good way to import the data from the old databases into the new master one.  something like:

insert into [masterDB].[oldDBSchema].[tableName]
select * from [oldDB].dbo.[tableName]

does not work, because of this error:

  An explicit value for the identity column in table
  'cobraclientdata.cconfigd.adjudicationbutton' can only be specified
  when a column list is used and IDENTITY_INSERT is ON.

now i can set IDENTITY_INSERT to ON easily enough, but i'd rather not specify the columns if i don't have to because it would be a PITA to do that for every table.  These tables have exactly the same structure, is there a quick way to do this (I'm trying to build a script that i can run this 'update' on several servers), or am i stuck writing out the columns for each table, or doing an import/export data explicitly?


texts: Does NTILE work over floating point numbers?

texts:
I am trying to use NTILE on my data using a float data type in my PARTITION BY clause.  Please excuse the contrived example, but I think the best way to illustrate my question and confusion is through this code:

CREATE TABLE Test
(
    Cash float NOT NULL,
    Number int NOT NULL
)
GO

INSERT INTO TEST VALUES(1.05,1);
INSERT INTO TEST VALUES(1.368,1);
INSERT INTO TEST VALUES(0.775,1);
INSERT INTO TEST VALUES(1.699,1);
INSERT INTO TEST VALUES(1.599,1);
INSERT INTO TEST VALUES(0.80,1);
INSERT INTO TEST VALUES(0.80,1);
INSERT INTO TEST VALUES(0.994,1);
INSERT INTO TEST VALUES(0.848,1);
INSERT INTO TEST VALUES(0.675,1);
INSERT INTO TEST VALUES(0.575,1);
INSERT INTO TEST VALUES(12.998,1);
INSERT INTO TEST VALUES(1.999,1);
INSERT INTO TEST VALUES(0.65,1);
INSERT INTO TEST VALUES(0.80,1);
INSERT INTO TEST VALUES(2.60,1);

SELECT CASH,
    NTILE(3) OVER (PARTITION BY Cash ORDER BY Cash) AS Trio
    INTO #Test
FROM Test

I would have expected the groupings to be something like this:

0.575   1
0.65    1
0.675   1
0.775   1
0.8     1
0.8     2
0.8     2
0.848   2
0.994   2
1.05    2
1.368   3
1.599   3
1.699   3
1.999   3
2.6     3
12.998  3

But instead, The result set looks like this:

CASH    Trio
0.575   1
0.65    1
0.675   1
0.775   1
0.8     1
0.8     2
0.8     3
0.848   1
0.994   1
1.05    1
1.368   1
1.599   1
1.699   1
1.999   1
2.6     1
12.998  1

And the range groupings look really strange when max and min are applied by group

SELECT
    MAX(CASH), MIN(CASH), Trio
FROM #Test
GROUP BY Trio

MAX     MIN     TRIO
12.998  0.575   1
0.8     0.8     2
0.8     0.8     3

Can anyone explain what is going on here?


texts:
I have a 400k XML document trying to insert into an 86-column SQL Server 2008 table.
It takes about 35 minutes to get that XML into a temp table using this code:

with XmlNamespaces ( 'urn:xpaykj-report-xml-1.0' as xkj)
    Select
     xItem.value('declare namespace xkj="urn:xpaykj-report-xml-1.0"; (xkj:prop[@idx=1])   [1]   ','Date')  as [InputDate]
    -- and then 85 other columns --
Into #loadtemp
    From @xmldoc.nodes('xkj:output-data/xkj:childs/xkj:child[@name="output"]/xkj:childs/xkj:child[@name="lvla"]/xkj:childs/xkj:child[@name="lvlb"]/xkj:childs/xkj:child[@name="lvlc"]/xkj:properties') as x(xItem)

I need to optimize... Something. But I am not sure what. Can anyone help me with ideas?

Thanks.


texts: How to join two table without getting duplicate from both left & right table in Oracle

texts:
Tables Details:
CREATE TABLE Test2 (
ID INT,
Value INT,
other INT);
CREATE TABLE Test1 (
ID INT,
TYPE INT,
other INT);
INSERT INTO Test2 VALUES (123456, 5, 12);
INSERT INTO Test2 VALUES (123456, 10, 17);
INSERT INTO Test1 VALUES (123456, 00, 2);
INSERT INTO Test1 VALUES (123456, 01, 6);
INSERT INTO Test1 VALUES (123456, 02, 4);
INSERT INTO Test1 VALUES (987654, 00, 7);
INSERT INTO Test1 VALUES (987654, 01, 8);
INSERT INTO Test1 VALUES (456789, 00, 6);
INSERT INTO Test1 VALUES (456789, 01, 16);
This is the Query i m using to avoid duplicate from table Test
SELECT DISTINCT t1.ID, t1.TYPE, t1.other, t2.value
FROM Test1 t1 INNER JOIN Test2 t2 ON t1.ID = t2.ID
GROUP BY t1.ID, t1.TYPE, t1.other, t2.value
ORDER BY t1.ID ASC;
Query Result:
ID  TYPE    other   value
123456  0   2   5
123456  0   2   10
123456  1   6   5
123456  1   6   10
123456  2   4   5
123456  2   4   10
Description:
Expected Result is , when i m fetching match records from tables. Should get  the records from left table(Test1) without duplicate records and avoid duplicate record from right table (Test2).
Reference Check Pic
Expected Query Result are,
23456   0   2   5
123456  1   6   10
123456  2   4
SQL Fiddle Link
http://sqlfiddle.com/#!9/3cd8a0/26
Criteria When there is ID match, should get  matched records from left table (Test1) without duplicate and from right table (Test2) without duplicate.


texts: pg_restore showing errors when specifying schema when backing up with pg_dump

texts:
I have created two different dump files one without specifying the schema, another specifying the public schema
without specifying the public schema'
pg_dump -h IP_ADDRESS -p 5432 -U my_user -Fc my_db  > my_db_allschema.dump

and the pg_dump statement when specifying the public schema
pg_dump -h IP_ADDRESS -p 5432 -U my_user -Fc my_db -n public > my_db_publicschema.dump

When using pg_restore to restore the dump files, I get errors with the dump file that was generated when specifying the public schema.
postgres@debian:~$ pg_restore -h localhost -p 5432  -U my_user -d my_db my_db_publicschemaonly.dump
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 8; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;

pg_restore: from TOC entry 212; 1259 18102 TABLE abandoned_url real_estate
pg_restore: error: could not execute query: ERROR:  function public.gen_random_uuid() does not exist
LINE 2:     id uuid DEFAULT public.gen_random_uuid() NOT NULL
                            ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
Command was: CREATE TABLE public.abandoned_url (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL
);

Looking at this statement that throws an error
CREATE TABLE public.abandoned_url (
        id uuid DEFAULT public.gen_random_uuid() NOT NULL
    );

The reason it's throwing an error is because pg_dump has put public before gen_random_uuid(), the following statement works fine when removing public before gen_random_uuid()
CREATE TABLE public.abandoned_url (
        id uuid DEFAULT gen_random_uuid() NOT NULL
    );

Am I creating the dump file incorrectly? Could this be a bug in pg_dump?


texts: Is there a way to set different destination order with mysql-router?

texts:
I have innodb cluster setup with 3 mysql instances. Each instance is on different server and each server has mysql-router also.
I would like each mysql-router on each server to try to connect to the mysql instance  on the same server. Is this possible?
Currently all mysql-router will check the "destinations=metadata-cache://mycluster/default?role=PRIMARY" option and try to connect to the top server. Is there a way so each mysql-router trys to first try to connect to different instances? (the instance on the same server as mysql-router)


texts: SQL - Update table for a unique ID with multiple entries if one entry has variable in question

texts:
I'm trying to update a table to fill in a variable to each row in the table if one row for the unique ID shows that variable.

I was trying to do the following, but it's giving me an error.  Appreciate the help!

update patient_encounter
Set user_defined4 = 'DTC'
where person_id = (SELECT person_id from patient_encounter where  mrkt_plan_id = '489681B4-9D94-413B-95A0-C50D5964F340')

I want to fill in "DTC" into the user_defined4 field for any person_id that shows the particular mrkt_plan_id listed (which correlates to DTC) for any of their entries.  Each person_id has multiple encounters and I want them all to share the same market plan once it is listed as DTC once.

The error says Subquery returned more than 1 value.
Thanks!


texts: Why does this PostGIS query take so long to run?

texts:
I am new to PostGIS and SQL in general. I am running a query on polygons which, I hope, only selects distinct geometries and then repair (ST_MakeValid()) those distinct geometries. Thereby removing duplicate geometries in the process of fixing invalid geometries. However, the query takes really long to run (days). This is the SQL statement:

CREATE TABLE schema.table_geomfix AS
SELECT gid,id, ST_MakeValid(geom) as geom
FROM schema.table_polygons
WHERE geom IN (SELECT DISTINCT geom FROM schema.table_polygons);

Running the two select statements separately is quick. About 1 minute for the makevalid and 15 seconds on the distinct statement.

Why does it take so long to run the combined statement?
Any advice for improving the query in order to make it faster?

NOTE: There is an index on the geom column.


texts: New Server Query AD is giving permission error

texts:
We replaced a physical server with virtual, I copied all the logins, and linked servers from the old server and restored all the databases from the old server to the new.  Everything works fine except for a query that uses a linked server to query Active directory.  I get a permission error:

The OLE DB provider "ADSDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation.

If I go to the linked server and test connection it succeeds.  The linked server is set to connect to AD using SQLService.  The only think that changed is the physical box we were running on, so it has to be something on the sql side that is not configured correctly?


texts: Create Field With Default Value Of Different Field With No Data Loss

texts:
I have a datetime2 field that I need to be a datetime field.  If I try to run an Alter Table statement my SSMS session freezes and I have to force reboot with no change made, if I try to alter my design view of the table I get an error about needing to drop/re-create the table.

Which leads me to my question, if I have a field that is datetime2 if I Add Column helper1 datetime  how could I set the default value of this column to dt2 which is a datetime2 data type?


texts: ORA-1110, ORA-1114, OSD-4002, O/S-Error: (OS 33) The Process Cannot Access The File

texts:
Hi All,

We have a problem today wherein in Oracle we are trying to execute a package (every morning) and this is the only time we encountered the error (ORA-0114).

Any ideas how to fix this? We have already extended the SYSTEM and TEMP tablespace space.

And the crazy thing is, we tried yesterday and recompiled the package (we are using TOAD) and it worked. But today, it didn't work again even though it was already recompiled (same error).

BTW, line 273 was the part INSERT INTO SELECT statement.

I tried to execute the SELECT statement on the query window, and it also gets the same error.

Thanks !


texts: Will creating an index improve performance on a full scan

texts:
Will adding an index on a Large data warehouse table (8Million rows, 200+ column) make any difference to the query time if all I am doing is selecting all rows from the table ?
Thanks


texts: Use of Shrink after rebuilding Indexes in sql server 2005

texts: I need to create a weekly database plan for my company's databases (5.8Gb), now I want to rebuild the Indexes, and then want to trigger a Shrink operation. Now I know Shrink creates  Index defragmentation, so can anyone guide me how to create this maintenance plan... Your suggestion will be very much valuable to me...Please


texts:
We have a SQL Server 2014 with 140 user databases, each of which have approx. 180 tables.
A maintenance plan was scheduled for once a week but it was discovered that the Check Database Integrity (DBCC CheckDB) job was taking too long. The schedule was disabled, the job stopped and the process killed.

However, we are now seeing DBCC TABLE CHECK command running on multiple threads. The MAXDOP configuration setting has been reduced to give other processes a chance, but the table check is still using as many threads as it can get hold of.

The question is: How to kill this process?

BTW - the server has been rebooted(twice).


texts: Is it possible to bulk insert data into a table that has columns encrypted with Always Encrypted?

texts:
In SSMS we are attempting to bulk insert from a csv file into a table that has a column encrypted using SQL Server 2016's Always Encrypted feature.

This is the command we're using:

INSERT INTO membersE
SELECT *
FROM OPENROWSET(
    BULK 'c:\members.csv',
    FORMATFILE = 'c:\membersEFormat.xml',
    FIRSTROW = 2
    ) m

This returns the typical error you get when attempting to insert into an encrypted column:

  Msg 206, Level 16, State 2, Line 6
  Operand type clash: varbinary is incompatible with varchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'DATABASE') collation_name = 'Latin1_General_BIN2'

We understand that you can't insert into an encrypted column via SSMS and that you need to use a .NET 4.6.1+ client, but we'd like to know if bulk insert operations are not possible as well?

SAMPLE CODE THAT WORKED FOR ME
(to satisfy Windows10's request)

        SqlCommand cmd;
        SqlConnection conn;
        SqlBulkCopy copy;
        SqlDataAdapter da;
        DataTable dt;

        using (conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Database"].ConnectionString))
        {
            conn.Open();

            using (cmd = new SqlCommand("SELECT * FROM members", conn))
            using (copy = new SqlBulkCopy(conn))
            using (da = new SqlDataAdapter(cmd))
            using (dt = new DataTable())
            {
                cmd.CommandTimeout = 600;

                da.Fill(dt);

                cmd.CommandText = "TRUNCATE TABLE membersE";
                cmd.ExecuteNonQuery();

                copy.DestinationTableName = "membersE";
                copy.WriteToServer(dt);
            }
        }


texts: How do I set up a Derived Column Transformation to get row number in SSIS 2014?

texts: I am trying to import a ragged right flat file. I am wanting to import the file as one column and used Derive Column Transform to add a row number to each row. I would like to end up with a column called RowNum and a column called EntireRow. How do I do this?


texts:
Although I was able to find the definition of predicates online at MSDN:

Is an expression that evaluates to
TRUE, FALSE, or UNKNOWN. Predicates
are used in the search condition of
WHERE clauses and HAVING clauses, the
join conditions of FROM clauses, and
other constructs where a Boolean value
is required.

I have been unable to find the definition of what a residual predicate is. What is a residual predicate?


texts: Performing a huge amount of individual INSERTs occasionally causes a lag that triggers the timeout

texts:
I have some code that is using entity framework to perform a large number of INSERTs in succession. SQL profiler shows a SELECT, then another SELECT, then an INSERT for each one due to it having to read some data first. These occur very quickly so I can see my console logging program scrolling like crazy.

Every so often, it will completely stop for a few seconds, then it will resume again. There is no pattern to it. I've had it do all 450,000 inserts without any issues, but sometimes, I find that it pauses for long enough to trip the timeout on the sql command.

I'm running it through the profiler and examining the results, and I can see sometimes that the duration is very high for what is a very small insert query that should be instant.

Is there a reason why it pauses every so often? Could it be something like garbage collection or something internal?

I don't really want to increase the timeout as a finger in your ears approach to solving the issue. And I can't turn the inserts into a BULK operation very easily as they are all distinct calls to a service that logs every time it is called. I am wondering if there's something obvious that may be the culprit. Any thoughts/speculation on this are welcome.


texts:
I have three tables, representing two entities and a connection relationship. Each of the entity relations has an artificial surrogate key used for foreign key constraints in place of natural keys.

CREATE TABLE Person (
person_id SERIAL PRIMARY KEY,
username VARCHAR NOT NULL UNIQUE
);

CREATE TABLE Group (
group_id SERIAL PRIMARY KEY,
group_name VARCHAR NOT NULL UNIQUE
);

CREATE TABLE GroupMember (
person_id INTEGER REFERENCES Person,
group_id INTEGER REFERENCES Group,
PRIMARY KEY (person_id, group_id)
);

I would like to insert multiple entries into the relationship relation GroupMember, but using the natural keys of the associated entities. Something along the lines of:

INSERT INTO GroupMember (person_id,group_id)
SELECT person_id,group_id
FROM Person P, Group G, (
('alice','girls'),
('bob','boys'),
('alice','coolkids'),
('bob','coolkids')
) AS X
WHERE P.username = X.1 AND G.group_name = X.2;

Obviously the above syntax isn't valid, but hopefully conveys the idea. Is there a way to do this in PostgreSQL?


texts: SQL Delete rows where a dataname will equal mulitple variables

texts:
Researching similar common queries have really helped but I am still none the wiser as to my issue. To note that the database I intend to use this on contains around 1000 rows (same 2 columns listed below) of which 300-350 will need deleting depending on their ID.

E.g. I think the query should be something like (table = mytable):

Title ID
One   1
Two   2
Three 3
Four  4

delete from mytable
where ID = 1 or 2 or 3;

Obviously when it comes to deleting 300 odd rows, putting an operator inbetween every variable probably isn't the best way of this (nor probably correct anyway) hence why I am asking this question!

Any advice would be much appreciated, thanks!