texts: MyISAM with Innodb in the same Wordpress database reduce performace?

texts:
My new Wordpress database all the Wordpress created tables use InnoDB engine and plugin created table use MyISAM. But older site database all the tables use MyISAM table engine.
I use MySQL 5.5.35-cll i686.

If I use two different table engines in a same database, it will reduce my site performance or are there any downside?


texts: sp_help_jobsteplog fails when run as a @query of sp_send_dbmail

texts:
I have a SQL 2005 stored proc that sends an email with some job history:

msdb.dbo.sp_send_dbmail
 @recipients =   'me@here.com',
 @Subject =      'job history',
 @Body =         'check this out...',
 @Profile_name = 'PublicEmail',
 @query = 'EXEC msdb.dbo.sp_help_jobsteplog ''F85B07F2-BC89-4934-B997-...''',
 @attach_query_result_as_file = 1

(that's a full GUID, I just cut it off for the code window) I know there are other ways to get job history, but this method has worked until recently, and now I get the following error:

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495
Query execution failed: Sqlcmd: Error: Internal error at GetDataRowset
   (Reason: Not enough storage is available to complete this operation).
Sqlcmd: Error: Internal error at FormatRowset
   (Reason: Not enough storage is available to complete this operation).

I can run the sp_help_jobsteplog query all by itself with no problems. There is no shortage of space on the server, so I think the "not enough storage" part of the error message is probably not really what's going on. The documentation for sp_help_jobsteplog says

  By default, members of the sysadmin
  fixed server role can execute this
  stored procedure. Other users must be
  granted one of the following SQL
  Server Agent fixed database roles in
  the msdb database:

  SQLAgentUserRole
  SQLAgentReaderRole
  SQLAgentOperatorRole

So when I combine that with the fact that db_send_dbmail runs @QUERY statements in a separate session, I'm worried that its actually a permissions issue.

My question is: what user does sp_send_dbmail try to use to run its @Query? (I'm running sp_send_dbmail itself as a sysadmin, and if it carried through those credentials, it would work, right?) Does it depend on the profile used?

Any other ideas what might be going wrong here?

EDIT: So I still have no idea why this was happening, but I rebooted the server, and now everything appears to be back to its (normal) working state!


texts:
I have a db with the following:  Name, Team, Score, Points  I want to be able to do a query that returns Name, Score, Points for a particular team and another table that returns Points, Score, Name from another team...  Then I want to combine them as follows:  Name, Score, Points, Points, Name, Score
EXAMPLE

John, Boston, 127, 6
Mark, Boston, 120, 5
Paul, Newark, 110, 4
Carl, Boston, 105, 3
Matt, Newark, 101, 2

Would result in:
John | 127 | 6 | 4 | Paul | 110 |
Mark | 120 | 5 | 2 | Matt | 101 |
Carl | 105 | 3 |   |      |     |

OR (depending on which city is selected first)
Would result in:
Paul | 110 | 4 | 6 | John | 127 |
Matt | 101 | 2 | 5 | Mark | 120 |
     |     |   | 3 | Carl | 105 |

Thanks


texts:
I want to update data of a table where data-name matches with another table and also one data are common with many items.
For example, I have a cycle in which tyre is a part and quantity of tyre is 500. Now I had a new brand cycle in which tyre is also required. When i issue one cycle then i want to update tyre quantity and if i issue another cycle then i also want to update the same tyre quantity. I applied this quarry of MySQL
update available set quantity = quantity - '"+v1+"' * ( select required from byproduct where subitem = (select subitem where name = '"+v2+"'))
i was trying to this i got an error like " subquery returns more than one row". After that, I searched on the internet and applied IN at the place of "subitem=" i got a new error "integer constraint violation exception" . Please help me to solve this


texts: Table partition existing table where partition key is not part of the primary key

texts:
Main objective: Add partitioning to table to make deletion of old orders non-blocking/quicker (and also understand partitioning)

I have an existing table Order, like this:

CREATE TABLE Order (
    OrderId INT,
    OrderDate Datetime,
    Quantity INT,
    CONSTRAINT [PK_OrderId] PRIMARY KEY CLUSTERED
(
    [OrderId] ASC
)
ON [PRIMARY];

This table contains 50 million rows from the last 10 years.
I only need the last 5 years data.

I have a partition function like this:

CREATE PARTITION FUNCTION OrderPF (datetime)
AS RANGE RIGHT FOR VALUES ('2014-01-01')

I have a partition scheme like this:

CREATE PARTITION SCHEME OrderPS
AS PARTITION OrderPF ALL TO ([PRIMARY])

My question is how to proceed?
I still want a primary key on the table.

Does the [OrderDate] column have to be a part of the clustered index? (Main question)

CREATE UNIQUE CLUSTERED INDEX IX_Order ON Order(OrderDate,OrderId) ON OrderPS(OrderDate) ;

If so, do I then have to create an extra non-clustered Primary Key purely on [OrderId]?

ALTER TABLE Order ADD CONSTRAINT PK_OrderId PRIMARY KEY NONCLUSTERED (Id) ON [PRIMARY];

Is this the correct approach?


texts:
I am writing an INSTEAD OF trigger on a view. I need to split a string into 4 40 character long parts to insert into separate columns. I want to use substr() to break the variable up into these parts, but when I try to create the trigger I get the error:

  SQL State: 42703
  Vendor Code: -206
  Message: [SQL0206] Column or global variable SUBSTRING not found.
  Cause . . . . . :   SUBSTRING was not found as a column of table *N in *N and was not found as a global variable in *N. If the table is *N, SUBSTRING is not a column of any table or view that can be referenced.
  Recovery  . . . :   Do one of the following and try the request again:
  -- Ensure that the column and table names are specified correctly in the statement.
  -- If this is a SELECT statement, ensure that all the required tables were named in the FROM clause.
  -- If the column was intended to be a correlated reference, qualify the column with the correct table designator.
  -- If the column was intended to be a global variable, qualify the name with the schema where the global variable exists or ensure the schema is in the path.

This page indicates that I can use an expression in my USING clause as long as it is within a compound SQL statement. As I understand it, the SQL between BEGIN and END in a trigger would be considered a compound SQL statement.

  "An expression other than host-variable can only be used when the EXECUTE statement is used within a compound SQL (compiled) statement."

CREATE OR REPLACE TRIGGER MYLIB.INFO_INSERT
INSTEAD OF INSERT ON MYLIB.INFO
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2SQL
BEGIN
    DECLARE...
    ...
    set insert_into_info = 'INSERT INTO MYLIB.' || info || ' (
      CICUST, CIINF1, CIINF2, CIINF3, CIINF4
    ) VALUES (' || NEW_ID || ', ?, ?, ?, ? )';

    PREPARE s1 from insert_into_info;
    EXECUTE s1 using substring(paddedComments, 1, 40), substring(paddedComments, 41, 80),
        substring(paddedComments, 81, 120), substring(paddedComments, 121, 160);
END;

I have found examples for Oracle of using functions in the USING clause, but no iSeries or DB2 examples, although there are enough hints in the descriptions of the EXECUTE statement to keep me hoping. I am running z/OS 7.2 if that is relevant.

I've been searching around but haven't managed to figure this out; can anyone get me going in the right direction? I did also try to do this with an array so I wouldn't have to break up the string at the end, but I could not manage to get an array type declared and used within this trigger either.


texts: Postgres 9.4 won't accept remote connections

texts:
I'm running postgres 9.4 on Ubuntu server 14 which is running on virtualbox.  After a reboot no remote connections would work to postgres.

I have other services on server that still take remote connections (Rstudio server, Shiny server, SSH) and that machine can still connect to the internet, and to samba shares.

Here's my pg_hba.conf

    local   all             postgres                                peer
    host    all           scraper         localhost             trust
    local   all             all                                     peer
    host    all             all             127.0.0.1/32            md5
    host    all             all             ::1/128                 md5
    hostssl all             all             0.0.0.0/0               md5

In trying to connect with pgadmin from another machine, I get this message

An error has occurred:

03:23:06 PM: Error: Error connecting to the server.
FATAL: password authentication failed for user "postgres"

It fails if I use R, psql, or windows odbc connections so it doesn't appear to have anything to do with pgadmin

If I ssh in then I can run psql locally...

If I run \du I find that all users show Password valid until infinity

EDIT

I forgot to say that I am indeed using the correct password.  I can be sure I'm using the right password because I when I was in psql I reset the password of the users in question using ALTER USER user WITH PASSWORD 'MYPASSWORD';


texts: optimizing or estimating postgresql table size on disk

texts:
This question is in the context of the helpcovid GPLv3+ project (work in progress in March 2020). With PostgreSQL 11 on Debian/Buster/x86-64. See the file hcv_database.cc there.

I have a table of web users:

---- TABLE tb_user
CREATE TABLE IF NOT EXISTS tb_user (
  user_id SERIAL PRIMARY KEY NOT NULL,  -- unique user_id
  user_firstname VARCHAR(31) NOT NULL,  -- first name, in capitals, UTF8
  user_familyname VARCHAR(62) NOT NULL, -- family name, in capitals, UTF8
  user_email VARCHAR(71) NOT NULL,      -- email, in lowercase, UTF8
  user_gender CHAR(1) NOT NULL,         -- 'F' | 'M' | '?'
  user_crtime DATE NOT NULL             -- user entry creation time
); --- end TABLE tb_user

Imagine I have a million rows in that table, and 99% of users have a user_firstname of less than 16 bytes, and 99% of users have a user_family of less than 32 bytes.

Assume that this table is only filled by INSERT SQL statements.

What would be the estimated disk space consumption of the PostgreSQL files (under /var/lib/postgreql) containing that database. As measured by du -h

For simplicity, assume that this table is the only one in the database.

To ask the question otherwise, the VARCHAR(31) size is used for every row on disk, or not?

In other words, how practically important is the width of columns in tables, w.r.t to disk space?

Related question: How does Postgres store many varchars on disk?


texts:
Because my memory seemed to be rapidly disappearing (according to the Task Manager graph), but I couldn't see any programs using it, I downloaded RAMMap to get a better picture of what's going on. Turns out all of my memory was being chewed up by AWE.

When I try to research this, however, everyone seems to say that AWE isn't enabled for SQL Server 2012 64 bit systems, since it isn't needed; the expanded memory that AWE would enable is directly accessible. But the system still will happily chew up memory in AWE until it reaches its limit.

How can I disable this and free the 24GB ram?


texts: Postgresql 9.4: How to find out the stack level of nested function

texts:
Here are the two cases:

Function fA fetches some rows and returns a table
Function fB calls function fA and returns a table

Within fA, how could I know whether it's been called from the program or by fB?

Is there some @@nestlevel that can count how deep in the stack are the functions running?

Big thank you in advance,


texts: Create multiple columns from a single column filtering criteria?

texts:
thanks in advance for your time, what I need to achieve is generate multiple columns result from a single query, the thing is this:

I got a table were is storing the next:

id | user_id | revision
1       1      Approved
2       1      Rejected
3       1      Pending
4       1      Pending
5       1      Pending

What I need is to get the next result:

Total | User | Pending | Rejected | Approved
  5       1       3         1           1

I have tried using Group by like this:

SELECT count(id) Total, user_id User, revision FROM table1 GROUP BY user_id, revision

But the output is way to far from what I am spectating to get:

Total | User | revision
  3      1      Pending
  1      1      Rejected
  1      1      Approved

The idea is to get the proper object returned to avoid over cycling the server side code with unnecessary processes, so If the database can do the handling in one call this would be great.

I tried googling for hours and all I could get were a lot of results for 'single column from multiple rows' which is something I do not need, thanks for your help in advance.

The final result set with many users should look like this:

Total | User | Pending | Rejected | Approved
  5       1       3         1           1
  5       2       4         0           1
  5       7       2         3           0

Updated:
After more researching I found this:
https://stackoverflow.com/questions/14172282/sql-query-needed-to-get-result-in-two-columns-grouped-by-type-and-percentage-in

This is almost what I need after some editing I still can't get it to send it in solid numbers instead percentages, I lack the knowledge for working group cases, I will keep trying if any of you can help it is greatly appreciated.

Solution
Hey I managed to do this, it was easy, but my lack of knowledge was in the middle, I read the chapter Generating Summaries in MySQL Cookbook, this is where they explain that you can create count, sum and other methods inside your query to gain the proper values in those columns you define, so this is the query I ended up using:

SELECT user_id as userid, count(id) Total,
COUNT(IF(revision = 'pending', 1, NULL)) as pending,
COUNT(IF(revision = 'approved', 1, NULL)) as approved,
COUNT(IF(revision = 'rejected', 1, NULL)) as rejected
FROM table1
GROUP BY user_id

This will fill those values by count only if criteria is met.


texts: Configuring GeoServer SQL Views and Validation

texts:
For a little background i want to mention that I am using GeoEXT with Geoserver.
I am using the Geoext comboboxes to search point data by name. The application works properly for name having single name such as 'Khan' but it doesn't work for words having spaces like 'Not Khan'. I am herby writing the query and the parameters.

Geoserver SQL View:

select *
   from %Table%
WHERE
   to_tsvector('english', Column1) @@
   to_tsquery('english', regexp_replace(trim('%WordPassed%'), E'\\s+', '&', 'g'))

The above query work perfectly for texts having no spaces but doesn't work on names having spaces.
The Validation used by Geoserver by default on that column is :

^[\w\d\s]+$


texts:
I installed repmgr10 on PostgreSQL server before. But now will reinstall them.
Deleted postgresql's data folder: /var/lib/pgsql/10/data/.
Stoped postgresql-10 service: sudo systemctl stop postgresql-10.
Also did sudo systemctl stop repmgr10.
But check repmgr's status, got
$ sudo systemctl status repmgr10
● repmgr10.service - A replication manager, and failover management tool for PostgreSQL
   Loaded: loaded (/usr/lib/systemd/system/repmgr10.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Wed 2020-07-08 10:37:43 JST; 3h 7min ago
  Process: 250464 ExecStop=/usr/bin/kill -TERM $MAINPID (code=exited, status=0/SUCCESS)
  Process: 251422 ExecStart=/usr/pgsql-10/bin/repmgrd -f ${REPMGRDCONF} -p ${PIDFILE} -d --verbose (code=exited, status=1/FAILURE)
 Main PID: 152648 (code=exited, status=0/SUCCESS)

Jul 08 10:37:43 server_db systemd[1]: Starting A replication manager, and failover management tool for PostgreSQL...
Jul 08 10:37:43 server_db repmgrd[251422]: [2020-07-08 10:37:43] [NOTICE] using provided configuration file "/etc/repmgr/10/repmgr.conf"
Jul 08 10:37:43 server_db repmgrd[251422]: [2020-07-08 10:37:43] [NOTICE] redirecting logging output to "/var/log/repmgr/repmgr.log"
Jul 08 10:37:43 server_db systemd[1]: repmgr10.service: control process exited, code=exited status=1
Jul 08 10:37:43 server_db systemd[1]: Failed to start A replication manager, and failover management tool for PostgreSQL.
Jul 08 10:37:43 server_db systemd[1]: Unit repmgr10.service entered failed state.
Jul 08 10:37:43 server_db systemd[1]: repmgr10.service failed.
Hint: Some lines were ellipsized, use -l to show in full.

I checked log in the /var/log/repmgr/repmgr.log
[2020-07-08 10:24:18] [NOTICE] repmgrd (repmgrd 5.0.0) starting up
[2020-07-08 10:24:18] [ERROR] repmgr extension not found on this node
[2020-07-08 10:24:18] [DETAIL] repmgr extension is available but not installed in database "repmgr"
[2020-07-08 10:24:18] [HINT] check that this node is part of a repmgr cluster
[2020-07-08 10:37:43] [NOTICE] repmgrd (repmgrd 5.0.0) starting up
[2020-07-08 10:37:43] [ERROR] repmgr extension not found on this node
[2020-07-08 10:37:43] [DETAIL] repmgr extension is available but not installed in database "repmgr"
[2020-07-08 10:37:43] [HINT] check that this node is part of a repmgr cluste

It shown repmgr extension is available but not installed in database "repmgr".
But now repmgr service can't been restarted so how to install repmgr extension in database?
So I want to know how to stop repmgr daemon or remove this package then install again? That maybe can solve the problem.


texts: Most efficient way to retrieve a sub-query COUNT grouped by top table?

texts:
Given the following schema

CREATE TABLE categories
(
    id UNIQUEIDENTIFIER PRIMARY KEY,
    name NVARCHAR(50)
);

CREATE TABLE [group]
(
    id UNIQUEIDENTIFIER PRIMARY KEY
);

CREATE TABLE logger
(
    id UNIQUEIDENTIFIER PRIMARY KEY,
    group_id UNIQUEIDENTIFIER,
    uuid CHAR(17)
);

CREATE TABLE data
(
    id UNIQUEIDENTIFIER PRIMARY KEY,
    logger_uuid CHAR(17),
    category_name NVARCHAR(50),
    recorded_on DATETIME
);

And the following rules

Each data record references a logger and a category
Each logger will always have a group
Each group can have multiple loggers
I only want to count the most recent data recorded

category_name isn't unique per row, it's just a way to associate a given data record under a category, id is really just a surrogate key.

What would be the optimum way to achieve the a resultset like

category_id | logger_group_count
--------------------------------
12345          4
67890          2
.....          ...

i.e. count the no. of groups for each category where a logger has recorded data?

As an initial stab I came up with:

SELECT g.id, COUNT(DISTINCT(a.id)) AS logger_group_count
FROM categories g
  LEFT OUTER JOIN data d ON d.category_name = g.name
  INNER JOIN logger s ON s.uuid = d.logger_uuid
  INNER JOIN group a ON a.id = s.group_id
GROUP BY g.id

But is extremely slow (~45s), data has 400k+ records - here's the query plan and here's a fiddle to play with.

I want to make sure I'm eeking the most out of the query before I start looking at other things i.e. hardware utilization etc. Azure SQL costs can go up substantially (even though you maybe just need a little more juice off your current tier).


texts: MySQL Workbench is adding --enable-cleartext-plugin option on mysqldump command

texts:
For some reason my copy of MySQL Workbench on one of my servers has started adding this --enable-cleartext-plugin option on every mysqldump command, which results in this error every time.

  20:13:02 Dumping timesavr_dev (certifications) Running: mysqldump.exe
  --defaults-file="c:\users\admini~1\appdata\local\temp\3\tmpn1an6u.cnf"  --set-gtid-purged=OFF --enable-cleartext-plugin --user=root --host=localhost --protocol=tcp --port=3306 --default-character-set=utf8 --skip-triggers "timesavr_dev" mysqldump: unknown option '--enable-cleartext-plugin'

  Operation failed with exitcode 2 20:13:02 Export of
  C:\inetpub\wwwroot\TimeSavr\DB_Stuff\MySQLBackups\Dump20180608.sql has
  finished with 1 errors

I just noticed this today but I suspect this started happening a few days ago when some other weird things also happened on this server.  After a reboot all the bindings on my website had disappeared, as had the user security privileges on the website folder, and for some reason Shared Config was enabled in my redirection.config file.  It all seems very random, and now this.

These are the options I have enabled, and I'm pretty sure these are default because when I click Restore Defaults nothing changes.

How can I tell MySQL Workbench to stop using the --enable-cleartext-plugin option?


texts: SQL Server Partially Contained Databases and Linked Servers

texts: What is the work around for using linked servers with partially contained DBs?  Additionally, how would this work with Always on and a secondary replica?


texts: Trying to take a backup of a database, but drives are different than in file explorer

texts:
In SQL Server Management Studio for SQL Server 2008, I do the following:

In the Object Explorer windows, right click on the database
Choose Tasks -> Back Up...
The Back Up Database windows appears. I choose 'Disk' under Destination
I choose 'Add...' under destination
The Select Backup Destination window appears. I choose '...'
The Locate Database Files window appears.

Now I want to save it to a network drive, but there are only three drives to choose from (C,G,H). But in any other place in windows there are C, and the network drives K, Q and Z. Why can't I save it on a network drive? And what is drive G and H? I'm fine with saving it on G or H, but where is it and how much space is left on these drives?


texts: how to automatically create the associated schema when I `pg_restore` a table?

texts:
I backed a table into a file using something like pg_dump --table=ex.tab.

But when I restore the dump file into a newly created database, it complains that the schema ex does not exist. Looking into the dump file ( listed below), it does not have a CREATE SCHEMA statement.

Is there an option that I can add to pg_dump or pg_store so that the schema of the table is automatically created?

Note: I do this for multiple tables in the command line (multiple --table=), and do not want to manually create schema for each table beforehand (if possible).

--
-- PostgreSQL database dump
--

-- Dumped from database version 11.1 (Ubuntu 11.1-3.pgdg18.04+1)
-- Dumped by pg_dump version 11.1 (Ubuntu 11.1-3.pgdg18.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: tab; Type: TABLE; Schema: ex; Owner: myuser
--

CREATE TABLE ex.tab (
    name character varying(80),
    state_ character varying(2)
);

ALTER TABLE ex.tab OWNER TO myuser;

--
-- Data for Name: tab; Type: TABLE DATA; Schema: ex; Owner: myuser
--

COPY ex.tab (name, state_) FROM stdin;
San Francisco   CA
ST LOUIS    MO
LOS ANGELES CA
\.

--
-- PostgreSQL database dump complete
--


texts:
I have a table that looks like this:

CREATE TABLE transactions(
id SERIAL NOT NULL PRIMARY KEY,
value NUMERIC(10,3),
note TEXT,
group_id INTEGER);

I'd like to update the group ID automatically with a trigger, if it's not set on insert.  So something like this:

CREATE FUNCTION trigger_update_group()
RETURNS trigger AS $$
DECLARE max_group_id_ INTEGER := 0;
BEGIN
IF NEW.group IS NULL THEN
  SELECT MAX(group_id) INTO max_group_id_ FROM transactions;
  max_group_id_ := 1;
  NEW.group = max_group_id_;
END IF;
RETURN NEW;
END;
$$ language plpgsql;

The problem with this is that if I set the trigger to FOR EACH ROW, then each inserted row gets a different group_id (not to mention we run the SELECT statement for each row too).

Instead, what I was hoping for was that all the rows in a given INSERT statement would get the same group_id. Is there a way to do that (even if it involves transactions)? It seems FOR EACH STATEMENT doesn't allow access to the individual rows.  Maybe there's some way to wrap the INSERT?


texts: Use Database to Store Data Extracted From Internet

texts:
I'm mining 500 million users, and their "followers" from a social network using their API. The extraction of data itself is not a problem, since I can do it with my scripts. However having 500 million users and their followers in a list in memory can be very costly.

My script created two lists,one with the users that I already got their followers, and one with the users to be looked at (I would get each user, put their followers in the queue, write to file, and then go to the next one.) So it would be 2 long lists that I cannot handle in memory. So I thought of a database.

So finally to my question, is it better for me to use a relational database, or a NoSQL, graph, database, like Neo4j. The only information I'm getting now is the user ID and the ID of the followers, which later I want to analyse (for graph theory research.) I thought of a database because I might try add more information later as well.

Thank you.


texts:
I am not used to working on the postgres backend. So forgive me if I leave something obvious out.
I have postgres 9.4 running on 3 (debian) servers. #1 is the main prod server. #2 is slave to #1. #3 is for dev.
Currently #2 is out of sync with #1.  The #2 server rebooted and was stuck waiting for a password to restart. Several days later it was brought to my attention that #2 was down.  I went through the hosting site and was able to get the server back online. Postgres sync between #1 and #2 is broken. I do know that we have the WAL files backed up in an S3 bucket. What I can find online about this issue talks about rebuilding the server.
Is there a way to restart the sync without rebuilding the server?


texts:
I'm trying to restrict the access to the cube to certain dimension members. I Used the following function on the "Cell Data" tab of the role editor:

[Magatzems].[Codi Magatzem].CURRENTMEMBER = [Magatzems].[Codi Magatzem].&[27] or [Magatzems].[Codi Magatzem].CURRENTMEMBER = [Magatzems].[Codi Magatzem].&[57]

and it works ok on the members, but I don't get my totals calculated.

What am I doing wrong?

Thanks.


texts:
So, I created a PE32+ DLL, in an attempt to load it into SQL Server,

$ file example_dll.dll
example_dll.dll: PE32+ executable (DLL) (console) x86-64, for MS Windows

but when I try to compile it as an assembly, with CREATE ASSEMBLY [ok] AUTHORIZATION [dbo] ... WITH PERMISSION_SET = UNSAFE; I get,

Msg 6544, Level 16, State 1, Server x230, Line 1
CREATE ASSEMBLY for assembly 'ok' failed because assembly 'ok' is malformed or not a pure .NET assembly.
Unverifiable PE Header/native stub.

It as come to my attention that what Micrsoft calls CLR "safe" may just mean, .NET's C++. I know that C# is the prefered method of extending SQL Server, but regardless does Microsoft permit loading shared objects or DLL into the server at all? Can you extend SQL Server with "native code"?


texts: Database solution for a large matrix/very wide table

texts:
Suppose I have these two data frames (simplified for my question):

users

+---------+
| user_id |
+---------+
| 1       |
| 2       |
| ...     |
+---------+

articles

+------------+------------+
| article_id |    date    |
+------------+------------+
| a          | 2019-01-01 |
| b          | 2018-03-03 |
| ...        |            |
+------------+------------+

And a dense matrix of user-article pairs where each value is how much I predict each user would want to read each article (from 0 to 1):

+-----+------+------+-----+
|     |  1   |  2   | ... |
+-----+------+------+-----+
| a   | 0.54 | 0.99 | ... |
| b   | 0    | 0.7  | ... |
| ... | ...  | ...  | ... |
+-----+------+------+-----+

I have a web app that needs to do thing like return the top 10 most recommended articles for a single user, or the 11th-20th most recommended, for a given date range etc.:

query: (user_id=123) AND (news_date IN ('2019-04-01', '2019-05-01')) LIMIT 10 OFFSET 10

+---------+-------+------+
| news_id | score | rank |
+---------+-------+------+
| g       | 0.98  | 11   |
| d       | 0.97  | 12   |
| ...     | ...   | ...  |
| q       | 0.8   | 20   |
+---------+-------+------+

The challenge is that I have users and articles numbering in the tens of thousands, so I can't just store my matrix as a Postgres table due to its column limit.

I could store the recommendation scores in Postgres in a table as (user_id, article_id, score), which would be fast for querying, but this table would have 100M+ rows and be expensive to update, which I do daily.

My current solution is to store a single data frame (news_id, news_date, user_1_score, user_2_score, ..., user_n_score) as a gzipped Parquet file on disk, load the news_date and user_x_score columns, then filter, sort and slice. The only downside is that my web host has an ephemeral file system, so this file needs to be downloaded when the app boots. It's fast enough to get data during a web request, at least.

I don't know much about columnar data stores, but I have a feeling that one of these products might be good for my problem. Does anyone have an idea?


texts: Upgraded from SQL Server 2008 R2 Express to SQL Server 2014

texts: I've upgraded my SQL Server from the 2008 R2 Express edition to a full standard version of SQL Server 2014. Now I'm running them both side by side. But my problem is how can I abandon the 2008 version and only run on the 2014 version? I've check the properties on the SQL Server 2014, and it shows that I'm still with 2008 R2 Express edition. How is that possible?


texts: Cognos, modifying canned report. Namespace of tag doesn't exist

texts:
I want to modify a canned report to add more information to what it already provides. However, when I go to one of the data items, to see where it originates from so that I can know where to add my own data item from, I don't understand the expression given.

For instance, the finished Query has a data item Patients Name. Here is the expression for Patients name:[NO Group No Resource].[CPT With OUT Resource Proivder No Groups].[Patient Name]

However, there is no namespace[NO Group No Resource].

Can anyone help?


texts: How to list the missing multicolumn statistics?

texts:
I have used the database engine tuning advisor, DTA, in order to improve the performance of one of my databases.

It produced a list of recommendations, mostly multicolumn statistics.

These recommendations need then to be cross checked and tested, before applying them in production.

Statistics are used by the query optimiser (whitepaper on MSDN) to track distribution of values in indexes and/or columns.

Inspired by the following reading:

When is it better to create STATISTICS instead of creating an Index?

MY QUESTION IS

Other than the DTA, is there any other way to find out the missing multicolumn statistics?


texts: I need to copy a Hyperscale DB to another server in the same region/location, (the copy function is in coming soon state but not already available) does someone know how?


texts: search for something in the first 1000 records in the table

texts:
If I do the following MySQL query

select * from table where name Like '%john%' limit 5;

that mean the query will search all the mysql database table and return only the top 5 results that match the query where condition.

BUT, what if want to just search the top 1000 records in the database table that match the where condition and return only the first 5 rows , because I have 1,000,000 records in the table and i just want to search in the first 1000 records, what should i use?

this is the wanted query in human readable:

search the top 1000 records from the table which have the name like john and return only the top 5 records that match.

what is the corresponding in MySQL


texts: is there any way to left join the select result in MySQL 5.7

texts:
Now I am using this query to left join the result of my query:
select date_format(FROM_UNIXTIME(created_time/1000), '%Y-%m-%d') as dt,count(*)
from action_record a
where user_id = 16459
and action_type = 1
group by date_format(FROM_UNIXTIME(created_time/1000), '%Y-%m-%d')

left JOIN

select date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d') as dt,max(login_count) as login_count
from report_user b
where user_id = 16459
group by date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d')

on a.dt = b.dt

but it shows error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left JOIN
select date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d') as' at line 6

what should I to fix this problem?


texts:
I have a database dump that's about 140Gb.  (When loaded, the database is about 350Gb.)  I occasionally load copies of my dumped data into development and testing databases, which takes a while.

Any thoughts on what configurations I might be able to change during the restore in order to speed things along?

So far I have thought of:

in postgresql.conf:

synchronous_commit=off
checkpoint_timeout=1d

Although I'm less sure about that checkpoint timeout value.

in my shell

PGSSLMODE=disable

And at some point I will try copying the dump file to the target system so that pg_restore can run on the same box, and ideally use the unix domain socket, rather than going over a TCP/IP network.

(9.6 on Ubuntu 17.04)


texts: Need to break up a large file into smaller files to send via sp_sendsqlmail

texts: I have created a job to send a "report" to someone (actually two reports). My issue is that the query is returning some 150K+ rows of data (no lobs or xml or anything, so not large rows), but sp_sendmail is balking at sending that large of a file (apparently -- no job failure, but no email is generated. Nothing at all in the log). So, I think I need to break this into smaller files to send, but need a little help in doing that programmatically.


texts: AFTER UPDATE trigger that modifies the table on which it is defined

texts:
On MySQL 8.0  Trigger created:

DROP TRIGGER `Metrosky_spotters_2020_au`;
DELIMITER ;;
CREATE TRIGGER `Metrosky_spotters_2020_au` AFTER UPDATE ON `Metrosky_spotters_2020` FOR EACH ROW
UPDATE Metrosky_spotters_2020 SET NEW.trnmswid = 5000;;
DELIMITER ;

The trigger  loads okay.
The function to edit a row but in a change to a field in that row- then t=fails the trigger functionality with:

  Can't update table 'Metrosky_spotters_2020' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

The idea here is to add the current users MSWID (or login) (using 5000 as test)  to  trnmswid whenever a row item is updated.

I've also tried this in BEFORE UPDATE  and still fails.

Is SET command incorrect or what am I missing?


texts: Postgres: DROP TABLE and ALTER TABLE hangs because of locks

texts:
After issuing DROP TABLE or ALTER TABLE command pg_stat_activity table becomes full of Locks and the query hangs forever. Killing PIDs and reissuing query gives the same result. The problem disappears after the system gets restarted (I restart docker container for that).
How to investigate this issue?


texts: MySQL Workbench -- How to efficiently review large database tables?

texts:
I have a database table with 900,000+ rows, 20 columns, mostly string data.  Using MySQL Workbench, I want to page through the entire contents of the table to see what exactly has been inserted into the table (I am reviewing it because I believe my code somehow accidentally only partially loaded the contents of a CSV file and I want to understand why and where the process ceased loading the entire CSV file).

The problem is that, because of the somewhat large size of the DB table, running a "SELECT * FROM..." query with "Don't Limit" causes my computer to take far too long (over 10 minutes) to produce a result in MySQL Workbench's result grid and basically appears to freeze up.

Is there a way to make the MySQL Workbench result grid load much much faster?  Or should I be using a different tool?

I'm a bit of a noob, so it's possible I'm doing something pretty stupid here.
Thanks in advance!


texts: ~2 Billion row fact table in star schema on Oracle. Advisable?

texts:
Let me start with: I am not a DBA; I am a backend developer.

It has been suggested that, for a report schema, I implement a star schema where-in the fact table can have ~2B rows before being archived. ~1M rows a day will be added to said table. The information in the report can be no more than 15 minutes stale and page load time must be < 7s

The report to be offered will be a sort of "summary-and-drill-down" where in when a selected summary portion is selected a subset of the rows that made up said summary will be displayed in another section Example:

I am inclined to move to a NoSQL store like Cassandra because I am really concerned about a fact table (and perhaps attribute tables) stretching to 2 Billion rows. Hoping someone here can shed some light on both the use of a star schema for this and how well I can expect Oracle (or any RDBMS, for that matter) to handle selecting and joining through a table with ~2B records.

Thanks


texts:
I can connect to Heroku with psql passing a URI:

$ psql postgres://user:password@ec2-54-243-xxx-xxx.compute-1.amazonaws.com:5432/d7xkxididkdi

I want to do the same with pgAdmin:

$ pgadmin3 -qc postgres://user:password@ec2-54-243-xxx-xxx.compute-1.amazonaws.com:5432/d7xkxididkdi

but I get the error message:

Unknown token in connection string: <the passed string>

Is it possible to pass a URI as parameter to pgAdmin?


texts: How do I access ETW Traces on SQL Server for linux?

texts:
I have SQL Server running in a docker image. I have an extended event session running on it. I can connect to it fine and view live data with SSMS. I can use T-SQL to view the histogram target. If this was a windows box, I could use logman.exe to view and manage the event trace session. How do I do it in SQL Server for Linux?

I am absolutely sure that the ETW is getting created.

SELECT
    xes.name AS [Session Name],
    CAST(xest.target_data AS xml) [Histogram XML],
    xest.target_name
    FROM sys.dm_xe_session_targets xest
    INNER JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
    WHERE xest.target_name = 'etw_classic_sync_target';

<ETWTarget truncated="0">
  <DefaultETWSession>
    <Name>XE_DEFAULT_ETW_SESSION</Name>
    <Path>C:\Users\Client\Temp\XEEtw.etl</Path>
    <BufferSizeKb>128</BufferSizeKb>
    <FileSizeMb>20</FileSizeMb>
  </DefaultETWSession>
  <DefaultXESession>
    <Name>XE_DEFAULT_ETW_SESSION</Name>
  </DefaultXESession>
</ETWTarget>

However, if I execute find / -iname XEEtw.etl inside bash on the docker container I get no results. I know that SQL Server on Linux is running its own internal copy of Windows NT. I just don't know if that ETW is exposed.


texts: How to point tablespace in Oracle's pre-built Developer Day VM to another mount point

texts:
I have downloaded and started up Oracle's pre-built OTN Developer Day VM. The VM is running on an SSD. I want to load some data for development purposes. I am planning to create a new user and load the data under this new user's schema.

I would like to keep this data outside the SSD. I already have a mount point to an HDD inside the VM (as a network share). This mount point is a Samba share from another VM in the same machine.

My question is, how do I create a tablespace for the datafiles on this HDD mount point?

It would be nice if I can point undo and temporary tablespaces also to the HDD mount point.

My objective is to keep the VM size as low as possible.

I will appreciate if someone can point me in the right direction.


texts: Calculating percentage growth month by month

texts:
I have a basic database for a retail store (4 tables) and am trying to run a query that will calculate the percentage growth (or decline) month by month in revenue for the store. The ORDERS table holds the OrderPrice info for each month.

I am struggling to find a query that will do this, does anyone know if one exists?

The DB is here: https://www.db-fiddle.com/f/qS515ShyFGxgj4h6Ga6HA3/0#&togetherjs=009rG6HwUe

I have come up with this so far to SUM the revenue totals month by month:

SELECT * FROM (
SELECT Coalesce(MONTHNAME(ORDERS.OrderDate), 'Grand Total') AS OrderDate,
SUM(ORDERS.OrderPrice) as SumOrderPrice
FROM ORDERS
WHERE OrderDate BETWEEN '2018-01-01' AND '2018-12-31'
GROUP BY MONTH(ORDERS.OrderDate) with ROLLUP)
AS T;


texts: Select values of a column into one row - Sql Server


texts: Should a DBA be able to write basic T-SQL?

texts:
I am a SQL Server database developer (not a DBA) and I find myself frustrated working with my company's new DBA. I'm trying to figure out whether my expectations are unreasonable.

For the sake of clarity, I'll oversimplify: I'm defining a database developer as  one who writes stored procedures, views, functions, etc that are used in applications, such as a web application. I'm defining a DBA as one who creates maintenance plans for backups, installs the SQL Server on new servers, does patches and other mysterious things.

Sometimes, in order to do problem solving on production data I'll need to know something about it, like how many rows are in a table or how many rows there are where a column has a specific value. In the past, the DBA and I would have a phone conversation where I would ask my question, he would fire off the query and I would have my answer.

If you are a DBA (not a developer) and I asked if you could give me a count of the number of rows in MyTable, would you know how to write "select count(*) from MyTable" ?

No bashing, please, of me or my DBA. Just tell me if this query would be in your skillset if you were a mid-level DBA on a large project.


texts:
I have a dynamically built query with a where clause that looks like this:
where column in ('a', 'a', 'a', 'b')
The actual data is a LOT longer with a lot of duplicates, as in the example above where a is repeated three times.
Is there a potential performance hit for a very large WHERE clause using an IN with a large number of values, many of which are duplicates?


texts: Dividing comma separated strings into columns

texts:
I have a problem with dividing comma separated values. I have 2 columns with comma separated values, for ex:

ID        Name
1,2,3     Ab,cd,ef

I want the columns divided as:

Id  Name
1   ab
2   cd
3   ef

I used:

 xmltable('r/c' passing xmltype('<r><c>' || replace(ID,',','</c><c>') || '</c></r>')
          columns ID_NEW varchar2(400) path '.') <br/>

Output was:

ID      Name
1       ab,cd,ef
2       ab,cd,ef
3       ab,cd,ef

I'm using Oracle 10g.


texts: How can we improve this PIVOT operation? ( SQL Server 2008 R2 )

texts:
We have this query, that runs in 1:10. We need to improve this. This is just a test query, but we will implement this upgrade on our real query.

select top 100 *
from (
    select codCliente
        ,Resposta
        ,NrQuestao --,COUNT(1) as Qtde
        ,(
            select COUNT(1) as qtde
            from tblRespostaINT T2
            where T2.codCliente = T0.codCliente
                and T2.codNrQuestao between 9
                    and 29
            ) as QResp
    from tblRespostaINT T0
    inner join tblQuestao T1 on T0.codNrQuestao = T1.codNrQuestao
    where T0.CodQuestionario = 1
        and T1.codNrQuestao between 9
            and 29
                --and codCliente = 5156
                --Group by codCliente, Resposta,NrQuestao
    ) as tblx
Pivot(Sum(resposta) for NrQuestao in (
            [I_1_P14_1]
            ,[I_2_P14_1]
            ,[I_3_P14_1]
            ,[I_4_P14_1]
            ,[I_5_P14_1]
            ,[I_6_P14_1]
            ,[I_7_P14_1]
            ,[I_9_P14_1]
            ,[I_10_P14_1]
            ,[I_11_P14_1]
            ,[I_12_P14_1]
            ,[I_13_P14_1]
            ,[I_14_P14_1]
            ,[I_15_P14_1]
            ,[I_16_P14_1]
            ,[I_17_P14_1]
            ,[I_18_P14_1]
            ,[I_19_P14_1]
            ,[I_20_P14_1]
            ,[I_21_P14_1]
            ,[I_22_P14_1]
            )) tbl

I've added some indexes but none of them helped. This is the execution plan:

if I add loop in the inner join, we have low CPU usage on HASH MATCH, but a high percentage with the index seek:

It takes 30sec to run the insider query:

select codCliente
    ,Resposta
    ,NrQuestao --,COUNT(1) as Qtde
    ,(
        select COUNT(1) as qtde
        from tblRespostaINT T2
        where T2.codCliente = T0.codCliente
            and T2.codNrQuestao between 9
                and 29
        ) as QResp
from tblRespostaINT T0
inner join tblQuestao T1 on T0.codNrQuestao = T1.codNrQuestao
where T0.CodQuestionario = 1
    and T1.codNrQuestao between 9
        and 29
            --and codCliente = 5156
            --Group by codCliente, Resposta,NrQuestao

What can we do? is there some index tip I should know for problems like these?Should we use only joins?

Query Plan:

XML plan: http://pastebin.com/raw/anLifzBC


texts: DB2 online restore - “SQL1035N The database is currently in use.”

texts:
I am trying to do ONLINE RESTORE ROLLFORWARDING TO END OF LOGS AND COMPLETE. When I run this command I am getting error SQL1035N The database is currently in use. It does not allow any connections to it!

If I deactivate database and then run command I can restore but then my database is not available for users but it should be becuase it is live production system 24/7. How to resolve this?


texts: how to clear only the query plans from the current session?

texts:
I am working on tuning a query on a very busy server, where I don't want to clear the cache, using DBCC FREEPROCCACHE;

I know it will cause at least a CPU spike, and in this case I don't want it.

here in this article it shows how to clear the cache for an specific database only.

-- Example 2 (Ballpeen hammer)
-- Remove all elements from the plan cache for one database
-- Get DBID from one database name first
DECLARE @intDBID INT;
SET @intDBID = (SELECT [dbid]
                FROM master.dbo.sysdatabases
                WHERE name = 'AdventureWorks');

-- Flush the procedure cache for one database only
DBCC FLUSHPROCINDB (@intDBID);

I would like to do something like this:

-- Example 3 (Scalpel)
-- Remove one plan from the cache
-- Get the plan handle for a cached plan
SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%/* GetOnlineSearchResultsMonday %';

-- Remove the specific plan from the cache using the plan handle
DBCC FREEPROCCACHE (0x05000800F7BA926C40C15055070000000000000000000000);

  Is there any way I can  locate the query that I am working on, or at
  lease my own session in the cache?

I am tuning the query below:

checkpoint

set statistics io on
set statistics time on

    SET NOCOUNT ON;

    DECLARE @OrderStartDate DATETIME
    DECLARE @OrderEndDate DATETIME

    SELECT @OrderStartDate = '27-FEB-2016'
    SELECT @OrderEndDate = '28-FEB-2016'

    SELECT
           op.lngPaymentID
          ,op.strBxOrderNo
          ,op.sintPaymentTypeID
          ,op.strCurrencyCode
          ,op.strBCCurrencyCode
          ,op.decPaymentAmount
          ,op.decBCPaymentAmount
          ,ap.strAccountCode
          ,o.sintMarketID
          ,o.sintOrderChannelID
          ,o.sintOrderTypeID
          ,CASE WHEN opgv.lngpaymentID IS NULL THEN NULL
                     -- Not a Voucher = Null
                WHEN gvp.strIssuedBxOrderNo IS NULL THEN 0
                ELSE 1 END AS [IsPromoVoucher]
                     -- Is a Voucher - check type
          ,o.sdtmOrdCreated

    FROM    tablebackups.dbo.tblBOrderItemOrderComplete com

            INNER JOIN tablebackups.dbo.tblBOrderPayment op
                    on op.strBxOrderNo = com.strBxOrderNo

            INNER JOIN tablebackups.dbo.tblBOrder o
                    ON op.strBxOrderNo = o.strBxOrderNo

            INNER JOIN tablebackups.dbo.tblBOrderItem as oi
                    on com.strBxOrderNo = oi.strBxOrderNo
                   AND com.sintOrderSeqNo = oi.sintOrderSeqNo

            INNER JOIN tablebackups.dbo.tblBAccountParticipant ap
                    ON o.lngAccountParticipantID = ap.lngParticipantID

            LEFT OUTER JOIN tablebackups.dbo.tblBOrderPaymentGiftVoucher opgv
                         ON op.lngPaymentID = opgv.lngPaymentID

            LEFT OUTER JOIN tablebackups.dbo.tblBGiftVoucher gv
                         ON opgv.strVoucherNumber = gv.strVoucherNumber

            LEFT OUTER JOIN tablebackups.dbo.tblBGiftVoucherPromotion gvp
                         ON gvp.strIssuedBxOrderNo = gv.strIssuedBxOrderNo

    WHERE   com.LoadDate >= @OrderStartDate
    AND     com.LoadDate < @OrderEndDate
    AND     o.sdtmOrdCreated >= @OrderStartDate
    AND     oi.decCatItemPrice > 0

set statistics io off
set statistics time off


texts: How to best handle the situation where my "table creation code" and my actual live tables have become desynced?

texts:
I'm developing a system which, relevant to this question, consists of PostgreSQL database tables.
When I first created the SQL code to CREATE TABLE, I did think it through. However, over the years, I've thought of improvements, fixed bugs, and implemented new functionality. Each time I do this, on the "live" table containing my data, I then make sure to make the appropriate change to the code file, so that (at least in theory) I could run it on a different computer or fresh PostgreSQL installation and get an identical "structure".
However, as we all are painfully aware of, it's impossible to change the column order. So already that is a change between my live table and the code. There's also the possibility that I simply forget to change it after fiddling with some problem for hours.
Either way, it's not a good feeling that my table creation code and my live tables are "slightly different", if only in the column order and some of the identifiers for constraints which I never refer to in my code, and thus "don't matter". But it's not a nice feeling.
I can't just nuke the tables and re-run my code to recreate the structure, because then I lose all the data in them!
Is there any reasonable way for me to "sync" my code and the live table structure while retaining the data, in a safe and problem-free manner?


texts: Delete query from SQL Server 2008 to linked Postgres

texts:
I want to delete some data from SQL Server 2008 in linked PostpresSQL.
I found this solution:

--DELETE data
DELETE FROM
OpenQuery('MSDASQL','SELECT  name  FROM sometable wHERE name LIKE ''test%'' ')

but how can I delete some data from SQL Server query in PostgresSQL?

Query from SQL Server

select name from myMSSQL_table


texts: MYSQL: Stored function to replace category ids with names

texts:
I have a stored function which should replace category ids in column path with names from name column. Then store result string into a new column named path_long.

I use Debian 8, MySQL v5.5.

Example

I have a column named path with content like '/426/427/428'. I would like to replace category id numbers with category names. Result would be like '/Computers/Other accessories/Laser printers'.

I have this stored function:

CREATE DEFINER=`root`@`%` FUNCTION `decode_path`(
    `path_input` MEDIUMTEXT
)
RETURNS mediumtext CHARSET latin1
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''

BEGIN

CREATE TEMPORARY TABLE IF NOT EXISTS
t1
ENGINE=MyISAM
AS (
    SELECT
         n AS nr
         , SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM @path_input)), '/', tmp.n), '/', -1) AS catid
         , (
                SELECT name FROM category
                WHERE category.id = catid
            ) AS name
         , (
                SELECT path FROM category
                WHERE category.id = catid
            ) AS path
    FROM
      (SELECT @rownum := @rownum + 1 AS n, category.id, category.name, category.path
            FROM category
            CROSS JOIN (SELECT @rownum := 0) r
        ) AS tmp
    GROUP BY catid
    ORDER BY
       n
);

INSERT INTO t2
SELECT group_concat(name SEPARATOR '/') as path_long FROM t1;

RETURN (SELECT path_long FROM t2 limit 1);

END

Here is the test DDL:

CREATE TABLE `category` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `path` VARCHAR(100) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=429
;

Also test data:

INSERT INTO `category` (`id`, `name`, `path`) VALUES (1, 'A', '/1');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (2, 'B', '/1/2');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (3, 'C', '/1/2/3');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (4, 'D', '/4');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (5, 'E', '/4/5');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (6, 'F', '/4/5/6');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (7, 'G', '/7');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (8, 'H', '/7/8');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (9, 'I', '/7/8/9');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (426, 'Computers', '/426');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (427, 'Other accessories', '/426/427');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (428, 'Laser printers', '/426/427/428');

Unfortunately I can't change the design. It is given in the software. Horrible schema or not, horrible database or not, this is what I have. The framework uses this schema, and the database is MySQL. I have to do the query on this system and I have to get the desired result.

Using query:

SELECT decode_path(category.path) as decoded FROM category

Problem

The query results the following:

decoded
A
A
A
A
A
A
A
A
A
A
A
A

Source column looks like this (showing undecoded paths):

path
/426/427/428
/1/2/3
/4/5/6
/7/8/9

Desired result column should be like this (showing decoded paths):

path_long
/Computers/Other accessories/Laser printers
/A/B/C
/D/E/F
/G/H/I

Basically it should decode a path having category ids to readable path format using category names.

How to fix the stored function to make it work?


texts: Page Break is splitting in the middle of a single row in SSRS

texts:
I have an SSRS report for an Invoice and it generally works perfectly but occasionally it will page break in the middle of a row in the main Tablix. The row will split and leave part of the text on one page and the rest on the next. The Tablix has no inherent page breaking. I was just relying on it to break between rows (regardless of which rows). There are a couple rows that repeat on each page. There is a second Tablix below the Detail one with Summary Totals. This is just some background info about the report. I'm relatively new to SSRS and haven't had the greatest luck with the formatting thus far.

Thanks for any help!


texts: How to see what is cached in memory in SQL server 2008?

texts: Is there a way how to find out what is cached in SQL Server 2008 R2? I have found the following nice article: http://blog.sqlauthority.com/2010/06/17/sql-server-data-pages-in-buffer-pool-data-stored-in-memory-cache . However, I would like to know how much data (e.g. in percentage and KB) are stored of each table and index. Is there some simple way how to obtain such data?


texts: What steps are required to setup PostgreSQL and create a db in Windows?

texts:
I have installed PostgreSQL 12 from https://www.2ndquadrant.com/en/blog/pginstaller-install-postgresql/. and put \C:\...\PostgreSQL\12\bin in the PATH.
In an admin powershell I execute psql.exe -U postgres and enter the password I chose in the installation wizard. Now I'm in a shell. I want to create a database so I run createdb but it seems the database is not created:
postgres-# \l
                                          List of databases
   Name    |  Owner   | Encoding |       Collate       |        Ctype        |   Access privileges
-----------+----------+----------+---------------------+---------------------+-----------------------
 postgres  | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 |
 template0 | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 | =c/postgres          +
           |          |          |                     |                     | postgres=CTc/postgres
 template1 | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 | =c/postgres          +
           |          |          |                     |                     | postgres=CTc/postgres
(3 rows)

postgres-# createdb demo
postgres-# absolute nonsense
postgres-# \l
                                          List of databases
   Name    |  Owner   | Encoding |       Collate       |        Ctype        |   Access privileges
-----------+----------+----------+---------------------+---------------------+-----------------------
 postgres  | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 |
 template0 | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 | =c/postgres          +
           |          |          |                     |                     | postgres=CTc/postgres
 template1 | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 | =c/postgres          +
           |          |          |                     |                     | postgres=CTc/postgres
(3 rows)

I assume that my command createdb demo fails without error response as the intentionally wrong next command absolute nonsense fails as well. But I don't know how else to create a db, and moreover I'd like to know why this doesn't work and how to debug this.


texts: Check constraint on nullable data, how to approach?

texts:
This is one of the tables in my database. Constraints omitted for clarity.

CREATE TABLE [Person].[Person]
(
    [ID]                     INT           NOT NULL IDENTITY,
    [Forename]               VARCHAR(16)   NOT NULL,
    [Surname]                VARCHAR(32)   NOT NULL,
    [Gender]                 CHAR(1)       NOT NULL DEFAULT 'U',
    [DateOfBirth]            DATETIME      NULL,
    [HobbiesAndInterests]    VARCHAR(256)  NULL,
    [AdditionalInformation]  VARCHAR(512)  NULL,
    [LocalCentreID]          INT           NOT NULL DEFAULT 0,
    [EmergencyContactID]     INT           NULL,
)

The problem I have involves the date of birth field. If a value is provided, I need to run a check against the date. If a value is not provided, the check does not need to run.

I basically need to check if the person is 18 or older based on the date of birth. If a date of birth isn't provided, then there's no need for it to be checked.

I've thought a few different ways I can address this:

The first is obviously to place a check constraint on the date of birth field but I think this fails if the date is null.

Another option is to use a scalar UDF inside the check constraint.

The final option I've thought of is to use an AFTER INSERT/UPDATE trigger to check the date and rollback if the date is invalid.

I've spoken to a colleague and he says technically I'm checking a business rule in the database, so this check should be pushed off to the application.

Is there a 'right' way to approach this problem or is it basically what the developer thinks is best?


texts: Why would MongoDB resident memory suddenly drop?

texts:
We run Mongo 3.2.8 on Windows 2008. Since the start of this week the server MongoDB runs on has effectively run out of memory every morning. The server has 16gb of RAM. When we see this behaviour mongod.exe reports 6gb of memory, but when we kill the process it frees up around 12gb of memory.

When we leave mongostat running against the server we see vsize sits at 6.3gb consistently, but res drops from 6.0G to 52.0M over a period of about 10 minutes, during which time memory consumption on the boxes goes up until it hits ~100%.

What could cause that to happen? We're using WiredTiger, and we do have some other application processes running on the same server


texts:
I have created a role called "role1" with these steps :

postgres=# CREATE ROLE role1;
CREATE ROLE
postgres=# GRANT CONNECT ON DATABASE paybills TO role1;
GRANT
postgres=# \c paybills
You are now connected to database "paybills".
paybills=# GRANT SELECT, UPDATE, INSERT, DELETE ON myeg_users TO role1;
GRANT

The steps above are all successful.

However, when I run the command below :

paybills=# GRANT role1 TO surentran;
ERROR:  role "surentran" does not exist

As you can see, an error is thrown. I am in the db called "paybills", and the user details are stored in a table called "myeg_users" within this database.

How do I add all the users in the "myeg_users" table into the role "role1"?


texts: reading content of master database at a different server

texts:
I have a query for example

select * from sys.databases
select * from sys.configurations

I believe this information is stored at master database.
I would like to have a look at configuration settings in the past.

Can I restore msdb/master databases at different server as regular user databases and make sense of those mentioned queries?

Looks like select * from restored_master.sys.databases just ignores current database. I know an option to replace master/msdb using startup parameters - that should work, but a bit longer option and need compatible version of server.


texts:
I am new to mysql DB.
I have set log_slow_verbosity value to full.

root@localhost/(none)> SELECT @@global.log_slow_verbosity;
+-----------------------------+
| @@global.log_slow_verbosity |
+-----------------------------+
| microtime,query_plan,innodb |
+-----------------------------+

But I am unable to see innodb,query_plan info in the mysqld-slow.log


texts: How Does Max Server Memory Affect PLE on a Host That Is Not Memory Bound?

texts:
I came across an unusual scenario on an instance where only half the physical RAM was being utilized but I was observing PLE values between 1-3 seconds constantly.

Windows Server 2003, Enterprise 5.2.3790 (32-bit) SP2
4 GB RAM
2 Xeon 2.3 GHz CPU
SQL Server 2008 SP2 (10.0.4000)

Min/Max Server Memory were set at defaults and the most resource intensive query was the backup history check by Quest Spotlight. Only 1.6 GB of RAM was utilized, 1.5 of which by buffer and procedure cache.

After setting Max Server Memory to 3 GB, PLE started to climb and is currently at 21 minutes.

I am wondering why Max Server memory would have this positive impact on PLE when less than half the onboard RAM is currently utilized. My first thought is O/S memory trimming was in play before the change in the setting but how can I confirm this?


texts:
As a real newcomer to Transactional Replication I would appreciate some guidance.

I have a production database on premise and the need to take two tables from here and copy them regularly to a SQL Azure database. This is so that our suppliers can run a very simple label printing program from their premises so they can stick labels on the goods they supply to us.

We raise a Purchase Order in house on our ERP system and send it to our supplier. They produce the goods and print the labels. Stick the labels on the goods and send them to us.

That is it. Nothing fancy in the way of filters or otherwise (This can all be handled by the program).

Is this a fairly easy thing to set up? Is there an idiots guide anywhere that could show me how to go about this?


texts: how to stop OracleRemExecServiceV2 from cmd?

texts:
I have Oracle 12c running on Windows 7.

When I look at the Oracle's specific services (Control Panel\System and Security\Administrative Tools\Services), I have the following 6 services:

So far, everything is normal. I can even start or stop every service by double clicking on it, and then clicking the Start/Stop button.
However, for convenience, I want to create a .bat file with commands to open/close all the services. I run in command prompt commands such as net stop  [service], and they all work fine except for OracleRemExecServiceV2.

As you can see, I get System error 5 - access is denied. How can I solve this problem?

UPDATE:

i ran cmd as admin. Please see the below screenshot. In the Services window, OracleRemExecServiceV2 remains in the 'Starting' state for a long time, without changing to 'Started'.

One more thing: i double clicked the OracleRemExecServiceV2 and, in the General tab, i noticed that the Path was something like C:... (i cannot remember), while the database is installed on D, and all the other five Oracle services have the Path beginning with D:\app\OracleHomeUser\product\12.1.0\dbhome\BIN. So i changed it accordingly, to  point to RemoteExecService.exe in the BIN folder in the D directory. Is it ok what I did? otherwise, what would be the normal value for the path of this service?


texts: Create a view containing multiple rows per customer

texts:
I'm creating a TASKS view that should list up to 10 different "todo" tasks per customer in my system.

Example tasks could be "check customer order", "send customer order", "enter invoice in external system"... At a given time, there can be multiple active tasks for a customer.

All these tasks are performed manually and I want to consolidate them all in a view so that I can display them in a single screen, send email notifications when they are due, etc...

My current query looks like this :

SELECT 1 as task_id, customer_id, created as since, confirmed as done
FROM CUSTOMERS
WHERE cancelled IS NULL
...
UNION ALL
SELECT 12 as task_id, customer_id, invoice_paid as since, invoice_registered as done
FROM CUSTOMERS
WHERE cancelled IS NULL AND has_invoice

The query is working fine but the performance is not so great.

Even queries like SELECT * FROM TASKS where customer_id = 12 take a few seconds.

Reading the CUSTOMERS table 12 times to generate only 0 or 1 row per task_id seems wasteful.

Is there any way to improve it ? I had thought of something along these lines (but it is not valid SQL) :

SELECT * FROM (
    SELECT VALUES (1 as task_id, customer_id, created as since, confirmed as done, cancelled_date is not null as ignore)
    ,...
    , (12 as task_id, customer_id, invoice_paid as since, invoice_registered as done, cancelled_date is not null or not has_invoice as ignore)
    FROM CUSTOMERS
) a
WHERE not a.ignore;


texts: INSERT from a SELECT combined with a single value

texts:
I have this query that returns several rows of item_id:

select item_id from properties where name like 'body';

And I have the second one that returns 1 row of tag_id:

select id from tags where name ilike '%hoax%';

I want to use both results to create new rows in a third table item_tags so that I can insert each item_id from the first query with the tag_id from the second query.

I'll have something like:

INSERT INTO item_tags (item_id, tag_id) VALUES (item_id1, tag_id);
INSERT INTO item_tags (item_id, tag_id) VALUES (item_id2, tag_id);
INSERT INTO item_tags (item_id, tag_id) VALUES (item_id3, tag_id);
 ...

The 1st query returns over 800 rows. How can I automate the insert?
psql 9.1.11, Postgres 9.1.4.


texts: misplacing of on condition in a join was executing successfully instead of throwing a syntax error

texts:
I have executed the following CTE with joins

;with cte(asd) as(select 601
union all
select 602)
,cte2(fgh) as (select 601 union all
select 602 union all
select 603 union all
select 604 union all
select 605 union all
select 606 union all
select 607 union all
select 608 union all
select 609 union all
select 610 union all
select 611 union all
select 612)
select p.* from cte2 p
inner join cte c
inner join cte2 c2 on c.asd=c2.fgh
on p.fgh=c.asd

the result of the above query was as follows:

The execution plan for the query was as follows:

I was happy with the result, now I cross checked my query which I have used above, identified that there was a misplaced near on condition.

The misplaced join was as follows:

inner join cte c
inner join cte2 c2 on c.asd=c2.fgh
on p.fgh=c.asd

As per my understanding there must be a on condition after inner join, but sql server didn't throw an error but executed and results were as expected.

After I changed the on condition and verify the both results and execution plan both were same.

;with cte(asd) as(select 601
union all
select 602)
,cte2(fgh) as (select 601 union all
select 602 union all
select 603 union all
select 604 union all
select 605 union all
select 606 union all
select 607 union all
select 608 union all
select 609 union all
select 610 union all
select 611 union all
select 612)
select p.* from cte2 p
inner join cte c
on p.fgh=c.asd
inner join cte2 c2 on c.asd=c2.fgh

Can any one please explain why this thing happens, how sql server was executing?


texts: Regular Data Space Specification vs Text/Image Filegroup

texts: I want to change the FileGroup of a table for FG01 to FG03 . but in properties of table there is the Regular Data Space Specification  and Text/Image Filegroup which have the list of FileGroups, what are the differences?


texts: Slow Linked Server INSERTs on SQL Server 2014

texts:
My local and remote servers were all using SQL Server 2008 R2 but the local ones were recently upgraded to SQL Server 2014 and the performance of Remote Queries has been crippled (literally from minutes to hours).

It appears that the problem is with sending data to the Remote Server so, as long as I am just reading data from the Remote Server, it performs fine except that, if the query is based on data at the local end, it seems to be effectively sending data to the remote server. I cannot setup the Linked Servers on the Remote Server end so I cannot make the remote server pull from the local one.

In most cases I can rewrite the queries to pull the data from the remote server into a local table, filter the local data, and then just send the relatively few updates to the remote server - Still slower than it was, but functional.

However, when doing anything involving lots of records, it is unuseable. The classic example is this query (originally it was doing a more complex query on the local server so I changed it to first populate a local copy of the final table so that I can do the simplest possible query for the INSERT);

INSERT INTO [REMOTE-DB].Databasename.dbo.Test_Table
SELECT Person_Nbr, FullName, Birth
FROM Test_Table WITH (nolock)

Even when I try OPENQUERY it seems no faster;

INSERT OPENQUERY ([REMOTE-DB], 'SELECT Person_Nbr, FullName, Birth FROM Databasename.dbo.Test_Table')
SELECT Person_Nbr, FullName, Birth
FROM Test_Table WITH (nolock)

If it weren't that this worked almost instantly on the older version of SQL Server, I would accept this as a fact of life but there is such a huge difference in performance that it seems that there must be some obvious fix that I am missing. I don't have control of the local servers but the DBA for them claims that the database configuration is consistent with the old setup.

Do you have any ideas what I can do to fix this or what I can point the DBA at?


texts: Can not run Sys.dm_exec_requests during backup restore

texts:
We do database creation through an application,i am  seeing below error when running below DMV(connection context is master)

query ran :

SELECT session_id as SPID,wait_type,wait_resource,last_wait_type,
 command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r with(nolock)  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

Error thrown :

Msg 927, Level 14, State 6, Line 5
Database 'XXXXXX' cannot be opened. It is in the middle of a restore.

Please Note this works some times and it fails some times with above error...when it works wait type is PREEMPTIVE_OS_CREATEFILE if this helps

Any ideas on why DMV throws this error.I expected DMV to work.

SQl version:

Microsoft SQL Server 2014 (SP2-CU10-GDR) (KB4052725) - 12.0.5571.0 (X64)
    Jan 10 2018 15:52:08
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

SSMS related info:

   Microsoft SQL Server Management Studio                       12.0.5571.0
    Microsoft Analysis Services Client Tools                    12.0.5571.0
    Microsoft Data Access Components (MDAC)                     6.3.9600.17415
    Microsoft MSXML                     3.0 6.0
    Microsoft Internet Explorer         9.11.9600.19541
    Microsoft .NET Framework            4.0.30319.42000
    Operating System                    6.3.9600

screenshot for error:

restore command ran:

  RESTORE DATABASE [somename] FROM  DISK = N'M:\path\Shares\55543f86-b817-4bc2-aaa3-baff0fc632c8.bak' WITH  FILE = 1,
    MOVE N'xxx' TO N'M:\path\somename.mdf',
    MOVE N'xxx_1' TO N'M:\path\FTIftrow_somename_1.ndf',
    MOVE N'xxx_log_1' TO N'M:\path\somename_log.ldf',  NOUNLOAD,  STATS = 5

some more details on this restore, this restore also takes a awful lot of time, which is being asked here ..

Restore database stuck at 100% progress


texts: Performance Monitor % Processor Time metric difference between _Total and sqlservr.exe

texts:
I have SQL Server running on 32-core CPU, and I use Performance Monitor (perfmon.msc) to collect data on Processor: % Processor Time (Instance - _Total) and Process: % Processor Time (Instance - sqlservr.exe)

So for example for day 2019/07/21 (Sunday), it shows average values:

Processor: % Processor Time (Instance - _Total) - 3.959
Process: % Processor Time (Instance - sqlservr.exe) - 107.312

Why is such a difference ?

I expected sqlservr.exe to show values like 2.900 or 3.100 - something like this, but not 107.312

How to correlate _Total to sqlservr.exe ?

I am using MaxDOP limited to 8 server level setting on SQL Server, if this is any help


texts: Running a number of .sql files using SQLCMD

texts:
I'm trying to put together a batch file to run a large number of .sql files without doing so individually.

So far I have:

@echo off
echo "Enter Server"
set /p SERVER=

echo "Enter Database"
set /p DATABASE=

del *.log

for /r %%i in (*.sql) do sqlcmd -E -S %SERVER% -d %DATABASE% -i"%%i" -m0 -o"%%i.log" -w500

pause

This works as far as it goes.

However what I really want to do is route any errors to a separate .err file, so that it is very clear at a glance which of the (100+) files failed.

I saw the :Error parameter, but I'm not sure how to apply it here.

Is there an easy way to acheive this?


texts: SQL Server x64 DBCC Shrinkfile Dies not release Unused space (never ends No errors)

texts:
I have a 1.8TB Production database in my test environment on a 2TB drive. Currently there is only 1 .MDF and 1 .LDF (on a separate drive).  The Database has had some major cleanup of unused indexes, Archived tables, and de-fragmented.  The actual data size being used is now only 300GB (after an SP_Spaceused) of the 1.8TB container.

I have tried multiple DBCC Shrinkfiles (larger and smaller Target sizes varying from 100MB to 2GB) without success.  The job never fails, but after 22 hours of running (at multiple attempts of sizes) I stopped the job.  No errors, Database is in simple recovery mode, drives are not full, nothing in windows or SQL error logs.  I've also tried the Truncate option that finishes successfully, but no space is released.

Why do I want to shrink you might ask?  I want to migrate from 1.mdf to (1.mdf + 3.ndf) data files for improved performance (on a RAID 10 set of Drives).  I don't have enough disk space on my 2TB drive now to do it all on the same RAID Set.

My thoughts were, shrinkfile (truncate) or Shrinkfile Target in smaller increments.  The truncate completes successfully and SQL thought the minimum size would be around 300GB.  Unfortunately, the DB only shrunk to 1.2TB initially and I believe there is more unused space that could be freed.  Its like there is some kind of Ghost record or row that cannot be moved in order to reclaim that unused space.

I did run Integrity Checks with physical, indexes and data purity which reported no errors.  I can shrink other databases on the same server without any troubles...just this one that is giving me trouble.

Has anyone Run into this?


texts: MySQL: Granting permission for CREATE VIEW

texts:
I created a new database schema as 'company_security' and populate the database with the table structure of very popular Company-Employee tables which contains DEPARTMENT,DEPENDENT,DEPT_LOCATIONS,EMPLOYEE,PROJECT & WORKS_ON tables. Then I created a new user in a root user session as,

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password1'

What I wanted to do is create a view WORKS_ON1(Fname,Lname,Pno) on EMPLOYEE and WORKS_ON from user1. To do this, first I granted necessary permission as root to user1 as follows.

GRANT SELECT ON company_security.WORKS_ON TO 'user1'@'localhost'
GRANT SELECT company_security.EMPLOYEE TO 'user1'@'localhost'
GRANT CREATE VIEW ON company_security.* TO 'user1'@'localhost'

Then I flushed the privileges and this was the output for SHOW GRANTS for 'user1'@'localhost'

Then I tried to create the view from user1 as,

CREATE VIEW WORKS_ON1 AS select Fname,Lname,Pno from EMPLOYEE,WORKS_ON;

but I got an error like this,

ERROR 1142 (42000): CREATE VIEW command denied to user 'user1'@'localhost' for table 'WORKS_ON1'

I have found a similar problem in MySQL forum but the MySQL DOC reference link he mentioned in the solution seems like removed.

So my question is how to solve this problem? even I tried to solve this by granting permission for create view in a global level for user1 but that failed too. Either we cannot grant permission for the WORKS_ON1 view as the error saying because it does not exist in the database already.

MySQL version I'm using: mysql  Ver 14.14 Distrib 5.6.28, for debian-linux-gnu (x86_64) using  EditLine wrapper


texts: How to convert Bytes to GB using sql query

texts:
Im trying to verify value that have been changed in the DB using query. Howeverm it return the value I passed (in GB) to bytes.
I wanted to verify to esnure value stored in (bytes) is correct when I make comparison with gigabytes.

set serveroutput on
DECLARE
static_value VARCHAR2(4000);
BEGIN
SELECT value
INTO  static_value
FROM   v$parameter WHERE  name = '&1';
DBMS_OUTPUT.PUT_LINE('&1 is set to '|| RTRIM(static_value));
DBMS_OUTPUT.PUT_LINE(&2);
DBMS_OUTPUT.PUT_LINE(RTRIM(static_value));
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed to set parameter because of '|| SQLCODE);DBMS_OUTPUT.PUT_LINE('Error message - '|| SUBSTR(SQLERRM,1,128));
END;/

The result above return
static_value = 6442450944
and
&2 = 6g (which i specified i shell script that I call and pass the parameter to the sql query above.

How can I convert from bytes to gb in this case and compare static_value IS EQUAL TO &2?


texts: 502 error after restarting mongo db in remote server using fork command

texts:
I was trying to upgrade mongo 3.4.20 to 4.0.14 , but it lead to the system crash, so i tried reverting it back to 3.4.20. But after doing db restart using fork , there 502 error. Please help.I tried the following command
sudo mongod --fork --auth --logpath /var/log/mongodb/mongod.log


texts: MySQL: order tables by entity relations?

texts:
Sorry if the title is a bit ambiguous - I'm not asking about anything to do with SELECT.
This is the problem: I have a database with tables and a number of foreign keys defined between them. In many situations this can become a stumbling block - like when you want to delete data, or when you load data.
In the case of loading, this means you have to figure out the order in which to load the tables, because otherwise you get into trouble. So, it would be nice to have a tool that could look at all the foreign key relations and figure out which order would work - is there any such tool?


texts: SQL Server 2012 Visualization of Table relationships

texts: Does SQL Server 2012 /2014 have a way of creating a visualization of the table relationships of a database ?


texts: Are there any good database management applications for JDBC/Java databases?

texts:
I am working with an Apache Derby/JavaDB database via JDBC. Before have I been working with MySQL and used phpMyAdmin as a good Database Management tool.

Is there any good desktop application for managing databases over JDBC?


texts: Backup to URL not working when using Ola Hallengrens Database Backup

texts:
I am using Ola Hallengrens DB backup and am trying to get it working with the URL parameter.

I first set up a Shared Access Credential in SQL with the following sql

USE MASTER
CREATE CREDENTIAL [https://exampleblob.blob.core.windows.net/backups]
WITH IDENTITY='SHARED ACCESS SIGNATURE'
,SECRET = 'sv=2015-04-05&ss=bf&srt=sco&sp=rwd&se=2030-11-14T20:07:06Z&st=2016-11-13T12:07:06Z&spr=https&sig=KEY'
GO

Next I tried using the following in my log backup maintenance plan:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBATools -Q \
"EXECUTE [dbo].[DatabaseBackup] \
@Databases = 'USER_DATABASES', \
@BackupType = 'LOG', \
@Verify = 'Y', \
@Compress = 'Y', \
@CheckSum = 'Y', \
@LogToTable = 'Y', \
@ChangeBackupType='Y', \
@url='https://exampleblob.blob.core.windows.net/backups', \
@Credential='https://exampleblob.blob.core.windows.net/backups'" -b

However when I run the job I get the following error

  Use of WITH CREDENTIAL syntax is not valid for credentials containing
  a Shared Access Signature


texts: mysql column naming conventions for foreign keys

texts:
Lately I've been thinking about what's a better name for foreign keys, either user_id, or id_user style.

Personally I think id_user style is better for handling within database (db queries) where it's clear from the first three characters that the column is containing ID.

On the other hand I find user_id style more suitable e.g. with use of HTML autocomplete fields where there are two input fields; one name="user" for text, the other name="user_id" for his ID; which makes it easier to recognize that they belong to each other because they both start their name by defining what the content will be about.

What do you think?


texts: Unexpected results with random numbers and join types

texts:
I have a simple script that gets four random numbers (1 through 4) and then joins back to get the matching database_id number. When I run the script with a LEFT JOIN, I get four rows back every time (the expected result). However, when I run it with an INNER JOIN, I get a varying number of rows -- sometimes two, sometimes eight.

Logically, there shouldn't be any difference because I know rows with database_ids 1-4 exist in sys.databases. And because we're selecting from the random number table with four rows (as opposed to joining to it), there should never be any more than four rows returned.

This happens in both SQL Server 2012 and 2014. What is causing the INNER JOIN to return varying numbers of rows?

/* Works as expected -- always four rows */

SELECT rando.RandomNumber, d.database_id
FROM
  (SELECT 1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber
   FROM sys.databases WHERE database_id <= 4) AS rando
LEFT JOIN sys.databases d ON rando.RandomNumber = d.database_id;

/* Returns a varying number of rows */

SELECT rando.RandomNumber, d.database_id
FROM
  (SELECT 1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber
   FROM sys.databases WHERE database_id <= 4) AS rando
INNER JOIN sys.databases d ON rando.RandomNumber = d.database_id;

/* Also returns a varying number of rows */

WITH rando AS (
  SELECT 1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber
  FROM sys.databases WHERE database_id <= 4
)

SELECT r.RandomNumber, d.database_id
FROM rando AS r
INNER JOIN sys.databases d ON r.RandomNumber = d.database_id;


texts: Execute sp_update_jobstep to change SQL Agent job step type

texts:
As I was going to deploy a change to our Ola Hallengren indexing scripts, I noticed on some SQL Servers we have indexing job running as an "Operating System (CmdExec)" step instead of being called as a "Transact-SQL script (T-SQL)" step.

I'm using CMS to deploy this using sp_update_jobstep and I'm wanting everything as "Transact-SQL script (T-SQL)".

It doesn't appear that there's a way to change the step (unless I'm getting the terminology confused in this Microsoft article)

use msdb
go

exec sp_update_jobstep @job_name = N'IndexOptimize_USER_DATABASES',
                       @step_id = 1,
                       @command = N'EXECUTE [dbo].[IndexOptimize], @Databases = ''USER_DATABASES'', @LogToTable = ''Y'', @LockTimeOut = 300'

Is there a way to change the step type?


texts:
We are using MongoDB GridFS to create a document archive.  I am creating a sharded cluster at our Primary DataCenter.  I need a working backup at our offsite Disaster Recovery DataCenter.  This will be a very large database, ~6TB.  I will be adding 20k new documents daily.  I need to be able to take incremental backups at the end of our batch cycle and apply them to the offsite cluster.  We do not want to use replication because we want the clusters to be entirely independent, but essentially identical.  We will never delete from the collection.  We are running Red Hat.

Does anyone have any suggestions about how to keep the databases in sync?


texts: How do I use a stored procedure in SQL Server 2014 to call a external XML source?

texts:
I am attempting to retrieve currency exchange rates from http://www.floatrates.com/daily/usd.xml using a stored procedure in SQL Server 2014. The result is in XML. So I created the following stored procedure:

--DROP PROCEDURE [dbo].[proc_CallWebService]
CREATE PROCEDURE [dbo].[proc_CallWebService]
AS
    DECLARE @obj INT
    DECLARE @sURL VARCHAR(200)
    DECLARE @response varchar(max)

    SET @sURL = 'http://www.floatrates.com/daily/usd.xml'

    EXEC sys.sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
    EXEC sys.sp_OAMethod @obj, 'Open', NULL, 'GET', @sURL, FALSE
    EXEC sys.sp_OAMethod @obj, 'send'
    EXEC sys.sp_OAGetProperty @obj, 'responseText', @response OUT

    SELECT @response [response]
    EXEC sys.sp_OADestroy @obj
RETURN

and when I run this stored procedure, I get NULL in return instead of the XML.

What do I need to change in my stored procedure to get an XML response from that site mentioned above?


texts: What is the best practice for free space for a SQL server database drive?

texts:
I'm trying to determine the best practice for sizing a single database on a 120Gb data drive that will contain the LDF and MDF files.

I'm assuming that I don't want to set the size to be the maximum size of the drive, allowing room for moving and reorganising the data. I don't want to let it expand automatically either.

I'd like to set the size to something sensible and clear out the data regularly.

Can anyone provide any guidance?

EDIT:

some additional data...

The database is set to "Simple mode" and the transaction logs will be on the same drive as the data files.


texts: Maatkit shows the MySQL replication error in one table, but won't fix it

texts:
We use MySQL 5.1 with a primary/secondary replication setup. I use mk-table-checksum from Maatkit to generate checksums on the master and perform consistency checks on the replica.

mk-table-checksum is used to generate the checksums, and is pretty simple:

% mk-table-checksum localhost \
        --quiet \
        --replicate=test.checksum --empty-replicate-table --create-replicate-table \
        --chunk-size=500000 --sleep-coef=0.5 \
        --ignore-tables=mysql.general_log_backup,mysql.general_log,mysql.slow_log,mysql.help_topic,mysql.tables_priv

These checksums are then replicated to the Replica (slave) server, where we compare the checksums.

Around a month ago we had a server failure. We fixed the server, started up the database and resumed replication. Everything seems to be working fine-- Slave_IO_Running and Slave_SQL_Running are both set to "Yes", everything appears to be getting replicated successfully except for the error mentioned below, etc.

There is one problem, however. When I check the table using the following recommended method on the Replica, I see a consistency problem.

# mysql --execute "SELECT db, tbl, chunk, this_cnt-master_cnt AS cnt_diff, \
>         this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc) AS crc_diff \
>         FROM test.checksum \
>         WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc);"
+--------+------------------+-------+----------+----------+
| db     | tbl              | chunk | cnt_diff | crc_diff |
+--------+------------------+-------+----------+----------+
| plants | trees_properties |    40 |        0 |        1 |
+--------+------------------+-------+----------+----------+

This problem still occurs even after force another sync on the master using mk-table-sync on the master. I will probably need to replace the corrupt data by hand, but I am unclear what the actual problem is. What is a 'chunk'?

My guess is that only a handful of rows are different, and I can probably replace those rows by hand. But how can I list which rows are missing?

I realize that Maatkit has been deprecated in favor of Percona Tools. I plan to upgrade some day. But for now, "if it ain't broke, don't fix it."


texts:
I have a cluster set up with two virtual instance names

  WIN2K8SQL1\INST1

  WIN2K8SQL2\INST2

that was set up by the IaaS company.
Now developers are complaining about the naming convention so we decided to create an alias in the DNS. But alias only works for WIN2K8SQL1 and not for INST1, so that they need to use that in the connection strings:

  MEANINGFULNAME\INST1

Is there a way to name the instances on the cluster MSSQLSERVER?


texts: Is it possible for me to be notified if an 'after insert or update trigger' stops working?

texts:
Last week, I found my after insert or update trigger wasn't working. After I disabled and enabled it, it started working again.

I do not yet know why it stopped working. Is there any way to deal with this?  Because this trigger is recording the value of daily jobs, and is used for report purposes. If this trigger goes dead in few days without my notice or error, I will be in hot water.

I am using Oracle 10g, access the db by using sqldeveloper

My Trigger

create or replace
TRIGGER MASTER.INSTANCE_STEP_TRG
AFTER INSERT OR UPDATE OF SYSID,STEP_ID,INSTANCE_ID,PARENT_STEP_ID ON MASTER.WF_INSTANCE_STEP
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
WHEN (new.sysid > 0)
declare
stepSysid number;
crCode varchar(50);
crDate date;
step_id number;
BEGIN
  step_id := :new.step_id;

  select ss.sysid into stepSysid from TEMPLATE.wf_step ws
  inner join TEMPLATE.step_stage ss on ss.sysid=ws.stage_id
  where ws.sysid= step_id;

  if ( stepSysid>0) then
    insert into MASTER.fact_cr_progress values(0,:new.instance_id,stepSysid,:new.create_dt);
  end if;

  dbms_output.enable(10000);
  dbms_output.put_line('start print');

END;


texts: Azure PostgreSQL pg_dump impact on backup "chain"

texts: I'm a SQL Serve DBA new to PostgreSQL.  Does pg_dump have an impact on the backup chain, i.e., in SQL Server if I needed a copy of a database I would peform a COPYONLY backup so as not to break the lsn sequence.  Does pg_dump impact the native Azure backups being performed in the background?


texts: ORA-12518: TNS:listener could not hand off client connection ORACLE XE 11.2 x64

texts:
I'm starting to become very very frustrated by Oracle.
So, I have Oracle XE 11.2 64 bit and ODAC121012_x64 installed.

I can start listener; I can do tnsping xe; but when I try to connect as via sqlplus as system I'm getting:

ERROR:
ORA-12518: TNS:listener could not hand off client connection

Here are my config files:

tnsnames.ora:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
                           (SID_NAME = XE)
       (ORACLE_HOME =  C:\oraclexe\app\oracle\product\11.2.0\server)
     )

    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

sqlnet.ora:

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Help is very much needed and will be even more appreciated!


texts:
id value
1   50
2   60
3   55

select max(value) from tablename;

Generally we know, we will get 60, but I need the next value 55.

How do I get the value 55 using SQL?


texts: How can I use SQL Profiler to capture a single stored procedure

texts:
Using SQL Server SQL Profiler, which configuration can be used to monitor a single stored procedure? I'd like to capture all EXEC sprocName to include the parameter list. I'd like to capture this procedure so that I can load test it using realistic parameter data.

I've tried the following in a new SQL Profiler trace:

Events Selection > Column Filters > Text Data > LIKE: 'exec sprocName%'
Events Selection > Column Filters > Text Data > LIKE: 'exec sprocName'

Neither of above configurations capture my procedure. I've also tried to remove the procedure name with the following configs:

Events Selection > Column Filters > Text Data > LIKE: 'exec%'
Events Selection > Column Filters > Text Data > LIKE: 'exec'

Above configs do not capture any procedures.

Finally, I tried to execute the same profile without any column filters and I can confirm that it captures all SQL queries sent to SQL Server.

I've considered a workaround of capturing ALL stored procedure execs and post-filtering to my sproc of interest, using SQL queries or Excel Power Query.  However, the high frequency of sproc executions in the environment I want to model, make this unfeasible.


texts: "ORA-01017: invalid username/password" while connecting to an instance remotely as sysdba

texts:
I'm new to the oracle world; so please pardon my ignorance.

I'm trying to connect to a remote instance as sysdba.
However, I encounter the error: "ORA-01017: invalid username/password; logon denied"

$ sqlplus "sys/xxxx@<IP>:1521/sid1 as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 29 04:07:34 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

When I try to connect to another instance on the same server, I can get through:

$ sqlplus "sys/xxxx@<IP>:1521/sid2 as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 29 04:07:23 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Can someone point me to what I may have missed here?

Thanks!


texts: How wrong is my web app modeling schema?

texts:
So, I'm about to write a quite simple web application, I am currently working actively on the database schema, and I need help to avoid major drawbacks. I've been programming (php) for a few years now, but never had any theoretical education, so I'm totally ignorant about database best practices, and would really like to learn more.

The problem is as follows: the application is going to be a very basic CMS that allows to store blog posts, events, photo-galleries, and other various items. Now all those items share the same attributes (more or less, but let's say they do) which are user_id, title, date, content. Furthermore, they all can be "commented", "favorited", "shared", etc. And finally, they can be linked to each other (a blog post can refer to an event, which has pictures, etc.). Because of that, I am tempted to just create one table called "items" and have a "type" field to differentiate the items. The consequence would be to have only one "join" table for each action: "users_comments", "users_favorites", etc. and a join table for item connections between each other. Ultimately, there could be more item types in the future...

I kind of feel like this is a bad lazy way to go, mostly because of performance, but I really would like to have your opinion on this. Does the lock effect will render the site totally useless as multiple people will try to read and write those "items"? For what it's worth, I'm using MySQL with MyIsam, and CakePHP.

Beyond this question is the fact that I'm not used to large scale applications and always wonder about the performance. I just read about 3NF and am going to keep on learning those things, so any reading hints would be greatly appreciated.


texts: 3NF decomposition + database normalization

texts:
I am studying for a test and would like to know if I am on the right track to get the following into 3NF with minimal number of keys for R:

Given relation R(A,B,C,D,E,F,G) and the functional dependencies
f = {A->DE, E->BF, AB->FG, G->D, F->D}

how can I derive the minimal key(s) of R ->
I thought of this f+={A→BDEFG, E→BF, G→D, F→D} but it is wrong I don;t know how
Is R in 3NF? If not decompose it into 3NF.