texts: Does CREATE TABLE SomeSchema.#TempTableName have a bug?

texts:
Simple test-bed:

USE tempdb;
GO

/*
    This DROP TABLE should not be necessary, since the DROP SCHEMA
    should drop the table if it is contained within the schema, as
    I'd expect it to be.
*/
IF COALESCE(OBJECT_ID('tempdb..#MyTempTable'), 0) <> 0
    DROP TABLE #MyTempTable;

IF EXISTS (SELECT 1 FROM sys.schemas s WHERE s.name = 'SomeSchema')
    DROP SCHEMA SomeSchema;
GO

CREATE SCHEMA SomeSchema AUTHORIZATION [dbo]
CREATE TABLE SomeSchema.#MyTempTable /* specifying the schema
                                        should not be necesssary since
                                        this statement is executed inside
                                        the context of the CREATE SCHEMA
                                        statement
                                     */
(
    TempTableID INT NOT NULL IDENTITY(1,1)
    , SomeData VARCHAR(50) NOT NULL
);
GO

INSERT INTO tempdb.SomeSchema.#MyTempTable (SomeData) VALUES ('This is a test');

SELECT *
FROM tempdb.SomeSchema.#MyTempTable;
GO

SELECT *
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = 'SomeSchema';

SELECT s.name
    , o.name
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = 'dbo'
    AND o.name LIKE '%MyTempTable%';

DROP SCHEMA SomeSchema;
DROP TABLE #MyTempTable;

The above should create a temporary table named #MyTempTable in the tempdb under the schema named SomeSchema; however it does not.  Instead the table is created in the dbo schema.

Is this expected behavior?  I realize this is certainly an edge-case around the use of schema-specific temp tables; however it would be nice if the engine either provided an error when attempting to create a schema-bound temporary table, or actually did bind it to the schema specified in the DDL.

Also, I do not presently have access to SQL Server 2014 or 2016; does it work as expected on those platforms?


texts: SQL Query with ODBC, Joins and Group Bys

texts:
I am having issues with the statement below.  The main purpose of the query is to pull a list of customers into an Access db from ODBC (our customers are stored in DB2 on IBM i).  The query is intended to look for a customer (OM01U1.OM01015) that has not had a delivery (RS2101F.DELDATE) in over 13 months but is not already suppressed (OM01U1.OM01068).  I do need all of the columns in order to research the equipment and financial records so I can't remove them.  The statement is pulling from several tables though.  I believe that the issue may be with the "Group By".  I am also getting duplicate outlets (OM01U1.OM01015) when I do receive results.  I only need to see the outlet one time with it's last delivery date (RS2101F.DELDATE). Please don't beat me up too bad, I did not create this query I just sort of inherited it.  I know it is way off and there is probably a better way for me to get the information I need.  I appreciate any help guys!

SELECT
    OM01U1.OM01041 as Loc,
    OM01U1.OM01015 AS Outlet,
    OM01U1.OM01945 AS OLTyp,
    OM01U1.OM01052 AS Outlet_Name,
    OM01U1.OM01054A AS Street_Number,
    OM01U1.OM01054C AS Street_Name,
    OM01U1.OM01055A AS City,
    OM01U1.OM01055B AS State,
    OM01U1.OM01106 AS Zip,
    OM01U1.OM01058A AS Area_Code,
    OM01U1.OM01058C AS Phone,
    OM01U1.OM01037 AS Channel,
    OM01U1.OM01926 AS USA_Type,
    OM01U1.OM01078 AS Key_Acct,
    OM01U1.OM01110 AS TRDGRP,
    OM01U1.OM01034 AS Trade_Name,
    OM01U1.OM01248 AS DTC,
    OM01U1.OM01073 AS Sales_Route,
    OM01U1.OM01068 AS Sup_CDE,
    OM01U1.OM01982 AS Sup_Reason,
    OM01U1.OM01067 AS Sup_Date,
    OM01U1.OM01065 AS Creation_DT,
    OM01U1.OM01066 AS Update_DT,
    OM01U1.OM01141 AS CAN_NUM,
    CF30_CNSTYP AS CF_CONTYP,
    EC01_EQUNUM AS EC_EQUIP,
    EC01_PRMSLR AS EC_SUPPLIER,
    EC01_EQUOWN AS EC_OWN_CD,
    EC01_EQUACY AS EC_ACCESSORY,
    EC01_EQUSPP AS EC_COMPONENT,
    EC01_SYSSTA AS EC_STATUS,
    EC01_ISTDAT AS EC_INSTALL_DT,
    EC01_RSPPRN AS EC_PERSON,
    EC01_LASMTCDAT AS EC_LAST_MNT_DT,
    OC02_RNTCTCNUM AS OC_CONT_NUM,
    OC02_RNTCTCSTA AS OC_CONT_STATUS,
    OC02_BTONUM AS OC_BILL_TO,
    RS2101F.DELDATE AS RS_LAST_DEL_DT
  FROM
    CF30, EC01, OC02, OM01U1, RS2101F
  WHERE
    OM01U1.OM01015 = RS2101F.OUTNUM

  GROUP BY
    RS2101F.DELDATE,
    OC02_BTONUM,
    OC02_RNTCTCSTA,
    OC02_RNTCTCNUM,
    OC02_OUTNUM,
    EC01_LASMTCDAT,
    EC01_RSPPRN,
    EC01_ISTDAT,
    EC01_EQUOWN,
    EC01_EQUACY,
    EC01_EQUSPP,
    EC01_SYSSTA,
    EC01_PRMSLR,
    EC01_EQUNUM,
    CF30_CNSTYP,
    OM01U1.OM01141,
    OM01U1.OM01066,
    OM01U1.OM01065,
    OM01U1.OM01067,
    OM01U1.OM01982,
    OM01U1.OM01068,
    OM01U1.OM01073,
    OM01U1.OM01248,
    OM01U1.OM01034,
    OM01U1.OM01110,
    OM01U1.OM01078,
    OM01U1.OM01926,
    OM01U1.OM01037,
    OM01U1.OM01058C,
    OM01U1.OM01058A,
    OM01U1.OM01106,
    OM01U1.OM01055B,
    OM01U1.OM01055A,
    OM01U1.OM01054C,
    OM01U1.OM01054A,
    OM01U1.OM01052,
    OM01U1.OM01945,
    OM01U1.OM01015,
    OM01U1.OM01041
  HAVING  RS2101F.DELDATE < 1110809
      AND OM01U1.OM01068<>'S'


texts: ROLLBACK make data in INSERTED table is removed in AFTER INSERT TRIGGER

texts:
I give an example to show my problem. I create a table as the following:

CREATE TABLE a
(
  id INT
)

I create a AFTER INSERT trigger to don't allow to insert id=1 into table "a"

CREATE TRIGGER [dbo].[insert_a]
ON [dbo].[a] AFTER INSERT
AS
BEGIN

DECLARE @id INT
SELECT @id=id FROM inserted
IF @id=1
 BEGIN
  RAISERROR('1',12,1)
  ROLLBACK;
 END
SELECT * FROM inserted
END

Then I insert id=1 into table "a":

INSERT INTO a VALUES(1)

I get nothing from INSERTED table.

I realize that when I ROLLBACK then:

the data in table "a" was ROLLBACK (I know)
data in INSERTED table is also removed.Why is that?

If I change AFTER INSERT into INSTEAD OF INSERT in trigger.

ALTER TRIGGER [dbo].[insert_a]
ON [dbo].[a] INSTEAD OF INSERT
AS
BEGIN

DECLARE @id INT
SELECT @id=id FROM inserted
IF @id=1
 BEGIN
  RAISERROR('1',12,1)
  ROLLBACK
 END

SELECT * FROM inserted
END

INSERT INTO a VALUES(1)

Then I get the result:

id
1

That means data in INSERTED table is not removed though have been ROLLBACK.

Help me explain?


texts: Find which account SQL Server service is running BACKUP under

texts:
Our DBA is out and I'm trying to back up our (remote) dev database to a UNC path. I'm running the back up operation while logged into Management Studio as a particular Windows domain account (e.g. CORP\myUser). I've checked that this domain account has FULL CONTROL rights the UNC path \\nameOfMachine\backup\.

When I attempt to execute the following as CORP\myUser

BACKUP DATABASE [DEV] TO  DISK = N'\\nameOfMachine\backup\dev.bak' WITH COPY_ONLY, INIT;

I receive the error

Cannot open backup device '\\nameOfMachine\backup\dev.bak'.
Operating system error 5(Access is denied.).

Which seems to indicate that whatever account SQL Server is using to run the backup command does not have rights to that UNC path.

I do not have a way to RDP to the dev database server and find out which account SQL Server is using to execute the backup command above. Is there a way to find this out from Management Studio using e.g. a dynamic management view?


texts: Issue with Time Zone while migrating MySQL on-premises DB to AWS RDS

texts:
I am having MySQL 5.6 database with PST time format in on-premises server. Now we are willing to migrate the database to RDS.

For this, we have taken backup from physical server and restored in RDS and setup replication to sync the data.

But found the time is getting changed to UTC. We found the time format in RDS is UTC, so we changed the time format of time_zone to PST in parameter group. But we are not able to change the default time_zone parameter.

Due to this, the time which is coming from master server is getting updated to UTC and storing in database which is 8hrs more than actual time.

Due to constraints, we are not able to use AWS DMS for replicating data. How can we change the default time_zone or fix the time zone issue in RDS.


texts: Can i set Accent insensitive for individual languages?

texts:
Can i make a table/column like this:

Accent sensitive for Turkish and English.
Accent insensitive for Arabic.

Actually if this can be done on a full-text index, that would also work for me.

Accent does matter for Turkish but doesn't matter that much for Arabic.

And I don't want to compose individual tables for each table if possible.


texts: PostgreSQL - do we need to DROP the database and recreate it if we want to RESTORE it?

texts:
I worked with many different databases (SQL Server, Oracle, IBM db2) and for all of them if we have a backup it is just enough to execute RESTORE command in order to restore the data.

I am beginner in PostgreSQL.

As I see in documentation and on this question also https://stackoverflow.com/questions/35289888/restore-postgres-database-dump
for succesfull restoration of the database we need to:

* DROP database
* CREATE database
* execute psql dbname < dumpfile

I am really confused (based on the behavior from all other databases ) that we need to drop and recreate database if we want to restore? It's hard for me to believe that Postgres really is implemented in that way.


texts: Should a locked query increment its CPU and IO counts?

texts:
When I create a test case in a session #1:

create table ##t (a int)
begin tran
insert into ##t select 1

and in another session #2 run

select * from ##t

and yet in another session run

sp_who2 active

then no CPU and IO counters are increasing on the blocked session #2. This makes sense, but is this always the case for a blocked statement?


