texts: ORA-39002, ORA-39168 on IMPDP, Object path GRANT was not found

texts:
I'm trying to impdp a schema with just one table on it. The impdp parfile looks like this:

LOGFILE=scott_impdp.log
DUMPFILE=scott.dmp
SCHEMAS=scott
REMAP_SCHEMA=scott:scott2
REMAP_TABLESPACE=scott:scott2
EXCLUDE=grant
PARALLEL=8
CLUSTER=N
TRANSFORM=disable_archive_logging:Y

However, I'm getting these errors:

ORA-39002: invalid operation
ORA-39168: Object path GRANT was not found.

If I'm not mistaken, EXCLUDE=grant is valid syntax according to https://docs.oracle.com/database/121/SUTIL/GUID-DC7668E1-C846-48C5-A0D5-F4659EC119BB.htm#SUTIL911

From that link:
Specifying EXCLUDE=GRANT excludes object grants on all object types and system privilege grants.

Removing EXCLUDE=grant seems to work, but why would that break the impdp in the first place?

EDIT:
My expdp parfile looks like this:

LOGFILE=scott_expdp.log
DUMPFILE=scott.dmp
SCHEMAS=scott
PARALLEL=8
CLUSTER=N
COMPRESSION=ALL
FLASHBACK_TIME=systimestamp
REUSE_DUMPFILES=Y
EXCLUDE=statistics


texts: Use non-printable characters in SQL Server BCP xml format file as TERMINATOR

texts:
I have a flat-text data file containing records which fields are separated by the non printable character "File Separator" (0x1c). I am trying to use SQL Server's bcp utility to load this data into my database. Yet, when using the hex encoded value of the File Separator as TERMINATOR, I get a syntax error.

I have tried using

the hex encoded value : "0x1c"
the XML encoded value as hex: "&#x1c;"
the XML encoded value as decimal: "&#28;"

None of these work, yet when using the same encoding for a printable character, like tab, this does work: &#x09;, &#9; (0x9 doesn't. Not unsurprisingly, since this is an XML file.)

The conclusion seems to be that non-printable characters are not supported. Is this the case? That would be ironical, since the non-printable separator characters are created exactly for this purpose...

Below you can find all code to reproduce this issue:

XML format file: test.xml

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="&#x1c;" MAX_LENGTH="10" COLLATION="Latin1_General_CS_AS_WS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="41"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="COL1" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="COL2" xsi:type="SQLNUMERIC" PRECISION="4" SCALE="0"/>
 </ROW>
</BCPFORMAT>

Data file: test.txt
This is just one row, as a test case. StackExchange doesn't show the separator in the row below, yet when you click "Edit" for this post, the separator is included, and you should be able to copy-paste this.

1111111112008

Commandline

bcp TEST_DB.dbo.UL_TEST in "test.txt" -T -f "test.xml"


texts: How to configure mongodb replicaset of 3 nodes in authentication mode

texts:
I had configured a mongodb replicaSet of 3 nodes. It was running fine But WHen i created a adminUser and dbuser on primary node and enabled the authorization in mongodb.conf file of all nodes then they are not connecting. Below is the output of rs.status() after enabling authorization. Kindly Help.

{
    "set" : "replset1",
    "date" : ISODate("2017-07-15T04:09:41.796Z"),
    "myState" : 3,
    "term" : NumberLong(1),
    "heartbeatIntervalMillis" : NumberLong(2000),
    "members" : [
        {
            "_id" : 1,
            "name" : "10.10.8.1:27017",
            "health" : 1,
            "state" : 3,
            "stateStr" : "RECOVERING",
            "uptime" : 54841,
            "optime" : {
                    "ts" : Timestamp(1500033801, 1),
                    "t" : NumberLong(1)
            },
            "optimeDate" : ISODate("2017-07-14T12:03:21Z"),
            "infoMessage" : "could not find member to sync from",
            "configVersion" : 1,
            "self" : true
        },
        {
            "_id" : 2,
            "name" : "10.10.8.2:27017",
            "health" : 0,
            "state" : 6,
            "stateStr" : "(not reachable/healthy)",
            "uptime" : 0,
            "optime" : {
                    "ts" : Timestamp(0, 0),
                    "t" : NumberLong(-1)
            },
            "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
            "lastHeartbeat" : ISODate("2017-07-15T04:09:37.008Z"),
            "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
            "pingMs" : NumberLong(0),
            "authenticated" : false,
            "configVersion" : -1
        },
        {
            "_id" : 3,
            "name" : "10.10.8.3:27017",
            "health" : 0,
            "state" : 6,
            "stateStr" : "(not reachable/healthy)",
            "uptime" : 0,
            "optime" : {
                    "ts" : Timestamp(0, 0),
                    "t" : NumberLong(-1)
            },
            "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
            "lastHeartbeat" : ISODate("2017-07-15T04:09:37.136Z"),
            "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
            "pingMs" : NumberLong(0),
            "authenticated" : false,
            "configVersion" : -1
        }
    ],
    "ok" : 1
}


texts:
I'm trying to draw stats out of a table and depending on the increment, group them by year, month, date.

The table is as follows

