texts:
I have a SQLite table named hashbands that looks like this:
hashband | file_id | window_id
------------------------------
potato   | 0       | 0
potato   | 1       | 0

The table has 100M+ rows (lol). I need to find all hashbands that have two or more distinct file_id values, and then I need to fetch all rows that contain those hashbands and order the results by hashband. Right now I'm using this:
  WITH file_id_counts AS (
    SELECT hashband, COUNT(DISTINCT(file_id)) as count
    FROM hashbands
    GROUP BY hashband
    HAVING COUNT > 1
  ) SELECT hashband, file_id, window_id
    FROM hashbands
    WHERE hashband IN (SELECT hashband from file_id_counts)
    ORDER BY hashband

Does anyone see ways to expedite this query? Any pointers would be helpful!


texts:
I have no idea how to configure that pg_hba.conf file, to connect with my server via pgAdmin. Right now i was trying to add:

host all all host.name.org.pl trust

host all all host.name.org.pl md5

host user_name database_name ip_adress trust

host all all ip_adress trust/md5

and others..

I've got a postgresql server. My provider says that i must connect by adress: sql.user_name.org.pl and 5432 port. There is no ssl on the server. I've got user and password. Can somebody please help me, to deal with this?


texts: SQL Server Full Text Search - .rtf files incorrectly indexed by rtf tags

texts:
I've set up a full-text index on my SQL Server DB on a varbinary(max) column.  I have a type column specified, which contains the extension of the file, such a ".doc", ".pdf" etc.

However, I have noticed that when any .rtf files are indexed, SQL is including all of the meta information in the file (such as the RTF tag "listoverridecount0").

This is bloating the indexes quite a bit, and also means that searches will match on these tags (i.e. I can search on "listoverridecount0" and get EVERY .rtf returned).

Is there any reason that the iFilter for .rtf would not be removing the RTF Tags?

When I run this:

SELECT * FROM sys.fulltext_document_types WHERE document_type = '.rtf';

I get this:

document_type  .rtf
class_id       C7310720-AC80-11D1-8DF3-00C04FB6EF4F
path       c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\msfte.dll
version        12.0.6828.0
manufacturer   Microsoft Corporation

I have filed a Microsoft Connect bug as I don't seem to be able to find any workaround.  Presumably this is either a) Error with the RTF iFilter not removing tags, or b) Full Text index issue.

My SQL Server version is:

Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64)
Oct 25 2013 19:04:40
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.2  (Build 9200: ) (Hypervisor)


texts:
create or replace function Total_sal
 return varchar2
  is
 Total number;
begin
select sum(salary)into total from employees;
return total;
 end;
  /

 BEGIN
 DBMS_OUTPUT.PUT_LINE(TOTAL_SAL);
 END;
 /

Please correct if my understanding of this
?first we mention a return varchar2 data type
and then we pass salary into total (which is number datatype) as salary is number datatype  and again return total which is varchar2
which is after the select statement ?
do we need to bother about the datatype of the return total statement. ?

.


texts: join two tables and limit results by an ordered list

texts:
problem:
given is a table with language support (translations)
a user can configure a list of languages (order is priority)
now i have to get all rows which are part of app and the locale is in the list of preconfigured languages for a user;
only ONE result per property shall be returned
so for example the user configured de-AT, de-DE, de, en as preferred user settings
NOTE: not all values need to be defined!
the user requests all data for app='test'
the expected result is
 property, value
('father', 'Oida'),
('lastName', 'Nachname'),

Table for Multilanguage Data
CREATE TABLE IF NOT EXISTS `i18n` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `app` VARCHAR(20) NULL,
    `locale` VARCHAR(10) NULL,
    `property` VARCHAR(20) NULL,
    `value` VARCHAR(1000) NULL,
    PRIMARY KEY (`id`)
);

example values in i18n table
INSERT INTO `i18n` (app, locale, property, value)
VALUES
('test', 'de-AT', 'father', 'Oida'),
('test', 'de-DE', 'father', 'Papa'),
('test', 'de', 'father', 'Vater'),
('test', 'en', 'father', 'father'),
('test', 'fr', 'father', 'père'),
('test', 'en','lastName', 'last name'),
('test', 'de','lastName', 'Nachname'),
('test', 'fr','lastName', 'nom de famille'),

my idea was to put user config into its own table (if its possible without this it would be even better)
Table for User Config
CREATE TABLE IF NOT EXISTS `i18n_cfg` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `user` VARCHAR(20) NULL,
    `locale` VARCHAR(10) NULL,
    `priority` INT NULL,
    PRIMARY KEY (`id`)
);

example values in i18n_cfg table
INSERT INTO `i18n_cfg` (locale, user, priority)
VALUES
('de-AT', 'lm', 1),
('de-DE', 'lm', 2),
('de', 'lm', 3),
('en', 'lm', 4),
('de', 'hr', 1),
('en','hr', 2),

i'm not really getting to a point
i get only one result or way too much
NOTE using mysql 5.7 (if it would work on sql server too - with minor changes - it would be perfect)


texts: Need some recommendations on organizing data

texts:
I'm new here.  This question, to my mind, is allowed by the rules I saw in the "2 minute tour".  If I got it wrong, no need for nasty grams.  I'll delete if enough people say so.  But anyway, I have a bunch of data that I need to organize in a database.  This isn't the first I've ever constructed a database, but this might be the most complicated I've done and I want to "do it right".  So ....

I have a bunch of simulation runs.  To completely specify a simulation run, you need: code, code rev., date of run, begin time, time span, population model, time step, time step method, left boundary, and right boundary.

That specifies a run, but associated with each run is a set of data that comes out of analysis of the results: global max, final value, fitting coeff 1, fitting coeff2, and number of local maxima.

If number of local maxima is greater than zero, then I need to capture that info.  A local maximum will have a: value, location, and simulation run.

Well, I have figured out that I need to split off the local maxima into their own table, and link the simulation run of each maximum to the simulation run in the central table.

But what about that central table?  An aggregate key would have to have all of the information I listed above for specifying a particular run.  Is it a good thing to repeat all of that data in my local maxima table?  I thought about establishing a simulation run table that has all of the values needed to specify a run, and then an added unique numerical identifier for each run.  Then I could link to this identifier in my local maxima table.  This would cut down on the amount of data storage, but would it cause other problems?

Also, as I indicated, some of the data pertaining to a run defines the run while other data comes out of the analysis of the run.  Should I split these two types of data into two tables and link with that identifier I mentioned, or should I leave everything all together in one big table?

Thanks.


texts: How to repair/replace a single faulty table on a MySQL slave server?

texts:
My MySQL replication check tool suddenly started reporting a table with a wrong checksum on the slave, after months of perfect replication.

Is there a way to instruct the slave to reload the faulty table from the master?

This table is quite small (2% of the total DB size) so I'd like to avoid setting up replication all over again with a full mysqldump, if possible.


texts: show statement_timeout as millis in postgres

texts:
I want to create a view that will return the numeric value of the current statement_timeout in milliseconds. However, the result of the SHOW command is human-readable and may have different suffixes such as min and ms:
begin transaction;
set local statement_timeout = 120000;
show  statement_timeout;
--------------
'2min'

How to retrieve the raw, untranslated value?


texts: Make a relation of a table with three different category of id's table

texts:
I have an issue
There a table that's called "Routing" this table is for routing a task for three category of object
      -

Analyser (interne machine)
      -
subcontractor (externe )
Manually (interne worker)

how to design a relation between 5 tables

   1 - task table -> Routing table -> - analyser table
                                      - subcontractor table
                                      - Interne user table

Edit:, answers to questions in comments:

Can a task have many routings? Can a routing belong to many tasks or just one?
No, a task is routed to one routing (the routing is the manner to know where the task should be done).
Is there an Analyzer, a Subcontractor and an Interne (ManualWorker) table?
Yes, there is an analyser table, and subcontractor table, and interne table.
Can an analyser be part of many routings (of the same task)?
Yes, an analyser can be a part of many routing.
Can a subcontractor take part in many routings of the same task?
No, a subcontractor (or analyser or interne) take to one routing of the task (the task is routed to one subctr, or analyser or in interne).


texts: Table size is not changed after new column is added

texts:
I am trying to check how the table size is going to be increased after adding new column with default values.

