SQL Server Partition - File Placement According to Microsoft best practices you can optimize SQL and take a lot of benefits by using this technique, my only question is how to correctly install the SQL Server to match the drives/paths according to their article:

https://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx

I got my system partitioned matching the article above:

C: SYSTEM
D: TEMP DATA
E: TEMP LOGS
F: SERVICES DATA
G: SERVICES LOGS
H: CONTENT DATA
I: CONTENT LOGS
J: BACKUP DATA

During the setup wizard I'm prompted for the directories (see below) but I can't figure the correct matching for all my drives/paths, I'm not sure at this point if my assumptions are remotely correct, even more, where do "Services Data" and "Services Log" will go? The options below aren't enough to match all my drives, I'm pretty sure I'm missing something and I'm lost here :)

My questions are:

How to match the drivers/directories to meet the aligned partition structure?
Is it possible to configure the Config.ini to automate this scenario?

I think I nailed this thing! Took me a lot of work but I think I could put this together and here it is what I did on my system:

C: SYSTEM = where SQL is installed
D: TEMP DATA = Temp DB directory
E: TEMP LOGS = Temp DB for logs
F: SERVICES DATA = Database Engine root INSTALLSQLDATADIR=F:\ServicesData
G: SERVICES LOGS = MSSQLServer Service startup to -G:\ServiceLogs\Logs\ERRORLOG
H: CONTENT DATA = User database  (*.mdf)
I: CONTENT LOGS = User database logs (*.ldf)
J: BACKUP DATA = The SQL backups


MySQL: Grant Read-Only DB Access for one DB and Create/Drop Access for other DBs Within MySQL, is it possible to provide a user read-only access to an existing database, but allow the user to create or drop any new databases?

I know it is possible to grant privileges via matching the database name (e.g. by prefix or suffix), but what would be the proper syntax for the first statement below:

GRANT ALL PRIVILEGES ON 'NOT(readonly_db_name)' ...;
GRANT SELECT ON 'readonly_db_name' ...;

No.  Instead use the following as a general way to provide limited actions.

Here's how to "grant" things to the user indirectly.  Create a Stored Procedure with "security definer" and be "root" when you define it.  In the SP, first check that the user is not trying to touch mysql, information_schema, or the readonly db.  Then proceed to do the CREATE or DROP.

This can obviously be extended to a variety of actions.  You should probably have one SP per action -- one for CreateDb('dbname'), one for DropDb('dbname'), and whatever else you might need.

Then train the users to use

CALL CreateDb('...');

instead of

CREATE DATABASE ...;

Note: you might need extra parameters for what should be added to the creation, such as CHARACTER SET utf8mb4.  Or you could assert more control by not giving the user a choice.


How do I synchronize parallel imports with COPY? I have ~20 tables that get built at the same time but using different psql processes (via bash script) at the first stage with something like:

create table foo_1 as (select ....); -- via psql process 1
create table foo_2 as (select ....); -- via psql process 2
create table foo_3 as (select ....); -- via psql process 3
...
create table foo_20 as (select ....); -- via psql process 20

Once they are built a new stage should start. Is there any good strategy to coordinate this multiple build? I mean, sync the two stages by discovering that all tables are built first from stage 1.

Parallel DB operations

  Is there any good strategy to coordinate this multiple build?

Well, the right way to run a task that requires multiple connections to the database is to use fork-exec (spawn on Windows). Then you wait in the parent for the children to return. For a good review check out this video on YouTube. It brings up fork-exec about 27 min in. That whole video is probably watching if you're new to Unix.

It's unlikely though that your loads are substantially faster in parallel, because it's not likely that the CPU is the bottleneck.

Loading from Files

If it really does matter though, and you're loading from file rather than query check out pg_bulkload rather than doing this yourself.

Materialized Views

In your case you're batching up CREATE TABLE AS SELECT (CTAS). A CTAS is useful for test data. If what you're doing is not test data, and you're dropping these tables and recreating them or the like then what I highly suggest you check out is MATERIALIZED VIEWS which can even be REFRESH MATERIALIZED VIEW CONCURRENTLY,

  REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view. The old contents are discarded. [...]

  CONCURRENTLY Refresh the materialized view without locking out concurrent selects on the materialized view. Without this option a refresh which affects a lot of rows will tend to use fewer resources and complete more quickly, but could block other connections which are trying to read from the materialized view. This option may be faster in cases where a small number of rows are affected.


Debugging table permissions for a windows user We have a SQL Server 2008 Database.

I created a few Windows users, added them to a specific database, and granted them read write access with the scripts below:

CREATE LOGIN [PRUDENTIAL\X208476] FROM WINDOWS
    WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];

Use client
GO
CREATE USER [PRUDENTIAL\X208476] WITH DEFAULT_SCHEMA=dbo
GO
exec sp_addrolemember db_datareader, [PRUDENTIAL\X208476]
GO
exec sp_addrolemember db_datawriter, [PRUDENTIAL\X208476]
GO

They run a basic insert command as follows and receive the error shown below:

INSERT INTO [client].[dbo].[admin_user_role]
           ([user_id]
           ,[role_id]
           ,[last_acty_oper_id]
           ,[last_acty_dtm])
     VALUES
           ('X208480'
           ,'ADMIN'
           ,'x163798'
           ,'2017-04-06 11:12:19.597')

Msg 229, Level 14, State 5, Line 1
The insert permission was denied on object ‘admin_user_role’, database ‘client’, schema ‘dbo’.

I tested this with another colleague of mine and he has no issue inserting into the table.

What other check/debugging can I do to resolve this issue?

Please run this code to find out if you have a deny on that user under the column state_desc.

SELECT permission_name,
   state_desc,
   type_desc,
   U.NAME,
   Object_name(major_id)
FROM   sys.database_permissions P
   JOIN sys.tables T
     ON P.major_id = T.object_id
   JOIN sysusers U
     ON U.uid = P.grantee_principal_id


Backup & recovery strategies for Partitioned Database in Sybase ASE I need to know about backup & recovery strategies for partitioned Database in Sybase ASE 15.7 . I am not worried about transaction failure & recovery scenarios.

We have already faced this 2 scenarios previously in un-partitioned database & it took more than 6 hrs for us to recover back [Reason : Low level sybase error]

In case of catastrophic failure like:

Data corruption (Some low level defect causes a table corruption that went unnoticed for days or weeks)
Failure of a device [A storage system gets corrupted]

Is my idea good to partition my database to achieve better resiliency?
In case I am doing so, is it possible to recover only failed partition? Sybase ASE maintains logical partitions, so is it possible to do piecemeal recovery?
In case my primary partition gets corrupted because of sybase low level error / disk corruption, will my replica also gets corrupted? [After all it's a replication :)]

In response to the initial answer by Catarrunas:

I didn't get point 2. Could you help me to explain that?

So in Sybase ASE, storage structure looks like (Partition --> Segment --> Device --> Extent --> Allocation Unit --> Page). Idea is to store 1 partition per segment. Of course again segment can span multiple devices.

Is it possible now to recover only failed partition held by 1 segment? Can we perform segment level recovery or still we would require to recover whole db?
So currently we don't have any replication. But it was more like due to some low level error, data got corrupted which went unnoticed for few days. In fact the last 2 dumps were also corrupted & we had to go -3th level dump to recover back.

Yes is a good idea, if the DB is spread across different devices. ( that ideally would be on different volumes ). The percentage of the volume of corruption would be less. However to restore you still have to restore the whole DB, which in separate devices and volumes can be faster. ( I/O is spread, less concurrency )
No. You have to recover the Db(s) that the corrupted device holds. So if you have a DB that has 1 corrupt device and you have those logical connections (good practice), you just need to change on OS, the connection to the new physical device. Then restore DB.
How is you replication ? Repserver or disk mirroring? For repserver, i do not think so, your source ASE would stop functioning, and you would have to stop replication to restore the db. Disk mirroring I don't know.

Response to updated question:

My point 2 was:

You will need to recover the whole db, unless you know exactly which objects where present on that device (and segments of it) that got corrupted, if yes then you can rebuild them manually( tables, procs, views... ). If you don't know or the labour to do it manually is too great, complete db recover.

What I mentioned about the logical connections was this, for example:

You have DB called TEST1 on the the following devices:

data01
data04
log02
data03

When you create the device with disk init, you have to give the path. But the path is not directed to the device itself, it goes to a soft link. Your data01 for example:

disk init.... physname='..sybase/data01.dat" ...

On the OS that sybase/data01.dat will be pointing to /dev/data01.dat. This way if you need on the OS to replace a corrupted device, you won't have to rebuild the database, you just create a new raw file, point data01.dat to it, and on DB restore data (LOAD). Faster process, than drop old DB, create new, load.

Well I'm pretty sure your errorlog would have some information about that corrupted device and also about your dumps had issues.
If your dumps were successfully dumped you should be able to use them on your restore (good devices). So I don't really get why you had to go back so many days. The thing is for instance if you have a corrupted device, it will manifest whenever some action tries to use it, (I/O). So your dumps would have failed i believe.


MySQL max_connections setting - per client or global? So I just need to clear this up.

For a test, I set max_connections=10 in my.cnf.

I then ran 10 scripts concurrently that each take over 20 seconds to finish.

I monitored my threads and noticed that when I hit 10 the Too many connections error fired.

That's fine, but I'm only one person. If there were multiple clients connected would they have 10 connected threads each, or between them?

Thanks

The option max_connections is a global setting.

You are limited to a total of 10 connections, no matter how many users are authenticating.

If you want to limit a specific user, such as myuser@localhost, to 10 connections, you would specify that with the GRANT command

GRANT USAGE ON *.* TO `myuser@localhost` MAX_USER_CONNECTIONS 10;

or hack it in like this

UPDATE mysql.user SET max_user_connections = 10
WHERE user='myuser' AND host='localhost';
FLUSH PRIVILEGES;

If you want 200 connections total, just set max_connections to 200.

You don't even have to restart mysqld.

Just do the following

STEP 01 : Add max_connections=200 to my.cnf under [mysqld] group header

STEP 02 : Login as root@localhost and run SET GLOBAL max_connections=200;


SQL Server Master Data Services database history by snapshots I'm involved in a project where there is SQL Server 2012 Master Data Services used. There's a requirement to save a version of the database contents in certain point of time, say, every month or a quarter or so and possibly replacing previous versions on a yearly rhythm. That is, if a monthly history of database versions is used, there will be at most twelve different snapshots at any given time.

I'm curious and hence two questions:

Is this a preferred way of doing this?
Would there be a better way?

For what I've seen, from a developer perspective, snapshots are utilised for example for reporting purposes, but dropped soon after they are done. It seems they impact performance and perhaps make backups and restores more difficult.

<edit:

As an answer to the database size question: some hundred megabytes. But that's a very rough guess. Certainly at least on hundred.

I've now learned the snapshots are in fact a plan to store MDS history and possibly retained indefinitely -- or  until someone explicitly deletes the oldest ones. I don't know the frequency they are taken. At least four times a year, possibly more often and seemingly retained until someone decides to delete the oldest ones. The rationale is to store MDS history as "untampered" read-only snapshots, of which at least a few previous quarterly snapshots are actively queried (but not altered). There is a plan to publish the MDS periodically -- every ten minutes or so -- the MDS to a special purpose "publish database", which may be the place the snapshots are taken (I'm not sure, but certainly there ought to be awfully lot of snapshots then to cover all the published changes).

This doesn't feel the right way to work with MDS. This looks like the slowly changing dimension problem and it looks like the (great) majority of data does not change. What I know, or believe is that there are nightly bulk loads, but mostly the data loaded should be the same as it has been, and changes are mostly new items plus some small modifications and some additional attributes maintained purely by the data stewards. Then looking Technet regarding Database snapshots it looks me it's difficult to run the system efficiently (e.g. I/O) and reliably (backups and restores) in presence of multiple snapshots, which do carry an operational and contractual purpose in this case.

Maybe I shouldn't fret or this. This doesn't even look like I have any real questions here anymore than

Would it be normal to version database data in snapshots? Be it an operational database or a MDS one.

Snapshots do have some performance overhead. With a dozen snapshots you could have up to 12 additional pages written to disk for each page written to the main DB, depending on your write patterns.

Consider what you want to do with these snapshots.  If they're simply for putative future auditing then do a backup and get them off your production server.  You can restore them when they're needed.  Ad hoc analysis?  best not done on the production server (and engaging the live DB) either.  Disaster recovery?  DB snapshots do not give you that.  To revert to in the event of a mistake?  You'll be rolling back a lot of other work, too, which would have to be re-done.  Then-versus-now analysis.  Hmmm, maybe.

In short, a "proper" backup, restored as an independent database and only db_reader granted will be better in almost every way.


Need to understand output of sqlcmd.exe I was advised to make another question, from here - https://stackoverflow.com/questions/19674045/need-help-to-make-sqlcmd-run-as-per-requirements

My sqlcmd.exe is invoked with a command of the form -

sqlcmd -S .\MYSQLSERVER2008 -U MyUsername -P MyPassword
-i C:\Database\hugescript.sql > log.txt 2> error.txt

Now,

log.txt says - Canceling connection. Please wait...
Error.txt says - Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 :
Operation was canceled..

How do I find out the cause of the problem and fix it ?

Lets try and test connectivity to your server with sqlcmd

-- connect to instance with sqlcmd
sqlcmd -S MYSQLSERVER2008 -U MyUsername -P MyPassword

-- attempt to open master
USE MASTER;
GO

You should see the text : Changed database context to 'master'

Next type "quit" and then try this

Create a file called c:\test_sqlcmd.sql

Add the following code:

:setvar myserver "MYSQLSERVER2008"

:CONNECT $(myserver)
USE MASTER;
GO
SELECT @@SERVERNAME;
GO

Now execute that script sqlcmd -i c:\test_sqlcmd.sql

You should see

Changed database context to 'master'
and
MYSQLSERVER2008

Once we have that out of the way, we can continue our research


You can not write DDL in PL/SQL block directly as you have shown in your question.

However you can execute these DDLs using EXECUTE IMMEDIATE command as demonstrated below.

  CREATE OR REPLACE PROCEDURE ddl_test AS
  BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE tbl1';
  END;
  /


Combine 2 SQL Queries How do I combine these 2 queries?

select a, b, c from MY_TABLE where date < sysdate+1 AND c is NULL order by id

select a, b, c from MY_TABLE where date < sysdate AND b is NULL order by id

Define "combine".  My guess is that you simply want to OR the predicates together

SELECT a, b, c
  FROM my_table
 WHERE (dt_column < sysdate+1 and c IS NULL)
    OR (dt_column < sysdate and b IS NULL)
 ORDER BY id

You may, however, want to do something else.  Perhaps you want to UNION ALL the two result sets, for example, it order to get duplicate rows.


Can you change recovery mode from simple to full with users working on the server? Can you change recovery mode from simple to full with users working on the server?
While the server is changing the mode is the work of the users interrupted? If so, for how long.
Version: SQL server 2016
Thank you very much

You can - as long as there's no running transaction on that database, or you run the alter statement using WITH ROLLBACK IMMEDIATE. Be careful with this clause as it will rollback nonqualified transactions - see here for a human-readable explanation.
Also, remember to take a full backup after you change the recovery model from simple to full, or the change won't really take place (info here)!


How do I use currval() in PostgreSQL to get the last inserted id? I have a table:

CREATE TABLE names (id serial, name varchar(20))

I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I don't understand how to use it.

I have tried with:

SELECT CURRVAL() AS id FROM names_id_seq
SELECT CURRVAL('names_id_seq')
SELECT CURRVAL('names_id_seq'::regclass)

but none of them work. How can I use currval() to get the last inserted id?

If you create a column as serial PostgreSQL automatically creates a sequence for that.

The name of the sequence is autogenerated and is always tablename_columnname_seq, in your case the sequence will be names names_id_seq.

After inserting into the table, you can call currval() with that sequence name:

postgres=> CREATE TABLE names in schema_name (id serial, name varchar(20));
CREATE TABLE
postgres=> insert into names (name) values ('Arthur Dent');
INSERT 0 1
postgres=> select currval('names_id_seq');
 currval
---------
       1
(1 row)
postgres=>

Instead of hardcoding the sequence name, you can also use pg_get_serial_sequence() instead:

select currval(pg_get_serial_sequence('names', 'id'));

That way you don't need to rely on the naming strategy Postgres uses.

Or if you don't want to use the sequence name at all, use lastval()


Understanding MySQL's 'explain' I have following query with explain:

mysql> explain
     select dd.data from dane dd
       join test1.tag1 t1 on dd.id = t1.id
       join test1.tag2 t2 on dd.id = t2.id
       join test1.tag3 t3 on dd.id = t3.id
       join test1.tag4 t4 on dd.id = t4.id
       join test1.tag5 t5 on dd.id = t5.id
       join test1.tag6 t6 on dd.id = t6.id
       join test1.tag7 t7 on dd.id = t7.id
       join test1.tag8 t8 on dd.id = t8.id
       join test1.tag9 t9 on dd.id = t9.id
       join test1.tag10 t10 on dd.id = t10.id
     where t1.val = 1
       and t2.val = 2
       and t3.val = 3
       and t4.val = 4
       and t5.val = 5
       and t6.val = 6
       and t7.val = 7
       and t8.val = 8
       and t9.val = 9
       and t10.val = 10 ;
+--+-----------+-----+------+-------------+-------+-------+------------------+-----+------------------------+
|id|select_type|table|type  |possible_keys|key    |key_len|ref               |rows |Extra                   |
+--+-----------+-----+------+-------------+-------+-------+------------------+-----+------------------------+
| 1|SIMPLE     |t10  |index |PRIMARY      |PRIMARY|8      |NULL              |94577|Using where; Using index|
| 1|SIMPLE     |dd   |eq_ref|PRIMARY      |PRIMARY|4      |test1.t10.id      |    1|                        |
| 1|SIMPLE     |t8   |eq_ref|PRIMARY      |PRIMARY|8      |test1.t10.id,const|    1|Using index             |
| 1|SIMPLE     |t1   |eq_ref|PRIMARY      |PRIMARY|8      |test1.t8.id,const |    1|Using where; Using index|
| 1|SIMPLE     |t2   |eq_ref|PRIMARY      |PRIMARY|8      |test1.t1.id,const |    1|Using where; Using index|
| 1|SIMPLE     |t6   |eq_ref|PRIMARY      |PRIMARY|8      |test1.dd.id,const |    1|Using where; Using index|
| 1|SIMPLE     |t4   |eq_ref|PRIMARY      |PRIMARY|8      |test1.t8.id,const |    1|Using where; Using index|
| 1|SIMPLE     |t3   |eq_ref|PRIMARY      |PRIMARY|8      |test1.dd.id,const |    1|Using where; Using index|
| 1|SIMPLE     |t7   |eq_ref|PRIMARY      |PRIMARY|8      |test1.dd.id,const |    1|Using where; Using index|
| 1|SIMPLE     |t9   |eq_ref|PRIMARY      |PRIMARY|8      |test1.t10.id,const|    1|Using index             |
| 1|SIMPLE     |t5   |eq_ref|PRIMARY      |PRIMARY|8      |test1.dd.id,const |    1|Using where; Using index|
+--+-----------+-----+------+-------------+-------+-------+------------------+-----+------------------------+
11 rows in set (0.08 sec)

Every tag1 - tag10 tables have the same build: two columns (id,val), id is the primary key, no other indexes.

I have questions about the first line of explain:

Number of rows - are this number exact number of scanned rows or just estimate? Every table has 100,000 rows.
The first row has type index, but Extra shows Using where; Using index. Does it mean, that the first full table scan on where is used, and then index? Where is on the val column which does not have any indexes.

type: index means it's an index scan. That is, it's scanning through an entire index of that table.

An index scan often goes along with Using index because the latter indicates that the query is able to use the index to satisfy the query, without touching the rows of the table. Using index would be more clearly labeled Using only index.

There are as many entries in an index as there are rows in the table, therefore the rows reported by an index-scan is the same number as rows reported during a table-scan.

The number reported by rows is an estimate, based on InnoDB's statistics collected about the table. It can be inaccurate by 10% or even more. Don't rely on this being a precise number, but treat it as an indicator in the order of magnitude as the true number of rows (or index entries).

You may have no secondary indexes on your tags table, but you have a primary key, which implicitly has an index. The type: eq_ref indicates that the query is using the primary key to find the respective row during the join. Lookups by primary key are good.

To read more about the EXPLAIN output, read: http://dev.mysql.com/doc/refman/5.6/en/explain-output.html


How do I add composite MySQL Indexes with denormalization and JOINS? I have the following tables:
CREATE TABLE base_event (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    created_by ... -- some columns
);

CREATE TABLE transaction_events (
    event_id BIGINT UNSIGNED NOT NULL,
    transaction_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    merchant_id BIGINT UNSIGNED NULL DEFAULT NULL,
    merchant_city VARCHAR (...) NULL DEFAULT NULL, -- Denormalize
    customer_id BIGINT UNSIGNED NULL DEFAULT NULL,
    customer_ip_address VARCHAR(...) NULL DEFAULT NULL, -- Denormalize
    ...
    FOREIGN KEY (event_id) REFERENCES base_event(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (merchant_id) REFERENCES merchants(id),
);

CREATE TABLE customers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    customer_ip_address VARCHAR(...) NULL DEFAULT NULL,
    ...
);

CREATE TABLE merchants (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ...
);

And my SELECT:
(SELECT t.*, c.name AS customer_name ...
FROM transaction_events t
JOIN customers c ON t.customer_id = c.id
JOIN merchants m ON t.merchant_id = m.id
WHERE t.customer_ip_address = 'abc' AND t.transaction_time > 'abc')
UNION DISTINCT
(SELECT t.*, c.name AS customer_name ...
FROM transaction_events t
JOIN customers c ON t.customer_id = c.id
JOIN merchants m ON t.merchant_id = m.id
WHERE t.merchant_city = 'abc' AND t.transaction_time > 'abc')

And my indexes are:
ALTER TABLE transaction_events
ADD INDEX index_1 (customer_ip_address, transaction_time),
ADD INDEX index_2 (merchant_city, transaction_time);

My query is in this form to avoid OR.
I've denormalized to a degree for the sake of the indexes.
I do not need to reference my base_event table for this query.
The relation with transaction_events to customers and merchants is not 1-to-1 but 1-to-0-or-1.

My questions:

I can get rid of the wildcard, but transaction_events has around 20 columns (Would it help creating any further indexes to speed up the query?
Do I need to put any other composite indexes (that potentially reference my FKs) to further improve this query?

Do you need all 20 columns from transaction_events? If not, then getting rid of * and specifying only the columns you need not only reduces the amount of data you're pulling back at one time but also reduces the chances of a sub-optimal query plan. It's possible the query plan generated will vary based on the columns in your SELECT clause.
You could test adding an index on the customer_id field and another index on the merchant_id field for your JOIN clauses and see if it improves performance and produces a better query plan. But this will require testing and comparison of the EXPLAIN for each case.


how to failover in log shipping? I am reading the book 'Pro SQL Server 2019 Administration' from Carter, where in the Log shipping section he mentions:

"Unlike other high availability and disaster recovery technologies, an
amount of administrative effort is associated with failing over log
shipping. To fail over log shipping, you must back up the tail end of
the transaction log, and copy it, along with any other uncopied backup
files, to the secondary server."

He previously specified that log shipping is mostly used in DR scenarios... My question is, if DR hit the primary data center, how is one supposed to "up the tail end of the transaction log, and copy it, along with any other uncopied backup files"?!
If DR hits, and we have set up log shipping, my guess is that we are only left with whatever the secondary replicas have... That is, if I am able to 'backup the tail' then the server must be up and running...

I suspect he's talking about doing the failover manually.
If, as you say, your Data Centre gets totalled, then you're not going to be able to get anything back from your old, Primary server.  It's toast.
That's probably why he leads with:

Unlike other high availability and disaster recovery technologies ...

These other technologies ship logs around much faster so that there's no "trailing edge" to worry about.


SSMS How to find the Connection name How do you return the connection name via TSQL? This is visible in SSMS via the properties window. For example if you have a DNS alias "ProductionServer" for a machine named "SRVR0001" and further have a local server registration for this named "Live Production" and then

1) Open a new query providing a Server name of "ProductionServer" then the connection name will be "ProductionServer"
2) Right click the local registration and select new query, the connection name will be "Live Production"

And of course in both instances select @@SERVERNAME will return SRVR0001.

This is similar, but different, to these questions:
https://stackoverflow.com/questions/16088151/how-to-find-server-name-of-sql-server-management-studio
https://stackoverflow.com/questions/129861/how-can-i-query-the-name-of-the-current-sql-server-database-instance

I anticipate the answer may well be that this is a local property held by SSMS, and thus can not be returned by TSQL run on SQL Server.

The comment left by user sepupic is correct:

  And indeed there is no interest to server to know how did you name your connection or local registration, so only your client tool may know this

The only places I might have expected to see this would be the "connection" related DMVs, for instance if SQL Server kept track of the connection name used to connect to it (similar to how it keeps track of the client host name, set options used, etc).

However, it's not present in any of the main ones I checked:

sys.dm_exec_connections
sys.dm_exec_requests
sys.dm_exec_sessions

And this makes sense, especially with registrations where the client has to translate those names before creating the connection anyway.


How should I be backing up Postgres for this scenario? So the way I have been backing up Postgres is with:

pg_dumpall -U eox-dev -h localhost --clean --file=/home/backups/postgres.sql

I want to make sure I have a production ready backup that has functions, relationships, triggers, in addition to tables and data.

I'm trying to restore it now in a Kubernetes cluster into a database with a different name prod_db -> staging_db, but get FATAL: database "prod_db" does not exist. I'm trying to restore it to staging_db so that makes sense. One of the first lines in the .sql does declare CREATE DATABASE prod_db. I'm not about the edit the .sql. If I have to do that, I'm doing something wrong.

What is the correct way of making this backup?

If you want to restore individual databases, you should probably be using pg_dump, not pg_dumpall.  And if you don't intend to restore over the top of an existing populated database, you shouldn't be using --clean.

Backing up your entire cluster and migrating/cloning one database that is in it are different tasks, you probably want different commands to do them.

But you have the dilemma that if you specify --create to pg_dump it will want to restore into a database of the same name as it dumped from, and if you don't specify --create then it is up to you to create a new database with the name you want, but also with the correct ENCODING and LOCALE.


Move data from 4 shard, replicated mongodb cluster to a single server without downtime I need to move sharded data from four replica sets to a single remote machine without downtime. Can you describe the process step by step?

In your scenario there is a good discussion on this already on the Mongo Jira server on unsharding a database:
https://jira.mongodb.org/browse/SERVER-9845

Test and stage these steps using your real data in a non prod environment repeatedly until you are confident and find one that works:

database = 'test'
collection = database + '.fs.chunks'

sh.stopBalancer()
use config
primary = db.databases.findOne({_id: database}).primary

// move all chunks to primary
db.chunks.find({ns: collection, shard: {$ne: primary}}).forEach(function(chunk){
    print('moving chunk from', chunk.shard, 'to', primary, '::', tojson(chunk.min), '-->', tojson(chunk.max));
    sh.moveChunk(collection, chunk.min, primary);
});

// unshard
db.collections.remove({ "_id" : "test.fs.chunks" })
db.chunks.remove({ ns : collection })

// flush all mongos, is this neccessary?!
use admin
db.runCommand({ flushRouterConfig: 1 })

Note now you have unsharded your data.  If you don't want to just directly unshard it before sending it over to the new cluster, then setup the new cluster sharded, and after it's synched then unshard it.


Get the highest parent id recursively in MySQL I want to find the uppermost parent ID for each entry. For example,
CREATE TABLE t1
(
ID int(11) unsigned NOT NULL,
ParentID int(11) unsigned,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

INSERT INTO t1 (ID,ParentID) VALUES (1,NULL),(2,1),(3,2),(4,3);

I intent to get
ID     Highest ParentID
1      NULL
2      1
3      1
4      1

I planned to create a recursive query (MySQL 8 or MariaDB 10.5) by adding a condition to break the recursive when Parent ID is a specific value (e.g., NULL in the above case). I started with
WITH RECURSIVE cte (ID, ParentID) as (
  SELECT ID,ParentID FROM t1
  UNION ALL
  SELECT t2.ID,t2.ParentID FROM t1 t2
  INNER JOIN cte on t2.ParentID = cte.ID
)
SELECT * FROM cte;

but it does not work as I intend, as it gets the next ParentID instead of recursively.
Sample fiddle.

WITH RECURSIVE
cte as ( SELECT id, id nextid, parentid
         FROM t1
       UNION ALL
         SELECT cte.id, t1.id, t1.parentid
         FROM t1
         JOIN cte ON cte.parentid = t1.id )
SELECT Id, nextid RootId
FROM cte
WHERE parentid IS NULL

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=209df940143d3e984d418e49929bd847


Mysql Insert/Update is so Slow I have a laravel application which must insert/update thousands of records per second in a for loop. my problem is that my Database insert/update rate is 100-150 writes per second . I have increased the amount of RAM dedicated to my database but got no luck.

is there any way to increase the write rate for mysql to thousands of records per second ?
please provide me configurations.

My storage engine is InnoDB

Innodb storage is care about ACID transaction it will make sure your data(insert/update) commit to disk that why take the time:

Considering about the columns which frequently change(insert/update), you should not indexing on that column.
if you always need big data of DML operation try to avoid auto-commit=0.but it not good if server crush or some fallen.
turning your query if your DML operation bases on subquery:

Example:

UPDATE TEST SET NAME='XXXX' WHERE (subquery).


PostgreSQL function definition "syntax error at or near +" Suppose a simple query:

(SELECT MAX(timestamp) FROM events e WHERE e.id < some_id)
  + ((SELECT MIN(timestamp) FROM events e WHERE e.id > some_id)
    - (SELECT MAX(timestamp) FROM events e WHERE e.id < some_id)) / 2

If some_id is the parameter, this finds the approximate timestamp based on some sequence of events (it takes the previous and following event and averages their timestamps).

This query works great, now I want to package it as a function:

CREATE FUNCTION id_to_timestamp(integer) RETURNS timestamp with time zone AS
$BODY$
    (SELECT MAX(timestamp) FROM events e WHERE e.id < $1)
      + ((SELECT MIN(timestamp) FROM events e WHERE e.id > $1)
        - (SELECT MAX(timestamp) FROM events e WHERE e.id < $1)) / 2
$BODY$
LANGUAGE sql;

This suddenly fails with message

ERROR:  syntax error at or near "+"
... events e WHERE e.id < $1) + ((SELECT...

What am I doing wrong? Why is the + operator problematic?

(The actual identifiers are anonymized.)

A LANGUAGE sql function must be a complete SQL statement (or more than one).

Yours is just an expression.

Just prepend SELECT, like you would when running it stand-alone.

$BODY$
SELECT (SELECT ...

The reason it complains at the + is that, surprisingly, it's legal to parenthesise a top-level query. This, for example, is a valid query:

regress=> (SELECT 1);
 ?column?
----------
        1
(1 row)

(I had no idea about that until your question, either, so thanks. I'm not even sure it's intentional...)

Note that you'd have got the same error if you ran your original query as-written, so I assume you'd prepended SELECT to it.

regress=> (SELECT 1) + (SELECT 2);
ERROR:  syntax error at or near "+"
LINE 1: (SELECT 1) + (SELECT 2);


PostgreSQL: Table and index on different tablespace In PostgreSQL i have the freedom of choosing to put index (of a table or materialized view) on tablespace other than the one i chose for the table (or materialized view) itself.

Is there any performance impact of this?

The idea to separate tables and indexes in different tablespaces (which must be on different devices) as a performance tuning measure is an old myth that is slow to die.

It may be a good idea to spread I/O over several devices, but you should do that by using striping on the file system or hardware layer. Of course you can also do it by using different tablespaces, but it is unclear why specifically separating indexes and tables should be beneficial.

People who know other databases think similar.


Correct way to perform an UPDATE on multiple tables? This query is working, but I am only just beginning to understand relational tables. I have spent the last few yeas doing all of the processing with PHP after retrieving the data I needed (oftentimes using multiple queries). So, please hold my hand here, and tell me if this is being done the "right" way?:

Table Breakdowns

SettingsList

UserSettings

SettingTypes

Basically when I add a setting to the API (through the control panel) it gets put into SettingsList, I give the setting a value and a type (so that it actually does something) in the UserSettings table (this will be the only table accessed by the client from the app) . and the setting itself can only be a type specified in the enumeration (of sorts) in the SettingTypes table.

Right now, when I edit a setting from MY administrative control panel (the client will have far less control), I am running an update qiery that looks something like this:

UPDATE `SettingsList` l, `UserSettings` u
SET
l.settingName = 'pow',
l.settingDescription = 'otherTest',
u.setting = 0,
u.type = 1 WHERE
l.settingID = 3 AND u.settingID = 3

Does this look correct? Again, it does work but as most here would agree, just because it works, doesn't mean I did it right.

Well, I may have supplied more information than you need to give me an answer, but how am I doing in my first application with relational tables?

Usually updates to two tables are done as two different queries to avoid making a mistake and badly mangling both tables.

A combined update is a form of JOIN, so you need to be careful when establishing update conditions. I don't see how this query you've created is any better than two simple queries that achieve the same effect. If you need to make the operation atomic, frame it inside a transaction.


use variable to determine the name of a column in the select in postgre I'm trying create a update for a new structure of tables from a old table, but not using functions. I'm trying create a script to it.
The old table is like this:

-- old table (OldTable)
name| col_a | col_b | col_c |
--------|---------|---------|--------|
ABC  |     0    |NULL |    1    |
DEF  |     1    |    1     |    1   |
GHI   | NULL |    1    |     0   |

And, new tables:
-- Table Users

ID | NAME
----| ---------
 1  | ABC
 2  | DEF
 3  | GHI

-- Table Rules
ID | RULE_NAME
--- | ------------------
 1  | col_a
 2  | col_b
 3  | col_c
 4  | col_d

-- Table UserRules
ID_USER | ID_RULE
------------- |-------------

So, I'm needing populate the table UserRules with the result from a select on TableMix where the user name is equals to the user name on table User and the value for column  in TableMix is equals 1. (select  from OldTable where OldTable.name = Users.name).
Well, I'm trying this:

DO $$
    DECLARE rules CURSOR FOR SELECT column_name FROM information_schema.columns
        WHERE table_schema = 'public' AND table_name   = 'OldTable'
        AND data_type = 'numeric' AND column_name NOT IN ('foo','bar');
        -- "foo" and "bar" are another numerc cols, but not define rules.
    DECLARE users CURSOR FOR SELECT name FROM public.Users;
    BEGIN
        FOR ruleName IN rules LOOP
            FOR userName IN users LOOP
                EXECUTE format('SELECT COALESCE(%I,col,$1) FROM public.OldTable
                    WHERE name = ''$2''', ruleName, username);
                -- insert on... populate the table Rules after get the id of the rule in table Rules if the result of select in OldTable equals 1 (or true if has a "where ... = 1")
            END LOOP;
        END LOOP;
END $$

After this, I don't have progress...
Someone help me? Thans!

UPDATE

This way I can get the real value:

DO $$
DECLARE
    rules CURSOR FOR SELECT column_name FROM information_schema.columns
    WHERE table_schema = 'public' AND table_name   = 'oldtable' AND data_type = 'numeric'
    AND column_name NOT IN ('foo','bar');

    names CURSOR FOR SELECT username FROM public.users;

    res integer;
BEGIN
    FOR rulename IN rules LOOP
        EXECUTE format('SELECT %s FROM oldtable WHERE nome = %L', rulename, 'USER NAME')    INTO res;
    -- IF res > 0 THEN
       RAISE NOTICE '%', res;
    --END IF;
    END LOOP;
END $$

Result the real integer value.

But if I use a loop for usernames, it's doesn't work.

DO $$
DECLARE
    rules CURSOR FOR SELECT column_name FROM information_schema.columns
    WHERE table_schema = 'public' AND table_name   = 'oldtable' AND data_type = 'numeric'
    AND column_name NOT IN ('foo','bar');

    names CURSOR FOR SELECT username FROM public.users;

    res integer;
BEGIN
    FOR name IN names LOOP
        FOR rulename IN rules LOOP
            EXECUTE format('SELECT %s FROM oldtable WHERE nome = %L', rulename, name)   INTO res;
        -- IF res > 0 THEN
           RAISE NOTICE '%', res;
        --END IF;
        END LOOP;
    END LOOP;
END $$

So, the resul is always .
Where am I going wrong?

I think you might be looking for the USING clause:

EXECUTE format('SELECT COALESCE(%I,col,$1) FROM public.TableMix
                WHERE name = $2', ruleName)
        USING (username, whateverTheSecondParameterIs);

You use $1, $2, etc for placement-parameters, passed by USING. You use %I for itentifiers expanded by format.

Update: If you want to coalesce the column name ruleName if it's null, perhaps you want something more like:

EXECUTE format('SELECT %I FROM public.TableMix
                WHERE name = $1', coalesce(ruleName, 'col'))
        USING (username);

??

You need to think about order of evaluation. What's part of the generated SQL, and what's evaluated in order to create that SQL?


PostGIS trigger is fired without error, but the action seems not executed Context
I have set up a trigger on a PostgreSQL (+Postgis) table containing observation points.
This trigger must compute a new geometry for each inserted or updated point. This new geometry must be calculated as the projection of the geometry of the currently inserted feature point onto the nearest line of another table containing line features.
What I've done
The trigger is as follow, it is correctly fired as the EXPLAIN ANALYZE shows after a new insertion:

no errors are thrown when I insert a new point, but the projected geometry column proj_geom stays empty:
CREATE OR REPLACE FUNCTION project_funct()
  RETURNS trigger AS
  $$
  BEGIN
    -- Here I wish I could update several fields (line_id, dist and proj_geom) at the same time
    -- but for the moment I only put the focus on the new geometry proj_geom:
    NEW.projected_geom := (
      SELECT sub.proj_geom
      FROM
        ( SELECT
             points.id AS point_id,
             schema.lines.id AS line_id,
             ST_Distance(schema.lines.geom, NEW.geom) AS dist,
             ST_ClosestPoint(schema.lines.geom, NEW.geom) AS proj_geom
          FROM schema.line, points
            -- Something is weird to me there, I should probably not have this WHERE statement here as the
            -- trigger should be used on a single row at a time, hence with a single ID for each execution:
            WHERE points.id = NEW.id
            ORDER BY dist
            LIMIT 1
        ) AS sub
    );
      RETURN NEW;
  END
  $$
LANGUAGE 'plpgsql';
CREATE TRIGGER proj_trigger
    -- Here, I don't really know if I must used BEFORE or AFTER, AFTER sounds better to me
    -- but I may be totally wrong. I also wish I could keep the same code for both UPDATE and
    -- INSERT but this may not be a good idea?:
    BEFORE INSERT OR UPDATE ON points
    FOR EACH ROW
    EXECUTE PROCEDURE project_funct();

As I discovered triggers yesterday evening, and even if I've done my best, my code is probably full of mistakes.
I also noticed some SQL scripts that I already have and which are actually working, ... doesn't work anymore in this fancy(?) way of writing triggers (e.g. I don't know the meaning of the double dollar sign $$).
Notice:
If I take out the inner most SELECT statement, and replace the word NEW by the actual name of the points table, the query, when run directly in pgAdmin with a specified ID such as WHERE points.id = 41 returns the correct results:

In the trigger, my first comment relates to the fact I wish to be able to update at the same time the line_id, dist and proj_geom fields of the table points each time an observation is inserted.
Question
How could I fix my code, at least to be able to get the new geometry?
I'm pretty sure I'm missing some tiny details (probably where I left some comments in the code where I have that actual feeling) but I can't figure them out and I've done too much hit'n'try now with the trigger function (none of them worked as good as the one here above).
Environment
Ubuntu 18.04
PostgreSQL: 10.12
PostGIS: 2.4

In a BEFORE INSERT trigger, the new row has not yet been inserted into the table. So when the query searches for points.id with the new value, it will not find it.

And there is no need to read the points table because you want to access only the new row, and all those values are available in NEW:

      SELECT
         NEW.id AS point_id,
         schema.lines.id AS line_id,
         schema.lines.geom <-> NEW.geom AS dist,
         ST_ClosestPoint(schema.lines.geom, NEW.geom) AS proj_geom
      FROM schema.line
      ORDER BY dist
      LIMIT 1

You need to use a BEFORE trigger to be able to modify the row before it is written into the table.

For a nearest-neighbour search, better use the <-> operator, which is able to use a geometric index on lines.geom.


Create a read only excel file from SSIS? Is there a way to create a read only excel file from SSIS?

That's a filesystem concern, not an SSIS one. Any file that SSIS creates will be owned by the user SSIS runs as. The best solution would be to set the ACL on the target folder to be what you want; when SSIS creates the file it will get those permissions by default.

If you can't do that for some operational reason, you can manipulate the file after creation with a command task. You can use SetACL or any command line tools for this.


Transposing a row data in multiple rows based on multiple columns I have data in the database in the below format

And I want output data in this format

BudgetCategoryId | CostCenterDepartmentId | CostCenter | AccountNo | Amount    | Timestamp
714              | 135                    |  12010194  |       110 | 102000.00 | 01-01-2020

Basically timestamp in the desired result is '01-' + <'MONTH'> + <'YEAR'>

I tried transposing, pivot, unpivot but unfortunately unable to get the desired result.

Column Names:

Id | BudgetCategoryId | CostCenterDepartmentId | Year | AccountNo | CostCenter | Month1 | Month2 | Month3 | Month4 | Month5 | Month6 | Month7 | Month8 | Month9 | Month10 | Month11 | Month12 | Budget

PS: I am very new in SQL (RDBMS)

You probably need to do it in several steps: first unpivot, then calculate the timestamp.

Unpivoting would be

SELECT BudgetCategoryId, CostCenterDepartmentId, CostCenter, AccountNo, Year, Amount, Month
  FROM (
    SELECT BudgetCategoryId, CostCenterDepartmentId, CostCenter, AccountNo, Year,
      Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10, Month11, Month12
      FROM table) p
  UNPIVOT (
    Amount FOR Month IN (Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10, Month11, Month12)
)

The timestamp can be calculated as DATEFROMPARTS(Year, CAST(SUBSTRING(Month, 6, LEN(Month) - 5)), 1) and it might work to add it as an extra field in the above SELECT, or you might need to do an extra SELECT on the above result.


Migrating Postgres server from Windows to Linux Simple question: I want to migrate all of a Postgres server's data, schema, roles, everything from Windows to Linux.

Can I just copy the contents of the %PGDATA% directory in Windows to which the server points to Linux and start the server there pointing PGDATA argument/variable to that directory? In other words, are the data files platform independent to make porting from one OS to another a matter of copying?

I also have developed a script for cloning PG databases, schema+data but, for a variety of reasons, I would prefer to just put the data files on a USB drive and be done with it.

No, you cannot do that. The files in the data directory are not compatible across operating systems (or different architectures).

You will need to use pg_dump and pg_restore


Query that compares dates I'm trying to create a query that shows if students are mature or not, based on their date of birth. However when the query is executed it returns all students as not mature. Any students that are born before 1/10/1971 should be classed as mature.

Shown below is the IIf() statement I have used to show if students are mature.

When executed the table returns the following results

Can anyone give any idea as to why this is happening?

Shown below is the full page of the Query

Most likely because the expression 1/10/1971 is interpreted as a numeric expression, i.e. 1 divided by 10 divided by 1971 which is 0 (with integer division rounding values down). Telling Access that this is a date can be done using quotes (not really recommended) or by using a date function, like this:

IIf([DOB]<DateSerial(1971, 10, 1), "Mature", "Not mature")

The above DateSerial() VBA function will return October 1, 1971. The parameters are (Year, Month, Day).

Also, the following will work (thanks, @ypercube!)

IIf([DOB]<#1971-10-01#, "Mature", "Not mature")


How does one know when to stop normalizing? Alright, so basically I feel that I tend to over-normalize things, and maybe I am doing it at the cost of performance. So, to illuminate on the problem, I have created the following schema to use as an example:

As you can see, I have outlined two different approaches. The idea here is that all universities have programs (e.g., Engineering), and all programs have majors (e.g., Electrical Engineering). For this example to work, we must assume that there are 40 programs, and say 1,000 majors, and that schools have the same programs/majors.

Now, my typical approach in this scenario is to take anything that may be repeated (i.e., majors and programs), and put those items into their own table; then have a relationship, as modeled above. Another approach, one I tend to stay away from, is the second model, in which program and major are columns with repeated values (e.g., Engineering may be repeated 1,00s of times over the table). Basically, if the value is repeated, I create a table for it.

Now, I'm not so much interested in which one of these is the better approach, since I am only using them as an example to illuminate the true question: How does one know when they are over-normalizing? I know that there is a point when you're going too far in normalizing your tables, but I never quite know what the measurement is.

Addendum

A university need not have all the majors in a program, thus the reason universities are tied to majors, not programs (e.g., University X has an Engineering school but does not have Nuclear Engineering, which is part of the Engineering program).

When designing a database, normalization is a process undertaken when producing the logical model i.e. the relational data model. Performance is an attribute of the physical model; the implementation of the logical model on a particular DataBase Management System.

You cannot "over-normalize" a relational data model. Either the relational data model is in fifth normal form  or it is in a lower normal form and update anomalies exist.

The normalization process is a well-defined procedure for analyzing the functional, multivalued and join dependencies of the relations within the model and taking projections of these relations to eliminate any dependencies not implied by the candidate key.

Assuming you have a relational data model in fifth normal form which has been implemented on a particular DBMS. At some point the performance of this implementation has become unacceptable. How you decide to address this issue will depend on the DBMS that is being used and the features that DBMS makes available.

Having looked at the features available to you for the DBMS being used you may decide to take some of tables in the DBMS and re-factor them so that they are in a lower normal form. (Note: the logical model has not changed, you have changed the physical model to address a performance issue with the implementation.) By re-factoring these tables you have introduced the possibility of update anomalies and, because your relational data model is in fifth normal form, you can determine precisely where these may occur. Having determined these you may choose to ignore them, or add some additional processing to either identify them and/or address them. (Note: by adding additional processing you may impact performance such that the perceived benefits of "denormalizing" these tables is actually negated.) In any case you are able to make an informed analysis of any action you may take.


how to find password for mySQL database server How can I find the password for my database on phpMyAdmin? I want to connect mySQL database just by using mysqli_connect and understand that three strings are needed, but I can't seem to find where the password could be. If anyone could help that would be great. Thanks

You can do it with some simple commands:
sudo su

cd /etc/mysql
cat my.cnf

When the file my.cnf is opened you can read it's contents, and it should contain your username and password for the current installation.
Hope this helps! ;)


MySQL 5.5 InnoDB = OFF? We run a server with MySQL 5.5. 95% of the tables are set to the InnoDB engine.

However whenever I look at the my.ini file (using the MySQL Workbench) under mysqld section innodb is unchecked and OFF is selected in the drop down. I don't see this line added to the my.ini file when viewing with notepad.

Should I check this setting and set it to ON so we can utilize the enabled innodb settings (buffers & log file etc.) or does this not matter?

Thanks

Sounds like an issue with MySQL Workbench.

MySQL 5.5 has a default storage engine of InnoDB.

In fact, the option default_storage_engine is predefined as follows:

MySQL 5.5.4 and prior, MyISAM
MySQL 5.5.8 and after, InnoDB

There is another option called storage-engine, which was deprecated as for MySQL 5.5.3. Keep in mind that MySQL 5.5.8 was the very first GA release.

I would strongly suspect MySQL Workbench if it is coded to check storage-engine instead of default-storage-engine. This would allow MySQL Workbench to monitor older release of MySQL.


Creating a GroupBy query to include a result for when no results match Let's say I need a query to see sales results by Month and I want the result-set to include results for each month whether there were any sales in that month or not.

And let's say my table "sales" has the columns "sales_date" and "sales_amount" and I want a result-set to look like:

Jan, 100.00
Feb, 240.00
Mar, 0.00
Apr, 430.00

So, if I want to sum the values by month and there aren't any records for Mar, I still want to see a result for Mar that's 0.  How would I do that?

The usual way to get a series in postgres is with generate_series. This function produces a series of integers or timestamps - you can use either but assuming your 'dates' are really timestamptz, here's how you might go about it if you are on 8.4 or above:

testbed:

create table sales(sales_date timestamptz, sales_amount numeric);
insert into sales(sales_date, sales_amount) values('2011-01-15 12:00', 100);
insert into sales(sales_date, sales_amount) values('2011-02-15 12:00', 240);
insert into sales(sales_date, sales_amount) values('2011-04-15 12:00', 400);
insert into sales(sales_date, sales_amount) values('2011-04-16 12:00', 30);

query:

with w as ( select month, sum(sales_amount) as total
            from (select date_trunc('month',sales_date) as month, sales_amount from sales) z
            group by month )
select to_char(month, 'fmMon') as month, coalesce(total, 0) as total
from (select generate_series(min(month), max(month), '1 month'::interval) as month from w) m
     left outer join w using(month);

result:

 month | total
-------+-------
 Jan   |   100
 Feb   |   240
 Mar   |     0
 Apr   |   430

--edit: a bit of extra detail on the query:

produce a summary of sales by month (but no month present if no sales):

with w as ( select month, sum(sales_amount) as total
            from ( select date_trunc('month',sales_date) as month, sales_amount
                   from sales ) z
            group by month )

which could alternatively be written as:

with w as ( select date_trunc('month',sales_date) as month, sum(sales_amount) as total
            from sales
            group by date_trunc('month',sales_date) )

produce an unbroken series of months (without sales) from the minimum to the maximum:

select generate_series(min(month), max(month), '1 month'::interval) as month from w

outer join the unbroken series to the summary of sales by month to produce an unbroken series with sales (or null sales if no sales present):

left outer join w using(month)

for the months with null sales, change the null to a 0:

coalesce(total, 0)


Delete data before INSERT I have this table which is used to generate second table with the result:

INSERT INTO payment_transactions_daily_facts (count, volume, date, year, month, week, day, transaction_type, contract_id, merchant_id, terminal_id, status, card_brand, currency)
SELECT
        COUNT(*) count,
        SUM(amount) volume,
        DATE(created_at) date,
        YEAR(created_at) year,
        MONTH(created_at) month,
        WEEK(created_at) week,
        DAY(created_at) day,
        type transaction_type,
        contract_id, merchant_id, terminal_id,
        status, card_brand, currency
        FROM payment_transactions
        WHERE created_at BETWEEN '2018-11-11' AND '2018-11-14'
        GROUP by date, contract_id, merchant_id, terminal_id, transaction_type, status, card_brand, currency

But I need to delete the old data before I make insert. Is there a way with one SQL to delete first the table data starting from some date and them to insert a new one?

Yes, you can issue a DELETE statement before your INSERT. If you do it inside a transaction, they will be essentially one operation, they will either fail or succeed together (assuming that the table uses a transactional engine, like InnoDB):

START TRANSACTION ;

    DELETE FROM
        payment_transactions_daily_facts
    WHERE
        date BETWEEN '2018-11-11' AND '2018-11-14' ;

    INSERT INTO
        payment_transactions_daily_facts
        (count, volume, date, ...)
    SELECT
         ...  ;

COMMIT ;


PRIMARY KEY CONSTRAINT fundamentals Can you explain why "primary key constraint" exists if, as you know, it is possible to create relationships between tables using unique indexes only ?

The RDBMS that is used is SQL Server.

In fact, after creating a unique index (not null), I can create a foreign key constraint on an other table pointing to the column that is indexed without having to declare that this column is "the primary key" ... so, what is the purpose of it ? Is it a question of esthetics ?

The distinction is mainly historical. The relational model was developed in part in response to the way IMS handled data.

  The presently released version of IMS provides the user with a choice for each file: a choice between no indexing at all (the hierarchic sequential organization) or indexing on the primary key only . . .

Source: A Relational Model of Data for Large Shared Data Banks

Codd retained that term. (Same source.)

  Normally, one domain (or combination of domains) of a given relation has values which uniquely identify each element (n-tuple) of that relation. Such a domain (or combination) is called a primary key.

It's not unusual for a relation to have more than one "combination of domains" that uniquely identify each tuple. The relational model offers no theoretical basis for elevating the status of one of them to "primary". As far as the relational model is concerned, all candidate keys are created equal.

As far as standard SQL is concerned, any column or combination of columns that's declared UNIQUE can be the target of a foreign key constraint. In the past, dbms platforms differed slightly in how they handled NULL in a column declared UNIQUE. That's part of the reason old-school database designers lean toward NOT NULL UNIQUE declarations; the other part is that NOT NULL UNIQUE is functionally equivalent to PRIMARY KEY.

There is a semantic difference between a UNIQUE index and a UNIQUE constraint. I prefer to see constraints expressed as constraints.


Left join not working with sub-query I've got table of scores where each entry corresponds to a particular (student, subject) pair.

CREATE TABLE score (
  id serial PRIMARY KEY,
  value integer NOT NULL,
  subject_id integer NOT NULL,
  student_id integer NOT NULL,
  CONSTRAINT s1_id FOREIGN KEY (subject_id) REFERENCES subject (id),
  CONSTRAINT s2_id FOREIGN KEY (student_id) REFERENCES student (id),
);

I want to pick the top 5 subjects with highest overall scores, and then compute the average score of each student across those 5 subjects. Some students may not have entries for some subjects. Those values would be given a default score.

Here's what I have:

SELECT student_id, AVG(COALESCE(score.value, default_value)) FROM
(
    SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum
    FROM score
    JOIN subject on subject.id = score.subject_id
    WHERE subject.name != 'skip me'
    GROUP BY score.subject_id
    ORDER BY score_sum DESC
    LIMIT 5
) AS score_sort
LEFT JOIN score ON score_sort.subject_id = score.subject_id
GROUP BY student_id

The inner query works correctly to select the top 5. But the LEFT JOIN in outer query does not select the rows where a student does not have a score. What am I doing wrong here ?

You could use a CROSS JOIN from score_sort to all the students and then a LEFT join to score:

SELECT st.student_id,
       AVG(COALESCE(sc.value, default_value)) AS average_score
FROM
(
    SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum
    FROM score
    JOIN subject on subject.id = score.subject_id
    WHERE subject.name != 'skip me'
    GROUP BY score.subject_id
    ORDER BY score_sum DESC
    LIMIT 5
) AS score_sort AS sort
  CROSS JOIN student AS st
  LEFT JOIN score AS sc
    ON  sort.subject_id = sc.subject_id
    AND   st.student_id = sc.student_id
GROUP BY st.student_id ;

A different approach would be to alter your query to count the available scores each student has (in the 5 top subjects):

SELECT score.student_id,
       (SUM(score.value) + (5 - COUNT(score.subject_id)) * default_value) / 5
           AS average_score
FROM
(
    SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum
    FROM score
    JOIN subject on subject.id = score.subject_id
    WHERE subject.name != 'skip me'
    GROUP BY score.subject_id
    ORDER BY score_sum DESC
    LIMIT 5
) AS score_sort
JOIN score ON score_sort.subject_id = score.subject_id
GROUP BY score.student_id ;

But even with this, you would still not get any result for the students that have no score in any of the 5 top subjects.


Reading SQL Performance Counters using PowerShell script I'm trying create a PS script to read some performance counter about my SQL for my Level 1 partners but I'm having a problem with the instance name.

I'm using this code:

$computer = "\\\" + $ENV:Computername
$instance = $computer + '\MSSQL$SG25DB'
write-Host $instance
Write-Output -InputObject "Use of Memory Buffer. The expected value must be greater than 80."
Get-Counter "$instance:Buffer Manager\Buffer cache hit ratio"

and the output is:

\\\\[hostname]\\MSSQL$SG25DB     <--- Here appears the proper value :)
Use of Memory Buffer. The expected value must be greater than 80.
Get-Counter : The specified counter path could not be interpreted. At
line:5 char:12
+ Get-Counter <<<<  "$instance:Buffer Manager\Buffer cache hit ratio"
+ CategoryInfo          : InvalidResult: (:) [Get-Counter], Exception
+ FullyQualifiedErrorId : CounterApiError,Microsoft.PowerShell.Commands.GetCounterCommand

I have verified with Write-Host that the value of variable $instance is properly constructed but when I apply it to the Get-Counter command its fails. Do you have any idea? Could be related with the special character $? How can I make run it properly?

Try this:

Get-Counter "$($instance):Buffer Manager\Buffer cache hit ratio"

What happens is that your $instance variable isn't isolated, so therefore you need to explicitly state what the variable is and where it ends.  You can do that with the above notation (dollar sign and parenthesis around the variable name).

Take a look at the different outputs of the following:

Write-Host "$instance:Buffer Manager\Buffer cache hit ratio"

The output is:

 Manager\Buffer cache hit ratio

Now with this correct notation:

Write-Host "$($instance):Buffer Manager\Buffer cache hit ratio"

The output is this:

\\MyMachineName\MSSQL$InstanceName:Buffer Manager\Buffer cache hit ratio

A common way to troubleshoot these sorts of problems is to use the above methodology, by seeing exactly what you're passing into other cmdlets/functions/etc.


SQLPackage.exe not picking up changes I'm trying to get SQLPackage for schema compare working from CMD line.  Have to compare a master db to 300 live dbs.  All 2014 SQL and none registered as data-tier applications. PowerShell and 3rd party tools are not an option at this time.

I've followed the scripts in the first answer here:
https://stackoverflow.com/questions/20673516/command-line-api-for-schema-compare-in-ssdt-sql-server-database-project

I can successfully create .DACPAC files from my source and my targets.

If I add some objects (1 table, 3 views) to "Test_B" database, the compare script does not show those differences when I compare the master to either the database itself or to the .dacpac that was generated.  BUT, if I run a SSDT schema compare from VS, it does pick them up and will create a .sql script with the expected DROP statements.

So the question: What am I doing wrong at the cmd line that Visual Studio/SSDT is doing right? Missing a parameter?  Wrong order of things?

#Compare DACPACs

sqlpackage.exe /a:Script /sf:C:\Users\hillke4\Documents\SQLScripts\DACPACS\ACACalculationDB.JTA.v7.master.dacpac /tf:C:\Users\hillke4\Documents\SQLScripts\DACPACS\ACACalculationDB.JTA.v7.Test_B.dacpac /tdn:ACACalculationDB.JTA.v7.Test_B /op:C:\Users\hillke4\Documents\SQLScripts\DACPACS\Deltas\ACACalculationDB.JTA.v7.Test_B.sql

#Compare master dacpac to database

sqlpackage.exe /a:Script /sf:C:\Users\hillke4\Documents\SQLScripts\DACPACS\ACACalculationDB.JTA.v7.master.dacpac /tsn:US1213113W1\SQL2014 /tdn:ACACalculationDB.JTA.v7.Test_B /op:C:\Users\hillke4\Documents\SQLScripts\DACPACS\Deltas\ACACalculationDB.JTA.v7.Test_B.sql

The issue is that the default deployment options when using Script or Deploy actions are set up to avoid dropping objects so as to minimize the risk of data loss. Schema Compare, on the other hand, is targeted at an interactive mode where the actions (Add, Delete, Update) are much clearer so this doesn't occur. To generate the Drop syntax as expected, add the property shown below (from the SqlPackage.exe documentation) to your call.

Property: /p:DropObjectsNotInSource= {True | False}

Default: False

Description: Specifies whether objectsthat do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database.


Why is MongoDB oplog size different between servers I have a ReplicaSet running fine once I ran rs.stepDown() and looking for rs.status() I get fine logs.

Here is one as evidence:

rsdev:SECONDARY> db.printSlaveReplicationInfo()
source: lab:27017
    syncedTo: Mon Jul 17 2017 09:20:52 GMT-0300 (BRT)
    0 secs (0 hrs) behind the primary

I got one problem with my application that if I change the primary to the server X which used to be the secondary than I just can't use its oplog as I was when using server Y as primary.

I know that when I have  a new member in the ReplicaSet it may will have the dataSet size smaller than its primary due to its age, delete, insert and so on.

But should its oplog size be different between them?

rsdev:PRIMARY> db.getReplicationInfo().usedMB
40543.1

rsdev:SECONDARY>  db.getReplicationInfo().usedMB
21734.5

This secondary server is the new one which I get into problem if become primary.

Both using wiredTiger storage.

If you haven't defined in config (file or parameter; replication.oplogSizeMB) what size of opLog you wanted when node was initialized, it is automatically set base on free space on data disk. In wireTiger it's 5% of free disk space (990MB-50GB).

You can change that oplog size if you want, but after that you need to wait until it's full.


Long Relational Algebraic expression provided than actually required Schema Diagram:

Make a list of project numbers for projects that involve an employee
whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project.

The given solution is :

However, I believe that simply using the following expression should be enough:

Now, the reason why I believe this is mainly because of the fact that the EMPLOYEE relation holds the tuples which correspond to workers as well as managers. Now, an employee can be either a worker or a manager. So, going by that logic the above expression should be enough.
I wholeheartedly agree with the fact that a given query can be represented through more than one relational algebraic expressions, however doing what the given solution (the bigger one) suggests above is highly redundant.

So to sum it up, is the direction of my thought correct?

Source : Fundamentals of Database Systems by Ramez Elmasri & Shamkant B. Navathe, 6th Edition, Page No. 172 Query 4.

Trying to find a simpler solution is admirable, but your expression is subtly wrong.

The important part of the question is:

  ...or as a manager of the department...

Notice, that's not the same as "...or as a manager of the employee...".

Let's throw some names out there.

Ed White works for Brian Green. Ed is working on Project 123.
Project 123 is controlled by the Finance Department. Kathy Smith is the manager of the Finance Department.

So the requested result should include Project 123 in the output, because Project 123 is controlled by a department whose manager has a last name of Smith.

Your proposed solution will not include Project 123, because neither Ed nor his manager have a last name of Smith.


Most efficient to prevent duplicates I'm building an application that will fetch messages with JSON every once in a while and store these in a postgres db. Now I want to prevent duplicates, so I calculate the sha-512 of the message and set it as the primary key. Since I want to prevent duplicates I can do two things:

Fetch all hashes from the database and see when inserting new messages if the hash already exists (checking in code)
Use an insert ignore (in postgres an update probably, since postgres doesn't support insert ignore) and let the database handle these exceptions

Considering I will probably be inserting a few messages at a time, I was wondering if method two will look up the primary key and see if exists for every insert as opposed to just storing a list in memory with method 1. What would be the most efficient?

PostgreSQL doesn't have INSERT ... IGNORE so that isn't an option. You can use upsert-like operations (search for "postgresql upsert) but they certainly have a performance cost. Inserting and ignoring the resulting error on duplicate key is possible, but it tends to result in pretty spammy logs.

Personally, I'd do what's effectively do a bulk upsert, where I'd do something like this:

CREATE TEMPORARY TABLE staging_table(...)
COPY TO staging_table(...) FROM ... (or do bulk inserts)
LOCK TABLE real_table IN EXCLUSIVE MODE
INSERT INTO real_table(...) SELECT col1,col2,col3,... FROM staging_table WHERE NOT EXISTS (SELECT 1 FROM real_table WHERE real_table.primarykey = staging_table.primarykey)
COMMIT

You can do an UPDATE before the INSERT if you want, updating existing records with new values.

The LOCK TABLE is necessary to prevent concurrent updates failing with errors. You can still SELECT from the table during the update.

If you EXPLAIN the plan for your INSERT you should see that it's been transformed into a JOIN when you're inserting larger numbers of records.


Creating a 'shadow' database in MySQL A while back, a friend told me of a facility that would allow us to "shadow" a production database using a local database. The term shadow may be incorrect (so please correct me if so)

What I understand, and want to do is:

When I make an update / insert, it only impacts the database that is local.
I make a select statement, the return is first the data from the database that is shadowing, then the production data is filled in.
table modifications only impact the database that is shadowing
clear out (roll back transactions?) to get the local to a neutral point.
would prefer not to have to copy the database over, but pull from the live data if possible.

Since this is not designed for production, performance is not really an issue.

The use case in mind is that I would have said database for local / testing, and a quick way to revert all the changes that I had done, if they need to be.

In Oracle Database world, what you are describing sounds like dNFS-based cloning (http://www.oracle.com/technetwork/server-storage/hardware-solutions/o13-022-rapid-cloning-db-1919816.pdf [PDF], https://oracle-base.com/articles/11g/clonedb-11gr2).

Start with a read-only copy of all the data files, which are read by the clone instance.  Changes/writes are written to delta files ("copy on first write"), and the database looks to these delta files before looking at the read-only copies.  This provides full "copies" of the database content in remarkably little space.  The whole lot can be rolled back by shutting down the instance and removing the delta files.

I've never seen such a thing in MySQL.  It might be possible with fancy storage tricks, but I don't think it's a feature that the database supports.


why locks a table will improve performance during transaction? I just started to read about table lock in SQL (from this page)

It saids:

  table lock may reduce the overhead of acquiring a large number of row
  or page locks and save overall locking time

From my understanding it will prevent other query to access the table I have locked, but why will this improve the performance? Isn't it will slow down over roll queries when there is mutiple user trying to use the table?  Can someone explain little bit more?

Let's say you have a million row table.
One million individual row locks is a lot of memory that can be put to better use.

I'd also read some SQL Server articles on lock escalation:

SQL Server Storage engine team http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/lock-escalation.aspx (selected quote, my bold)

  Similarly, if you lock individual rows, you will get higher concurrency but then you will incur the overhead of acquiring/releasing locks on each row and lot more locking resources depending upon the isolation level of your transaction, as you may need to hold the locks on all the rows till the end of transaction.

DBA.SE: https://dba.stackexchange.com/a/6449/630
DBA.SE: https://dba.stackexchange.com/a/6515/630


How to determine if a postgres database needs to be vaccumed? Given a PostgreSQL database what command can be used to determine if the database needs to vacuumed?

UPDATE I am working with client who is evaluating postgres but I am not a DBA, I just happen to be more experienced with postgres than they are. They are getting update times of several hours in their tests with 500K rows in a table. They had not done any changes to their GUCS for shared buffers, work_mem ... etc I just went through with them and had them do that. In some of their tests postgres reports 3674 seconds for updates that take 2.66 seconds in oracle and 33.8 seconds in MySQL. I want to be able to answer the question would Vaccum make a difference to their tests, but to be able to get real data back to them about the impact of vacuum on their tests.

Postgres 9.2 on CentOS 6 Intel Dual Process Quad Core Xeon K5570 8GB RAM

There is no single parameter that determines, whether vacuum is necessary, but it can be quite interesting to look at the thresholds that postgres uses to trigger autovacuum.

Details and a query to do so can be found in another thread: Aggressive Autovacuum on PostgreSQL.


Can't stop replication sp_dropdistpublisher responds "there are subscribers defined" I am running into errors:

exec sp_dropdistpublisher @publisher='servername'

response:

  Msg 21047, Level 16, State 1, Procedure sp_dropdistpublisher, Line 145
  Cannot drop the local distribution Publisher because there are
  Subscribers defined.

There is no subscriber defined, and I don't know where to find the information that makes SQL Server think that there is a subscriber defined.

I'm open to any suggestions...

Have you tried using this to enumerate the list of subscribers to find if it thinks there are some defined? Notice that I'm just starting in sp_dropdistpublisher and reading the code to see where it checks before deleting. Just an idea.

--
-- Name:
-- sp_MSrepl_enumsubscriptions
--
-- Description:
--  Enumerate subscriptions on a database
--
-- Returns:
--  0 == Failed
--  1 == Succeed
--
-- Security:
--  public, db_owner check
--  Requires Certificate signature for catalog access
--
-- Notes:
--  Used by the UI to generate a list of subscriptions
--


Extract N rows from 2 tables (t1, t2) - if t1 doesn't match N rows, then (and only then) extract data from t2 I would like to extract 100 names from table t1 and t2 where id = 'value', in such a way so that if table t1 hasn't 100 names, I will then go and take the remaining names from table t2.

The first part of the query would be (for table t1)

SELECT distinct name, id
FROM t1 WHERE id = 'value'
ORDER BY id
LIMIT 100;

You should be able to do this by using a union to join the two queries together. This assumes that Origin is a value that signifies which table is being queried so that results from t1 take precedence.

SELECT 1 as origin, name, id

FROM t1 WHERE id = 'value'

union

SELECT 2 as origin, name, id

FROM t2 WHERE id = 'value'

ORDER BY origin, id

LIMIT 100


How to get the result of MySQL explain query into a file? I am unable to get the results of the explain query in its normal tabular format or json format into a file.

select * from table_name into outfile 'file.format';
select trace from information_schema.optimizer_trace into outfile 'file.json' lines terminated by '';

The above queries work, and I get the file. But the following queries do not create the file.

explain select * from table_name into outfile 'file.txt';
explain format=json select * from table_name into out 'file.json' lines terminated by '';

Is there any way to achieve this? It is preferable if the tabular explain results are written in text file with column names and table borders.

Using the syntax of EXPLAIN, no.

You can run the explain from the command line however:

mysql -e 'EXPLAIN ... ' > /tmp/myexplain.json

SQL isn't responsible for the pretty presentation of data, Workbench is one option.


Postgresql sequence always 1 I have an application that often does insertion into PostgreSQL database, for some tables I have id column with serial type with auto-generated sequences. Running

SELECT nextval(pg_get_serial_sequence('client', 'id'));

on each new session gives 1. What should I do to correct this, or I need to this manually in my app?

For last case, I think get total records and alter sequence, but this is 2 more queries to do, which I better skip.

What you want is how sequences in PostgreSQL generally behave.

You must have some rogue process which is issuing a command such as:

alter sequence client_id_seq restart;

Or something equivalent to that.  Figure out what is issuing that command, and make it stop.

If you can't figure out who is sabotaging your sequence, there are a variety of ways to figure it out.  One is to crank up your log level by setting log_statement to 'ddl' or 'all', and look in the logs.  Another is to make your table and sequence owned by a role which does not normally log into the database, and grant rights to the table and sequence to the normal log-in users.  They will then get errors when they try to reset the sequence, because only the owner can do that.  (This is a good practice anyway, to have objects owned by special roles which do not log in except when it is necessary to change the schema.)


SSAS cell permission 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.

I was doing it the wrong way. It's not in the "Cell Data" tab, It's in the "Dimension Data" tab. The "Enable Visual Totals" checkbox has to be checked.


Server configuration SQL Server 2008 I am really fresh in the DB world so firstly,sorry about my noob question.
I have a data warehouse project to perform in a group of 3 people.
When installing SQL Server 2008 R2 Developer in my own machine, when configuring the server, I expected that I could "use the same account for all SQL Services" in order to facilitate the access for all the group's members and I received a few errors after define username and password that block the instalation process.
Should I select Authority/SYSTEM? Or in negative case how can I define a single account without errors?

Thanks,
Francisco

Best practice is to use a domain account for SQL Services, preferably a different one for SQL Server and SQL Server Agent. These accounts should not be local admins (and do not need to be), you should use a principle of least privilege. You need to set these up in advance so you can enter the account credentials during SQL Server setup.

One question, why on earth are you doing a new project (it is 2016) on an 8 year old version of SQL Server? SQL Server 2016 RTMs [releases to manufacturing] on 1.6.2016, i.e. in just over 4 weeks. I'd recommend using that.


Quotename in Openquery I am using bcp to export query results to CSV file. The query works perfectly as below:

declare @sqlcmd varchar(6500)

set @sqlcmd='bcp "SET QUOTED_IDENTIFIER OFF SELECT * FROM OPENQUERY(LinkedServerName, '
           +'''SELECT [column1] FROM Hist '
           +'WHERE DateTime >= DateAdd(hh,-24,GetDate()) '
           +'AND DateTime <= GetDate()'')" '
           +'queryout C:\Temp.csv -T -c -t,'

exec xp_cmdshell @sqlcmd

After I added quotename function to column1, the below query doesn't work:

declare @sqlcmd varchar(6500)

set @sqlcmd='bcp "SET QUOTED_IDENTIFIER OFF SELECT * FROM OPENQUERY(LinkedServerName, '
           +'''SELECT quotename([column1]) FROM Hist '
           +'WHERE DateTime >= DateAdd(hh,-24,GetDate()) '
           +'AND DateTime <= GetDate()'')" '
           +'queryout C:\Temp.csv -T -c -t,'

exec xp_cmdshell @sqlcmd

Result:

Error Message Incorrect syntax near '('

I would appreciate it if anyone can give me some advise. Thanks

It sounds like the linked server may not have a QUOTENAME function. Perhaps it's not a SQL Server itself, or is older than SQL 2008?

Check what that server is, and the functions it can perform. Note that:

QUOTENAME([column1])

is equivalent to:

'[' + REPLACE(column1, ']', ']]') + ']'

I think most SQL variants will have some version of REPLACE.

UPDATE: Based on you comments, you actually wanted to format column1 for inclusion in the CSV file, assuming it may have commas in it.

To do that, you need to put double quotes around the contents of column1, and replace any double quote characters in column1 with two double quotes (which acts as an escape).

So, again, assuming that the destination server does have some form of the REPLACE function, you should be able to use:

'"' + REPLACE([column1], '"', '""') + '"'

An alternative:  If there's no equivalent to the REPLACE function on the remote server, you could try setting up an SSIS package to do the job. You can replace column1 in the data flow with the above, using a Derived Column component. However, this is probably not a great option if you're not familiar with SSIS packages already.


What is it called when you search the middle of a string instead of the beginning? I am trying to polish up my vocabulary to better communicate with my fellow developers. We have several places in the site where we are debating if we should search for a string from the beginning 'running%' vs anywhere in the string '%running%.

I have been calling the middle search "fuzzy" which I realize is incorrect as fuzzy means changing the form of the word "run", "runing" [sic], "runed" [sic].

What is the correct terminology for searching the beginning of a string and searching the middle of a string?

It's called an "un-anchored search pattern", and it looks like this in SQL.

foo LIKE '%bar%'

If you lack a % on either side, it is said that the search pattern anchors to the start or end of the string respectively. This lingo comes from the regex world.

foo LIKE 'bar%'

You would say, "the search pattern bar% anchored to the start of the string".

For comparison, a PCRE is anchored with ^ or $ tokens and it looks like ^bar or bar$. PCREs require explicit anchoring with tokens, whereas SQL LIKE statements are implicitly anchored and require explicit % to create an "un-anchored search pattern".

As a side note, you can index these types of expressions with trigrams using something like pg_trgm in PostgreSQL


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

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

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

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

So all

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

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

Any help is much appreciated.

The list of the servers are kept in the msdb database.

SELECT * FROM msdb.dbo.sysmanagement_shared_registered_servers

If you're wanting the connections and tabs to be the same, then you would need to go to Tools --> Import and Export Settings... and export the selected environment settings.
The two that would work for what you're asking for is under Options --> Environment. The two options would be "General" and "Tabs and Windows". It will export to a file. Send that to whomever will need it, and they can import it.


My Galera Cluster does not connect across AWS regions I am building a MariaDB Galera Cluster, on AWS, EC2. Using MariaDB 10.5 and Galera 4.
I have two nodes in the California region, named galera_ca and galera_ca2, and a third node in Oregon name galera_or
The security groups between the servers are wide open, and the ping time between OR and CA is 12ms
I started galera_ca using galera_new_cluster
I started galera_ca2 using systemctl start mariadb

The two California nodes connect and share data just fine.
wsrep_cluster_size indicates that I have two nodes in the cluster.

I started galera_or using systemctl start mariadb

Oregon then hangs on starting for a very long time, and wsrep_cluster_size shows three nodes, but the server cannot be connected to.
Eventually systemctl start mariadb returns this message:
Job for mariadb.service failed because a fatal signal was delivered to the control process. See "systemctl status mariadb.service" and "journalctl -xe" for details.
systemctl status mariadb.service doesn't show anything exceptional,
journalctl -xe shows:
requested state transfer from 'any', but it is impossible to select State Transfer donor: Resource temporarily unavailable
wsrep_cluster_size still shows three nodes, but mariadb is not running in OR.
If I try to shut down either of the California servers systemctl stop mariadb it never returns.
All three servers have basically the same configuration:
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so

wsrep_node_name='galera_ca'
wsrep_node_address="INTERNAL ADDRESS OF THIS NODE"

wsrep_cluster_name='galera-experiment'
wsrep_cluster_address="gcomm://EXTERNAL ADDRESS OF NODE CA, EXTERNAL ADDRESS OF NODE CA2, EXTERNAL ADDRESS OF NODE OR"

wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
wsrep_slave_threads=4
wsrep_sst_method=rsync

binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

If I try just one CA node and the OR node, the result is the same.

In wsrep_node_address on Amazon EC2, you must use the global DNS name, instead of the internal IP address.


RMAN incremental restore Can you anyone tell me if it's possible to restore an 11g Oracle database using media management software like Net Backup or Commvault incrementally?

I need to perform a restore of a DB into a new target database (overwriting the DB) ... this of course is fine - connect to the RMAN catalog and then duplicate the DB. However I would then, perhaps 5 days later, like to apply all the incremental changes that occurred on the source to this new target. Is it possible to restore again but only apply the logs from the previous 5 days and roll forward? The new target can be left mounted during this time. It's not being used until all logs are applied. The idea is to save time restoring a massive database before switching over to it permanently. Many thanks.

Sure it is possible. I have done this dozens of times.
It is a simple RESTORE, then a RECOVER phase 5 days later. Or you can even run RECOVER periodically to keep the copy up to date.

Just make sure you do not mess up the catalog with the copy database. Usually I just restore the latest controlfile and use without a catalog connection to prevent RMAN registering restored copies of datafiles in the catalog.

Or use DUPLICATE if you want to use Data Guard as suggested.


ERROR 1067 (42000) at line 7: Invalid default value for 'LAST_UPDATED' Running MySQL 5.5, got the error above when I tried to import a .sql file

I've checked the SQL_MODE of my server and nothing similar to 'NO_ZERO_DATE' is found

As well, I've took out the text of 'STRICT_MODE' from my my.ini file and restarted the server, yet this error still persists

Here's the .sql file being imported

CREATE TABLE  `street_segment` (
  `ID` int(8) NOT NULL,
  `PROVINCE_CODE` varchar(2) NOT NULL,
  `POSTAL_CODE` varchar(6) NOT NULL,
  `MUNICIPALITY_NAME` varchar(30) default NULL,
  `STATUS` varchar(16) NOT NULL,
  `LAST_UPDATED` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DIRECTORY_AREA_NAME` varchar(30) default NULL,
  `STREET_NAME` varchar(255) default NULL,
  `STREET_TYPE_CODE` varchar(6) default NULL,
  `STREET_DIRECTION_CODE` varchar(2) default NULL,
  `ADDRESS_SEQ_CODE` int(8) default NULL,
  `STREET_ADDRESS_FROM_NUMBER` varchar(6) default NULL,
  `STREET_ADDRESS_TO_NUMBER` varchar(6) default NULL,
  `NUMBER_SUFFIX_FROM` varchar(6) default NULL,
  `NUMBER_SUFFIX_TO` varchar(6) default NULL,
  `SUITE_NUMBER_FROM` varchar(6) default NULL,
  `SUITE_NUMBER_TO` varchar(6) default NULL,
  `STREET_NAME_ACCENT_F` int(1) default 0,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This error can occur when you attempt to import a file into MySQL Server 5.6.4 and earlier, when the database was created on and exported from MySQL 5.6.5 or later.

  As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table."

  — https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

It has been suggested that the workaround was to use TIMESTAMP rather than DATETIME for this column, since that appears to work, but that solution may fail for multiple reasons.  Additionally, though it gives the initial appearance of working for this specific table, MySQL Server versions before 5.6.5 have other limitations and implicit behavior for TIMESTAMP columns such that this workaround would not be usable in every case.

Notably, in all reasonably modern (5.x and up) versions of MySQL Server the TIMESTAMP and DATETIME data types are not fully interchangeable.  DATETIME columns support a broader range of values than TIMESTAMP and TIMESTAMP is subject to the database connection's notion of time zones, with automatic time zone conversions, while DATETIME is not.

Since the database was created on 5.6 or later, for optimum compatibility, you should be loading it onto a server that is also 5.6 or later.  Throughout the 5.x series, things are generally backward-compatible; that is, it is generally possible to restore a dump file on a later version of the server, but due to the introduction of new features, the reverse is not as true.

See also:

Time Zone Support
The DATE, DATETIME, and TIMESTAMP Types
Should I use DATETIME or TIMESTAMP?


Recover a MySQL InnoDB from ibdata1 that won't repair using innodb_force_recovery We have a MySQL database that we have manage to grab the raw files from a server with a fatal hardware failure. However, this failure seems to have occurred when the DB server was running.

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

innodb_force_recovery does not work from 1 to 6.

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

Thanks in advance

PS - backups have also failed for a different reason


MySQL Master/Slave replication on same VM instance? I'm fairly new to databases so I hope this isn't a silly question!

Is it possible to set up master/slave replication on the SAME server?

I understand there are not any advantages to this in terms of hardware utilisation/recovery purposes, but a set up like this would help test a piece of software I am developing.

It's possible, why not. You have to run two instances of MySQL. One will be a master, second - a slave.

Check this for detailed instructions https://dev.mysql.com/doc/refman/5.5/en/multiple-servers.html

UPDATE:

Usually I use Vagrant for this. Here's a piece of code to setup a master + slave.

Vagrantfile:

Vagrant.configure(2) do |config|
  config.vm.define "db01" do |db01|
    db01.vm.box = "bento/centos-6.7"
    db01.vm.hostname = "db01"
    db01.vm.provision :shell, path: "bootstrap-master.sh"
    db01.vm.network "private_network", ip: "192.168.50.101"
    db01.vm.synced_folder "../", "/home/vagrant/src/twindb"
  end
  config.vm.define "db02" do |db02|
    db02.vm.box = "bento/centos-6.7"
    db02.vm.hostname = "db02"
    db02.vm.provision :shell, path: "bootstrap-slave.sh"
    db02.vm.network "private_network", ip: "192.168.50.102"
    db02.vm.synced_folder "../", "/home/vagrant/src/twindb"
  end

bootstrap-master.sh

#!/usr/bin/env bash

set -e

hostname="`hostname`"

yum -y install https://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm

packages="
mysql-community-server"

yum -y install ${packages}

chkconfig mysqld on

function wait_for_mysql() {
    # wait till mysql starts
    timeout=300
    mysql_started="NO"
    while [ ${timeout} -gt 0 ]
    do
        if ! [ "`mysql -e 'SELECT 1'`" = "1" ]
        then
            echo "SUCCESS"
            break
        fi
        sleep 1
        let timeout=$timeout-1
    done
}
cat <<EOF > /etc/my.cnf
[mysqld]
server_id=101
log_bin=mysqld-bin
EOF

/etc/init.d/mysqld start

if [ "`wait_for_mysql`" = "SUCCESS" ]
then
    mysql -u root -e "RESET MASTER"
    mysql -u root -e "CREATE USER 'replication'@'%' IDENTIFIED BY 'bigs3cret'"
    mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%'"
    mysql -u root -e "GRANT ALL PRIVILEGES ON *.* TO root@'%'";
else
    echo "MySQL failed to start"
    exit -1
fi

bootstrap-slave.sh

#!/usr/bin/env bash

set -e

hostname="`hostname`"

yum -y install https://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm

packages="
mysql-community-server"

yum -y install ${packages}

chkconfig mysqld on

function wait_for_mysql() {
    # wait till mysql starts
    timeout=300
    mysql_started="NO"
    while [ ${timeout} -gt 0 ]
    do
        if ! [ "`mysql -e 'SELECT 1'`" = "1" ]
        then
            echo "SUCCESS"
            break
        fi
        sleep 1
        let timeout=$timeout-1
    done
}
cat <<EOF > /etc/my.cnf
[mysqld]
server_id=102
log_bin=mysqld-bin
EOF

/etc/init.d/mysqld start

if [ "`wait_for_mysql`" = "SUCCESS" ]
then
    mysql -u root -e "RESET MASTER"
    mysql -u root -e "CHANGE MASTER TO
        MASTER_HOST='192.168.50.101',
        MASTER_USER='replication',
        MASTER_PASSWORD='bigs3cret',
        MASTER_LOG_FILE='mysqld-bin.000001',
        MASTER_LOG_POS=120"
    mysql -u root -e "START SLAVE"
else
    echo "MySQL failed to start"
    exit -1
fi