CREATE TABLE `channel_stats` (
  `channel_id` int(10) unsigned DEFAULT NULL,
  `datestamp` date DEFAULT NULL,
  `udatestamp` int(10) unsigned DEFAULT NULL,
  `ip_country` char(2) DEFAULT NULL,
  `billing_country` char(2) DEFAULT NULL,
  `payment_method` varchar(45) DEFAULT NULL,
  `trans_type` varchar(45) DEFAULT NULL,
  `trans_status` varchar(45) DEFAULT NULL,
  `trans_currency` char(3) DEFAULT NULL,
  `trans_amount` decimal(13,4) unsigned DEFAULT NULL,
  `base_amount` decimal(13,4) unsigned DEFAULT NULL,
  `trans_fees` decimal(13,4) DEFAULT NULL,
  `volume` int(10) unsigned DEFAULT NULL,
  UNIQUE KEY `channel_stats_UNIQUE` (`channel_id`,`datestamp`,`ip_country`,`billing_country`,`payment_method`,`trans_type`,`trans_status`,`trans_currency`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and the query I'm trying to use (which doesnt work) looks something like this

select
    datestamp,
    trans_type,
    trans_status,
    sum(base_amount) 'total_amount',
    sum(trans_fees) 'total_fees',
    sum(volume) 'total_volume'
from channel_stats
where channel_id = p_channel_id
and udatestamp >= p_startdate and udatestamp <= p_enddate
group by case
    when p_increment = 'days' then
        (datestamp)
    when p_increment = 'months' then
        (year(datestamp), month(datestamp))
    when p_increment = 'years' then
        (year(datestamp))
end, trans_type, trans_status;

does anyone know how i can achieve this?

EDIT:

How it would get called for each increment

call uic_get_dashboard_transactions_100('1',(unix_timestamp() - 60 * 60 * 24 * 30),unix_timestamp(),'days');
call uic_get_dashboard_transactions_100('1',(unix_timestamp() - 60 * 60 * 24 * 365),unix_timestamp(),'months');
call uic_get_dashboard_transactions_100('1',(unix_timestamp() - 60 * 60 * 24 * 365 * 5),unix_timestamp(),'years');

It works if I break it out which I think in terms of processing, is a better way of doing it, just creates more code than probably necessary. Current solution:

CREATE PROCEDURE `uic_get_stats_transactions_100`(
    p_channel_id int unsigned,
    p_startdate int unsigned,
    p_enddate int unsigned,
    p_increment varchar(10)
)
BEGIN
    case p_increment
        when 'days' then
            select
                datestamp,
                trans_type,
                trans_status,
                sum(base_amount) 'total_amount',
                sum(trans_fees) 'total_fees',
                sum(volume) 'total_volume'
            from channel_stats
            where channel_id = p_channel_id
            and udatestamp >= p_startdate and udatestamp <= p_enddate
            group by datestamp, trans_type, trans_status;
        when 'months' then
            select
                datestamp,
                trans_type,
                trans_status,
                sum(base_amount) 'total_amount',
                sum(trans_fees) 'total_fees',
                sum(volume) 'total_volume'
            from channel_stats
            where channel_id = p_channel_id
            and udatestamp >= p_startdate and udatestamp <= p_enddate
            group by year(datestamp), month(datestamp), trans_type, trans_status;
        when 'years' then
            select
                datestamp,
                trans_type,
                trans_status,
                sum(base_amount) 'total_amount',
                sum(trans_fees) 'total_fees',
                sum(volume) 'total_volume'
            from channel_stats
            where channel_id = p_channel_id
            and udatestamp >= p_startdate and udatestamp <= p_enddate
            group by year(datestamp), trans_type, trans_status;
    end case;
END


texts:
I'm looking for a simple solution: if you have a job with multiple steps and one step fails but the job is set to continue, is there a way for the SQL Agent email to say which step(s) in the process failed?

Something more than "One or more of the job steps in the  job was not successful."

I'm sure I can add another step that queries the job history table and sends another email but I'm trying to combine the default email with a bit more information.


texts: oracle 11g seems to die every 2 days, how can i debug what's happening?

texts:
I installed oracle 11g on ubuntu 11.10, but it seems that every couple of days I'll get to work and the database isn't running but the listener is.

To fix it, all I have to do is login and start it up:

su - oracle
cd /path/to/oracle11g/bin
./sqlplus sys as sysdba
startup;
exit

and then everything's ok. Taking a look at the alert log at:
/u01/app/oracle/diag/rdbms/mydb/mydb/alert/log.xml

It looks like the problem started here:

<msg time='2012-03-09T22:00:02.570+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='kesaiTuneSqlDrv:4182:2579917519' client_id='' type='NOTIFICATION'
 group='sqltune' level='16' module='DBMS_SCHEDULER'
 pid='4460'>
 <txt>Begin automatic SQL Tuning Advisor run for special tuning task  &quot;SYS_AUTO_SQL_TUNING_TASK&quot;
 </txt>
</msg>
<msg time='2012-03-09T22:00:31.273+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='3646992671' type='INCIDENT_ERROR' group='Generic Internal Error'
 level='1' prob_key='ORA 600 [kgkprrpicknext1]' upstream_comp=''
 downstream_comp='' ecid='' errid='11150'
 detail_path='/u01/app/oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_29268.trc'>
 <txt>Errors in file /u01/app/oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_29268.trc  (incident=11150):
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [18], [2], [], [], [], [], []
 </txt>
</msg>


texts: The memory usage of SQL Server keeps growing

texts:
We have a production SQL Server 2008r2 x64. 16GB RAM and this server is used by the application (hosted) and SQL Server.

The max memory setting = 6GB
The min memory setting = 128MB.

There are ~25k users access this database server through the application. As the memory usage reaches to the max memory setting value, we will start facing timeout issues and users will not be able to login. Also, we see CPU usage by SQL Server is 100% when this happens.

Is there any way to control this buffer pool memory usage by SQL Server as SQL Server will not release this memory until the OS is in badly need of it. Currently, whenever such a situation occurs, we just restart SQL Server and let it release the memory to the OS. After restarting it works smoothly for few days and again it reaches to the max memory limit and start causing issues.

So how do all huge production environments using SQL Server manage this issue? As memory usage will rapidly be growing.


texts: Setting utf8mb4_unicode_ci as the default for all utf8mb4 character set tables

texts:
I am running MySQL 5.7.19 in RDS and recently went through a conversion of everything to Character Set = UTF8MB4 and Collation = UTF8MB4_unicode_ci. I set the following parameter group settings and rebooted:

character_set_client = utf8mb4
character_set_connection = utf8mb4
character_set_database = utf8mb4
character_set_results = utf8mb4
character_set_server = utf8mb4
collation_connection = utf8mb4_unicode_ci
collation_server = utf8mb4_unicodel_ci

My goals were:  for one to provide consistency on all databases, tables and columns. Second to stop requiring developers to specify character_set and collation when creating tables.

The problem I am running into is that when I look into the information_schema.collations table, utf8mb4_general_ci is still set to IS_DEFAULT yes. So when one of our servers connects and creates a temp table it's set up as utf8mb4_general_ci rather than utf8mb4_unicode_ci, unless they specify it in table creation, which I wan to avoid. In addition when I run show variables like 'coll%' I get the following:

collation_connection    utf8_general_ci
collation_database  utf8mb4_unicode_ci
collation_server    utf8mb4_unicode_ci

Which is the same problem, just on my laptop vs the server. Has anyone run into this and solved it?


texts: Migration from SQL Server 2008 R2 to SQL Server 2012 SP2

texts:
I have many backups on my SQL Server 2008 R2 system, and I must restore them on new SQL Server 2012 SP2.

My question is, after the restore on new SQL Server 2012, what can I do to verify that everything is ok?

I'm sure that it is necessary to change the compatibility level, but is it necessary to run a DBCC CHECKDB and an UPDATEUSAGE command?

Is there a need to perform other particular checks? What's the best way?

Cheers


texts: Transactional replication reports ok, but tracer token not arriving

texts:
I've got an odd problem here.  I was trialling the use of 'initialize from lsn' in our test replication setup and now have broken all publications from one of our databases, rebuilding from scratch (applying a new snapshot) has not fixed the issue.

In Replication monitor, distributor agent running continuously and reporting:

  No replicated transactions are available

Tracer token is reporting that:

Publisher to Distributor: 00:00:02
Distributor to Subscriber: Pending...
Total Latency: Pending...

When I investigated the distribution logs using:

SELECT time,
       CAST(comments AS XML) AS comments,
       runstatus,
       duration,
       xact_seqno,
       delivered_transactions,
       delivered_commands,
       average_commands,
       delivery_time,
       delivery_rate,
       delivery_latency / (1000 * 60) AS delivery_latency_Min,
       agent_id
FROM dbo.MSlogreader_history WITH (NOLOCK)
WHERE agent_id = 5
ORDER BY time DESC;

SELECT *
FROM dbo.MSdistribution_history
WHERE agent_id = 125
ORDER BY time DESC;

I can see that the latest xact_seqno from:

LogReader agent is: 0x00000378000384880003
Distribution agent is: 0x000CA68B000010A8000A000000070000

As a comparison on one of the other databases (going to the same subscriber), I get:

LogReader agent is: 0x00000D140001F52A0003
Distribution agent is: 0x00000D140001F52A0003000000000000

To me it looks like the distribution agent is remembering the old broken xact_seqno (from when I made a mistake when testing 'initialize from lsn'), but now it appears to have permanently broken all replication from that database.

Servers:

PubA - One publisher hosting several databases that are replicated
Dist - A separate distributor
SubA - First subscriber
SubB - Second subscriber

Databases:

PubA.DB1 = Publisher DB that is working fine
PubA.DB2 = Publisher DB that is broken
SubA.PullWorking1 = 1st subscriber has a working pull subscription from DB1
SubA.PullBroken2 = 1st subscriber also has a broken pull subscription from DB2
SubA.PushWorking1 = 2nd subscriber has a working push subscription from DB1
SubA.PushBroken2 = 2nd subscriber also has a broken push subscription from DB2

Publications:

PubA.DB1.PushPub_works
PubA.DB1.PullPub1_works
PubA.DB1.PullPub2_works
PubA.DB2.PushPub_broken
PubA.DB2.PullPub_broken


texts:
My understanding is that Postgres supports UUID data type out-of-the-box. I'm running Postgres on Amazon RDS with Engine version 10.6 and am scratching my head as to why the following commands are not behaving as expected:

CREATE TABLE IF NOT EXISTS "Tenants"
(
    "id" UUID NOT NULL ,
    "domain" VARCHAR(255) NOT NULL UNIQUE,
    "name" VARCHAR(255) NOT NULL,
    "schema" VARCHAR(255) NOT NULL UNIQUE,
    "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL,
    "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id")
);

The critical part here is that I'm creating "id" UUID NOT NULL. So far, so good. Or so I thought.

Next, I'm trying to add an entry:

INSERT INTO "Tenants" ("id","domain","name","schema","createdAt","updatedAt")
VALUES
(
    '3b6b220b-2690-4279-8343-cbe6167f7596',
    'test1.stage-domain.io',
    'Test1','test1',
    '2019-02-25 14:29:33.475 +00:00',
    '2019-02-25 14:29:33.475 +00:00')
RETURNING *;

and surprisingly the following error shows up:

  SequelizeDatabaseError: invalid input syntax for integer:
  "3b6b220b-2690-4279-8343-cbe6167f7596

Sure enough, if I go to PGAdmin, I'm seeing that the column has not been set to UUID, rather it has been set to an integer:

The dropdown shown in this picture does not even contain a UUID type.

The weirdest part is that I created a different Postgresql server on RDS last night and everything actually worked just fine in that instance.

My question: How can I get my database to recognize the UUID type when creating the Tenants table?


texts: separating tlog and db file when using fusionio

texts:
We have a FusionIO card that we use in our production database. We have both the transaction log as well as the database files on that same drive. I know the recommendation is to place the tlog files on a separate physical drive, but given that the I/O on the FusionIO card is significantly better will moving the tlogs to a disk drive create a bottleneck?

I understand that  the tlogs are written to sequentially (the reason it is recommended moving them). But if IO difference is significant between the two drives as it is between traditional drives and a FusionIO card, will I still see a benefit from moving the tlogs to a different drive? Or will performance be worse?

The reason I'm asking is because we're running out of space to have both on the same drive and it would be nice if we could use a cheaper drive array for the tlogs instead of having to buy a separate FusionIO card for the tlogs.

BACKGROUND:

The database is a typical web-based workload with much more reading going on than writing - with the exception that when writes happen there is a lot of writing going on. For example, users can upload multiple files with 10,000 or more records in each of them.


texts: Error Querying MySQL server linked to SQL Server 2008

texts:
I've created a linked server in MS SQL Server 2008 to a remote MySQL server. When I try to query any tables, I get an error:

.tablename. contains no columns that can be selected or the current user does not have     permissions on that object.

Does anyone know why this happens?

UPDATE: Looks like this is a known issue with MSSQL SERVER 2008

http://support.microsoft.com/kb/971261


texts: Dropping unique constraint on PostgreSQL table taking 10+ hours

texts:
We have a PostgreSQL database with a fairly large table (~50 million rows). The table has a unique constraint across several columns, each of which is indexed. For context, I recently deleted about half the rows in the table -- old data. Now I want to simply remove the multi-column unique constraint, so I executed this SQL:

alter table my_big_table drop constraint my_constraint_name

However, I started the command 10+ hours ago, and it's still running with no sign of abating. Why would dropping a constraint be taking so long? Is there anything I'm missing, or any way to perform actions like this with some kind of progress or status indication?

(I'm using PostgreSQL 9.0 on a Windows 64-bit system, and I generally connect to it using pgAdmin, though I can use the command-line client or connect programmatically too.)


texts: What is the usage of sys.sysrowsets in SQL Server?

texts:
I just wonder what is the usage of sys.sysrowsets in SQL Server? Microsoft said it “Exists in every database. Contains a row for each partition rowset for an index or a heap.” But what is a partition?

Also what is the meaning of idmajor and idminor for this table? I try to find the object ID of a table(246486340) and then execute the query:

Select * from sys.sysrowsets where idmajor = 246486340

The results are two records:

rowsetid           ownertype       idmajor idminor  numpart status  fgidfs  rcrows

72057603504865280   1              246486340     1     1        6     0     2582
72057603612213248   1              246486340     6     1        2     0     2582

But I think there should only be one record with that major id.


texts: send custom email when SQL Server job fails

texts:
I have setup a profile and account for sending mail using DatabaseMail. I have a SQL JOB. If the job fails, I receive an Email

Till this point, everything works fine. This is what the email looks like :

JOB RUN:        'testjob' was run on 3/7/2014 at 4:50:00 PM
DURATION:       0 hours, 0 minutes, 0 seconds
STATUS:         Succeeded
MESSAGES:       The job succeeded.  The Job was invoked by Schedule 10 (s1).  The last step to run was step 1 (run).

Now what I want is, if the job fails for any reason, that error message should appear in the email message body.

for example, FK_Constraint error , Conversion errors or any error that caused my sql job to fail.

Is this possible ?


texts: What are some technical reasons for not including N-ary multiplication in the SQL standard?

texts:
If I want to add up two values in a query, I can use the + operator.
If I want to sum up all values returned by a query, I can use SUM.

If I want to multiply two values in a query, I can use the * operator.
If, however, I want to multiply together all values returned by a query, there does not appear to be a MULT or PRODUCT or the like.

I find myself using exp(sum(ln(X))), which is ridiculous. It's astonishing that this feature is not present since even more "esoteric" things like covariances and regression coefficients are conveniently available in some dialects.

Why is it that the SQL standard does not have this (I believe) "basic" feature? Surely it must be a conscious design decision. Are there any technical advantages to not having this operation or discouraging users from performing multiplication on large query results?


texts: How to recover an overwritten SQL Server database

texts:
We have SQL Server Standard Edition with several databases. We backup via a Windows Task that executes the sp_BackupDatabases procedure.

We have a case where a database got replaced by a backup from 2 days ago by mistake.

Unfortunately the connection to the NAS crashed, so the last 4 days, there where no backups made.

Since a backup was restored over the current database, there is no transaction log we could use to manually undo the changes.

Does anyone have an idea how I can get a version of the database before the database was restored? For example, does sp_BackupDatabases  make a local temp backup and moves it then, so that this temp backup could be still available?


texts: Problem with database migrated from SQL Server 2005 to SQL Server 2014

texts:
First of all I want to apologize for my English, I hope you understand me correctly.

I have a database migrated from a SQL Server 2005 in a SQL Server 2014.
Well, in addition, I have a .NET process that uses ODBC connectors to execute stored procedures in the database.

My problem is that this process lasts 20 minutes against SQL Server 2005, and against the migrated database in SQL Server 2014 it takes 8 hours.
After many tests, if the level of compatibility of the database to SQL Server 2008, the process takes only 8 minutes.

Any idea of the reason? What problems can I have when lowering the compatibility level?


texts: Does Batch Mode only apply to Agg Functions on ColumnStore Index

texts:
Azure SQL Database - Standard Edition (S3 Service Tier)

Why does Batch Mode only come into effect when using Aggregate Functions?

DROP TABLE IF EXISTS dbo.TransCS

CREATE TABLE dbo.TransCS (
     Col1 INT
    ,Col2 AS Col1*2
)
CREATE CLUSTERED COLUMNSTORE INDEX CS_TransCS on dbo.TransCS;

WITH
    L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
    L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    AS n FROM L5)
INSERT INTO dbo.TransCS (Col1)
SELECT TOP (1000000) n FROM Nums ORDER BY n;

Insert 1 Million rows

Row Processing

SELECT * FROM dbo.TransCS

Execution Plan

Batch Processing

SELECT Col1, Col2, SUM(Col2)OVER ()
FROM dbo.TransCS

Execution Plan

Is there a way to utilise the performance benefits without using Agg functions?
Documentation on this is thin.


texts:
Recently I am experiencing performance issue on a SQL Server database and I have two queries that show me the wait as shown below, one of the databases on this server is a member of Always On replica, but the load is on the other database.

The first query is my own query which collects the waits stat during 10 seconds and calculates the difference and the second list if from Pauls's query:
https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

Regarding the differences, I'm wondering should I have to ignore CXPACKET and Redo_Thred_Pending_Work wait and work on the root cause of PageIOLatch_SH wait (maybe poor indexing).
MAXDOP value is 0 and the server has 36 cores, any thoughts?


texts: How do I create/publish a report in SQL Server 2014?

texts:
I installed SQL Server 2014 with Reporting Services but I don't how understand how to create and publish a report so that users can run it on the report server on IIS.

These are the programs that have been installed when I search 'SQL':

SQL Server 2014 Management Studio
SQL Server 2014 Reporting Services
Configuration Manager

Are these the only required programs to be able to create and publish reports? Which do I use to create a report?


texts: How to filters data with two windows conditions?

texts:
I want to get the result of two sub-queries. In my case I have users, user can have multiple access for doors, but sometimes doors are disabled so the access exist but do not point to any doors. I want to find the multiple access for the same user that have at least access with no door and at least one access with one door set.
Here is the SQL, that work. But it is very complicated. How could I improve it?
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS doors CASCADE;
DROP TABLE IF EXISTS access CASCADE;
CREATE TABLE users(id SERIAL PRIMARY KEY);
CREATE TABLE doors(id SERIAL PRIMARY KEY, type TEXT NOT NULL);
CREATE TABLE access(
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL, FOREIGN KEY (user_id) REFERENCES users (id),
  door_id INT, FOREIGN KEY (door_id) REFERENCES doors (id)
);
INSERT INTO users VALUES (1), (2), (3), (4);
INSERT INTO doors(type) VALUES ('front'), ('back');
INSERT INTO access(user_id, door_id)
VALUES
  (1, 1), -- user have only access to door
  (2, null), -- user with empty door access
  (3, 1), (3, null), -- user with access and empty access
  (4, 1),(4, 2) -- user with access to front and back access
  ;

--- We want all the door access for the same user with at least one empty access and one door access

WITH users_with_multiple_access AS (
  SELECT access.user_id
  FROM access
  GROUP BY access.user_id
  HAVING (COUNT(access.user_id) > 1)
),
users_with_multiple_access_and_without AS (
  SELECT access.*, CASE WHEN door_id IS NULL THEN 1 END AS marked
  FROM access
  JOIN users_with_multiple_access USING(user_id)
),
users_with_multiple_access_marked AS (
  SELECT access.id AS access_id, sum(marked) OVER (PARTITION BY access.user_id) AS marked
  FROM access
  LEFT JOIN users_with_multiple_access_and_without uwmaw ON uwmaw.id = access.id
)
SELECT access.*
FROM access
JOIN users_with_multiple_access_marked awmam ON awmam.access_id = access.id AND marked IS NOT NULL;

Fiddle


texts: SQL Availability Groups: Can clients use cluster name instead of listener name(s)?

texts:
When using Availability Groups, is it possible to simply just configure all clients/applications to connect to the database(s) by using the cluster name/IP instead of the AG listener for the AG that the database resides in?

If it is possible, would it be advised against?

Example:

3 node cluster with cluster name of "SQLCLUSTER2014" and cluster IP of 10.0.1.10
2 nodes located at main site, one node off site at a DR site
2 AG's, with listeners called "AG1_LISTENER" and "AG2_LISTENER"
Create a DNS A record for "SQLCLUSTER" that points to 10.0.1.10 (the cluster IP)
Configure all clients/application to point to "SQLCLUSTER" in their connection string

So when you upgrade to SQL 2016 and move your databases to the new server, you can just point your A record to the new cluster IP and not have to change connection strings in applications.

It seems like this would be a better setup because it allows you to utilize DNS CNAME's or A records to mask server moves, which I've done to great success on systems that don't use AG's.

Is there any reason not to do this?
Any potential issues reaching the DR site if the 2 main nodes go offline?

Thanks.


texts: Is there a way to see the access log of a table?

texts:
I am using MS SQL Server 2008. I have a table in a database, in which records are being inserted and deleted.

E.g.

Initially the table is empty. A Java program (probably) inserts few records in it. Another Java program quickly access the table and deletes the records (All the records which were inserted). And then I manually run a select query on the table. So basically I can see only the "initial" and final state of the table, both of in which the table is empty.

I need to know that is there a way by which I can see those records, or at least check if something is being inserted/deleted?

Any help is appreciated.


texts: How to organise and understand a terribly modelled database

texts:
I have recently joined a company where there are hundreds of schemas and thousands of tables. All the schemas we have are from other vendors who have allowed us access to their database. The entire database is poorly documented (little to no documentation). Vendors also don't care for documentation, hence they don't provide us with any documentation.
The other employees themselves find majority of their time figuring out what the data represents and if the data is current. There are numerous front end tools (not built by our company) which allow us to pull data (which is correct and relevant). However, then the issue becomes that we don't know the schemas, tables or columns the front end is using to pull the data.
I cannot create new tables as the tables are owned by 3rd parties who have allowed us access to their database. How can I go about documentation and how do I figure out what schemas, tables and columns front end tools are using to pull data.
Tables don't even have foreign keys to figure out how they are linking to other tables. For example in tableA there may be a column called car_id, but in tableB there is a column called cid (which is the car_id). They both link together.


texts: How to know when to scale RDS MySQL instance? How to scale it?

texts:
I'm running 1 single medium RDS instance for my websites.

How do I determine when I will need to scale it up?
Is there a way to scale it horizontally or I can only upgrade it to a bigger instance?


texts:
I have a table i'm trying to update, where i'm adding a prefix to some records, depending on whether or not, the value appears in another table.
For example, I have a region_ref, and region_no, and I want to update all of the regions, if the region_no exists in another table.

The problem is, if i were to update all records, it would fail on some, due to the column length. However, i'm not updating these records as they dont appear in the sub select (these records dont appear in the sub select if i run it standalong.

update emp
set region_ref = 'UK_' + emp.REGION_REF
from EMPLOYEES emp
inner join REGIONS reg
on emp.region_no=reg.REGION_NO
where emp.region_no in (select region_no from UKREGIONS);

There are two records in the employees table which are already at the max value, which I shouldnt be updating anyway. Any ideas? Its almost like the update statement is being validated without the sub select.


texts: Postgres performance difference on insert

texts:
I was tried to partition one large table, and come with such Postgres behavior that I could not explain. Maybe you will have any suggestions?

I went quite straight forward way (i know that it is not most efficient, but still i had some time):

create table target_table  as select * from parent_table limit 0;
insert into target_table select * from parent_table where date_field >= '20120101' and date_field <='20120131' and tariff_id <> -1;
insert into target_table select * from parent_table where date_field >= '20120201' and date_field <='20120228' and tariff_id <> -1;
insert into target_table select * from parent_table where date_field >= '20120301' and date_field <='20120331' and tariff_id <> -1;
insert into target_table select * from parent_table where date_field >= '20120401' and date_field <='20120430' and tariff_id <> -1;
insert into target_table select * from parent_table where date_field >= '20120501' and date_field <='20120531' and tariff_id <> -1;
.....

And there is results how much time each command took to execute:

08:44:42  [INSERT - 8981055 row(s), 119.962 secs]  Command processed
08:46:36  [INSERT - 8222656 row(s), 114.057 secs]  Command processed
08:48:43  [INSERT - 8981454 row(s), 126.789 secs]  Command processed
08:51:02  [INSERT - 8929325 row(s), 139.815 secs]  Command processed
08:53:13  [INSERT - 9465383 row(s), 130.752 secs]  Command processed
08:55:25  [INSERT - 9302664 row(s), 131.911 secs]  Command processed
08:57:36  [INSERT - 9581798 row(s), 130.853 secs]  Command processed
08:59:58  [INSERT - 10080875 row(s), 142.071 secs]  Command processed
09:15:03  [INSERT - 9717698 row(s), 905.030 secs]  Command processed
10:24:22  [INSERT - 10169181 row(s), 4159.346 secs]  Command processed
11:33:11  [INSERT - 10000601 row(s), 4128.590 secs]  Command processed
12:38:36  [INSERT - 10576846 row(s), 3924.579 secs]  Command processed

I can't find the explanation why sometime inserts from the same table took 2 minutes and sometimes it took 1 hour! What could be reasons for such behavior?

Some info:

PG server running on amazon cloud xlarge EC2 instance.
I am the only one user and it was not overloaded with other tasks.
work_mem = '1GB'
parent_table is pretty large - 800 million of rows.


texts: ORA-02270: Error report: no matching unique or primary key for this column list

texts:
I have created two tables:

CREATE TABLE team (
    teamname        VARCHAR2(30) NOT NULL,
    carndate        DATE NOT NULL,
    teamnomembers   NUMBER(2) NOT NULL,
    charname        VARCHAR2(30),
    entryno         NUMBER(4) NOT NULL
);

COMMENT ON COLUMN team.teamname IS
    'Team name';

COMMENT ON COLUMN team.carndate IS
    'Date of carnival';

COMMENT ON COLUMN team.teamnomembers IS
    'Number of team members';

COMMENT ON COLUMN team.charname IS
    'Approved charity name';

COMMENT ON COLUMN team.entryno IS
    'Team leader for this team in this carnival';

ALTER TABLE team ADD CONSTRAINT team_pk PRIMARY KEY ( teamname,
                                                      carndate );

and another:

CREATE TABLE entry (
    entryno           NUMBER(4) NOT NULL,
    carndate          DATE NOT NULL,
    entrystarttime    DATE NOT NULL,
    entryfinishtime   DATE NOT NULL,
    entryplace        NUMBER(4) NOT NULL,
    charname          VARCHAR2(30) NOT NULL,
    compno            NUMBER(4) NOT NULL,
    eventypecode      CHAR(3) NOT NULL,
    teamname          VARCHAR2(30) NOT NULL
);

ALTER TABLE entry ADD CONSTRAINT entry_pk PRIMARY KEY ( entryno,
                                                        carndate );

ALTER TABLE entry
    ADD CONSTRAINT entry_charity_fk FOREIGN KEY ( charname )
        REFERENCES charity ( charname );

ALTER TABLE entry
    ADD CONSTRAINT entry_carnival_fk FOREIGN KEY ( carndate )
        REFERENCES carnival ( carndate );

ALTER TABLE entry
    ADD CONSTRAINT entry_team_fk FOREIGN KEY ( teamname )
        REFERENCES team ( teamname );

When i run the script in Oracle, I'm getting an error report saying no matching unique or primary key for this column list.

*Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement
       gives a column-list for which there is no matching unique or primary
       key constraint in the referenced table.
*Action:   Find the correct column names using the ALL_CONS_COLUMNS
       catalog view

and the error is reported at the Entry table, where i set the foreign key "teamname" at the last line. Why am i getting this error?


texts: I've been told that there's an oracle in-memory option for 12c and that it uses columnar compression to get some great query speeds. I'd like to leverage it for some business intelligence stuff. I've been searching google and the only thing I can find are announcements and news articles. Does anyone have any code snippets or any directions on where I should look to implement this or learn more about it?


texts:
The title pretty much asks the question, so I'll begin with an example.
I like to include a unique prefix in my table names, e.g. "con_contract".  In T-SQL I can then set an synonym like this:
CREATE SYNONYM con FOR dbo.con_contract

Then, I can refer to that table as dbo.con for convenience:
SELECT con.* FROM dbo.con

Is there anything similar in PostgreSQL?  I think not, having looked into it, so asking here is my last recourse!


texts: mysql doesnt have write access in data dir?

texts:
I am running a MySQL on my Windows 7 64 bit development machine at work and I can't seem to get around a database corruption issue. In my unit tests it is telling me that the storage engine (which is innodb) is issuing an error message of -1.

In my mysql error log I am reading a log message that says

130906 13:31:04  InnoDB: Operating system error number 5 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory. It may also be you have created a subdirectory
InnoDB: of the same name as a data file.
InnoDB: File name .\databaseName\employee_leastfavoriterealnumber.ibd
InnoDB: File operation call: 'create'.
130906 13:31:04  InnoDB: Error creating file '.\databaseName\employee_leastfavoriterealnumber.ibd'.
130906 13:31:04  InnoDB: Operating system error number 5 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory. It may also be you have created a subdirectory
InnoDB: of the same name as a data file.

but looking through my file permissions it is showing that both myself and SYSTEM have full access. I have run mysqld from command line as well as from a service, restarted my computer, and even reinstalled mysql in a vain attempt to be able to fix things so I can get back to work. I changed my my.ini file to see if that would fix it.

I am at a loss as to how to fix this? Did I miss something in my my.ini file that I should change? Should I be looking for gremlins mucking around with my user permissions? Is this the beginning of the end?


texts: Memory usage when refining temporary table into other smaller temporary table

texts:
I'm working on a algorithm to explore different connections between two data sets and I am wondering how smart InnoDB is with memory usage in regards to temporary tables.

I have a tree of conditions to explore:

    C1
   /  \
  C2  C3
 / |  | \
C3 C4 .....

My idea for exploring the tree would be to first create a temporary table, tempC1, from my data where C1 is matched. Then one, tempC1C2, from tempC1 where C2 is matched. Then two, tempC1C2C3 and tempC1C2C4, from tempC1C2 where C3 and C4 are matched respectively.

I got the idea of creating temporary tables since I'll need to do multiple selects for each table in order to score that particular combination of conditions but I am a little worried about memory usage.

Can I expect InnoDb to be smart enough and not store a bunch of duplicates in memory? Will an other storage engine give me better results?


texts:
is there a tool that spots redundant indexes in MySql?

e.g. if I have the following indexes:

index1(col1)
index2(col1,col2)

then index1 should be flagged as redundant.

any ideas?


texts: High CPU on Unloaded Server -- Performance Monitor

texts:
We recently upgraded a very lightly used SQL server (running < 5% CPU 99% of the time) from 2012 to 2016.   In conjunction, we moved the server from an AWS t2 to a t3 instance and installed the new NVMe drivers.
All is working well except the CPU has a baseline load of 50% (all from SQL server) now.   I have dug on the issue for 2 days and seem to have narrowed it to the following 2 threads in SQL which together account for 99% of cpu time.   They seem to be related to the resource monitor.
spid    cpu percentileCPU   physical_io memusage    cmd lastwaittype
36  6430844 71  0   0   RESOURCE MONITOR    PREEMPTIVE_XE_CALLBACKEXECUTE
14  2561536 28  0   0   RESOURCE MONITOR    ONDEMAND_TASK_QUEUE

I am very certain this server is not loaded (it is on, but no queries, not network load, etc --- It is a legacy server we must maintain for contract reasons, but has minimal clients)
This 50% CPU load is running up a large AWS bill as it is on t3 instance and being charged SurplusCPUCredits.
Can anyone help me understand what these Resource Monitor Processes are and provide any ideas on ways to eliminate / trouble shoot this performance issue?
Thank you Internet!


texts: Storing linked server in stored procedure variable

texts:
I have following setup:

Server 1: CGENMONOSAMP01

Server 2: CGENMONOSAMP02

I have Server 2 linked to Server 1 and link works, I can do queries like this on Server 1:

SELECT * FROM [CGENMONOSAMP02].master.dbo.sysdatabases

And it retrieves list of DB's on Server 2.

I am trying to create the following:

    DECLARE @SourceDatabase varchar(50) = '[CGENMONOSAMP02].Sample_DB'
    DECLARE @SourceHost varchar(50) = (SELECT SUBSTRING(@SourceDatabase, 2, 14))
    DECLARE @SourceDB varchar(50) = (SELECT SUBSTRING(@SourceDatabase, 18, 100))

--@SourceHost is CGENMONOSAMP02

--@SourceDB is Sample_DB

    IF NOT EXISTS (SELECT [name] FROM master.dbo.sysdatabases WHERE [name] = @SourceDatabase)
    BEGIN
        IF NOT EXISTS (SELECT [name] FROM @SourceHost.master.dbo.sysdatabases WHERE [name] = @SourceDB)
        BEGIN
        RAISERROR ('Error: Source Database[%s] is not existing.', 10, 1,@SourceDatabase)
        RETURN
        END
    END

This is being called on Server 1, it's supposed to take DB name which can either be name of DB located

on Server 1 as: "Sample_Table"

or it can be name of DB located

on Server 2 as: "[CGENMONOSAMP02].Sample_Table"

It's supposed to first check if passed name of DB exists on local server, if not - it should check if it exists on linked server.

I've tried the above and it gives me:

  Incorrect syntax near '.'.

I've also tried:

IF NOT EXISTS (SELECT [name] FROM [@SourceHost].master.dbo.sysdatabases WHERE [name] = @SourceDB)

But that gives me:

  Could not find server '@SourceHost' in sys.servers. Verify that the
  correct server name was specified.

Is it possible to make such reference?


texts: In Sql Server what is the difference between VAS and Buffer Pool?

texts:
My understanding:

In Sql Server,

Buffer Pool is where all the Data/Proc/Log Cache, etc are located.
Virtual Address Space is a logical location that refers to physical locations (either in RAM or page file).

But this statement is confusing to me:

  "..MultiPage allocation happens in the VAS Area and Single page
  allocation comes from Buffer Pool..."

What is the difference between Buffer Pool and VAS?


texts: SQL Server 2005: Full-text search on a multi-lingual table

texts:
I have an articles table as follows.

+-----------+--------------+---------+
| uid       | int          | PRIMARY |
| articleId | int          | UNIQUE  |
| lang      | varchar(2)   | UNIQUE  |
| title     | varchar(500) |         |
| body      | varchar(max) |         |
+-----------+--------------+---------+

I would like to implement full-text search on the article title and body.  The complication is that the title and body can be in one of six languages, indicated by the "lang" column.

I see there are noise word files for several languages, so depending on the currently set language, I would like to use the appropriate noise file when searching.

Does this require one catalog per language?  If separate catalogs are used, is it possible to, for example, not include French articles in the English catalog?


texts: Deleting records in relation with other tables

texts:
I need suggestions on deleting records in relation with other tables

For example i have a client in the clients table and this client has bills, payments, wire transfers etc... so this client id is in relation with other tables, it seems like i will never be able to delete this client because of past records, because if i delete this client other tables wont be able to inner join the clients table and all the records which has this client id will be vanished :)

I hope i was clear enough on my explanation,
My question is; what is the right way in a situation where i have to delete a client record?


texts: 10g to 11gr2 upgrade causes SYS.UTL_HTTP malfunction

texts:
After a 10g to 11gr2 upgrade, we are getting ORA-29273 errors when we try to use the SYS.UTL_HTTP package. Our upgrade list requires ACL to be disabled.

How can we resolve this problem without enabling ACL?


texts: LEFT JOIN on AFTER INSERT Trigger in MariaDB or MySQL?

texts:
Is there any simple way to access records related to a new one that is causing a trigger?

I am allocating expenses for vehicle use, by creating a general journal entry via AFTER INSERT Trigger when a new vehicle use record is created.

Problem is, I need a lot of info that is related to the new vehicle use record.

Currently, I am doing a number of sub-selects that I'm sure would be more efficient as a LEFT JOIN. For example, NEW.Driver is the ID of a User, the cost per kilometre is in a Vehicle_cost table keyed by NEW.Vehicle, and I also need the Name of the NEW.Vehicle, and the general journal Source and Destination accounts are referenced (in part) by NEW.Project.

I can post my ugly code, but I was hoping the view from 40,000 feet might inspire some better strategy than using a bunch of sub-selects.

Thanks!


texts: can't execute opatch even I'm in the oinstall group with -rwx--x---

texts:
I'm in $ORACLE_HOME/OPatch directory and
I type opatch.  It responds:

bash: opatch: command not found

if I do ./opatch:

/bin/sh: ./opatch: Permission denied

ls -l opatch shows -rwx--x--- so for group the user which is oinstall, it should have the execute right.

id -g does show me in the oinstall group.

So why I can't execute opatch in this case?


texts: SQL Server 2012 Installation failing on Windows 8.1

texts:
I keep on getting the following error message:

Came across several articles where they are asking to start from Group Policy Editor , however, since mine is Windows 8.1 and not pro version of windows, there is no group policy editor listed as shown below:

Please let me know how to fix this problem.


texts: Delete and Insert performance SQL Server

texts:
I have the below stored procedure that is taking close to an hour to complete. The result set is about 200K machines.

All I am doing is extracting a set of ids from a linked server, deleting those ids in a local server and then extracting all details for those ids from a remote server and inserting them. The INSERT takes 90%, DELETE table 9% and SELECT INTO 1%.

id is the Primary Key/Clustered Index.

    DECLARE @ProcessDate DATETIME
    SET @ProcessDate = CONVERT(VARCHAR(10), DATEADD(DAY, -3,
                                                    GETDATE()), 111)

    SELECT  DISTINCT id
    INTO    #temp_machines
    FROM    remote_server.db.dbo.table1
    WHERE   dt_modify >= @ProcessDate

    DELETE  FROM local_server.db.dbo.table2
    WHERE   id IN ( SELECT id  FROM  #temp_machines )

    INSERT  INTO local_server.db.dbo.table2
    SELECT  *
    FROM    remote_server.db.dbo.table1
    WHERE   id IN ( SELECT id  FROM  #temp_machines )

Any suggestions on improving performance?


texts: Top 2 rows per partition from an absolute value date difference

texts:
I have a database of security camera footage in a denormalized database. I have Locations which have multiple Cameras which take multiple images.

Location + Camera + image capture_date is the clustered primary key, and currently the only index on the table. The kicker is searching a single camera takes <1 millisecond from SSMS and ~70ms from my web application. My current working CTE solutions take around 3 minutes for three cameras.

To give an overview of the cameras at a location I need to select 2 images from each camera nearest a given date (such as the current date). Because of this I need an absolute value (dates before or after the search date are equally valid), thus I'm searching by the smallest ABS(@date -capture_date).

Here's the current code. It works but it's not SARGable and it's extremely slow. I also only need the top 2 rows per camera in the CTE, since there may be hundreds of thousands of images per partition.

DECLARE @date datetime,
        @location varchar(4)
SET     @date ='2011-12-13 12:00:00'
SET     @location='CS01';
WITH CTE AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION  BY Camera ORDER BY  abs(datediff(second,@date, [capture_date]))) AS Ranking
    FROM rs_camera_pictures
    WHERE
        location=@location)