I use the following default values: NULL, ''(empty string) and some text(some random text)` but it seems the table size is not changed.

I am using the following statements:

EXEC sp_spaceused '[Table_A]';

ALTER TABLE [dbo].[Table_A]
ADD [Loops] NVARCHAR(900)
CONSTRAINT [DF_Loops] DEFAULT('XXX') WITH VALUES;

EXEC sp_spaceused '[Table_A]';

ALTER TABLE [dbo].[Table_A]
DROP CONSTRAINT [DF_Loops]

ALTER TABLE [dbo].[Table_A]
DROP COLUMN [Loops];

but the sp_spaceused always returns:

name                                               rows                 reserved           data               index_size         unused
------------------------------------------------- -------------------- ------------------ ------------------ ------------------ ------------------
Table_A                                            73540994             8701688 KB         6658544 KB         2042976 KB         168 KB

Could anyone tell what I am doing wrong?


texts:
I'm a huge fan of CTE's. They are the best. You know it, I know it, we all know it.

Today, I got burned with a query that is about 1000x faster if the CTE is written to a real table.

The query itself is in the link, but I suspect it isn't important. To simplify, I needed a list of all the claims in an insurance policy where one claim met a certain condition (cat = 1).

A simplified version looks like this:

    with cat_claims as (select distinct pol_nbr from claims where cat = 1)

    select * from claims c
    inner join policies p on p.polnbr = c.polnbr
    inner join cat_claims cat on c.pol_nbr= cat.pol_nbr

which was super duper slow. If I rewrite it to look like this:

    with cat_claims as (select distinct claim_nbr from claims where cat = 1)

    select * into cat_claims
    from cat_claims

    select * from claims c
    inner join policies p on p.polnbr = c.polnbr
    inner join cat_claims cat on c.pol_nbr = cat.pol_nbr

    drop table cat_claims

it's way, way faster. This is odd to me.

Question Why would writing the CTE to an actual table provide a performance improvement?

Slow plan:

https://www.brentozar.com/pastetheplan/?id=HkyrpAtH4

Fast Plan:

https://www.brentozar.com/pastetheplan/?id=ByGkAAKrN


texts: Postgres - Error while adding constraints

texts:
Can anyone tell me what this error is? And How to resolve it?

ERROR:

  could not read block 27651 in file "base/114689/118316": read only
  4096 of 8192 bytes CONTEXT:  SQL statement "SELECT fk."_ID" FROM ONLY
  "public"."table1" fk LEFT OUTER JOIN ONLY "public"."table2" pk ON (
  pk."_ID"::pg_catalog.text OPERATOR(pg_catalog.=)
  fk."_ID"::pg_catalog.text) WHERE pk."_ID" IS NULL AND (fk."_ID" IS NOT
  NULL)"


texts: Could anyone help to index the below Query

texts:
SELECT ITEM.I_TITLE,ITEM.I_PUB_DATE,ORDER_LINE.OL_DISCOUNT
FROM ITEM JOIN ORDER_LINE ON ITEM.I_ID = ORDER_LINE.OL_I_ID
WHERE OL_QTY = '&olQty'
AND I_SUBJECT = '&ISubject'
AND I_COST = '&ICost';


texts: Using sql server alias via management studio on another computer

texts:
Suppose I setup alias in sql configuration manager, then on the same server, via management studio I can use the alias name to connect to the server.
However, on another computer when I open management studio, then can I use the alias name to connect? My understanding is that the SQL server IP or server name can be used as that is recorded in the DNS. Can alias work and if yes, then how?


texts: Running SSIS on SQL Server without SSIS installed

texts:
This is a follow-up to this question:  Running an SSIS package without the SSIS service installed

I have build an SSIS package on my local machine but when deploying to a server, I get the following error:  "To run SSIS package outside of SQL Server Data Tools you must install Derived Column of Integration Services or higher."

Can an SSIS 2012 package in fact be run on a server that doesn't have SSIS installed?


texts: MongoDb Sharding between replica and standalone server

texts:
So i got a replica set with three servers and a standalone server .
I have enabled sharding between them.

I inserted more data on replica primary server using "rockmongo".

But the chunks are not divided yet and they are still on my replica server

I got more than 50k records so my size in db folder is more than 65M.
the output of sh.status() is

        --- Sharding Status ---
sharding version: {
"_id" : 1,
"version" : 3,
"minCompatibleVersion" : 3,
"currentVersion" : 4,
"clusterId" : ObjectId("534ab99817bb6815bff6bd47")
}
 shards:
{  "_id" : "rsName",  "host" : "rsName/dhruvil-Inspiron-3521:22017,dhruvil-Inspiron-3521:25017,dhruvil-Inspiron-3521:27001" }
{  "_id" : "shard0000",  "host" : "dhruvil-Inspiron-3521:27917" }
databases:
{  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
{  "_id" : "zips",  "partitioned" : true,  "primary" : "rsName" }
    zips.zip
        shard key: { "state" : 1 }
        chunks:
            rsName  1
        { "state" : { "$minKey" : 1 } } -->> { "state" : { "$maxKey" : 1 } } on : rsName Timestamp(1, 0)

Any thoughts about what i am doing wrong???

Do we insert data on sharded servers in some other way than normal?


texts: Run DELETE on each INSERT to enforce row limit

texts:
Using PostgreSQL 11.
Consider a table like
CREATE TABLE "logs"
    (
      "id" INTEGER NOT NULL,
      "userId" INTEGER NOT NULL,
      "timestamp" TIMESTAMP NOT NULL,
      CONSTRAINT "PK_8d33b9f1a33b412e4865d1e5465" PRIMARY KEY ("id")
     )

Now, the requirement is that only 100 rows are stored per userId. If more data comes in, the oldest logs have to be deleted. If, for a short time, 101 rows are stored, it's not the end of the world, however. It's fine if the superfluous row gets deleted with a few seconds delay.
I cannot create a database TRIGGER. So, I need to write a query which is triggered on a log creation event on application layer.
Pure SQL is preferred over plpgsql.
This is the solution I came up with:
WITH "userLogs" AS (SELECT id, timestamp FROM "logs"
                    WHERE "userId" = $1
                ),
"countLogs" AS (SELECT count(id) FROM "userLogs")

DELETE FROM "logs" WHERE id = ANY
                (
                    SELECT id FROM "userLogs"
                    ORDER BY "timestamp" ASC
                    LIMIT GREATEST( (SELECT count FROM "countLogs") - 100, 0)
                );

Idea is: Always run a DELETE and base the decision if actually something has to be deleted on the LIMIT of a sub-query. If there are more than 100 logs, the sub-query will return the ids of the oldest ones to drop. Otherwise, LIMIT will be 0, the sub-query won't return anything and nothing gets deleted.
My questions are now:

Is it sensitive to run a DELETE query on each INSERT - even if it
doesn't delete anything?
Are there any performance implications here? (Or other pitfalls I might not be aware of?)
I am not quite sure if I need a LOCK. In my tests I could not
produce any unexpected behavior when running INSERTs in parallel,
however could it be that there are edge cases where I'd need a LOCK?

Edit: It's hard to predict how many times an INSERT will be run against that table. If all goes well (business-wise), it could be a few thousand times a day in sum - and a few dozens times per user each day.
Edit 2: timestamp values are not necessarily unique per user: there can be multiple log entries with the same timestamp and the same userId. It is expected that the table will get more columns containing what actually happened.


texts: How can I measure the performance hit of tablespace autoextensions?

texts:
I'm not a DBA, nor is there a DBA on the project I'm currently working, so we have tablespaces with autoextend on next 6m. I have no idea if this is low, although I believe so.

I want to measure what's going on with the current configuration and, if possible, tune the autoextension of my tablespaces so that a low value does not cause this to happen very frequently.


texts: calculate date difference in monthwise in sqlserver

texts:
I am using SQL SERVER. I want to calculate the exact date difference in month wise. I know there is one inbuilt function DATEDIFF for this but i want some custom fractional difference like:

sDate(yyyy-MM-dd)  eDate          output              formula
---------------------------------------------------------------------------------------------------------------
2016-03-03        2016-03-05      0.0967741           3.00/31.00 (daycount/lastday of the month)
2016-03-05        2016-06-05      3.0376343          (27.00/31.00)+(30.00/30.00)+(31.00/31.00)+(5.00/30.00)
2016-02-26        2016-04-03      1.2379310          (4.00/29.00)+(31.00/31.00)+(3.00/30.00)

So how this will be calculated? I can apply this logic by using a function but I don't want to do so. How can I apply this logic in a single query?

Your valuable suggestion will be highly appreciated.


texts:
I have received vulnerability on Microsoft SQL server 2008 (RTM)

  [high] [1433/tcp/mssql] TLS Version 1.2 Protocol Not Enabled
  TLS v1.2 is not enabled on this port.

Already enabled TLS 1.2 on OS level (Windows Server 2008 R2).

below are the details Product version:
Microsoft SQL server 2008 (RTM) - 10.0.1600.22(x64)
Product level: RTM Product Edition : Express edition(64bit)
Whether I need to go for SQL Server 2008 SP4 10.0.6547.0(First build supports TLS 1.2)

Please let me know how to resolve this issue.


texts: Why is DROP DATABASE taking so long? (MySQL)

texts:
New CentOS installation.

I was running an import of a large DB (2GB sql file) and had a problem. The SSH client seemed to lose the connection and the import seemed to freeze. I used another window to login to mysql and the import appeared to be dead, stuck on a particular 3M row table.

So I tried

DROP DATABASE huge_db;

15-20 minutes later, nothing. In another window, I did:

/etc/init.d/mysqld restart

The DROP DB window messaged: SERVER SHUTDOWN.
Then I actually restarted the physical server.

Logged back into mysql, checked and the db was still there, ran

DROP DATABASE huge_db;

again, and again I'm waiting already about 5 minutes.

Once again, it's fresh installation. The huge_db is the only db (other than system dbs). I swear I've dropped db's this large before and quickly, but maybe I'm wrong.

I've successfully dropped the database. It took something like 30 minutes. Also note that I think I was mistaken when I thought the mysqldump import was dead. The terminal connection was lost, but I think the process was still running. I most-likely killed the import mid-table (the 3M row table) and probably 3/4 of the way through the whole db. It was misleading that "top" showed mysql using only 3% of memory, when it seemed like it should be using more.

Dropping the DB ended up taking 30 min, so, again, I might not have had to restart the server and possibly could have just waited for the DROP to finish, but I don't know how mysql would react to getting a DROP query for the same db that it's importing via mysqldump.

Still, the question remains, why does it take 30min+ to DROP a 2GB database when all it should have to do is delete all the db files and remove all references to the DB from information_schema? What's the big deal?


texts:
I have a field to update which uses isnull(field,'') = 2. But I am getting error as the field has character value in it?

for exaample : field = 'E'
It says conversion failed converting varchar value 'E' to datatype int


texts:
MongoDB version: 3.2.11

Coming from using primarily PostgreSQL, I am new to MongoDB and am having trouble constructing complex queries in the required JSON format (where in SQL I would use CTEs). To provide a simplified example - there are a number of cars with their own unique identifier vin.  The cars ping when they are reserved, and when they are not reserved.

I have a number Objects in my collection engine that are structured like this:

{
    "_id" : ObjectId("1"),
    "vin" : "12345",
    "class_name" : "foo",
    "decoded_payload" : {
        "timestamp" : 1547329250,
        "status" : "LOCKED"
    }
}
{
    "_id" : ObjectId("2"),
    "vin" : "12345",
    "class_name" : "foo",
    "decoded_payload" : {
        "timestamp" : 1547329260,
        "status" : "LOCKED"
    }
}
{
    "_id" : ObjectId("3"),
    "vin" : "12345",
    "class_name" : "bar",
    "decoded_payload" : {
        "timestamp" : 1547329270,
        "status" : "UNLOCKED",
        "reservation_id" : "71"

    }
}
{
    "_id" : ObjectId("4"),
    "vin" : "12345",
    "class_name" : "baz",
    "decoded_payload" : {
        "timestamp" : 1547329280,
        "status" : "UNLOCKED",
        "reservation_id" : "71"

    }
}
{
    "_id" : ObjectId("5"),
    "vin" : "12345",
    "class_name" : "foo",
    "decoded_payload" : {
        "timestamp" : 1547329290,
        "status" : "NO SIGNAL"

    }
}
{
    "_id" : ObjectId("6"),
    "vin" : "12345",
    "class_name" : "foo",
    "decoded_payload" : {
        "timestamp" : 1547329300,
        "status" : "LOCKED"

    }
}

Records produced outside of a reservation are always of class foo (those during a reservation can vary but are never foo)

I am interested only in retrieving the status immediately before and immediately after each reservation on each vin.  This can be retrieved by taking the closest record of class foo to the min timestamp per reservation_id and vin,  as my objective is to retrieve all of the before/after statuses for each reservation_id made on each vin

The output for the above example would be something like

{"vin": "1234", "reservation_id": "71", "pre_status": "LOCKED", "post_status": "NO SIGNAL"}

So far I have this to get the reservations themselves, but would appreciate any recommendations on how to proceed further - would a forEach statement be appropriate in the above case?

db.getCollection('engine').find({"decoded_payload.reservation_id": {$exists: true}})


texts: In SQL Server, what happens when a constraint is added to a table with data in it?

texts:
Q&A for SQL Server migrated here from this question.

What happens if a "NOT NULL" constraint is added to a table to a column that already has NULL values?


texts: Can an index speed up a query with GROUP BY/aggregate over the whole table (no selectivity)?

texts:
Let's say i have a table with 3 columns a,b and c.

Could i possible speed up a query that looks like this by using index(es) ??

SELECT a,b,SUM(c)  # or AVG(c)
FROM table
GROUP BY a,b
ORDER BY a,b
;

If the above question is positive, what type of index do you recommend and how would this work?


texts: ola-hallengren index rebuild with multi thread and multiple days

texts:
Ola scripts for index maintenance work really well for smaller databases. For multi terabyte databases with big indices, many partitions, and a small interval of schedule times I observed that the script always tends to rebuild same indices and never finishes.

How can we implement the solution to capture and then run the index optimization for certain duration of days and then reset. Can we also have it run in multiple threads?

We did develop a solution to achieve it, but the only problem we have is updating it on regular basis.


texts: Retreive the list of Up/Down status of Oracle database using Shell Script

texts: I am trying to create a shell script for retrieving the list of Oracle database with status UP/DOWN.


texts: Add range of values under a column using rowid

texts:
How do i add range of values in a column based on row id?
For example: I have to add all the values under column "time" where rowid>=1 and rowid<=5.

Additionally is it possible to divide the then obtained sum by 3600?


texts:
I'm trying to bring incremental backups to one old setup. The script I create worked perfectly when run directly. However when I run it from crontab or when redirecting shell output all data is written to stdout.

mysqldump --compact -T /some/backups/$db/ $db

It looks like -T doesn't work correctly in this case. Can someone share any experience of creating backups in a -T way?

Thank you.


texts: Can I specify a name for the index which SQL Server creates for my unique constraint?

texts:
In SQL Server I deploy a table with a UNIQUE constraint:

CREATE TABLE [dbo].[Something] (
    [IdentityId]  UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID()
    -- Whatever else...
)

and this makes SQL Server to create a unique index to back the constraint. That index will have name generated automatically and looking something like

UQ__tmp_ms_x__1F778345BD

where UQ means "unique" and all the rest is well, random stuff to me. If I have two unique constraints for the same table they just look like two random strings which is not very convenient.

Is it possible to specify the name for the index which is created under the hood when a unique constraint is created?


texts:
My server is down now, 'couse indexes of 84GB DB are overloads disc.

Here is output of df-h:

/dev/ploop58097p1     193G  187G   16M 100% /
tmpfs                 2,0G     0  2,0G   0% /lib/init/rw
tmpfs                 2,0G  4,0K  2,0G   1% /dev/shm

DB retuns #1030 - Got error 28 from storage engine every time.
I can login in phpmyadmin, but don't know how to delete indexes of tables.


texts:
i have a query below

select emp_id,
  sum(case when leave_type = 'Planned' then 1 else 0 end) Planned,
  sum(case when leave_type = 'Not Informed' then 1 else 0 end) NotInformed,
  sum(case when leave_type = 'Informed' then 1 else 0 end) Informed
from table where activity_type='Leave'
group by emp_id

it gives below result

how to get count for above columns for 567 it will display 3
and for 619 it will display 4 in separate column please help


texts: What is the best design for storing courses subscription data?

texts:
Building an app that will manage lessons, students, courses. Each lesson belongs to a certain course. A student can subscribe to a single lesson or to a whole course.
So it seems straightforward that I need these tables(only essential columns):

Students
--------
Id[PK]

Lessons
-------
Id[PK]
CourseId[FK, NOT NULL]

Courses
-----------
Id[PK]

But how should I store subscriptions? Should I store them in one table that has two columns LessonId and CourseId only one of which will be populated for each subscription, or should I create two similar tables managing lessons and courses individually?

Subscriptions
-------------
Id[PK]
StudentId[FK, NOT NULL]
LessonId[FK, NULL]
CourseId[FK, NULL]

vs

CourseSubscriptions
-------------------
Id[PK]
StudentId[FK, NOT NULL]
CourseId[FK, NOT NULL]

LessonSubscriptions
-------------------
Id[PK]
StudentId[FK, NOT NULL]
LessonId[FK, NOT NULL]

Or may be you can advise another more suitable approach?

Additional info

Table(s) of subscriptions can be thought of as an intent of some person(student) to participate in a specific lesson(since he has an interest in subject) or in whole bunch of lessons that are grouped under the hood of a course.
A student can subscribe to as many lessons and courses as he likes, I think that each subscription should have a record of its own, so in case of a shared table there wont be any data overlapping.


texts: Export SQL Server varbinary field to string using Visual Basic script in SSIS?

texts:
I'm using SSIS to export data from a set of tables to flat files. The export is running great except for one table with a varbinary field. The export is running a Visual Basic script task to load the data into a DataTable object, then spinning through each row and writing the values out to a flat file. Here's the loop that gets the data from the DataTable and writes it out:

For Each row As DataRow In dataTable.Rows
    delim = ""
    builder = New System.Text.StringBuilder
    For Each col As DataColumn In dataTable.Columns
        ' By default, bit fields are exported as boolean (True or False).
        ' Cast them to int to avoid errors on import.
        If col.DataType Is GetType(Boolean) And Not row.IsNull(col.ColumnName) Then
            builder.Append(delim).Append(IIf(row(col.ColumnName), 1, 0))
        ElseIf col.DataType Is GetType(Byte) And Not row.IsNull(col.ColumnName) Then
            If isUnicode Then
                builder.Append(delim).Append(New UnicodeEncoding().GetString(row(col.ColumnName)))
            Else
                builder.Append(delim).Append(New ASCIIEncoding().GetString(row(col.ColumnName)))
            End If
        Else
            builder.Append(delim).Append(row(col.ColumnName))
        End If
        delim = delimiter
        Next
    writer.WriteLine(builder.ToString())
    Next

When I run my SSIS package, I get this error:

  System.InvalidCastException: Unable to cast object of type
  'System.Byte' to type 'System.Byte[]'

I thought that Visual Basic interprets the varbinary field as a Byte array, so why am I getting a cast error?


texts: Recover a MySQL InnoDB from ibdata1 that won't repair using innodb_force_recovery

texts:
We have a MySQL database that we have manage to grab the raw files from a server with a fatal hardware failure. However, this failure seems to have occurred when the DB server was running.

As a result we have the ibdata1 file, the ib_logfile0 and ib_logfile1 and all of the .frm files.

innodb_force_recovery does not work from 1 to 6.

Are there any tools on the market or methods that we can use to recover these databases?

Thanks in advance

PS - backups have also failed for a different reason


texts:
I have an ETL solution which we have been doing in conventional SSIS. The job of SSIS is just to create star schema from the application database to dimension and fact tables. There is very little transformations happening.

The problem that we are facing is that on local machine (desktop with 32GB of RAM) this job takes 16 hrs + to complete if we are lucky. On most days the job fails when it runs out of memory with below error:

  A buffer failed while allocating 104850152 bytes.

On the production server this job runs in 4 hrs, which has 64GB of ram.

We are trying to explore other possibilities to perform this ETL on local. Do you have any suggestion/strategies to leverage azure + python + HDInsights?
Any other suggestion are also welcome.


texts: Change defaults of SSMS generating script

texts:
In Microsoft Sql Server Management Studio most of times I need a set of advanced settings when generating script. For example I need to set "Script Drop and create" to true and "script extended properties" to false etc. Is there a way or registery items to change the default settings for ever so I don't set them again any time?

Exactly I mean these settings:

Database > Tasks > Generate Scripts > Advanced Scripting Options


texts: TDE Encryption Certificate Verification?

texts:
I'm attempting to audit/very TDE encryption of a database; significantly I've inherited a bunch of certificates and passwords and I'm attempting to update documentation to properly indicate the current/correct keys and passwords.
Working backwards from the DB > DMK > SMK:
Database
Querying sys.dm_database_encryption_keys and sys.certificates i get:
 DbName | encryp_state | type       |    name   | expiry
 NProd      3           CERTIFICATE     tdeCert   2014-10-10 18:10:55.000

Database is encrypted using tdeCert with expiry of 10/10/2014; correct?
DMK
Checking the same sys.dm_database_encryption_keys i see the following for master:
 DbName | is_master_key_encrypted_by_server
 master   1

Does this indicate that the DMK is secured by the SMK?
SMK
Performing a fresh backup of the SMK successfully indicates that it is accessible, correct?
-- Creates a backup of the service master key.
BACKUP SERVICE MASTER KEY TO FILE = 'J:\SQLKeys\2021_04_22-service_master_key.key'
    ENCRYPTION BY PASSWORD = 'supersecurepassword';
GO

DMK Confusion
When i run the following query:
SELECT
    b.name,  a.crypt_type_desc, b.*, a.*
FROM
    sys.key_encryptions a   INNER JOIN sys.symmetric_keys b    ON a.key_id = b.symmetric_key_id
WHERE
    b.name = '##MS_DatabaseMasterKey##';
GO

I get two results, one indicating encryption by master key; another indicating encryption by password. Is this expected? Does this indicate the SMK or password can be used to decrypt the DMK?
Putting Everything Together
Is it possible to perform a new backup of the SMK, and a backup of TDECert, and then have all of the required certificates and keys to properly restore the database if needed?

Backup SMK
Backup TDECert

SQL:
 -- Creates a backup of the service master key.
    BACKUP SERVICE MASTER KEY TO FILE = 'J:\SQLKeys\2021_04_22-service_master_key.key'
        ENCRYPTION BY PASSWORD = 'supersecurepassword';
    GO
  -- Creates a backup of the tde certificate and key.
    BACKUP CERTIFICATE tdeCert TO FILE = 'J:\SQLKeys\2021_04_22-tdeCert.cer' WITH PRIVATE KEY (FILE='J:\SQLKeys\2021_04_22-tdeCert.pvk', ENCRYPTION BY PASSWORD = 'supersecure');
        ENCRYPTION BY PASSWORD = 'supersecurepassword';
    GO

Additional Questions

Running the SQL: SELECT SIGNBYCERT( CERT_ID( 'tdeCert' ), 'test' ); indicates that I can properly decrypt everything?
Given the age of the certificate is there an advantage to rotating the keys?


texts: Adding new data source connection only contains sql server in dropdown in SSRS 2016

texts:
I just installed SQL Server 2016 Express and was going to add an ODBC connection to my SSRS instance. When I went to the dropdown to change the connection type from SQL Server to DB2, the only connection type available in the dropdown is SQL Server.

Anyone know how to fix this? Is this a limitation in the Express version?


texts: notes / description string in another table in a relational database

texts:
I'm primarily a non-database developer, however I am familiar with normalization techniques.

A coworker was suggesting that it is correct for a table to store 'description' and 'notes' as a foreign key rather than in the table for performance reasons, given that some of the records will contain NULL (or empty string in this case), and presumably will be split across pages.  This conversation occurred after we noticed that the integrity of the one-to-one table design was broken with a duplicate record.  It seems to me that if the developer who designed the column put it in the source table, all of the code that handles the one-to-one integrity would be unnecessary (and would have not caused this bug)

Pros of putting Nvarchar in a table

never have the risk of duplication
easier to work with, easy queries

Cons of putting Nvarchar in a table

after insertion, if the notes field grows, might cause a page split
and worsened performance when querying the table.

Additional question: when putting notes in a seperate table, is the design closer to 1st normal form?

related, which seem to defend putting the strings in the source table instead of a FK:
https://stackoverflow.com/questions/9540738/nchar-vs-nvarchar-performance
Are relations slower than a big, inefficient table?
https://stackoverflow.com/questions/1412040/do-i-need-a-separate-table-for-nvarcharmax-descriptions


texts: How simplify/optimize a search of keywords in two tables at the same time?

texts:
I'm trying to model a function which will be used by a search engine (of cars for the example).
The last version of PostgreSQL will be installed on a Windows server (I don't know server's specifications for the moment). The default Postgresql configuration will be used.
In this search engine, you can type many words and each word can correspond to a specific car part or a tag.
The function below seems to work but i have some doubts about its performance on the long term because the 'tag' table and the 'parts' table will constantly grow. We can consider that each table will have more than a million of row.
The fields cars.id, parts.id, cars_parts, cars_tags.id are declared as primary keys so there is an index on each of them.
Each item in "tags" table contain only one word. Items in "parts" can contain many word.
How can I improve simplify it and improve its performance ?

CREATE OR REPLACE FUNCTION search(keywords text[], itemsLimit int)
    RETURNS TABLE(nbr bigint, cars_id bigint, cars_name varchar, cars_description text) AS $$
DECLARE
    potential_parts text[];
    potential_tags text[];
    keyword text;
BEGIN
    FOREACH keyword IN ARRAY keywords
    LOOP
    potential_parts := array_append(potential_parts, '%' || keyword || '%');
    potential_tags := array_append(potential_tags, keyword || '%');
    END LOOP;

    RETURN QUERY
    SELECT COUNT(*) nb, id, name, description
    FROM (
        SELECT cars.id, cars.name, cars.description
        FROM cars
        INNER JOIN cars_parts CP ON cars.id = CP.car_id
        INNER JOIN parts P ON CP.part_id = P.id
        WHERE p.name ILIKE ANY(potential_parts)

        /* Duplicates required to get cars having (part + tag) on top,
         using an order by on the count */
        UNION ALL

        SELECT cars.id, cars.name, cars.description
        FROM cars
        INNER JOIN cars_tags CT ON cars.id = CT.car_id
        INNER JOIN tags T ON CT.tag_id = T.id
        WHERE T.name ILIKE ANY(potential_tags)
    ) ids
    GROUP BY id, name, description
    ORDER BY nb DESC
    LIMIT itemsLimit;
END;
$$ LANGUAGE plpgsql;

-- Example of call :
    SELECT * FROM search(array['Fast', 'Wheel-554D'], 10);


texts: Ltree query performance optimization of Postgres RDS DB

texts:
I have a AWS RDS m5.large Postgres 10.13 database that performs a lot of the following queries
SELECT "bundles".* FROM "bundles" WHERE "bundles"."version_id" = $1 AND (tree_path ~ ?) LIMIT $2
Here the table structure (roughly 1M rows):
CREATE TABLE "public"."bundles" (
    "id" int8 NOT NULL DEFAULT nextval('bundles_id_seq'::regclass),
    "cached_name" varchar NOT NULL,
    "uuid" uuid NOT NULL,
    "typology" int4 NOT NULL,
    "created_at" timestamp NOT NULL,
    "updated_at" timestamp NOT NULL,
    "resource_template_id" int8,
    "state" varchar,
    "resource_id" int8,
    "version_id" int4,
    "tree_path" ltree NOT NULL,
    "tree_ordinal" int4 NOT NULL DEFAULT 1,
    CONSTRAINT "fk_rails_a0e6c8e3c8" FOREIGN KEY ("resource_template_id") REFERENCES "public"."resource_templates"("id"),
    CONSTRAINT "fk_rails_02b50dac11" FOREIGN KEY ("resource_id") REFERENCES "public"."bundles"("id"),
    PRIMARY KEY ("id")
);

I have the following indexes (I created the version_id index after @nbk suggestion)

INDEX NAME
ALGO
UNIQUE
COLUMN

index_tree_paths_on_bundles_using_gist
GIST
f
tree_path

index_bundles_on_tree_path
BTREE
t
tree_path

index_bundles_on_resource_template_id
BTREE
f
resource_template_id

index_bundles_on_resource_id
BTREE
f
resource_id

bundles_version_id_idx
BTREE
f
version_id

bundles_pkey
BTREE
t
id

the problem is the poor performance of the overall system. Via advanced monitoring we see a very high value for current activity:

and it seems that the forementioned query have some sort of impact on the load by waits
What do you suggest to check? I'm not a DBA so I can't judge if those queries are efficent.
EDIT: as requested, here is the explain query ->
EXPLAIN SELECT  "bundles".* FROM "bundles" WHERE "bundles"."version_id" = 1641 AND (tree_path ~ '*{1}') LIMIT 1;
Limit  (cost=0.41..698.44 rows=1 width=225)
  ->  Index Scan using index_tree_paths_on_bundles_using_gist on bundles  (cost=0.41..4188.57 rows=6 width=225)
"        Index Cond: (tree_path ~ '*{1}'::lquery)"
        Filter: (version_id = 1641)


texts:
Ubuntu 16.04.2. MySQL 5.7.17.

During peek hours, my PHP sites suffer fatal error trying to connect to MySQL. Apache says

  Error 24 Out of resources when opening file

Ok makes sense, I get lots of traffic. I read up, and seems my open_files_limit var of 1,024 is too small (it really is seeing 5000 is default).

So after some reading, I ran mysql --verbose --help | less to find which config files I should go to. It gave me:

  Default options are read from the following files in the given order:
  /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

But, /etc/my.cnf and ~/.my.cnf didn't exist.

/etc/mysql/my.cnf did, and it looks exactly like this:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

When I appended open_files_limit = 2084 to the above /etc/mysql/my.cnf file. I was unable to sudo service mysql start MySQL. I had to remove the line, then I could start MySQL.

The only other MySQL configuration file I could find was /etc/mysql/mysql.conf.d/mysqld.cnf and I tried adding the open_files_limit line there and restarted, it restarted but the variable didn't chang.

What am I doing wrong? What do I do to increase my open_files_limit?

UPDATE

Seems the place to change the value was in /lib/systemd/system/mysql.service

Had to add LimitNOFILE=infinity save, then systemctl daemon-reload

There's lots of readings on the reasonings for this for some setups, and reasons not to use infinity.


texts:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'FullName'.

select FullName,
['1-Urgent'], ['2-High'], ['3-Medium (3000)']
from (select FullName,AltBusinessSeverity, total from Mdata) AS ST
PIVOT (
min(FullName) FOR  AltBusinessSeverity in (['1-Urgent'], ['2-High'], ['3-Medium (3000)'])
) AS PT


texts: What is the most compact way to calculate and then Update a maximum value for each day's data?

texts:
I have a database that stores records for each bundle of material.  A sample is taken from each bundle and sent out for assaying (for lead and sulfur).  I receive the assay data and write the assay value to the record.  I also take the maximum value for each assays and assign them to the day's "sample" record.

Generally, I only receive a single days assays at one time.  However, on occasion, I received the assays for multiple days (if there was a re-assay or assays were delayed).  When this happens, I end up assigning the maximum for all included days instead of for each day separately.

What is a better way to set up the following query to prevent one day's assays from being included as a maximum value for another day?

UPDATE pOut
SET
pOut.lead_ppm = (SELECT MAX(p1.lead_ppm)
 FROM production p1,  assay_temp t1
 WHERE (p1.tank = 'B' or p1.tank = 'C')
 AND p1.scheduled_pull_date = t1.formatted_date),
pOut.sulfur_ppm = (SELECT MAX(p2.sulfur_ppm)
 FROM production p2,  assay_temp t2
 WHERE (p2.tank = 'B' or p2.tank = 'C')
 AND p2.scheduled_pull_date = t2.formatted_date)
FROM production pOut, assay_temp tOut
WHERE pOut.tank = 'S'
AND pOut.cell_num = 1
AND pOut.scheduled_pull_date = tOut.formatted_date

Code has been updated to use differently named aliases and to include the needed parenthesis.  I believe now I will receive a single maximum for lead and for sulfur, likely from the earliest date.  I need to improve this so that I get a maximum value for each day with assays, and write that value to the proper day's "sample" record.


texts: SQL Server: I need to estimate execution time of a SELECT .. INTO .. statement

texts:
To take backup of a big table (using SELECT .. INTO .. ) took me almost 4 hours in a machine with 4 CPUs and 16 GB of RAM. No external application/process were accessing the table during the operation.

The table size was 220 GB and the SELECT .. INTO was a simple one (i.e. SELECT * INTO BACKUP_TABLE FROM ORIGINAL_TABLE)

This was a test environment and based on that, I need to work out an estimation of the execution time for the same operation in the production environment. The production environment has 40 CPUs and 64 of RAM.

CPUs are identical and I/O systems for both systems are the same. (i.e. disk type and disk layouts are the same).

Is it realistic to estimate the production's SELECT .. INTO .. will be complete in less than an hour, considering the production server has 10 times more processing power?

If not possible to answer this question based on the above, shall I re-run the test and collect some metrics? If yes, what those metrics should be?

Thanks in advance, for providing your thoughts on this!


texts:
How to store office hours in PostgreSQL rows, so that I can calculate the office hours.
Example:

We have open from 9:00 to 18:00 from Monday till Friday.
Saturday we have open from 10:00 to 15:00
from 24. Dec to 31. Dec we have open from 10:00 to 13:00 (but not on Saturday and Sunday)
bank holidays like 25/26 December are closed.
All above rules are valid until 31. Dec 2021.
Starting from 01. January 2022 we have open from 10:00 to 18:00 from Monday till Friday. Saturday is open like before.

I would like to store this data in rows, so that we can develop an interface for it.
And then there needs to be a method which uses the rows/rules to calculate the specific opening hours.
Different timezone are not important in this context.
I use PostgreSQL version 12.6. But if needed I can upgrade to a newer version.


texts: Why does PSQL not allow hash indexes with Bitmapscan?

texts:
For doing a simple equality query such as

EXPLAIN SELECT * FROM table where x = 1;

I see that a Bitmap Index scan is done using the BTree indexes. Apparently, Bitmap Index scans disable Hash indexes but I'm curious as to why? Wouldn't the query be faster if we can hash the pointer values and then run the page fetches afterwards?


texts: How do I figure out how Postgres was installed on my Linux system?

texts:
I'm using Postges 9.6 on Ubuntu Linux 16.  I'm trying to figure out how the Postgres server was installed so I can figure out a command to start and stop it.  It evidently isn't installed as a Linux service

[rails@server ~]$ sudo service postgresql status
Redirecting to /bin/systemctl status postgresql.service
Unit postgresql.service could not be found.

"which psql" reveals

[rails@server ~]$ which psql
/usr/bin/psql

I'm sort of at a loss for the next steps in figure out the command to start and stop it.


texts: tempdb logs on same drive as tempdb data or with other logs?

texts:
For many reasons I only have 3 hard drives (RAIDed and in an Always-On AG) for all my database files:

D: Data
E: Logs
F: Tempdb

Should the tempdb log file go on F: with the data file(s) or on E:?

My tempdb data file has the highest stalls by far, with the log file 4th out of 24.

In my limited DBA experience (I'm a developer) I would lean to putting the tempdb.ldf on E: as the writes will all be sequential.


texts: Why does "GRANT SELECT ... TO Role;" not allow members of Role to SELECT?

texts:
Please consider the following scenario on a Oracle 11g Database.
The user ADMIN performs the following:
CREATE USER Alice IDENTIFIED BY pwdalice;

GRANT CREATE SESSION TO Alice;

CREATE ROLE Viewer IDENTIFIED BY pwdviewer;

GRANT Viewer TO Alice;

GRANT SELECT ON Table_1 TO Viewer;

Then Alice logs into the database as 'Alice' and executes the following command:
SELECT * FROM Table_1;

Alice gets the following error:
SELECT * FROM Table_1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

I thought that granting privileges to a role would enable its member-users to get those privileges. However, this scenario shows that it is not so. What am I missing here? How can I grant SELECT to Alice using a role?
Update:
Following the answers, tried 3 fixes with no success
1) Using Fully-qualified Table Names
I missed to include the schema name in  SELECT * FROM Table_1; command. However, even after adding the schema name as shown below, still the error comes.
Alice executes:
SELECT * FROM ADMIN.Table_1;

Gets the error:
SELECT * FROM ADMIN.Table_1
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

2) Using a synonym for the fully-qualified table name
Unfortunately, this does not seem to solve the problem either.
Alice executes the following:
CREATE SYNONYM Syn_Table_1 FOR ADMIN.Table_1;
CREATE SYNONYM Syn_Table_1 FOR ADMIN.Table_1
*
ERROR at line 1:
ORA-01031: insufficient privileges

3) Altering the session
ALTER SESSION SET current_schema = ADMIN;

Session altered.

SELECT * FROM Table_1;

SELECT * FROM Table_1
              *
ERROR at line 1:
ORA-00942: table or view does not exist


texts: Computed Column Operand type clash on Date Column When Checking For Null

texts:
This error is generated when trying to create a table with a computed column from a Date field.

  Operand type clash: int is incompatible with date

If I comment out the computed column it works.

[DateInActive][Date] NULL,             -- Date origination
[IsValid] AS (IsNull(DateInActive, 1)) -- Failure here

The thought on IsValid is to simply return a bit of on|off for this field to signify that no in-active date has been reported and if so, it is valid.

The column will ultimately be passed on to JSON in a For JSON output as a is-a field for usage outside SQL.

Full SQL

CREATE TABLE [history].[PhoneBook](
    [PhoneBookId] [int] IDENTITY(1,1) NOT NULL,
    [PersonId] [int] NOT NULL,
    [PhoneId] [int] NOT NULL,
    [DateActive][Date] NOT NULL,
    [DateInActive][Date] NULL,
    [IsValid] AS (IsNull(DateInActive, 1))
 CONSTRAINT [PK_PhoneBook_1] PRIMARY KEY CLUSTERED
(
    [PersonId] ASC,
    [PhoneId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


texts: How to profile a particularly slow or inefficient query

texts:
I'm currently writing queries for my database but this is the first time I've dealt with large data sets of any sort, and I'm now finding that I need to improve the performance of my queries.

Scenario

I have a series of tables for purchases and customers as described below

users (user_id, forename, surname) 20k+ records

customers (customer_id, user_id, etc etc) 20k+ records

purchases (purchase_id, item_id, customer_id, price, timestamp, store_id) 150k+ records

deliveries (purchase_id, delivered_time etc) 150k+ records

store (store_id, name etc) <50  records

The query I am currently trying to perform should retrieve all customer names, the date of their last order, when that order was delivered and their total number of orders for all customers at a particular store whos most recent order was in the last 6 months. Here is what I'm currently using.

SELECT `customers`.`customer_id`, `forename`, `surname`, `delivered_time`, MAX(`purchases`.`timestamp`) as lastPurchase, COUNT(purchases.purchase_id) as totalPurchases FROM (`purchases`)
JOIN `deliveries` ON `purchases`.`purchase_id` = `deliveries`.`purchase_id`
JOIN `customers` ON `customers`.`customer_id` = `purchases`.`customer_id`
JOIN `users` ON `users`.`user_id` = `customers`.`user_id`
WHERE `store_id` = '1' GROUP BY `customer_id` HAVING lastPurchase >= '1372086055'

However, this is pretty slow and takes 1-2 seconds each time, and I can only assume this time would increase as the amount of data increases.

In an attempt to profile the query I have used EXPLAIN which seems to indicate it is searching through around 20k records in the deliveries table. What should my next steps be to optimize this query? How can I go about reducing the execution time?


texts: mySQL trigger to prevent insert won't work

texts:
The problem has been discussed before but the answers won't work in my case.

I have a table in my database which contains a numeric column called "value".

I would like to prevent an insert if the new.value is close to the value of a previous insert. So here is the main part of the BEFORE trigger:

first try:

IF EXISTS(SELECT * FROM mytable WHERE ABS(new.value - value) <= 1) THEN
SET new.value = null;
END IF

Result: for one insert, it will throw an error and it will work as expected. However, for multiple inserts at once, the inserts that should be prevented are there, with value 0.

second try:

IF EXISTS(SELECT * FROM mytable WHERE ABS(new.value - value) <= 1) THEN
SIGNAL SQLSTATE '45000';
END IF

Result: again, for one insert, it works as expected. However, for multiple inserts at once, it will also prevent the inserts that should be inserted.

I hope I was clear enough. Thank you in advance!


texts: Is ELT appropriate for life critcal applications such as Medical Systems when converting to a new application?

texts:
My small experience with ELT and reading makes it look like it is mostly for development.  Because the data is not cleaned up like in ETL that makes me think the new application will need to adapt its queries to the old data structure alongside the new application's new data structure.  And, then, eventually ETL takes place somewhere down the road, in the new database, overtime until you only have your new application, new schema, etc., the old data schema isn't used anymore.  It may still be in some tables somewhere, but unused, archive only.

For something like a hospital, ELT doesn't feel right.  I would think the hospital wants things cleaned up before the conversion.

Is ELT for going to a new application in a super critical life or death system?

Edit:  This article was suggested, http://www.jamesserra.com/archive/2012/01/difference-between-etl-and-elt/

After reading this helpful article, one of the ELT cases for preferring ELT is:

  The source database and the target database are the same

What does this mean?  The same database engine or the same schema?  New applications that replace 20-year-old systems don't usually have the same schema, so I do not understand.


texts: ODBC connection fail when running AD account for SQL Server services

texts:
I am following up a question from Remote backup to folder path in domain I asked earlier. I could not get my proxy SQL Server Agent user to work to back up to a remote location and have given up for the time being due to pressure to move on. However, I still want to back up to remote folders.

To that end I have created an AD user and put them into the local server which runs the SQL Server instance Admin group and given the AD user full permission to the remote folder and can browse and create a file at that location.

I then tried to change the MS Service from "Local Service" to the new AD user, using SQL Server 2008 R2 Configuration Manager this failed due to a log on error? Even after a number of tries and resetting the User password and then pasting it into the Configuration Manager this would not work.

I resorted to using the Windows Services to change the log on and all was fine. The instance worked ok and the remote backups worked as well. However, we then noticed a ODBC datasource issue from our TS into the SQL Server, it kept failing with "HY000 cannot generate sspi context", we Googled this and found a fix is to put the AD User into the Domain Administrator Group, which we did and the error disappeared. However, I have defeated my objective that was to use a standard user to run SQL Server services to reduce the security risk!

My question: is there another way to set the standard user / give it different permissions so it does not have to be a Domain Administrator so that ODBC datasource connections work? I am also wondering why I could not change the Service via SQL Server Configuration Manager


texts:
My ssrs server was moved to a different domain because of which the Users table need to be updated with new SID

current sid:

S-1-5-21-39949945-591092532-1226208319-1007

I want to convert this sid into varbinary(85) which would look something like this:

0x0105000000000005150000005AE2326D3D9AC66DD5CCABEE56040000

I had earlier found a link but unable to find it now.

Any tips?


texts: AWS - SQL Server Agent "white dot" problem

texts:
I'm trying to setup a SQL Server 2016 installation in AWS EC2 and can't seem to get the security group settings right. Please note the "white dot" in the screen cap below - I am unable to administer the agent when connecting from my laptop.

I've tried allowing inbound on 1434 in the security group, but no luck so far. I'd like to be able to stop/start the agent without needing to RDP into the server. It is worth noting that the "Laptop" behavior occurs even from other hosts inside the same security group.

The host is Windows Server 2012. I have also added a custom Windows Firewall rule allowing 1434 to listen. Netstat -a shows...

Proto  Local Address          Foreign Address        State
...
TCP    127.0.0.1:1434         hostname000:0          LISTENING
...

How do I remote administer the SQL Server Agent in this environment?

I had one of my DevOps guys confirm port 1434 is fully open; however the Server Agent behavior is unchanged.

I'm perfectly able to administer the service. But I need to RDP into the VM to do so. I'd like to have the quick visual indicator of service status & have the option to start/stop/restart from object explorer in a laptop session.

I logged in as sa for the purposes of the screencap. The behavior is consistent when logged in with sysadmin domain credentials.

I am able to connect via SSMS just fine. SQL Agent has no indicator however when I'm connected via SSMS running anywhere except local to the instance. From brief googling, it seemed like this may be similar to the "cannot connect to the DAC" issue to which teh interwebz suggested port 1434 config issues might be the root cause. Apparently that's not the cause so... to StackExchange for help!


texts: Select the minimum of a calculated distance value without sorting

texts:
I have a query dealing with geo distances. The query is super fast, returning in roughly .1175 seconds on my 2.24 million row table. However, I only need the lowest distance, and using the built-in order by is way too slow.

Is there any way to just keep track of a running minimum and just give me that?

For example, if I have these results:

city a - 45km
city b - 48km
city c - 12km

can I just have it give me the 12km, keeping in mind that all the distance values are calculated?

Here is the query that sorts:

SELECT
    City,
    (
        6371 *
        acos(
            cos(radians(-60.61384878636903)) *
            cos(radians(st_x(location))) *
            cos(radians(st_y(location)) -
            radians(112.80061386895574)) +
            sin(radians(-60.61384878636903)) *
            sin(radians(st_x(location))))
        ) as distance
FROM table_name
HAVING distance <  5
ORDER BY distance ASC LIMIT 1

The table structure is as follows:

id - int(12)
location - Point()
City - varchar(255)

The problem lies in that the order by flag takes way too long to sort the data and get the lowest. Can it just keep a running minimum and then just give me that without a major performance hit?


texts: Suppress database change message in SQLCMD.exe

texts:
I am writing a script generation script.
The script is generating list of DDL statments.

The util_generator.sql generation starts with database connection:

USE [TARGET_DATABASE]
GO
...
Many queries
Followed by print statements
...

Since this is a very long script, with unknown length.
We use SQLCMD.exe

sqlcmd -s sa\password -i util_generator.sql -r0 -o util_1.sql

The generated scripts util_1.sql looks something like this:

Changed database context to 'TARGET_DATABASE'.
CREATE TABLE ...
CREATE INDEX ...
CREATE VIEW ...

The problem is the generated script util_1.sql fails on unknown command Changed database context to 'TARGET_DATABASE'.

I need assistance to suppress Changed database context to 'TARGET_DATABASE'. message from SQLCMD.exe

Using SSMS util_generator.sql works good not producing any Changed database context to 'TARGET_DATABASE'. message.

But we cannot run util_generator.sql on SSMS from CLI for unknown length output scripts.

Please asvise how to suppress Changed database context to 'TARGET_DATABASE'. statement in SQLCMD.exe


texts: STRTOMEMBER function throws error about finding a dimension

texts:
I'm really stumped here...trying to create some year-to-date measures for a Cube, with NOW() specifying the current month and day (minus a two-day lag), with year being specified by the current member of the Year value on the hierarchy - and using STRTOMEMBER to paste together these bits of the year into a reference to a specific member of the dimension that can be cross-joined with measures to get annual value to-date.

This code works just fine when using STRTOMEMBER to specify the range...it lists every day between the first and the tenth, and the revenue for each day:

SELECT [Measures].[Revenue USD] ON COLUMNS,
{
STRTOMEMBER('[Time Dimension].[Year-Qtr-Month-Date].[Date].&[2017-01-01T00:00:00]') :
STRTOMEMBER('[Time Dimension].[Year-Qtr-Month-Date].[Date].&[2017-01-10T00:00:00]')
}  ON ROWS
FROM [CurrentCube]
WHERE [Time Dimension].[Year].&[2017]

But then the following does not work, it gives the error:

  "The dimension '[Time Dimenson]' was not found in the cube when the string, [Time Dimenson].[Year-Qtr-Month-Date].[Date].&[2017-01-07T00:00:00], was parsed."

I realize the date doesn't quite match up with the same range, but otherwise an identical string is being constructed. It makes no sense to me that the former would work and the latter wouldn't. Tried putting single quotes around it, too, which then complains that STRTOMEMBER expects a member expression but that a string or numeric expression was used. Makes no sense to me that the first option would work but the second one doesn't. Can't find much online to explain this error message, either.

SELECT [Measures].[Revenue USD] ON COLUMNS,
{
STRTOMEMBER(FORMAT(CSTR("[Time Dimenson].[Year-Qtr-Month-Date].[Date].&[" + [Time Dimension].[Year].CURRENTMEMBER.MEMBER_CAPTION + "-" + RIGHT("0" + FORMAT(CSTR(MONTH(NOW()-5))),2) + "-"  + RIGHT("0" + FORMAT(CSTR(DAY(NOW()-5))),2) + "T00:00:00]"))) :
STRTOMEMBER(FORMAT(CSTR("[Time Dimenson].[Year-Qtr-Month-Date].[Date].&[" + [Time Dimension].[Year].CURRENTMEMBER.MEMBER_CAPTION + "-" + RIGHT("0" + FORMAT(CSTR(MONTH(NOW()-2))),2) + "-"  + RIGHT("0" + FORMAT(CSTR(DAY(NOW()-2))),2) + "T00:00:00]")))
}  ON ROWS
FROM [DataInsight]
WHERE [Time Dimension].[Year].&[2017]


texts: SQL Server AlwaysOn to run updates and schema changes in 24/7 environment

texts:
First of all i have to say that this question comes from a software engineer-perspective. Unfortunately we have no DBA so we need to administer our own databases.

We have SQL Server 2012 Enterprise installed to run a database which should be available 24/7. The database is about 150 GB and some tables are containing billions of rows. Multiple services are hitting the database thousands of times per minute, to insert realtime measurement data. So when the database is down for half an hour, there is loss of measurement data for half an hour, which we can not afford...

In this situation we have two main issues:

When and how do we install Microsoft Updates?
How do we apply schema changes?

Windows Updates requires no further explaination i think. When new requirements come or existing requirements change, we sometimes need to do some schema changes. For example adding some extra columns, changing data types, resize varchar fields etc. Some of these changes take a very long time to run, or even timeout, because the database is Always under heavy load.

We are thinking to install an extra instance and enable AlwaysOn, to accomplish the following:

For Windows Updates, bring one server down, install windows updates, bring it back up, and then update the second instance.
For schema changes, bring one server down, apply schema changes, bring it back up, and then apply same changes to the second instance.

Can these two things be accomplished with SQL Server AlwaysOn, and is this a common approach? Does the data even gets synchronized aftwerwards? Or am i completely thinking into the wrong direction and are there better solutions?


texts: MySQL - Dumping 1TB database with SED to replace definer

texts:
Im going to dump a large database more than 1TB using MySQLDUMP (unfortunately I have to stick with mysqldump, I can't use mydumper or any other backup tools)
And I want to replace the definer on the fly during the backup.
So if I use sed with dump command, will it take more time than the normal mysqldump?
mysqldump db name | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > dump.sql


texts: How to insert/combine values from multiple tables?

texts:
I have multiple tables (12 for each month) that have 3 columns each ("code", "test_name", "value")

Example:

January
code | test_name | value
0001   name1       17
0002   name2       45
0005   name5       12

February
code | test_name | value
0001   name1       3
0002   name2       7
0004   name4       13

March
code | test_name | value
0001   name1       6
0006   name6       32
0007   name7       41

How do I merge or insert or combine it in sql into one table that would look like this:

Year
code | test_name | January | February | March | ...
0001   name1       17        3          6
0002   name2       45        7          0
0003   name3       0         0          0
0004   name4       0         13         0
0005   name5       12        0          0
0006   name6       0         0          32
0007   name7       0         0          41


texts: Access Form: New Record command not available now

texts:
I have an access database with two forms. When the form is opened I got a pop up saying acNewRec command is not available now. I can hit end or debug. If I hit end and manually re-open the form it works properly. It just gives the error eveytime the access is open. Any suggestions on how to fix this?

Private Sub form_load()
  DoCmd.Maximize
End Sub

Private Sub form_timer()
  [Clock1] = Now
End Sub

Private Sub form_open(cancel As Integer)
  DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub virusscanopen_click()
  DoCmd.Close Form.me.Name
  DoCmd.OpenForm "IT DEPARTMENT VIRUS SCAN FORM"
End Sub

Private Sub SIGN_IN_SUBMIT_Click()
  Me.Refresh

  If IsNull([LAST NAME].Value) Or IsNull(grade.Value) Or IsNull(issue.Value) Or IsNull(office.Value) Then
    Msbox "PLEASE ENTER ALL DATA FIELDS", vbCritical, "IT DEPARTMENT CUSTOMER INFO"
  Else

    txtmessage = grade.Value + Space(1) + [LAST NAME].Value

    MsgBox "Thank you" + Space(1) + (txtmessage) + "!" + Space(1) + vbNewLine_ + "Have a great day!" + vbNewLine_ + "Someone will assist you shortly." + vbNewLine_, vbInformation, "IT DEPARTMENT CUSTOMER INFO"

    [Date].Value = Format(Now, "mm/dd/yyyy")
    [Time].Value = Format(Now, "hh:mm:ss AM/PM")

    DoCmd.GoToRecord , , acNewRec

    Me.Refresh
  End If
End Sub


texts:
Hey guys right now I'm passing one value to my database, it's a random and unique 10 digit number.

Is there a way i could generate this number directly in my database each time a new row is created?

I'm Using PostgreSQL (pgadmin)

It looks something like this

id_Primarykey  Name  random number
1              x     1231233592
2              Y     5234234837
3              Z     3453450394

Said number has to be unique, 10 digits, and random (non sequential)


texts: Copy all data to slave before mySQL replication connect

texts:
I try to set up mySQL replication, however I have question before I connect 2 servers

Q1. Should I import all data from master into slave before I connect 2 servers?

ex. if My db have millions of rows, should I copy all data to slave first?

Q2. I have read few post about when disaster strikes on master, it switch to slave, so you have time to fix master. What kind of disaster could happen? I have set up mySQL only SELECT, ADD and UPDATE, just wondering what could happen?


texts: I know that block(multiple disk sectors) is the basic unit of data transfer for the virtual file system so, if NTFS uses 4 KB cluster size for files how does InnoDB access disk if it uses 8 KB page size?


texts: What's the point in SQL Server Central Management Server?

texts:
Online MSDN article "Execute Statements Against Multiple Servers Simultaneously (SQL Server Management Studio)" (SQL Server 2012) states:

"Note

Before you execute the following procedures, create a Central Management Server and server groups. For more information, see Create a Central Management Server and Server Group (SQL Server Management Studio) "

Though, I removed any CMS (Central Management Server) instance and still getting the same results over multiple SQL Servers on 2 differing machines

Seems like I am failing to find in online MSDN the illustration of the sense (or points) of an SQL Server Central Management Server.
Can you provide me with some?


texts: Sql Server Measuring Query Memory Consumption

texts:
I am optimizing a stored procedure in sql server 2005, and while I know of several ways to measure timing differences, I am not quite sure of a good way to measure memory consumption differences.

How do you measure memory consumption of a stored procedure in sql server 2005? My goal is to be able to convince myself something like "it is about the same as it was" or "it uses X times more memory"


texts:
I guess this is a pretty tough one. I want to get results from the database, per 5 rows max (so a limit of 5 each time), but each time I don't want to return incomplete "sets" either. Look at my table of forms:

+----+--------+------------+
| id | formID |  created   |
+----+--------+------------+
|  1 | 111111 | 2014-01-01 |
+----+--------+------------+
|  2 | 111111 | 2014-01-01 |
+----+--------+------------+
|  3 | 111111 | 2014-01-01 |
+----+--------+------------+
|  4 | 222222 | 2014-01-01 |
+----+--------+------------+
|  5 | 222222 | 2014-01-01 |
+----+--------+------------+
|  6 | 222222 | 2014-01-01 |
+----+--------+------------+
|  7 | 333333 | 2014-01-02 |
+----+--------+------------+

So I want to return my results in sets of 5 database rows BUT with complete forms. In this case I will have incomplete results, cause I don't have all rows of form with ID 222222 returned. So either I need to get 3 results (less than 5 but complete forms) or 6 (a bit more than 5 but also complete forms).

Anyone who has any idea how to accomplish this?

At the moment my query looks, more or less, like this:

SELECT `id`, `formId`, `created`
FROM `forms`
WHERE created >= $lastCreated
      AND id != $lastId
ORDER BY created
LIMIT 5;

(this is just an example so no worries about the sql injection vulnerability ;)). As you can see I check for the last returned row id and created column every time I run a new query, so I know where I ended. But like I said, this will return a complete form 111111 but an incomplete form 222222.


texts: Merge performs always additional INSERTS

texts:
I am facing the followign issue, my MERGE statements based on data (views) that do not deliver primary keys or any unique keys, always just ADD additional INSERTS on every execution to the target table.

Since I have used ALL COLUMNS in the comparison part, I was expecting that they are compared t the target and in case matched, NOT inserted but just left alone.

Instead the number of rows in the target keeps growing continuously with every execution.

Yes I know it might be better to deliver primary key. But I have not enough knowledge about the data delivered and in some cases it was not possible to find a primary key combination at all.

How can I prevent additional inserts in that case?

 MERGE INTO [Z].[IAPP] AS Target
    USING (
        SELECT
    [SXD],[UUZQ],[SSRTD],[AAFFT],[IID] From [Z].[vIAPP]
    ) AS Source ([SXD],[UUZQ],[SSRTD],[AAFFT],[IID])
    ON (Target.[SXD] = Source.[SXD] AND Target.[UUZQ] = Source.[UUZQ] AND Target.[SSRTD] = Source.[SSRTD] AND Target.[AAFFT] = Source.[AAFFT] AND Target.[IID] = Source.[IID])
    WHEN NOT MATCHED BY TARGET AND @Action = 'UI' THEN
        INSERT([SXD],[UUZQ],[SSRTD],[AAFFT],[IID])
        VALUES(Source.[SXD],Source.[UUZQ],Source.[SSRTD],Source.[AAFFT],Source.[IID])
    WHEN NOT MATCHED BY SOURCE AND @Action = 'D' THEN
        DELETE;


texts: Getting error while installing log4db2 utility

texts:
DB21034E  The command was processed as an SQL statement because it was
  not a valid Command Line Processor command.  During SQL processing it
  returned: SQL0601N  The name of the object to be created is identical
  to the existing name "LOG_CONF_BP" of type "BUFFERPOOL".
  SQLSTATE=42710

How to remove pre-existed "LOG_CONF_BP" of type "BUFFERPOOL" in db2 database? Is there any other way to define logging for my stored procedures?


texts: What's the optimal server configuration for fastest inserting in a MySql database in this scenario?

texts:
I have a MyISAM table partitioned by range with 90 partitions representing the last 90 days. I expect this table to receive somewhere between 500 million to 1 billion records before i start rotating partitions (dropping old ones and creating new ones).

What server configuration options would you put in your .my.conf file to ensure that the fastest possible insertion into this table was possible at all times?

Any other tips would also be appreciated.

EDIT: By 'fastest possible' i mean best possible insert speed and being sustained once it contains 100's of millions of rows.


texts: Converting HSTORE to an EAV in a view using "each" or "lateral"

texts:
I have a table with ~100M rows, all of which contain HSTORE fields.  I am creating a view that will return (convert) the hstore column to key | value columns and also add a few fields for filtering.

id | hstore                         id  | user_key | user_value
--------------------------          --------------------------------
11 | "a" => "2", "b" => "z"   =>    10  | a        | 2
                               >    10  | b        | z

This view will be used by an export tool which knows sql, but doesn't grok hstore or json.

There are two reasonably nice solutions I have found, both using each, but I am curious if there are any drawbacks to one approach over the other, especially considering the desire to remove empty rows.

$1) using each

To filter rows without user data I would need to compare the underlying hstore to empty (user_data != '' :: hstore).

CREATE VIEW v1 AS
SELECT
    t.id,
    t.filterable_column,
    s.owner,
    (each(t.user_data)).key,
    (each(t.user_data)).value
FROM
    public.hugetable AS t
        LEFT JOIN public.smalltable s ON s.id = t.s_id

$2) using lateral left join

In this case I can use an INNER join on the lateral to remove empty rows (rows with no user data).  Seems cleaner.

CREATE VIEW v2 AS
SELECT
    t.id,
    t.filterable_column,
    s.owner,
    u.key,
    u.value
FROM
    public.hugetable AS t
        LEFT JOIN LATERAL each(t.user_data) AS u ON TRUE
        LEFT JOIN public.smalltable s ON s.id = t.s_id

I can do explains on them, but I am not sure how to interepret since the difference seems small.  Perhaps they are the same, essentially, or one is fundamentally worse than the other (more work, loops, or inefficent).  I've no familiarity with each or lateral joins, but am reading up as I can.


texts: Is the MySQL replica's data consistent if the Master was switched to binlog_format = MIXED but the Slave's relay logs were rotated sometime later?

texts:
The accepted answer to this question is very helpful but it's not clear to me if one should be concerned about the replication that occurred from the time the Master's binlog_format was changed to MIXED to the time the Slave's relay logs were rotated.

There are no errors or warnings in the Slave's error log.

Both Master and Slave are MySQL 5.1


texts: Performing group by, Order by on a table starting from particular date to recent last date

texts:
Am sorry if this question sounds so dumb, please help.

I currently use the below MS SQL query to get the list of volunteers on each date and it works perfectly fine,

select Date, count(id) as co from volunter Group BY Date Order by Date

and the result set looks like this

----------
Date       | co
25/2/2016    8
5/3/2016     18
25/3/2016    28

i Now want to perform the same but from a particular date, say from 5/3/2016 to the latest date in the table or say till the last record in the table, how do i do this ? Thank you so much for your time


texts: Non matching attribute 'INSTANT COLUMN(s)' between partition and table in mysql 8.0.20

texts:
I am using MySQL 8.0.20. I found a solution that recommended to include algorithm=Inplace or algorithm=copy in alter table exchange partition statement explicitly, but my DDL statement doesn't seem to work, it gives a syntax error
alter table schema.tablename algorithm=INPLACE
EXCHANGE partition p20 with table schema.tablename_partition

Can someone point out what  I am doing wrong  here.


texts: Question about database structure for network visualuzation

texts:
Hello folks. I have a matrix I created in Excel that shows 17 nodes, each node's connection to every other node (if any), and that connections strength. Note the error in the image, cells that represent a node's connection to itself should be null not 0.

Where:

0 no connection
1 min connection
2 median connection
3 primary connection

I want to translate this matrix into a database I can use for network visualization but I need help with the structure.

I'd like to avoid having a record for each connection as that's 278 records for my limited dataset alone (final dataset will have 200+ nodes). Would like to have a record for each node that contains every connection and it's connection strength.

Ideal database would look like (columns)
Id
Name
Connections
Strengths (where i2,3,4 = connections i2,3,4)

I've done this type of thing before with data trees and XML but looking for a more robust option I can query for online interactive graphics and games.

In terms of translating the Excel file to the database itself I was planning on writing a string parser to convert CSV to SQL but interested to hear if there are other ideas out there.

Thanks!


texts: MySQL count rows of multiple tables for each user

texts:
I basically need this with some adjustments, I just can't seem to figure it out how to do it.
I need to sum the rows of two tables, for example:

SELECT
  student.StudentID,
  student.`Name`,
  COUNT(attendance.AttendanceID) AS Total
FROM
  student
LEFT JOIN attendance ON student.StudentID = attendance.StudentID
GROUP BY student.StudentID,student.`Name\`;

This would return

+-----------+-------------------+-------+
| StudentID | Name              | Total |
+-----------+-------------------+-------+
| k1052280  | Ali Shaikh        |     2 |
| k1052287  | McKenzie Roth     |     2 |
| k1052288  | Dacey Sullivan    |     1 |
| k1052294  | Zelda Cantu       |     0 |
| k1052295  | Kimberly Melton   |     3 |
| k1052296  | Tatianna Cantrell |     0 |
| k1052297  | Morgan Thornton   |     0 |
| k1052298  | Allistair Barlow  |     0 |
| k1052299  | Troy Fulton       |     0 |
+-----------+-------------------+-------+

Total being the rows for the attendance table/student. I need to add in another table, for example meetings, i need a SUM of the two, so the total should be the sum of meetings + attendances for each student.


texts: Problem Connecting Report Server to Report Server Database in SSRS

texts:
I have configured Reporting Services on one Server and have the ReportServerDb and ReportServerTempDb on other server (Future requirement of scale out deployment).

I am connecting to these servers using a domain account. I have configured Reporting Services using ReportingServiceConfiguration Wizard and it seems to have worked fine except that the Initialization tab does not have the green tick in front (not initialized).

The error I get is:

  The report server cannot open a connection to the report server
  database. The logon failed. (rsReportServerDatabaseLogonFailed) Get
  Online Help Logon failure: the user has not been granted the requested
  logon type at this computer. (Exception from HRESULT: 0x80070569)

The domain account I have used is perfectly fine and I am able to connect using SSMS (No Password errors). I have searched through many a blogs, forums etc, but have not been able to find a solution.


texts: SQL Server AlwaysOn database stuck in Not Synchronizing / In Recovery mode after upgrading. Error: Cannot open database '...' version 782

texts:
While testing an upgrade from SQL Server 2014 SP1 (12.0.4422.0) to SQL Server 2016 CTP 3.2 (13.0.900.73) I was following the recommended update process and ran into an issue where the database would not start on the old primary after failing over to the updated secondary. Our setup is a primary replica and a single secondary replica, and the steps I completed were:

Remove automatic failover on synchronous-commit secondary replica
Upgrade secondary server instances to new version
Manually fail over to the secondary replica
Verify databases are online on new primary replica
Upgrade previous-primary replica to new version

The upgrade of the secondary and failover to make it the primary worked exactly as expected. But after upgrading the previously-primary replica I noticed that the databases on it were listed in SSMS as Not Synchronizing / In Recovery. Also trying to access them would generate an error message:

  The database ... is not accessible. (ObjectExplorer)

Checking thru the SQL Server Logs I saw

  Cannot open database '...' version 782. Upgrade the database to the latest version.

Querying the master..sysdatabases table showed that it was indeed an older version and had not been updated during the upgrade:

Unfortunately the logs did not indicate why it wasn't updated, and the Availability Groups Dashboard only gave a generic warning indicating the Data synchronization state of some availability database is not healthy with no reason why.

I tried using TSQL to detach the databases or set them offline to "kick" it into updating, but since they are part of the SQL AG those commands don't work.

How can I upgrade the database to the latest version when it is part of a SQL AG?


texts: How to Audit 2 or more SQL Logins (server principals) using SQL Server Audit

texts:
I need to audit the few SQL Server Logins, I have used filters while creating Server Audit by using

CREATE SERVER AUDIT [audit123]
TO FILE
(   FILEPATH = N'D:\'
    ,MAXSIZE = 2048 MB
    ,MAX_ROLLOVER_FILES = 6
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 5000
    ,ON_FAILURE = CONTINUE
)
WHERE ([server_principal_name]='AAA')

But when I am including more SQL logins using where ([server_principal_name] in ('AAA','BBB','CCC')) it's giving error. I have tried using below but failed to create audit for 2 or more logins.

ALTER SERVER AUDIT audit123 WHERE server_principal_name ='BBB';
ALTER SERVER AUDIT audit123 WHERE server_principal_name ='CCC';


texts: Activity Monitor Showing Long Duration for WAITFOR

texts:
Using Activity Monitor, I am consistently seeing the following query showing an average duration of over 300 seconds:

WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body
                FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeout

There don't seem to be any adverse effects on the server, and this query is showing zeros for all other columns.

Is this normal or a cause for concern?


texts: What happens if we copy neo4j's data folder while the server is running?

texts: If my neo4j server is running and I copy the data folder using copy/rsync folder, what are the chances of data corruption?


texts: Domain name change of SQL servers involved in replication

texts:
I have a situation where client wants to change their domain name of servers hosting SQL Servers(e.g. from AB to CD). I have a transactional replication with updatable subscription configured with 1 publisher, 1 distributor and 4 subscribers.

We are only changing the domain name and not hostname/IP address. Will it affect replication as it uses linked server? Linked server login used to configure during replication configuration was 'sa'. I am more concerned about replication as reconfiguration is a big headache. Also I know I'll have to change service accounts, logins, proxys etc. Any other things to take care of?

Let me know if you need any further details and thanks in advance.


texts:
I have two ORACLE tables.

First table:

ID    l_name f_name belong_to

123   Smith  John        A Club

456   Jones  Mary        The Club, Your Club, My Club

789   Moore  Sue         The Club, A Club, Some Club

007   Wills  Art         My Club, Some Club, Two Club

Second table:

Club_ID  Name

1            A Club

2            The Club

3            Your Club

4            My Club

5            Some Club

6            Two Club

The idea is to link these tables based on the name of the club in the 'name' field of the second table AND have a row of output showing the person's name with each club he or she belongs to, like this:

l_name    f_name    belong_to

Smith     John      A Club

Jones     Mary      The Club

Jones     Mary      Your Club

Jones     Mary      My Club

Moore     Sue       The Club

Moore     Sue       A Club

Moore     Sue       Some Club

Wills     Art       My Club

Wills     Art       Some Club

Wills     Art       Two Club

The belong_to field in the first table can have multiple clubs separated by ', ' (i.e. comma space).

SELECT distinct id, l_name, f_name, trim(regexp_substr(belong_to, '[^,]+', 1)) club_str
FROM first_table

So far, this only returns the first club a person belongs to in each record of the first table.


texts: MySQL Workbench - Creating a one-to-one relationship using existing columns?

texts:
How do I create a one-to-one relationship using existing columns in MySQL Workbench?

I have an EER diagram tab open and I am selecting from the toolbar on the left hand side.

Unfortunately it seems that I can create a one-to-many or many-to-one relationship but not a one-to-one.


texts: How to re-sync the Mysql DB if some Masters have different database incase of MySQL Group replication?

texts:
Somehow one of my server got into split brain situation, how can we reset & sync back one of servers's data with another master.

i won't mind lossing one's set of new data but i want now that detached server to in sync with others.

suppose i've only two servers. because after split brain now its not more be able to join group replication because it has many more new transaction and another one also have many new.

there is similar question for normal master slave mysql config
How to re-sync the Mysql DB if Master and slave have different database incase of Mysql replication?

but here i am talking about group replication in mysql.


texts: Oracle SQL Developer - Database Diff tables & constraints without table data

texts:
I know I can compare two Oracle schemas by using Tools -> Database Diff in Oracle SQL Develop. It will open the following wizard:

Which will compare everything you've checked.

The problem I'm currently facing is that I only want to compare the Schema (tables, constraints, etc.) itself. Everything except for the data inside the tables. All the data combined was more than 1 GB of INSERT INTO statements (which I couldn't even load into SQL Developer, so I had to use SqlPlus to insert it), and it's currently already running for 1 hour with the Database Diff without result.

Because the database I want to compare it to is empty anyway, I'm only interested in comparing the actual tables, instead of also the data inside the tables.
Is this possible with Oracle SQL Developer?

If not I guess I'll have to use an alternative to check if there are any differences. Either a different tool, or exporting both schemas without data and compare those two files (excluding timestamps and such), or duplicating the existing database with data - empty it - and then compare with Database Diff. There are work-arounds, but I'm just wondering if it's possible to use Database Diff without table-data.

PS: The reason why I want to check if they are the same: a colleague of mine created Liquibase scripts to create the database, and I want to check if everything is correct by comparing it to the database (with data) we already had.


texts:
Where can I find SQL Developer Database examples (scripts) to add data to practice?
I require SQL scripts that will add tables and data on those tables in order to practice with Power Bi.


texts:
In another question a user asks how to force row level locking. The answer that he receives tell them how to force row level locking by altering the table and the indexes. The user that answered the question does also mentions that there can be a side effect of preventing index reorganization.

What i want to know is if someone could explain this side effect further, because our worry is that a table the is inserted into and deleted from a lot, could become slow over time if the side effect of forcing row level locking does what we think it does.

Orginal post:

How to force the use of row locks?

Many thanks in advance


texts: Two different databases on the same RAC cluster

texts:
We have an Oracle 11gR2 RAC cluster with 4 nodes.  2 nodes are dedicated to one database that's devoted one application and 2 nodes are dedicated to a completely separate database that serves a completely different application.  They got all put on the same cluster.  Why?  Nobody really knows anymore.

We're looking at changing hardware and OS's and such on two of the nodes for one of the applications.  Since they're in the same cluster, we're concerned about how to do this without effecting the other two nodes (or if it can even be done).  We're installing two much newer nodes on this database with much newer OSs and I'm not sure if it will even let us add it to the cluster with the newer OS.

As such, we'd like to split the cluster into two clusters?  Is there any easy way to do this?  They share one ASM instance, but all the files are obviously separate within that instance.


texts: postgresql trigger unexpected behaviour by many copies of inserted row

texts:
On insert in tbl1, the trigger I am working on, copies rows in tbl2 with some updates.

Trigger:

create or replace function myprocedure() returns trigger as
begin
IF (TG_OP = 'INSERT')THEN
 insert into tbl2
 select new.* from tbl1 ;

 update tbl2
 set (field1, field2) =  ...
 where gid = new.gid;

 return new ;
 END IF ;
END;
$$ LANGUAGE plpgsql ;

DROP TRIGGER IF EXISTS mytrig ON public.tbl1 ;
CREATE TRIGGER mytrig AFTER INSERT or UPDATE on public.tbl1
FOR EACH ROW EXECUTE PROCEDURE myprocedure();

My problem is:

The first insert in tbl1 gives 1 insert in tbl2. It is fine.
The second insert in tbl1 gives 2 (identical) lines in tbl2 (values of second insert)
The third insert in tbl1 gives 3 (identical) lines in tbl2 (values of third insert)
etc.

I am feeling a little lost. Any help?


texts: How to change the default isolation level of a transaction in a stored procedure in PostgreSQL PL/pgSQL?

texts:
I am interested in controlling the isolation level of a transaction inside a PostgreSQL procedure but haven't found any information on how to do it. The documentation on Transaction Management within Chapter 42. PL/pgSQL — SQL Procedural Language only states that:

A new transaction starts out with default transaction characteristics such as transaction isolation level.

Can we change the default isolation level? Can you provide example code?


texts:
Each month we make an end of month snapshot of our production database. These month end snapshots are strictly for reporting purposes, there are no inserts, updates or deletes ever done on them. Each of these snapshots has an .MDF and .LDF file.

I want to delete the .LDF files and free up some space on the server. Are there any reasons why I need to keep the .LDF files?

Clarification:

Our production database is recreated each night from file extracts off of another system. We only report off of the production database... no updates are ever done.

Nightly Process:
From what I can tell...
Each night the databse tables are truncated
The tables are populated through a series of bulk insert statements
Indexes are rebuilt


texts: transactional Replication of millions of updates

texts:
I have standard transactional replication setup from 2005 to 4 2008 subscribers. One of the tables that is replicated is updated several times from an agent job resulting in 6 million updates statements every morning which is causing massive deadlock chains on the subscribers.

I think the resolution is to change this to replicate just a stored procedure call?  Do I simply have to add the new stored procedure into the publication & add the article at the subscriber ?

I have attempted this as still seem to be transmitting all of the update statements, I have added the article properties to replicate the call of the stored procedure as well but still seeing all rows in replication monitor


texts: Increasing the number of tempdb files question about initial size

texts:
I am planning on increasing the number of tempdbs we have on our sql cluster. I do have one question that I can't seem to find a clear cut answer to...

I have one tempdb that is over 50GB and I want to add an additional 11 more tempdb files but I do not want them all to be 50GB so I want to 'shrink' the original tempdb but my question is if I set the initial size to 4GB on the original tempdb file and I bounce the server will it shrink the original tempdb to 4GB or will it stay at 50GB?

Or

Should I just use DBCC SHRINKFILE (tempdev, 4096) on the original tempdb?


texts: Status of PostgreSQL replication after pause and resume

texts:
To backup the PostgreSQL database we setup a slave server which uses streaming replication.

We found the commands pg_xlog_replay_pause() and pg_xlog_replay_resume(). So we thought we use the commands to pause the replication (the replaying of the xlogs), start pg_dump and then resume the replication.

How do I find out when the PostgreSQL database is again in sync after the "resume"? Usually I used this command to see the "lag":

SELECT extract(seconds from (now() - pg_last_xact_replay_timestamp())) AS time_lag;

If I use it after pg_xlog_replay_pause() then it shows only a "lag" in seconds. So it seems that it is not the right query here. So is there a good solution for this?

One solution would be to write a cronjob on the master server to update a timestamp every minute and then check on the slave when the timestamp is more or less up to date. But I think there should be a better solution from PostgreSQL itself.


texts: MySQL Binary Installation (mysql_install_db)

texts:
While installing from binaries. The below always gives error when we execute from 'scripts' directory

[mysql@hostname ~]$ cd /mysql/product/5.6.12/scripts/
[mysql@hostname ~]$ . mysql_install_db --defaults-file=my.cnf

-bash: use: command not found

But the same works fine when I execute one step back from scripts directory

[mysql@hostname ~]$ scripts/mysql_install_db --defaults-file=my.cnf

Question: Why this happens ?


texts: How to remove some data not needed on new row from mysql query with rollup

texts:
Hello I have a problem with the rollup mysql query. I want to get total of each column. I use mysql SUM() and ROLLUP to get the result. But on the new row that is added it copies the data of the last row. How can I remove those data. I provided an image on what I really needed to achieve.

Here is my query.

    SELECT  IFNULL(payments.id, "General Total"), payments.driver_id,
        payments.vehicle_specifications_id,payments.admins_id,
        payments.vehicle_id, payments.boundaries, payments.cashbond_payments,
        payments.loans, payments.penalties, SUM(payments.total_payments),
        a.first_name, a.mid_name, a.last_name, d2.date, d.first_name,
        d.mid_name, d.last_name, v.plate_number, v2.car_rate
    FROM  payments
    INNER JOIN  admins AS     a  ON payments.admins_id = a.id
    INNER JOIN  drivers AS    d  ON payments.driver_id = d.id
    INNER JOIN  dispatch AS   d2 ON payments.dispatch_id =d2.id
    INNER JOIN  vehicles AS   v  ON payments.vehicle_id = v.id
    INNER JOIN  vehicle_specifications AS v2
                ON payments.vehicle_specifications_id = v2.id
    GROUP BY  payments.id WITH ROLLUP


texts: Does MySQL 8 ASCII vs utf8mb4_0900_ai_ci size differ when only using ASCII characters?

texts: If I only use only ASCII characters, will VARCHAR (255) with utf8mb4_0900_ai_ci be larger on disk than VARCHAR (255) using ASCII?


texts: User has Areas, which have Buildings, 3 PK's or tables in between?

texts:
I'm building a database which has the following

User
Area
Building

A User has many areas, and a user can add buildings to his/her areas.
I can up with the following idea but I feel like there's some duplication going on
user (id, name)
user_area (user_id, area_id)
area (id, size)
area_building (area_id, building_id, user_id)

Since this allows an area_building that is put on an area that doesn't belong to the user.
Would it be better to have this layout?
user (id, name)
user_area (id, user_id, area_id) <-----
area (id, size)
user_area_building (user_area_id, building_id) <-----

A user can have an area and build on it, another user can also have that area and build on it, but they won't ever see each other's building.


texts: Centos 6.2 mysql configuration file help

texts:
I have a new centos 6.2 setup. Below is the configuration file. The machine is going to be fully innodb and it had 8Gb ram. What else must I include in the .cnf?

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid