texts: Why am I getting a "login failed" when creating this linked server?

texts:
I'm creating a linked server from ServerA to ServerB, both of them are SQL Server 2008 servers. I need to create the linked server using a domain service account so that anyone will be able to utilize the linked server. ServerB only accepts Windows authentication, so I've added the service domain account to ServerB with proper permissions. I've verified the permissions by logging in with the service domain account to ServerB and everything looks great. But when I create a linked server on ServerA with the script below, I get a "login failed for user". Is this script incorrect, or am I missing something else?

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'ServerB', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'ServerB', @catalog=N'MyDatabase'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ServerB',@useself=N'False',@locallogin=NULL,@rmtuser=N'mydomain\myuser',@rmtpassword='mypassword'
GO


texts: Can I calculate the number of locks required by pg_dump ahead of time?

texts:
I've got a multi-tenant app on postgres 9.5 DB with some 200+ schemas each with about 170 tables on.  pg_dump operations have become a frustrating hit and hope exercise where I fiddle with max_locks_per_transaction and shared memory and eventually the dump runs, but this is based on me doubling the values in the various files with no science behind the number other than new_value = 2 * current_value.

I noticed this blog post where Josh Berkus says:

  Given that a lock in Postgres is usually a lock on an object (like a table or part of a table) and not on a row...

So I'm wondering if there's a way to calculate the objects in a given DB? That way I could start to calculate:

The required amount of locks and shared memory required
The growth over time of the values


texts:
I'm new to SQLite and searching for a way to create a query to do the following:

I have 3 tables:

goods: List of goods
tags: list of tags
tag_goods: table used to associate a tag to a product

This is the structure for each table:

Goods:

id (INTEGER, autoincrement)
name (TEXT, name of the product)

Tags:

id (INTEGER, autoincrement)
name (TEXT, name of the tag)

Tag_goods:

tag_id (INTEGER)
goods_id (INTEGER)
UNIQUE (tag_id, goods_id)

What I want:

Obtain ONLY ids and names of the products that are tagged with ALL of the possible tags (NB: I need to reference all the tags in the query, not one by one).

(Meaning, for example I only have four tags: "yellow", "round", "eatable", "organic" and only goods "Cheese" and "Melon" match all of them)

With my very limited experience I understand, that I can try something like intersect, but it is impractical when there are too many tags.


texts: Assign dbo role to login without creating a new user in db

texts:
I would like to create assign dbo rights on an existing database to an existing login without creating a new user in the database. I would like to have the login use dbo user, like a member of sa server role does.

All I have found involves creating a new db user.

Thanks!


texts: How write mysql query corresponding this tables

texts:
This is my database tables

I want to get this result:

How can I write select query to get that result?


texts: How to resolve COM server application local activation permission for machine account

texts:
I am trying to enable SSIS package logging to event logs. When an attempt is made to write to the event log the following error appears in the system event logs:

  The application-specific permission settings do not grant Local
  Activation permission for the COM Server application with CLSID
  {806835AE-FD04-4870-A1E8-D65535358293}  and APPID
  {EE4171E6-C37E-4D04-AF4C-8617BC7D4914}  to the user DOMAIN\machine$
  SID (S-1-5-21-16020293-282541685-632688529-241633) from address
  LocalHost (Using LRPC). This security permission can be modified using
  the Component Services administrative tool.

I've dealt with this error before but the fact the error relates to a machine account has thrown me. Does this mean I have to specifically grant permissions to the machine account in component services? The app pool account running under the NETWORK SERVICE account.


texts:
When I use "load data infile" on master, the data is properly transferred to slave.
But when I use "load data local infile" from remote server to master, the data does not pass on to slave.

Why does the data from remote server is not read by slave?


texts: What firewall ports should be opened for central management server

texts:
Within my environment we currently have a MSSQL installation which serves as the Master in a Multiserver Agent environment (MSX). Due to this server being within a tightly controlled network the necessary ports for MSX functionality have not been opened.

As shown in the image, when I try to enlist a target (TSX) to the master (MSX) I am given an error that the RFC server is not available, I am also unable to deploy multi-server jobs to targets.

What ports should I request to be opened from targets to the master and vice versa?


texts: "The backup set holds a backup of a database other than the existing database." - No matter what - even with replace?

texts:
I have a strange problem. I have been able to restore + overwrite databases for years on our SQL Server yet suddenly I seem to be unable to overwrite any databases.

I should be system admin, there is nobody else who can change settings.

I can restore a database back over itself without any trouble.
Overwriting to another database causes me the below error:

  Msg 3154, Level 16, State 4, Line 2
  The backup set holds a backup of a database other than the existing database.
  Msg 3013, Level 16, State 1, Line 2
  RESTORE DATABASE is terminating abnormally.

For example, I can create 2 brand new databases, try to restore one to the other, or save to file then restore, and I get the above error message.

It's like the replace function is having no effect.

This happens on SQL Server 2008 R2.
I have this problem both on the server itself on SQL Management Studio 10.50.6560.0.
And on my local PC on SQL Management Studio (v17.9.1) 14.0.17289.0.

I've tried this in both the GUI and using SQL script:

*RESTORE DATABASE [Database2] FILE = 'Database1' FROM DISK = 'G:\MSSQL\TempBackups\database1.bak' WITH NORECOVERY,
MOVE 'Database1' TO 'd:\MSSQL\DATA\Database2.mdf',
MOVE 'Database1_log' TO 'm:\MSSQL\LOGS\Database2_log.ldf',
STATS = 5,
REPLACE*

Any ideas on what this could be or things to try? I feel like I've trawled the Internet for any script references I can try and nothing is working.


texts: Fix Linked Server Without Restarting SQL Server Instance

texts:
Problem:
Occasionally a nightly job will fail, and when I look into it, it is the result of a query across a linked server failing. But for some reason that query seems to remain 'open' in some way and cause subsequent failures.

Bad Solution:
The only way I have found to fix this is to restart the instance which seems to clear everything up. But when it happens in production, obviously it is a bit of a hassle.

Question:
Does anyone have strategies for resetting a linked server which is stuck open? I am not even really sure what is happening under the hood.

Other Checks made:
 -

Tested the query on the server linked to. And I can select the data fine on that side
Tested everything else on the server w/ the link. Everything works fine there it is just that when it tries to query across that link it fails.

Is the only solution to this to really restart the instance?


texts:
I plan on building a web based room booking application for meeting rooms at a company. The requirements are not set in stone and are pretty much left to me, as long as these core ones are met:

Users can view monthly schedule for meeting room, so see what days and times there are bookings for a particular room.
Users can book a meeting room
Users can edit,delete a booking once made.
Receptionists have power to cancel bookings, even though they were not the ones that made them.

If I have missed any obvious ones, please point them out.

So with the above in mind, I have come up with the following ERD.

Can you see any obvious issues with the above ERD? I have gone through the above scenarios / requirements, and I believe the above design satisfies them all, but it is possible that I am very much mistaken (has happened before).

Any help and feedback would be greatly appreciated.


texts: Why do database servers have different time_source values

texts:
I have two SQL Server 2008 R2 Enterprise SP1 servers that have different values for sys.dm_os_sys_info.time_source.

The time_source column of sys.dm_os_sys_info indicates the API that SQL Server is using to retrieve wall clock time:

0 = QUERY_PERFORMANCE_COUNTER
1 = MULTIMEDIA_TIMER

What causes the difference and can I change SQL Server to use the more precise QUERY_PERFORMANCE_COUNTER value?

+--------------------------+---------------------------+-------------------------+
|                          | Original                  | New                     |
+--------------------------+---------------------------+-------------------------+
| time_source              | QUERY_PERFORMANCE_COUNTER | MULTIMEDIA_TIMER        |
| os install               | clean                     | clean                   |
| virtual/physical         | physical                  | virtual                 |
| hypervisor               | n/a                       | vmware, ESXi 5.5        |
| VMware hardware Version  | n/a                       | 7                       |
| bcd nx                   | optin                     | optout                  |
| bcd recovery enabled     | no                        | yes                     |
| bcd /usepmtimer          | no                        | no                      |
| sql sku                  | sql standard 2008 r2      | sql standard 2008 r2    |
| sql upgrade from express | no                        | no                      |
| sql auto-close any db    | no                        | no                      |
| power plan               | high performance          | balanced                |
| dm_os_performance_counter| yes                       | yes                     |

+--------------------------+---------------------------+-------------------------+

My coworker sent this very interesting link: How It Works: Timer Outputs in SQL Server 2008 R2

VMWare uses a different API for time source depending on the OS version and the VM Hardware version. They also have a whitepaper with lots of details.


texts: Mysql update different fileds set depend on condition

texts:
I have two tables "a" :

CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `i1` int(11) DEFAULT NULL,
  `i2` int(11) DEFAULT NULL,
  `ib` int(11) DEFAULT NULL,
  KEY `id` (`id`)
)

 +----+------+------+------+
| id | i1   | i2   | ib   |
+----+------+------+------+
|  1 |    1 |    1 |   10 |
|  2 |    1 |    1 |    1 |
|  3 |    1 |    1 |    2 |
|  4 |    1 |    1 |    3 |
|  5 |    1 |    1 |    5 |
+----+------+------+------+

and "b"

CREATE TABLE `b` (
  `i` int(11) DEFAULT NULL
)

+------+
| i    |
+------+
|    5 |
|    1 |
|   10 |
+------+

My script parse external data (jid,j1,j2) and update table "a".
With 2 commands:

update a set i1=j1, i2=j2 where id=jid
   and not exists (select 1 from b where a.ib=b.i)

and

update a set i1=j1 where id=jid
  and exists (select 1 from b where a.ib=b.i)

So, I want to update both fields i1 and i2 if I have not related record in "b" table, and update only one field i1 if I have related record.

If I have input data

(1,2,3)
(2,5,6)
(3,7,8)

My table "a" should be:

+----+------+------+------+
| id | i1   | i2   | ib   |
+----+------+------+------+
|  1 |    2 |    1 |   10 |   <- i1 changed
|  2 |    5 |    1 |    1 |   <- i1 changed
|  3 |    7 |    8 |    2 |   <- both i1 and i2 changed
|  4 |    1 |    1 |    3 |
|  5 |    1 |    1 |    5 |
+----+------+------+------+

I am pretty sure - it can be done with only one sql command.


texts: Merge replication throws PRIMARY KEY violation during the initialization of Subscriber

texts:
I am using SQL Server 2014 SP1 to set up merge replication on Windows 2008. The databases (Publisher & Subscriber) are restored from the production back up of SQL Server 2005 publisher database.

The merge replication set up on SQL Server 2005 works fine. However when I set up the merge replication on SQL server 2014, I am getting the following error.

If I add new tables, there replication seems working fine. The issue is occurring only for the existing tables with production data.

Error messages:

  The merge process was unable to deliver the snapshot to the
  Subscriber. If using Web synchronization, the merge process may have
  been unable to create or write to the message file. When
  troubleshooting, restart the synchronization with verbose history
  logging and specify an output file to which to write. (Source:
  MSSQL_REPL, Error number: MSSQL_REPL-2147201001)

  The process could not bulk copy into table '"dbo"."FiscalVisitType"'.
  (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)

  Batch send failed (Source: MSSQLServer, Error number: 0)

  Violation of PRIMARY KEY constraint 'PK_FiscalVisitType'. Cannot
  insert duplicate key in object 'dbo.FiscalVisitType'. The duplicate
  key value is (1). (Source: MSSQLServer, Error number: 2627)

  To obtain an error file with details on the errors encountered when
  initializing the subscribing table, execute the bcp command that
  appears below. Consult the BOL for more information on the bcp utility
  and its supported options. (Source: MSSQLServer, Error number: 20253)

  bcp "SIPG_Subscriber"."dbo"."FiscalVisitType" in "C:\Program
  Files\Microsoft SQL
  Server\MSSQL12.OGIS\MSSQL\ReplData\unc\PC39606-ORG-D-P$OGIS_SIPG_PUBLISHER_OGIS_PUBLICATION\20150624142806\FiscalVisitType_2.bcp" -e "errorfile" -t"" -r"" -m10000 -SPC39606-ORG-D-P\OGIS -T -w (Source: MSSQLServer, Error number: 20253)


texts:
I'm coding something in PL/SQL for the first time and have come up with a situation.

We are building a package to handle insertion tasks but it's supposed to handle the check violations instead of pre-checking the inserted data.

The thing is, check violations in Oracle return the same error code (-02290) and I want to display a relevant message regarding each different violation.

I tried parsing the SQLERRM with an IF THEN ELSEIF structure, but it doesn't seem to catch the other messages. Example:

EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
     RAISE_APPLICATION_ERROR(-20001, 'Data already in table. Please check again.');
  WHEN OTHERS THEN
  BEGIN
  IF (SQLCODE = -02290) THEN
    IF (SQLERRM LIKE '%SIZE%')
    THEN
      RAISE_APPLICATION_ERROR(-20002, 'Size too large for product');
    ELSE
      RAISE_APPLICATION_ERROR(-20003, 'Other dimensions are not coherent');
    END IF;
  END IF;
  END;

Second message never gets shown. Is there a mistake in my checking?


texts: Commit vs Fast Commit vs Commit Cleanout in Oracle Database

texts:
I was wondering whether someone could verify my understanding regarding the differences between those 3 terms as pertaining to Oracle Databases.

Many sources confuse these terms and do not explain them in detail so it was a bit of a challenge to find info.

From what I gather:

Commit and fast commit are the exact same thing, all commits are fast commits.
A fast commit essentially only updates the flag in the transaction table of the undo/rollback segment header to indicate that the transaction has committed. However, the actual block is not revisited meaning that the undo byte address (UBA) in the interested transaction list (ITL) which is located in the header of the data block still points to the transaction table of the corresponding undo segment. Furthermore, the lock bytes of the corresponding rows are not released and the lock count in the ITL is unchanged (rows still locked).
In a commit cleanout, the block is revisited and the ITL is updated with the commit SCN. However, the lock count in the ITL and the lock byte stored with each row is still not updated (row still locked just like in fast commit), this does not generate redo even though the block is changed.
Blocks which were committed normally (== fast committed) will undergo Delayed Block Cleanout when they are next touched (and generate redo).
Blocks which were underwent commit cleanout will undergo Delayed Logging Block Cleanout
when they are next touched (and generate redo).

Hopefully someone can verify these points!
Thanks!


texts: MongoDB How to query data in object with random names?

texts:
I have a collection 'news' in database as follows.

{
"_id" : ObjectId("5a6d068b0088c92a34324d8e"),
"4210003170944" :
    {
    "date" : "20180126",
    "sectionId" : "100",
    "rank" : "1",
    "newsTitle" : "Foo",
    "oid" : "421",
    "aid" : "0003170944"
    },
"2140000800903" :
    {
    "date" : "20180126",
    "sectionId" : "100",
    "rank" : "2",
    "newsTitle" : "Bar",
    "oid" : "214",
    "aid" : "0000800903"
    }
}

If I want to search all news with "oid" : "421", what command should I use?

I've tried

db.locations.find({ "_id" : ObjectId("5a6d068b0088c92a34324d8e")}, { oid : '421' })

but it doesn't fetch me anything.

Please correct me if I'm wrong.


texts: MySQL Update Multiple Rows in Large Table

texts:
I have a large InnoDB MySQL database table. I would like to run this query against the database table:

Update myBigTable Set someColumn=x WHERE userId = 7 OR userId = 88 OR userId = 22 OR userId = 99;

The exact contents of the where clause is dynamic. Through a web front end, an administrator checks one or more users who they want to update via this query.

However, when the above query is run, mysql usually returns:

#1205 - Lock wait timeout exceeded; try restarting transaction

So I'm trying to find some way to refactor my sql query so that it doesn't need to do a table lock. The 'someColumn' that is being updated and the userId both have indices defined.

I first tried:

Update myBigTable Set someColumn=x WHERE userId IN (7, 88, 22, 99);

However, the same problem persisted. I know that I can do four separate queries like:

Update myBigTable Set someColumn=x WHERE userId = 7;
Update myBigTable Set someColumn=x WHERE userId = 88;
Update myBigTable Set someColumn=x WHERE userId = 22;
Update myBigTable Set someColumn=x WHERE userId = 99;

The above approach does work, but it requires four separate queries (and in the worst case, a user could spawn 150 queries based on our current UI design - this number could grow over time as well).

What is the best practice for doing a sql update statement in MySQL Innodb to update multiple rows in a large table to the same value?


texts:
I am trying to copy CSV data into tables. Also I want to print the count.
The command I'm using is:

echo "begin; delete from a; \copy a from a.csv CSV HEADER; end;" | psql -hlocalhost -dpostgres -Upostgres

Expected Output:

BEGIN
DELETE 2
COPY 2
COMMIT

Actual Output:

BEGIN
DELETE 2
COMMIT

Any ideas on what I'm missing here?


texts: dropping index in a table that has 8 million entries

texts:
I am trying to execute the following:

DROP INDEX idx_deletedAt_isLocked_testi ON app_instagram_shop_picture;
DROP INDEX idx_id_deletedAt_isLocked ON app_instagram_shop;

currently I am using mysql 5.6 InnoDB and this is taking more than 2 hours to execute on a table which has 8 million rows. How do I speed this up? It seems when I try to run this all other processes are stuck on 'waiting table lock'


texts: How to encode a known date but unknown year?

texts:
I am using a Postgres database, and have a table with a date-time field. For many records, we know the year but not the date. Sometimes we know the date but not the year. And other times we know the time but not the date.

How can I store partial date information while using the date-time data type?


texts: Sql Server Replication requires the actual server name to make a connection to the server

texts:
I get the following message when I want to create a Remote Distributor.
First i delete my whole configuration of replication then after i take a first step to create new replication and i get below error for creating a Distributor....

I already do this but still same error........

sp_dropserver 'NODE-XYZ';
GO
sp_addserver 'NODE-XYZ', local;
GO

when run this command to get actual name then i get 'NULL' value

SELECT @@SERVERNAME
GO


texts: Cassandra cqlsh browser queries possible only in bytes

texts:
I am using Cassandra database through cqlsh client on Linux. I can make only queries with values given as bytes, but I would like to search with the client like in SQL where everything is Strings.

Should I use assume? Still using it I can only change output, but not queries.


texts: How to reject unvalidated SSL client connections from the MongoDB server?

texts: I have setup a MongoDB server with a self-signed server certificate, and when I use the server validation method in Compass along with the CA certificate, it works well. However, I am still able to connect to the database if I select the “unvalidated” option in Compass, and I don’t want that. I want to reject any client connections that are not validating the server certificate. Is it possible to setup the server to do that? I have requireTLS setup in the config file, but Compass still lets unvalidated connections through.


texts: Could someone recommend a db management tool that supports SQLite, MySQL, PostgreSQL, and is web-based? By 'web-based' I just mean it can be run in a browser locally or remotely.


texts: Does Oracle server installation include client components?

texts: I installed Oracle personal edition on a Windows Server 2008 virtual machine for testing purposes. My intention is to test Linked server functionality with SQL Server. Does Oracle Server installation include also client components?


texts: Which is better MySQL search strategy, varchar x 2 or text column?

texts:
I have a MySQL database for an SMS application. For the sms_msg column, I initially setup varchar(255) expecting SMS to be short anyway. Now I realize that's not enough and some messages are getting truncated.

I can simply convert the column to text type, but my next issue is when a user searches using keyword for an SMS message the database will have to run search on a text type column through millions of rows in the future, and that could be very slow.

So my strategy is to have three varchar(255) columns to support the overflow of characters like:

sms_msg_1
sms_msg_2
sms_msg_3

I'll just create logic where "if message is over 255 characters, split it and save to the extra columns." And so when I do a search I'll just do a

SELECT *
FROM sms_messages
WHERE sms_msg_1 = '%dog%' OR sms_msg_2 = '%dog%' OR sms_msg_3 = '%dog%'

Is this an "OK" strategy or am I better off, performance wise, just using a single text column, specially if there's not much performance difference anyway?


texts: Why is my database restore being aborted?

texts:
I'm trying to restore a SQL Server 2016 database into a SQL Server 2017 docker instance (microsoft/mssql-server-linux:latest). The process seems to be:

Create docker container: docker run -e ‘ACCEPT_EULA=Y’ -e ‘SA_PASSWORD=<strong password>’ -p 1433:1433 --name 'sql1' microsoft/mssql-server-linux
Create backup directory: docker exec -it sql1 mkdir /var/opt/mssql/backup
Copy backup to directory: docker cp Foobar.bak sql1:/var/opt/mssql/backup
Login to mssql server: mssql -o 1433 -u sa -p
Get list of backed up files: restore filelistonly from disk='/var/opt/mssql/backup/Foobar.bak' <- each row returned will result in a move command in the next step
Restore database: restore database Foobar from disk='/var/opt/mssql/backup/Foobar.bak' with stats=10, replace, move 'Foobar' to '/var/opt/mssql/data/Foobar.mdf', move 'Foobar_log' to '/var/opt/mssql/data/Foobar.ldf'

The last step is failing with the output:

  Error: The backup or restore was aborted.

I tried to find an error log, and found /var/opt/mssql/log/errorlog. AFAICT, nothing gets output in this file from my aborted restore operation.

How can I determine what is happening? Are there logs available that I can use to get more info?


texts: postgreSQL: FATAL: password authentication failed for user

texts:
Trying to connect to postgresql on MAC OS (psql -U postgres) , I receive:

  FATAL:  password authentication failed for user "postgres"

And I cant change password.
In pg_hba.conf:

 local   all             all                                     trust

I also tried:

  local   all             postgres                               trust

and

 local   all             all           127.0.0.1/32              trust

After each change I restarted app by

 brew services restart postgres

But nothing is working!


texts:
Just wondering if this query could be optimized or if sub-queries would be better?

select t.entry_id,
t.title,
d.field_id_249 as unique_id,
d.field_id_577 as keywords,
d.field_id_681 AS video,
GROUP_CONCAT(DISTINCT CASE WHEN sub.parent_id = 0 THEN sub.cat_name ELSE NULL END SEPARATOR ",") as subject_name,
GROUP_CONCAT(DISTINCT CASE WHEN sub.parent_id = 0 THEN sub.cat_url_title ELSE NULL END SEPARATOR ",") as subject_url,
GROUP_CONCAT(DISTINCT CASE WHEN sub.parent_id = 0 THEN sub.cat_order ELSE NULL END SEPARATOR ",") as subject_order,
GROUP_CONCAT(DISTINCT CASE WHEN sub.parent_id != 0 THEN sub.cat_name ELSE NULL END SEPARATOR ",") as topic_name,
GROUP_CONCAT(DISTINCT CASE WHEN sub.parent_id != 0 THEN sub.cat_url_title ELSE NULL END SEPARATOR ",") as topic_url,
GROUP_CONCAT(DISTINCT CASE WHEN sub.parent_id != 0 THEN sub.cat_order ELSE NULL END SEPARATOR ",") as topic_order,
GROUP_CONCAT(DISTINCT CASE WHEN sw2.status = "open" THEN sw2.title ELSE NULL END SEPARATOR ",") as software_name,
GROUP_CONCAT(DISTINCT CASE WHEN sw2.status = "open" THEN sw2.url_title ELSE NULL END SEPARATOR ",") as software_url,
GROUP_CONCAT(DISTINCT CASE WHEN sw2.status = "open" THEN sw3.field_id_166 ELSE NULL END SEPARATOR ",") as software_icon,
GROUP_CONCAT(DISTINCT grade.cat_description SEPARATOR ",") as grades,
GROUP_CONCAT(DISTINCT CASE WHEN ab.grade_code != "" THEN ab.grade_code ELSE NULL END SEPARATOR ",") as ab_grade,
GROUP_CONCAT(DISTINCT ab.state SEPARATOR ",") as ab_state,
GROUP_CONCAT(DISTINCT ab.state_id SEPARATOR ",") as ab_standards
from exp_channel_titles AS t
inner join exp_playa_relationships as p on (t.entry_id = p.child_entry_id)
left join exp_channel_data as d on (t.entry_id = d.entry_id)
left join api_ab AS ab on (ab.unique_id = d.field_id_249)
left join exp_category_posts AS cp on (cp.entry_id = p.parent_entry_id)
left join exp_categories AS sub on (sub.cat_id = cp.cat_id)
left join exp_playa_relationships AS sw1 on (sw1.parent_entry_id = t.entry_id)
left join exp_channel_titles AS sw2 on (sw2.entry_id = sw1.child_entry_id)
left join exp_channel_data AS sw3 on (sw2.entry_id = sw3.entry_id)
left join exp_category_posts AS gr on (t.entry_id = gr.entry_id)
left join exp_categories AS grade on (grade.cat_id = gr.cat_id)
where `p`.`parent_field_id` = 678 AND `t`.`status` = 'open' AND FIND_IN_SET("animal", d.field_id_577) OR `t`.`title` LIKE '%animal%'
group by p.child_entry_id


texts: Does SentryOne Plan Explorer count the reads in UDFs?

texts:
I have a query like this:

select dbo.fn_complexFunction(t.id)
from mytable t

In SQL Sentry Plan Explorer, I noticed I have to run Get Estimated Plan to make the Query Plan include the UDF.

When running 'Get Actual Plan', it doesn't appear that the logical reads and other metrics include the operations that occur in the UDF. In cases like this, is the only workaround to use Profiler?


texts: Monitor database every hour to prevent data loss

texts:
Here is my requirement, though I'm aware what could possibly be done to fulfill it but still looking out for the best solution to work out with.

Few days ago someone(some hacker or some previous developer) had truncated my important table causing inconvenience to my customers. I need to change my database password, server's password and other application's password in order to avoid such obnoxious actions and prevent my application.

Additionally first I need to know if this kind of activity is being performed with my data before my customers so that I can find out any work around for them. This is what I thought till now-

I should create a window service which will run in every 1 hour and check in all tables or most importantly in main tables that # of records are as expected or not. If not then an alert will be sent to me, so that I can quickly fix that.
I should create an SQL job which will perform same steps that I mentioned in first point.

These workarounds seems fine and workable. but I'm really curious to know what else can I do to prevent my data or alert me in case of data lost. I'm sure there are other efficient and more feasible way to achieve what I want. It would be great if anyone can help me around.

This is a web application and only authenticated users can login. My SQL Server edition is Enterprise.


texts: How to retrieve a view column derivation in PostgreSQL or Greenplum

texts:
I want to retrieve the derivation expression of a column in a view, and I want to do it dynamically from within a stored procedure, so psql \d+ or pgAdmin is no use to me.

Where can I retrieve this information from? The full text is in pg_views but I'd rather not get into slicing this string up to try and find each of the column expressions.


texts: MySQL Query missing from the slow query log

texts:
I've been looking at queries creating many temporary tables in the performance_schema.events_statements_summary_by_digest.

FROM performance_schema.events_statements_summary_by_digest
where SUM_CREATED_TMP_TABLES > 0 and digest is not NULL
order by SUM_CREATED_TMP_TABLES desc limit 1\G

I have a clear worst offender:

                 COUNT_STAR: 76703
             SUM_TIMER_WAIT: 96029343223000
             MIN_TIMER_WAIT: 631886000
             AVG_TIMER_WAIT: 1251963000
             MAX_TIMER_WAIT: 19597013000
              SUM_LOCK_TIME: 14475751000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 1125890
          SUM_ROWS_EXAMINED: 29941322
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 76742
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 76754
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2019-09-25 00:08:30
                  LAST_SEEN: 2019-09-25 11:22:49

If my understanding is correct.  The above suggests the query has been executed 76,703 times since 2019-09-25 00:08:30 and created 76,754 temporary tables.  Implicitly.

What I don't understand is that if I set the long_query_time to 0 the query does not show up in the slow query log.
I thought if the long_query_time was set to 0 all queries are written to the log.
Thanks in advance.


texts: SQL Server - Grid Export, choose delimiter

texts:
I'm trying to find the fastest way to actually get SQL Server query results to Excel, with the least possible trouble.

Frankly the query > text is a bit annoying, there's a "9393 results found" at the bottom. There's also a character limit of 8,000.

The query > grid looks nice, but it either exports (copy>paste) with no delimiter whatsoever, or defaults to commas with (save as) - which doesn't work for data containing natural commas.

Is there any way to do grid export > with semicolons or custom deliminator?

Not sure why SQL Server likes to make this as annoying as possible, there are many MySQL clients that make this simple.


texts:
I have the following table: http://pastebin.com/XH6FvhZ8

and the following query:

SELECT *
FROM (SELECT *
      FROM `job_logs`
      ORDER BY id DESC) AS job_logs_tmp
WHERE (job_logs_tmp.user_id = ? AND job_logs_tmp.workflow_state = ?)
GROUP BY chain, subchain, job_type

here is the explain of this query: http://pastebin.com/AjXgzRBA

this query usually takes over 4K ms to execute, any ideas how I can speed the query execution? I have no problem adding indexes to table if needed

thanks!


texts: Why do I need witness in WSFC with two nodes?

texts:
I have Windows Server 2019 and SQL Server 2019 running in both nodes of my cluster. In my case everything is working fine without witness. I need Windows Server Failover Clustering (WSFC) for availability group which I'm using as DR solution.

When both nodes are running, I'm shutting down one node (which has primary database of my AG) and do failover of Availability group from another and vice-versa.  Can someone please explain why we need a witness in two node failover cluster or how can I recreate that scenario in which we'll need a witness.


texts: How large space will be occupied by mysql for a varchar utf8 column?

texts:
Tip: To save space with UTF-8, use VARCHAR instead of CHAR. Otherwise, MySQL must reserve three bytes for each character in a CHAR CHARACTER SET utf8 column because that is the maximum possible length. For example, MySQL must reserve 30 bytes for a CHAR(10) CHARACTER SET utf8 column.

From:http://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8.html

Does this mean if I use a varchar utf8 column other than char utf8 column in mysql, mysql will physically store characters using 1 byte for latin  and 3 bytes for CJK(Chinese、japanese、Korean) ？


texts:
I have a situation where I need to assign a unique "ID" for every unique address. Each row contains other columns as well, besides the "address" columns.
My query is:
SELECT umLocationID,
       arcs_name1,
       arcs_name2,
       arcs_addr1,
       arcs_addr2,
       arcs_city,
       arcs_state,
       arcs_zip,
       arcs_country,
       arcs_cust_class,
       RANK() OVER(PARTITION BY arcs_name1,
                                arcs_name2,
                                arcs_addr1,
                                arcs_addr2,
                                arcs_city,
                                arcs_state,
                                arcs_zip,
                                arcs_country
                   ORDER BY arcs_name1,
                            arcs_name2,
                            arcs_addr1,
                            arcs_addr2,
                            arcs_city,
                            arcs_state,
                            arcs_zip,
                            arcs_country) AS 'Rank_Address#',
       DENSE_RANK() OVER(PARTITION BY arcs_name1,
                                      arcs_name2,
                                      arcs_addr1,
                                      arcs_addr2,
                                      arcs_city,
                                      arcs_state,
                                      arcs_zip,
                                      arcs_country
                         ORDER BY arcs_name1,
                                  arcs_name2,
                                  arcs_addr1,
                                  arcs_addr2,
                                  arcs_city,
                                  arcs_state,
                                  arcs_zip,
                                  arcs_country) AS 'Dense_Rank_Address#'
  FROM #Cleanout_artstcid2

Both my RANK() and DENSE_RANK() are giving the same result, a value of 1 for every row of the query results. What am I doing wrong? I know there is a need to use DISTINCT with DENSE_RANK but I cannot use it given the requirements for my overall query. Here is a screenshot of what I am getting (partial columns).


texts: Postgres: permission denied for relation for read_only user

texts:
I am trying to set up a new read only user in postgres. I did the following steps to create the new user:

sudo -u postgres createuser --interactive
    name: read_only_user
    superuser: n
    create database: n
    Create new roles: n

Then I logged into postgres with the postgres user and executed the following commands:

ALTER USER read_only_user WITH PASSWORD '123456';
GRANT CONNECT ON DATABASE myDB TO read_only_user;
GRANT USAGE ON SCHEMA public TO read_only_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO read_only_user;

But if I execute the following command:

SELECT * FROM mytable;

I get the follwing error:

ERROR:  permission denied for relation mytable

When logged in with the postgres account I can view the following settings - but I do not see what is missing. I checked a lot of other Questions/Answer here but could not find the reason for the missing permission.

List of roles:

Role name        |                         Attributes                         | Member of
-----------------+------------------------------------------------------------+-----------
 some_user       | Superuser, Create role, Create DB                          | {}
 read_only_user  |                                                            | {}
 postgres        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

List of databases:

   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 myDB      | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/postgres         +
           |          |          |            |            | postgres=CTc/postgres+
           |          |          |            |            | read_only_user=c/postgres
[...]

List of schemas:

  Name  |  Owner   |  Access privileges         |      Description
--------+----------+----------------------------+------------------------
 public | postgres | postgres=UC/postgres+      | standard public schema
        |          | =UC/postgres+              |
        |          | read_only_user=U/postgres  |

Access privileges:

 Schema |       Name        |   Type   |     Access privileges           | Column privileges | Policies
--------+-------------------+----------+---------------------------------+-------------------+----------
 public | mytable           | table    | postgres=arwdDxt/postgres+      |                   |
        |                   |          | read_only_user=r/postgres       |                   |
 public | mytable_id_seq    | sequence | postgres=rwU/postgres+          |                   |
        |                   |          | read_only_user=rU/postgres      |                   |


texts: Where can I find a list of CheckIDs for sp_Blitz?

texts:
We have a home-grown system of monitors and processes that generate events which are then gathered into a central instance and are sent as daily summary reports to operational DBAs via SSRS.  It reports on anything from non-standard configuration, missing backups, disabled jobs that shouldn't be, databases in bad states, mirroring and AG issues, audit related issues (SOX), etc.

One of the things I am currently doing is writing a process that runs sp_Blitz on a weekly basis and generates events based on some of the findings.

Brent Ozar mentions that an Excel file with all of the possible CheckIDs is contained in the First Responder Kit download, and I can't find it in there.  There is also a link on the sp_Blitz page that is supposed to link to it, but it points to a non-existent page on GitHub (I get a 404).

Does anyone know where I can find this document that contains every possible CheckID and its description?


texts: merge multiple fields of csv file while running mysqlimport command

texts:
I have a mysql table like this

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| roll_no        | varchar(50)  | NO   | MUL | NULL    |                |
| marks          | varchar(100) | YES  |     | NULL    |                |
| academy        | varchar(100) | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

the marks column can have multiple numbers separated by comma like this

45,67,78,80, # First Example
34,56,  # Second Example

I have to run mysqlimport command such that the fields after the first fields till second-last fields should merge into marks column

I tried something like this

mysqlimport --fields-terminated-by=, --columns='roll_no, marks, academy' --ignore --local -u root -pxxxx result results.csv

But extra fields (after 3rd one) gets truncated. How to handle such scenarios where the csv fields are dynamic


texts: Sync of Master Master replication when Primary node comes up MySQL 5.7 Enterprise

texts:
I have a Master (say Node A) - Master (Say Node B) asynchronous replication setup with ROW based logging method.

Node A has been setup as Write/Read node and Node B has been setup as Read Node Only.

From JBOSS , i am doing switch over that , when A node goes down , all the Write operation will get forward to B node and whenever Node A comes up , JBOSS will forward all the Writes to A node.

Now suppose Node A remains down for long time , then all the Writes will go to B node . When A node comes up , it will remain un-sync for a while till it get complete sync with B . While it is getting sync , write operation is also happening on Node A which might result in locking issues as well.

So my question is :

Is there any method in MySQL that when Node A comes up , it will not be accessible by outer application ( like JBOSS , i have mentioned) till it gets complete sync with Node B .
Can we control it via MySQL_Safe mode so that other application can perceive Node A as still down or is there any other method


texts:
I'm not a database guy but I need to design a database to store events for our application. Our client app connects to remote hardware and syncs count data. The count data are csv files split into /date/guid.cvs. The sync part is done but now I need to take the synced files and dump them into a database for a reporting system. This all gets stored on a client's computer so I need a file database for ease of use. I've been using sqlite.

Now for my question. Each row in the csv file is an event with data defined:

Timestamp, vehicle length (single), controller state (int), turn type (int), queue length (int), other ints defining the event

I was also planning to include the guid from the csv filename. The problem I see is that this single table will have many, many rows. Just on a small amount of data (couple days) I already have over 100k rows. This will need to handle potentially years worth of events.

Is there a good way to reduce the row amount or is it not going to be an issue? Am I missing some way to design the table(s) so they don't grow so large? I saw something about partitioning but I'm not sure sqlite supports that. Is there another file database that does? I'm using c# 4 as the front end for the client.


texts: Firebird Configuration: firebird.conf Parameters Unit of Measurement

texts: Many of the parameters in the firebird.conf configuration file are integers and I'm unsure what unit of measurement (KB, Byte etc.) is being used. For example, if tweaking the TempCacheLimit param, is the value in kilobytes or bytes (or a different unit)?


texts: Calculate Percentage with JOINS and original value

texts:
I need your help. I'm trying to calculate percentages by using results from JOINING divided by the original value. I do not know how to combine both values. I try to find trips with station id which do not exist in the table station and then find the percentage of that trips.
I only can write the query to this extend:
SELECT COUNT(*)
FROM (SELECT a.start_station_id, b.station_id, a.end_station_id,a.bikeid
FROM `bigquery-public-data.new_york_citibike.citibike_trips` as a
LEFT OUTER JOIN  `bigquery-public-data.new_york_citibike.citibike_stations` as b
ON a.start_station_id = b.station_id
WHERE b.station_id IS NULL)

I need to divide the result COUNT from the query above with COUNT(*) before JOIN.
Kindly help me with this.
Thank you!!


texts: Trick to remember which instance I am working with

texts:
I work as a BI developer and always switch between different instances on a same server. However, sometimes I forget the right instance and for example may cause to truncate a table on the other.
I know this is not a technical question but rather experimental. I would be so thankful if you , dear DBAs, could tell me what do you do to always work on the right instance?


texts:
We have EMC CLARiiON disks that are near their end of support, so we need to move Oracle ASM disks to a new sets disks.  We are trying to minimize the database down time, so I am trying to find ways to use ASM mirroring or disk clone method to move current databases on ASM old disks to new ones.

If our databases were on the file system, we could use the disk mirror/clone to copy the data files on the file system to new disks.  We could then open the database on new disks with ease.  But Oracle ASM disks are different than file systems.

Any suggestions or references will be very much appreciated.


texts: Performance comparison in ways a SSIS package can be run

texts:
I'm wondering if there is any difference in performance of a SSIS package if it runs using SQL Server job, vs running using DTEXECUI.EXE Utility or running in BIDS?

Also if the target database in on the server but package is running on a local machine, would it be using the memory on the server or the local machine?

Thanks in advance.

Cheers,
Nazila


texts:
I'm running a Failover cluster on windows 2016 servers (2 nodes) where I set up an AG in SQL 14.0.2027.2
This works good and I'm happy. But when it failover I need to fix my logins so I need to run a query. It doesn't happen often but when it happens we are always too late-running this script. So is there a good way of running this script automatically right after failover and just send us one e-mail that the node's failover?


texts:
My result of the request of group by is :

ref       state       quantity     total
PO3253    assinged    300.0        323400
PO3253    done        300.0        323400

When I delete state from group by using:

SELECT   ref, sum(quantity),sum(price*quantity)  as total
FROM     myTable
GROUP BY ref

I get:

ref       quantity     total
PO3253    600.0        646800

I want the result to be:

ref       quantity     total
PO3253    300.0        323400

I want to do something like this operation :

SELECT   ref, sum(quantity)/count(*),sum(price* sum(quantity) / count(*))
FROM     myTable
GROUP BY ref


texts: date aggregation for average temperatures from 2 different tables

texts:
I have two tables with temperatures,
if I use this query:

SET @odd:='2018-10-28';  SET @doo:='2018-11-03';

( SELECT
    DATE_FORMAT(T1.temp_data_dodania, '%Y-%m-%d') as D1,
    ROUND(avg(T1.temp_t1), 1) as T01_01,
    ROUND(avg(T1.temp_t2), 1) as T01_02,
    ROUND(avg(T1.temp_t3), 1) as T01_03,
    ROUND(avg(T1.temp_t4), 1) as T01_04,
    ROUND(avg(T1.temp_t5), 1) as T01_05,
    '' as T02_01,
    '' as T02_02,
    '' as T02_03,
    '' as T02_04,
    '' as T02_05
FROM temp.temp1 T1
WHERE T1.temp_data_dodania
   BETWEEN @odd AND @doo
GROUP BY D1 )

UNION

( SELECT
    DATE_FORMAT(T2.temp_data_dodania,'%Y-%m-%d') as D2,
    '',
    '',
    '',
    '',
    '',
    ROUND(avg(T2.temp_t1), 1),
    ROUND(avg(T2.temp_t2), 1),
    ROUND(avg(T2.temp_t3), 1),
    ROUND(avg(T2.temp_t4), 1),
    ROUND(avg(T2.temp_t5), 1)
 FROM temp.temp2 T2
WHERE T2.temp_data_dodania
   BETWEEN @odd AND @doo
GROUP BY D2 ) ;

i get the correct data

but ...

how to aggregate this data into one line with a date?

thank you in advance for your help
Tadziu.


texts:
I need to get daily reports for an app.

A report should tell me how many times a event page was visited and how many tickets were sold for each category. All that should be grouped by day in a single row, like so:

Date        | Category A | Category B | Category C | Visits
'2017-03-10'|         10 |          2 |          8 |     30

Using a sequence table I was able to group results by day, but the best I've been able to do is to have a row for each category for each day, ie, if I have 3 categories, I get 3 rows for each day.

Fiddle


texts: SQL Server Linked Servers and Remote Statistics

texts:
I'm trying to get a better understanding of how SQL Server uses statistics on remote SQL Servers (e.g. a Linked Server). I understand that the user needs db_owner/db_ddladmin rights to be able to use the stats on the remote box. At first this seems really straight forward based on the below text from Microsoft.

  To create the best query plans when you are using a table on a linked server, the query processor must have data distribution statistics from the linked server. Users that have limited permissions on any columns of the table might not have sufficient permissions to obtain all the useful statistics, and might receive a less efficient query plan and experience poor performance. If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server.

The pieces that is confusing me is the word "User". We use a standard security context for all connections rather than the user's login. This login/user has the needed permissions as defined above over the databases in question. Additionally, we do not use Impersonation.

I have tried running queries and capturing the traces on both ends using accounts with and without elevated permissions on the remove server. It looks to me like the remote server is not collecting statistics unless the actual user (the guy at the keyboard) has the elevated rights. I believe this to be true because I am only seeing calls to sys.sp_table_statistics2_rowset when the user has the needed rights.

I really hope this makes sense.

Does anyone have an understanding of how this really works in SQL 2008 R2 and below?


texts: Enforce referential integrity between two groups of data

texts:
I have two sets of records:

A collection of rules.
A collection of actions to perform based on those rules.

What I'm trying to model is something like this:

Multiple rules can be grouped together to form a composite rule.
Multiple actions can be grouped together to form a composite action.
When a rule(set) matches the input the corresponding action(s) should be performed.

Both the set of rules and the set of actions change very infrequently (once a year, if that). Because the data is pretty much read-only I came up with the following design.

A short note:
I explictly avoided adding extra lookup tables to define the group relationships, in the hope that doing so would both simplify and speed up my SELECT query (it will be run often). I'm open to the suggesting of adding them, but even with them in a design I'm still struggling to get the referential consistency I'm after:

CREATE TABLE Rules
(
  ID INT NOT NULL IDENTITY(1,1),
  GroupID INT NOT NULL,

  ... columns required by a rule

  CONSTRAINT PK_Rules_ID PRIMARY KEY (ID),
  CONSTRAINT UK_Rules UNIQUE(GroupID, other columns...)
)

CREATE TABLE Actions
(
  ID INT NOT NULL IDENTITY(1,1),
  GroupID INT NOT NULL,

  ... columns required by an action

  CONSTRAINT PK_Actions_ID PRIMARY KEY (ID),
  CONSTRAINT UK_Actions UNIQUE(GroupID, other columns...)
)

CREATE TABLE RuleToActionMappings
(
  RuleGroup_ID INT NOT NULL,
  ActionGroup_ID INT NOT NULL

  -- Note: These constrains are invalid, but I wished they weren't!
  CONSTRAINT FK_RTAM_RuleGroupID_ON_Rules_ID FOREIGN KEY (RuleGroup_ID) REFERENCES Rules(GroupID) ON DELETE CASCADE,
  CONSTRAINT FK_RTAM_ActionGroupID_ON_Actions_ID FOREIGN KEY (ActionGroup_ID) REFERENCES Actions(GroupID) ON DELETE CASCADE
)

In which I expect to insert multiple entries into both the Rules and Actions tables grouped by a common GroupID value. I could later query these tables in the following manner:

SELECT Actions.* FROM Actions
JOIN RuleToActionMappings As M ON M.ActionGroup_ID = Actions.GroupID
JOIN Rules ON M.RuleGroup_ID = Rules.GroupID
WHERE Rules.SomeColumn = 'SomeValue'

The problem with this design is there's no way to place the foreign key constraints I want/need on the RuleToActionMappings table, and the Rules and Actions tables are relying on a convention rather than something more concrete to ensure uniqueness.


texts:
I'm trying to do something like:

INSERT INTO stories
   (vid, ip, body, timestamp)
VALUES (
   (SELECT vid+1 FROM stories ORDER BY vid DESC LIMIT 1),
   INET_ATON('127.0.0.1'),
   'test',
   NOW()
)

but I'm having a problem figuring out how to nest a SELECT statement's returned data into an INSERT statement. I need to figure out how to get the highest existing vid and increment it by 1.

Any ideas?


texts:
I recently moved my Temp database files onto a separate drive (I had two data files and one log file).

However, after restarting the server, my second data file disappeared both from the file list on the gui and from sp_helpfile. I've managed to reproduce this behaviour several times: I add a new data file, see it appear both in the gui and sp_helpfile, and disappear after rebooting the server.

I've run dbcc checkdb against the database and it returns no errors. There's no information either in the SQL logs or the windows application event log.

After doing a little more research, I ran the following query:

select d.name, m.* from sys.master_files m
inner join sys.databases d
on (m.database_id = d.database_id)
where d.name like 'tempdb'
order by 1, 2

Results: http://pastebin.com/Zu5fJ2hU

It shows four different secondary data files, none of which appear in sp_helpfile. If I try to use any of those names SQL server doesn't allow me to. None of these files physically exist on the disk. My server is SQL 2012 Standard.


texts: Is it acceptable to use the presence of a value in a NULLable column as a flag?

texts:
I have a stored procedure that processes rows in a table.  The stored procedure is called by an external process (Jitterbit, though I don't believe that is relevant).  Occasionally the stored procedure throws an exception wchich is caught in a TRY CATCH block.  My client wants this to work slightly differently; they want the error to be written back to the table (e.g. create a new column for the error information) which can then be periodically aggregated into a notification email.  There will be another bit column (DEFAULT 0) that will be set to 1 when an error in that row is included in a notification email (so that the email is sent only once).

My question is this.  If I make the column for the error message NULLable, and therefore do something like:

SELECT * FROM MyTable WHERE ErrorMessage IS NOT NULL AND EmailSent = 0;

is this an acceptable approach - to use the presence (or absence) of data in a column as a flag?  My inner pedant thinks that another BIT column ought to be used - for example:

SELECT * FROM MyTable WHERE ErrorCreated = 1 AND EmailSent = 0;

Am I being pedantic?


texts: Help performance tuning master/detail (email like inbox) SQL query

texts:
I have spent the last couple days searching, watching videos, and I think I've gotten as far as I can get just fumbling my way through. I am looking for more specific direction given my example below.

I have two tables I'm working with. MessageThreads (400k records) & Messages (1M records). Their schemas are shown below.

MessageThreads indexes

https://gist.github.com/timgabrhel/0a9ff88160ebc9e40559e1e10ecc7ee4

Messages indexes

https://gist.github.com/timgabrhel/d649074cbe82016e8a90f918c58c4764

I am trying to improve the performance of our primary "inbox" query. Think of your email provider's inbox. You'll see a list of threads, some new, some read, sorted by date, and also gives you a preview of the most recently sent message, whether it was to or from you. Finally, there is an element of paging on this query. By default, we want 11 items. 10 for the page to display, and +1 to know if there's more on the next page.

For some of our long time users, they can have up to 40K messages.

This query has seen many different forms over the last few days, but this is where I've gotten to. I've given OUTER APPLY a try, but I'm seeing worse execution time & statistics.

SET STATISTICS IO ON; /* And turn on the Actual Excecution Plan */

declare @UserId bigint
set @UserId = 9999

; WITH cte AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY SendDate DESC) AS RowNum,
        MT.MessageThreadId,
        MT.FromUserHasArchived,
        MT.ToUserHasArchived,
        MT.Created,
        MT.ThreadStartedBy,
        MT.ThreadSentTo,
        MT.[Subject],
        MT.CanReply,
        MT.FromUserDeleted,
        MT.ToUserDeleted,
        LM.MessageId,
        LM.Deleted,
        LM.FromUserId,
        LM.ToUserId,
        LM.[Message],
        LM.SendDate,
        LM.ReadDate
    FROM MessageThreads MT
    -- join the most recent non-deleted message where this user is the sender or receiver
    LEFT OUTER JOIN
    (
        SELECT RANK() OVER (PARTITION BY MessageThreadId ORDER BY SendDate DESC) r, *
        FROM [Messages]
        WHERE (FromUserId=@UserId OR ToUserId=@UserId)
        AND (Deleted=0)
    ) LM ON (LM.MessageThreadId = MT.MessageThreadId AND LM.r = 1)
    --WHERE MT.ThreadSentTo=@UserId OR MT.ThreadStartedBy=@UserId
)
SELECT
    cte.*,
    UserFrom.FirstName AS UserFromFirstName,
    UserFrom.LastName AS UserFromLastName,
    UserFrom.Email AS UserFromEmail,
    UserTo.FirstName AS UserToFirstName,
    UserTo.LastName AS UserToLastName,
    UserTo.Email AS UserToEmail
FROM cte
LEFT OUTER JOIN Users AS UserFrom ON cte.FromUserId=UserFrom.UserId
LEFT OUTER JOIN Users AS UserTo ON cte.ToUserId=UserTo.UserId
WHERE RowNum >= 1
AND RowNum <= 11
ORDER BY RowNum ASC

Statistics for above query (execution time ~2 seconds in SSMS). This execution time is acceptable, but the statistics feel less than desirable, and even more so upon reviewing the actual execution plan.

The execution plan is linked here
https://gist.github.com/timgabrhel/f8d919d5728e965623fbd953f7a219ef

One huge hiccup I've spotted is the 400k row Index Scan on the MessageThreads table. Presumably this is because the primary SELECT X FROM MessageThreads query doesn't have a filter on it. When I apply a predicate to it (uncomment the WHERE from the query), the statistics greatly improve (below), but the time jumps from ~2 seconds to ~18 seconds in SSMS.

The problem area on the query is the MessageThreads predicate

https://gist.github.com/timgabrhel/1383ff9362567fdf41ba011dead63ceb

Thank you in advance!


texts: Why scanning an index backwards is slower?

texts:
Empirical tests show that a query like this on an InnoDB table:

SELECT indexed_column FROM tab ORDER BY indexes_column ASC;

is faster than its counterpart with ORDER BY ... DESC. Why is this the case?

Note: I did the tests with MySQL 5.7 and 5.6. So this has nothing to do with ascending indexes in 8.0.


texts:
It is my understanding that I can use the EXECUTE AS OWNER clause as part of a procedure that I create to make the body of that procedure run as a different user. My goal is to execute a command that requires the sysadmin role (DBCC TRACEON(1224)). This procedure is supposed to be called by an unprivileged user.

I ran the following script under the sa user:

SELECT USER_NAME(), USER_ID(), IsSysAdmin = IS_SRVROLEMEMBER('sysadmin')
-- dbo  1   1

IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'MyProc')
    DROP PROCEDURE MyProc

GO
CREATE PROCEDURE MyProc
WITH EXECUTE AS OWNER
AS
    SELECT USER_NAME(), USER_ID(), IsSysAdmin = IS_SRVROLEMEMBER('sysadmin');
-- dbo  1   0

    DBCC TRACEON(1224)
--Msg 2571, Level 14, State 3, Procedure MyProc, Line 7
--User 'dbo' does not have permission to run DBCC TRACEON.

RETURN
GO

EXEC MyProc

Output is inline in comments. It turns out that outside of the procedure I seem to have sysadmin membership, but not inside the procedure.

The procedure is owned by the dbo user. I understand that it is not possible to grant the sysadmin role to a database user (at least the GUI doesn't offer this possibility). So I don't see how I could ever make a database user have a server role.

I also tried EXECUTE AS 'sa' which results in Cannot execute as the user 'sa', because it does not exist or you do not have permission.. The documentation states that I can only specify a user name, not a login name. So I understand why that didn't work.

How can I run my procedure with sysadmin role membership?


texts:
I want to back up my DB. I have some huge tables in some specific FileGroups which I want to exclude them to be getting backup.
I have tried this script:

BACKUP DATABASE TestDB
   FILEGROUP = 'PRIMARY',
   FILEGROUP = 'Temp_FG',
   FILEGROUP = 'Index_FG'
TO DISK = 'E:\TestBackup\Test.bak';
GO

But I am getting this error:

The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model. Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS.

What can I do to get the backup including only these 3 FileGroups or excluding the 'Dump_FG'?

'Dump_FG' FileGroup is not ReadOnly.


texts: Registered Servers without the ability to connect to Multiple Servers at once

texts:
I'd like to provide a "directory" of all the different SQL Servers we have our different DEV, TEST, PROD environments by setting it up in Central Management Servers.

Another DBA said to be cautious of giving this to the group because there is an ability to inadvertently run a query on multiple servers IF they did a "New Query" on a group, instead of an individual server (and this would make sense why they call the feature Central Management Server).

Is there any way to provide this "directory" without having to worry about executing commands on multiple machines?


texts:
Where is the source code of the Postgres txid_current function? (docs)
I want to read the function to better understand how it handles values bigger than (2^64)/2.


texts: how can i write the max value of different consecutive values in the new column by length

texts:
i have a data frame as "a".
a:
[,1] 1  2  3  0 1 2 0 [,2] 0 0 1 2 3 0 1 [,3] 1 2 3 4 5 0 0 and dim(a)=7*3

i want to create a new column including maximum value for each set of consecutive numbers till 0. and calculate for next set till next zero. like this:
result:
[,1] 3 3  3  0 2 2 0 [,2] 0 0 3 3 3 0 1 [,3] 5 5 5 5 5 0 0

unfortunatly i couldn't put the picture of my data frame(a).
please help me to write the right function


texts: Alter stored procedure - linked server not connected

texts:
I'm trying to change a stored procedure that is used to updates data in a local and a linked server. "UPDATE [PcName].[DatabaseName].[dbo].[TableName]"

As both servers are available the stored procedure can be executed (updated) without an problem. If the linkedserver is down, I'm getting this error message:

OLE DB provider "SQLNCLI11" for linked server " linkedserver " returned message "Login timeout expired".
OLE DB provider "SQLNCLI11" for linked server "linkedserver " returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].