SELECT * FROM CTE WHERE Ranking <= 2


texts:
We have a simple 1:1 Master-Slave replication configuration. The Master crashed, but we recovered and replication seems fine (confirmed by pt-heartbeat and SHOW SLAVE STATUS) but we've picked up one small, strange issue: there is an old, empty, and bypassed relay log hanging out on the Slave.

We are on relay.000725, and this one is relay.000724, so we're past it. What's more, it doesn't contain any actual queries. Just the meta data.

Can I delete this? What caused this?

> /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET
> @@session.max_insert_delayed_threads=0*/; /*!50003 SET
> @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER
> /*!*/;
> # at 4
> #140704  3:36:04 server id 16  end_log_pos 107    Start: binlog v 4, server v 5.5.38 created 140704  3:36:04 BINLOG '
> ZFm2Uw8QAAAAZwAAAGsAAAAAAAQANS41LjM4AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
> AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
> '/*!*/;
> # at 107
> #140721  4:22:51 server id 14  end_log_pos 1073741944     Rotate to bin-log.006142  pos: 4
> # at 148
> #140704  3:36:04 server id 16  end_log_pos 187    Rotate to relay.000725  pos: 4 DELIMITER ;
> # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET
> @@SESSION.PSEUDO_SLAVE_MODE=0*/;


texts: Why is failed job not logged in SQL Server Agent Error Log?

texts:
I have a failed job which is logged in SQL Server Logs but not logged in SQL Server Agent Error Logs. The job is executed as user: NT SERVICE\SQLSERVERAGENT. Limit size of job history and Remove agent history boxes are unchecked in properties of SQL Server Agent. Here are the results of SQL Server Logs:

When it comes to SQL Server Agent Error Logs, it does not contain any information about this failure. I thought that if a job fails then it should be necessarily logged into SQL Server Agent Error Logs. Why this happens?


texts: First metric in each time "bucket" for multiple IDs

texts:
I have the following table in PostgreSQL 12:
CREATE TABLE vehicle_fuel (
   vehicle_id     int NOT NULL
 , submitted_at   timestamp NOT NULL
 , fuel           float NOT NULL);

Currently there are approximately 1000 vehicle IDs each with an entry every 15 minutes or so, just over 100 million rows in total.
I'd like to be able to plot the fuel usage on a line chart for multiple chosen vehicles for any chosen time interval. I'd like to plot only n=200 points spread relatively evenly over the time interval for each vessel.
My current solution uses generate_series() to generate evenly spread "buckets" and then choosing the first timestamp within the "bucket" for each vehicle. E.g. for 10 vehicles, 200 points over the past 6 months
SELECT vehicle_id, submitted_at, fuel
FROM (VALUES (1), (3), (4), (34), (44), (56), (76), (79), (81), (83)) vehicle_ids(v)
         CROSS JOIN (SELECT generate_series('2020-05-17T00:00:00'::timestamp, '2020-11-17T00:00:00'::timestamp,
                                            '79488 seconds') AS bucket
                     LIMIT 200) AS buckets
         CROSS JOIN LATERAL ( SELECT vehicle_id,
                                     submitted_at,
                                     fuel
                              FROM vehicle_fuel
                              WHERE vehicle_id = v
                                AND submitted_at <= buckets.bucket
                              ORDER BY submitted_at DESC LIMIT 1) data
ORDER BY vehicle_id, submitted_at DESC;

However, this does not scale well with the number of vehicles requested, ~ 2300ms for the above query, significantly longer when I add more vehicles.
Is there any way I can make this faster? dbfiddle
I'm also using TimescaleDB if there is anything that can be utilised


texts:
I have a collection that has some fields that should be integers. Due to a software bug, some of these values are strings. I need to convert them. Here is an example document:

var coll = db.getCollection('info');
coll.find({ id: "985517" });

Result:

{
    "_id" : "41cbb4e48284c0612a1c4b3ee5e3e2720c01197a",
    "id" : "985517",
    "title" : "009_1a.jpg",
    "ext" : ".jpg",
    "content_type" : "image/jpeg; charset=utf-8",
    "sha1" : "41cbb4e48284c0612a1c4b3ee5e3e2720c01197a",
    "has_thumb" : true,
    "size" : 2917,
    "width_px" : "70",
    "height_px" : "69"
}

I try to convert width_px and height_px values into integers. My first naive try:

var coll = db.getCollection('info');
var bulk = coll.initializeOrderedBulkOp();
var counter = 0;
coll.find({ width_px: { $type: "string" }, id: "985516" }).limit(100).forEach(function(data) {
    var updoc = {
        "$set": {}
    };
    updoc["$set"]["width_px"] = parseInt(data.width_px);
    // queue the update
    bulk.find({
        "_id": data._id
    }).update(updoc);
    counter++;
    // Drain and re-initialize every 1000 update statements
    if (counter % 1000 == 0) {
        bulk.execute();
        bulk = coll.initializeOrderedBulkOp();
    }
    })
    // Add the rest in the queue
if (counter % 1000 != 0) bulk.execute();

The result is dissapointing:

{
    "_id" : "41cbb4e48284c0612a1c4b3ee5e3e2720c01197a",
    "id" : "985517",
    "title" : "009_1a.jpg",
    "ext" : ".jpg",
    "content_type" : "image/jpeg; charset=utf-8",
    "sha1" : "41cbb4e48284c0612a1c4b3ee5e3e2720c01197a",
    "has_thumb" : true,
    "size" : 2917,
    "width_px" : 70.0,
    "height_px" : "69"
}

So it was updated to double, instead of int32. I understand that in JavaScript, there is no separate double and integer type, just "number". But I need to update 100 000 documents and I want to do this efficiently.

But how?


texts: SQL Server : replication between two different locations

texts:
I have problem setting my replication between two servers on different locations (different network).

Steps I have done:

setup aliases in SQL Server Native Client configuration on both servers
setup ports on both server
created Windows accounts on both server and run SQL Server and SQL Agent under this account
created SQL Server users for replication with sysadmin permissions
checked that ports are opened and reachable
connected on both sides from Management Studio and connection works
I am almost sure that I have done this correct, because I have done it 100 times....

Then I made replication on publisher server using exact procedure as described here: http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/2/

In Replication Monitor I get error:

  Error messages:
  The process could not connect to Subscriber 'XXX'. (Source: MSSQL_REPL, Error number: MSSQL_REPL0)
  Named Pipes Provider: Could not open a connection to SQL Server [53].  (Source: MSSQLServer, Error number: 53)
  A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (Source: MSSQLServer, Error number: 53)
  Login timeout expired (Source: MSSQLServer, Error number: HYT00)

As I can see I have the same problem than in this post:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b5095a7f-e015-4086-a8cc-c31abb022aa4/transactional-replications-problem?forum=sqlreplication

But there were no solutions to it...


texts:
Apparently, a former DB administrator configured our main DB server (located in Germany) so that it only would be accessable from certain IPs. Now we just added a new host (which needs to access that data) and for the life of me I cannot get it to do so. PHP-programs trying to connect just clock-out for about 30 seconds and then they time out with the following error:

  [19-Jan-2016 13:35:51 America/New_York] PHP Warning: mysql_connect():
  Connection timed out in /home/criterion/public_html/main_sms.php on line 10

I checked the IP tables and added the IP of the new client, to no avail.

Can anybody tell me WHERE is the "allowed IPs" list or something of that sort that grants or denies access to the database?

I did a " select * from user_privileges;"  and it listed several records.  The funny thing is that among the IPs listed there were not the ones that DO work. So that tells me that is not the place.  I must be looking in the wrong place.


texts: Backup MySQL databases to external hard drive then restore

texts:
I have some issues with my new ASUS laptop. I'm running on Windows 10 and the helpdesk told me that they will reset the computer with it's original OS (Windows 8.1).

I run PHP 5.6.22 over fast CGI and PHP 7.0 over fast CGI with MySQL 5.7 on my local machine.

IIS : 10.0.10586.0

I use phpmyadmin 4.6.2

Does anybody have an idea how to export my databases to an external HDD and recover them after on my computer after a possible format of my PC?

Exporting them through phpmyadmin is really long because I have some large databases.

If someone have an idea or some links to share I will really appreciate your help.

I just use my databases, but I don't know anything about this kind of issue.
I've some innoDB DB, but I'm not sure about all the DB types.

I think that copy and paste my db's folder will not help...am I wrong?


texts: How to load entire database into memory?

texts:
I want to load my entire database into memory, but how can I do this? I have about 256 GB memory and my database is about 200 GB, so I can easily handle in memory.

When I do select count(*) from table1 sqlserver automatically load table into memory and after this I'm able to use table very fast, but I want to know how can I load my entire database into memory?

If I do select count(*) from on each table I'm able to work faster but is there any other way to load entire database to memory? I want to load the entire database by a single command, not select count(*) from table by table.