texts: mariabackup slave info and multi-master replication

texts:
I have several master databases using mariadb 10.3 and replicate then all to one replica using multi-master replication. All is working fine.

I want to backup that replica server, so i can restore it quickly if needed, with all the multi replication setup.
In the past, I used mariabackup with --slave-info when i only had one master replication and the restore worked fine and i had the xtrabackup_slave_info with the required info to restore the replication.

Now with mutl-master replication, the --slave-info report that there is no replication setup and looks like it do not support the multi-master support.

So i'm missing something or mariabackup fail to support this? what other people do to backup a mariadb multi-master setup that allow easy replication restore?


texts: Suitable way to record users requests for usage invoicing/billing in database?

texts:
Is there more efficient way to keep a track of all requests made by each user with timestamp? And then filter the requests every fortnight to invoice the users?

Example DB schema

Table: user; Attributes: UserID (PK)

Table: item; Attributes: ItemID (PK)

Table: user_request; Attributes: UserID, ItemID, Timestamp

where UserId and RequestID is composite key

How should one invoice requests made by users.
Is the only way to filter the stored requests based on invoice period and count the records within that period? Is there a more efficient/suitable way to invoice the requests?

What DB design are other services that charge users made on usage rather than subscription that charge on a set num of requests.
Please note I'm referring to requests for data. The service would be charging users based on number of requests made for data.


texts: How to backup some selected tables, but not all, in pgAdmin 4?

texts:
In pgAdmin 3, the dialog to perform a backup allowed you to check and un-check the tables you wanted to be part of your backup.

In pgAdmin 4, either I am unable to find this feature, or it is not there.

Is there any method of backing up only a selection of tables in pgAdmin 4?


texts:
I'm following this tutorial to set up my postgresql db.

http://inventtheweb.blogspot.ca/2012/05/install-postgresql-for-django.html

After I finish running the CREATE DATABASE command and run \q

I get the following response.

could not save history to file "/var/lib/postgresql/.psql_history": No such file or directory

What exactly is causing this and will this cause me any issues?

More Information: The version of postgresql that is being installed is 9.3 and I'm installing it on Ubuntu Server 14.04 LTS


texts:
I am using Cloudera Manager to monitor and configure Hadoop. I choose PostgreSQL as metastore of HUE - a web portal to submit your SQL to Hadoop.

When I submit a SQL, the HUE says the database is locked. I don't have any database management experience.

So, how to find the root cause and diagnose?

I try to connect to the database postgres which I believe this is the meta-database of PostgreSQL itself. I expect I can find the processes, events and lock information in this database.

postgres=> \c postgres
Password for user hue:
You are now connected to database "postgres" as user "hue".
postgres=> \d
No relations found.
postgres=>

I connect to this database - 'postgres' as user - 'HUE'. But I didn't find any tables under it. Am I using the wrong user?

How can I access this database successfully? How can I discover why the database is locked? Where is it locked? On which table and which database?


texts:
I know that SQL Server 2012 and 2014 offer AlwaysOn Availability Groups which provide this functionality, but I'm still stuck on SQL Server 2008 R2 for a while.

I recently came across this AWS whitepaper: http://media.amazonwebservices.com/AWS_RDBMS_MS_SQLServer.pdf

I was confused by this chart on page 11:
which seems to indicate that the mirror database can be used for reading (checks in the 'Readable Copy' feature column), just as it can when using Log Shipping, because it's been my experience that the mirror database cannot be used at all--it's only purpose is for failover.

The answer to this question: Database mirroring is limited to an original database seems to confirm my suspicions that the mirror database is NOT readable, though only in passing.

Is this whitepaper wrong, or are SQL Server 2008 R2 mirror databases indeed readable?

If they are, what does one need to do to do so, as attempting to connect in SSMS results in a window connected to the master database, and running USE [database] gives the following error:

Msg 954, Level 14, State 1, Line 1
The database "database" cannot be opened. It is acting as a mirror database.

UPDATE: I understand that there are ways to kind-of workaround this issue and get something derived from the mirror and time-delayed that is readable, and I suppose one could make an argument that a snapshot is a "Readable Copy", but that's very different from the type of readable copy you'd get through log shipping or transaction replication, as both of those would be automatically updated, even if slightly out-of-date (maybe snapshots can be too--I'm stuck on Standard Edition, so I'm not as familiar with the Enterprise Edition feature set).  In addition to that, a truly readable synchronous mirror (like using AlwaysOn Availability Groups in 2012+) would provide a perfectly synchronized readable version--which is FAR more useful because the mirror could be used to distribute read-query load and avoid all the issues with out-of-date data being returned.  My primary purpose (as stated in the question above) was really to get a definitive answer about the readability of the mirror itself (not a snapshot or a replica of it).  While the provided answer agrees with my own experience, the only links to definitive sources provided are for possible ways to achieve similar results--neither of them definitively state that mirrors are not readable.  I will accept the first answer that provides such a link.


texts:
Suppose I have a table:

SERVICE={SID,NAME,PORT}

and with the help of a Java program, I want to select the SID of the known port with the help of a SQL query:

Select SID from SERVICE where PORT=port

But when I write the code snippet:

String sql="Select SID from SERVICE where PORT=port;";

where port is an integer variable but on execution I get the following error:

No such column port".How to get rid of this error?


texts: How to set datadir to different values based on the hostname in my.cnf?  I want to use the same my.cnf file on a Linux machine and a Mac OS X machine where the datadir should have different values.


texts: Linked Server to Mirrored Database Fails

texts:
I've setup up a mirrored database on two servers Server1 and Server 2.

On Server3, I'm trying to setup a linked server that will work regardless of which server has the active database.

I'm using this to setup the linked server.

EXEC sp_addlinkedserver
  @server = N'MyLinkedServer',
  @srvproduct = N'',
  @provider = N'SQLNCLI',
  @provstr = N'Server=SERVER1;FailoverPartner=SERVER2;Network=dbmssocn',
  @catalog = N'MyDatabase';

EXEC master.dbo.sp_serveroption
  @server = N'MyLinkedServer',
  @optname = N'data access',
  @optvalue = N'true';

EXEC master.dbo.sp_serveroption
  @server = N'MyLinkedServer',
  @optname = N'rpc',
  @optvalue = N'false';

EXEC master.dbo.sp_serveroption
  @server = N'MyLinkedServer',
  @optname = N'rpc out',
  @optvalue = N'true';

EXEC master.dbo.sp_serveroption
  @server = N'MyLinkedServer',
  @optname = N'connect timeout',
  @optvalue = N'60';

EXEC master.dbo.sp_addlinkedsrvlogin
  @rmtsrvname = N'MyLinkedServer',
  @locallogin = NULL,
  @useself = N'False',
  @rmtuser = N'sqluser',
  @rmtpassword = N'sqlpassword';

As long as MyDatabase on Server1 is the principal, the linked server works.  When I manually failover to Server2, the linked server fails.

The error messages are:

Msg 11001, Level 16, State 1, Line 0
TCP Provider: No such host is known.
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sqluser'.
Msg 4060, Level 11, State 1, Line 1
Cannot open database "MyDatabase" requested by the login. The login failed.

The 18456 and 4060 error messages repeat until the query times out after 60 seconds.

On Server1, I can see repeated failures trying to login to "MyDatabase".  It's failing because Server1 is the mirror.  I see no attempts to connect to Server2 which is the principal.

If I recreate the linked server with Server2 as the Primary and Server1 as the Mirror, it will work until I fail back to Server1.

The mirrored servers are SQL Server 2012 and the box I'm trying to create a linked server on is SQL Server 2005, but I also have the same problem when creating the linked server on a SQL Server 2008 R2 box.

I've seen reports that it does work.

What am I missing?

Thank you!

Update

I've made progress.  Yesterday, when I first set things up, I could have sworn it worked once.  I could never reproduce it.  Then, today, I got it to work once.  I can failover from Server1 to Server2 (or vice versa) one time.  The linked server will work after a failover, but if I failover back to the original server, it stop working.  A restart of the SQL Server service on the server with the linked server will let it work again (for 1 failover).  Letting things sit overnight must work too since it worked the first time I tried it today.

Creating a new linked server (with a different name) doesn't work, so I'm guessing that the SQL Native Client driver is caching something.

Another Update

I did get the linked server (as described above) to work correctly on a SQL Server 2012 Express instance.  Then, I tried it on another SQL Server 2012 Standard instance.  It did not work.  One thing that might be relevant is that Server1 and Server2 are in a different untrusted domain than Server3.  But, since I'm using sql server authentication, I wouldn't think it should.  The Express instance that works fine is in the same untrusted domain.


texts: 3 Simple Queries SQL Student Question, Query Errors in Microsoft Access

texts:
The First Question is: "List the name of each trip that does not start in New Hampshire (NH)."
SELECT TripName
FROM Trip
WHERE State!='NH'; /* works with State='NH' */

The Second Question is: "List the trip name, type, and maximum group size for all trips that have Susan Kiley as a guide."
SELECT TripName, MaxGrpSize
FROM Trip, TripGuides
WHERE Type=Hiking, GuideNum = 'BR01';
/* also i tried WHERE Type=Hiking AND GuideNum = 'BR01'; */

I Cant Filter by only type: "Hiking"
The Third Question is: List the reservation ID, customer number, customer last name, and customer first name for all trips that occur in July 2018.
I Tried This:|
SELECT DISTINCT  LastName, FirstName, CustomerNum, ReservationID
FROM Customer, Reservation
WHERE TripDate = '2018-12'; /* i tried CustomerNum.Customer */

If you need more tables and more information let me know.


texts: Application Server is not able to connect to SQL Server database when TLS 1.2 or 1.1 is enabled

texts:
In our environment, SQL Server 2012 Servers whenever TLS 1.2 or TLS 1.1 is enabled Application Server is not able to connect to the database.The same issue is observed only on MS SQL Server 2012 related Servers.