How can I still change a stored procedure while the linkedserver is down?


texts: Recommended hardware configuration Analysis and Reporting Services

texts:
What would be a good way to calculate the recommended minimum hardware configuration for a Windows 2008 R2 64bit that will be running SQL Server 2008 R2 Analysis and Reporting Services?  The database server is on another machine.

The current configuration is :

Virtual Machine running on a vmware7 host

2 vCPUs
4GB of RAM
80GB of Hard Disk space.

4GB of RAM appears to be insufficent.

No one has the answer to this question?  Perhaps I need to provide more information?  Is there a white paper on minimum requirement for SSAS and SSRS?

What started this question was essentially the customer saying, hay the RAM on my machine is all used up.  I checked out the processes and noticed owstimer.exe.  After a quick search I found that owstimer.exe is a sharepoint component.  I say that with some doubt because I have not verified that information.

So I have a Windows 2008 R2 64bit, 4Gb of Ram, 4vCPUs on a machine with SSAS and SSRS.  Among the processes I find one called owstimer.exe which might belong to sharepoint.  I read somewhere that sharepoint should be installed on a system with a minimum of 8Gb of RAM.

http://technet.microsoft.com/en-us/library/cc262485.aspx

But sharepoint isn't on this machine just SSAS and SSRS.

The databases for this client are stored on a dedicated SQL Server 2008 R2.

More information.  I can at limit SSRS and SSAS via their configuration files.

For SSRS

Control SSRS according to memory pressure

http://msdn.microsoft.com/en-us/library/ms159206.aspx

C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config.

MemorySafetyMargin 80 /MemorySafetyMargin -- % of memory reserved for
the system

MemoryThreshold 90 /MemoryThreshold --  If Report server memory use
reaches this value, the report server slows down request processing
and changes the amount of memory allocated to different server
applications

Min and Max Memory control in SSRS

WorkingSetMaximum 2097152 WorkingSetMaximum>
WorkingSetMinimum 1048576 WorkingSetMinimum>

So we're going to try this out on SSRS and see how it goes.


texts: Table was deleted during ALTER, how to recover?

texts:
I tried to ALTER a table to add a FOREIGN KEY as:

ALTER TABLE artists ADD
FOREIGN KEY(country_id) REFERENCES countries(country_id) ON DELETE CASCADE;

and I received an error of

ERROR 2013 (HY000): Lost connection to MySQL server during query

I tried twice and the same error. The third time, my table was gone. How can I recover the dropped table? MySQL server (Ver 14.14 Distrib 5.7.21, for Linux (x86_64)) is on localhost.

The entries in other tables which have FK to this table still exist. Thus, I believe the table has not been deleted, but InnoDB map is damaged.

I checked the files, table_name.ibd exists but table_name.frm is missing. I cannot CREATE the table as the TABLESPACE already exists. How can I recover or create the table schema to use the available data? My concern is about the FOREIGN KEYS, which still exist in other tables.

I had a backup, but it is too old to restore the backup. I have to keep the current version. And it is a huge database. The table has about 1 million rows. The ibd file is over 100MB.