texts: can we use MySQL for a large data reporting application?

texts:
We are working on a web application with a big database, that's growing exponentially.

Since we start the dev 4 years ago, it is difficult to change the database to a new architecture, actually we are using a Mysql with Master/Slave configuration on AWS EC2.

Recently we built a reporting suite app for our clients based on the Master/slave configuration, here is

our approach:

   Main App
      |
    Master  <=>  Slave
                   |
             Stored procedure  =>  | Database_client_1
                                   | Database_client_2
                                   | Database_client_3
                                   | Database_client_n

Our database have a complicated schema, so EVEN with this solution some queries still take more than 10s.

What do you think about this configuration with (Mysql, Master/slave) for a Large application? and what do you suggest to use as a database for the reporting suite application specially the database will be also used for BI analytics.


texts: MySQL Row Based Replication, how to find failed entry in binary log?

texts:
I'm looking at moving my production systems from Statement Based Replication to Row Based Replication, but in my testing I am finding it very difficult to locate problems in the binary logs.

For example. I run a query like:

UPDATE tablename SET updateTime = NOW() where customerid = 100;

This statement updates 1000 rows. However on the slave I have removed one, so replication breaks and I get:

[ERROR] Slave SQL: Could not execute Update_rows event on table dbname.tablename;
Can't find record in 'tablename', Error_code: 1032;
handler error HA_ERR_KEY_NOT_FOUND; the event's master log dbmaster.010915, end_log_pos 82982844, Error_code: 1032

I then run mysqlbinlog against the binary log, and search for 82982844.

I find that in a long list like:

#161201  3:05:05 server id 21  end_log_pos 82977769     Table_map: `dbname`.`tablename` mapped to number 269238158
#161201  3:05:05 server id 21  end_log_pos 82978768     Update_rows: table id 269238158
#161201  3:05:05 server id 21  end_log_pos 82979752     Update_rows: table id 269238158
#161201  3:05:05 server id 21  end_log_pos 82980784     Update_rows: table id 269238158
#161201  3:05:05 server id 21  end_log_pos 82981808     Update_rows: table id 269238158
#161201  3:05:05 server id 21  end_log_pos 82982844     Update_rows: table id 269238158
#161201  3:05:05 server id 21  end_log_pos 82983872     Update_rows: table id 269238158
#161201  3:05:05 server id 21  end_log_pos 82984904     Update_rows: table id 269238158

which is followed by a whole load of:

### UPDATE `dbname`.`tablename`
### WHERE
###   @1=1
###   @2=2
###   @3=3
###   @4=4
###   @5='abcdef'
###   @6=2016-12-01 09:00:16
### SET
###   @1=1
###   @2=2
###   @3=3
###   @4=4
###   @5='abcdef'
###   @6=2016-12-01 10:01:08

Bear in mind that there is apx 1000 entries in the first section, and apx 1000 entries in the second section, how do I identify which row was missing?

Is there perhaps a better tool than mysqlbinlog to help in these situations?


texts: Using pt-table-checksum to compare a slave's DB to another slave

texts:
I understand that pt-table-checksum can do checksum compares between a master's DB and a slave's DB easily.

However, let's say your master has two slaves replicating from it for the same DB, i.e. Slave 1 and Slave 2.

Is there a way to use pt-table-checksum to run a direct checksum comparison between Slave 1 and Slave 2's databases? In this scenario, I am not as concerned with comparing any of the slaves with the master itself.

Thanks
IS


texts: Vcenter database log file keeps growing - shrinking the log doesn't help

texts: We have a vcenter database on an AG setup. The log file keeps growing sometime due to metrics which are enabled on the application end. They will be moving this soon to another db vendor to probably mitigate the situation, but when I try to run tlog backup by myself it is taking hours to finish(this is not the case when I setup a scheduled tlog backup via sql agent)  Also, when I try to shrink the log file after the backup, it is not getting shrunk. Is there another way to shrink the logs? Please let me know


texts: Selecting attributes from multiple tables via foreign key

texts:
Apologies if this question has already been answered. Couldn't find the answer myself.
I'm trying to work out the SQL to extract data from one table which is being referenced from another table. My explanation might be terrible due to looking at it way too long, so see my tables below:

Table 1
Table 2

(PK) ID (int)
(PK) ID (int)

Att_1 (str)
(FK) Att_1 (int)

Att_2 (str)
(FK) Att_2 (int)

Att_3  (str)
Att_3 (str)

Essentially I'm trying to select all from table 2, but table2.att_1 and table2.att_2 are foreign keys of table1.id. What I want to achieve is extract the following:
Value of Table2.ID
Table1.Att_1 where Table2.Att_1 = Table1.ID
Table1.Att_1 where Table2.Att_2 = Table1.ID
Value of Table2.Att_3
The final SQL will be used in a PHP script, so if its not possible to do this in a single line of SQL, I can split the requests up one by one. Just looking for some good old CPD.


texts: pgAgent doesn't run on another database than "postgres"

texts:
I run a postgreSQL 9.3.3 database and I want to do some automatic Tasks with pgAgent, but it doesn't run on another database than "postgres" db. What can I do to run pgAgent on another db ?

thanks !


texts: CONVERT() returns garbage with CLOBs in Oracle

texts:
I am looking to repair a table of data in Oracle, ideally via unprivileged SQL, which has had UTF-8 data inserted into a UTF-8 database, using the Latin-1 character set by mistake.

The symbol β GREEK SMALL LETTER BETA should have gone into the database, but instead the two characters Î² have gone in...as the two UTF-8 characters
Î LATIN CAPITAL LETTER I WITH CIRCUMFLEX followed by ² SUPERSCRIPT TWO.

This example code demonstrates the problem and the fix, however it only works with VARCHAR columns.  As soon as a CLOB is used, the conversion fails:

-- This must return AL32UTF8 for this example to be valid
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

CREATE TABLE EXAMPLE (T VARCHAR2(20));
INSERT INTO EXAMPLE (T) VALUES ('Example Î²');
SELECT T FROM EXAMPLE; -- Should return 'Example Î²'
SELECT CONVERT(T, 'WE8ISO8859P1') FROM EXAMPLE;
UPDATE EXAMPLE SET T=CONVERT(T, 'WE8ISO8859P1');
SELECT T FROM EXAMPLE; -- Should return 'Example β', problem fixed
DROP TABLE EXAMPLE;

However if the VARCHAR2(20) is changed to CLOB then this no longer works.  CONVERT() returns garbage characters.  I can use TO_CHAR() to work around the problem, but eventually I get an error that the CLOB is longer than 4000 chars so TO_CHAR() fails.

Is there a way to get the above example working, when using a CLOB column that's longer than 4000 chars?


texts: VS2012 setup spoils SQL Server 2012 Data Tools for everybody or just me?

texts:
On Windows 7 Prof I have installed SQL Server 2012 R2 with, as I am pretty well remember, client tools.
Then, I tried to install Visual Studio 2013 Ultimate but failed.
Then I've installed VS2012 Prof (30-day trial).
Now, on launching SQL Server 2012 (i.e. VS2010 Shell)) Data Tools --> New Project I am getting:

Fig.1.1. On launching SQL Server 2012 (i.e. VS2010 Shell)) Data Tools --> New Project
and on pressing OK button

Fig.1.2.
telling:

"The operation you are attempting requires Microsoft SQL Server Data Tools to be installed"  though I am somewhat sure I'have installed SQL Server Data Tools...

Also, a book on SQL Server 2012  I am reading tells me that in such situation I should see (to choose) "SQL Server Database project" template (which is absent in my case)
When I launch VS2012 --> choose New Project ,  I see SQL Server Database project template

Fig.2.1.
but on pressing OK button, I get

Fig.2.2

"This version of SQL Server Data Tools is not compatible with database runtime components installed on this computer"
Also, I cannot rely on this since I am developing against machines with SQL Server 2012 but without VS2012

After installing Download SSDT for Visual Studio 2010 then Download SSDT for Visual Studio 2012 from Download SQL Server Data Tools - October 2013, everething seems started to work but I am eager to understand whether VS2012 setup damages SQL Server 2012 (i.e. VS2010 Shell) Data Tools for everybody or just me?


texts:
Please could you tell me the correct order of maintenance plan operations in SQL Server?

Check integrity
Update statistics
Rebuild indexes

Is correct this order?


texts: Does postgres have any configuration similar to mysql innodb_thread_concurrency?

texts:
InnoDB provides an innodb_thread_concurrency option  to limit the number of concurrent storage engine operations MySQL can perform at a time. Seems like InnoDB controls (schedules) execution of its thread.

Does PostgreSQL provide any configuration that allow us to limit the number of concurrent operations performed at a time?
Does postgresql controls execution of its child processes or does it delegate this task to the OS?


texts: Does SQL Server supports both encrypted and unencrypted connections at the same time?

texts: Is it possible to have encrypted and unencrypted connections on the same port (1433)? If so, how?


texts: How to set password for PostgreSQL database with a script?

texts:
In a bash script I'm writing, I need to create a database and set a password that is generated automatically by my script.

The problem is I can't find a way to set up the password without user interaction.

I need something like this:

$ createdb mydb -p $(cat password_file)

Any ideas?


texts: Do Hive Materialized Views respond to added partitions to source tables?

texts:
Hive Materialized Views support partitions,
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name
  [DISABLE REWRITE]
  [COMMENT materialized_view_comment]
  [PARTITIONED ON (col_name, ...)]
  [
    [ROW FORMAT row_format]
    [STORED AS file_format]
      | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]
AS
<query>;

Hive supports "incremental changes",

Hive supports incremental view maintenance, i.e., only refresh data that was affected by the changes in the original source tables. Incremental view maintenance will decrease the rebuild step execution time. In addition, it will preserve LLAP cache for existing data in the materialized view.

From the Release Notes,

[HIVE-18839] - Implement incremental rebuild for materialized views (only insert operations in source tables)
[HIVE-18321] - Support REBUILD for MVs backed by custom storage handlers

But it's not clear if that will work with partitions.
If my source tables used in <query> have a partition added to them, will the MATERIALIZED VIEW update for the new partitions efficiently?


texts:
Is findOne() in MongoDB deprecated? I am asking this because when I use findOne() in WebStorm the IDE puts a line through findOne() and gives a message saying findOne() is deprecated and to find substitute.

I looked in the MongoDB documentation to try and find an answer but it did not mention anything about findOne() being deprecated.

If findOne() is deprecated what would be the best alternative to this method?

If it is not deprecated does anyone know why WebStorm gives this warning?

Any help with this is appreciated! Thanks!


texts: Using a keywords table with multiple other tables

texts:
Using MySQL

I need help determining how to model a Keywords table to three different tables (Products, Certifications, and Exams) - I have included my model which is using what I refer to as intermediary tables (what would they be called), but im not sure that is correct. Can someone tell me if this is correct, or show me an example of how to do it correctly?  Thank You!


texts:
I have a table in my database for storing supplier contacts and it is laid out like so:

Table: supplier_contacts
    contact_id  INT IDENTITY(1,1)
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NULL,
    supplier_id INT

I have an external VB.Net project that will be using the database, and will be passing in contact names as string with the first and last names put together.  For example:

Database:
    first_name = John
    last_name = DOe

VB.Net:
    name = "John Doe"

My thought in order to properly get the contact_id value is to just put together a stored procedure that can be called that does a search through the table using substrings and such.  My issue is I can't get it to work.

This is basically what I have some up with for a sort of "proof of concept" query to see if I can get this to work.

DECLARE @Contact   VARCHAR(150) --Value for the full contact name
DECLARE @ContactID INT

SELECT @ContactID = contact_id
  FROM supplier_contacts
 WHERE first_name = SUBSTRING(@Contact, 1, CHARINDEX(' ', @Contact) - 1) AND
       last_name = SUBSTRING(@Contact, CHARINDEX(' ', @Contact) + 1, LEN(@Contact))

My problem comes when the last_name column is blank, since it isn't a mandatory field.  So I am not sure how to set up the last_name condition to essentially be an optional check.


texts: Delete comment - actually delete it, or create `deleted` column and update it?

texts:
I'm really not sure if this is the right community to ask it in, but it seemed like it fits here.

But anyway, I'd like to know what's the usual approach of deleting a comment from database through website?

What I mean: when the user decides to delete his comment for example, does the comment actually get deleted from the database, or do developers create a column like deleted - int(1) default 0, where 0=not deleted, 1=deleted, and that column is being updated from 0 to 1 when a comment is being deleted? In this case, only comments where the deleted column is 0 would be displayed on the site.

Or is there no usual approach for this at all?


texts:
I want to design a user tracking system between different tables/models. I want to track the user's progress. There is a Module table which is related to sections and sections are related to sub-sections. Each sub-section can relate to different independent tables. The independent tables store different items (slides, videos, texts) which the user can go through in a linear flow just like a Coursera course. The flow will be predetermined by us but can be changed by us. So, the linear flow between the sub-sections can't be hardcoded. I also need to keep track of the user's progress through these sub-sections.

For Example:

A sub-section can point to game table where the user_info, game_score, game_completion_date_time will be stored or there might be case where the sub-section is pointing to slides table storing slide_text, slide_url and user_info.

I want to keep track of changes in those table so what should be my approach. Below i have posted an image in which i am approaching to the problem.

EDIT :

I want to track the user's progress.

A module is a collection of sections.


texts: Always Encrypted - Out of Memory Exception when attempting to encrypt a column in a table

texts:
Using Always Encrypted we are attempting to encrypt one column of type varchar(50) in a table that contains 5.5 million rows with the deterministic encryption. After a while the wizard returns failure due to the following error:

Exception of type 'System.OutOfMemoryException' was thrown..

Any ideas on how to prevent this error?


texts: How can I restore the sql_variant_property of baseType back to a table variable in SQL Server

texts:
Ok, I know the title is confusing but here it goes. BTW I am using SQL Server 2008

I have a table that I am trying to use as a storage container. It has 3 columns:

reportID integer,
dataLabel varchar(50),
dataValue sql_variant

The way I want to use this is to pass a reportID to a function and have the function return a table variable that is accurately typed. I have the process down EXCEPT for the accurately typed part.

Below is a script that will build a temp table, insert in some values and the pivot the values to the table variable that I would want to use. The problem is that the values type returned for each column is sql_variant and I cannot figure out how to convert them to their baseType. I have tried (unsuccessfully) to use the sql_variant_property but can't figure it out.

For example, in the final table I would like the myDate column to be a dateTime type and not a sql_variant type.

Are there any SQL gurus who can push me in the right direction?

create table #rStorage
    (
      reportID  bigint not null
    , dataLabel varchar (50) null
    , dataValue sql_variant null
    )
go

insert into #rStorage (reportID, datalabel, datavalue)
values (1, 'myInt', convert(int,5621))

insert into #rStorage (reportID, datalabel, datavalue)
values (1, 'mydate', convert(smalldatetime,getdate()))

insert into #rStorage (reportID, datalabel, datavalue)
values (1, 'myvarchar', convert(varchar(200),'testing Varchar'))

insert into #rStorage (reportID, datalabel, datavalue)
values (1, 'mydateNoTconverted', getDate())

select reportID
, dataLabel
, dataValue
into #tmp
from #rStorage

declare @QuestionList nvarchar(max)
, @qry nvarchar(max)

SELECT @QuestionList = STUFF(
 (SELECT ', ' + quotename(dataLabel)
 FROM #tmp
 GROUP BY dataLabel
 ORDER BY dataLabel
 FOR XML PATH(''))
 , 1, 2, '');

select @qry = '
 select *
 from #tmp

 pivot
 (
 max(dataValue)
 for dataLabel in ('+@QuestionList+')
 ) as q
 '

print @qry

exec sp_executesql @qry;

drop table #tmp
drop table #rStorage

The eventual goal is a udf since a stored proc can't return a table variable. So I tried this with a cursor and some dynamic sql. I think I am close but with the below function I get the error

  Only functions and some extended stored procedures can be executed
  from within a function. Severity 16 State 2

Code:

if exists (select * from sysobjects where  name = N'fn_pivotReportStorage')
   drop function fn_pivotReportStorage
go

create function fn_pivotReportStorage
 (@reportID bigint)
 returns @result table (reportID bigint)