Error Message:

  Connection failed:
  SQLState: '01000'
  SQL Server Error: 1
  [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (SECCreateCredentials())
  Connection failed:
  SQLState: '08001'
  SQL Server Error: 18
  [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error


texts: SQLServer In-Memory database and log file size unexpected increase

texts:
I have a strange issue with a SQLServer 2014 (12.0.4439.1) database.

I created some months ago a data file and table to play with In-Memory basic features and see if could improve some of our processes.
When I finished I wanted to clean everything but I didn't know that "Once you create a memory-optimized filegroup, you can only remove it by dropping the database".

Well, I didn't want to delete my database (since it is used in the real life by real people) nor re-create / re-fill it (quite a lot of work).

So everything stayed like that : a normal DB with one empty file for in-memory stuff. And everything is going well.

But, once, log file of this database is going crazy : its size increases and nothing can stop it.
And when I try to shrink it, I often (not always) get a "shrink failed for log file due to XTP_CHECKPOINT" which looks like related to in-memory features.
And after trying backup / service restart / checkpoint and other esoteric commands, log file can be manipulated and shrunk correctly.

So briefly : I don't understand what happened.

Does someone has experienced that ? and/or know a clean way to avoid/fix this strange behaviour ?

[EDIT] : the issue looks very similar to https://support.microsoft.com/en-us/kb/3090141 but in my case, hotfix is already installed and the workaround suggested (offline / online) doesn't work

Usually, in SQLServer logs there's a HkHostLogCheckpointRecord() message every hour.
Log file increasing started to happen when this messages stopped to appear.
Is it the cause or the consequence ? that's the question....


texts: How can I check the path of my.ini, through the command line?

texts:
I would like to know, if possible, how to display (using the command line) the path to my.ini used by the server.

I am using MySQL 5.6 inside a windows IIS.

I have the following:

Program Files\MySQL\MySQL Server 5.6\my-default.ini
C:\ProgramData\MySQL\MySQL Server 5.6\my.ini.

For sure, the server is using the second one. Is the path stored in any variable inside mysql server variables?
I was checking some system variables but I couldn't find it.

Any ideas?

UPDATE
After Ronaldo's post I ran a --help and this is what I found:

...
Default options are read from the following files in the given order:
C:\Windows\my.ini
C:\Windows\my.cnf
C:\my.ini
C:\my.cnf
C:\Program Files\MySQL\MySQL Server 5.6\my.ini
C:\Program Files\MySQL\MySQL Server 5.6\my.cnf

The following groups are read: mysql client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
...

The strange thing is that I don't have none of these files. I sit to display hided files on each folder.

So, I took a look to this post and I followed the steps.
Finally I arrived at the window MySQL56 Properties(Local Computer).
I could read:

Path to executable:
"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file

BUT,
if we click over it with the mouse's right button and choose Select All, this is what we have:

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe"
--defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56

So that replies my question.


texts: Can I get shell access through SQL Server 2017 on Linux?

texts: There is a stored proc called xp_cmdshell that is supposed to give you shell access on SQL Server, to the server. I am wondering if that works on Linux?


texts: Can I create a table and database on a slave without breaking replication?

texts:
I have One Mysql master server and Slave server running on ec2. The replication is working fine. Now I need to make some changes.  I need to add new slave server to the current replication loop.

Is it possible to add new tables and database which is not existing on master server on newly created slave.
Is this affect master server ? or broke the replication ?

We are not using this slave server for fail-over. Need to analyses live data and store to another database.

Server version: 5.5.48 MySQL Community Server


texts: sp_executesql: what can be parameterized

texts:
Is there a definitive list published showing what can and cannot be parameterized in a call to sp_executesql.

For example, predicates can, table names cannot.  Number of rows in TOP can:

exec sp_executesql @stmt =
    N'SELECT TOP(@n) a, b
    FROM (VALUES(1,2)) v(a,b)',
    @params = N'@n int',
    @n = 10

MAXDOP cannot:

exec sp_executesql @stmt =
    N'SELECT TOP(10) a, b
    FROM (VALUES(1,2)) v(a,b)
    OPTION (MAXDOP @n)',
    @params = N'@n int',
    @n = 10

  Msg 102, Level 15, State 1, Line 8 Incorrect syntax near '@n'.

I suppose there are many more examples of what works and what doesn't.  I'm looking for an authoritative list to eliminate trial and error.


texts: Is it necessary to make a full backup after pg_upgrade with Postgresql?

texts:
pg_upgrade allows one to quickly upgrade big Postgresql databases, by taking advantage of the fact that the raw data does not need modification, it is only the configuration and metadata. However, I'm wondering how to handle the situation where the database was originally backed up using a full base backup, and from then on using archiving of WAL files. But how does that work with respect to upgrades? I tried to test it, so I configured postgres.conf of the new version to also WAL archive (to a different folder) and then upgraded using pg_upgrade. Both clusters produced some WAL files, however, it doesn't seem to be possible to restore the database by using the original base backup combined with the WAL files (the new version refuses to start on such a directory, and the old version starts but only replays WAL files up to the point of the upgrade).
So I'm wondering what the official procedure is regarding backup after using pg_upgrade. Does one have to make a fresh base backup or is there a way to only backup the changes made by the upgrade (WAL files or otherwise)? I can see there's a procedure for upgrading standby servers but that is not the same scenario (although related).


texts: Is there a need to keep refreshing the indexes from time-to-time?

texts:
This question is common for all the database products I use, viz. SQL Server, MySQL, Oracle and PostgreSQL.

I have created a fresh database and the tables, at present, have no records. I have created indexes on all such columns of tables which are needed in queries often. I want to know how indexes are physically (clustered) and logically (non-clustered) updated when I keep on adding records in tables? Is the index updated automatically or do I need to update the indexes manually, from time-to-time?


texts: Cross join yields execution plan that reads one of the tables multiple times

texts:
I have a query where I need to cross join a small table with 5 rows to a table with roughly 31k rows. The execution plan SQL Server came up with does index scans in both tables and joins them with Nested Loops (INNER JOIN). However, when checking the execution plan, I noticed that the scan on the smaller table produced 5 x 31k = 155k rows for the following step. Does that mean SQL Server is scanning the index of the smaller table 31k times?

Here is a small reproduction of the problem I'm having:

CREATE TABLE A (id INT PRIMARY KEY);

CREATE TABLE B (id INT PRIMARY KEY);

INSERT INTO A(id)
    VALUES (1), (2), (3), (4), (5);

INSERT INTO B(id)
    VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9)

SELECT * FROM A CROSS JOIN B

Here is the query live statistics of the SELECT command

My question is whether the index scan on the smaller table is performing multiple I/O operations, one for each row on the bigger table, as opposed to doing it only once and having its result being fed to the following step only once.


texts: Import XML data while creating IDs with relationships

texts:
I need to parse XML from a file into tables while retaining their relationships through custom-generated ID values.

For example, if I had the following XML:

<root>
   <construction>
      <constructionName>randomname1</constructionName>
      <project>
         <projectname>another randomname</projectname>
         <businesspartners>
            <partnername>bilbo bagginses</partnername>
         </businesspartners>
         <employees>
            <employee>
               <empname>frodo</empname>
               <empaddress>etc...</empaddress>
            </employee>
         </employees>
      </project>
      <info>
         <randElement></randElement>
      </info>
      <constructionType>houses</constructiontype>
   </construction>
   <construction>
      <...(etc, same as above, times n^10)/>
   </construction>
</root>

From that, I need to generate data to the following tables:

CONSTRUCTION (CONSTRUCTION_ID INT PRIMARY KEY
   , CONSTRUCTIONNAME VARCHAR..
   , CONSTRUCTIONTYPE VARCHAR.. )

PROJECT (PROJECT_ID INT PRIMARY KEY
   , CONSTRUCTION_ID INT FOREIGN KEY REFERENCES CONSTRUCTION
   , PROJECTNAME VARCHAR.., )

BUSINESSPARTNERS (BUSINESSPARTNERS_ID INT PRIMARY KEY
   , PROJECT_ID INT FOREIGN KEY REFERENCES PROJECT
   , PARTNERNAME VARCHAR..)

etc...

Basically, the idea is to construct tables with their references intact to represent the XML. The table structures already exist and cannot be changed to accommodate this script. This is simply a matter of being able to do the same kind of XML parsing and then adding the data to the tables while generating the correct referencing ID values, as we did before with integration. Only doing this exclusively with SQL Server, and without using SSIS.

Now, the actual scenario and the file in question is rather massive, so I do by no means expect a full answer. Just tips on where to start looking. I'm quite inexperienced with dealing with XML.

For now, my preferred solution would be just importing the data with OPENROWSET, and then using dynamic SQL with OPENXML to parse the document into the tables by basically looping the elements and their sub-elements one at a time. But that seems a lot more trouble than some other, smarter way.

  How are the ID values generated?

That's kind of part of the question. For now, they aren't. The idea is that each CONSTRUCTION element would be assigned an ID increasing from 1. Then all the children of CONSTRUCTION would then refer to the same ID assigned to the parent, and so on. Basically, it's just about dividing the data in the XML into multiple tables while keeping the referential integrity intact.

I would have no problem using an identity column as a primary key, so long as the relations aren't mixed up as a result. I don't know how to go about doing this, so I assumed one would have to somehow manually define the logic for creating the ID's, as opposed to identity?


texts:
I was test in process of testing the strict mode of mongo shell queries. This the link i am referring.
Sample document looks like -

   {  "_id" : ObjectId("19876"),
    "ip" : "127.0.0.1",
    "pgDownloadTime" : NumberLong(25000),
    "domain" : "test.domain.com",
    "pgAccessTime" : NumberLong("1469422166381"),
    "pgAccessMin" : NumberLong(1469422140),
    "pgAccessHour" : NumberLong(1469421000),
    "contentType" : 1,
    "tags" : [
            "testTag1",
            "testTag2"
    ],
    "catIds" : [
            "cat1",
            "cat2"
    ]

Query using strict mode is -

    db.testData.find({ "pgAccessMin" : { "$gte" : { "$numberLong" : "1469406600" }, "$lt" : { "$numberLong" : "1469413800" } }, "domain" : "test.domain.com" })

And Query without using strict mode is -

    db.testData.find({ "pgAccessMin" : { "$gte" : 1469406600, "$lt" :1469413800 }, "domain" : "test.domain.com" })

Issue is that Strict mode query is not providing results but query without strict mode provides the data.
I am using mongo version - 3.2.7


texts: What index(es) should I use for three columns join?

texts:
I have a table which has the following columns:

CREATE TABLE example (
   exid INT,
   extype INT,
   studentid INT,
   (more columns)
)

The table will have hundreds of thousands rows, and each record will have distinct value of exid, extype and studentid. While studentid and exid will have plenty of distinct values, extype will have max. 6 values. The table will be joined in the following way:

SELECT ...
FROM example ex

LEFT JOIN otherTable1 ot1 ON ex.studentid = ot1.studentid
    AND ex.exid = ot1.exid
    AND ex.extype = 1

LEFT JOIN otherTable2 ot2 ON ex.studentid = ot2.studentid
    AND ex.exid = ot2.exid
    AND ex.extype = 2

Can you suggest me what index(es) on example table will be the most suitable in regards of performance here (I should also add other tables used for joins will go into millions of rows)?


texts:
I am looking to select only 1 row based off the IMMS_ID field. Which row I select is based off this if statement (excuse the syntax)

if (status group based off IMMS_ID) status='DI' select the DI row
else if ... status='OI' ...
else if ... status='FI' ...
else if ... status='OV' ...
else if ... status='FV' ...

So this means if the group based off the IMMS_ID contains OI, OV and FV I want to select the row that has OI (as it's highest in the if/elseif statement).

Ex. Here is a dataset

ID  IMMS_ID  STATUS
 1      1       FV
 2      1       OI
 3      1       FI
 4      2       DI
 5      2       OV

So for this dataset I'd want the following results returned

ID  IMMS_ID  STATUS
 2      1       OI
 4      2       DI

Thanks for your time!


texts: Locking, deadlocking workaround while process data at the same table

texts:
My database has a table: tableX.

Task1 makes intensive INSERTs (1000 records per minute) as records to process and very seldom UPDATEs (1-2 records per minute) as records to recalculate.

At the same time other Task2 SELECTs not processed records (inserted by Task1) and then inserts new records (as processed) to the same tableX in one transaction (it's too long calculation, up to 10 seconds).

Task3 recalculates UPDATED records (by Task1): just UPDATEs tableX.

Could someone recommend design to avoid long locking and deadlocks for this scenario.
It's very important to process all INSERTED records in order as its were inserted!

1) Should I separate (create a new tableX2) processed and non-processed records?

2) Should I separate transaction in Task1 and do SELECTs and INSERTs in two separated transactions?

3) Should I use rowlock, readpast...hints and where (can it help me)?


texts: Migrate / Copy Huge data from Oracle to SQL Server

texts:
I have huge tables in oracle database, appx 1 crore+ (10 million+) rows and want to migrate / copy those table and data in sql server.

Currently I am using Import functionality of SQL Server for this process. But it takes a day for this process and takes too much time.

Is there any better way? Any good outcome or step (SSIS, Any other functional step) to follow for this process?


texts:
I think this is more like an open question: How do you do the ETL for your company?

We are using MySQL as our database management system and we have a production schema. Every month, when we received new data files, we processed the data into that.

The problem is, if directly processing data into the production schema, if anything goes wrong, we have to rollback (if we made backup before). So, currently, I replicate that schema and do the ETL there, once everything is fine, I move those tables into the production one.

However, the size of the production schema is about 250GB (some table rows are more than 800mil), it is difficult to replicate that schema and do the ETL.

So, any brilliant idea? Appreciated!

Additional considerations:

We are MySQL 8.0 Enterprise Edition.
Database is hosted on AWS RHEL EC2.
When using the tables, we may need to refer to all historical data so I don't think partition by month will help...


texts:
I have two tables, one with a single payment information and an id (LFN), on the other hand there are payment positions:
single payment:

payment positions:

goal is to get a table like this:

with our_recno there is one match, through customer id i can find all associated positions. I do this with a subquery. The best solution I get is this:

Do you have an idea how I can repeat the LFN Number instead of getting NULL?
Thank you very much for your ideas.


texts:
I'm using a SQL Server stored procedure to generate an Excel report. Basically each "entry" in the report has three parts: Object, Observation, and Points. The observations have a reference to the primary key of an object, and the points have a reference to an observation. That is the relationship between the parts. (Sorry if my terminology is off, I am used to writing in C# or Java but still learning SQL.)

I want the output to be like this:

-Object

  -All Observations linked to that Object

    - Under each observation there should be all the points linked to that observation

And repeat for each object. I have this working using a cursor for generating all observations with each object, but I can't get the points to come up under each observation.

I have read all over the Internet that cursors are Bad, and I assume this means nested cursors are Very Bad. But this is what I have so far. If you know of a better way to do this, please tell me. I am open to totally refactoring this if necessary.

  DECLARE @Object_Cursor cursor
  DECLARE @Observation_Cursor cursor
  DECLARE @i int
  DECLARE @j int
  DECLARE @row int = 1
  SET @Object_Cursor = CURSOR FOR (SELECT PK_ObjectId FROM Objects)
  OPEN @Object_Cursor
  FETCH NEXT FROM @Object_Cursor INTO @i
    While @@FETCH_STATUS = 0
    Begin
    Select
        PK_ObjectID,
        Name,
        Timestamp
    From Objects where PK_ObjectID = @i

  SET @Observation_Cursor = CURSOR FOR (SELECT @PK_ObservationID FROM Observations)
  OPEN @Observation_Cursor
  FETCH NEXT FROM @Observation_Cursor INTO @j
    While @@FETCH_STATUS = 0
    Begin
    Select
        Timestamp,
        Note,
        User
        from Observations
        where FK_ObjectId = @i
        order by FK_ObjectID

    Select Lat, Lng
    From Pts
    Where FK_ObservationID = @j
    Fetch Next From @Observation_Cursor into @j
    END

    FETCH NEXT FROM @Object_Cursor into @i
    END

    Close @Observation_Cursor
    DEALLOCATE @Observation_Cursor
    CLOSE @Object_Cursor
    DEALLOCATE @Object_Cursor

I feel like the order I'm executing these loops is off somehow, but I haven't been able to get it. Current output is:

 -Object
   -Observations for that object
    -empty points result set

These results are repeated several times for the same object before it goes to the next one, and the points are always empty.

Any help or pointers in another direction is much appreciated.


texts: DB2 on Windows: Fix encoding in db2cmd and for JDBC

texts:
I'm running a DB2 v10.5 FP 7 on a Windows Server 2013 and there is a problem with the encoding setup:

The windows is set to use German(Switzerland) as region setting and input language.

chcp: Active code page: 850

My database is set to UTF-8, see this output from db2 get cfg

 Datenbankgebiet = CH
 Codepage für Datenbank = 1208
 Codierter Zeichensatz der Datenbank = UTF-8
 Landescode der Datenbank = 41
 Sortierfolge der Datenbank = SYSTEM_1252
 Alternative Sortierfolge (ALT_COLLATE)  =

UTF-8 Strings with umlauts are stored correctly in the database (checked by looking at the effective hex-values). However on retrieval, they are mixed up:

The correct value would be '87100 - Überbauung Hinterroos'. Notice how also the default db2 output is shown as 'Sõtze' instead of 'Sätze'.

The incorrect output can also be reproduced by selecting the same rows via JDBC/Java.

IBM Documentation says this behaviour is linked to the DB2CODEPAGE registry variable, which is not set explicitly on the server.

Any ideas how to fix this?

EDIT: (More info as requested by @mao):

The windows default code page is 850 too.
In the registry, codepages are as follows:

ACP 1252
OEMCP 850
MACCP 10000

To make sure I'm not following any wrong leads, I verified the stored string in the table by SELECT HEX(DISPLAY_NAME)... which outputs

3837313030202D20C39C626572626175756E672048696E746572726F6F73

The letter with umlaut is 0xC39C which is CAPITAL LETTER U WITH DIARESIS

Setting the DB2CODEPAGE variable to 1208 changes the output to a different wrong output:


texts: What's the problem with PostgreSQL temporary files?

texts:
I'm studying PostgreSQL architecture and in almost every book or blog post I read people say that we have to track any increasing of temp_files numbers and find the best work_mem size to avoid new temp_file creation.

But none explain why these temp_files are so bad. Could anyone clarify to me why we need to keep the number of temp_files low?


texts: ORA-12546: TNS:permission denied, on Windows through Task Scheduler

texts:
I am trying to export all objects of specific oracle user by executing a batch file through Windows Task Scheduler:

exp.exe myuser/pass@dbname owner=myuser log=mylog.txt

The Oracle 11.2.0.4 x64 and the database are on that same Windows7 Pro machine

The task starts OK. That is to mean that no wrong user or password issues are there. But when task is started by schedule, it says in the log:

ORA-12546: TNS:permission denied

When task is started manually, under same user as the scheduled user (this user is member of Administrators group and ora_dba group, by the way), it works fine and export is done. The task is scheduled with an "Execute with highest privileges" check-box in task properties, so I don't see why there is difference between the "by schedule" and "manual" task start.

I've read a lot on "ORA-12546: TNS:permission denied" on stackoverflow site, but all topics are concerning Linux. I tried to set read and execute permissions on c:\app\oracle, where my Oracle is installed. Actually, Administrators already by default had all permissions on that directory, so it did not make any difference

I noticed that export of tables, however, works fine even in the task started by schedule:

exp.exe myuser/pass@dbname tables=mytable1,mytable2 log=mylog.txt

At any rate, the oracle user “myuser” has oracle rights to export entire database

Problem:
Only-tables are exported fine, but with «owner=myuser» export key I get «TNS:permission denied», and only if Windows scheduled task is started by schedule, while manually started task does everything OK. How do I make it export all, and by schedule?


texts: How can i replicate many database into one, by avoid primary key collision?

texts:
I have n number of databases and I have to collect data from those into one database. The databases has some specific value (e.g. currencies) which I want to update from the collector database(of course I want this to update for all databases).
So in the first case I need to avoid primary key collision because i don't want to loose data. I think that setting up auto_increment.increment and auto_increment.offset, can do the trick, but in the second case i have to identify specific rows in all databases.
What kind of technology and topology can I use?


texts:
Questions

What certifications do you have?
Has your certification (or lack of one) ever worked for or against you?
Do you intend to reach out for certification?
Do you renew your certifications? If so, does your employer actively support the renewal?


texts: Customer table with last order id - violate normalization?

texts: So I'm looking at a customers table that has a relationship with an orders table. There is a trigger in place to set the last_order_id field of the customers table whenever a order it placed. Does having a last_order_id violate normalization rules? Specifically 3rd normal form? And what would be a better methodology for this type of work?


texts: Why are zero length spaces being returned from a Union All against an empty table in SQL Server 2005 but not SQL Server 2012

texts:
Supporting an existing VB6 app with a SQL Server 2005 back end and we're migrating our database server to SQL Server 2012. Created a backup of the production database and restored it into a dev database on both servers. Other than the server version the databases are identical and the Compatibility Level is still set to 90 (SQL Server 2005).

When I run a query on SQL Server 2005, similar to the example below, a string is returned with extra spaces for any blank fields. If I run the same query on SQL Server 2012 there are no extra spaces being returned. As far as I can tell it seems to be an issue with doing a UNION ALL against a table with no records and a char data type but I wanted to ask anyway.

For the below example you'd need to create a table with one NOT NULL, Char(2) column named Unit6. Leave the table empty with no records once created.

CREATE TABLE [dbo].[tblTest](
    [Unit6] [char](2) NOT NULL
) ON [PRIMARY]

When I run the following query I receive two different results depending on the server version:

SELECT
   Unit5, Unit6,
   LEN(Unit6) as Len_Unit6,
   '--|' + Unit6 + '|--' as Spanner
FROM
   (SELECT
       '13' as Unit5,
       '' as Unit6
    UNION ALL
    SELECT
        '',
        Unit6
    FROM tblTest) as MyData

Results on SQL Server 2005

Results on SQL Server 2012

Hopefully you can see the Spanner column shows two spaces when run on 2005 vs zero spaces when run on 2012.

This is a major issue as the app runs queries similar to the above example and then later uses string functions to cut up a single string for database inserts and updates. Without the spaces which are being included on SQL Server 2005 the string functions are carving up the wrong pieces which causes any inserts or updates to fail.

Any information would be most helpful. Thank you!

Server Versions

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

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


texts: How do I export a multi table database with exceptions?

texts:
I am working out of phpmyadmin to export data using an sql.

I have an 88 table database. I would like to export all data to a .csv from 85 of those tables where I am only keeping the rows in which my column Length(Status) > 0.

So far I've come up with being able to select all data from a single table. I would like to apply to my entire database except the 3 tables and export it a .csv.

SELECT * FROM table WHERE LENGTH(Status) > 0


texts: Why does SQL Server require a private key to encrypt a backup?

texts:
I am setting up an experiment where I only have the public key portion of a certificate on the server. The certificate was generated on another server and I did not restore the private key.

When I try to backup a database with encryption using that certificate I get this error:

Msg 15556, Level 16, State 1, Line 15
Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.
Msg 3013, Level 16, State 1, Line 15
BACKUP DATABASE is terminating abnormally.

That's SQL Server clearly telling me it needs the private key for encryption.

But why is that?

My understanding is that BACKUP generates a symmetric key on the fly,  protects the symmetric key using the public key in the certificate and stores it alongside the backup contents. The symmetric key is then used to encrypt the actual backup contents, since symmetric encryption is usually (always?) faster than asymmetric encryption.

In order to restore the backup I would then need the private key that corresponds to the public key used in encryption. That's expected.

I just don't get why the private key is needed during the backup operation. Didn't find an answer on docs or anywhere else. Can someone explain that or point me to some documentation on why that is the case?


texts: How to Log Debug Information About a Stored Procedure

texts:
We have a group of fairly complex stored procedures that process a transaction queue on a round the clock schedule. These scripts use a number of print statements to document what is being done to aid in debugging.

I want to keep this information while it is running in production (as an SQL Server Agent job) to help with troubleshooting when something goes wrong.  I have looked at this site which has a fairly good method of saving the print output but it also has some downsides (output is not available until the script completes, etc). It also requires SSIS or DTS which I have no experience with.

I have thought of changing all of the print statements to insert statements to a log table. The problem is that much of the work we need debug info for is under transaction control and if there is an error, all of the log information would be rolled back with the actual transaction.

Does anyone have a better solution or an idea of how to work either of the solutions I have looked at above?


texts: Log Shipping Backup Successful Restore Errors

texts:
We have a Log Shipping scenario where the log Backups are backed up from the Primary server directly to a Third server (using a UNC path), the backups are then copied from here to the Secondary server for restore.

Occasionally we're getting an issue where on attempt to restore at the Secondary server we get the following error:

  Error: The media family on device '[path]\[dbname]_20170113011502.trn' is incorrectly formed.
  SQL Server cannot process this media family.

When I look at the Primary server logs there is nothing to suggest that the backup failed

  Log was backed up. Database: [dbname], creation date(time):
  2016/06/11(11:55:52), first LSN: 1165941:106198:1, last LSN:
  1166536:6235:1, number of dump devices: 1, device information:
  (FILE=1, TYPE=DISK:
  {'[path]\[dbname]_20170113011502.trn'}).

I'm willing to accept that there might be a disk error or something that's causing the corruption; though I can't see anything in any server logs to suggest network or disk errors.

Three initial questions:

1) If there is a disk write issue for the backup why does SQL Server not see this at backup time?

2) If a trn backup like this fails is there any short cut to keeping the Secondary DB in line that doesn't involve a new full backup and restore from more recent successful log backups. My assumption is that if SQL thinks a log backup was written successfully any transactions in that backup would be missed if we used CONTINUE_AFTER_ERROR or some thing like that.

3) I also assume it's not possible to "redo" a log backup for a particular point in time?


texts:
One of our application AX2013 is connecting to our DB's in an Availability group spanning three SQL2016 nodes.
End users are getting some error in the application that always point to the same error in the eventlog:
"[Microsoft][SQL Server Native Client 11.0]The connection is no longer usable because the server response for a previously executed statement was incorrectly formatted."
The ax-team is looking at the DBA-team to fix this issue but we already changed the timeouts and don't see any other tasks we could do to resolve this issue.
Does anyone have any ideas?
Kind Regards


texts: Transfer data from DynamoDB to SQL server 2008?

texts: I want to transfer data from amazon dynamo DB to SQL server 2008. What are the tools or methods which can be used to do this ?


texts: MySQL: can a table get stuck in the table cache?

texts:
I've asked this question on the ServerFault stackexchange, someone directed me here.

I'm a PHP developer and am responsible for a set of PHP scripts that run on a shared host running FreeBSD, that has a MySQL server (version 4.1.14-standard). Before anyone makes the remark: the decision to use a MySQL 4 server was not made by me, and any decision to upgrade is unfortunately also not mine to make.