texts:
How does the LENGTH() query relate to actual size of the data?
I notice that when I save this data to a text file, it's approximately 2% larger than the LENGTH() query.  What is happening here?  Here's an example on linux using MariaDB 10.3.16.
du and ls both agree`.
$ mysql -ufugu -p projects -e "SELECT ID, LENGTH(CutXML) as length FROM renders ORDER BY length DESC LIMIT 1"
Enter password:
+-----+---------+
| ID  | length  |
+-----+---------+
| 350 | 3431670 |
+-----+---------+
$ mysql -ufugu -p projects -e "SELECT CutXML FROM renders WHERE ID=350" > cut.xml
Enter password:
$ ls -al cut.xml
-rw-r--r--. 1 root root 3512937 Aug 20 14:23 cut.xml
$ du -b cut.xml
3512937 cut.xml


texts: Multilingual DB design with similar translatable fields

texts:
I am not in the process of designing the DB schema for a new project that I am working on.

So, the challenge is as follows:

There is a table Items
Each Item has a translatable description_60, description_180 text, (the suffix number stands for the type of description that is stored, e.g 60 means 60 char long) and some fields associated to each of those such as apiSourceNameetc.

There are two options that I see:

1

descriptions_translations

  Id
description_60
description_180
description_300
apiSourceName_60
apiSourceName_180
....
...

which does not look very good as we might end up with lots of NULL fields
and

2

descriptions_60_translations
  Id
description_60
apiSourceName
languageId
...
...

3 Other?

I am totally open to other recommendations!

Also, another challenge is that I want to store in the main Item table the description_60 text. Is this possible without duplicating data?


texts: Copy maintenance plans to a different server

texts: Is there a way to copy maintenance plans from one SQL Server 2012 to another? Both servers are not accessible via the same copy of Management Studio at the same time (on different networks).


texts: Postgres - Optimizing an view dependent on an aggregate function?

texts:
I have a table of about 15 million records.  Whenever information about a specific charge_id is changed, a new row is added with the current timestamp and the changes.  This results in multiple rows with the same charge_id, and related hierarchical columns.  This isn't controlled by me and can't be changed, plus we like having the history available for querying.

The view below is intended to identify the most recent entry for each charge_id, and create a simple key value pair table for joining off of.  The view works fine, but execution time is horrendous.  I've tried a couple of stabs at an index to speed things up, but each time it appears that postgres is skipping the index and scanning everything anyways.  I should also note that most queries where we're joining to this view are going to be full table aggregates, grouped by 2-5 different dimensions in the charges table.

My question specifically is what can I do to speed up execution time on this particular view?

CREATE VIEW current_charge_ids AS
(
SELECT
    c2.id,
    c2.charge_id,
    t1.last_post_date
FROM
    charges c2
LEFT JOIN
    (
        SELECT
            c1.client,
            c1.practice,
            c1.account_id,
            c1.encounter_id,
            c1.charge_id,
            max(c1.post_date) AS last_post_date
        FROM
            charges c1
        GROUP BY
            c1.client,
            c1.practice,
            c1.account_id,
            c1.encounter_id,
            c1.charge_id
    ) t1
ON
    c2.client = t1.client AND
    c2.practice = t1.practice AND
    c2.account_id = t1.account_id AND
    c2.encounter_id = t1.encounter_id AND
    c2.charge_id = t1.charge_id AND
    c2.post_date = t1.last_post_date
);


texts:
I am trying to add a slave MySQL server to an existing server. I ran the following mysqldump on the master to prepare the data for the slave initially.

mysqldump.exe -u ... -p... --single-transaction --flush-logs --skip-lock-tables --master-data=2 > all-dbs.sql

But after a while, it gave the following error;

Program 'mysqldump.exe' failed to run: Exception of type 'System.OutOfMemoryException' was thrown.At line:1 char:1
+ .\mysqldump.exe -u ... -p... --skip-lock-tables --single-tran ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~.
At line:1 char:1
+ .\mysqldump.exe -u ... -p... --skip-lock-tables --single-tran ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ResourceUnavailable: (:) [], ApplicationFailedException
    + FullyQualifiedErrorId : NativeCommandFailed

The master MySQL server has 3.5GB memory, and the data is about 5GB.
It is running MySQL 5.7.
Any help would be greatly appreciated.  Thanks.


texts:
I am not sure whether this is offtopic. However, please read on.

A person whom I know has encountered a problem in the answer key published by CBSE, India. As their key challenging form contains a column for citation/proof, we have to find some authentic book which can be shown as the base of our claim.

I attach the question here, and I request you to verify the answer and suggest us some book/other ways to prove our challenge (She has checked the regular syllabus books, but didn't find something precise related to this problem).

Question:

Identify the minimal key for relational scheme R(A, B, C, D, E) with
functional dependencies F = {A->B, B->C, AC->D}

The options are A, AE, BE, and CE

The key shows the answer is A and she says it is AE actually.

Please help us find some proof/way to challenge the key.


texts: SKU and Price on product that have multiple attributes

texts:
Currently, my Inventory system is writing products data like this:

T-Shirt Gildan -> blue, L, short -> stock: 140 -> price: $40
T-Shirt Gildan -> blue, XL, short -> stock: 0 -> price: $45
T-Shirt RedMango -> blue, L, short -> stock : 100 -> price: $30
T-Shirt RedMango -> red, L, short -> stock : 140 -> price: $30
T-Shirt RedMango -> red, L, long -> stock : 100 -> price: $35

Basically, I want to create a website like products catalog. e.g user flow:

User choose a T-Shirt Gildan product (options: T-Shirt Gildan or T-Shirt RedMango)
User choose blue color: (option: only blue)
User choose XL size: (options: L or XL, inform the user if XL size is out of stock)

To achieve that flow, I'm try to normalize above data. Then I'm to split to some tables. Here's my current work.

Table product

---------------------
id | name
---------------------
1  | T-Shirt Gildan
2  | T-Shirt RedMango

Table attr

------------
id | name
------------
1  | color
2  | size
3  | sleeve

Table attr_value

---------------------------------------
id | attr_variant_id | attr_id | value
---------------------------------------
1  |        1        |    1    | blue
2  |        1        |    2    | L
3  |        1        |    3    | short
4  |        2        |    1    | blue
5  |        2        |    2    | XL
6  |        2        |    3    | short

Table attr_variant

------------------------------------------------
id | product_id |       sku      | price | stock
------------------------------------------------
1  |     1      |  #Blue#L#Short | $40   | 140
2  |     1      | #Blue#XL#Short | $45   | 0

Is that tables enough for my user flow?


texts: Is it possible to upgrade mongodb cluster without upgrading arbiters?

texts:
Is it possible to have running real nodes with version 2.4 and arbiter nodes with 2.0 or 1.8 in the replica set?

Upgrade guides for version 2.2 and 2.4, did not tell anything about upgrading arbiters.


texts: How do I safely drop a list of tables, when i have the table names listed in a column?

texts:
Assuming I have a database column containing table names, how do I safely delete all of the tables it lists?

The tables themselves are fairly simple - they may have indeces, but definitely don't contain other complex items (and don't have dependencies on other tables - ie, foreign keys into/out of them, etc...)


texts: How does SQL Server know what index to use?

texts:
I have this non clustered index

CREATE NONCLUSTERED INDEX [nci_wi_StoreVariantInventory_F09B4BE817BC953FAC6861B7E46F6F51] ON [dbo].[StoreVariantInventory]
(
    [ProductId] ASC,
    [StoreId] ASC,
    [VariantId] ASC
)
INCLUDE (   [LocalInventory]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

And this clustered index

ALTER TABLE [dbo].[StoreVariantInventory] ADD  CONSTRAINT [PK_StoreVariantInventory] PRIMARY KEY CLUSTERED
(
    [ProductId] ASC,
    [ChainId] ASC,
    [CountryCode] ASC,
    [StoreId] ASC,
    [VariantId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

Generated for this table

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[StoreVariantInventory](
    [ProductId] [bigint] NOT NULL,
    [ChainId] [nvarchar](5) NOT NULL,
    [CountryCode] [nvarchar](2) NOT NULL,
    [StoreId] [bigint] NOT NULL,
    [VariantId] [int] NOT NULL,
    [Size] [int] NOT NULL,
    [SizeRow] [nvarchar](1) NOT NULL,
    [KoncernNumber] [nvarchar](4) NOT NULL,
    [LocalInventory] [int] NOT NULL,
    [OrderedInventory] [int] NOT NULL,
    [Price] [money] NOT NULL,
    [LastUpdated] [datetime] NOT NULL,
 CONSTRAINT [PK_StoreVariantInventory] PRIMARY KEY CLUSTERED
(
    [ProductId] ASC,
    [ChainId] ASC,
    [CountryCode] ASC,
    [StoreId] ASC,
    [VariantId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[StoreVariantInventory]  WITH CHECK ADD  CONSTRAINT [FK_StoreVariantInventory_Stores] FOREIGN KEY([StoreId])
REFERENCES [dbo].[Stores] ([StoreId])
GO

ALTER TABLE [dbo].[StoreVariantInventory] CHECK CONSTRAINT [FK_StoreVariantInventory_Stores]
GO

ALTER TABLE [dbo].[StoreVariantInventory]  WITH CHECK ADD  CONSTRAINT [FK_StoreVariantInventory_Variants] FOREIGN KEY([ProductId], [VariantId])
REFERENCES [dbo].[Variants] ([ProductId], [VariantId])
GO

ALTER TABLE [dbo].[StoreVariantInventory] CHECK CONSTRAINT [FK_StoreVariantInventory_Variants]
GO

If I do a simple select

select LocalInventory
from StoreVariantInventory
where ProductId=1219100110
and VariantId = 49
and StoreId = 9050
and ChainId = 'F'

It looks through the non clustered index

But why does it pick the non clustered index? I am doing a WHERE that doesn't fit the non clustered index 100% so how can it use it? Does SQL Server just picks what it seems a best fit, when all columns in the WHERE clause doesn't fit an index 100%? Is it bad practice not having an index like here not fitting 100% with my where clause?


texts: Questions about PostgreSQL backup/restores

texts:
I've inherited a large PostgreSQL 8.4.7 database that is currently not being backup at all.  The database itself I believe is around 100GB, I've been reading about the different methods to backup this type of DB here

My questions are:

1) Typically how fast or slow is the pg_dump dump process?  I know its a subjective question and depends on hardware and the database, but roughly 100gb can I expect this to take 1 hour, 12 hours, 24+?  I'd like to set an expectation with management and users before I begin.

2) The restore instructions indicate the following:

Before restoring a SQL dump, all the users who own objects or were granted permissions on objects in the dumped database must already exist. If they do not, then the restore will fail to recreate the objects with the original ownership and/or permissions.

Is it possible to gather this information as part of the backup?  Or is there a separate backup process for users and permissions?  Because I'm inheriting this database, Im not sure about all the users and permissions yet and would like to just backup everything for offsite storage.


texts:
I'm creating a CRM type application for a client. They need to store information about Companies and information about Contacts within those companies. Each Company can have multiple Contacts associated with them but only one of the Contacts in each Company can be the "Preferred" contact.

So I have the following two tables:

Company
PK: ID
Name
Tel
etc

Contact
PK: ID
FK: CompanyID
Name
Tel
etc

I can see two ways to add the Preferred Contact attribute. I could put a FK:PreferredContactID field on the Company table or I could put a PreferredContact boolean field on the Contact table. The former means that I would have a circular relationship the latter means that I have to have a constraint "Only one Contact within a Company can be the Preferred".

My question is which of these is the best option and why or is there a better way?


texts:
In short, are STDisjoint() and STIntersects() antipodes of each other?
According to the documentation, it seems that they are. But there is no explicit note about it.

Are there any tricks? Which one is faster, for example?


texts:
I'm striving to convert badly encoded data from my table. For instance, I have a field with NadÃ¨ge which should be Nadège.

I tried using Postgres's functions convert, convert_from, convert_to without much success.

db=# SHOW client_encoding;
 client_encoding
-----------------
 UTF8
(1 row)

db=# SHOW server_encoding;
 server_encoding
-----------------
 UTF8
(1 row)

db=# SELECT "firstName", encode("firstName"::bytea, 'hex') FROM contact;
 firstName |       encode
-----------+--------------------
 Nadège    | 4e6164c3a86765
 NadÃ¨ge   | 4e6164c383c2a86765
(2 rows)

db=# SELECT "firstName", convert_from("firstName"::bytea, 'latin1') FROM contact WHERE "lastName" ILIKE 'crochard';
 firstName |  convert_from
-----------+----------------
 Nadège    | NadÃ¨ge
 NadÃ¨ge   | NadÃ\u0083Â¨ge
(2 rows)

db=# SELECT "firstName", convert("firstName"::bytea, 'utf8', 'latin1') FROM contact;
 firstName |     convert
-----------+------------------
 Nadège    | \x4e6164e86765
 NadÃ¨ge   | \x4e6164c3a86765
(2 rows)

Using python I'm able to get the correct encoding with:

data.encode('latin1').decode('utf8')

Any hint on how to convert these wrongly encoded data in postgres ?


texts:
I am trying to export a table from one database and import it into another. The table in question has a definition like this:

CREATE TABLE `mydb`.`people` (
  `id` BINARY(16) NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)

And I'm exporting it like this:

mysqldump -u root -pmypass -h x.x.x.x mydb people > /tmp/export.sql

And trying to import it like this:

mysql -u root  mydb < /tmp/export.sql

To which I get the error:

ERROR 1406 (22001) at line 1: Data too long for column 'id' at row 1

Looking at the file, It has exported the id as values like this:

'\0\0�\�\��DM�� �Ϥ6�'

Which seems to be interpreted as a string. Now, since I'm dealing with tremendous amounts of data and cannot go through it manually, is there a way I can fix the import/export to interpret this value as binary?


texts: Do I still need to disable or drop column store index before insert and/or update on big tables?

texts:
As the title says, I thought that from MSSQL 2016 I don't really have to drop or disable ColumnStore indexes before ETL and rebuild it after.

Any consensus?


texts: The reading with secondaryPreferred when chunk migration occurred often fail

texts:
We have some problems regarding balancer configuration.
Our application which is set up with secondaryPreferred often fails to read documents from the secondary when balancer( chunk migration ) is running.
So I changed the Balancer setting to the appropriate setting as below, but I could observe the unexpected behavior.

use config
db.settings.update(
   { "_id" : "balancer" },
   { $set : { "_secondaryThrottle" : true ,
              "writeConcern": { "w": 6} } },
   { upsert : true }
)

->  { "_id" : "balancer",   "stopped" : false,   "_secondaryThrottle" : true,   "writeConcern" : { "w" : 6 } }

1. Environment

Our cluster is as below.

  ( 1 primary, 5 secondary, one arbiter ) * 6 masters on Sharded cluster
  on MongoDB 3.2.4 on Google Cloud Platform Mongod instance type  is
  "n1-standard-16 (16 vCPUs,  60 GB memory) with 500GB persistent disk"

2. What did I expect?

I expect that the application with reading as secondaryPreferred can read from the Secondary-mongod without failing. I think that the Balancer can wait for all completion of chunk-migration because I set up "writeConcern:6". So my expectations are as described above.

3. Test conditions and results

I configured as above settings and restarted balancer and execute the application.
The application( secondaryPreferred ) failed with high frequency to read from the secondary-mongod.

Please let me ask a question.

Q1. What is the meaning of writeConcern in balancer setting? It seems
that writeConcern does not work as shown in the above test.
Q2. How can I read without failing when chunk migration occurred?
Q3. What is the purpose of _secondaryThrottle?  I have read the
official document of v3.2. And official document described that
_secondaryThrottle is true as the default value and has the same meaning as writeConcern: 2.  But we can configure settings as below.
If I set up _secondaryThrottle:false, does it makes the writeConcern
invalid right? Is "_secondaryThrottle" the flag for enabling
writeConcern?

  e.g. { "_id" : "balancer",  "stopped" : false,  "_secondaryThrottle" :
  false,  "writeConcern" : { "w" : 6 } }

sincerely


texts: SQL Server Service Account login or password is not valid error

texts:
I am trying to install SQL Server 2012 Express on a Windows 2012 Server for test purposes. This machine is part of our company domain.

During installation I am trying to use one of domain account for SQL Server Database Engine service but it is failing with following error,

  "The SQL Server service account login or password is not valid. User
  SQL Server Configuration Manager to update the service account."

I have verified both domain user and password. And this account has "Log on as a Service" permission enabled. I have also added this user into local admin group but still no go.

Any suggestion is appreciated.


texts: How do others know what is delivery address and invoice address if they are in the same table?

texts:
When I searched around, the recommended way is to use the invoice address and delivery address in the same table and use a field "type" to show what it is. For example
Addresses(Company, address, city, type)

Type = 1: The address is a delivery address
Type = 2: The address is a invoice address
Type = 3: The address both delivery address and invoce address (same address)
If someone does not look at my code that uses the database, they can not know what Type 2 is. Is it delivery, invoice or both? Do you usually clarify this and how? For example, if someone wants to search the database (outside my code that calls the database) for all billing addresses, they do not know if they should use type 1, 2 or 3 to get all invoice addresses from a database client.
Compared to if you had two tables, then the name of the table says what it is and whoever can easily pick out all the invoice addresses.
Delivery(Company, address, city)
Invoice(Company, address, city)


texts: on deleting a row, the update logic is run also

texts:
I am using a after insert, update delete trigger.

When I insert a record in my application called as clarity, it inserts a row in the test table with the value as 'Insert'

When I update the record in my application, it insert a row in my test table with the value as 'Update'

When I delete the record in my application, it adds two rows in my test table with the value as 'Update' and 'Delete' in that order.

Why would deleting a row call the update logic of the trigger.

My code is as below

USE [claritydb]
GO
/****** Object:  Trigger [niku].[TRIG_STATUSRPT]    Script Date: 11/11/2019 11:04:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [niku].[TRIG_STATUSRPT]
ON [niku].[ODF_CA_COP_PRJ_STATUSRPT]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @STATUSID INT,
            @PROJECTID INT,
            @SCHEDULE_STATUS INT,
            @SCOPE_STATUS INT,
            @EFT_STATUS INT,
            @OVERALL_STATUS INT,
            @ACTION_TYPE VARCHAR(50),
            @MAXSTATUSIDVAR INT,
            @DelCount int,
            @InsCount int,

   SELECT @InsCount = Count(*) FROM INSERTED
   SELECT @DelCount = Count(*) FROM DELETED

---- Get data from inserted/ updated
SELECT @STATUSID= ID,
         @PROJECTID= ODF_PARENT_ID,
            @SCHEDULE_STATUS= COP_SCHEDULE_STATUS,
            @SCOPE_STATUS= COP_SCOPE_STATUS,
            @EFT_STATUS= COP_COST_EFT_STATUS
           FROM inserted
 ---- Get data from deleted
SELECT @STATUSID= ID,
         @PROJECTID= ODF_PARENT_ID,
            @SCHEDULE_STATUS= COP_SCHEDULE_STATUS,
            @SCOPE_STATUS= COP_SCOPE_STATUS,
            @EFT_STATUS= COP_COST_EFT_STATUS
           FROM deleted
SELECT TOP 1 @MAXSTATUSIDVAR= ID FROM ODF_CA_COP_PRJ_STATUSRPT op where op.ODF_PARENT_ID = @PROJECTID  ORDER BY ID DESC

   If @InsCount > 0 and @DelCount = 0
   Begin
      -- At least 1 row inserted. Your Insert Trigger logic here
        INSERT INTO TEST_STATUS_REPORT(STATUS_ID,PROJECT_ID,COP_SCHEDULE_STATUS,COP_SCOPE_STATUS,COP_COST_EFT_STATUS,ACTION_TYPE,CREATED_DATE,MAXSTATUSID)
              Values( @STATUSID, @PROJECTID, @SCHEDULE_STATUS, @SCOPE_STATUS,@EFT_STATUS, 'Insert',GETDATE(),@MAXSTATUSIDVAR)

   End
   Else If @DelCount > 0 and @InsCount = 0
   Begin
      -- at least 1 row deleted. Your Delete Trigger logic here
       INSERT INTO TEST_STATUS_REPORT(STATUS_ID,PROJECT_ID,COP_SCHEDULE_STATUS,COP_SCOPE_STATUS,COP_COST_EFT_STATUS,ACTION_TYPE,CREATED_DATE,MAXSTATUSID)
              Values( @STATUSID, @PROJECTID, @SCHEDULE_STATUS, @SCOPE_STATUS,@EFT_STATUS, 'Delete',GETDATE(),@MAXSTATUSIDVAR)
   End
   Else If @DelCount > 0 and @InsCount > 0
   Begin
      -- old row deleted, new row inserted; both indicates an update.
      -- your update logic here.
              INSERT INTO TEST_STATUS_REPORT(STATUS_ID,PROJECT_ID,COP_SCHEDULE_STATUS,COP_SCOPE_STATUS,COP_COST_EFT_STATUS,ACTION_TYPE,CREATED_DATE,MAXSTATUSID)
              Values( @STATUSID, @PROJECTID, @SCHEDULE_STATUS, @SCOPE_STATUS,@EFT_STATUS, 'Update',GETDATE(),@MAXSTATUSIDVAR)
   End
END


texts: current_timestamp default column value working in Windows but not in Linux

texts:
This MySql query runs fine in Windows:

CREATE TABLE `posts`.`table` (
  `idTable` INT NOT NULL AUTO_INCREMENT,
  `Column1` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`idTable`));

In Ubuntu, it is giving the following error:

  ERROR 1067: Invalid default value for 'Column1'

How do I give CURRENT_TIMESTAMP as the default value for 'Column1' in Ubuntu?

The version of MySql is this:

mysql  Ver 14.14 Distrib 5.5.43, for debian-linux-gnu (x86_64) using readline 6.3


texts: Will query continue to run even after network times-out

texts:
I have a few long-running queries that I'm executing against a remote instance of Postgres (AWS RDS).  When I execute any of these queries, I get a network timeout on my client after a couple of minutes.
Does the query continue to run even though the client connection timed-out?
These particular queries do updates and deletes, so I don't need any information back from the query, I just need to know that they completed.  I've tried with both psql (locally on my machine, not on the server; I can't run it on the server because it's on RDS), and Datagrip, but the timeout happens for both.
There's a similar question here, but it contains two contradictory answers.
Alternatively, is there a way to execute a query fire-and-forget style, where the server wont send information back at all?


texts: Tables in one of my schemas are not listed in INFORMATION_SCHEMA.TABLES. What can be the reason?

texts:
I have three schemas in my SQL Server database, but when I query over INFORMATION_SCHEMA.TABLES only tables that belong to two of the schemas are listed. Tables in the third schema are not listed. What can be the cause?

My query is:

USE MyDatabase
SELECT * FROM INFORMATION_SCHEMA.TABLES;

I think it is a permissions issue, but I can't find it. I have compared the schemas (and their owner roles) and their permissions look the same. I can't find any difference between them.

I want to list all the tables, not schemata. Even when I run SELECT * FROM INFORMATION_SCHEMA.SCHEMATA my third schema is not in the result.

When I run SELECT OBJECT_ID('Schema3.Table1') I receive NULL whereas the result for SELECT OBJECT_ID(Schema2.Table1)is the object_id.


texts: How I do a complete backup restore of MySQL Schema with views and functions?

texts:
I am trying to backup a single schema on a Windows server and restore it to an Ubuntu 12.04 Linux server running MySQL 5.5.4. The schema includes views and functions. When I used the MySQL Workbench "export" feature (including data and structure), most of the views were not imported to the new server.

Great if someone could share how to do this via Workbench, otherwise, I just need the syntax to do a complete dump from the CLI and it's restore equivalent.


texts: PostgreSQL - how to recognize if function was called by delete or update trigger?

texts:
I have function, which should be called after update and delete on one table.

At the end of function I have to return "old" if function was called after delete or "new" if procedure was called after update.

I did it like this:

if new is null
then
  return old;
end if;

return new;

But I get error telling me that "new" has not been initialized if function is called after delete.
My question is how to recognize if function was called after delete or update?


texts:
At my new job, we have multiple named instances on each servers. e.g.

Server1\Dev
Server1\DevIntegrated
Server1\QA

I have a SQL PowerShell script in the works that calls out to the OS, invokes Foo.exe but needs to pass a command line parameter (the connection string). A SQL Agent job will exist on each instance, with a step of type PowerShell, that needs to know what the current context is. i.e. This execution began on DevIntegrated.

I have no desire to have every script start with...

$thisInstance = "Dev"

...especially since I'd have to edit that when we migrate to environments (new servers and named instances) in the upcoming months.

If I start SQLPS, I can determine my instance by slicing and dicing the results of Get-Location or running

(Invoke-Sqlcmd -Query "SELECT @@servername AS ServerName" -SuppressProviderContextWarning).ServerName

When the SQL Agent starts a job of type PowerShell, it starts in the C:\windows\system32 and the Get-Location route doesn't work as it's not in the SQLSERVER context. I can change into that context but I'll be at the "root" of SQL Server and won't know what instance I should be in. Using the Invoke-Sqlcmd route won't work either for same reason (technically, it times out as there is no default instance)

To the best of my knowledge, I've enumerated all the basic "things" I can get into the job log but nothing seems to show SQLSERVER:\SQL\Server1\DevIntegrated

Get-ChildItem
Get-Host
Get-Location
Get-Process
Get-PSDrive
Get-PSProvider
Get-Service
Get-TraceSource
Get-Variable

Get-Process seems like I could use that and some voodoo of trying to cobble things together by hitting the instances and matching spids but that just sounds like a bloody-hack from hell. There must be something basic that I'm missing, can anyone shed some light?

Alternatives to PowerShell investigated

I had investigated using other job types and didn't get a satisfactory resolution. Research indicated the PowerShell listed under SQL Agent was SQLPS and starting an instance of it by right clicking on the Agent automagically dropped me into the correct location. It was only when I pasted my interactive code into the job step that I learned of the difference as previously mentioned.

Job type of OS put me into an identical state in that I could not find a way to determine which instance dropped me into the command shell. Sure, I could sqlcmd and get the value of @@servername but if I knew what connection to start sqlcmd, I wouldn't need to to query the database ;)

TSQL could probably work if we enable xp_cmdshell but I'm not sure if they have it turned on---government facility and they can be persnickety on non-default settings. Even then, I'm stuck futzing with dynamic SQL and losing a lot of the expressiveness and power that PowerShell lends.

While a bit ungainly, I thought defining a variable at the first step and passing that to successor steps but research turned up this article Handling Multiple Job Steps (BOL)

  Job steps must be self-contained. That is, a job cannot pass Boolean values, data, or numeric values between job steps. You can, however, pass values from one Transact-SQL job step to another by using permanent tables or global temporary tables. You can pass values from job steps that run executable programs from one job step to another job step by using files.

I can't use common tricks like a well-known file/environment variables/registry setting that Foo.exe looks for since that would prevent concurrent execution across instances.

TL;DR:

In a SQL Agent Job step of type PowerShell, how can you determine the instance of SQL Server that launched the process?


texts: I want to find out how data is getting inserted to a particular table?

texts:
I want to find out how data is getting inserted to a particular table.

I mean to say I want to find out the job or Stored Proc which is running on my SQL Server and pushing data regularly into one of my tables.


texts:
The VMs I run SQL Server on are regularly backed up by the VMware backup tool. I would prefer not to use this backup solution but I have no choice but to cope with it.

The problem I've come up across is the fact that every time the VM backup us ran, SQL Server thinks it has performed a full backup of the databases to a virtual device (physical_device_name = GUID).

I can see those backups in msdb (they are copy_only backups so I don't have to worry).

Do you know why it works this way? Is VMware aware of SQL Server existence? Or maybe SQL Server has wrong data in msdb?

Can I run into any problems with that solution (other than trying to restore the DB from the virtual device)?


texts: Implicit conversion between decimals with different precisions

texts:
I know that when SQL Server handles an implicit conversion between types it converts the lowest priority one to the highest one.

But what is the result datatype when I perform an operation between decimals with different precisions?


texts: How can I benchmark the performance of UDFs to figure out which one is faster?

texts:
Basically I have two scalar UDFs that output the same data, one that uses a cursor and one that uses a recursive CTE. I want to determine which one should be used and discard the other; I'd prefer to make this decision based on actual performance numbers. I'm also curious how much of a difference there is between the two.

How can I benchmark the performance of these UDFs to figure out which one is faster?