java.sql.SQLException: Login failed for user 'sa' I am having trouble connecting to my Microsoft SQL Server database through a jTDS connection:

Connection conn = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433
    /MnA;instance=SQLEXPRESS", "sa","");

SQL Server is enabled, TCP/IP is enabled, Mixed server authentication is enabled.

Any ideas?

java.sql.SQLException: Login failed for user 'sa'.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
    at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:603)
    at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:345)
    at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
    at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at DB.dbConnect(testConnection.java:17)
    at testConnection.main(testConnection.java:7)

You are attempting to pass in a blank password.  Odds are the sa account has a password.

You should create a separate account for the application to use instead of using the sa account.  Using the sa account is a major security issue.


Do Oracle databases use Java internally? My question is fairly simple. Is Java used internally by Oracle in their DBMSs?

No.

All (standard) packages are written in PL/SQL.
The DBMS engine itself is written in C

Edit:
Oracle does include a JVM which runs on the same machine as the database itself, but that is not used to run any "DBMS related" code.

It's only there to run stored procedures/functions written in Java.


Working memory on the connection string for postgres instance I'm trying to understand how can I set the work_mem setting on the Postgres(v10) for a single connection/session.

Currently I'm connection to my Postgres Instance in the cloud through a NodeJs app using a query string as showing bellow:

postgres://<usr>:<pwd>@<host>:5432/<db_instance>;

I know that you can set parameters in the connection string such as connection_timeout and so on, but I can't find anyware how to set the work_mem for the current connection/session.

Only a select list of parameters are allowed directly in the connection string.  Others must be imbedded via the "options" parameter.  This leads to much ugliness and escaping and quoting challenges.

Here is an example that sets work_mem to 1234 kB:

psql "postgresql://jjanes@localhost/jjanes?connect_timeout=10&options=-c work_mem%3D1234"


Trigger function check if all NEW.* fields exist in another table In PostgreSQL I need to code a trigger function that checks if all the fields in NEW.* exist in another table that has the exact same name as the table that fired the trigger but appended with "_hv".

For example, if the table my_example fires the trigger I need to check if all the fields in my_example exist in my_example_hv, and if it does not, alter my_example_hv to add the fields that are missing.

I am very new to plpgsql and I starting trying to code it with no success:

CREATE OR REPLACE FUNCTION trigger_hv()
  RETURNS trigger AS
$BODY$
DECLARE
    typeoffield character varying;
BEGIN

FOR field IN NEW LOOP
      IF NEW.field not exists ON TG_TABLE_NAME||'_hv' THEN
        typeoffield := typeof(NEW.columns); -- of course does not work
        EXECUTE 'ALTER '||TG_RELNAME||'_hv ADD COLUMN '||NEW.column ||' '||typeoffield;
      END IF;
END LOOP;

RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql;

I think I need some expert help ...

Your attempt fails for multiple reasons. First of all a row is not an array. This construct is just not possible:

FOR field IN NEW LOOP ...

But there is more.