as
begin

    declare @dataLabel sql_variant
    ,   @dataValue sql_variant
    ,   @dataType sql_variant

    ,   @alterTableStr nvarchar(max)

    declare thisCursor Cursor fast_forward for
       select   dataLabel
            ,   dataValue
            ,   dataType = case SQL_VARIANT_PROPERTY(dataValue,'BaseType')
                        when 'varchar' then convert(varchar(50),SQL_VARIANT_PROPERTY(dataValue,'BaseType'))+'('+convert(varchar(50),SQL_VARIANT_PROPERTY(dataValue,'maxLength'))+')'
                        else SQL_VARIANT_PROPERTY(dataValue,'BaseType') end
        FROM reportStorage
        where reportID = @reportID

    insert into @result (reportID) values (@reportID)

    open thisCursor

    fetch next from thisCursor into @dataLabel,@dataValue,@dataType

    while @@fetch_status = 0
    begin
            select @alterTableStr = 'alter table @result add '+ convert(varchar(2000),@dataLabel)+' '+ convert(varchar(2000),@dataType)
            exec sp_executesql @alterTableStr;
            select @alterTableStr = 'update @result set '+ convert(varchar(2000),@dataLabel)+' = '''  +convert(varchar(2000),@dataValue)+''''
            exec sp_executesql @alterTableStr;
     fetch next from thisCursor into @dataLabel,@dataValue,@dataType
    end
    close thisCursor
    deallocate thisCursor

 return
end
go


texts: Best way to shrink a DB after nulling data from varbinary(max)?

texts:
We have a database with a large amount of data stored in a field of type varbinary(max). At some point we can purge that data for most rows, but not all. Our plan is to make that field nullable, and simply null out the data when it is no longer needed. Once we do that we would like to reduce the size of the DB. What is the best way to accomplish this?

If there isn't a good way to reclaim space with the current setup, one idea I have is to move that data field to a separate table with just two columns: the key to the main table and the data field. Then we could simply delete the rows when they are no longer needed. (And then do some sort of shrink.) However, this would be a much more difficult change to make than simply making the existing field nullable.

Note: I actually don't care so much about making the database file smaller, but I do care about the newly freed up space becoming re-usable.

Over 90% of the DB size is this one field. I'm at 3TB already.


texts: Is it possible to catch the value of any field in a table? and once catched, can it be inserted into another table? SQL

texts:
I'm trying to catch a field from a table (User), for example the Id, to be inserted in another table.

Example:

DECLARE @tempUSERNAME NVARCHAR(1000) = 'HERE USERNAME'
DECLARE @tempUSERID NVARCHAR(1000) = 'HERE USERID'

SELECT Id, UserName FROM Users WHERE UserName = @tempUSERNAME;
GO

INSERT INTO Person(Id, FirstName, LastName, UserId) VALUES
(newid(), 'FIRST NAME', 'LAST NAME', @tempUSERID);
GO

And the error that I have is:

Must declare the scalar variable "@tempUSERNAME".

But is it possible, or could I catch it with a subquery?


texts: Query with LVARCHAR performance in Informix DB

texts:
In a maintenance project that it's working with IBM Informix Dynamic Server Version 11.50.FC6 we have to improve the performance of some queries.
One of this queries to the Informix DB fetches a LVARCHAR datatype column and it takes too long.

I've also tried the same query with different programs (DbVisualizer, DBeaver) and I've found the same problem that in my aplication.
However, I've tried Server Studio and the same query is instant, so I gather that the database is not the problem.

I would like to know why Server Studio works so good. And how to copy its behaviour in my application, with JDBCTemplate.


texts: Is there a drawback for using Aerospike when used in non-SSD server?


texts:
In debugging a problem the application is throwing the error:
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
In tracing through where it happens, I found that it is happening when calling an SP that is updating only a few records.  I saw comments related to this error being related to large where clauses or large volumes of data, but I can't understand why this error is happening in SQL 2019 for an SP that only updates a few records.  The logic essentially does:

Grab the information of the record into a temp table
Create a new database record with that info
Call another SP that returns a pre-formatted string
Update the record with some other custom info later on
Call another SP that is creating another singular record

That's all that it is doing, and I'm getting this error message and it is causing problems.  Any thoughts?


texts:
I need to transfer the database (as a whole) to another server, to make a duplicate database to setup another test environment.

I have two choices:

Make a full backup at source server/restore at destination server;
Detach at source server/attach at destination server.

What are the pros and cons of the two solutions according to my requirements?

I am using SQL Server 2008 Enterprise.


texts:
I have two tables:

STUDENTS
APPLICATION

A student can apply to 1-3 programs. Student_ID is a column on Application.
Table structure
APPLICATION
ID  | Student_ID   | ProgramName
1   |     65432    |  ComputerScience
2   |     65432    |  Math
3   |     65432    |  Cinema
4   |     65431    |  Theatre
5   |     65431    |  Cinema
etc...

The end goal is to get to this:
1 student per row, with all their application programs
Expected results
First     | Last     | Program1        |  Program2 | Program3
Robert    | De Niro  | ComputerScience |  Math     | Cinema
Sponge    | Bob      | Theatre         |  Cinema   |

Questions

Can this be achieved with one SQL statement?
What would it be?
If not, what would be the approach (and queries) to use an intermediate table?

If we're not sure each student has exactly 3 applications, (can have one or two) - does this change anything?

I am using SQL Query Activity on Salesforce Marketing Cloud. The documentation says:

SQL support for the SQL Query Activity is based on, but does not precisely correspond to, SQL Server 2005 capabilities.

Looked into PIVOT. I found that it is used with aggregation operators (AVG, COUNT, MIN, MAX, SUM, etc.) - which is not the case here.


texts: MySQL returning only 1 row, multiple entries in the database

texts:
I am creating a list of all publications for employees, and I am currently stuck on a query.  It runs with no syntactical errors, but it only returns one row.  The ERD for the relevant tables is as follows:

There is a many-to-many relationship between publications and employees, publications publication tags, and publications and publication types.  Each publication must have at least one tag and one type.

The Query

SELECT publicationsId, title, summary, image, publicationDate, citation, dateAdded, GROUP_CONCAT(DISTINCT tagName) AS tagName, GROUP_CONCAT(DISTINCT publicationType) AS publicationType, employee_employeeId
FROM publications
LEFT JOIN publications_have_publicationTags ON publications_have_publicationTags.publications_publicationsId = publications.publicationsId
LEFT JOIN publicationTags ON publications_have_publicationTags.publicationTags_publicationTagId = publicationTags.publicationTagId
LEFT JOIN publications_have_publicationTypes ON publications.publicationsId = publications_have_publicationTypes.publications_publicationsId
LEFT JOIN publicationTypes ON publications_have_publicationTypes.publicationTypes_publicationTypeId = publicationTypes.publicationTypeId
LEFT JOIN employee_has_publications ON publications.publicationsId = employee_has_publications.publications_publicationsId
ORDER BY dateAdded DESC

Extra Info

I was originally using INNER JOIN with the same effect.  I'm using PDO on the website, but I get the same result when running the query in phpmyadmin.

How can I get the publications information, and their related tags and types in one query?  Or should I just run separate queries to eliminate the complexity?


texts:
I have a mongoDB collection with 42M registers with 1M new registers every day.

We found a low performance on mongoDB querying.
showing in mongostat the fields: qr|qw ar|aw with values of 300-500.

First move was to check index and make new indexes for querys that may took so long.

Now we are getting a good performance most of the time.

But in some cases (random time) we get a lot of connections in pending status, all slow on querys.

But, the top command shows a good performance on processing speed and ram.

The question is:

How I can check more further what are the not optimized querys.
In new relic show the INSERT command in this specific collection take the 62% of "most time consuming", there is any way to speed this up?
And why can be the causes of the pending connections?
The strange thing is: when the numbers starts going up never stops until website crash.

EDIT:

We finally solved a big part of the problem doing this steps:

Move mongo to a new server withouth a openvz, seems mongo gives troubles with openvz
New filesystem and saas.
Create new indexes based on our needs.

Mongo is now running pretty well, so thanks all for your help :)


texts: SQL Server 2008R2 database migration to cloud: case-insensitive collation changed to case-sensitive

texts: Recently felt the fall out (crappy legacy code base) of a database migration by a third party vendor where the collation was swapped from case-insensitive to case-sensitive. I am wondering if the collation was deliberately changed during the migration? I would have thought that in any database migration the collation would remain unchanged (i.e. stay the same as the source database collation). Does the server which the database is hosted automatically override the collation maybe?


texts: How to install UTF8 locale on PostgreSQL?

texts:
I have a PostgreSQL 8.4 on Windows 7 machine, and my issue is that I have only the win1251 locale.

So when I create a database, I choose the UTF8 encoding and can select only lc_collate  = Russian_Russia.Win1251. And when I get any errors when I use JDBC I get unreadable logs.

How can I set a locale like en_US.UTF8 or ru_RU.UTF8?


texts: "Could NOT find Curses" error when installing MySQL from source

texts:
I am installing MySQL Server from the source distribution which I downloaded from:

  http://dev.mysql.com/downloads/mysql/#downloads.

I have downloaded the file (mysql-5.5.19.tar.gz).

I am using Debian/Ubuntu Machine. I am following the instructions as given in MySQL Documentation.

After issuing cmake . at the shell I am getting the following error:

-- MySQL 5.5.19
-- Could NOT find Curses  (missing:  CURSES_LIBRARY CURSES_INCLUDE_PATH)
CMake Error at cmake/readline.cmake:83 (MESSAGE):
  Curses library not found.  Please install appropriate package,

      remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.
Call Stack (most recent call first):
  cmake/readline.cmake:127 (FIND_CURSES)
  cmake/readline.cmake:217 (MYSQL_USE_BUNDLED_LIBEDIT)
  CMakeLists.txt:257 (MYSQL_CHECK_READLINE)

-- Configuring incomplete, errors occurred!

Why am I getting this error?


texts: Help with this query dealing with aggregates

texts:
I have a database that stores information about artists, bands and albums and I'm trying to select the band names and album titles for bands with more than 1 album.

band_id, name,            year_formed
1,       blink182,        1990
2,       fall out boy,    1997
3,       u2,              1982
4,       coldplay,        1990
5,       zach brown band, 2002

and album

album_id, title,                          band_id
1,        take off your pants and jacket, 1
2,        stars,                          3
3,        fbgm,                           3
4,        reach,                          3
5,        yup,                            4

This doesn't seem too hard of a problem but I cannot figure it out for the life of me.
The problem seems to be with getting both the album title and band name from the select statement.

SELECT b.name, alb.title
FROM band as b, album as alb
WHERE exists (SELECT * FROM album as al WHERE b.bid = alb.bid)

Returns the all band names and their album titles. My guess is to have the subquery handle finding the bands with more than one album but I just am at a complete loss for how to do this. Subqueries have been throwing me for a loop in general, so any nice description of how they work conceptually would be appreciated.


texts: Oracle 19c standby - redo transport errors

texts: I have a 19c RAC Standby database on RHEL6 using dataguard, however, I’m currently getting a “ORA-16737 redo transport device dir standby has an error” msg. I’ve tried recreating the password file and copying it over to the standby. I’ve reset the DG config. I’ve stopped and restarted the MRP service on the standby. Nothing seems to work. The log and trace files don’t give any extra info. It may relate to the scan listeners. Is there a check list I can run through to diagnose the issue? Any help appreciated. Thanks.


texts: How to keep the sync active with Bucardo

texts:
I have a master-master sync setup between 3 nodes or 3 different host machines using bucardo. All works good.

However when one of the database or a host is not reachable or goes down for some reason, the other 2 nodes go out of sync too. It stalls the sync and goes into inactive state.

I would like to keep the sync active between the other 2 nodes at the least. And the third one should catchup once its back online. Is this possible?

Let me know how should I go about setting up bucardo sync for such a scenario.


texts:
I have a single postgres database, with two users; Alice and Bob.

I would like to be able to do a NOTIFY alice_channel 'sensitive data' without Bob being able to sneakily LISTEN in just by guessing that the channel name is 'alice_channel'.

In practice the channel names are very hard to guess, but this is security through obscurity at best.

Am I correct in believing that there is no way to prevent a database user from using (abusing) LISTEN & NOTIFY? i.e. there does not appear to be any associated privileges that can be granted or revoked.

Is this a dead end?


texts: What are the disadvantages of using Couchbase's Global Secondary Indexes (GSI)?

texts:
Couchbase's documentation on GSI states that Global Secondary Index is a powerful solution for secondary lookup queries that are required for interactive applications that require low latencies.

The documentation also mentions that GSI has good write performance, so I am wondering if there are use cases (besides requiring MapReduce aggregations) where Views would be preferred.


texts: Migrating a tablespace to a different database on the same ASM

texts:
How might one go about copying or moving tablespaces between databases that uses the same ASM instances?

Might it be possible to cut some corners in RMAN or have ASM remap files belonging to a transportable tablespace?


texts: Single server / instance mongodb deployment

texts:
I need a database for backends (for iOS apps). Those are mostly low-load demand applications and I like mongo for it's schemaless-ness. I'm looking to host both db and app server on a small digital-ocean instances.

I plan to:

run it under systemctl on debian probably
have periodic backups (not sure yet how)
make sure it's only accessible to localhost

Anything else I need to do, like maybe enable journaling? (if yes, how do I do it?). What else do I need to do so that my single-server mongod production deployment is decent?


texts: Adding more space + bandwidth to a MongoDB cluster

texts:
I've read a lot about sharding and have gone through cluster setup process as well. What I can't get my head around is the actual database capacity after its sharded.

So say for instance I have one master server + 3 config servers + 5 shards.

If master has 30 GB available, each shard has 40 GB and each of the config servers have available space of 20 GB, what is my total capacity for a sharded collection in the cluster?

Is it 230 GB? How do I find this out? Is there a command that I can run?

Also, as I scale out, wouldn't that put more and more stress on the master node in terms of resources/bandwidth? Can I have multiple masters? Or replication across two sets of clusters?

Thanks in advance :D


texts: How do I track why my SQL Azure occupied space pulsates?

texts:
This is inspired by this StackOverflow question. I have this code:

SELECT CONVERT(INT, SUM(reserved_page_count) * 8 / 1024) FROM sys.dm_db_partition_stats

for detecting database space consumption and it returns exactly the same result as SQL Azure management portal pie chart. The problem is the number fluctuates greatly without apparent reasons - sometimes it's around 35% and sometimes it's around 54%.

This is kinda worrying - I don't get what happens and the major concern is that we suddenly consume all the space and our service comes to a halt.

I seriously tried to look into sys.dm_db_partition_stats and sys.partitions but they look like a pile of magic numbers to me.

What data should I use to find the root cause of the fluctuations?


texts: Select children from hierarchy (/a/b/c/d/)

texts:
I have to select from database which I did not create and I have this problem:

example schema:

Unit
----
id
HierarchyPath (example: '/42/48/63/84/')
UnitName (example1: "John" example2: "Team1")
typeId

UnitType
--------
id
Name (example1: "Name", example2: "TeamName")

I need to have result like

Team1, John
Team1, Joe
Team1, Johana
Team2, Bob
Team2, Bobek
Team2, BobaFett

I tried join tables like

UnitName_Teams.HierarchyPath = left(4,UnitName_Players.HierarchyPath)

but it crash on error

'Conversion failed when converting the varchar value '/42/48/63/' to data type int.'

How can I get children in this hierarchyPath 'data type' ?
I can select players from one team with for example

Like '/42/%'

But how to do it for all Teams ?!


texts: Does SQL Server handle databases on SMB shares different than on Hard Drives?

texts: Does it, for example, automatically recover from network hiccups, perhaps? If so, would masking the file share under a symbolic link change that behavior, or does SQL support symlinks to file shares, too?


texts: Full text search error: Invalid locale ID was specified

texts:
Query involving full text search is generating the following error message after restoring the database to a new server:

  Invalid locale ID was specified. Please verify that the locale ID is correct and corresponding language resource has been installed.

It was working alright on the previous server.
How can I resolve it? I'm on SQL Server 2008 R2
Thank you.


texts: How to relate LatLong and State columns on PowerBI

texts:
I have two separate tables, one with latitude and longitude columns (for locations in the USA), and the other with the US state abbreviation (TX, AZ, CA, etc).

Is there a way to count the number of rows in the LatLong table that fall within each state in the other table using PowerBI?