texts: Strange behavior when combining CONVERT(CHAR()) with RIGHT()

texts:
When I was trying to create a report that included a decimal-implied number column that needs to be a fixed length of six characters, I chose to go the route of converting to char, replacing the decimal with '', and then using RIGHT() to pad zeros on the left hand side, turning 15.52 into 001552, 1.11 into 000111, etc...  My code looked like:

  SELECT RIGHT( '000000' + REPLACE(CONVERT(CHAR(8), ISNULL([TotalModePremium], 0)), '.', ''), 6) AS TotalModePremium
  FROM <Database>.<Schema>.<Table>

This created unexpected behavior where it would clip off my results, making a max length of 3 characters. 15.52 would become 552. However, rows with 0 would become 00--only two characters. When I would change the CHAR(8) to CHAR(7), 15.52 would become 1552, but 0 would become 000.

I then change the CHAR(n) to VARCHAR(n), and it now works as desired. 15.52 becomes 001552, 0 becomes 000000, etc...

My question is, why would increasing or decreasing the value in CHAR(n) create those sorts of results when combined with the RIGHT() function to pad zeros, but the use of VARCHAR() eliminates that behavior?


texts: Rows already DELETEd on MySQL slave; How to DELETE on master?

texts:
I have a handful of rows that exist in my MySQL master but not in my slave.
Replication is running ok and these rows are unlikely to ever be referenced as it is a store-and-forget type of table. However, I want to delete these orphaned rows in order to have everything in sync and prevent any future problems.
If I issue a DELETE statement on the master to remove the offending rows, the slave obviously won't be able to replicate it as those rows don't exist at the slave.
What are the implications of doing this?

Will this break replication or will the slave simply ignore the problem and carry on?
What is the recommended way to resolve this?

Background
A DELETE statement was interrupted on the master. Replication stopped as a result. I believe the same DELETE statement was then executed on the slave before restarting replication.  However, it ran to completion on the slave, resulting in more rows being deleted there.


texts: Mysql Adding date/time stamp to file produced, or other method of automated file naming to keep resulting files unique