I am not sure I like the general idea. This kind of trigger would run for at least every statement (don't use a row-level trigger for this!), which is quite a bit of overhead. It is also error-prone to put DDL commands in a trigger. Especially if you are a beginner.

That said, here is a proof of concept:

Trigger function:

CREATE OR REPLACE FUNCTION trigger_hv()
  RETURNS trigger AS
$func$
DECLARE
    _sql text;
BEGIN
   SELECT INTO _sql
         'ALTER TABLE ' || quote_ident(TG_RELNAME || '_hv') || ' ADD COLUMN '
       || string_agg(quote_ident(attname) || ' ' || att_type, ', ADD COLUMN ')
   FROM  (
      SELECT attname, format_type(atttypid, atttypmod) AS att_type
      FROM   pg_attribute
      WHERE  attrelid = TG_TABLE_NAME::regclass
      AND    NOT attisdropped   -- no dropped (dead) columns
      AND    attnum > 0         -- no system columns
      ) a
   LEFT   JOIN (
      SELECT attname
      FROM   pg_attribute
      WHERE  attrelid = (TG_TABLE_NAME || '_hv')::regclass
      AND    NOT attisdropped
      AND    attnum > 0
      ) b USING (attname)  -- ignoring data type!
   WHERE  b.attname IS NULL;

   IF _sql IS NOT NULL THEN
      EXECUTE _sql;
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER trg_foo_hv
BEFORE INSERT ON foo
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_hv();

SQL Fiddle.

This is a basic proof of concept. It does not check for matching data types and ignores things like schema search_path, NOT NULL constraints or COLLATION.

Related (with more explanation and links):

Change column type from varchar to text in all tables at once
Update multiple columns in a trigger function in plpgsql
Select rows where value of second column is not present in first column


Turning OFF CHECK_POLICY by default We moved from SQL Server 2000 to SQL Server 2005. The client software, which I can not change, creates a user without option

CHECK_POLICY = OFF;

After creating a user, I have to run the command

ALTER LOGIN username WITH CHECK_POLICY = OFF;

Disable the policy, as recommended, I can not.

Is it possible to disable the default CHECK_POLICY that without her CREATE LOGIN user created with CHECK_POLICY = OFF?

You do it with a DDL Trigger that captures the DDL event for CREATE LOGIN

CREATE TRIGGER PolicyStuff
ON ALL SERVER
FOR CREATE_LOGIN
AS
SET NOCOUNT ON;
IF EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]','nvarchar(100)') = 'SQL Login'
BEGIN
    DECLARE @sql nvarchar(1000) = 'ALTER LOGIN ' + QUOTENAME(EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')) + ' WITH CHECK_POLICY = OFF;';
    EXEC(@sql);
END
GO

CREATE LOGIN foo WITH PASSWORD='C-mplex654Password', CHECK_POLICY=ON;
GO
SELECT SL.name, SL.is_policy_checked FROM sys.sql_logins SL WHERE SL.name = 'foo'
GO
DROP LOGIN foo
GO


Why do these queries deadlock and how to work this around in MariaDB? The following 2 queries enounter DB deadlock in my program:

DELETE FROM proxies
WHERE NOT EXISTS (
  SELECT 1 FROM proxy_xref_proxy_list AS pxpl
  WHERE pxpl.ProxyID=proxies.ID
    AND (pxpl.DeactivationTS IS NULL OR pxpl.DeactivationTS >= (NOW() - INTERVAL 24 HOUR))
  ORDER BY pxpl.ProxyListID
  LIMIT 1
)
ORDER BY proxies.ID

INSERT IGNORE INTO proxy_xref_proxy_list (ProxyID, ProxyListID)
(SELECT ID, 13 FROM proxies
JOIN new_proxies ON proxies.Url=new_proxies.Url
ORDER BY proxies.ID)
ON DUPLICATE KEY UPDATE DeactivationTS=NULL

However, they are both ordered by proxies.ID. Why do they deadlock and how to solve this?

Tables:

CREATE TABLE `proxies` (
    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `Url` VARCHAR(767) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
    `Schema` TINYTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `Host` TINYTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `Port` TINYTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `SuccessCount` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    `UnknownCount` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    `FailureCount` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`ID`) USING BTREE,
    UNIQUE INDEX `UrlUnique` (`Url`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

CREATE TABLE `proxy_xref_proxy_list` (
    `ProxyID` BIGINT(20) NOT NULL,
    `ProxyListID` BIGINT(20) NOT NULL,
    `DeactivationTS` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`ProxyID`, `ProxyListID`) USING BTREE,
    UNIQUE INDEX `DisactivationTS_ProxyID_ProxyListID` (`DeactivationTS`, `ProxyID`, `ProxyListID`) USING BTREE,
    INDEX `ProxyXRefProxyList_ProxyListID_FK2` (`ProxyListID`, `DeactivationTS`) USING BTREE,
    CONSTRAINT `ProxyXRefProxyList_ProxyID_FK1` FOREIGN KEY (`ProxyID`) REFERENCES `mydb`.`proxies` (`ID`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `ProxyXRefProxyList_ProxyListID_FK2` FOREIGN KEY (`ProxyListID`) REFERENCES `mydb`.`proxy_lists` (`ID`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

CREATE TEMPORARY TABLE `new_proxies` (
    `Url` VARCHAR(767) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
    `Schema` TINYTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `Host` TINYTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `Port` TINYTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    UNIQUE INDEX `UrlUnique` (`Url`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

Table proxy_lists referenced by the foreign key is linked to proxies via a many-to-many relation.

UPDATE1: Upon the request of @Rick James, here is a query that selects the proxy IDs that need deletion:

SELECT p.ID FROM proxies AS p
LEFT JOIN proxy_xref_proxy_list AS pxpl
  ON p.ID=pxpl.ProxyID
    AND (pxpl.DeactivationTS IS NULL OR pxpl.DeactivationTS >= (NOW() - INTERVAL 24 HOUR))
WHERE pxpl.ProxyID IS NULL

OR is terrible on performance, and probably equally bad on transaction locks.

pxpl.DeactivationTS IS NULL OR pxpl.DeactivationTS >= ...

Instead of depending on NULL in that, consider storing a date in the future.  That way this would suffice:

pxpl.DeactivationTS >= ...

EXISTS -- Since EXISTS stops at the first occurrence, LIMIT 1 is redundant.  Similarly, the inner ORDER BY seems fishy.

TINYTEXT -- This datatype has some minor disadvantages compared to VARCHAR(255)  Better yet, is to use VARCHAR with a realistic limit.

If those don't help enough, please provide a SELECT (possibly with a LEFT JOIN) that would list the rows that should be deleted.  With that, we might be able to devise a variation on the DELETE that will be more deadlock-proof.

If that, too, fails, then simply plan on handling deadlocks:  Detect them, then replay the transaction containing the DELETE.

This might be a better DELETE:

DELETE proxies FROM proxies
    LEFT JOIN proxy_xref_proxy_list AS pxpl
      ON proxies.ID = proxy_xref_proxy_list.ProxyID
    AND (proxy_xref_proxy_list.DeactivationTS IS NULL
      OR proxy_xref_proxy_list.DeactivationTS >= (NOW() - INTERVAL 24 HOUR))
   WHERE proxy_xref_proxy_list.ProxyID IS NULL


Check constraint for Canadian postal code? Is there a way to check if a Canadian postal code is valid? Not sure if something like this would work:

PostalCode varchar(10) CHECK(PostalCode>='t1w1v1' and PostalCode<='t9w9v9'

Canadian postcodes are

  in the format A1A 1A1, where A is a letter and 1 is a digit, with a
  space separating the third and fourth characters ... Postal codes do
  not include the letters D, F, I, O, Q or U, and the first position
  also does not make use of the letters W or Z.

So the following should do it.

CHECK (PostCode LIKE REPLACE(REPLACE('Alpha1[0-9]Alpha2 [0-9]Alpha2[0-9]',
                                     'Alpha1',
                                     '[ABCEGHJKLMNPRSTVXY]'),
                             'Alpha2',
                             '[ABCEGHJKLMNPRSTVWXYZ]') COLLATE Latin1_General_Bin) )

Also you should probably use CHAR(7) if you are only allowing values exactly 7 characters long (especially if this column is mandatory).

As the space appears predictably between the third and fourth characters arguably storing this is redundant and it should be added at display time instead. If you decide to go that route then use CHAR(6) and remove the space in the middle of 'Alpha1[0-9]Alpha2 [0-9]Alpha2[0-9]'


Change Data Capture vs trigger for logging changes I am going to create history table which is populated by trigger (after insert, update, delete). As only 20% of the columns are going to be updated, I decided to log only the changed values - if the values are not changed, NULL value is going to be used in the history table. For example:

The history table columns will be sparse and I am going to save a lot of space versus ordinary implementation which is logging all data (this is due to my test and my bussness cases).

As I the SQL Server 2016 SP1 standard edition supports Change Data Capture I am wondering are their any pros/cons/differences between using it and trigger-based logging?

I have check few artciles (here and here) and cannot see what more Change Data Capture can give me.

Given the choice between those two things, here's why I'd choose triggers:

Triggers can make related table changes synchronously (like if you need to update reporting tables or check referential integrity)
Triggers let you store the data in the format that works for you, whereas CDC puts it in its own CDC schema, in tables that may not work for your reports
Triggers can have logic that checks the kind of update or the source of the update, and only log things you want to log
Triggers can easily be temporarily disabled for things like bulk loads, or if the business is facing huge load demands (like Black Friday sales)
Triggers let you put more business logic in, like if you want to put some kinds of history in one table and other kinds of history in another
Triggers don't directly add storage overhead (whereas CDC adds at least 34 bytes per row)


Question about logging architecture I understand that when (for example) an update is issued, the data pages are first read into the buffer pool, locks are acquired and changes are written out to the log file in the buffer, then the log file is written to the disk and later at a checkpoint dirty pages in the buffer pool are flushed out and gets hardened on the drive.
What I don't understand is, doesn't this mean the data in the data pages on the disk is stale until a checkpoint? Where does SQL Server read the data from when a select query is issued between "commit transaction" and the checkpoint flush?
Thanks.

What I don't understand is, doesn't this mean the data in the data
  pages on the disk is stale until a checkpoint?

On Disk - yes.

  Where does SQL Server read the data from when a select query is issued
  between "commit transaction" and the checkpoint flush? Thanks.

Memory. Let me explain a little more...

When a the update statement runs (say update mytable set columnA = 1 where columnA = 2) the change is logged and also made on the data page in memory. You've already said that the logging happens and is hardened (flushed to stable media) but the change is also made on the data page in memory. Thus, when the page is "read" by another transaction, the updated values are there.

There is something else that comes into play which are called latches. Locks keep logical consistency but latches keep physical consistency. The data page is latched, locks are taken, updates happen, logs are written and eventually flushed. The changes are always first made, in memory.

You might be thinking... "hey wait, if the changes are made to the data pages but logging isn't complete, the database is inconsistent!" That's close! The changes made to the data pages aren't reflected on disk yet - so if a crash were to happen, the data would be as it was pre-update which is how it should be. When the checkpoint occurs, WAL (write ahead logging) must still take place, so all LSNs up to the most recent reflected on data pages part of the checkpoint must first be flushed to disk. Thus the consistency is maintained and all is well in the world of SQL Server :)


Toad for Oracle 64-bit : 1603 - Fatal error during installation I'm trying to install Toad for oracle client, my Toad version is 12.1 for 64 bit system, i have succeeded to install oracle client 12c for 64 bit system, but i have stumbled by a problem when installing the Toad, the error is :

The content of the 'Toad for oracle 12.1 64bit.msi.lo' file is available via this link (beacause his size is big and we can not write it here):

Toad for oracle 12.1 64bit.msi.lo

Can anyone here help me please, Thanks in advance.

I get this error all the time with the installation or updgrade of the sql optimizer tool, For me this error is always due to the required VBSCRIPT.DLL and JSCRIPT.DLL files not being correctly registered.  Following the step listed here:
http://social.msdn.microsoft.com/Forums/en-US/jscript/thread/b7d1a35f-3759-4217-91ba-e4416ac19d78
works for me.


How can I determine the edition an Oracle session is using? We are using the Edition Based Redefinition feature in some software and as a DBA, I want to be able to determine which edition each given connection is using.  This way, we can be sure all connections to the database are using the correct edition.  How can I tell the edition being used by any given session?

Query V$SESSION and include the column SESSION_EDITION_ID which contains the value you are looking for.

The link http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3016.htm#REFRN30223 defines all the column meanings for that dynamic view; I would probably do a query like

SELECT SID, USERNAME, SCHEMANAME, OSUSER,
   PROCESS, MACHINE, TERMINAL, PROGRAM,
   SESSION_EDITION_ID
from V$SESSION
order by SESSION_EDITION_ID;


Execution Plan Basics -- Hash Match Confusion I am starting to learn execution plans and am confused about how exactly a hash match works and why it would be used in a simple join:

select Posts.Title, Users.DisplayName
From Posts JOIN Users on
Posts.OwnerUserId = Users.Id
OPTION (MAXDOP 1)

As I understand it the results of the Top index scan become the hash able and each row in the bottom Index clustered scan is looked up. I understand how hash tables work to at least some degree, but I am confused about which values exactly get hashed in an example like this.

What would make sense me is the the common field between them, the id, is hashed -- but if this is the case, why hash a number?

As SQLRockstar's answer quotes

  best for large, unsorted inputs.

Now,

from the Users.DisplayName index scan (assumed nonclustered) you get Users.Id (assuming clustered) = unsorted
You are also scanning Posts for OwnerUserId = unsorted

This is 2 unordered inputs.

I'd consider an index on the Posts table on OwnerUserId, including Title. This will add some order on one side of the input to the JOIN + it will be covering index

CREATE INDEX IX_OwnerUserId ON Posts (OwnerUserId) INCLUDE (Title)

You may then find that the Users.DisplayName index won't be used and it will scan the PK instead.


Creating a form using SSRS We have some application forms in our system and want to define them as Reports using SSRS 2008 R2. So when users see the form on the screen and what to print it as a report in a print friendly format, they can.

I'm new to SSRS and wondering how it can be done. From what I can see the format of reports in SSRS is either tabular or matrix which none is what we need. If you imagine an application form (e.g.: Car registration), the data fields are distributed on the whole page not solely on a row.

I want to put the placeholders on the form as defined by the business analyst and link the columns of the Data source to these placeholders individually.

Any help is much appreciated.

Cheers,
Nazila

Tabular and matrix are not formats of the report.

Try to create a report server project instead of report project wizard in SSRS. Create a new report item (through add->new item) and check the toolbox. You'll find labels, charts, tablices which you can arrange to your personal taste, and if you are using C# for the application you can use ReportingService library to connect reports with your app.


How to optimise read/write performance a MySQL table with many rows? I am developing a django application where I use a MySQL database. In my application there is one table for domains information which has only 2 columns

id
domain_name

but I have a problem when I have imported almost 14,150,000 rows in that table. I have not done any optimisations yet (using default settings) and there is also a possibility of importing another 14,123,500 records.

Right now I am not able read that data in my application.

Which steps should I follow to perform a read,search operation faster?

As you need to perform a read,search operation faster you can use the MyISAM as the table engine if the table will not have the High write's in future.

When you will use the MyISAM Engine for fast read/search for this table ,you need to set the Key_Buffer_size to some appropriate value depending upon the index size of your MyISAM tables and also the amount of available RAM.

As the Table having two columns id and domain name, so you can use the FULL TEXT index on domain name so it will make searching fast on the column domain name.

Look the structure which I have Created

mysql> show create table domain\G
*************************** 1. row ***************************
       Table: domain
Create Table: CREATE TABLE `domain` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `domain_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `domain_name` (`domain_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Restore the data in table and find out index_length for MyISAM table and set the Key_Buffer_size accordingly.

See How to set Key Buffer Size.


Find Ancestry From JSON I have a hierarchy that looks like this:

As JSON in TSQL it is this:

declare @Employees nvarchar(max) =
'{
  "person": "Amy",
  "staff": [
    { "person": "Bill" },
    {
      "person": "Chris",
      "staff": [
        { "person": "Dan" },
        { "person": "Emma" }
      ]
    }
  ]
}';

This is just an example. The actual data could be any tree of indeterminate depth or breadth.

The documentation and all the examples I've found show top-down traversal. Every JSON path starts at the root node and navigates, by known node names, toward the desired node. I've found nothing about starting at an indeterminate depth within the hierarchy and working upward. I feel like I need something like transitive closure.

Given a name I would like to get the ancestry for that name. For example, given "Emma" the result will be "Emma / Chris / Amy". Given "Bill" the answer will be "Bill / Amy". The output format is unimportant; it can be JSON, a string or a resultset. The names are unique.

This is a learning exercise for myself. It's OK to change the original JSON representation to anything equivalent as long as it's still JSONy. A JSON_QUERY over an adjacency list representation of the hierarchy wouldn't achieve my goal.

This seems to me to be a pretty vanilla recursion query so long as you unwrap the JSON dynamically.

If HandyD will excuse some slight plagiarism...

declare @Employees nvarchar(max) =
'{
  "person": "Amy",
  "staff": [
    { "person": "Bill" },
    {
      "person": "Chris",
      "staff": [
        { "person": "Dan" },
        { "person": "Emma" }
      ]
    }
  ]
}';

;WITH hier ([Level], Parent, Person, staff) AS (
    SELECT
        1 AS [Level],
        convert(nvarchar(255),'') AS Parent,
        l1.person,
        l1.staff
    FROM OPENJSON(@Employees) WITH (
        person NVARCHAR(255),
        staff NVARCHAR(MAX) AS JSON
    ) l1
UNION ALL
    select
        [Level]+1,
        convert(nvarchar(255),h.Person) as Parent,
        oj.person,
        oj.staff
    from hier h
    outer apply openjson(h.staff) with (
        person NVARCHAR(255),
        staff NVARCHAR(MAX) AS JSON
    ) oj
    where [Level] < 100 -- or relevant maxrecursion level
        and oj.Person is not null
)
SELECT
    [Level],
    Parent,
    Person
FROM hier
order by [Level];


How to check if current SQL session has uncommited changes? Say you are in SQL Developer and want to check if the current user session (i.e. your connection) has uncommitted changes.

If you quit SQL Developer, it does such a check and displays a dialog box how to proceed. I want to check it without exiting - e.g. via executing a special SQL statement or a SQL Developer action.

Ideally, it would be great if one could configure SQL Developer such that it is visually indicated if the current session is 'dirty' (i.e. has uncommited changes) - e.g. via displaying a red margin around the worksheet.

You could check V$TRANSACTION. Here is an example:

create table t(a number);

Table created.

insert into t values (1);

1 row created.

select t.status
from v$transaction t
join v$session s
on t.ses_addr = s.saddr
where s.sid = sys_context('USERENV', 'SID');

STATUS
----------------
ACTIVE

After commiting:

commit;

Commit complete.

select t.status
from v$transaction t
join v$session s
on t.ses_addr = s.saddr
where s.sid = sys_context('USERENV', 'SID');

no rows selected

For this you have to grant SELECT privilege on SYS.V_$TRANSACTION and SYS.V_$SESSION though.


Alter ON DELETE CASCADE in 1 table I'm really new to ON DELETE CASCADE. How can I delete child comments when the parent comment gets deleted in a table?

Table Schema

'CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent` int(11) NOT NULL,
  `comment` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=135 DEFAULT CHARSET=latin1'

Current Query (fails)

ALTER TABLE comments
   ADD CONSTRAINT `delete_child`
   FOREIGN KEY (`parent`)
   REFERENCES `comments` (`id`)
   ON DELETE CASCADE

Error Code: 1452.

Cannot add or update a child row: a foreign key constraint fails
(`App`.`#sql-1405_16b7`, CONSTRAINT `delete_child` FOREIGN KEY (`parent`)
REFERENCES `comments` (`id`) ON DELETE CASCADE)

This SQLFiddle shows my situation. It works in SQLFiddle but not in MySQLWorkbench.

The error indicates that you have invalid references in the parentId column.

This error shows when you are trying to insert a row with an invalid reference, update an existing row with an invalid reference, or delete a row that is referenced. You also get it when you are trying to create a foreign key constraint on a column that is already populated and contains references to non-existing rows, which is what happens in your case.

Solution: check the contents of the parentId column and correct/remove the invalid references.


2012 Transactional replication not delivering new articles I have added a new article to an existing push publication with the following code

sp_addarticle @publication='statics', article='FlightBaggageMarkup_tbl',source_object='FlightBaggageMarkup_tbl'

  EXEC sp_addsubscription
  @publication = 'Statics',
  @subscriber = 'SURVIVOR',
  @destination_db = 'MLT-Replicated'

this is a push subscription with immediate_sync switched off, running continuously instead of a schedule

run the snapshot agent and see it picks up just the new table, ok great. but the table and the custom stored procedures never appear at the subscriber and then replication falls over if you try to change a value in the source table as it cant find the stored procedure to call on the subscriber ?  I have only seen this behavior since upgrading to 2012, but this maybe a coincidence

According to Define an Article:

  If the publication has existing subscriptions and sp_helppublication
  returns a value of 0 in the immediate_sync column, you must call
  sp_addsubscription to add the article to each existing
  subscription.

After executing sp_addarticle, execute sp_addsubscription to add the article to each existing subscription.  Then generate a new snapshot.  The Distribution Agent should then copy the schema and data for the newly added article to the Subscriber(s).


Error 1067: The process terminated unexpectedly [InnoDB] I am facing the following error when i try to restart the MySQL service after an unexpected shutdown of the database server last night.

Could not start the MySQL service on Local Computer.
Error 1067: The process terminated unexpectedly.

When i check the .err log file under MySql data folder, the log details are as per below.

InnoDB: Log scan progressed past the checkpoint lsn 804 2135184621
150513 12:20:39  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...
InnoDB: Doing recovery: scanned up to log sequence number 804 2136195241
150513 12:20:50  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 150513 12:20:50  InnoDB: Assertion failure in thread 2412 in file .\rem\rem0rec.c line 337
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http:// bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace.
150513 12:20:51 - mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=135266304
read_buffer_size=65536
max_used_connections=0
max_threads=400
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 262617 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
InnoDB: Thread 164 stopped in file .\os\os0sync.c line 391
0070A1A8    mysqld.exe!rec_get_offsets_func()[rem0rec.c:337]
0071B498    mysqld.exe!page_cur_parse_insert_rec()[page0cur.c:798]
0071512F    mysqld.exe!recv_parse_or_apply_log_rec_body()[log0recv.c:814]
00715CF1    mysqld.exe!recv_recover_page()[log0recv.c:1294]
006EBE0F    mysqld.exe!buf_page_io_complete()[buf0buf.c:2033]
006E4472    mysqld.exe!fil_aio_wait()[fil0fil.c:4273]
006BCDCD    mysqld.exe!io_handler_thread()[srv0start.c:437]
77E6482F    kernel32.dll!GetModuleHandleA()

Error log show in Event Viewer:

An unhandled win32 exception occurred in mysqld.exe [1200]. Just-In-Time debugging this exception failed with the following error: Debugger could not be started because no user is logged on.

MySQL version: 5.1
Table Type: InnoDB
ibdata1 Size: 28GB

There are no SQL dump backup files for the tables made and there is only the SQL physical data files. I am desperately needed to restore the data of these and bring the website back to online.

Please help.

Sounds like you have your data file corrupted. You may try to start up your server using innodb Force Recover and then dump your table data using Select Into Command. If you had your binary log file enabled before the crash, you may restore data through Point In Time Recovery


Sql server full text search performance with additional conditions We have a performance problem with SQL Server (2008 R2) Full text search. When we have additional where conditions to full-text search condition, it gets too slow.

Here is my simplified query:

SELECT * FROM Calls C
WHERE (C.CallTime BETWEEN '2013-08-01 00:00:00' AND '2013-08-07 00:00:00')
AND CONTAINS(CustomerText, '("efendim")')

Calls table's primary key is CallId (int, clustered index) and also Calls table indexed by CallTime. We have 16.000.000 rows and CustomerText is about 10KB for each row.

When I see execution plan, first it finds full-text search resultset and then joins with Calls table by CallId. Because of that, if first resultset has more rows, query gets slower (over a minute).

This is the execution plan:

When I run where conditions seperately, it returns 360.000 rows for CallTime condition:

SELECT COUNT(*) FROM Calls C
WHERE (C.CallTime BETWEEN '2013-08-01 00:00:00' AND '2013-08-07 00:00:00')

and 1.200.000 rows for Contains condition:

SELECT COUNT(*) FROM Calls C
WHERE CONTAINS(AgentText, '("efendim")')

What can I do to increase performance of my query?

Kin made a couple of suggestions which should be helpful.

First: Make sure that your statistics for the CallTime index are up to date.

Your plan shows that the time filter is being made by seeks to the clustered index.  So, for some reason the CallTime index is not being used.  What is the definition of that index?  If it is a multicolumn index, be sure that the most specific column is first. Example:

IDX_Calls_CallTime
  NOT: CallID, CallTime
  USE: CallTime, CallID

Second: Consider using CONTAINSTABLE.

I doubt that you will ever want to return 1.200.000 rows for "efendim".  By using CONTAINSTABLE and ranking you can set the top number of rows that you want, thus reducing the number of FullText results to process to 10, 100, 1000, or whatever fits your needs.


How to trouble shoot intermittently slow SQL Server I have a Sql Server that runs stored procedures slowly sometimes. I’d assume it was a problem with the contents of the stored procedures, but sometimes it’s slow to update stored procedures.

Where does one start?

I've run into this several times: new server, hand-me-down, etc. etc.

I always start with the First Responder Kit from the BrentOzar team.  It's always allowed me to quickly diagnose issues and take preventative measures to mitigate future issues.

https://www.brentozar.com/first-aid/


If entrance is always the same per barcode and ID you could first group by barcode and id taking the max entrance and then get the sum grouping by barcode.

SELECT x.barcode,
       sum(x.entrance) entrance
       FROM (SELECT t.barcode,
                    t.id,
                    max(t.entrance) entrance
                    FROM elbat t
                    GROUP BY t.barcode,
                             t.id) x
       GROUP BY x.barcode;


Does cancelling an (AUTO)VACUUM process in PostgreSQL make all the work done useless? In some occasions, and after making a massive update, insert or delete from a table, I have started a VACUUM FULL ANALYZE to make sure the DB was not getting too bloated. Doing it in a production database has let me discover that this was not a good idea, because I could block the table for a long period of time. So, I cancelled the process, maybe tried just VACUUM (not full) or let AUTOVACUUM do later whatever it can do.

The question is: if I stop a VACUUM or AUTOVACUUM "mid-way", is all the processing already done lost?

For instance, if VACUUM already found 1 M dead rows and I stop it, is all this information lost? Does VACUUM work in a fully transactional manner ("all or nothing", like a very good number of PostgreSQL processes)?

If VACUUM can be safely interrupted without all the work being lost, is there any way to make vacuum work incrementally? [Work for 100 ms, stop, wait 10 ms to allow for non-blocking the rest of the world... and so on]. I know you can do part of this by tuning autovacuum parameters, but I am thinking along the lines of being able to control this programmatically, to be able to do it at certain times / under certain conditions.

NOTE: Stop / cancel / kill the process means in this context:

If using pgAdmin, press the "Cancel Query" button.
If working programmatically, call pg_cancel_backend().

I assume that both are equivalent. I have not used any shell/system-level kill command.

Work done by an interrupted VACUUM FULL will be entirely lost, as it will simply revert to using the previous version of the table and throw away the in-progress version of the table.

Work done by a regular (not-FULL) VACUUM might not be entirely lost.  It cleans indexes in batches, and any batches which were fully cleaned will not need to be cleaned again.  They will still need to be inspected again, but will be found already clean the next time.  So you might save some write IO which won't need to be repeated.


How to add rows/columns to the table in runtime in SSRS 2008 Usually we design the table to have x number of rows and y number of columns in a report. But how can we create a report which adds the rows and columns dynamically at run time based on the result of the source query?

For example I want to list stdentId, StudentName and any course each student has enrolled in. As the number of courses is different from one person to the other, I should add the rows and related column for courses at run time based on the query result. How can it be done? For example:

Thanks for your help in advance.

I don't know how your report table is currently being designed, but I would think the report table list all the rows from the query results (with predefined number of columns, in your case, 2 columns). As long as the query is correctly developed to return all required rows e.g. all enrolled courses, the report table designing part should be fairly straight forward.


Accessing Service Broker from Different Databases In my Application we have a lot of databases nearly 100 dbs.
We are planning to use Service Broker for calling a Stored Proc Async.
Instead of running Service Broker on all the Databases, Is it possible
to run the Service Broker on only one Database and all the databases can
share the Service Broker. I appreciate your guidance.
I am new to Service Broker and I apprecieate if you show me some example how to
implement it.

Consider the Asynchronous Procedure Execution example. One approach is to simply have one DB hosting all the SSB objects involved (the services, the procedure, the activation queue etc) and simply call the invocation procedure usp_AsyncExecInvoke across DBs, eg. EXEC SSBExecutionDB.dbo.usp_AsyncExecInvoke .... The SSB activated procedure could in turn run the code in the DB you invoke it from, by simply passing in a fully qualified 3 part name to execute:

EXEC SSBExecutionDB.dbo.usp_AsyncExecInvoke 'MyDb.dbo.MyProcedure';

Note that your procedure will execute under activation EXECUTE AS context and will thus be subject to the EXECUTE AS sandboxing. You must either mark the SSBExecutionDB as TRUSTWORTHY, or use code signing, see Signing an Activated Procedure.

This approach keeps all the SSB infrastructure in one place (in one DB) so it may be feasible for you. The disadvantage is that it does not leverage the loosely coupled SSB model which would allow you to schedule the async procedure even when the executionDB is not available. It also introduces a tight coupling between your DBs and the execution SSB DB, which creates problems vis-a-vis restores and failover (mirroring).


Best indexing strategy for query that searches between two bigint columns I have the following table in MS-SQL:

CREATE TABLE [dbo].[dbip_locations](
    [ip_from] [bigint] NOT NULL,
    [ip_to] [bigint] NOT NULL,
    [country_code] [nvarchar](64) NOT NULL,
    [region_name] [nvarchar](128) NOT NULL,
    [city_name] [nvarchar](128) NOT NULL,
    [latitude] [float] NOT NULL,
    [longitude] [float] NOT NULL
)

The ip_from and ip_to columns are calculated from ipv4 addresses as such:

return (
      convert(bigint, parsename(@ip, 1)) +
      convert(bigint, parsename(@ip, 2)) * convert(bigint, 256) +
      convert(bigint, parsename(@ip, 3)) * convert(bigint, 65536) +
      convert(bigint, parsename(@ip, 4)) * convert(bigint, 16777216)
    )

I am then using an ipv4 address converted to a bigint using the calculation above to search for the row in which the ip address falls between the ip_from and ip_to columns.

I'll always find one row, while it is not enforced by the schema this is the reality in the data.

Here is the query:

SELECT TOP(1) [latitude], [longitude] FROM [dbo].[dbip_locations] WHERE @ip_int BETWEEN ip_from AND ip_to

I currently have two non-clustered, non-unique indexes on both the ip_from and ip_to columns.  The query is executing pretty quickly, but I am performing a huge number of these queries per second and would like to know if I could be getting better performance through the usage of different indexes?  Perhaps a clustered multi-column index or by using unique indexes?

Are there any better indexes I could be using?

I think a composite index with both ip_from and ip_to would be the most efficient for this range search. If the only columns returned are latitude and longitude, those queries should be included as non-key columns in the non-clustered for the best performance of this particular query. The index should be clustered instead of you often return the other columns.

Because your query specifies TOP (1) without an ORDER BY, any arbritrary row may be returned within the range search.  Specify 'ORDER BY' with columns to uniquely identify a row if you need the same row returned each time.


Oracle Data Guard:Can archived redo log files be sent from primary database to physical standby database manually? As a result of the configuration of firewall,the primary database can`t not send the archived redo log to physical standby database in real time,the network connection between primary and physical standby would be available at midnight.In that case,i want to copy the archive log to physical standby manually by the shell scripts.In the general case,the sending of archived redo log is handled by the process call ARCH,so if i copy the archived the redo log to the physical standby ,can the data guard keep working correctly?

Yes, you can manually script it.

This is covered in the documentation here.

In essence, you copy the archive log files to the correct filesystem location on the logical standby host, then register them using the ALTER DATABASE REGISTER LOGFILE command.


Using Foreign Key and References for ON DELETE CASCADE in mysql I was thinking that these two ways for creating foreign keys are the same

CREATE TABLE child1 (
id int(11) not null auto_increment,
parent_id int(11) REFERENCES parent_table(parent_id) ON DELETE CASCADE,
PRIMARY KEY(id)
);

and

CREATE TABLE child2 (
id int(11) not null auto_increment,
parent_id int(11),
PRIMARY KEY(id),
FOREIGN KEY(parent_id) REFERENCES parent_table(parent_id) ON DELETE CASCADE
);

but when deleting a record from parent table, the corresponding record in table child2 will be deleted but NOT that of table child1.

Where am I wrong? REFERENCES is not enough and we necessary need to write FOREIGN KEY to use ON DELETE CASCADE?

It is a MySQL "gotcha". The first way does NOT work.

From MySQL docs, FOREIGN KEY Constraints:

  Important:

  ... 4 paragraphs below...

  Furthermore, InnoDB does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. InnoDB accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification. For other storage engines, MySQL Server parses and ignores foreign key specifications.

Creating the 2 tables (way 1):

CREATE TABLE parent_table (
parent_id int(11) not null auto_increment,
PRIMARY KEY(parent_id)
);

CREATE TABLE child1 (
id int(11) not null auto_increment,
parent_id int(11) REFERENCES parent_table(parent_id) ON DELETE CASCADE,
PRIMARY KEY(id)
);

Lets see what is child1:

> SHOW CREATE TABLE child1 ;

delimiter $$

CREATE TABLE `child1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

Where is the FOREIGN KEY ? ... Gone with the wind (and without warning)

Creating the table child2 (way 2) works fine:

CREATE TABLE child2 (
id int(11) not null auto_increment,
parent_id int(11),
PRIMARY KEY(id),
FOREIGN KEY(parent_id) REFERENCES parent_table(parent_id) ON DELETE CASCADE
);

> SHOW CREATE TABLE child2 ;

delimiter $$

CREATE TABLE `child2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `child2_ibfk_1`
    FOREIGN KEY (`parent_id`) REFERENCES `parent_table` (`parent_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$


MySQL user privileges lost on reboot On occasion our MySQL 5.6 server loses some user privileges when restarted.  The CREATE USER and GRANT commands are run through phpMyAdmin and function for as long as the server is running.  After a restart of MySQL, the user record still exists but the privileges are missing.  In phpMyAdmin User Accounts, the user appears with global privileges of "Usage".  The expected SELECT permission is missing.

Users and privileges are created using:

CREATE USER 'userName'@'hostName' IDENTIFIED BY 'password';
GRANT SELECT ON *.* TO 'userName'@'hostName';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON `dbName` . * TO 'userName'@'hostName';

What could be causing the loss of privileges?  Is there something else that should be executed in addition to these commands to force MySQL to persist the permissions?

This is a strange behaviour!
Once user is created you can make sure of that by reviewing the grants for it.

show grants for 'username'@'hostname';

Privileges are persistent, you don't need any additional step to make them stay.

Check if my.cnf has something like --init-file or --skip-grant-tables. Also, how mysql is started? Does your root password get reset as well? Are you sure you're connected to the right mysql?


how to 'flush hosts' via ssh I have a MySQL db hosted on a commercial server as a shared account. Recently I have been getting an error: 'DBI connect('xxxxxx',...) failed: Host 'xxx.xxx.xxx.xxx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'.

However I can ssh into my host but trying to execute "mysqladmin flush-hosts' tells me 'DBI connect('xxx:example.com','xxxuserxxx',...) failed: Host 'xxx.xxx.xxx.xxx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'".

My host cannot help me (outside their scope) but I am hoping there is a way either to escalate my privileges, or execute a mysqladmin command.

Anyone?

What "users" exist in MySQL?  I hope you are not doing everything as root (or other SUPER user)!

Once you get past this problem, be sure to have a user that will let you get in, but is not being used otherwise for the application.

What I just said is good advice, but it may not be relevant to your symptom.  It seems that your host (at IP xxx.xxx.xxx.xxx) has been pounding on the shared account.  Could this be?  Could it be that a hacker got into your host, and is using it to get at the hosted MySQL?

Is there another machine from which you could try to do the FLUSH HOSTS?


I'm not sure to understand correctly your use case, but maybe you could simply do the update comparing refer_to_id and id columns.

update my_table
set group_id =  case when refer_to_id > id then id
                    when  refer_to_id < id then refer_to_id
                    else id
                end

This is really basic and will not fix your issue if you have complex dependencies (line #1 referring to line #2 referring to line #3 etc...).


Should I create a new table with redundant data for optimization reasons? I have a table with multiple columns with numeric data, and it is inserted every second by a few microcontrollers.

I need to query that data in the following manner:

The averages of all columns every 1, 5 and 15 minutes (three different queries).
Also, those averages are requested relatively often (hundreds of requests per day by a website).

I'd like to know what are some options I may try for optimizing those queries and not having to create other three tables with redundant data (the averages are already calculated, so I would just need to query them without performing a lot of math).

It's worth mentioning that those queries can't take longer than a few miliseconds.

Not really enough information to give a good recommendation.

A few hundred queries a day is not much at all for Postgres, generally speaking.

As for queries, keep in mind that Postgres caches data in memory. As mentioned, do some study to learn about this caching and how to tune settings for Postgres. By default the settings are quite conservative, to favor erring in the side of safety and not overburdening the host server machine.

Adding a row every second means 86,400 per day, and 31.5 million per year. That is a serious number of rows, but many people handle such data with Postgres.

The key to fast queries here is in the indexing (assuming the RAM buffers issue is tuned). You've not described enough detail to help. If you are simply indexing a TIMESTAMP WITH TIME ZONE column, you may have no performance issues at all. Remember that with suitable hardware, tuned Postgres settings, and appropriate indexing, both your data and the relevant portions of your index will all be cached in RAM and very fast.

If you do decide to cache data in a redundant table, know that you can turn off the write-ahead log (WAL) on that table for faster performance as you do not care about its integrity.

Try some tests, running on hardware similar to your production system. Load up fake data to simulate real loads. Verify behavior with the EXPLAIN feature to make sure your queries act as you expect.

Another approach for performance is to have a background process perform the query and generate results every so many seconds (whatever your users’ tolerance for staleness of results). When user request arrives, your app uses the already determined result rather than performing a fresh query. This increases the burden on your database. And most of those queries are for nought given you have so few user requests per day. But the benefit is fastest possible response to user request. For Java, see ScheduledExecutorService.

Another option is to cache the data in your app rather than in the database if it easily fits in RAM. Java for example offers many kinds of collections and queues for such caching. This especially makes sense if the app gathering the data is also the app responding to the hundreds of requests per day. Use the database as history, but keep the recent data in memory. Keep three caches, one for each of your recent 1 minute, 5 minutes, and 15 minutes, so no queries needed at all, data ready to go at all times.


INT(5) vs SMALLINT(5): numbers in parenthesis after the numeric type In MySQL table definitions is there a different between INT(5) and SMALLINT(5)?  Or do they both represent the same size?

An int and a smallint have different sizes and consequently ranges.
The (5) is smallint(5) or int(5) is called a "Numeric Type Attribute" and it represents the "display width" of the field,

  MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)

  When used in conjunction with the optional (nonstandard) attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(4) ZEROFILL, a value of 5 is retrieved as 0005.

This is a MySQL "extension", and zerofill is an "extension" on an extension.

For the sane method of controlling the display of a numeric type, don't ues Numeric type Attributes and instead use

FORMAT
LPAD
RPAD


Cannot login to a MS Access database Below is some VBA code to authorize users to login into an MS Access database. I am trying to fix this code and I don’t understand what is the issue in this code snippet.
Private Sub cmdLogin_Click()
Dim lngUserID As Long
    lngUserID = Nz(DLookup("UserID", _
    "tblUser", "username = '" _
    & Me!cbousername _
    & "' AND Password = '" _
    & Me!txtPassword & "'"), 0)
    If lngUserID = 0 Then
        MsgBox "Username and/or password are incorrect."
        Me!txtPassword.SetFocus
    Else
        MsgBox "Registration successful!"
        OptionSetting "CurrentUserID", CStr(lngUserID)
        DoCmd.Close acForm, Me.Name
        DoCmd.OpenForm "Homepage"
    End If
      SetWebControlAsIE9
    ChangeWebControlFeature
END SUB

The debugger in the VBE shows the following code highlighed in yellow. Can someone help me to understand what the issue is?
lngUserID = Nz(DLookup("UserID", _
    "tblUser", "username = '" _
    & Me!cbousername _
    & "' AND Password = '" _
    & Me!txtPassword & "'"), 0)

Instead of looking for matching username and password from the table, I would suggest you to use login system explained in this link. You just need to import forms and code from demo database from the link into your DB. You'll also get password change form and password reset option. I've use the same approach in my project. I also adopted (recommend you to do too) storing encrypted password approach to prevent user password in case the DB is compromised. If you need any help in that regard too, I'll be happy to help.


Installed Percona-Toolkit, but no sign of xtrabackup I've installed Percona-Toolkit on my Debian (Wheezy) test Server, using

dpkg -i percona-toolkit.deb
apt-get install --fix-missing -f
dpkg -i percona-toolkit.deb

However, there is no sign of Innobackupex in the /usr/bin folder. Trying to run it simply gives me Command Not Found

I can see all the pt-xxx applications in there suggesting the installation worked ok.

I then tried

apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

as per the documentation, but that resulted in:

. . .
gpgkeys: key 1C4CBDCDCD2EFD2A not found on keyserver
gpg: no valid OpenPGP data found.
gpg: Total number processed: 0

Followed by

apt-get install xtrabackup

But that gave me

Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package xtrabackup

How can I get xtrabackup (Innobackupex) installed as well?

It turned out to be an issue with our Proxy Server, blocking downloads from percona.com

Running sudo apt-get update revealed that every connection to percona.com 'failed', and so I got that added to the approved list, and after that it installed fine.


How can I install the SQL Server PowerShell module on an offline machine? I have some SQL Server machines that cannot connect to any external sites.

I have been using PowerShell more and more when managing databases, so I definitely need this module to be installed so that I can use my PowerShell routines.

It has been proven difficult to install the SQL Server module on those offline machines.

When I try to install the module:

the SqlServer module asks for the NuGet module package:

I then get this ocean of red letters and the following error message as you can see on the picture below:

  WARNING: Unable to download from URI
  'https://go.microsoft.com/fwlink/?LinkID=627338&clcid=0x409' to ''.

  WARNING: Unable to download the list of available providers. Check
  your internet connection. PackageManagement\Get-PackageProvider :
  Unable to find package provider 'NuGet'.

  It may not be imported yet.
  Try 'Get-PackageProvider -ListAvailable'.

  At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:7415
  char:30
  + ... tProvider = PackageManagement\Get-PackageProvider -Name $script:NuGet ...

What is the workaround for this PowerShell module installation?

PowerShell has a built-in mechanism for this which should be easier than the previous answer.

From an Internet-connected computer, run Save-Module sqlserver -path c:\tmp (substitute whatever path you want for the module to be saved to). This will save the module to a directory of the same name in c:\tmp (c:\tmp\SqlServer).

Then, copy that whole directory to your target computer(s). You have a couple options for the destination, depending upon how you want to make it available to users. On PowerShell 5.1 and older look at the paths in the PSModulePath environment variable. On my system, I have:

> $env:psmodulepath.split(";")
C:\Users\MYNAME\Documents\WindowsPowerShell\Modules
C:\Program Files\WindowsPowerShell\Modules
C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules
C:\Program Files\Intel\Wired Networking\
C:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\

To make the module available to all users, I'd put it in C:\Program Files\WindowsPowerShell\Modules. For just myself, it'll go into C:\Users\MYNAME\Documents\WindowsPowerShell\Modules

These paths may be a bit different if you're running PowerShell Core or PowerShell 7; you'd look at one or more of the paths in $PSGetPath.

From here, you should be able to run Import-Module sqlserver successfully.

Shameless plug: If you're doing a lot of SQL Server administration via PowerShell (and I heartily encourage it!), you should be checking out the dbatools module.


Trigger with multiple inserts based on JSON data I am using sqlite and have its json1 extension available. I have the following tables (reduced to a minimal example):

-- table filled from external source,
-- with only a little possible impact on data
create table "source" (
    "id" Integer primary key,
    "json_data" Text not NULL
);

-- table to be filled using a trigger on "source"
create table "target" (
    "node" Text primary key,
    "parent" Text, -- NULL when root of a tree
    "kind" Text  -- NULL when "parent" is NULL
        check ("kind" in ('clone', 'child', 'offspring'))
) without rowid;

The "json_data" is of the following form:

{
    'id': [n0]  // single-component list
    'clone_ids': ['c2', 'c1'],  // multi-component list
    'parent_id': ['p0'],  // single-component list
    'ancestor_ids': ['a1, a2, p0']
        // single-component list, but multiple semantic components
}

This instance of "json_data" should give rise to the following tuples being inserted into "target" ( "node", "parent", "kind"):

'c2', 'c1', 'clone'
'c1', 'n0', 'clone'
'n0', 'p0', 'child'
'p0', 'a2', 'offspring'
'a2', 'a1', 'offspring'
'a1', NULL, NULL

Usually, 'clone_ids' will be absent; often 'parent_id' and 'ancestor_ids' will be absent; even 'id' could be absent. So the approach should be robust in view of this.

The 'child' kind is an ‘upgrade’ from the 'offspring' one; my feeling was that this upgrade could be done as an update at the end.

So my idea was that I should first try and get a list (as a JSON string or perhaps a temporary table) of the form

[
    ["c2", "clone"],
    ["c1", "clone"],
    ["n0", "offspring"],
    ["p0", "offspring"],
    ["a2", "offspring"],
    ["a1", null]
]

and then create the variable number of inserts from this list.

However, I'm quite inexperienced with SQL and sqlite's json1 extension, so I do not know (i) whether this is possible, and if yes (ii) how. So I would like to get responses to both questions. In case getting the latter list of pairs needs to be done using an external function (which I would do in Python), I still appreciate guidance on how to go from the list to the inserts.

EDIT:

In a comment it has been indicated that what I asked is not possible. So I would like to amend my question in the following way: Assume I have a user-defined function that produces the following JSON:

[
    ["c2", "c1", "clone"],
    ["c1", "n0", "clone"],
    ["n0", "p0", "offspring"],
    ["p0", "a2", "offspring"],
    ["a2", "a1", "offspring"],
    ["a1", null, null]
]

So, essentially the tuples I want to insert.

How would I go about inserting them?

This is not valid JSON; strings require double quotes.

Anyway, the table-valued function json_each() allows to enumerate a dynamically-sized array:

SELECT * FROM json_each('[["c2","c1","clone"],["c1","n0","clone"],["n0","p0","offspring"],["p0","a2","offspring"],["a2","a1","offspring"],["a1",null,null]]');
key         value                type        atom        id          parent      fullkey     path
----------  -------------------  ----------  ----------  ----------  ----------  ----------  ----------
0           ["c2","c1","clone"]  array                   1                       $[0]        $
1           ["c1","n0","clone"]  array                   5                       $[1]        $
2           ["n0","p0","offspri  array                   9                       $[2]        $
3           ["p0","a2","offspri  array                   13                      $[3]        $
4           ["a2","a1","offspri  array                   17                      $[4]        $
5           ["a1",null,null]     array                   21                      $[5]        $

For extracting the values from an array with a known size, we can use json_extract():

SELECT json_extract(value, '$[0]'), json_extract(value, '$[1]'), json_extract(value, '$[2]')
FROM json_each('[["c2","c1","clone"],["c1","n0","clone"],["n0","p0","offspring"],["p0","a2","offspring"],["a2","a1","offspring"],["a1",null,null]]');
c2          c1          clone
c1          n0          clone
n0          p0          offspring
p0          a2          offspring
a2          a1          offspring
a1

This can then simply be plugged into an INSERT … SELECT … statement.


Best practices for creating partition schemes and functions I'm implementing partitioning for the first time in SQL Server 2008 R2. I've several large tables that I am partitioning.

My main question is: Should I use the same Partition Scheme and Function for all these tables, or should I create multiple Partition Schemes and Functions?

When I move older data to a different file group, will having separate Schemes and Functions make moving the data easier?

And what are some things that I should consider when making this decision?

If you have a single schema and function and you alter that to move data around all the tables will be moved in a single shot instead of having to change all of them.


DB design to avoid versioning inconsistencies between the application layer and the database layer Usually there is a 1-1 mapping between an object in the application layer and a table in the database layer.
This coupling leads to complexities whenever there is a database schema change and the application version may not be compatible with the current database version that it is talking to.
I was reading that there is an approach of database design that is driven by metadata. Something like the fields available for an object is found from a table and then a query is done. So some form of direction in order to avoid versioning problems.
Is this approach used in general and how exactly is it implemented? Also are there are other approaches to deal with this issue?

The approach you're think of is called Entity-Attribute=Value (EAV). If you Google around for it you'll find very quickly that it is almost universally reviled.  In short, it's generally a bad idea because it doesn't let your relational database management system do what it was designed to do.

As an alternative, you should think about schema changes as something that you need to keep backward compatible.  This often means that new columns will be nullable or have defaults set.  Depending on what you want to do with your application, though, sometimes a database change is simply a breaking change and you can't release the database change without releasing the corresponding code change.

I've seen some people keep a short code table in their database that contains version information in it (e.g. minimum and maximum supported code versions). The idea with this is that when your application starts it does a quick lookup in the code table and if the database version isn't compatible with the code the code exits gracefully.


User cannot select from table but their permission appear to show that they should be able to I attempted to revoke delete permissions to most users in a database by using the deny and revoke sql commands and checking the deny box for delete when adding the table as a securable in the UI.

When I did that, the users are now not able to select from any table in the database. They have select permission granted in the UI for all tables including the table I am trying to revoke delete permissions to.

I tried to recreate what I did on a backup copy of the database and I have not been able to deny select on the tables.

The only deny permissions I found on any of the users were deny delete on all objects and deny on the one table I am trying to prevent delete on.

I did not find any deny selects on any users or roles.

No users are part of the db_denydatareader role.

What is the best way to find out why the users are not able to select from any table in the original database?

I am using SQL Server 2008.

I would check if the users are part of the db_denydatareader role, check for deny permissions on the tables schema, and also for a deny permission on the public role. I would suspect these are the most likely culprits.


NT SERVICE\SQLTELEMETRY User I've noticed that SQL Server 2016 installations include a new default login called NT SERVICE\SQLTELEMETRY. I'm aware that for the free editions of SQL Server 2016 you agree in the license to allow your server to send telemetry information to Microsoft. I would guess that this login is associated with that work. As part of our new instance installation process we prefer to clean up and remove any unused system or default logins to avoid potential holes in security or clutter. Does this account do anything particularly important such that we'd want to keep it and if not would it break the license agreement to disable or delete it? I should note that our SQL Servers are generally not Internet facing.

This login is used for Extended Events. More information here: Extended Events logins

I disabled it once, and I was no longer able to collect the data I needed. If I were you I'd keep the login as it is, unless of course you already know that you wont need tracing/Extended Events.

When it comes to telemetry you may like to read the instructions how it can be disabled: Disable telemetry Apparently many users are not happy about telemetry. More information here


Type 2 Dimension for a fact measure I have a need to store products along with their prices and quantities (and many other things) and track the history.
Now, given both price and quantity are stored as integers, it is my understanding these are fact measures. So we might have a table like:
f_products:
  id
  sku
  price
  quantity

As the price varies over time we can insert a new fact, and I can introduce effective dates to the table to allow for querying the price or quantity at a given time.
But with this design, it means if the quantity changes, I must insert another row, repeating the unchanged price value again. And while this isn't too bad for 2 columns, I have in fact 10.
So, my design is:
f_products:
  id
  sku

price:
   id
   product_id
   price
   start_date
   end_date

another "dimension":
   id
   product_id
   value
   start_date
   end_date

and so on

Now the "fact" no longer needs to change when the price changes, and I can query for historical values across these tables.
So my question is, this doesn't seem to fit the standard fact / dimension designs, so I wonder how more experienced modellers would approach these requirements.

Honestly it really just depends on your use cases, but for me I would likely just store all 10 columns in the same table and call it a day (unless they were very unrelated to each other, then I might normalize it a bit). Having the full list of fields might influence different answers, and your approach is a valid one as well. It just might be more work to maintain that way.
Essentially it would be a Transaction table, and log a record for each change on any of the fields. Then it's easiest to look at what the current state of a given product is, or what its state was for a given timeframe.
Yea you'll run into some data duplication, but most modern database systems automatically compress their tables anyway. So unless you think you're going to be creating billions of transactions a day and start creeping into big data territory, I wouldn't be super concerned from a performance perspective.


Checking IFI enabled on SQL server below 2016 I am using in one of my reports to check for status of IFI being enabled on SQL server.

It works well when using dmv sys.dm_server_services for SQL2016

However, in SQL2014 and SQL2012 i see difficulties in rendering this check via SQL query.

For example if i use below

EXEC sys.xp_readerrorlog 0, 1, N'Database Instant File Initialization';

There is no guarantee it will show status on file '0' and sometimes i keep querying the error log manually to find that out. How can i achieve the IFI check using a better way so that it can show up on my SSRS report

According to this MSSQLTips.com article, you can use sys.dm_server_services beginning with SQL 2012 SP4.

SELECT  @@SERVERNAME AS [Server Name] ,
        RIGHT(@@version, LEN(@@version) - 3 - CHARINDEX(' ON ', @@VERSION)) AS [OS Info] ,
        LEFT(@@VERSION, CHARINDEX('-', @@VERSION) - 2) + ' '
        + CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(300)) AS [SQL Server Version] ,
        service_account ,
        instant_file_initialization_enabled
FROM    sys.dm_server_services
WHERE   servicename LIKE 'SQL Server (%'

For older versions, you'll need to scrape the log, looking for "Database Instant File Initialization: enabled."


How to make a table with fields that are other tables in MySQL? I am making a music library system with php in MySQL.  In this system there would be a table named Songs.  Since many songs can be by the same Artist (Person), I wanted to store a table of Person that contains a persons data (name, DoB, hometown, fan page URL), then just store that ID in the Songs table.

I'm trying to avoid repeating the same 10 fields for an Artist over and over for each of their songs. I'd rather store the ID (Primary Key) and then be able to pull all of their specific data.

Two questions:

What is this called, so I can research how to do it (or can someone give me an example)?
If I do this, will queries for "Barry Manilow", who's ID  may be '123', be found in every song that contains the field Artist='123'?

Thanks,
-CJJ
(Basically, what's the best way to reuse fields)

This is a really basic example of normalization.

Typically you set up a foreign key relationship.  In your example you would have two tables:

Artists - id, name, etc...

Songs - id, artist_id, name, etc...

You would retrieve needed data from Artists using a JOIN.  You should look into some entry level SQL tutorials.  JOINs are the bread and butter of SQL, and what makes it a relational database.


Replacing Postgres User I'm new to postgres. It might sound a silly question, due to some security requirement I need to remove default superuser 'postgres' and replace it with any other super user (for example:abc) created by me.

To test this, I created a database user 'abc' and grated all superuser permission(like postgres user) to this user. However, when I'm trying to connect to postgresql prompt from my linux machine with below command:

    sudo su - abc -c "psql"

It wasn't working. I know there is a default database with postgres user 'postgres' in postgres and it's template database.
Postgres is open source and I believe, there should be a possibility to accomplish what I want to. Anyone could please let me how can I make it happen?

It might sound a silly question,

I think you have a security requirement written by someone who doesn't know what he is doing, and then implemented by someone else who doesn't know what he is doing.  "Silly" is far too benign a word to describe this.  I hope this is a toy system completely firewalled off from the internet.

sudo su - abc -c "psql"

You said you created a database user named "abc", but now you are trying to do something as the operating system user named "abc", which probably does not exist, since you didn't describe creating it.

Try psql -U abc.  It will probably get farther than your last effort, but still not work due to pg_hba.conf settings.  Once you see the error message produced (look in the server log file for postgresql for the complete error message, the one returned to the client is intentionally vague to avoid leaking sensitive information to a presumed attacker), then you can decide what to do about it.


Linked Server along Oracle 32 and 64 bit Oracle Client As we all know that to connect from SQL Server to Oracle we need to Install Oracle Client at SQL End. But since I am stuck in one issue which has made me wondered about how things work behind the scenes in case of Linked Server. I have few questions and wanted clarification

Scenario: we have SQL Server 2008 64 bit running on machine. Also we have 32 and 64 bit Oracle client installed. We have a SQL Job under which a DTS package is running using 32-bit environment. Inside package, SQL is connecting to Oracle. Our job is failing. My questions are:

So when I run the job and it is connecting Oracle Server, will it use Linked Server or will directly use Oracle 32-bit client?
If linked server is failing will that can be the reason for job failure. Error message when Testing Linked Server is:
ORA-12154: TNS: could not resolve the connect identifier specified
Is the order in which oracle bin path is defined in PATH System Environment variable can impact this issue in anyway?
After making the changes in PATH System Environment variable do we need to restart SQL Server service or entire server?

I can provide more inputs for more clarifications. Any help is appreciated.

So when I run the job and it is connecting Oracle Server, will it use Linked Server or will directly use Oracle 32-bit client?

Depends on how the DTS package is written, since DTS was the old 2000 era packages I have no idea if they imported a connection in the package or query through the linked servers. You'd have to investigate to see if it references the linked server using TSQL or it uses a connection, directly.

  If linked server is failing will that can be the reason for job failure. Error message when Testing Linked Server is: ORA-12154: TNS: could not resolve the connect identifier specified

Looks like you should check the tnsnames.ora file to make sure the listeners are identified properly.

  Is the order in which oracle bin path is defined in PATH System Environment variable can impact this issue in anyway?

Potentially, depends on how/what the package is doing.

  After making the changes in PATH System Environment variable do we need to restart SQL Server service or entire server.

If you make changes to the system variables it's best to restart the server.


Default trace enabled but not active When I query the configuration of the default trace, it shows enabled:

exec sp_configure 'default trace enabled';
-->
name                    minimum  maximum  config_value  run_value
default trace enabled         0        1             1          1

But when I query sys.traces for the path, it returns an empty rowset:

select * from sys.traces;

What could explain the absence of the enabled trace?

I would say there is a strong correlation between your out of space event and the missing trace. Note that the sp_configure option merely tells you that the default trace is enabled, but that does not mean that it is running or that it even exists. Note that sys.traces is not a table but a view:

create view sys.traces as select * from OpenRowset(TABLE SYSTRACES)

What does the TABLE SYSTRACES rowset provide? How does it work? How are its results filtered? Your guess is as good as mine. It is possible that the trace is still there, but in a state that prevents it from being exposed by this view. And it may be in a state that still prevents it from being started even after restarting the service.

First, make sure the location of the default trace has sufficient space, the SQL Server service account still has adequate permissions to write to it, you aren't subject to any space quotas, etc. You can get the location from the registry:

HKEY_LOCAL_MACHINE\Software\Microsoft\...YourInstance...\Setup\SQLDataRoot\

Once you are sure that SQL Server should be able to write to this folder, then you can disable and re-enable the default trace:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'default trace enabled', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'default trace enabled', 1;
GO
RECONFIGURE WITH OVERRIDE;

You shouldn't need to restart the SQL Server service at this point, but may be a final kick in SQL Server's pants if you still don't see a row in sys.traces. Note that the trace_id you get is not guaranteed to stay at 1.


InnoDB Data Dictionary Cleanup for a Non-Existent Database Named "tmp"? I have a number of error messages like this in my error.log file referencing different files that can't be opened when I start my mariadb 10.4.8 server:

2019-09-30 17:38:19 0 [ERROR] InnoDB: Cannot open datafile for read-only: '/tmp/#sql50b_8808_1.ibd' OS error: 71
2019-09-30 17:38:19 0 [ERROR] InnoDB: Cannot read first page of './tmp/#sql50b_8808_1.ibd' I/O error
2019-09-30 17:38:19 0 [Note] InnoDB: Cannot read first page in datafile: ./tmp/#sql50b_8808_1.ibd, Space ID:18446744073709551615, Flags: 0
2019-09-30 17:38:19 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2019-09-30 17:38:19 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2019-09-30 17:38:19 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2019-09-30 17:38:19 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``tmp`.`#sql50b_8808_1``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2019-09-30 17:38:19 0 [Warning] InnoDB: Ignoring tablespace for `tmp`.`#sql50b_8808_1` because it could not be opened.

I don't have a database named tmp, so the instructions for data dictionary troubleshooting haven't been helpful.

I can't find any files that start with #sql anywhere in my file system, but

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE NAME LIKE '%#sql%';

returns 65 rows, including one associated with the file described above. Is there some way to clean this up so MariaDB doesn't try to open these non-existent files?

I was able to fix this using the brute force method of backing up my databases, removing and reinstalling MariaDB, and restoring my databases.


Restrict contained SQL Azure user to read-only access I am trying to create a contained user for a database in SQL Azure that only as read-only access. What I am doing is:

Connecting to the database via SSMS using my admin account.
Right click the database in question and select new query.

Then I typed the following:

CREATE USER DEMO_dbreader WITH PASSWORD = 'pass@word1'
EXEC sp_addrolemember N'db_datareader', N'DEMO_dbreader'`

After that I disconnect from the database in SSMS and I reconnect using the new SQL account I just created. then I type in the following as a test:

CREATE table testing123 (id int)

To my surprise, I can create tables (!?!) I can also truncate them and drop them (?!?) Not sure what the heck is going on here. Am I missing something?

I need the user to be contained in this database and not to exist outside of it.

This should never happen. db_datareader will grant only select access to the database.

I used your query in my Azure database and I couldn't create a table, the user got only select access to the database.

Either the user got elevated access or else you might be logged in with the administrator account. Do the same test in a different user database (just to ensure the user didn't get created already) and then connect using the user name and run the query below.

SELECT DatabaseName=DB_NAME(),
    UserName=USER_NAME(),
    x.DoesUserHasAccess,
    a.entity_name,
    a.permission_name
FROM sys.fn_my_permissions(NULL, 'DATABASE') a
    CROSS APPLY
     (
         SELECT DoesUserHasAccess = HAS_PERMS_BY_NAME(
                                        DB_NAME(),
                                        a.entity_name,
                                        a.permission_name
                                                     )
     ) x
WHERE x.DoesUserHasAccess = 1;

When I tested for the same user it returned only select access.


How can I create a user who could only create tables and can't delete/drop them There's my problem. I'm using PostgreSQL and tried so many things to create a user who can create tables but can't drop them, it's like create a table but assign it to another user. I've tried one trigger but it can't solve my problem.

I've used a trigger that I found on another site, just changed the role where the owner of the table need to be replaced but it didn't work. When I try to create a table with another user it returns me a message

  ERROR: must be a member of role "adm"

This is the trigger:

CREATE OR REPLACE FUNCTION trg_create_set_owner()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $$
DECLARE
  obj record;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag='CREATE TABLE' LOOP
    EXECUTE format('ALTER TABLE %s OWNER TO adm', obj.object_identity);
  END LOOP;
END;
$$;

CREATE EVENT TRIGGER trg_create_set_owner
 ON ddl_command_end
 WHEN tag IN ('CREATE TABLE')
 EXECUTE PROCEDURE trg_create_set_owner();

As the manual says,

  To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the table's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the table. However, a superuser can alter ownership of any table anyway.)

You need to either run execute CREATE TABLE as superuser, or create a SECURITY DEFINER function (again, as superuser) that creates such tables and assigns their owner roles, then grant the EXECUTE privilege on that function only to the user in question. See additional notes on security in this case.


Query 2 tables without a join I am given the schema:
BOOKING (hotelNo, guestNo, dateFrom, dateTo)
GUEST (guestNo, guestName, guestAddress)
I an asked to formulate the query for:
List the guest numbers associated with the first name ‘Peter’ having made bookings with unknown dateTo without using an explicit or implicit joins.
My attempted query is:
  (select guestNo from GUEST where guestName LIKE 'Peter')
  INTERSECT ALL
  (SELECT guestNo FROM BOOKING WHERE dateTo IS NULL);

But this does not return duplicates only at most 1 entry per guestNo, as shown in fiddle where returned values should be (1,1) rather than (1). I thought about using UNION but then this will return guestNo's if dateTo is NULL or name is Peter, i.e. both conditions may not necessarily hold.
Database fiddle: https://www.db-fiddle.com/f/tpzgVMwkQGAHBFxyMkyJvj/17
Do you guys have any suggestions?

In an almost literal translation from English to SQL, I would write the query as follows:
select guestNo
from guest
where guestName like 'Peter%'
and exists (
    select
    from booking
    where guestNo = guest.guestNo
    and dateTo is null
);

NB Postgres unlike some other DBMSes doesn't need to have any columns in the select clause! That's ideal in the subselect here as we're only interested in the existence of a record, not in its value).
See https://www.db-fiddle.com/f/tpzgVMwkQGAHBFxyMkyJvj/17


SSMS 2017 Export SCOPED CONFIGURATION I reinstalled Windows 10 from scratch.

I'm trying to get SSMS options setup the way I like them. When I r-click a DB, then Tasks->GenerateScripts, the result doesn't contain SCOPED CONFIGURATION lines anymore. I checked the various SSMS scripting options and don't see one specifically for SCOPED CONFIGURATION.

Does anyone have ideas on why GenerateScripts doesn't include SCOPED CONFIGURATION lines?

SSMS 17.9.1 SQL Server 14.0.2002.

Thank you in advance.

why GenerateScripts doesn't include SCOPED CONFIGURATION lines?

Because When scripting objects, DB Scoped configuration that has default values are omitted.

Check SQL Server Management Studio - Changelog (SSMS) for more information.

SSMS 18.0 (preview 4) -> Bug fixes -> Object Scripting


In which table we should put the foreign key For example I have a table Admin and a table Role. To do one-to-one relationship between these two tables I can put the foreign key either in the Admin or the Role table. My doubt is if its icual put the foreign key either on the Amin or Role table?

The relationship between Admin and Role doesn't seem to be one-to-one. It's either one to many (One Role can be assigned to multiple people, then you will have FK column in Admin table), or many-to-many (then it's gonna be a link table with 2 FKs to each table).


Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQL If I create an index CONCURRENTLY in PostgreSQL, how can I see when it is finished?

I am attempting to rebuild indexes to solve index bloat, and I need to keep the old index around for a while until the new one has finished, so I need to know when it's finished.

This is PostgreSQL 9.2/3ish

You can get list of invalid indexes.

SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;

If you see your index in this query it means the index won't work and you have to recreate index.

Don't do REINDEX. It won’t re-create the index concurrently, it will lock the table for writes while the index is being created, the best solution is to drop the invalid index and recreate it with CONCURRENTLY flag


How to copy MongoDB database from server to local machine? Or just may be dump database on to the folder on the server and then scp it to local machine? I'm trying to use mongodump from /usr/bin, but nothing good comes out of it, for instance:
mongodump --ssl -u root -p password --authenticationDatabase name -d databaseName -o /database
-->Failed: error connecting to db server: no reachable servers
if:
mongodump -u root -p password --authenticationDatabase name -d databaseName -o /database
-->Failed: error connecting to db server: server returned error on SASL authentication step: Authentication failed.
tried to do --host=ip.ad.re.ss:27017
tried to do --host=host-name:27017
--host=127.0.0.1:27017
--host=web-address:27017
tried to switch off mongodb...
Tried to copy files manually, but end up with the empty database on the local machine...
tried to use: mongo --> db.copydatabase(from, to, name)
Nothing is happening positive... How to use mongodump? Possible to use any other way. Only through terminal, ubuntu setup doesn't have any gnu.

Basically what is missing from your mongodump command is... "" round your --host -value.
--host="host-name:27017"

Check similar question here


define rating column in table? Hi I am creating database for rating the items in my list but I want to ask what this if I have five stars rating how can I do it in database "rating" column should I use Int with check constraint  or user define type ?

You better use tinyint. It's int from 0 to 255. It's also takes only 1 byte to store value. It's enough for your 5-star rate.
If you need store values like 4.5 stars you may store stars like this:
1 star =10
2.5 stars=25
and so on
And, of course, you need check constraint on this field.

tinyint - using storage 1 byte
numeric(1)/decimal - using storage 5 bytes


How to use CREATE TABLE ... AS with Primary Key in SQLite From SQLite documentation for CREATE TABLE http://www.sqlite.org/lang_createtable.html:

  A table created using CREATE TABLE AS has no PRIMARY KEY and no
  constraints of any kind.

So is there any general way to create table with primary key & other index information ?

I suspect you're missing the difference between CREATE TABLE and CREATE TABLE AS (otherwise known as CTAS).

CREATE TABLE AS allows you to create a table from the resultset of a query.

For example:

CREATE TABLE PHILSUCKS AS ( SELECT PHIL, SUCKS FROM EGGS );

You could, instead of using CTAS, use a "normal" CREATE TABLE statement, then INSERT the rows manually. This allows you to specify the PRIMARY KEY and any constraints. eg:

CREATE TABLE PHILSUCKS
(
  PHIL   INTEGER PRIMARY KEY,
  SUCKS  INTEGER NOT NULL
);

INSERT INTO PHILSUCKS ( SELECT PHIL, SUCKS FROM EGGS );

Obviously, you can also create indexes etc too:

CREATE INDEX EGGSUCKING ON PHILSUCKS (SUCKS);

Hope that helps!


How to get lexemes array from Postgresql ts_vector, sorted by lexems positional information? Postgresql full text search to_tsvector() function returns a sorted array of lexemes. I need exactly the same function, but returning unsorted array of lexemes. Is there any such postgresql function already available or I need to do it myself? How to do it?

I need it for Jaro-Winkler string similarity function, for which order of strings/words is important...I need tsvector only because of language support for lexemization. Actually, I just need to leximize text, e.g.

select array_to_string(tsvector_to_array(to_tsvector('english', 'I just want to lexemize this string and keep the original order of lexemes...')), ' ');

This gives me leximized text which is OK, except lexemes are ordered alphabetically instead their original relative order in original text...

This function seems to work, Please, suggest if there is a better way.

CREATE OR REPLACE FUNCTION public.tokenize_orig(
    p_string text,
    p_dictionary regconfig)
  RETURNS text[] AS
$BODY$
/* This function turns documents into array of lexemes, keeping original order of lexemes. */

select array_agg(lexeme)
from
    (
    select (arr).lexeme as lexeme
    from
        (select unnest(tsv) arr
        from to_tsvector($2, $1) as tsv) arr
        where array_length((arr).positions, 1) > 0
        order by (arr).positions
    ) as qry

$BODY$
  LANGUAGE sql IMMUTABLE STRICT
  COST 100;


Remote query (openquery) to linked server fails after upgrading to SQL Server 2016 Problem started after upgrading to SQL Server 2016 Enterprise edition.
Linked and local servers both were upgraded to ver.13.0.4435.0

I've tried the following:
 Drop/create the linked server after the upgrade to 2016. The problem persists.

This is the code snippet where error occurs:

SELECT t.NAME
    , t.Disabled
FROM openquery([Linked_Server], 'select t.Name, t.Disabled
    from [Linked_Server].[Database-1A].[dbo].[My_tbl] t where t.ID=232627') t;

And the error message:

  OLE DB provider "SQLNCLI11" for linked server "Linked_Server" returned message "Deferred prepare could not be completed.".
  Msg 5000 Could not find server 'linked server' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

The query you're sending to the linked server (via OPENQUERY) shouldn't also contain its own reference to the linked server (via 4-part name), unless the linked server has its own loopback linked server with the same linked server name (and even if it does, I wouldn't try to stack linked servers like that). Try:

SELECT t.Name, t.Disabled
FROM OPENQUERY
(
   [Linked_Server],
   N'select t.Name, t.Disabled
     from [Database-1A].[dbo].[My_tbl] AS t
     where t.ID=232627;'
) AS t;


When to stop splitting tables? I am creating a league management system. I came up with the diagram below. I am no database expert but I feel it would be better to merge the three following tables: season_league, league_division and division schedule(see second image). This is for mySQL using doctrine with Symfony2 if it matters.

What are your opinions? Any suggestions?

The decision of whether to merge these tables or not should depend on whether the separated tables provide useful information.  Use the rules of database normalization.  What are the functional dependencies?  What are the candidate keys?

Season_League doesn't seem to have any information in it other than which leagues exist in which seasons (pure intersection)  League_Division just adds division to this pure intersection.  Division_Schedule doesn't seem to add any information at all.

One question I would have is whether it is useful to know that a league exists in a season.  If you combine the tables, then what you have is a division exists in a league in a season.  There is no "independent life" of a league except that it has a division.  There are other subtleties too, like whether a division can exist in more than one league within a given season.  If you combine the tables into one then this type of business rule could not be enforced with declarative referential integrity.  Does this matter to you?  It depends on what other manual or automated controls you're willing to put in place to manage this risk.

Without any more information about your intended business rules, I would say these three tables should definitely be combined.


how to flatten child tables into parent I have inherited a db, and I need to transform an existing parent-child relationship into all parents.

Let me first explain the existing data.The tables have a structure something like:

Thing                  Widget
------------           ------------
thing_id               widget_id
thing_name             widget_name
thing_rank             widget_rank
                       thing_id

The data is presented to the end user as a list of Thingss, and occasionally a Thing has-a Widget, and the widget is presented as being "beneath" or "sub" to the Thing. So there's some hierarchy. The other key thing is that this presentation is sorted by the thing_rank and widget_rank.

Here's a concrete example with data:

Things:

1001  "thing blue" 1
1002  "thing green" 3
1003  "thing red" 4
1004  "thing yellow" 2

Widgets:

2001  "widget cucumber" 2  1002
2002  "widget bean" 1  1002

This would be presented with the Things ordered by rank, then the two child Widgets belong to "thing green", and ordered by their rank:

1 thing blue
2 thing yellow
3 thing green
   1 widget bean
   2 widget cucumber
4 thing red

The task is to remove the hierarchy, but preserve the rank order. For example, the above data would be assigned a new rank, and all data would belong to the same relation -- no longer a parent-child relationship. For example:

1 thing blue
2 thing yellow
3 thing green
4 widget bean
5 widget cucumber
6 thing red

And the single relation would be:

Stuff
-------------
stuff_id
stuff_type   (thing|widget)
stuff_name
stuff_rank

I'm looking for how to create a Stuff view that presents both Thing and Widget data, with new rank assignments.

Since you have not provided any RDBMS I've tried to post an answer that should work in more than one system.

Records can be ordered by both ranks and numbered using row_number() window function.

select row_number() over (order by r1, r2) id, name
from (
      select t.name as [name], t.[rank] r1, 0 as r2
      from   thing t
      union all
      select w.name as [name], t.[rank] r1, w.[rank] r2
      from   widget w
      join   thing t
      on     t.id = w.thing_id
     ) x;
GO

id | name
:- | :--------------
1  | thing blue
2  | thing yellow
3  | thing green
4  | widget bean
5  | widget cucumber
6  | thing red

dbfiddle here