Last January, I added a table to the database, and updated the PHP script so that each time the application is started, a row is inserted into this table. I set up the table so it started with a number of rows already in it. In the mean time, our client has been merrily using their application, causing several rows to be added to the table.

Last week, I wrote a PHP script that fetches all rows from the table and builds a report from the result, and ran it, only to find that several rows were missing. In fact, only the rows I started off with, seemed to be present in the report. I attributed this to no-one having used the application in the mean time. Yesterday, however, I decided to check if this was actually true. So I fired up phpMyAdmin, exported the database, and ran the report locally, only to find new data present and accounted for. So I checked if the code on the live site was up to date, and this is the case. Stumped, I ran the report on the live site again, and presto: there the missing rows were.

My reasoning:

The issue has to be some kind of cache thing, because the table has two DATE columns, they are filled with INSERT queries, and possibly updated with UPDATE queries, each time with NOW() as the value, never any other value, and the dates in the table were spread across the past half year. Last week I saw no new rows since January, but yesterday morning I saw all these rows with these dates in them: the new rows cannot all have been added in the past week.
I did not publish any code, or push data to the MySQL server, since I first ran the report.
It can't be a browser cache issue, because I wrote the report script last week: if it were a browser cache issue, it would have been a cache entry from January. Since the script did not exist then, the information can't come from my browser cache; and besides, we web developers clear our browser caches weekly, if not several times per day and I am no exception.
If it's a cache issue and it's not a browser cache issue, it has to be a MySQL cache issue.

However, it can't be a MySQL cache issue either, because the query cache gets invalidated as soon as a new row is inserted, which has happened several times the last half year. It also can't be the table cache, because table_cache is set to 64, open_tables is 64, and opened_tables is about 13 million. So if there is something wrong with the table cache at all, I'd expect "too new" data, instead of "too stale" data.

My working theory, for lack of a better one, is that somehow, despite my reasoning, the table got "stuck" in the MySQL table cache, and that exporting the table data with phpMyAdmin caused the table to "unstick".

All database queries the application does, are done with PHP's mysql_* functions: again, not my decision: I'd much rather use PDO, but am stuck with PHP 4. Anyway, the application does not use Memcached, so that's ruled out.

My question is: why did I get the older version of the table data, and is there any way I can prevent this from happening again?

The table in question:

CREATE TABLE `offset` (
  `id` int(10) NOT NULL auto_increment,
  `version` int(10) NOT NULL default '0',
  `user_id` int(10) NOT NULL default '0',
  `client` varchar(255) NOT NULL default '',
  `created_on` datetime default NULL,
  `modified_on` datetime default NULL,
  `hidden` tinyint(1) default '0',
  PRIMARY KEY  (`id`,`version`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=302 ;


texts: SSIS Data Flow Error Output runs all the time

texts:
I've a SSIS Data Flow Task. Within which i've souce flat file and OLEDB destination. I've an error output sent to redirect row on OLEDB Destination.
The problem is that the redirect row always run even though there's no error. Is this a bug with SSIS package? If someone could help, will be appreciated.
e.g.


texts:
I am in a Supply Chain Masters program and need to install the Excel Data Mining add-in for Excel. I downloaded and installed Microsoft® SQL Server® 2012 SP1 Data Mining Add-ins for Microsoft® Office®.

This package includes two add-ins for Microsoft Office Excel (Table Analysis Tools and Data Mining Client) and one add-in for Microsoft Office Visio 2010 (Data Mining Templates). The add-ins are supported on Office 2010 and Office 2013.

Once installed, I cannot connect to localhost. I run two separate accounts: an admin account and a regular account. I've tried starting the service and it doesn't work.

I've tried to uninstall and reinstall several times. Any help would be greatly appreciated.


texts: Full text search over multiple CPU processes?

texts:
Is it possible to configure Postgres to split out a full text search over multiple CPU processes in an attempt to complete quicker?

I'm running a full text search on 2 million records on a GIN indexed tsvector column, where the source text is about 10,000 characters long.

I have way more CPU than is being used during the search, so I feel like splitting the search over 4 processes in batches of 500k would allow it to run the search concurrently and therefore complete faster.

I'd be interested to know if anyone has tried this or implemented their own equivalent programatically in SQL.


texts: Prevent users from using power pivot excel connections to a database

texts:
A couple of weeks ago my boss connected to our production database via excel and was able to query the database.  Because of how we're doing security to the database (AD groups) he should have access to the database.  I'd rather not have users just willy nilly creating pivot tables against our OLTP databases.  Is there a way to prevent these sort of connections?

The way our security works, when a user accesses the application, it checks the AD group, and if the user is in the group the application lets them in.  We use the same AD group to allow access to the database, the application passes the user's information to SQL Server.


texts: Can I create and restore partial backups with mariabackup on MariaDB 10.1.23?

texts: I read in Percona xtrabackup manual that xtrabackup require MySQL 5.6 for restoring partial backups (only some databases or tables). But there are nothing about MariaDB. How about mariabackup? I have MariaDB 10.1.23. Does mariabackup support partial backup and restoring for this version?


texts: Update a valid column when another column has changed

texts:
We have a valid_phone column that just stores 1 or 0 for valid or not valid.

When the user changes their phone number we want that field to go back to 0 so they will have to validate their number again. If it's not changing we want to respect whatever value was there, so not always just assuming that if it didn't change set it to 1 because they may have not validated their phone yet so it should in that case stay at 0.

I saw a query that used a CASE and thought it may work for us but so far I have not gotten it to work.

UPDATE text2bid_users SET valid_phone = CASE
    WHEN phone NOT '0000' THEN '0'
END WHERE user_id = '1234'

I'm sure my syntax may be wrong or I may even be looking in the wrong place for a solution as I'm a front end guy just starting to dip into backend and database.

My thought on this is to see if phone is already set to the phone I'm about to update it with (so that wouldn't be hard-coded) and if it is not the same then set valid_phone to 0 and then outside of this name, email, address etc. will also update as well.

How would I get a query to do what we need it to do here?

I might be updating other values too, so I don't want it to not update the other fields if the phone comes through as the same. I suppose I could run two queries but I feel like I should be able to do it in one query. I may be wrong.


texts:
I wrote the below query which works just fine.
The PARAM_ID has a CLC_Q<number> form.

SELECT * FROM DB.TABLE
    WHERE PARAM_ID LIKE 'CLC_Q%'
    AND SUBSTR(PARAM_ID,6) BETWEEN 35 AND 45
    ORDER BY SUBSTR(PARAM_ID,6) ASC;

It might be a logical mistake , but I can't do the below,

SELECT SUBSTR(PARAM_ID,6) PAR_ID FROM DB.TABLE
    WHERE PARAM_ID LIKE 'CLC_Q%'
    AND PAR_ID BETWEEN 35 AND 45
    ORDER BY PAR_ID ASC;

Although it's a different question, I can't also do this,

SELECT SUBSTR(PARAM_ID,6),* FROM DB.TABLE -- The change is in this line
    WHERE PARAM_ID LIKE 'CLC_Q%'
    AND SUBSTR(PARAM_ID,6) BETWEEN 35 AND 45
    ORDER BY SUBSTR(PARAM_ID,6) ASC;

Version

Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production

Any further suggestions on how to improve my query would be much appreciated.


texts: How to select events based an adjacent times?

texts:
I have a table rooms which contains information regarding events that take in rooms. This is the definition of the table:

create table room(
  sdate date,--start date
  stime time,--start time
  rno int,--room no
  bno int,--building no
  eid int,--event id
  duration interval,
  primary key(sdate, stime, rno, bno, eid)
);

I want to find all events that are adjacent to one another, that is if event x finishes at 10:00 and event y starts at 10:00 (provided they're in the same room, building and date of course) these events are adjacent in terms of time.

I'm new to SQL and I wrote a query to find such events using 5 subqueries and one union which seems to me to be over the top. I'd like to learn the best practices therefore is there a better, more concise way to perform such a query?

This is the query:

with
  --calculate end times (etime)
  schedule_w_etime(sdate, stime, rno, bno, etime) as (
    select sdate, stime, rno, bno, (stime + duration) as etime from schedule
  ),
  --find adjacent events
  result(sdate, stime, rno, bno, etime) as (
    select * from schedule_w_etime as S
    where S.etime in (select stime from schedule_w_etime as T where S.sdate=T.sdate
                                                                    and S.rno=T.rno
                                                                    and S.bno=T.bno)
  ),
  --get the last event from result
  max_time(date, start_time, room_no, build_no, end_time) as (
    select * from result
    where etime=(select max(etime) from result)
  ),
  --lookup the start time of the last event
  lookup_max_time(sdate, stime, rno, bno, etime) as (
    select * from schedule_w_etime as S, max_time as M
    where S.stime=M.end_time and S.bno=M.build_no and S.rno=M.room_no
  ),
  --format lookup_max_time to only relevant columns
  last_event_final(sdate, stime, rno, bno) as (
    select sdate, stime, rno, bno from lookup_max_time
  )
  --union of the last event and all the other events from result
  (select * from last_event_final)
    union
  (select sdate, stime, rno, bno from result)

As you can see my main problem is with displaying the last event because in the result view doesn't contain the start time of the last event.


texts:
We have a full text catalog on one of our databases but one keyword does not work.

e.g:

select * from table where name = 'jon' -- result set returns as expected
select * from table where Contains(a.Name, '"jon"') -- no result set

What might be causing this?

Thank you.


texts: Most efficient table structure for multi-tenant database

texts:
I have a conventional relational database (mySQL) with several entities stored in their own table. Three examples are images, events and organisations.
We are making a change to the way our site works - from now on Clients will upload / add their own data, which will only be accessible to them. We store the Client data in the client table, each client has an id.
There are 3 simple ways I can think of to design this, but I want the most efficient, as some calls will have to select 10,000+ records for that Client (in the case of a large world map of events, for example). So which if these ways should I use to ensure that all data a Client "owns" is recalled with maximum efficiency?

Many to many relation to each type of entity. -

client.id ... | client_id, image_id | image.id ...
(and create a many to many table for each of the dozen entities involved). V simple, but could get in a tangle as we add more and more entity types, many of which have relations of their own. New many to many tables will need to be added regularly

Polymorphic association one table to associate client_id with any type of entity. Most efficient in terms of layout but slow to find all entities belonging to one client.

client.id  ... |  client_id, entity_type, entity_id  |  image.id ...

Add owner (client_id) column directly to each entity table

client_id, image_id, image_name etc... (all in the image table, same for each table)
Is there any reason not to add the client ID directly? It would mean storing a few more integers than the other methods but would be extremely fast to select all images 'owned by' a client (could be tens-of-thousands+)


texts: Selecting values that are not associated with one side of a many to many relationship

texts:
I have a junction table between categories and values that indicate which categories are associated with which each value. I would like to find the values that are not associated with each category.

Category:

ID CategoryName
-- ------------
 1 category1
 2 category2
 3 category3

CategoryValue:

CategoryID ValueID
---------- -------
         1       1
         1       2
         2       1
         3       2

Value:

ID ValueName
-- ---------
 1 value1
 2 value2
 3 value3

The output I would like for the query is the following:

CategoryID ValueID ValueName
---------- ------- ---------
         1       3 value3
         2       2 value2
         2       3 value3
         3       1 value1
         3       3 value3

I'm pretty stuck on how to approach this as a query. The "junction" table seems to prevent the usual LEFT JOIN WHERE null approach. So any advice would be much appreciated.


texts: Is an Oracle SID UPPERCASE or lowercase?

texts:
This may sound silly, but is an Oracle Service Identifier (SID) aka database name normally in upper or lowercase?

According to the documentation, a SID "can contain only the characters a-z, A-Z, and 0-9", so both upper and lowercase is allowed. In my experience, it usually doesn't really matter. However, I am installing Oracle 11.2 on a file system that is case sensitive, and a lot of directories and files have the SID in them (e.g. initMYDB.ora).

EDIT:

I found a quote saying that the SID in the password file name (orapwMYDB) is case sensitive and must match the instance name.


texts: How to handle null value number DataWarehouse

texts:
I have a question.

I'm developing a Data Warehouse, I retrieve the data from my reconciled level, this in order to keep updated my Data Warehouse, but i am not sure how to handle some numeric fields that have a null value.

I was thinking to replace it with some special numeric value like -1 for example, that never appear in my column list. But during the analysis process this value replaced by -1 they must not be considered.

Now my question is, is better leave the null numeric field to null, or replace it with some numeric value?


texts: need information on a deleted SSRS report

texts:
In SSRS, once a report is deleted, the ItemPath no longer appears in the ExecutionLog because the Catalog no longer has the report information.  I can track down the ReportID, but I can't find anywhere with the history of the report name.

Is there anywhere I could find the name and path of the deleted report?  If not, any suggestions on how to best stash the information moving forward?


texts: Restore a database but exclude certain indexes

texts: Is there a way to exclude certain indices when restoring a database? I see that there is no option to do this in pg_restore, but I was hoping there may be some other way.


texts: Sending one email when all jobs completes successfully

texts: I need to create backup jobs over several instances, I wonder if there is any way to send a single notification via mail only when jobs from all instances completed successfully.


texts: How to copy & paste "Missing Index" in "Execution plan" tab of SSMS?

texts:
Notwithstanding that blindly creating suggested missing indices is less than ideal, is there a way to copy and paste the suggested missing indices from the "Execution plan" tab in SQL Server Management Studio?

You get a tool-tip on mouse hovering over it but there does not appear to be a right-click menu copy them.  For years I've just typed out any I needed but am curious if the base form can be easily gotten from the tab.

Using SSMS 2017


texts: Supporting non-ascii characters in Postgres full text (english)

texts:
We're implementing Postgres full-text search. While the bulk of the text is normal English, there are a number of place names which include diacriticals (ñ,á,ó, etc)

More specifically, there are words like cañon -- I want to make sure that users who type canon also get matches for cañon.

Is there specific support for this? Or is the solution simply to manually manage the specific special cases using a thesaurus?


texts: Overwhelmed with Sensor Data - Excel to SQL Server fails

texts:
I'm managing a lot of sensor data and need to do charting, smoothing, stats over time on the data. Currently I import the data from flat files using VBA to do ETL into Excel.

The format of the data is:

[DataTime][SystemID][SystemParams 1][SystemParams ..n][Data 1][Data ...n]

There are 30 System Parameters and 8000 datapoints in each file.
All this is fine in Excel where I can manipulate, filter and pivot off the system parameters and then chart the Data over time.

But I want to get all rows out of Excel into a database as there will be 10K files showing up every week  in this format.

When I export the rows from Excel to SQL Server (using the SQL Server import/export tools I only get 200 columns of the 8000+ from my Excel row due to the column restrictions in SQL Server

What would be the correct way of structuring this data so that I can run queries on the System Parameters an retrieve the 8000 data points back into Excel for charting?

I'd like to stay in SQL Server but have read I might be better off with NoSQL. All advice gratefully accepted..

Overwhelmed


texts:
We are having a recurring situation where we have 2 one query hanging, and other queries waiting for that one, and our DB grinds to a standstill. I am trying to debug this and am seeking help. The DB is RDS running Postgres 10.6.
I use DBeaver to monitor and admin this DB, and here are the screenshots from the Administer -> Lock Manager in that application. In this case, to resolve this issue, I needed to kill the one session the others are waiting on, but I would like to learn how to debug this deeper to figure out what it is waiting on, and why.

There are multiple copies of the above query because it is being generated by a very frequent cron job, so that is atleast expected.

According to RDS -> Performance insights, this query, and a lot others are spending a lot of time at this point in the lock state relation.
I expect I havent given enough information here. Let me know what I can add.


texts:
In SSIS, how do I get the date for a specific day of the week? For example, how do I get last Sunday's date?

I assume I have to do some subtraction on the day-of-week, but not sure:

DATEADD("d", -DATEPART("DW", GETDATE()), GETDATE())


texts: Count number of common column names among all tables in a database

texts:
Is there any way to compare every table with each other in a database, listing the number of common columns between each tables? I prefer using join over subquery and information_schema.columns over sys.columns, if possible.

For example, if I have 4 tables, I'd expect output like this:

list1        list2        commonColumns
-----       --------     --------------------
table1     table2         1
table1     table3         2
table1     table4         0
table2     table1         1
table2     table3         5
table2     table4         2
table3     table1         3
table3     table2         0
table3     table4         1
table4     table1         2
table4     table2         3
table4     table3         0


texts: How to access table datatype like an array?

texts:
I tried to store data in a table variable @coordinates, which contains 2 rows and 2 columns: latitude and longitude.

To store value:

declare @coordinates table(latitude decimal(12,9), longitude decimal(12,9))
insert into @coordinates select latitude, longitude from loc.locations where place_name IN ('Delhi', 'Mumbai')

to see the value:

select * from @coordinates

result:

latitude    longitude
28.666670000    77.216670000
19.014410000    72.847940000

But I want to access the values one by one like:
@coordinates[1][1] or @coordinates.latitude[1]

or any easy way possible?


texts: How to transfer table in SQL Server 2008 from one computer to another

texts:
I want to transfer my table in SQL Server 2008 from one computer to another... Is it possible?

How to transfer from one computer to another? I am doing my final year project. In my project, if the table contains more number of rows means it will divide the table into smaller partition and transfer that partition to another computer.. is it possible to transfer?


texts:
I'm trying to understand mongodb queries, but one of my queries is returning a record that it shouldn't.

I am trying to call

db.user.find({'referral.money':{'$ne':0, '$ne':null}})

to get all users who have some cents in their referral data. Some users have referral data which doesn't include "money" and others don't have any referral data at all.

The problem is that after executing that command, the following record is returned:

{
    "_id" : ObjectId("58362414af5d810d1c9ec7f0"),
    "user_id" : 22,
    "referral" : {
        "_cls" : "ReferralProgramA",
        "end_dtm" : ISODate("2016-12-23T23:19:48.014Z"),
        "money" : 0
    }
}

If I'm correct it shouldn't be returned since "money" is obviously 0.

My collections and records are created with python's mongoengine hence the "_cls" fields for inheritance.

I'm making the find({}) call with a raw statement so mongoengine isn't messing it up( I think ).


texts: Different types of users in one table, How to add data off different users

texts:
I have 5 type of users:

Super Admin
Coordinator
Supervisor
Student
External Examiner

All of them have some common attributes like ID, name, email etc. I have inserted them in one table user differentiating them with user_type attribute.

I have also made different tables for each user like Coordinator, Student because each user have different attributes.

My Question is:

  if I want to add a coordinator user into table, I can add it into user table with user_type=Coordinator but how will I link this coordinator from user table into 'coordinator' table?

Can anyone provide me with PHP-MySQL code for it?


texts: Sql Server Fill Factor - Restart sql server services

texts:
I changed the Fill Factor option from 1 to 75.

When I run a query that displays the fill Factor I am seeing 75

But I read online that I have to stop and start the sql Server services inorder for this change to be effective. Is that True?

http://sqlmag.com/database-administration/fill-factor-truth

  You must stop and restart the SQL Server service for the change to take effect. The new fill factor will be in effect when you see it in the run-value column


texts: Best Strategy to remove the unused or duplicate indexes

texts:
I have searched the Internet a lot about this but I'm still not comfortable making a decision.

One of our reporting databases is 350 GB (it acts as a subscriber for transaction replication where indexes are not moved during replication), the index file group was created almost 4 years ago residing on its own drive.

This drive is now almost full with index files occupying 200 GB created for various tables involved on subscribed database.

My main goal is to start cleaning the 3 most used tables. Below are the details:

Table 1: 88 GB with 98659075 rows. Number of nonclustered indexes=88
Table 2 : 52 GB with 17793712 rows. Number of nonclustered indexes=52
table 3: 48 GB with 8666642 rows. Number of nonclustered indexes=44

The number of indexes definitely seems large, but since this is a reporting db server does that sound reasonable? Is there a specific number of indexes on the same table which raises a red flag for performance (just want to know what you guys think)?

Secondly, I have gathered a months data from the dmvs dm_db_index_usage_stats,dm_db_missing_index_columns, dm_db_missing_index_group_stats,dm_db_missing_index_groups to decide which indexes I can safety remove. So, just want to know what should be my strategy here based on above gathered data to delete the indexes for better performance and to save the disk space as well?


texts:
The moment I log into SQLPLUS I get an SP2-0556 error. I recently moved OS but am connecting to the same database so I am not sure if it's because I'm using my old TNSNames or what. I can connect and work normally but a few issues have arisen with running SQL scripts due to this where they then say Invalid File Name.

Any help would be greatly appreciated, thanks.

the database is 11g,
the command I use for running scripts is a custom ant script that basically loops through and runs normal .sql scripts,
and I changed from a Ubuntu system to Mac OS X.

The issues are that any new scripts I create (that I know work) give a Invalid File Name error then stop.


texts:
I have run SQL Profiler and I can see that I'm "missing" some indexes (non-clustered) on a table. How is it best to handle indexes that can be a "subset" of another index?

Index 1

UserId, InstanceId, Created

Index 2 (Subset of Index 1)

InstanceId, Created

Is index 2 necessary in this case?

Can't really found the query that caused Index 2

Query (using Entity framework):

SELECT 1 AS [C1], [Limit1].[InputForm] AS [InputForm], [Project2].[UserId] AS [UserId]
FROM (SELECT [Distinct1].[UserId] AS [UserId]
    FROM (SELECT DISTINCT
        [Extent1].[UserId] AS [UserId]
        FROM [dbo].[Dat_GameCompletionData] AS [Extent1]
        WHERE ([Extent1].[InstanceId] = @p__linq__0) AND ([Extent1].[Created] >= @p__linq__1)
    )  AS [Distinct1] ) AS [Project2]
OUTER APPLY  (SELECT TOP (1) [Project3].[InputForm] AS [InputForm]
    FROM ( SELECT
        [Extent2].[InputForm] AS [InputForm],
        [Extent2].[Created] AS [Created]
        FROM [dbo].[Dat_GameCompletionData] AS [Extent2]
        WHERE ([Extent2].[InstanceId] = @p__linq__0) AND ([Extent2].[Created] >= @p__linq__1) AND ([Project2].[UserId] = [Extent2].[UserId])
    )  AS [Project3]
    ORDER BY [Project3].[Created] DESC ) AS [Limit1]


texts: Can I add a primary key automatically for all tables?

texts:
I have a database (postgresql) with 10 schema and every schema has 10 tables. For some reasons, some tables do not have a primary key.

Is it possible to create a function/trigger that can look for all tables and

add a column id_pk serial (if the column does not exist)
and make it a primary key (if the table has not any primary key)?


texts: SQL Server 2019 executes unreachable code

texts:
[Update: This question describes a bug which has been fixed in Cumulative Update 5 for SQL Server 2019.]

Consider the following repro example (fiddle):
CREATE FUNCTION dbo.Repro (@myYear int)
RETURNS datetime
AS
BEGIN
    IF @myYear <> 1990
    BEGIN
        RETURN NULL
    END

    DECLARE @firstOfYear datetime;
    SET @firstOfYear = DATEFROMPARTS(@myYear, 1, 1);

    IF DATEDIFF(day, @firstOfYear, @firstOfYear) <> 0
    BEGIN
        RETURN NULL
    END

    RETURN @firstOfYear
END
SELECT dbo.Repro(0);

Obviously, that function should return the first of January 1990 if the input is 1990, and NULL otherwise. Yes, I know that DATEDIFF(day, @firstOfYear, @firstOfYear) <> 0 is a nonsensical operation. This is a mcve to demonstrate a potential bug, not production code.
Now let's execute SELECT dbo.Repro(0) on SQL Server 2017 and SQL Server 2019.
Expected result: NULL.
Actual result on SQL Server 2017: NULL
Actual result on SQL Server 2019:

Msg 289 Level 16 State 1 Line 1
Cannot construct data type date, some of the arguments have values which are not valid.

Apparently, SQL Server 2019 executes some of the code below the initial guard clause (IF @myYear <> 1990) even if it shouldn't.
My questions:

Is this expected behavior, or did I find a bug in SQL Server 2019?
If this is expected behavior, how do I correctly write a guard clause validating input paramters?


texts:
Consider we have a large set of statistical data for a record; e.g. 20-30 INT columns. Is it better to keep the entire set in one table as they all belong to a record OR creating a another table connected with a one-to-one relationship.

The advantage of the former is to avoid JOIN and have a quick access to all statistical data for the corresponding record.

The advantage of the latter is to keep the column tidy. The first column is read-intensive, and the second write-intensive. Of course, I think it has no significant effect on the performance, as I use InnoDB with row-level blocking.

In general I want to know if it is practical useful to separate different sets of data for a single record?


texts: Why is there no implicit conversion when using FORMAT vs CAST/Convert in CONCAT

texts:
When we have the following table:
CREATE TABLE Test1 (
    Name NVARCHAR(50) NOT NULL
    , Code SMALLINT NOT NULL
)

With only 1 row for demo purpose:
INSERT INTO Test1(Name, Code)
VALUES ( 'ABC', 1)

And the following queries:
SELECT CONCAT(Name, ' - ', Code)
FROM Test1

SELECT CONCAT(Name, ' - ', CONVERT(NVARCHAR(2), Code))
FROM Test1

SELECT CONCAT(Name, ' - ', CAST(Code AS NVARCHAR(2)))
FROM Test1

SELECT CONCAT(Name, ' - ', FORMAT(Code, '0'))
FROM Test1

The first 3 queries have a warning:

Type conversion in expression (...) may affect "CardinaltiyEstimate" in query plan choice.

But why is FORMAT behaving differently ? What happens behind the scenes to eliminate the implicit conversion?
Is there any other way then format so you are not impacted by the implicit conversion?
Queryplan


texts: MySQL: change column type using expression

texts:
In a MySQL database, I need to convert some UUID columns (BINARY(16)) into VARCHAR. I want to convert them in the canonical form, not as bytes. For clarity, in lowercase and with hyphens, and not in the 0xHEXADECIMAL format.

In Postgres, this is enough:

ALTER TABLE my_table
  ALTER COLUMN my_uuid_column
  TYPE VARCHAR(36);

The MySQL equivalent returns an error:

ALTER TABLE my_table
  MODIFY COLUMN my_uuid_column
  TYPE VARCHAR(36);

ERROR 1366 (HY000): Incorrect string value: '\x.....' for column 'my_uuid_column' at row 1

And even if it worked, the result wouldn't really be in canonical form.

I know that in Postgres I can resolve this with USING expression:

CREATE FUNCTION foobar(foo uuid) returns VARCHAR
    LANGUAGE SQL IMMUTABLE STRICT AS $$
  SELECT 'foobar' -- format the UUID using some helpers.
$$;

ALTER TABLE my_table
  ALTER COLUMN my_uuid_column
  TYPE VARCHAR(36)
  USING foobar(my_uuid_column);

And it does what you'd expect.

I can't find a way to do something similar in MySQL.


texts: Query for returning query plans from sql plan cache that contain 1 or more warnings?

texts: Is there a query I can run to return query plans from sql plan cache that contain 1 or more warnings?


texts:
I've just started a new job about a month ago working for a software company that is really understaffed. I've been tasked with modernizing some of this old non-ansi SQL code and I'm having a particularly tough time trying to crack open this blerb. I'm recently out of school so I'm marginally familiar with modern SQL standards and from the research I've done the deprecated piece would relate to the non-ansi joins *= . I've tried pretty much every way I can think of altering this mess and I'm at a loss.
Thanks for your assistance.

Select @SQLCmd =
'SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS     Item_Number, Alternate_ID, Description1, ' +
    'Company_ID, Department_ID, Group_Id, Section_Id, Media_Code, ' +
    'Location_Current, Location_Home, Hold_Status, Record_Type,   Disposition_Status, ' + @TableName + '.Retention_Code, ' +
    'Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status ' +
'FROM ' + @TableName + ', RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS ' +
'Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV ' +
'WHERE ' + @TableName + '.Retention_Code *= RETENTION.Retention_Code AND ' +
    @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type ' +
Case LTrim(@RetnType)
    When '' Then ''
    Else 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
End
+ ' AND ' + @WhereCond +
' UNION ' +
'SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS Item_Number, Alternate_ID, Description1, ' +
    'Company_ID, Department_ID, Group_Id, Section_Id, Media_Code, ' +
    'Location_Current, Location_Home, Hold_Status, Record_Type, Disposition_Status, ' + @TableName + '.Retention_Code, ' +
    'Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status ' +
'FROM ' + @TableName + ', RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS ' +
'Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV ' +
'WHERE ' + @TableName + '.Retention_Code *= RETENTION.Retention_Code AND ' +
    @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type ' +
Case LTrim(@RetnType)
    When '' Then ''
    Else 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
End


texts: Get-MSSQLLinkPasswords Powershell script doesn't seem to work under SQL Server 2016

texts:
I've been using the Get-MSSQLLinkPasswords Powershell script for a long time and it has been a huge help.  The script decrypts the passwords for all linked servers on a particular Windows server and shows them to you in plain text. This functionality is even incorporated into the dbatools Copy-SqlLinkedServer script.

There are security caveats for running the script as show here

  The script must be run locally on the MSSQL server (as DPAPI requires
  access to the local machine key). The user executing the script must
  also have sysadmin access to all the database instances (for the DAC
  connection) and local admin privileges on the Windows server (to
  access the entropy bytes in registry). In addition, if UAC is enabled,
  the script must be ran as an administrator.

This script has worked flawlessly until recent upgrades to SQL Server 2016.  I was wondering if anyone else has encountered any issues with this script under SQL Server 2016.  Perhaps some security model has changed in SQL Server 2016 and this script is unable to decrypt the passwords now.

As time permits, I will try to see if the dbatools Copy-SqlLinkedServer script still works on 2016.


texts:
SQL Server Change Data Capture is a feature that reads historical data from the SQL Server transaction logs and stores them in a special table.

Through the use of special table value functions (TVF) it then allows the user to query this data, making it either possible to get all the changes on a specific table or only the net changes that resulted from the changes within a specific time.

CDC has certain advantages

It can be configured to only track certain tables or columns.
It is able to handle model changes to a certain degree.
It does not affect performance as heavily as triggers because it works with the transaction logs.
It is easily enabled/disabled and does not require additional columns on the table that should be tracked.

It also has some disadvantages:

The amount of history data can become huge fast.
You are not able to track who made the changes (at least not for deletes).
The history data takes some time to catch up, because it is based on the transaction logs.
It depends on the SQL Server Agent. If the Agent is not running or crashes, no history is being tracked.

I have read quite a lot about CDC and while I know now how to use it, I am still not sure if it is the right tool for me.

For which tasks/scenarios is CDC the right tool? (e.g. Allowing users to restore a data object to a certain point in time? Auditing? Showing the complete history of data?)
When should you rather not use CDC, but resort to a custom trigger-based solution?
Is it ok to use CDC in an operational database and make use of the CDC data within an operational application? (e.g. showing it to the end user) Or is this clearly a misuse of this feature?

I commonly hear that CDC is an audit tool, but isnt that what SQL Server Audit is for? Are they both different tools for the same task? Or can CDC used for other things?

My current scenario is that I am asked to build a reliable data framework which is supposed to be the basis for multiple future applications. The exact requirements are blurry, but one is that it should be able to track data history and restore older entries together with all related data from other tables. I am evaluating CDC right now as an option, but am uncertain if this is the way to go, because I can't really find any recommended use cases.

While I appreciate advice for my specific scenario, answers should give general advice about when or when not to use Change Data Capture.


texts:
I am new to SQL world. I am practising on postgresql pgguide sample database . I wanted to count all the purchases of users who have bought a product 'Williams Selden'.  So i write something like below

select Count( p1.name) , u1.email from users as u1 INNER JOIN
purchases as p1 ON u1.id=p1.user_id
where u1.email IN (select u.email from users as u
INNER JOIN purchases p ON u.id=p.user_id
where p.name='Williams Selden') group by u1.email;

RESULT

 count |           email
-------+----------------------------
    15 | Shanell.Maxson@gmail.com
    23 | Ozella.Yoshimura@gmail.com
    14 | Isabel.Breeding@gmail.com

Now i want to know what all they purchased. Something like below.

However i am not able to do that. Please help.
I wrote

select DISTINCT p1.name , u1.email from users as u1 INNER JOIN
purchases as p1 ON u1.id=p1.user_id  where u1.email
IN (select u.email from users as u INNER JOIN purchases p ON
u.id=p.user_id where p.name='Williams Selden') group by u1.email,
p1.name;

As per first comment i removed the groupby and count and now i get something like. Maybe i am wrong at it, i do not want email with every record, i want it in rows just where  user email changes. Can it be done? If not, can i know why?

select p1.name , u1.email from users as u1 INNER JOIN purchases as p1
ON u1.id=p1.user_id
where u1.email IN (select u.email from users as u INNER JOIN
purchases p ON u.id=p.user_id where p.name='Williams Selden') order by
u1.email;

      name         |           email
----------------------+----------------------------
 Pauletta Arends      | Isabel.Breeding@gmail.com
 Miyoko Potter        | Isabel.Breeding@gmail.com
 Wendie Westmoreland  | Isabel.Breeding@gmail.com
 Williams Selden      | Isabel.Breeding@gmail.com
 Jettie Dilks         | Isabel.Breeding@gmail.com
 Catina Kiser         | Isabel.Breeding@gmail.com
 Bradly Vasko         | Isabel.Breeding@gmail.com
 Inocencia Cousineau  | Ozella.Yoshimura@gmail.com
 Cammy Bough          | Ozella.Yoshimura@gmail.com
 Danyelle Haefner     | Ozella.Yoshimura@gmail.com
 Ricarda Goldsberry   | Ozella.Yoshimura@gmail.com
 Cherryl Arends       | Ozella.Yoshimura@gmail.com
 Susanna Melendez     | Ozella.Yoshimura@gmail.com
 Granville Junge      | Ozella.Yoshimura@gmail.com
 Jerald Schuh         | Ozella.Yoshimura@gmail.com
 Miyoko Allbright     | Shanell.Maxson@gmail.com
 Tommie Ange          | Shanell.Maxson@gmail.com
 Williams Selden      | Shanell.Maxson@gmail.com
 Isabel Tripodi       | Shanell.Maxson@gmail.com


texts:
I installed mysql version 5.5.9 on my mac and I tried to start it using this command :

sudo /usr/local/mysql/support-files/mysql.server start

mysql didn't start with this command. I checked the localhost.err file in data directory and it was like this :

110227 22:51:14 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
110227 22:51:14 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
110227 22:51:14 [Note] Plugin 'FEDERATED' is disabled.
/usr/local/mysql/bin/mysqld: Table 'mysql.plugin' doesn't exist
110227 22:51:14 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
110227 22:51:14 InnoDB: The InnoDB memory heap is disabled
110227 22:51:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins
110227 22:51:14 InnoDB: Compressed tables use zlib 1.2.3
110227 22:51:14 InnoDB: Initializing buffer pool, size = 128.0M
110227 22:51:14 InnoDB: Completed initialization of buffer pool
110227 22:51:14  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
110227 22:51:14  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
110227 22:51:15 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
110227 22:51:15  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
110227 22:51:15  InnoDB: Waiting for the background threads to start
110227 22:51:16 InnoDB: 1.1.5 started; log sequence number 1595916
110227 22:51:16 [ERROR] /usr/local/mysql/bin/mysqld: unknown option '--skip-locking'
110227 22:51:16 [ERROR] Aborting

110227 22:51:16  InnoDB: Starting shutdown...
110227 22:51:16  InnoDB: Shutdown completed; log sequence number 1595916
110227 22:51:16 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

110227 22:51:16 mysqld_safe mysqld from pid file /usr/local/mysql/data/localhost.pid ended

I deleted the two ib_log files, I changed my.cnf file and I got nothing.


texts: Best way to arrange a hairdresser database based on their skills

texts:
I need some help in a smart way to organize a database for a hairdresser shop.
So far, I understand that I need a table for the hairdressers containing usual data such as id, name, etc.
I also think I need a table for the procedures the hairdressers can perform. This table would contain the procedure id, description, time required, cost, price and so on.
But I don't see a good way to link those two tables. For example: Professional A can do procedures #1 and #3, but not #2.
How to include this info in the database?
I thought of two possible ways:

Make the procedures be columns in the Professionals table, and assign true in the column in case a professional can perform that procedure;

Make the professionals be columns in the Procedures table, assigning true if the procedure can be performed by that professional.
Either possibilities don't see quite the best way to achieve what I want, specially if the number of professionals/procedures get large.

Since I have very little experience in this, I count on your help giving me some insights in this matter.
Thanks in advance.


texts:
I must create an DBF for an application which will collect data from 5 millions sensors on every minute, so I will have 5 millions records every minute.

I was thinking to use SQL server because I will have 7,2 billions records every day. But when I tested the writing speed on my PC (Dual Core E6300, @ Gb RAM, HDD 500Gb SATA) I found that Visual Foxpro 9 is registering 1 million records 100 times faster than SQL Server 2008.

SQL Server, 2.55 minutes:

declare @i int=1
while @i<1000001
begin
 insert into dbo.sensor (sensorid, datetime, value) values (1,getdate(),@i)
 set @i=@i+1
end

VFP9, 2 seconds:

? DATETIME ()
FOR  i=1 TO 1 000 000 STEP 1
 INSERT INTO Table1 (sensorid, dtime, value, status) VALUES (i, date(), i, "S")
ENDFOR
? DATETIME()

I mention that there was no Index, and the table will be read only. What is wrong?


texts: how to update the value of a period contained inside a bigger range