texts:
SELECT
'"Part #"',
'"Manu"',
'"Qty"',
'"partdesc"',
'"Rohs status"'
UNION all
(
SELECT partno, manuf, qtyleft, partdesc, stfifo.ROHScompliance
    FROM stmaster, stfifo
    WHERE stmaster.internal = stfifo.internal AND stfifo.warehouse = 'MAIN'

AND manuf != 'aaaa'
AND manuf <>'bbbb'
AND manuf <>'cccc'

AND stmaster.partno <>'yyyyyyyyy'
AND stmaster.partno <>'xxxxxxxx'
AND QTY >0
GROUP BY partno, manuf
ORDER BY partno ASC
INTO OUTFILE
    'D:/Reporting/abc - 19.03.15.xls'
    FIELDS TERMINATED BY '  ' ENCLOSED BY '' LINES TERMINATED BY ''

How do I make it so that the resulting file includes today's date as determined by a NOW() function? CONCAT() functions seem useless as the file is within a UNION all() function. At the moment I cannot run this query more than once without deleting the .xls file first. Any other workarounds are greatly welcomed. Thanks in advance


texts: Disable/Remove Table Index for INSERT Performance

texts:
I am doing a database maintenance where I have to add new fields to a table with over 10GB of data about (20 million rows). To solve that I renamed tableA to be old_tableA and created a new table called new_tableA and added new columns for this table as the this post has suggested.

According to this post, disabling table indexes and re-enabling them later would improve performance for INSERT transactions.

If I have old_tableA and new_tableA, where I would want to transfer all the rows from old_tableA to new_tableA, should I disable/remove the INDEXes in both of these tables?

I am using:

MySQL 5.6
InnoDB


texts:
I installed SQL Server, I deleted my windows user account, now I'm trying to access SQL Server using administrator account but it could not connect. It throws error like this

  The database TEST is not accessible. (ObjectExplorer)


texts: Is it normal for tempdb TPS to be higher than all other databases combined?

texts:
I'm investigating some performance issues on a production DB here, and I've noticed a slightly odd thing that I'm not sure is weird or not.

If I run a quick Performance Monitor trace for the SQLServer:Transactions/sec counter for each DB on the server, I'm seeing that tempdb accounts for a huge majority of the transactions - as they're transactions against 'real' tables, shouldn't there be some correlation with other database stats too?

Database  Average
-----------------
tempdb    2,127
master      115
MyDb1        63
OtherDbs      5

TOTAL     2,700

I have a query I was given to show queries active on the tempdb, and that seems to show queries that aren't temp tables etc, but 'normal' queries on other databases.

SELECT
es.host_name,
es.login_name,
es.program_name,
st.dbid as QueryExecContextDBID,
DB_NAME(st.dbid) as QueryExecContextDBNAME,
st.objectid as ModuleObjectId,
SUBSTRING
(
    st.text,
    (er.statement_start_offset / 2) + 1,
    (
        (
            CASE
                WHEN er.statement_end_offset = -1 THEN
                    LEN(CONVERT(nvarchar(max),st.text)) * 2
                ELSE
                    er.statement_end_offset
            END
        ) - (er.statement_start_offset / 2)
    )
) as Query_Text,
tsu.session_id,
tsu.request_id,
tsu.exec_context_id,
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,
(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,
er.start_time,
er.command,
er.open_transaction_count,
er.percent_complete,
er.estimated_completion_time,
er.cpu_time,
er.total_elapsed_time,
er.reads,
er.writes,
er.logical_reads,
er.granted_query_memory
FROM sys.dm_db_task_space_usage tsu
inner join sys.dm_exec_requests er ON (tsu.session_id = er.session_id and tsu.request_id = er.request_id)
inner join sys.dm_exec_sessions es ON (tsu.session_id = es.session_id)
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
-- WHERE (tsu.internal_objects_alloc_page_count+tsu.user_objects_alloc_page_count) > 0
WHERE program_name <> 'Microsoft SQL Server Management Studio - Query'
ORDER BY
    (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) +
        (tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) DESC


texts: SELECTing data based on a specific time frame

texts:
I'm using PostgreSQL. I have a table which stores data related to some orders. It has four to five columns, including:

cust_code char varying (20),
sugg_order integer,
ordered_qty integer,
date date

Now I want to find all the rows which occur within a specific time frame. Let us assume that this time frame starts on 1st of April every year and ends on 31st of March next year (like 2017-04-01 to 2018-03-31 and 2018-04-01 to 2019-03-31 and so on).

Below are the examples of the output which I want from the query:

If I run this query on 2018-08-01 then I need all the data from 2018-04-01 to 2018-08-01.
If I run this query on 2019-03-01 then I need all the data from
2018-04-01 to 2019-03-01.
If I run this query on 2019-05-01 then I need all the data from 2019-04-01 to 2019-05-01

In other words, I need all data between today (the day I run the query) and the latest (most recent) 1st of April before today.

I need a query which will work for any year and I cannot get the start date of the time frame from outside.


texts: Join two tables in MYSQL and avoid duplicate values

texts:
I created two tables using MYSQL, tblorder and tblitem.
CREATE TABLE `tblorder` (
`orderId` int NOT NULL,
`orderName` varchar(45) NOT NULL,
PRIMARY KEY (`orderId`)

CREATE TABLE `tblitem` (
`itemId` int NOT NULL,
`itemName` varchar(45) NOT NULL,
`itemUnit` varchar(5) NOT NULL,
`itemRate` double NOT NULL,
`orderRef` int NOT NULL,
PRIMARY KEY (`itemId`),
KEY `fk1_idx` (`orderRef`),
CONSTRAINT `fk1` FOREIGN KEY (`orderRef`) REFERENCES `tblorder` (`orderId`)

I tried to join the two tables using query below
SELECT orderId,orderName, itemName, itemUnit,itemRate
FROM tblitem
INNER JOIN  tblorder on tblorder.orderId = tblitem.orderRef

Now result show like Image-01

How to remove duplicate values in orderId ,OrderName columns in result table? Thanks to help me to solve this problem.
After joining tables is it possible to get output like below?

Output from new Query


texts: Direct the OUTPUT of an UPDATE statement to a local variable

texts:
I would like to do this :

DECLARE @Id INT;

UPDATE Logins
SET    SomeField = 'some value'
OUTPUT @Id = Id
WHERE  EmailAddress = @EmailAddress -- this is a parameter of the sproc

Is this even possible? I know I can declare a local table variable and direct the output there but I would prefer to skip it if possible


texts: Concurrency Issues when multiple users uses Portal (MySQL and PHP)

texts:
We have set up a portal using Yii Framework in Apache, PHP and MySQL. In the portal, there is a feature to export the data in dbf format, the extract takes around 7 minutes, but in those 7 minutes no other user can do anything in portal, not even login.

All tables are in MyISAM Engine.

I cannot provide you the link as its in our internal server.

Server is 4core Xeon Processor with 32 GB Ram.


texts: Why can't I read Oracles CLOB columns via SQL-SERVER 2008 linked server?

texts:
I want to access data in an Oracle 11g database from SQL-Server 2008

I set up a linked server and when I execute

select * from [Link_server_name]..Oracle_schema.Oracle_table

and Oracle_table contains Number and varchar2 columns, all works as excepted.

But when the Oracle_table contains a CLOB column, I get the following error:

Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Unspecified error' zurückgeben.

Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Oracle error occurred, but error message could not be retrieved from Oracle.' zurückgeben.

Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Data type is not supported.' zurückgeben.

Msg 7306, Level 16, State 2, Line 1

Die '"MCCAPP"."DOGGRUPPEN"'-Tabelle vom OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' kann nicht geöffnet werden.

Using OPENQUERY

SELECT * FROM OPENQUERY([L_V407SR8T], 'Select CLOB_COLUMN from Oracle_table'  )

I get

Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Oracle error occurred, but error message could not be retrieved from Oracle.' zurückgeben.
Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Data type is not supported.' zurückgeben.

Please excuse German error messages.

My question: Is there any way to read CLOB columns via linked Servers?

EDIT:

SQL Server Import and Export Wizard
seems to depend on OLE DB too and
sucks on the same tables
I'm asking myself, if the problem
depends on the character sets uses,
but I can't change them
As practical work around, I use some
PowerShell scripts to get at the
data, but


texts: Does SQL Server 2016 support foreign keys in an in-memory table from a disk bound table

texts:
If using an in-memory table in SQL Server 2016, can I define a column in that table as a foreign key from a non in-memory (or disk bound) table? I know that the foreign key does need to be the primary key of the referenced table.

I'm just wondering about the limitations and reliability of in-memory tables and how well they work with disk bound tables in SQL Server 2016.


texts: How can a blocking_session_id be minus 2?

texts: I have a case where a query I'm running is taking a long time.  When I checked sys.dm_exec_requests the blocking_session_id is a negative value, -2 to be specific.  The wait is a LCK_M_X and the blocked command is a DELETE statement.  To the best of my knowledge nothing else is touching that table at the moment.  In fact the only active requests are the blocked connection and the query checking sys.dm_exec_requests.  When I look at sys.dm_tran_session_transactions the blocked session is the only one showing up.


texts: How to create a Job for Merge Replication?

texts: I want to create a step in a Job for Merge Replication. I basically want to know which command should I write so that a particular Subscription starts synchronizing?


texts: How can I find duplicate key violations in a different collation?

texts:
I have a MySQL table that has the collation for one of its varchar columns in the primary key set to utf8_general_ci. I want to change the collation to utf8_unicode_ci so it stops treating "gmail.com" and "gmàil.com" like they're the same item. I tried running this:

alter table my_table change character set utf8 collate utf8_unicode_ci;

The alter fails with a duplicate key violation on one of the items.

  Error 1062 (23000): Duplicate entry 2013-07-15-119-comcast.net for key PRIMARY

I tried selecting the items out of the table that have date equal to 2013-07-15 and source_id equal to 119, but eyeballing the results I don't see two entries for "comcast.net" that might be causing the problem.

Can I construct a query into this table to determine which two rows are different under utf8_general_ci but the same under utf8_unicode_ci?


texts:
I have this table:

CREATE  TABLE `sold` (
`part_ID` INT(11) NOT NULL ,
`date` DATE NOT NULL ,
 PRIMARY KEY (`part_ID`, `date`) ,
 FOREIGN KEY (`part_ID` )
 REFERENCES `part` (`part_ID` );

This table represent parts sold each day, constraint says number of sales should be at least 25 and at most 100.

I think it should start with something like this:

CHECK ( NOT EXISTS ...


texts: Mysql: does full-text cover normal index?

texts:
I have seen this question everywhere but the answer is just explaining some technical stuff without a clear verdict so I have to make my question so narrow that no one mistakes it with another question:

I have a table with millions of records. This table has a string field and I need to search it normally and full-text. So of course I need it to be fulltext but does it make it faster for my normal search if I add "index" as well?

Another word: Does full-text cover simple index?


texts: Sql Max value from multiple columns in Computed Column

texts:
In this article solution 1, it talks about finding the maximum value from many columns. I would like to conduct this in a computed/persisted column. How would I do this?
https://www.mssqltips.com/sqlservertip/4067/find-max-value-from-multiple-columns-in-a-sql-server-table/
create table dbo.TestAmount
(
    Amount1 int,
    Amount2 int,
    Amount3 int,
    MaxValuedata as (select MAX(MaxAmount) FROM (VALUES (Amount1),(Amount2),(Amount3)) AS MaxAmount(LastAmount))
)

May have 10 values in the future, trying to prevent long case statement.


texts: How can I eliminate this costly index seek operation from my query?

texts:
I have two tables in Sql Server 2008 that look something like this:

Transaction_Details:
    Dataset,
    Department,
    Charge_ID,
    Transaction_Type,
    dos_month,
    post_month,
    amount

Charge_Summary
    Dataset,
    Department,
    Charge_ID,
    dos_month,
    Ins1_Category

The Output I'm looking for would look something like this:
    Dataset,
    Department,
    Ins1_Category,
    dos_month,
    Post_Month,
    Total_Payments,
    Total_Charges,
    Previous_Balance -- Defined as total amount for all matching transactions with a post_month previous to the current month

The SQL I'm using below gives the expected output, but there is one index seek in the execution plan taking up over 90% of the cost.  This is in a datawarehouse operation, so there is no other activity on the server aside from this query, no other reads or writes to worry about.  The execution time of ~30 minutes is acceptable for what it's being used for, but I'd still like some advice if there's a better way to do this.

Link to execution plan on pastebin

SELECT
    t1.Dataset,
    t1.Department,
    c1.Ins1_Category,
    t1.dos_month,
    t1.post_month,
    SUM(case
            when t1.transaction_type = 'payment' THEN t1.amount
            ELSE 0
        END
        ) AS total_payments,
    SUM(case
            when t1.transaction_type = 'adjustment' THEN t1.amount
            ELSE 0
        END
        ) AS total_adjustments,
    SUM(case
            when t1.transaction_type = 'charge' THEN t1.amount
            ELSE 0
        END
        ) AS total_charges,
    (
        SELECT
            SUM(t2.amount)
        FROM
            Transaction_Details t2
        LEFT JOIN
            Charge_Summary c2
        ON
            t2.Dataset = c2.Dataset AND
            t2.Charge_ID = c2.Charge_ID
        WHERE
            t1.Dataset = t2.Dataset AND
            t1.Department = t2.Department AND
            t1.dos_month = t2.dos_month AND
            t1.post_month > t2.post_month AND
            t2.Charge_ID IS NOT NULL AND
            c2.Ins1_Category = c1.Ins1_Category
    ) as previous_balance
FROM
    Transaction_Details t1
LEFT JOIN
    Charge_Summary c1
ON
    t1.Dataset = c1.Dataset AND
    t1.Charge_ID = c1.Charge_ID
WHERE
    t1.Charge_ID is not null
GROUP BY
    t1.Dataset,
    t1.Department,
    c1.Ins1_Category,
    t1.dos_month,
    t1.post_month


texts: How to collapse into a single row from multiple rows

texts:
Please refer to the attached screenshot for the requirement. I have to collapse rows into a single row with latest test_date in one column and second_latest_date in an other column and latest order_number.

Please share the tips to create a SQL Server query that can (a) transform data from the Source table to (b) data as shown in the Target table.

Data sample for the Source table:

Data sample for the Target table:


texts: How to speed up query with exists clause?

texts:
I have requirement to identify customers using multiple emails for the same billing address.  There are 414375 records.  I let the query run for 20 minutes and it still did not return.  How do I speed it up?
select so.customer_email
from sales_order so
inner join sales_order_address soab on soab.parent_id = so.entity_id and soab.address_type ='billing'
where exists
(
    select 1 FROM sales_order so2
    inner join sales_order_address soab2 on soab2.parent_id = so2.entity_id and soab2.address_type ='billing'
    where so2.customer_email <> so.customer_email
    and soab.firstname = soab2.firstname
    and soab.lastname = soab2.lastname
    and soab.street = soab2.street
    and soab.city = soab2.city
    and soab.region = soab2.region
)


texts: How I can monitor SQL Server 2012 databases to determine the security privileges that are called by different processes

texts: Is there any tool that can identify the SQL privileges or roles that are being called on query by query basis. We need to know which ones are being used at each time on a Microsoft SQL Server 2012.


texts: Buffer Pool and Multiple instances of SQL Server

texts:
If you have multiple instances of SQL Server running on the same windows server system,

a) Does buffer pool is shared amount multiple instances?

b) What is the effect of running DBCC DROPCLEANBUFFER OR DBCC FREEPROCCACHE? Would that only affect the SQL Server instance you ran the command on or all the instance(s) on the windows box would flush SQL server cache?


texts: SQL Server Configuration Manager changes effected via Central Management Server

texts:
When using Central Management Server (CMS), under Central Management Server Actions, SQL Server Configuration Manager (SSCM) is an option.

When launched, the GUI reflects the information of the CMS. Apart from what the GUI shows, I'm hopeful that changes made will affect all CMS managed instances.

Can anyone confirm whether changes made here will effect all the managed CMS instances? If possible, this could save much time if all the actions of SSCM can be performed against all the CMS-registered instances.


texts:
Master version: 5.5.13-1
Slave version: 5.5.14-1
Binary log format: MIXED

My Slave database (~ 40GB) has been out of sync from the Master. I cannot find anything interesting in the error log. Google gives me a very helpful link.

I'm going to re-sync the database follow this instruction to minimal downtime on the Master. But before doing this, I just want to make sure that this situation is limited in the future. I will scan through the parts in the above to show you what I've done:

Slave database was configured with read-only option
There is some unsafe queries. Does it get some problems with MIXED
based replication?
I replicated all databases
I used both InnoDB and MyISAM storage engines
Developers use alot of temporary tables

Should I:

Don't use the unsafe queries
Ask developers put all the temporary tables into a separated database

Is there anything else? In case of out of sync, is mk-table-sync reliable enough to re-sync automatically? Does anyone use it on production?

UPDATE: Tue Feb 28 23:27:13 ICT 2012

  My Slave database (~ 40GB) has been out of sync from the Master. I
  cannot find anything interesting in the error log.

To get more information about what was happening, the Slave should be started with --log-warnings=2.


texts: Does anyone know where the SQL Server Connection Properties are stored?

texts:
When you add a Server Instance to Central Management Servers or even your local instance of SSMS, if you click on the connection properties tab you can select the default database you'd like to connect to as well as network protocols connection and execution time-outs and even a custom color for the taskbar when you open a query to using the connection you're defining.

This allows you to shutdown SSMS on your machine and come back days/weeks/years later and connect to a previously connected server and have these connection properties already populated.

This information has to be stored in table in one of the four default databases, but I have been unable to locate any of this information.  I'm particularly interested in the fields that define the custom color of the taskbar for a connection.

I'm working on re-organizing my company's CMS and would like to organize by tier/environment and color code each one differently.

So all

production servers would be red,
Beta/Stage would be Orange,
QA would be green,
Dev would be blue.

I have already crafted a query to insert each Server Instance from a table into CMS, but can't seem to figure out how to populate the default connection's color.

Any help is much appreciated.


texts:
I am running PostgreSQL 9.4.5 on Ubuntu 14.04.3. I am trying to set up pgAgent job but I keep getting this error:

DEBUG: Creating DB connection: user=xxx host=localhost port=5432 dbname=otherdb password=pass
DEBUG: Allocating new connection to database
DEBUG: Executing SQL step 7 (part of job 3)
*** Caught unhandled unknown exception; terminating
DEBUG: Destroying job thread for job 3

I have a .pgpass file in xxx's user home folder with following content:

localhost:5432:*:xxx:pass

I tried to run pgadmin (as user xxx) with this command:

pgagent -f -l2 host=localhost dbname=postgres user=xxx password=pass port=5432

And the job which I set up is a simple, one-step job which should just print out the PostgreSQL version (the exact command I put in my step is SELECT version();) but note that it should run on a different database than defined in pgagent command.

I tried to change the connection string of job's step from 'local' (and just selecting otherdb from the dropdown menu) to 'remote' (and specifying all the parameters in a connection string manually - user=xxx host=localhost port=5432 dbname=otherdb password=pass) but I always see that same error. User xxx has all the privileges on both postgres and otherdb databases.

I am aware that there was a bug in previous versions of pgAgent but the version I was using to set up everything was the latest one (3.4.0) so I suppose this might not be related to that issue.

How to figure out what is causing this problem?


texts:
I have a set of dimensions which are updated only during specific times.

I have moved these tables to a specific filegroup named Dimensions. I wanna keep this filegroup as readonly. When ETL runs, it will set the filegroup to read-write, run, then set it back to readonly.

But when I run ALTER DATABASE MYDB MODIFY FILEGROUP Dimensions READ_ONLY, I get the message Database state cannot be changed while other users are using the database 'MYDB'.

What's the least intrusive way of avoiding new transactions, waiting current ones to finish, then run this command? Is there a way to make a DB single user for some time, or make it offline, whatever?

UPDATE: I'm basing myself on the article The Read Uncommitted Isolation Level:

  For scenarios that demand the very highest levels of consistency guarantees, serializable remains the only safe choice. For performance-critical operations on read-only data (for example, large databases that are effectively read-only between ETL windows), explicitly setting the database to READ_ONLY can be a good choice as well (shared locks are not taken when the database is read only, and there is no risk of inconsistency).


texts: Creating a Min date with Session Date also included in the table

texts:
Code: MSSQL / Bigquery

SELECT EmployeeId, SessionLoginLocal, cast(MIN(SessionLoginLocal) as date)MinSession
FROM database.data_source.user_session_table
Group by EmployeeId, SessionLoginLocal
Order by EmployeeId

Current output:

 Row | EmployeeId |  SessionLoginLocal  | MinSession
-----+------------+---------------------+------------
   1 |        354 | 2017-12-12T07:29:36 | 2017-12-12
   2 |        354 | 2017-12-11T16:58:21 | 2017-12-11
   3 |        354 | 2017-12-12T10:59:34 | 2017-12-12
   4 |        354 | 2017-12-11T18:32:59 | 2017-12-11
   5 |        354 | 2017-12-11T18:33:34 | 2017-12-11
   6 |        354 | 2017-12-11T17:16:58 | 2017-12-11
   7 |        354 | 2017-12-12T10:56:29 | 2017-12-12
   8 |        354 | 2017-12-11T19:37:54 | 2017-12-11
   9 |        354 | 2017-12-12T10:57:52 | 2017-12-12

Desired Output

 Row | EmployeeId |  SessionLoginLocal  | MinSession
-----+------------+---------------------+------------
   1 |        354 | 2017-12-12T07:29:36 | NULL
   2 |        354 | 2017-12-11T16:58:21 | 2017-12-11
   3 |        354 | 2017-12-12T10:59:34 | NULL
   4 |        354 | 2017-12-11T18:32:59 | NULL
   5 |        354 | 2017-12-11T18:33:34 | NULL
   6 |        354 | 2017-12-11T17:16:58 | NULL
   7 |        354 | 2017-12-12T10:56:29 | NULL
   8 |        354 | 2017-12-11T19:37:54 | NULL
   9 |        354 | 2017-12-12T10:57:52 | NULL

The goal of this view is to create a stacked column graph with the date along the X axis, and the number of sessions on the Y.
Currently the number of sessions by distinct employee per day are displayed, now I am trying to include a second column with the minimum session date, basically highlighting when they had their first session. How do i create the column with the Min(SessionTime) to only include the first session and create nulls for their other sessions?


texts:
My error file(/var/log/mysql/error.log) is increased rapidly（10% of disk usage per hour） with only this entry:

[ERROR] Error in accept: Bad file descriptor

and we can only shutdown the server down in the midnight to investigate further, now I need to find a way to stop the error file taking up all space of my disk.

I've tried something like set GLOBAL general_log = 0, but it's not working at all, also tried to flush error logs, a new error file(error.log.1) is created, but the process(mysqld) keeps writing the the old file(error.log).

please help, ask me if you need more information.

DEBUG INFO

Mysql Server version: 5.7.18-0ubuntu0.16.04.1


texts: Restore msdb from SQL Server 2008 R2 to SQL Server 2012 instance

texts:
I'm moving from a SQL Server 2008 R2 instance to a SQL Server 2012 instance on a separate box. I want to restore msdb to get all my agent jobs and the like and I'm getting the following error:

  Restore of database 'msdb' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks)
  System.Data.SqlClient.SqlError: The backup of the system database on the device
  \10.0.0.3\BackUps\NOFAULT2010_msdb_FULL_20121016_220006.bak cannot be restored because
  it was created by a different version of the server (10.50.1600) than this server
  (11.00.2100).
  (Microsoft.SqlServer.SmoExtended)

I would understand this if I was trying to go from 2012 to 2008 but this is new to me.

Anyway to get this to work? Or if not best way to transfer the agent jobs, users, blazi blah ?

Thanks


texts: MSSQL Ubuntu Unexpected call to legacy ABI

texts:
We recently had a Ubuntu 16.04 server loose power. After restarting the server I noticed that MSSQL Server was no longer running due to a an error Unexpected call to legacy ABI. After researching for hours I could not solve the issue.

I then uninstalled MSSQL Server fully and reinstalled it. I went through the configure prompts although when choosing version (SQL Server Express) it would give an error ValueError: unknown locale: UTF-8 I then solved that by doing the following 2 commands:

$ export LC_ALL=en_US.UTF-8
$ export LANG=en_US.UTF-8

Which solved that issue, although after entering the system admin passwords I get this error:

This program has encountered a fatal error and cannot continue running at Wed Apr 15 21:46:52 2020
The following diagnostic information is available:

         Reason: 0x00000003
         Status: 0x00000000
        Message: Unexpected call to legacy ABI.
          Stack:
                 IP               Function
                 ---------------- --------------------------------------
                 0000561aed8a2ada std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > std::__1::operator+<char, std::__1::char_traits<char>, std::__1::allocator<char> >(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > c
                 0000561aed8a25d9 std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > std::__1::operator+<char, std::__1::char_traits<char>, std::__1::allocator<char> >(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > c
                 0000561aed7ea9ae std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > std::__1::operator+<char, std::__1::char_traits<char>, std::__1::allocator<char> >(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > c?*??~
                 0000561aed848717 std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > std::__1::operator+<char, std::__1::char_traits<char>, std::__1::allocator<char> >(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > c
        Process: 20452 - sqlservr
         Thread: 20454
    Instance Id: ae93b937-d981-4d99-b419-bff7970c8bfb
       Crash Id:
    Build stamp: 79eb721ef02d0426160fe0ad320e7425f9d012fbe368753d2655c5bce25b1b3c
   Distribution: Ubuntu 16.04.6 LTS
     Processors: 4
   Total Memory: 8239382528 bytes
      Timestamp: Wed Apr 15 21:46:52 2020
/opt/mssql/lib/mssql-conf/invokesqlservr.sh: line 15: 20451 Aborted                 (core dumped) sudo -EH -u mssql /bin/bash -c "$CMDLINE"
Setup has completed successfully. SQL Server is now starting.

And the service is in a failed state.

Any suggestions?


texts: Too many records on database and how to handle it? MySQL is it good to clone?

texts:
I am doing a social network and came up with the following concern regarding database storage. Let's put facebook as an example, imagine 2,000 photos per user, just 1,000 users, it would equal to 2,000,000 rows in total assuming there is a row per photo.

Now imagine 100,000 users it would be two billion rows (in a single table?)

The problem is how to handle this?
With clones of the same table and then selecting by user id which is assigned to one sector?

suppose

photos_0
photo, date

photos_1
photo, date

and so on, then storing the suffix, 0, 1, etc along the user id and working from that point.

Is there any other better known solution?

//media

mysql_query("DROP TABLE IF exists media");
$vals='sbid int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(sbid),
vids varchar(200),
vidshd varchar(200),
vidso varchar(200),
duration varchar(200),
videow varchar(200),
videoh varchar(200),
notify varchar(200),
pics varchar(200),
picss varchar(200),
picsm varchar(200),
picsr varchar(200),
picsa varchar(200),
faces longtext,
descr longtext,
location varchar(200),
sbidv varchar(200),
id varchar(200),
albumid varchar(200),
albumn varchar(200),
norder int(200),
oldorder int(200),
title varchar(200),
shot varchar(200),
shott varchar(200),
nye varchar(200),
visibility varchar(200),
datetimep int(200),
datetimep_pp int(200)';
$vals=str_replace("
"," ",$vals);
$predefined='';
mysql_query("CREATE TABLE media ($vals) AUTO_INCREMENT=10000000;");

The above is the sample table,
would I go with storing up to billions of records on it, then updating and selecting from it normally where id='$uid' and $uid=theuser's id.

It is not the final table structure but I think it wouldn't be possible to use one table only so I thought about cloning and then when altering a table the need to alter all of the tables becomes necessary which sounds difficult to have to alter hundreds or thousands of tables using lots of queries whereas if it were one table only then it would mean a single query, that is one of the concerns I have about cloning but I also don't know if it is the actual solution to such eventual problem.


texts: Quickly restoring a backup over top of an existing database - error "database in use"

texts:
I'm working on a set of scripts (in SQL Server Management Studio) to fix certain data inconsistencies in a customer database (SQL Server 2008 R2).

I got a .bak file with the database, and I restored that to my system, to e.g. SampleDatabase. Now I'm working on my scripts, and after a while, I've fixed most of the issues.

So now I'd like to restore that original .bak file again, to be sure all my scripts do run against the original data, and do what they're supposed to do. I close all the Windows in SQL Server Management Studio, open a fresh new query window in my master database, and issue my RESTORE command:

RESTORE DATABASE [SampleDatabase]
FROM DISK = N'c:\tmp\sample.bak'
WITH FILE = 1,
MOVE N'Sample_PRIMARY' TO N'c:\mssql\Sample_PRIMARY.MDF',
MOVE N'Sample_LOG' TO N'C:\mssql\Sample_LOG.LDF',
NOUNLOAD, REPLACE, STATS = 5

Unfortunately, I now get an error message:

  Msg 3101, Level 16, State 1, Line 2
  Exclusive access could not be obtained because the database is in use.
  Msg 3013, Level 16, State 1, Line 2
  RESTORE DATABASE is terminating abnormally.

which seems a bit odd, since I made sure I had closed all the SSMS Windows into that database - why is it still in use?

How can I fix this situation as quickly as possible? The only solution I know that works right now is restarting the MSSQLSERVER service - but that's a rather lengthy and somewhat cumbersome process...

Is there another, more efficient, more streamlined way to get this database "out of use" so I can restore the backup over top of it again to check my scripts?


texts: How to reduce the log file size without shrinking it in SQL server

texts: In SQL server, how do you reduce the log files size without (DBCC) shrinking it. I know shrinking the log file will free up some space, but will also cause fragmentation. Doing a checkpoint in simple mode or backing up the transaction log in full mode should do the trick, but it is not working in one of my scenarios. Any advice/links will help in preventing log file fragmentation.


texts: Stored procedures and network congestion

texts:
When I went to an interview, the interviewer asked me "what are the benefits of stored procedures?" and I started answering but when I said "It reduces network congestion", he suddenly said "no, it doesn't".

I made a sudden pause, I was afraid to ask him why. After the interview session, I searched a lot on the Internet but I found the point I said was there but no explanation for it. So I asked myself and made some assertions:
Since a stored procedure is precompiled, i.e. an execution plan has already been created for it, it increases speed of execution.
But for cases where we pass some variables to the SP, those need to go to the server for execution, and should retrieve different results for each variable.

So my point is that the interviewer is correct: network congestion is not reduced, each time the variables have to be passed to the database and the results should be returned, so network is busy with these. Right?

Is my assertion correct?


texts:
I have an AlwaysOn setup in which the jobs are running on domain service account which have been given access to a file share for backups. The jobs are not able to run.

This is the error message which I get

https://ola.hallengren.com     Msg 50000, Level 16, State 1, Server xxxxx , Procedure DatabaseBackup, Line 457  The directory \xxxxxx\backups$ does not exist.     Msg 50000, Level 16, State 1, Server HNATLD1SVRLSQ02, Procedure DatabaseBackup,

Should SQL agent account in the configuration manager run on the same account as the job owner or can it run on the NT\service account as shown in the screenshot attached, because the security context is still that of the job owner which has access to those file shares? What would be the best practice here? Please advice.

We have different service accounts running on the jobs


texts: Best way to copy MS SQL (2000) database files to an external drive

texts:
We have a system running on Windows 2003, with a MS SQL server 2000 (Standard) instance as the data source. As we have sold the product, we are in the process of moving everything to the purchasing party.

We have cloned the virtual machines, and when they set them up, they are stating that the databases are corrupt and would like copies of the MDF/LDF files directly.

What is the cleanest way to do this? There are approximately 90 separate databases configured, and I don't really want to go in, stop/detach/copy/attach/restart, especially as I've heard that reattaching doesn't always go well. I also don't know if every database is stored in the same place.

I am looking for the least involved method of confirming the storage location and copy process for 90+ database files to a connected external hard drive.


texts: why from_utf8 does not exist in postgresql?

texts:
I have the following sql code that uses from_utf8 function:
SELECT FROM_UTF8(name, ’?’) AS name, COUNT(DISTINCT target) AS cnt
FROM directory_entry_file GROUP BY name
ORDER BY cnt DESC
LIMIT 1;

When I execute this query in the postgresql, I get the following error:
ERROR:  function from_utf8(unix_path, unknown) does not exist
LINE 1: SELECT FROM_UTF8(name, '?') AS name, COUNT(DISTINCT target) ...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

How can I fix this error?


texts: How do I sort the results of a recursive query in an expanded tree-like fashion?

texts:
Let's assume you have a nodes tables like this:

CREATE TABLE nodes
(
    node serial PRIMARY KEY,
    parent integer NULL REFERENCES nodes(node),
    ts timestamp NOT NULL DEFAULT now()
);

It represents a standard node-like tree structure with root nodes at the top and several child nodes dangling from root nodes or other child nodes.

Let us insert a couple of example values:

INSERT INTO nodes (parent)
VALUES (NULL), (NULL), (NULL), (NULL), (1), (1), (1), (1), (6), (1)
     , (6), (9), (6), (6), (3), (3), (3), (15);

Now I want to retrieve the first 10 root nodes and all their children up to a depth of 4:

WITH RECURSIVE node_rec AS
(
    (SELECT 1 AS depth, * FROM nodes WHERE parent IS NULL LIMIT 10)

    UNION ALL

    SELECT depth + 1, n.*
    FROM nodes AS n JOIN node_rec ON (n.parent = node_rec.node)
    WHERE depth < 4
)
SELECT * FROM node_rec;

This works great and gives me the following result:

 depth | node | parent
-------+------+--------
     1 |  1   |
     1 |  2   |
     1 |  3   |
     1 |  4   |
     2 |  5   |  1
     2 |  6   |  1
     2 |  7   |  1
     2 |  8   |  1
     2 | 10   |  1
     2 | 15   |  3
     2 | 16   |  3
     2 | 17   |  3
     3 |  9   |  6
     3 | 11   |  6
     3 | 13   |  6
     3 | 14   |  6
     3 | 18   | 15
     4 | 12   |  9

As you might have noticed, there's no ORDER BY clause, so the order is not defined. The order you see here is from root nodes to deeper nodes.

How would I order the results as they would appear in an expanded tree view, as you can see from the example picture below?

I basically want the child nodes to be placed right after their corresponding parent node. If two or more child nodes have the same parent node, I want them to be sorted by their timestamp. Based on the example above, here's the desired output order that I'm trying to achieve:

 depth | node | parent | ts
-------+------+--------+---------
     1 |  1   |        | 2014-01-01 00:00:00
     2 |  5   |     1  | 2014-01-01 00:10:00
     2 |  6   |     1  | 2014-01-01 00:20:00
     3 |  9   |     6  | 2014-01-01 00:25:00
     4 |  12  |     9  | 2014-01-01 00:27:00
     3 |  11  |     6  | 2014-01-01 00:26:00
     3 |  13  |     6  | 2014-01-01 00:30:00
     3 |  14  |     6  | 2014-01-01 00:36:00
     2 |  7   |     1  | 2014-01-01 00:21:00
     2 |  8   |     1  | 2014-01-01 00:22:00
     2 |  10  |     1  | 2014-01-01 00:23:00
     1 |  2   |        | 2014-01-01 00:08:00
     1 |  3   |        | 2014-01-01 00:09:00
     2 |  15  |     3  | 2014-01-01 10:00:00
     3 |  18  |     15 | 2014-01-01 11:05:00
     2 |  16  |     3  | 2014-01-01 11:00:00
     2 |  17  |     3  | 2014-01-01 12:00:00
     1 |  4   |        | 2014-01-01 00:10:00


texts: How to design database table to store process status?

texts:
I have an application (ProcessMonitor) that monitor some constantly running process at fixed interval (like 30 seconds, but can be changed by user) and store the process status to a database table, the table looks like this:

| id         | int             | NO   | PRI | NULL    | auto_increment |
| pid        | int             | YES  |     | NULL    |                |
| pstatus    | int             | YES  |     | NULL    |                |
| timestamp  | datetime        | YES  |     | NULL    |                |

Obviously, this table grows very fast because for every single process, there will be 2880 new records inserted into table per day if the interval is 30 seconds. Given the fact that the process's status is not changed very often  may keep one state for several hours), I decide change the logic of ProcessMonitor to write to database only when the status of process changes using the same table design. There are two problems for the new approach:

There is no way to track when and how long ProcessMonitor is stopped.
Unable to keep track of the change of monitoring interval.

I am feeling that I may need to change the design of the table, but I've not found a good solution, could anyone suggest me a better way to store the process status?


texts: So, you can use FILLFACTOR to leave space in leaf index pages. Specifying PAD_INDEX also leaves space in intermediate nodes. What scenarios should you specify PAD_INDEX, and what benefit does it provide to the index?


texts:
Got a bit of an issue here that I need some help to solve.

Hosting a database, that is about 400Gigs. This is giving us a little headache getting it to work with Managed Backup to Azure Storage Container.

This is the error we get

10/21/2018 12:45:18,spid149,Unknown,Write to backup block blob device https://NAME.blob.core.windows.net/CONTAINER/DB_NAME_20181021120709+02.bak failed. Device has reached its limit of allowed blocks.

The setup was done with

EXEC managed_backup.sp_backup_config_basic

@database_name = 'DB_NAME',
@enable_backup=1,
@container_url = 'https://NAME.blob.core.windows.net/CONTAINER',
@retention_days=30;

USE msdb;
GO
EXEC managed_backup.sp_backup_config_schedule
     @database_name =  'DB_NAME'
    ,@scheduling_option = 'Custom'
    ,@full_backup_freq_type = 'Daily'
    ,@backup_begin_time =  '00:30'
    ,@backup_duration = '02:00'
    ,@log_backup_freq = '00:05'
GO

And the log backup is running fine, so we know the credential is ok. But it will not make a backup of the database, since it is too large to fit, so it needs to be split up. But how to do that?
When I run a manual backup like this

Use MSDB
Go
EXEC managed_backup.sp_backup_on_demand
 @database_name = 'DB_NAME'
,@type = 'Database'

It does split it up into two pieces in the Azure container. So how do I get the automatic job to split up the BAK file.

We are running on SQL Server 2017.

Hope someone has a good idea, since Google doesn't give anything good.

Thanks


texts:
Once per day, this is being executed to rank everyone in the database based on points.

    SET @r=0;
    UPDATE ranks SET previousRank=rank, rank= @r:= (@r+1) ORDER BY points DESC;

The field previousRank contains their previous rank. However, we are now wanting to record the history of the rank.

If we change previousRank to a STRING, is it possible to modify the query to append "rank," to the end of field previousRank instead of replace it? For example, it would look like this: 21,22,20,18,24,.

If so, how would this be done, and are there any steps that can be taken to remove the end comma without slowing the query?


texts:
PostgreSQL 9.4.6
OpenSSL 1.0.2f

If my server or client certificate use SHA256, I get the following error on the client:

psql: SSL error: tlsv1 alert decrypt error

And the following log message on the server:

LOG:  could not accept SSL connection: unknown message digest algorithm

If I generate the client key with SHA1, it works fine:

openssl x509 -req -in user.csr -CA root.crt -CAkey server.key -out user.crt -CAcreateserial -days 365 -sha1

How can I get it to work with SHA256?


texts: CTE To Use Alternate Select When Select Returns No Rows

texts:
I have created an involved CTE, with no recursion, but with multiple selects being done as it processes the business logic of the requirement. Though, I have been advised that the logic needed for the scenario where no rows are returned, should instead to return all rows found from one of the middle CTEs select instead of the final operation.

How can this be achieved?

Note the following example is basic interpretation of my process and the operation in question occurs after multiple cascaded CTE selects are done an essentially chained together.

Example (as found on SQLFiddle)

In a User Table there are two records where the ids of UserIds are 1 and 2. The first CTEs select, TargetUsers will return no rows requiring the the third CTE, ComputeWithUsers, to use the AllUsers instead.

WITH TargetUsers AS
(
  SELECT *
  FROM Users
  WHERE UserId > 5
)
, AllUsers AS
(
   SELECT * FROM Users
)
, ComputeWithUsers
(
-- This CTE will determine it needs to use `AllUsers` and not `TargetUsers`
)

Or is there a way to make the TargetUsers select smart enough to extract all users when its main operation returns no rows?


texts: Script out objects that are in a specific schema

texts:
I am running SQL Server 2014 and using SSMS.

Is there a way to script out all objects in a specific schema (stored procedures, views, tables etc.) so that I can generate the objects in another database?

Is there a way to narrow it down to scripting out only all the tables in a certain schema?


texts: How to be sure that a SQL Script is running with ANSI encoding?

texts:
Maybe this is a stupid question or I'm asking it in the wrong way.

How can I be sure that a script ( with thousands of lines ) is running with ANSI encoding?

Let's say we created a script using Notepad++ ( programmers code for SQL and ORACLE at the same time ), and we save it with Encode in ANSI :

OK. Then, we have inside our script a Â character. If our client just copies this script into some tool that is using a different encoding ( really, I don't know why, but some users do this, and I know for sure it's not to find errors), this Â  will be transformed into a: , but since our script has thousands of rows, nobody will notice this at the client side.

We know users don't see this, they will not read the entire script to be sure everything is ok. Not even DBAs that are paid to do this, will do this ( I'm a DBA and I for sure read all scripts all the time ).

So, how can I be sure, that when the user press F5, all script is encoded with ANSI, the same way we sent it, without these strange characters? Can we only achieve this with the right collate for the database?

I was trying to think something like this on the first row of the script with a case when asci character = the ascii(character) then ok else ERROR ( using ascii to test ):

Select CHAR(ASCII('ã')) As Teste_CHAR, CHAR(227) as Teste_CHAR_ASCII

Solomon, this is the query:

Select *,
Case
        When Teste_CHAR=Teste_CHAR_ASCII
        Then 'OK'
        Else 'Erro'
END as STATUS_TESTE,
Case
       When Teste_CHAR=Teste_CHAR_ASCII
       Then 'Everything is OK'
       Else 'Script will not run. your encode is different from ours'
END as Mensagem_TESTE
 from (
Select CHAR(ASCII('ã')) As Teste_CHAR, CHAR(227) as Teste_CHAR_ASCII
) A


texts: Can two mysql SELECT FOR UPDATE FROM mytable WHERE .. IN (..) deadlock?

texts:
Can two or more instances of the following deadlock with one another?

START TRANSACTION
SELECT mycolumn FROM mytable WHERE myid IN (list-of-ids) FOR UPDATE
:
COMMIT

Points to keep in mind:

mycolumn is indexed
This is mariaDB/innoDB
There is only one table involved (mytable)

Example A:

Thread 1:

    START TRANSACTION
    SELECT mycolumn FROM mytable WHERE myid IN (100,200) FOR UPDATE
    :
    COMMIT

Thread 2: (note that IN-list is in opposite order)

    START TRANSACTION
    SELECT mycolumn FROM mytable WHERE myid IN (200,100) FOR UPDATE
    :
    COMMIT

Could these threads possibly deadlock on the SELECT in mariaDB/innoDB?

Example B:

Thread 1:

    START TRANSACTION
    SELECT mycolumn FROM mytable WHERE myid IN (100,200) FOR UPDATE
    :
    COMMIT

Thread 2: (note that IN-list is in same order)

    START TRANSACTION
    SELECT mycolumn FROM mytable WHERE myid IN (100,200) FOR UPDATE
    :
    COMMIT

Could these threads possibly deadlock on the SELECT  in mariaDB/innoDB?

UPDATE: A traditional way of avoiding deadlocks is to be sure to acquire locks in the same order, so that you can't have Thread 1 owning lock A and waiting on lock B while Thread 2 owns lock B and is waiting on lock A. However, I've seen several posts suggesting that even getting a single lock can't be considered atomic, because there are asynchronous processes going on in the engine that could still result in deadlock (and as a corollary to this, advising developers to always expect deadlocks and recover from them by retrying the transaction). I'm really asking whether deadlock can be eliminated by using proper coding practices in innoDB.)


texts: How can I relate ‘address’ with other two entity types?

texts:
In a certain business domain, there are three entity types: Address, Merchant and Hotel, and they're related as follows:

A Merchant can have many Addresses, and
a Hotel can have only one Address at a time.

In my database structure, I want to “use” the same table for both entity types, and I think that I need to have two columns for that, that is to say, merchant_id and hotel_id in the Address table.

I feel this design is not perfect because in many cases either of the two columns would contain NULL marks. What is recommended approach?


texts: Multi-Server Extended Events investigations

texts:
Do you happen to know if there's a method of centralizing the Extended Events information of many servers in one place (for easier administration and diagnostic).

Ideally I would like to have something like this:
http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/04/15/sql-2012-system-health-reporting-dashboard-visualizing-sp-server-diagnostics-results.aspx?CommentPosted=true

Only that it'll support Multi server environments and not a server/server  deployment.

Thanks in advance,
Roni.


texts:
When I run this command

exec xp_cmdshell 'copy C:\---\cmdshell1\a23.txt C:\---\cmdshell2';

It copies a23.txt file from cmdshell1 to cmdshell2 folder.

When I run the same command second time it does not copy the same a23.txt file to folder cmdshell2 for second time.
So after running xp_cmdshell command multiple times I only get one file in cmdshell2 which was generated the first time.
My question is, how to write this command in order to copy the a23.txt file from cmdshell1 folder into cmdshell2 the amount of times I ran the query(if I have run the above query 3 times, I want to have 3 copies of a23.txt file in cmdshell2).


texts: How to connect to SQL Server Analysis Server via Management Studio command line

texts:
I can connect SQL Server Management Studio to a SQL DB by passing command line parameters like this:

"C:\Program Files (x86)\....\Ssms.exe" -E -S SERVERNAME -d DB_NAME -nosplash

(Obviously -E is for window Authentication)

This connects to the SQL Server. But I often find I want to connect to an Analysis Server instance instead. Can anyone tell me the command line option please?


texts:
There is a tree-structures in two-tables with ParentId field. How to make UPSERT (INSERT ... ON CONFLICT (...) DO UPDATE ...) between them? The problem is that parent record for the current record in the 1st table may not be yet synchronized with 2nd table (missed in 2nd table).

This is query I am trying to write:

WITH upsert AS (
  SELECT
    "Name",
    d."Oid" "Parent",
    "ExternalId"
  FROM "KsuNsiDepartment" c
  LEFT JOIN "KsuNsiDepartment" p on c."ParentDepartment" = p."Oid"
  LEFT JOIN "Department" d on p."ExternalId" :: varchar(100) = d."ExternalCode"
  WHERE
    "GCRecord" IS NULL
    /*AND OID IN (...)*/
)
INSERT INTO "Department" ("Name", "Parent", "ExternalCode")
VALUES
  (
    upsert."Name",
    upsert."Parent",
    upsert."ExternalId" :: varchar(100)
  ) ON CONFLICT ("ExternalCode") DO
UPDATE
SET
  "Name" = EXCLUDED."Name",
  "Parent" = EXCLUDED."Parent";

The problem that for current record from KsuNsiDepartment parent record from KsuNsiDepartment may exists, but may be not yet corresponding record for this parent record in the Department table


texts:
I want to ask in SQL script for a start and finish date with accept but I get an error.

accept v_FechaInicio date format 'DD/MM/YYYY-HH24:MI:SS' prompt 'Fecha de inicio ('DD/MM/YYYY-HH24:MI:SS'): '
accept v_FechaFin date    format 'DD/MM/YYYY-HH24:MI:SS' prompt 'Fecha de fin    ('DD/MM/YYYY-HH24:MI:SS'):'

SQL> SQL> SQL> SP2-0003: Ill-formed ACCEPT command starting as DD/MM/YYYY-HH24:MI:SS'): '
SQL> SP2-0003: Ill-formed ACCEPT command starting as DD/MM/YYYY-HH24:MI:SS'):'
SQL> accept v_FechaInicio date format 'DD/MM/YYYY-HH24:MI:SS' prompt 'Fecha de inicio ('DD/MM/YYYY-HH24:MI:SS'): '
SP2-0003: Ill-formed ACCEPT command starting as DD/MM/YYYY-HH24:MI:SS'): '

What is the problem?


texts:
I am trying to do the following. I have about 50 DBs and a network NAS that acts as backup machine. I have made an sql script(lets say backup.sql) with 50 lines each of these say "BACKUP DATABASE ABC TO DISK='Y:\ABC.bak'". Also, I have a .bat file with a single command "sqlcmd -i backup.sql -o logs.txt". When I execute it I take a message in the log that say:

  HResult 0x2, Level 16, State 1
  Named Pipes Provider: Could not open a connection to SQL Server [2].
  Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
  Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

I have to mention that I want to make the backup from sql server 2008. Any ideas?
Thanks in advance.


texts: Wait for an AlwaysOn replica to be synchronized in Powershell

texts:
I am working on a disaster recovery script. One part of it is not being consistent.

This is the code:

...
## Switch replica to Synchronous
Set-SqlAvailabilityReplica -AvailabilityMode "SynchronousCommit" `
-Path SQLSERVER:\Sql\Server1\Instance\AvailabilityGroups\SCOM-DBs\AvailabilityReplicas\Server2

## Wait until the replica is synchronized
$NewPrimary = Get-Item SQLSERVER:\Sql\Server1\Instance\AvailabilityGroups\AG-DBs\AvailabilityReplicas\Server2

Write-Host -NoNewline "Waiting for DBs to be synchronized"
while ($NewPrimary.RollupSynchronizationState -notlike 'Synchronized') {
   Write-Host  -NoNewline "."
   Start-Sleep -s 2
}
...

Sometimes gets synchronizing and it does not change even though SSMS dashboard changes. Is there a better in Powershell way to detect when the replica is synchronized?


texts:
I'm using SQL Server Management Studio. I have a massive database and I want to search for column names that might be able to help me figure out what I need to join on.

Is there a tool or other method for doing that kind of a search? I have been looking for a way to do a column search but can't find one.


texts: Migration to version 2016 from versions 2000, 2005, 2008, and 2012

texts:
I just accepted a position that has multiple versions of SQL Server, from what I was told during my interview, it ranges from versions 2000 - 2012, the main goal is to centralized these databases to the 2016 version so we can get rid of the Linked Servers and the users would just point to the 2016 instance.

All of these versions have identical databases, logins, etc, for the most part, but there are slight object differences that need to be evaluated.  Most importantly, the data date on these versions are different, for instance, for SQL Server 2000, it contains data up to year 2005.  For SQL Server 2005, it contains data up to 2007, and so on.  The upgrade/migration experience I have are from SQL Server 2008 R2 to SQL Server 2012 which included Always On, but steps should be similar, and btw, this was a team effort.  You cannot upgrade from SQL Server 2000 to SQL Server 2012 as it requires an intermediate step.

I would like to approach it this way as this requires

Upgrade SQL Server 2000 to SQL Server 2008 R2
Upgrade SQL Server 2005 to SQL Server 2008 R2

or this can be upgraded directly to  SQL Server 2012

Upgrade SQL Server 2008 R2 to SQL Server 2012
Upgrade SQL Server 2012 to SQL Server 2016

There are a lot of documents out there but would you know of a good set of documents/whitepaper that includes the details step by step?
From what I mentioned above, would you approach it differently?

Please let me know your thoughts as I know this is tedious work since the versions accumulated.

Also, this is a side-by-side upgrade, thank God!


texts: Relationship Between Students, Parents and Guardians

texts:
Assume I use MySQL, and there are two tables, students and parents_guardians table. Table students is used to store students' data. The parents_guardians table is used to store students' parents and  students' guardians.
So either a student can have a father and a mother as his parents, or a student can have an uncle and an aunt as his guardians. To do that, I add one more table as pivot table, which is students_parents_guardians table.
This pivot table contains 3 columns:

students_id
parents_id
guardians_id

The question is:
Is this good design? And how can I do that in Laravel?
So far, I'm unable to do that. What I can do is create 5 table:

students table to store student's data
parents table to store parents' data
students_parents as pivot table to perform relationship between students
and parents.
guardians table to store guardian's data
students_guardians as pivot table to perform relationship bwtween students
and guardians.

I believe this is a bad idea, and I should normalize all of it.


texts: Does wsrep_cluster_address only accept the intranet IPs?

texts:
I was installing Percona XtraDB Cluster with 3 nodes.

Each node has a unique Internet IP address.

Following the docs Quick Start Guide for Percona XtraDB Cluster, I got the first node running but failed to get the other two nodes working.

So I am doubting that the wsrep_cluster_address only accepts the intranet IPs, is that true?


texts:
I am not sure that is a "SELECT/JOIN" problem but I really don't know how to formulate the question. Perhaps you could suggest a better title

I have a table (table-a) with a list of items, I Want to associate the information of table-B.col2 when it is equal to "1"

What query should I write?

Table A
Col1
AAA
BBB
DDD
EEE
                          AAA 1
Table B      =====>       BBB
Col1  Col2                DDD 1
AAA   1                   EEE
AAA   2
BBB   2
CCC   1
CCC   3
DDD   1
FFF   1


texts: Memory utilization per database - SQL Server

texts:
I am trying to find the memory utilization per database(using dmvs) in a sql server instance,
ie (Buffer Cache + Procedure Cache) over a period of time.
 I have the query to get the buffer cache utilization, what i require is procedure cache, plus any other parameter that a database will use in terms of memory.

-- Get total buffer usage by database for current instance

SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

Also if this can be compared with some perfmon counter info, it would be great


texts: MySQL Deadlock - accessing different primary key values also creating deadlock

texts:
I went through MySQL logs and found the reason for deadlock

1st thread is trying to execute below query. Which is waiting for thread 2.

UPDATE M_SAMP SET MM_Q_IND=0 WHERE M_ID IN (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19))

The result of inner query (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19)) is M_ID = 3562.

2nd thread is executing below query.

DELETE FROM M_SAMP WHERE M_ID=3455

There is no foreign key relation defined in M_SAMP and MM_RVW_SAMP tables. And M_ID is primary key of M_SAMP table. And engine is InnoDB. And this issue is repeating.

Can any one help me, how locks are granted because of which deadlock is happening?

Logs

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-02-23 10:52:28 de8
*** (1) TRANSACTION:
TRANSACTION 13275344, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 25 lock struct(s), heap size 2408, 1394 row lock(s), undo log entries 1
MySQL thread id 1455, OS thread handle 0x11ac, query id 39660 xyz 192.168.1.108 userName Sending data
UPDATE M_SAMP SET FLAG=0 WHERE M_ID IN (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 61569 page no 65 n bits 176 index `PRIMARY` of table `dbName`.`m_samp` trx id 13275344 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 29; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 13275335, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
254 lock struct(s), heap size 27512, 21595 row lock(s), undo log entries 7
MySQL thread id 1458, OS thread handle 0xde8, query id 39664 xyz 192.168.1.108 userName updating
DELETE FROM M_SAMP WHERE M_ID=3455
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 61569 page no 65 n bits 176 index `PRIMARY` of table `dbName`.`m_samp` trx id 13275335 lock mode S locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 29; compact format; info bits 0

Thank you.

Update:

Number of records in M_SAMP table is 3524 and in MM_RVW_SAMP is 2.

Below is the result of EXPLAIN query.

EXPLAIN UPDATE M_SAMP SET MM_Q_IND=1 WHERE M_ID IN (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19))


texts:
MariaDB was installed using yum on Centos7 using repository http://yum.mariadb.org/10.3/centos7-amd64.  MariaDB's log files are sent to directory /var/lib/mysql.  How can I change the location to another location such as /var/log/mysql/?

[michael@developserver mysql]$ mysql -u root -p
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.3.14-MariaDB MariaDB Server

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

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

MariaDB [(none)]> SET GLOBAL general_log_file='/var/log/mysql/developserver.log';
ERROR 1231 (42000): Variable 'general_log_file' can't be set to the value of '/var/log/mysql/developserver.log'
MariaDB [(none)]> show global variables like '%general_log_file%';
+------------------+-------------------+
| Variable_name    | Value             |
+------------------+-------------------+
| general_log_file | developserver.log |
+------------------+-------------------+
1 rows in set (0.002 sec)

MariaDB [(none)]>


texts: SQL Server 2000-2014 migration using 2008 Express as intermediary

texts: We have SQL Server 2000 Standard Edition, and we're researching migration to SQL Server 2014.  I already know that I need to use 2008 as an intermediate step.  My question is, do we need to purchase a licensed 2008, or will 2008 Express work.  The 2008 edition will only be installed briefly (as long as it takes to restore to it, and then backup from it), so I'm hoping Express is good enough.


texts:
I'm going to implement database replication (merge publication, bi-direction) between two servers running SQL Server 2008 Standard edition. The setup is alright but it comes to a problem when synchronizing data.

The main task of my program is to let user register and after the INSERT statement is executed on my member table (with uid primary key and auto-incremented), it queries SELECT @@identity; for the max. primary key. The program then uses this key for insertion into another table.

All these work fine without replication.

But after replication, when doing SELECT @@identity; it returns back an incorrect primary key. (Suppose now member contains 100 members, after inserting a new record, it should have returned 101. But instead it returns 2 or 3 which is wrong and not what I expected.)

So my question is how to get a correct value using SELECT @@identity;.

I know the possible solution is SCOPE_IDENTITY(), but I want to know how to fix this without changing the source code.

Thanks in advance.


texts:
I am trying to replace an existing database with pg_restore and am running into issues. I apologize if this issue is elementary.
Specifically my Postgres install is set up like this:

database postgres owned by root
database d owned by u
database restore owned by u (more on this later)

I created a dump like pg_dump --format=custom .... I am now trying to replace d with the contents of that dump. Specifically I am running a command like pg_restore -cC --dbname=$DB --exit-on-error --format=custom --username=$USER.
But it seems no matter what combination of $DB and $USER, Postgres is unable to restore. From the error messages (below) Postgres seems to want the user to simultaneously be root and the owner of d. I have read the man pages and tried various combinations to no avail.
What am I doing wrong?

Specifically, if $DB is d, regardless of $USER, I get

ERROR:  cannot drop the currently open database
Command was: DROP DATABASE d;

Fair enough. So let's try $DB=postgres and $USER=root:

ERROR:  must be owner of database d
Command was: DROP DATABASE d;

Okay, so let's try $DB=postgres and $USER=u:

ERROR:  must be member of role "root"
Command was: ALTER DATABASE d OWNER TO root;

Huh? Maybe that has to do with logging into postgres, which is owned by root? Let's try $DB=restore (created specifically for this purpose) and $USER=u:

ERROR:  must be member of role "root"
Command was: ALTER DATABASE d OWNER TO root;


texts: Should I stop an awfully inefficient MySQL DELETE statement?

texts:
I'm running the following statement on MySQL 5.5 in a 1GB 1CPU SSD virtual machine from Linode:

DELETE table
FROM (
    SELECT MAX(id) id, field
    FROM table
    GROUP BY field
) temp_table
INNER JOIN table
    ON table.field = temp_table.field
WHERE table.id != temp_table.id

It's been stuck in the Sending data state for more than 24 hours, and now I know why: it has an awful execution plan:

mysql> EXPLAIN SELECT 1 FROM (SELECT MAX(id) id, field FROM table GROUP BY field) temp_table INNER JOIN table ON table.field = temp_table.field WHERE table.id != temp_table.id
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 381888 |                                 |
|  1 | PRIMARY     | users      | ALL  | NULL          | NULL | NULL    | NULL | 984873 | Using where; Using join buffer  |
|  2 | DERIVED     | users      | ALL  | NULL          | NULL | NULL    | NULL | 984873 | Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
3 rows in set (46.12 sec)

(MySQL 5.5 doesn't allow EXPLAIN DELETE, so I'm doing it with EXPLAIN SELECT 1 as per this answer)

Please note that only the EXPLAIN itself is taking 46 seconds to complete.

Should I stop this statement and try to do things better, or should I wait?


texts:
So this is more of a theory question than practical but I'd appreciate any help. My friend and I are doing a project to learn new stuff and have fun. He's a coder and I like to think that I'm good with databases. Today an issue arose because he said that he thinks we shouldn't have repetitive queries just for different tables and had an idea of using ? or some other symbol in my queries so he can replace it with appropriate word in code.

However, I don't agree, partly because I like having all of my queries in pure SQL and partly because I don't think that it's a good permanent solution. For an example what if a table gets new fields? The query is automatically not valid anymore.

Even if there are tables that are relatively similar and could use the same query is his idea a good practice or am I right about separating the queries?

Sorry if this is not the right place to post the question since it's not really a specific problem.


texts:
I have recently started learning MySQL and am trying to join data from two tables into a third table built.

The two tables are:

'Company Financials'   -   Column of 'Revenue'

'Fortune500'   -    Columns of 'Year' and 'Company'

My query code is:

INSERT INTO Company(ID, Year, Company, Revenue)
    (select Year, Company, Revenue
    from (select Revenue from Company_Financials
    union select Year, Company from Fortune500));

Fortune 500 Table:

Company Financials Table:

I've had multiple errors related to this code, but have been able to figure out why they were wrong.  This one is stumping me :(

Any help is much appreciated!


texts: How to accomplish SQL Server 2012+2014 high availability

texts:
(Copied from here)

Maybe I'm searching for the wrong thing, but I can't see how I can do this.

I have two servers:

Server A (has SQL Server 2012 installed)
Server B (has SQL Server 2014 installed)

Server A provides an API via IIS. The code both inserts and gets data from the database depending on the API call.

Server B does exactly the same for its local database.

I would like to have it so that the two databases are consistent copies of each other. I do believe this is what HA accomplishes.

So a load balancer, for example, might direct API calls to A sometimes, and B sometimes, but the data needs to be consistent either way.

I found some documentation on HA for SQL Server 2012, but not on how to do this for a mixed environment of 2012 and 2014.

Any help or pointers to documentation would be great! Thanks.


texts: Is it possible to compare timestamps between rows?

texts:
I have a MySQL table reqs with (this is simplified from actual):

| Field                 | Type                  | Null | Key | Default | Extra |
+-----------------------+-----------------------+------+-----+---------+-------+
| time                  | datetime              | YES  |     | NULL    |       |
| user                  | varbinary(40)         | YES  |     | NULL    |       |
| foo                   | mediumint(8) unsigned | YES  |     | NULL    |       |
| bar                   | varbinary(20)         | YES  |     | NULL    |       |
| other fields          | ...

I want to find duplicate user-foo-bar combinations near one another in time — say, within 60 seconds of one another. So I want something like

select [whatever] from reqs inner join (
select user,foo,bar, count(*) as count from reqs
group by user,foo,bar having count>1
) as dups
where dups.user=reqs.user
and dups.foo = reqs.foo
and dups.bar=reqs.bar
and [something indicating the times are near each other];

— or at least that's the idea (in other words, I'm not sure whether the above is the way to structure the query, but the above is representative of the result I want from the query).

How would I do this?

Note that reqs is a huge table, so minimizing joins would be ideal.


texts:
We migrated from Oracle to SQL Server, the new server has good resources and should burn through data.

When reading OS MasterMap data it consumes over 50% of cpu and takes several minutes to load.

I have run ANALYZE and INDEX on the three databases.

SELECT    latch_class,   waiting_requests_count , wait_time_ms, max_wait_time_ms          --- *
FROM sys.dm_os_latch_stats
WHERE waiting_requests_count > 100
ORDER BY  waiting_requests_count DESC

latch_class waiting_requests_count  wait_time_ms    max_wait_time_ms
BUFFER  81551206    81704580    2017
ACCESS_METHODS_DATASET_PARENT   1641537 5663549 7506
NESTING_TRANSACTION_READONLY    1084221 494094  213
DBCC_CHECK_AGGREGATE    431924  120721  64
FGCB_ADD_REMOVE 167166  2130810 806
NESTING_TRANSACTION_FULL    91970   23584   194
ACCESS_METHODS_BULK_ALLOC   25362   493780  995

SELECT  wait_type, signal_wait_time_ms , waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE signal_wait_time_ms > 176397
ORDER BY waiting_tasks_count DESC

The results:

wait_type   signal_wait_time_ms waiting_tasks_count
SLEEP_TASK  2002394 76078204
PAGEIOLATCH_SH  277602  67814336
SOS_SCHEDULER_YIELD 7501348 66782317
LOGMGR_QUEUE    523607  54389813
DIRTY_PAGE_POLL 187977  44182775
CXPACKET    5480362 27547323
ASYNC_NETWORK_IO    720221  20290395
LAZYWRITER_SLEEP    1119484 14749937
WRITELOG    685367  14323568
HADR_FILESTREAM_IOMGR_IOCOMPLETION  176407  9559003
BACKUPBUFFER    564488  9513327
SLEEP_BPOOL_FLUSH   252249  8582959
CLR_MANUAL_EVENT    2000281 7383128
SP_SERVER_DIAGNOSTICS_SLEEP 9665265581  5643767


texts: How many queries per second is my Postgres executing?

texts: How can I tell how many queries per second my Postgres database is executing?


texts: Error loading example PostgreSQL database: "Could not find header for file ..."

texts:
I am learning o use PostgreSQL so I am trying to insert a whole practice database "dvdrental", which I downloaded from PostgreSQL learning blog. I am on a Mac OSX and I am using PostgreSQL through my terminal (and ZSH).

These are the steps I took:

download dvdrental.zip file
create dvdrental database under the right user, with the right permissions
unzipped dvdrental.zip
tar-ed the folder that I extracted from .zip file

Here is an ls -l output of that is inside the extracted directory:

2163.dat
2165.dat
2167.dat
2168.dat
2169.dat
2171.dat
2173.dat
2175.dat
2177.dat
2179.dat
2181.dat
2183.dat
2185.dat
2187.dat
2189.dat
restore.sql
toc.dat

Here is the command that I am using to get the sample db dump into my newly created db:

~>pg_restore -U postgres -d weather /users/edchigliak/downloads/dvdrental.tar

The error I receive:

~>pg_restore: [tar archiver] could not find header for file "toc.dat" in tar archive


texts:
I was asked to test a change to a query, moving a Table Valued Function (TVF) into a CTE (non-recursive).  My understanding was that each CTE reference is executed separately, so there was a possibility of the change leading to tempdb contention...

I was very surprised to see the query performance improve when using the CTE.  IE:

 ;WITH cte AS (
    SELECT t.col FROM dbo.getValues() t)
 SELECT ...
   JOIN cte c ...
 UNION ALL
 SELECT ...
   JOIN cte c ...

Can anyone shed any light on why this is happening?


texts:
I'm working with Oracle databases from 10g-r1 to 11g-r2. They are set as ISO-8859-15 (WE8ISO8859P15) character set.

I need to create a tablespace set as UTF-8 (AL32UTF8) character set inside these databases. Is it possible? What is the correct syntax?


texts: How do I list the constraints of a DOMAIN?

texts:
Consider the following scenario:

CREATE DOMAIN dom_zipcode AS text;
ALTER DOMAIN dom_zipcode
  ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5);

Now, if I want to drop that constraint with ALTER DOMAIN, The manual says:

ALTER DOMAIN name
    DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]

But how can we find constraint_name? \dD only shows the constraint's definition (CHECK statement).

\dD dom_zipcode ;
                             List of domains
 Schema |    Name     | Type | Modifier |             Check
--------+-------------+------+----------+--------------------------------
 public | dom_zipcode | text |          | CHECK (char_length(VALUE) = 5)
(1 row)

I can dump the schema using pg_dump, but I believe there must exist a more elegant way to establish this using the psql terminal.


texts: How should I use RMAN clone refresh using set until time?

texts:
I am using this technique to refresh dev. environments with production data.

The business process is to request a tape hold for the full backup I want to use for a clone refresh to dev. Once this is imported, I use the exact server end time of the database backup (not the logs backup) for my set until time clause.

run {
set until time = "TO_DATE('08/09/2014 20:24:47','MM/DD/YYYY HH24:MI:SS')" ;
allocate auxiliary channel t1 type 'sbt_tape';
duplicate target database to DEVDB;
}

This works only some of the time. More often than not, the script goes looking for a previous backup which is not in tape hold.

  Media required
  Detected Criteria: Media Handling Required
  Job ID:
  Status: Media is not in Library
  Library Name: SEA-TAPELIB07 (i500 B)
  Media label: SVL432L4
  Failure Reason: The required media [SVL432L4] for the job [2518965] is not in the Library [TAPELIB (9999)]. Please use the import functionality to import the media from the location [Not Available] into the library [TAPELIB (9999)].
  Media List: Not Applicable

As a workaround, I requested the previous backup and re-ran the script. It completed but it used the previous day's backup and the request was fulfilled two days late. What am I doing wrong with this technique or should I be using a different method? Any suggestions would be helpful.


texts: Are page numbers of a book a good analogy for a clustered index?

texts:
The title pretty much covers the question.
I think that the index of a book is a good analogy for a non-clustered index, as it demonstrates the extra storage and physical separation properties of a non-clustered index. I also think that the page numbers of a book represent well the physical ordering of data, similar to the structure of data with a clustered index.
However I'm a bit of a newbie to more advanced database theory. Does the analogy for page numbering make sense for a clustered index, or are there any properties of a clustered index where this analogy falls over?

Please note that the terminology of pages in the question refers to pages of a physical book, such as George Orwell's 1984, and not database pages.


texts: Discover missing foreign keys and/or indexes

texts:
We have a pretty big database (around 700GB of data), which has been inherited to us, and has had several DBAs. With time we've realized that there are several missing foreign keys and indexes.

I'd like to know if there is some automated way of discovering such potential missing keys/indexes using some kind of log parsing or something alike.


texts: Best way to flag a row for future deletion?

texts:
I have users table where a row can be marked for future deletion, so I'm looking for the best way to do this.

I've thought of two options so far:

Add a delete_date to the users table.

Every night a process will query the table and get all users that match delete_date with current date and delete them.
Create a users_to_be_deleted table with the id of the row in users table to be deleted along with a delete_date.

Every night a process will query users table and join on users_to_be_deleted in order to identify which users should be deleted (matching delete_date with current date), and then delete them.

Basically, Option 1 adds a column that can (and in most cases will) contain NULL marks, while Option 2 has no columns that accept NULLs at all, but will require an additional JOIN.

Considering that most users will not be marked for deletion (an estimate is that only 10-20% will), which option is the most efficient? What are the pros and cons of each option?


texts: What does: ERROR: tuple offset out of range: 0 mean?

texts:
It seems (but I can't tell for sure) that after I added a postgis indexed column (USING gist) to my database last week, I have been seeing a lot of those errors:

ERROR: tuple offset out of range: 0

The errors occur on all kinds of queries, also some that does not use those columns, and hence shouldn't use that index right?

Anyways my question is: What does that error message mean, and could it have something to do with postgis, or am I off track?

I'm on postgresql 9.3 and postgis 2.1


texts: Compare data between 2 different schemas

texts:
We refactored our old database. Some tables were dropped, some tables were split into 2 new tables etc. So now we have an absolutely new DB schema which has many differences from the old one. Someone has written an application which migrates data from the old database to the new one.

I'm looking for methods/techniques to compare correctness of data migration. Is there any standard or often-used ways to accomplish such compare?

After some research, I found one technique how to do it - create a VIEW of each table of the old database i.e. I will try to represent in VIEW data in format of old table (old schema), and then compare data in old table with data in VIEW.

I'm interested in other ways.


texts:
I'm trying to compare data from two systems. The basic idea is:

Create Temp Table.
Insert Table1 Data.
Insert Table2 data (Table a linked, remote server) with UNIQUE Ids respected.
Filter where first or second table has different info.

I'm trying to get the second set of inserts, from the second system, to insert, and if duplicate, update instead. I can't seem to get it right. For the final insert, what should I have?

I don't have the "Filter Part" because I don't have the Table2 data inserted. The filter part would show rows where Active, Last or First name is different between the two systems. Simple where Table1LastName <> Table2LastName type statements.

Note: I have this working with 2 temp tables created... and a full outer join and some "Where ID is null". I'm just trying to get this to work in a manner I haven't used before.

if object_id('tempdb..#CompareDrs') is not null
    drop table #CompareDrs;
GO

create table #CompareDrs
(
     ID varchar(20) unique
    ,TABLE1Active varchar(1)
    ,TABLE1LastName varchar(50)
    ,TABLE1FirstName varchar(50)
    ,TABLE2Active varchar(1)
    ,TABLE2LastName varchar(50)
    ,TABLE2FirstName varchar(50)
);
GO

INSERT INTO #CompareDrs (ID, TABLE1Active, TABLE1LastName, TABLE1FirstName)
    SELECT
         right('00000000000000000000' + ForeignID, 20) ID
        ,case when Active = 0 then 'I'
              when Active = 1 then 'A'
              else ' '
         end       TABLE1Active
        ,LastName  TABLE1LastName
        ,FirstName TABLE1FirstName
    FROM
                  [DATABASE1].dbo.DRs
    Order By
        ID;
GO

INSERT Into #CompareDrs (ID, TABLE2Active, TABLE2LastName, TABLE2FirstName)
    SELECT
         right('00000000000000000000' + Cast(DRDR# as Varchar(50)), 20) ID
        ,DRAFLG TABLE2Active
        ,DRLNAM TABLE2LastName
        ,DR1NAM TABLE2FirstName
    FROM
        [LINKEDERVER].[DATABASE].[CATALOG].[TABLE] DR
ON DUPLICATE KEY UPDATE TABLE2Active = VALUES(TABLE2Active),
                        TABLE2LastName = VALUES(TABLE2LastName),
                        TABLE2FirstName = VALUES(TABLE2FirstName),
GO

SELECT * FROM #CompareDrs;


texts: Alias of COUNT is not being "recognized" by SQL Server

texts:
I have restored the Northwind database(https://northwinddatabase.codeplex.com/) from a backup file using SQL Server.

Trying to execute the following query results in error "Invalid column name 'products'.", product is an alias.

SELECT OrderID, COUNT(ProductID) products
FROM [NORTHWND].[dbo].[OrderDetails]
GROUP BY OrderID
HAVING products > 5;

What is the problem?


texts: Why would a single ghost record remain in a few random indexes?

texts:
I used the ghost_record_count column to find indexes/partitions that have ghost records. I found a few that have one such record. I then tried to queue them up for processing by scanning all pages:

SELECT COUNT_BIG(*)
FROM T WITH (READUNCOMMITTED, INDEX(idx))
OPTION (MAXDOP 1)

I then waited at least 10 seconds to let the ghost cleanup task run. But the ghost records do not disappear. I also tried restarting the server. No ghost related trace flags are in use.

This is not an actual problem that I'm having. I'm trying to understand ghost cleanup in general. Why do the counters not drop to zero in tables that have no writes?


texts:
I have a query to get data between current time and INTERVAL 15 minutes

Table calls have 39790720 items;

SELECT src,unique,dstchannel,chan,calldate
from calls
WHERE calldate BETWEEN (NOW() - INTERVAL 15 MINUTE) AND NOW()
AND (dstchannel LIKE '%TEXT1/%'
OR dstchannel LIKE '%TEXT2%'
OR dstchannel LIKE '%TEXT3%'
OR dstchannel REGEXP '^SIP/[[:digit:]]{10}-'
OR dstchannel LIKE '%TEXT4%'
OR dstchannel LIKE '%TEXT5%'
OR dstchannel LIKE '%TEXT6%'
OR dstchannel LIKE '%TEXT7%')
AND lastdata NOT LIKE '%TEXT8%'
LIMIT 39780720,39790720

Query 1 row in set (1 min 7.38 sec)

+-------------+--------------+------+-----+---------------------+-------+
| Field       | Type         | Null | Key | Default             | Extra |
+-------------+--------------+------+-----+---------------------+-------+
| calldate    | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
| colum1      | varchar(80)  | NO   |     |                     |       |
| colum11     | varchar(80)  | NO   |     |                     |       |
| src         | varchar(80)  | NO   |     |                     |       |
| colum12     | varchar(80)  | NO   |     |                     |       |
| chan        | varchar(80)  | NO   |     |                     |       |
| dstchannel  | varchar(80)  | NO   |     |                     |       |
| colum2      | varchar(80)  | NO   |     |                     |       |
| colum3      | varchar(80)  | NO   |     |                     |       |
| colum4      | int(11)      | NO   |     | 0                   |       |
| colum5      | int(11)      | NO   |     | 0                   |       |
| colum6      | varchar(45)  | NO   |     |                     |       |
| colum7      | int(11)      | NO   |     | 0                   |       |
| colum8      | varchar(20)  | NO   |     |                     |       |
| colum9      | varchar(32)  | NO   |     |                     |       |
| colum10     | varchar(255) | NO   |     |                     |       |
+-------------+--------------+------+-----+---------------------+-------+

+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | calls | ALL  | NULL          | NULL | NULL    | NULL | 39791545 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+

When %TEXT1% is a variable only need contain like '%Sip/phone%' in value.

How to improve query?


texts: Multi-column uniqueness constraint, checking a field has one of two values

texts:
I'd like to enforce that only one row in a database matches value A in column 1, and either value B or value C in column 2. A small example

create table builders (
    id text serial
);
create table houses (
    id text serial,
    status text,
    builder_id text references builders(id)
);

The house status can be "NOT_STARTED", "PLANNING", "CONSTRUCTING", "BUILT" or integers mapping to these values or whatever you want.

There's a rule in my system where a builder can only plan or construct one house at a time, that is, if there are two house rows with builder Id 1 and one has status "PLANNING" and the other has status "CONSTRUCTING", the system is in an inconsistent state. I'd like to enforce this in the database.

I tried doing

CREATE UNIQUE INDEX CONCURRENTLY builders_unique_house
ON houses ("status", "builder_id")
WHERE "status" IN ('PLANNING', 'CONSTRUCTING');

I was able to add this index, and it can detect two houses in PLANNING or two in CONSTRUCTING, but it allows one to be in PLANNING and one to be in CONSTRUCTING.

Is there an index or constraint I can add to express this rule? Another possibility I had was adding a boolean to represent "planning or constructing", like in_progress or something and then adding a unique key on that.


texts: show variables - Unknown column 'value' in 'where clause'

texts:
In mysql I ran the below:

mysql> SHOW VARIABLES where variable_name = 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.32 sec)

mysql> SHOW VARIABLES where variable_name = 'character_set_database' and value = 'utf8';
ERROR 1054 (42S22): Unknown column 'value' in 'where clause'

Why does the above happen?


texts: How to deal with a person that may have multiple alias names?

texts:
I am designing a neo4j database which will be primarily be used to store information about people. One of the requirements is that it must be able to store an alias name of a person.

I'm having trouble deciding how I should design my database so that it deals with people that have multiple aliases?

I have two solutions:

Store the persons alias names in a single field, comma separated. This is simple, but makes query-ing difficult.
Store a persons alias name in a separate table/entity called alias. This seems to be overly-complex but makes querying easier than option 1.

Which of these, if any, is the correct answer to the problem? Or alternatively, is there a better solution which I am overlooking?


texts: SSMS 2016 Query Store: Missing Index Details error: DocumentFrame (SQLEditors)

texts:
Each time I try and view missing index details in Query store I get the following error:

  DocumentFrame (SQLEditors)

  Program Location:    at
  Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ShowPlan.ShowPlanControl.OnMissingIndexDetails(Object
  sender, EventArgs a)    at
  System.ComponentModel.Design.MenuCommand.Invoke()    at
  Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ShowPlan.ShowPlanControl.Microsoft.VisualStudio.OLE.Interop.IOleCommandTarget.Exec(Guid&
  guidGroup, UInt32 nCmdId, UInt32 nCmdExcept, IntPtr vIn, IntPtr vOut)

Is anybody else experiencing this? Is this something I can fix or is it a bug?

SSMS version 13.0.16106.4

It's a bit frustrating as I can't view the end of the create index recommendation.


texts: Suggestions - Enabling EFS on FileStream Data for SQL Server

texts:
If anyone has already tried enabling EFS on FileStream data for multiple databases around 500 GB+, please share your thoughts on feasibility or issues / considerations we should keep in mind.

We are in process of enabling EFS for PROD. We are testing in lower environments too.

Specifically, which one is better - enabling parallel EFS, or enabling EFS on one FileStream Folder at a time?


texts: Does Azure Hyperscale allow for Memory optimised tables and does it allow/have rollback facilities


texts: Using WITH clause with INSERT statement in POSTGRESQL

texts:
I have a requirement in which I need to get one column from another table and insert that column data with some other data into another table.

Example:

If the cust_id='11' then I need to get the cust_code from cust table (let's say it returns cust_code='ABCD') and then use that cust_code with some other data to insert into table_1 like so:

WITH get_cust_code_for_cust_id AS (
    SELECT cust_code FROM cust WHERE cust_id=11
)

INSERT INTO public.table_1(
    cust_code, issue, status, created_on)
    VALUES (SELECT cust_code FROM get_cust_code_for_cust_id, 'New Issue', 'Open', current_timestamp)

But this query does not work as we haven't called the get_cust_code_for_cust_id query.

My preference is some query with WITH clause but any other answer will also be appreciated.


texts: Is it bad practice NOT to delete unused database entities?

texts:
A recent code review by a client revealed that there some database entities (tables, views, stored procedures) were no longer being used in our Oracle 12c database. We have hundreds of stored procedures, views and tables (not thousands), and the database itself is about 40GB in size with a few hundred million records. I don't know the number of unused entities but I'm told it would take 4 hours or so to remediate.

The problem is that regression testing would take a huge amount of time.

I've been told that having unused entities is common and is not necessarily bad as they could be used at some future time. But on the other hand, this can be confusing to others who are working on the system.

I'd like to have a better perspective on this -- can this be considered a fault developers need to remediate, or is it a non-issue?

Thanks


texts: I have a VARCHAR(255) primary field field in an InnoDB database table. I only store values of length 32 bytes in it (32 ASCII characters). My primary key has a prefix length of 767 bytes. Clearly, this longer prefix length than I need. Does this have any performance impact?


texts: Error in loading data using DB2 load command in DB2 10.5

texts:
I have a IBM DB2 database in our application. We have a monthly Java batch process which loads data in the tables using

"load Replace"
sql command. Around millions of record will be updated every month in this batch process.
The batch was running fine without any issues in the DB2 version 9.5 . Recently the database was updated to DB2 Version V10.5. After the upgrade , we face the following issues in the batch process. (No change in the code)

There is an Interrupted Exception occurring in the middle of the process.
com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-952, SQLSTATE=57014, SQLERRMC=null, DRIVER=3.69.24

The data in which the data gets loaded is also getting slow after some time. At first the data ets loaded in around 50,000 rows /sec. After some time it is getting loaded at the rate of 1000 rows/second.

TheCPU utilization is also getting increased gradually as the load starts.

We select data from the input tables using Java JDBC connection.Then we process the data and store the data in temporary files. From the files we insert data in the utput tables using the load command. Is there any upgrade issue or enhancement in DB2 10.5, that does not support this. Will using cursor solve this issue. I am also not sure if there is any connection problems in the new version of Db2.
Any help is highly appreciated. Please comment for any additional information required.


texts:
Upgrading MySQL database to 5.5 and it seems that the root user has disappeared.

I'm trying to create it again using this commands:

javier@javier-mbp:~$ sudo pkill mysqld
[sudo] password for javier:
javier@javier-mbp:~$ sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables&
[1] 1927
javier@javier-mbp:~$ 111028 16:37:44 mysqld_safe Logging to '/var/log/mysql/error.log'.
111028 16:37:44 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

Then I do:

javier@javier-mbp:~$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> GRANT CREATE, DROP ON *.* TO root@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Any help?

EDIT: Solved, well… I just should do a backup of the user root since I'm removing the database before upgrading to 5.5.


texts: Deleting duplicate based on latest datestamp entry

texts:
I have a table and I'm trying to remove all the duplicate and keep the
the rows that has the latest datestamp.

Here is the table:

email address       orgin_date   new_opt_in_date   datestamp
123@ax.tu            1900-1-1     1900-1-1          2016-3-15
123@ax.tu            1900-1-1     1900-1-1          2016-3-15
iron_man@metrix.com  2015-2-2     2016-12-26        2017-1-19
iron_man@metrix.com  2015-2-2     2016-12-26        2018-6-6
sleep@dort.st        2016-3-15    2016-3-151        2019-1-23
sleep@dort.st        2016-3-15   2016-3-151         2018-5-6

I'm trying to keep only the data that has the recent datestamp, delete the
rest and hope that the
output will like this:
email address       orgin_date   new_opt_in_date   datestamp
 123@ax.tu            1900-1-1     1900-1-1          2016-3-15
 iron_man@metrix.com  2015-2-2     2016-12-26        2018-6-6
 sleep@dort.st        2016-3-15    2016-3-151        2019-1-23

DELETE FROM `tablename`
WHERE datestamp
NOT IN (
SELECT * FROM (
  SELECT MAX(datestamp) FROM tablename
    GROUP BY emailaddress
 )
 )
but nothing it didn't work