Why does user not have access to database? I have a user ls_readonly which is supposed to have db_datareader privileges on several databases. I thought I had set it up right:

But when I connect to the server as ls_readonly and try to open the db in the Object Explorer, I get an error:

  The database wtest is not accessible. (ObjectExplorer)

I open a query window in master and try run:

use wtest

This responds with:

Msg 916, Level 14, State 1, Line 1
The server principal "ls_readonly" is not able to access the database "wtest" under the current security context.

What am I missing?

UPDATE: here's a clue. If I delete ls_readonly as a user from under the database's security context, then I go to the user under the server's security context and under "User Mapping" I grant access to the database, then it starts working.

It could be that the database was originally restored from a different server, which also has a ls_readonly user. I guess then that the user identification is not based on the username?

It could be that the database was originally restored from a different
  server, which also has a ls_readonly user. I guess then that the user
  identification is not based on the username?

Normally, all the logins have the same sid with corresponding users. That is the sense of CREATE USER FROM LOGIN command: the database principal is created with the same sid (security identifier) that corresponding login has.

This user sid is now stored in your database, and when you backup/restore your database the sid is also preserved.

Imagine now that you have 2 servers and both of them have ls_readonly login. to make it simple, on the first server ls_readonly has sid = 1, on the second sid = 2. You have the database MyDB where this login is mapped. On the first server MyDB stores sid = 1, on the second sid = 2.

You backup MyDB database on the first server and restore it on the second. Now your login has sid = 2 and in the database MyDB it's sid = 1.

You log in as ls_readonly and try to access MyDB, server is checking if there is sid = 2 in the database, there is no, so for the server ls_readonly is not mapped to MyDB at all.

This can be fixed by doing

alter user ls_readonly with login = ls_readonly;

This command just update database's sid of ls_readonly user with the sid of ls_readonly login. All the permissions are preserved.

If you recreate user by dropping it, once you drop the user, all the permissions granted to it are dropped and you need to re-grant them.

Drop the user from the database
Delete and re-create the user
Add the user to the database

You might not need the 2nd but it should not hurt


One filegroup, multiple datafiles, how to get list of tables in each file In one of my production databases, I have a filegroup with 3 data files sitting on 3 different disks. There are over 50 tables on this filegroup.

How do I identify the location of each of the 50 tables between the 3 data files? I need to know on which data file does the clustered index for each table reside.

Example :

Table A : Datafile 1 : FG1
Table B : Datafile 2 : FG1
Table C : Datafile 1 : FG1
Table D : Datafile 3 : FG1

An object in a filegroup will use all datafiles in the filegroup. Any table in FG1 resides equally on Datafile1, Datafile2 and Datafile3. If you need to control placement you need to create distinct filegroups. To remove a file from a file group use DBCC SHRINKFILE EMPTYFILE.

No matter even if your data is spread over any number of data files but still you can use INFORMATION_SCHEMA.TABLES to get the list of tables.

  TABLES

  Overview The INFORMATION_SCHEMA.TABLES view allows you to get
  information about all tables and views within a database. By default
  it will show you this information for every single table and view that
  is in the database.

  Explanation This view can be called from any of the databases in an
  instance of SQL Server and will return the results for the data within
  that particular database.

  The columns that this view returns are as follows:

  Column name  Data type  Description

  TABLE_CATALOG nvarchar(128) Table qualifier.
  TABLE_SCHEMA nvarchar(128) Name of schema that contains the table.
  TABLE_NAME sysname Table name.
  TABLE_TYPE varchar(10) Type of table. Can be VIEW or BASE TABLE.

(Source: SQL Server 2005 Books Online)

Here is an example of data that was pulled from the AdventureWorks database.  This data was pulled using this query:

SELECT * FROM INFORMATION_SCHEMA.TABLES

To only show a list of tables you would use this query:

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

To only show a list of only the view you would use this query:

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'


How to get notices to the output stream? I have debugging messages in functions. Those message are raised like

RAISE NOTICE 'Value of id : %', id;

I set my log file with \o messages.txt

Then I do what I need to do with \i process.sql

And when the execution is terminated, \o.

The problem is that I don't have the messages raised by notices into messages.txt. The messages are displayed on the screen but I want them to be written in messages.txt

How could I do that ?

I tried to use RAISE LOG... and messages are written in log file... It's not what I want.

I have a work around with

plsql -f /path/to/process.sql > messages.txt 2>&1

but I would like to know how I could use \i and \o on plsql client having the messages into the file specified in \o

My client, on cygwin is psql (PostgreSQL) 8.2.11 and the server version is 9.0.7

I'm afraid that you won't like this answer, but currently it seems impossible.
From the psql documentation:

  Saves future query results to the file filename or pipes future
  results into a separate Unix shell to execute command. If no arguments
  are specified, the query output will be reset to the standard output.

  "Query results" includes all tables, command responses, and notices
  obtained from the database server, as well as output of various
  backslash commands that query the database (such as \d), but not error
  messages.

And as you noticed, there is no way redirecting error messages when using psql interactively.

(I've been playing around with all kinds of redirection from \o to no avail.  It looks like that the query output channel is different from the one getting error messages - and even errors from the server and raised in your procedures go different ways.

test=# SHOW client_min_messages;
 client_min_messages
---------------------
 notice

-- added the above to show it's not a config problem

CREATE FUNCTION raise_test() RETURNS integer AS
$body$
BEGIN
    RAISE NOTICE 'notice';
    RETURN 1;
END;
$body$
LANGUAGE plpgsql;

test=# \o | cat > out.sql
test=# SELECT raise_test(); -- you could put this in a file and call \i your_file, it's just the same
NOTICE:  notice

test=# \o | cat > out.sql 2>&1
test=# SELECT raise_test();
NOTICE:  notice

out.sql contains

 raise_test
------------
          1
(1 row)

in both cases.
This is why I am clueless about what channel/file descriptor is used for outputting the messages raised from a procedure.)
)

(There is a thread on PostgreSQL-hackers that may shed some light on this issue: http://postgresql.1045698.n5.nabble.com/psql-output-locations-td5068313.html )

What one could possibly do is starting psql like

psql test >/tmp/psql.out 2>&1

and this will redirect all output to the specified file.  The only problem with this that you don't even have a prompt, and lose the commandline editing capabilities.

Here is a function to print to stdout and stderr,
useful to track script runs on the cloud.
  CREATE OR REPLACE
Function debug_info(message text) Returns Text
  LANGUAGE plpgsql AS $_$
  declare
      -- Since server time will be in another TZ
      timeis text := to_char(now() at
               time zone 'Asia/Kolkata',
               'YYYY-MM-DD-HH24:MI');
      val    text := '';
  BEGIN
  RAISE   notice 'Date=% [%]', timeis, message;
  val :=  concat(timeis,' ',message);
  Return        val;
  END $_$;

-- Usage in pgsql script
...
select result from debug_info('doing hello world') as result;
..

-- Outputs on stdout
2018-11-11-20:25 doing hello world

-- Output on stderr
Date=2018-11-11-20:25 [doing hello world]


Is there a configuration option that lets me limit the percent of a hard drive that Cassandra uses? I'm doing some testing with a small hard disk and I'd like to basically stop Cassandra in its tracks if a write or compaction or any other service brings that usage of the disk above a certain percentage. Is there any way to do that? How could I approach this problem?

Thank you! :)

I dis not saw such a option by Cassandra configuration, but you can use OS quota to limit disk usage.

Or more sophisticated is to write own small script to stop Cassandra, when Cassandra disk usage is too high. It will not be very long.


Slow backup and extremely slow restores I don't normally work with MySQL but with MS-SQL and am having issues restoring a dump backup of a 9 GB database. I converted it to MS-SQL and it takes a grand total of 4 minutes to restore but the MySQL DB takes over an hour on the same server. The MySQL database is using InnoDB but is there an alternative to speeding up the restores? Both databases are on the same machine, Windows 2008R2 in a VM with a dymanic SANs.

Correction - it takes MS-SQL 1 minute to restore, 1 hour to restore the same database in MySQL

EDIT: mysql.ini (with commented lines removed):

[client]
no-beep
port=3306
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir="C:\Program Files\MySQL\MySQL Server 5.5\"
datadir="C:\ProgramData\MySQL\MySQL Server 5.5\data\"
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=NONE
general-log=0
general_log_file="CM999-SV510.log"
slow-query-log=0
slow_query_log_file="CM999-SV510-slow.log"
long_query_time=10
log-error="CM999-SV510.err"
max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=22M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=43M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=4M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_buffer_pool_size=124M
innodb_log_file_size=63M
innodb_thread_concurrency=9

Try adding this setting to your configuration:

innodb_flush_log_at_trx_commit=0


Behavior of Varchar with spaces at the end When I use a Varchar with spaces it ignores the spaces at the end.

ex:

declare @X varchar(50)

This...

set  @X= 'John'

...is the same as...

set @X= 'John           '

It considers these to be equal.  How can I cause the system to recognize these as different?

Everything is just according the ANSI standard:

Trailing blanks explained:

  SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2,
  , General rules #3) on how to compare strings
  with spaces. The ANSI standard requires padding for the character
  strings used in comparisons so that their lengths match before
  comparing them. The padding directly affects the semantics of WHERE
  and HAVING clause predicates and other Transact-SQL string
  comparisons. For example, Transact-SQL considers the strings 'abc' and
  'abc ' to be equivalent for most comparison operations.

  The only exception to this rule is the LIKE predicate. When the right
  side of a LIKE predicate expression features a value with a trailing
  space, SQL Server does not pad the two values to the same length
  before the comparison occurs. Because the purpose of the LIKE
  predicate, by definition, is to facilitate pattern searches rather
  than simple string equality tests, this does not violate the section
  of the ANSI SQL-92 specification mentioned earlier.

Here's a well known example of all the cases mentioned above:

DECLARE @a VARCHAR(10)
DECLARE @b varchar(10)

SET @a = '1'
SET @b = '1 ' --with trailing blank

SELECT 1
WHERE
    @a = @b
AND @a NOT LIKE @b
AND @b LIKE @a

Here's some more detail about trailing blanks and the LIKE clause.

BUT if you want to differ them - you may decide to use DATALENGTH function instead of LEN, because

SELECT 1 WHERE LEN('John ') = LEN('John')

will put you 1 instead of

SELECT 1 WHERE DATALENGTH('John ') = DATALENGTH('John')

The solution is

to use DATALENGTH function to differ between strings
to cast the string into NVARCHAR type - may be better declare this type to parameter of SP

You can simply add a white space to your condition.

set @X= 'John           \n'

Trailing Spaces in String Functions


When should a primary key be declared non-clustered? While creating a test database for another question I asked earlier, I remembered about a Primary Key being able to be declared NONCLUSTERED

When would you use a NONCLUSTERED primary key as opposed to a CLUSTERED primary key?

Thanks in advance

The question is not 'when should the PK be NC', but instead you should ask 'what is the proper key for the clustered index'?

And the answer really depends on how do you query the data. The clustered index has an advantage over all other indexes: since it always includes all columns, is always covering. Therefore queries that can leverage the clustered index certainly do not need to use lookups to satisfy some of the projected columns and/or predicates.

Another piece of the puzzle is how can an index be used? There are three typical patterns:

probes, when a single key value is seek-ed in the index
range scans, when a range of key values is retrieved
order by requirements, when an index can satisfy an order by w/o requiring a stop-and-go sort

So if you analyze your expected load (the queries) and discover that a large number of queries would use a particular index because they use a certain pattern of access that benefits from an index, it makes sense to propose that index as the clustered index.

Yet another factor is that the clustered index key is the lookup key used by all non-clustered indices and therefore a wide clustered index key creates a ripple effect and widens all the non-clustered indices and wide indices mean more pages, more I/O, more memory, less goodness.

A good clustered index is stable, it does not change during the lifetime of the entity, because a change in the clustered index key values means the row has to be deleted and inserted back.

And a good clustered index grows in order not randomly (each newly inserted key value is larger than the preceding value) as to avoid page splits and fragmentation (without messing around with FILLFACTORs).

So now that we know what a good clustered index key is, does the primary key (which is a data modelling logical property) match the requirements? If yes, then the PK should be clustered. If no, then the PK should be non-clustered.

To give an example, consider a sales facts table. Each entry has an ID that is the primary key. But the vast majority of queries ask for data between a date and another date, therefore the best clustered index key would be the sales date, not the ID. Another example of having a different clustered index from the primary key is a very low selectivity key, like a 'category', or a 'state', a key with only very few distinct values. Having a clustered index key with this low selectivity key as the leftmost key, e.g. (state, id), often makes sense because of ranges scans that look for all entries in a particular 'state'.

One last note about the possibility of a non-clustered primary key over a heap (i.e. there is no clustered index at all). This may be a valid scenario, the typical reason is when bulk insert performance is critical, since heaps have significantly better bulk insert throughput when compared to clustered indices.

When a clustered index is deemed to be more beneficial to the overall system than a clustered PK by using some measure of performance.  There can only be one clustered index on a table.

Example measures of performance are single query time (speed), integration of total query times against the table (efficiency) and having to add many include columns to a to a very large non-clustered index in order to achieve performance similar to clustered (size).

This can happen when data is generally retrieved using an index that is not unique, contains nulls (not allowed in a PK), or the PK was added for a secondary reason (such as replication or audit trail record identification).


Can't start Oracle after Virtual Machine restore As part of a project for a customer of mine, they've given me the complete backup of a physical server (taken by Veeam) which I have then used to make a Bare Metal recovery onto a test VM host I have here (VMWare Workstation Pro)
That's all gone fine and windows starts up with all data intact. I'm having issues getting Oracle going though (yes, this is Oracle running on Windows)
From what I can tell, all datafiles and log files are in place. However when I try to start Oracle, this happens
ORACLE instance started.

Total System Global Area 1.6034E+10 bytes
Fixed Size                  2191576 bytes
Variable Size            1.0972E+10 bytes
Database Buffers         5033164800 bytes
Redo Buffers               26554368 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'F:\DB_DATA\LSTEG\SYSTEM01.DBF'
ORA-01207: file is more recent than control file - old control file

EDIT
After speaking with the customer again, they've told me that the DBF / LOG files are screwed by the backup. Their expectation is that the current database be "trashed" and the schemas to be recreated and then the actual database backup dmp files to be reimported
But how do you remove all existing schemas if you can't even start the db?

And that is why Veeam should not be used to backup a database while it is online.
Just delete the database, create a new emtpy one then proceed with the rest of the steps:
"schemas to be recreated and then the actual database backup dmp files to be reimported".

Reason:  controlfile is older than datafiles.
Solution:  re-create controlfile


Unable to mount oracle instance I am trying to shutdown the system, then I want to mount it. I have tried this:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
SQL> startup mount;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist

I have googled for solution but I can't found the exact reason that causes it to go wrong.

Exit sqlplus.

Make sure your os user is part of the DBA group (ORA_DBA in Windows).

Make sure ORACLE_SID is set to the SID of the database and unset TWO_TASK:

Windows:

set ORACLE_SID=ORCL
set TWO_TASK=

or

Unix/Linux

ORACLE_SID=ORCL
TWO_TASK=
export TWO_TASK

Now, connect as sysdba:

sqlplus / as sysdba

Now, startup the database:

SQL> startup mount;

SQL> alter database open;

Once you have closed the database, you need to reconnect


boolean purpose column char(0) or tinyint(1) For Boolean purpose column which one is better: nullable char(0) or tinyint(1).

I know bool is an alias for tinyint(1) , but in "High Performance MySQL" book that published by O'reilly said:

  "If you want to store a true/false value in a single bit of storage
  space, another option is to create a nullable CHAR(0) column. This
  column is capable of storing either the absence of a value (NULL) or a
  zero-length value (the empty string). "

which one is better for size, performance, indexing or ...

UPDATE:
I found this link useful for this question:
Efficient Boolean value storage for Innodb Tables

You should keep in mind it is impossible to index a CHAR(0) column, even as part of a compound index.

In terms of size nothing beats NULLable CHAR(0); but how many of these do you have? As compared to other data types in your table? Typically when compared to overall data types & size this is neglect-able.

The clearest is BOOL/TINYINT UNSIGNED. It is most obvious what values are expected.

Otherwise please consider many alternatives in my post, and please see good ideas on comments: Choosing MySQL boolean data types

I think you should be using TINYINT(1) because with integer values, you can get values down to 0 and 1. With TINYINT, we only pay with 1 byte of storage. As with CHAR(1), we cannot prevent anyone from INSERTing other values than 0 and 1. But that doesn't really matter, if we're willing to accept that 0 evaluates as False, and all other values as True.  Using TINYINT(1), boolean expressions works very well with your column values.

Size, Performance and Indexing:

I feel that retrieving and sorting integer fields is faster than character fields and it's familiar to show 1s and 0s for on/off flags too.


tuning feed item's table query with multiple condition I have a query like this:

SELECT *
FROM `rss_item`
WHERE `feed_id` = 1 and `timestamp` < 1350698635
ORDER BY `rss_item`.`timestamp` ASC

MySQL: 5.1; table engine: innodb

Explain result:

id  |  select_type  |  table     |  type   |  possible_keys  |  key        |  key_len |  ref     |  rows    |  Extra
1   |  SIMPLE       |  rss_item  |  range  |  timestamp      |  timestamp  |  4       |  NULL    |  133534  |  Using where

Table schema:

CREATE TABLE `rss_item2` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(1000) NOT NULL,
    `description` longtext NOT NULL,
    `url` varchar(200) NOT NULL,
    `url_crc` bigint(20) NOT NULL,
    `date` datetime NOT NULL,
    `timestamp` int(11) NOT NULL,
    `feed_id` int(11) NOT NULL,
    `image` varchar(250) NOT NULL,
    `goto` int(11) NOT NULL,
    `likes` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `timestamp` (`timestamp`,`feed_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2425930 DEFAULT CHARSET=utf8

This query with 400,000 rows takes 4 seconds. I added a composite index on feed_id and timestamp fields. How can I tune this query?

I dont know about feed_id but your timestamp column by definition have probably many differents values. I will advise you to try to create a simple index on timestamp column.

Do you have "disparate" values too on feed_id column?

For me you should take your attention on the timestamp column (WHERE + ORDER BY). Maybe it'll not a index problem, maybe your MySQL instance have some difficulties to sort the result (bad temp table management) so you can try without ORDER BY clause to see its impact.

Let me know.

Max.


Single listener for multiple Oracle homes? I'm in the process of re-hosting two Oracle 12c databases on Unix. In the source environment, the databases were stacked -- they shared the same Oracle home and used the same listener. The listener looked roughly like this:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = foo)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = DATABASE1)
      (ORACLE_HOME = /oracle)
    )
    (SID_DESC =
      (SID_NAME = DATABASE2)
      (ORACLE_HOME = /oracle)
    )

In the new environment, each of the databases has its own Oracle home. I have a listener running out of one of the oracle homes. It looks roughly like this (note the ORACLE_HOMEs):

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = newfoo)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = DATABASE1)
      (ORACLE_HOME = /oracle/DATABASE1)
    )
    (SID_DESC =
      (SID_NAME = DATABASE2)
      (ORACLE_HOME = /oracle/DATABASE2)
    )

I would like for each environment to have its own listener running, but that would mean one of the databases would need to change its port (we're not using virtual IPs so I can't have two databases on the same host using the same port).

(For the sake of argument, imagine that the client is running tons of legacy software and they can't easily change which ports they're pointed at. I realize asking them to simply modify their tnsnames is the easiest option, but let's set that aside for the moment.)

Question: Is it problematic to use a single listener for multiple Oracle homes? I get that it's a single point of failure, but is there also a performance hit? Something else I'm not thinking of?

Question: Is there some other way I can run a listener out of each of the new Oracle homes using the same port? Again, we're not using virtual IPs, so I'm stuck with new host "foonew" + port 1521.

Thank for any answers or guidance you can provide.

Question: Is it problematic to use a single listener for multiple
  Oracle homes?

No.  There really is no valid reason to run multiple Listeners.

You just need to make sure each database "registers" with that one Listener.

  I get that it's a single point of failure, but is there
  also a performance hit?

I'd be more concern with your single node databases being a "single point of failure" before I worry about the Listener as a "single point of failure".

  Something else I'm not thinking of?

K.I.S.S.:  Just use a single Listener.
The Listener does not have to be on the same server as the database
I believe you can use Oracle Grid to run a collection of Listeners.

  Question: Is there some other way I can run a listener out of each of
  the new Oracle homes using the same port? Again, we're not using
  virtual IPs, so I'm stuck with new host "foonew" + port 1521.

No.  This is an OS thing, not a Listener thing.

First of all, the concept of 'each database has it's own port' is bogus.  It's the listener that uses the port, which by default is 1521.  So the idea of haveing separate listeners so that the database can have its own port makes no sense.  Second, please burn this into your brain:  One single listener, with the default name of LISTENER and default port of 1521, is quite capable of -- indeed, was designed to -- service multiple databases of multiple versions running from multiple homes.  I've been doing this since Oracle 7.3 and have _never_seen a valid justification for using multiple listeners.  Even if you already have multiple clients referencing a listener on a non-default port, you can configure the listener to use multiple ports.  But even then, there is no binding between any port the listener might be using and any database the listener is servicing.  Also keep in mind that as part of the listener setting up the connection, it spawns a separate process and informs the client of the port that process will be using.  At that point the listener could be shut down, and existing connections will continue unharmed.


How to set lower case for identifiers in Toad? I'm using Toad to write queries agains an Oracle database. When I write table_name. I get an auto-suggestion menu with all the column names. But if I choose a column name, both the table name and column name is written by uppercases. Is there any way to change this to be written in lowercases instead?

In View > Formatting Option > Case "Identifiers" are set to Lowercase

I found the following on Quest.com.  It works, but may have undesirable side effects.

  Go to View | Toad Options in the main menu.
  On the left-hand side of the options window go to Editor | Behavior.
  Under the Language section, click on the Syntax highlighting button.
  In the new window, under styles on the left hand side, select "Indentifer" and select Lowercase for Capitalization effect on the
  right hand side.
  Under styles on the left hand side, select "Toad_user_table" and select Lowercase for Capitalization effect on the right hand side..
  Click on Apply and OK.

Click on the following menu items:

View -> toad options -> Editor -> Behaviour

Then check Use lowercase object names from select windows


Database in transition I have a database that has been locked in transition for over 24 hours.  I found a similar thread that discusses how to find the commands that are running but I do not know how to unlock the database.  How can I resolve this transition state?

Query to get currently running commands:

use _MYDB_
go

select
    l.resource_type,
    l.request_mode,
    l.request_status,
    l.request_session_id,
    r.command,
    r.status,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    request_sql_text = st.text,
    s.program_name,
    most_recent_sql_text = stc.text
from sys.dm_tran_locks l
left join sys.dm_exec_requests r
on l.request_session_id = r.session_id
left join sys.dm_exec_sessions s
on l.request_session_id = s.session_id
left join sys.dm_exec_connections c
on s.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
outer apply sys.dm_exec_sql_text(c.most_recent_sql_handle) stc
where l.resource_database_id = db_id('_MYDB_')
order by request_session_id;

Results:

Attempting to kill the offending process failed with the same error.  In the end I had to reboot the server to get by the issue.


SQL server memory pressure First observed few months ago at end-user's laptop (rather slow i3 CPU, 4GB RAM, SSD) with SQL 2014 Express. The problem persists with SQL 2019, even with clean Windows 10 1903 installation.
The database has 250MB so pretty small even for Express Edition. Application mostly works smooth, but there are time to time very looong pauses, while simple query which is usually finished within 2 secs timeouts after 5 mins! It's client machine, there are no other queries nor databases.
Recently the same problem start occurring on my dev machine too. I can confirm the memory pressure in Windows event log:
A significant part of sql server process memory has been paged out. This may result in a performance degradation.
Duration: 7204 seconds. Working set (KB): 203376, committed (KB): 577992, memory utilization: 35%%.

Based on this MSDN article about paging I should turn on Locked pages. There is a caveat that the hungry SQL Server can eat all free memory, so user can't work at all. Are my worries relevant?
What struggles me most is that it start happening quite recently (approx. 2 months ago) while there is no significant change at my workload as well as 16GB RAM. Second, why freeing few hundreds MBs is taking several minutes with NVMe drive (and in my case also quite performant CPU).
Are there some recent changes how Windows 10 / SQL server handles out of memory states?

To me this sounds like a clear case of using Lock Pages in Memory and setting a reasonable max and min server memory.


Update request using table alias Executing this request:

update table t1 set t1.column = 0 where t1.column2 = 1234

Getting this error:

  column "t1" of relation "table" does not exist

This request runs fine in MySQL.
Why do I get this error in PostgreSQL?

I'm not sure if that's your desired syntax or not. Check your syntax for UPDATE

Currently, that's

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

So if you provide table t1, it's getting parsed as a table named table. Actually, to do that you need to have it in quotes "table" t1 which you're doing, or your library is doing.

As a design note, don't do that. In fact, don't name anything any SQL keywords.
But, if you want to have some fun and see what's happening we can play..

Let's create some text data,

CREATE TABLE "table" AS
SELECT x AS column, x AS column2
FROM generate_series(1,12345) AS t(x);

Now we can try your original query and get your original result,

UPDATE "table" t1 SET t1.column=0 WHERE t1.column2=1234;
ERROR:  column "t1" of relation "table" does not exist
LINE 1: UPDATE "table" t1 SET t1.column=0 WHERE t1.column2=1234;

And that's the problem you're getting. As with the table, if you're going to use a SQL keyword, you need to quote it. Interestingly, that's not enough here.

UPDATE "table" t1 SET t1."column"=0 WHERE t1.column2=1234;
ERROR:  column "t1" of relation "table" does not exist
LINE 1: UPDATE "table" t1 SET t1."column"=0 WHERE t1.column2=1234;

In addition to that, it seems that table aliasing is not supported in the SET list, regardless of whether or not the column is reserved keyword.

UPDATE "table" t1 SET "column"=0 WHERE t1.column2=1234;

Why it's currently Working As Designed

Why you can not use aliases, xocolatl from IRC helps with that,

  < xocolatl> EvanCarroll: the reason you can't use the alias on the left of the = is because of composite types

  < xocolatl> EvanCarroll: so, it's not a bug but WAD

So in code to CREATE a table with a custom composite type an execute an UPDATE on it.

CREATE TYPE foo AS ( x int, y int );

CREATE TABLE foobar AS
  SELECT v::foo AS mycol
  FROM ( VALUES (1,2), (2,100) ) AS v;

UPDATE foobar SET mycol.x = 9;

So the syntax that permits the . is mycol.type-address, not tablealias.col-name.

Solving the ambiguous syntax problem

If that didn't make sense, any behavior but this behavior would give you an ambiguous syntax,

CREATE TYPE foo AS ( mycol int, x int );

CREATE TABLE mytable AS
  SELECT v::foo AS mycol, 1 AS x
  FROM ( VALUES (1,2), (2,100) ) AS v;

UPDATE mytable AS mycol SET mycol.x = 9;

What does mycol.x refer to there? As is it's not ambiguous, table-referencing and table-aliasing is disabled, so it's definintely 100% of the time a composite-type named mycol, on the table mytable.

While update Query :
If you use Allis with Table Name Then in Set data you does not need to give reference through Allis like this

UPDATE data Q SET is_active = FALSE ,is_delete = TRUE WHERE Q.name = 'XYZ';


Literal does not match format string error in oracle I was given a SELECT statement that is running in a Crystal Report to investigate a data problem but when I try to run it against the database I get the error:

  ORA-01861: literal does not match format string
  01861. 00000 -  "literal does not match format string"
  *Cause:    Literals in the input must be the same length as literals in
             the format string (with the exception of leading whitespace).  If the
             "FX" modifier has been toggled on, the literal must match exactly,
             with no extra whitespace.
  *Action:   Correct the format string to match the literal.

I think it has to do with the Date comparisons but I am not sure what is wrong. I know nothing about oracle and since the error message does not specify the line the error is on I am only guessing at what might be the problem. Can anyone point me to what is causing this problem?

   SELECT ACTDET.GROSS_VOLUME,
          ACTDET.NET_VOLUME,
          ACTIVITY.SHIPPER_ID,
          ACTIVITY.ACTIVITY_START_DATE,
          SHIPPER.NAME_1,
          TANK.TANK_PRODUCT_CODE,
          PRODUCT.DESCRIPTION_2,
          SITE.NAME_1,
          SITE.NAME_2
   FROM   G3USER.ACTIVITY ACTIVITY,
          G3USER.ACTDET ACTDET,
          G3USER.SHIPPER SHIPPER,
          G3USER.TANK TANK,
          G3USER.SITE SITE,
          G3USER.PRODUCT PRODUCT
   WHERE ((ACTIVITY.TERMINAL_ID=ACTDET.TERMINAL_ID) AND (ACTIVITY.BOL_NUMBER=ACTDET.BOL_NUMBER))
   AND ((ACTIVITY.TERMINAL_ID=SHIPPER.TERMINAL_ID) AND (ACTIVITY.SHIPPER_ID=SHIPPER.SHIPPER_ID))
   AND ((ACTDET.TERMINAL_ID=TANK.TERMINAL_ID) AND (ACTDET.TANK_ID=TANK.TANK_ID))
   AND (ACTDET.TERMINAL_ID=SITE.TERMINAL_ID)
   AND ((TANK.TERMINAL_ID=PRODUCT.TERMINAL_ID AND (TANK.TANK_PRODUCT_CODE=PRODUCT.PRODUCT_CODE))
   AND (ACTIVITY.ACTIVITY_START_DATE >= '2014-02-05 00:00:00') AND (ACTIVITY.ACTIVITY_START_DATE < '2014-02-06 00:00:00'));

Oracle doesn't understand the literal date format strings, as they are not in the same format that your client is configured for. You need to use the TO_DATE function to tell Oracle the format of the date string you are presenting it with, like so:

AND (ACTIVITY.ACTIVITY_START_DATE >= TO_DATE('2014-02-05 00:00:00','YYYY-MM-DD HH24:MI:SS'))
AND (ACTIVITY.ACTIVITY_START_DATE < TO_DATE('2014-02-06 00:00:00','YYYY-MM-DD HH24:MI:SS')));

You could also represent your dates using ANSI syntax (seeing as they have no time component):

AND (ACTIVITY.ACTIVITY_START_DATE >= DATE'2014-02-05')
AND (ACTIVITY.ACTIVITY_START_DATE < DATE'2014-02-06'));

If ACTIVITY.ACTIVITY_START_DATE has the DATE data type, the comparison should look something like this (you cannot always compare dates with stings in the Oracle Database):

...ACTIVITY.ACTIVITY_START_DATE >= TO_DATE('2014-02-05 00:00:00','YYYY-MM-DD HH24:MI:SS')


The database manager failed to allocate shared memory because an operating system kernel memory limit has been reached I have installed IBM DB2 10.5.5 and I want to create a new database there.

When I entered command

db2 create database test1

I am getting a error saying

SQL1084C  The database manager failed to allocate shared memory because an
operating system kernel memory limit has been reached.  SQLSTATE=57019

Following is the output I get when I run "ipcs -l"

 ------ Shared Memory Limits --------
max number of segments = 10000
max seg size (kbytes) = 4882812
max total shared memory (kbytes) = 33554432
min seg size (bytes) = 1

------ Semaphore Limits --------
max number of arrays = 4096
max semaphores per array = 250
max semaphores system wide = 1024000
max ops per semop call = 32
semaphore max value = 32767

------ Messages Limits --------
max queues system wide = 16384
max size of message (bytes) = 65536
default max size of queue (bytes) = 16384

What is the reason for this? How can I fix it?
I am running on Ubuntu 14.04 with 8GB physical memory.

I just had the same problem! In my case the problem was just the memory of the DB2 instance.

In windows type:

db2 get dbm cfg | findstr MEMORY

In linux:

db2 get dbm cfg | grep MEMORY

You will get something like that:

Global instance memory (4KB)          (INSTANCE_MEMORY) = 500000

The solution in my case was simply set the same threshold value but by activating the automatic mode:

db2 update dbm cfg using INSTANCE_MEMORY 100000 automatic immediate

The command "immediate" apply the settings now without the need to restart the instance. So if you get the settings again will have:

Global instance memory (4KB)          (INSTANCE_MEMORY) = AUTOMATIC(5000000)

Now try to connect to your database again. I believe that will again connect normally.

According to the DB2 documentation:

  This parameter specifies the maximum amount of memory that can be
  allocated for a database partition if you are using DB2® database
  products with memory usage restrictions or if you set it to a specific
  value. Otherwise, the AUTOMATIC setting allows instance memory to
  grow as needed.

Font: IBM Knowledge Center

First check the memory limits in the output of

ulimit -a

as the instance owner and increase them if necessary.

Then just simply check the output of

free

I can see that you have sufficient physical memory but there is no proof that there is enough FREE memory for db2 to do its job.

And the most general, almost rtfm but inevitable hint is to read what

db2 '?' SQL1084C

Usually that is the minute when one slaps his/her forehead and self-miscalling words are heard.


Why is MYSQL not starting? When running the command 'sudo service mysql start' this is the output.
[FAIL] Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!

Here are the steps taken to troubleshoot already:
1. Checked permissions for /var/run/mysqld, it's owned by mysql but the user is root.
2. The syslog file has been checked and gives the following,

ERROR 2002 (HY000): 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!

After checking the my.cnf file the path's for the socket matched.

When running which mysql this is the output /usr/bin/mysql

Monit has recently been installed on the server as well as the port was changed to port 99.

Everything has been running fine up until now. (About a week and a half after said changes were made.)

Linode has been rebooted a couple of times to troubleshoot memory issues but to no avail. Here is some output from various commands

            user@hostname:~$ df -h
            Filesystem      Size  Used Avail Use% Mounted on
            /dev/root        24G  7.3G   16G  32% /
            devtmpfs        494M     0  494M   0% /dev
            tmpfs           100M  208K   99M   1% /run
            tmpfs           5.0M     0  5.0M   0% /run/lock
            tmpfs           250M     0  250M   0% /run/shm

            user@hostname:~$ df -i
            Filesystem      Inodes  IUsed   IFree IUse% Mounted on
            /dev/root      1556480 177140 1379340   12% /
            devtmpfs        126432   1379  125053    2% /dev
            tmpfs           126898    780  126118    1% /run
            tmpfs           126898      2  126896    1% /run/lock
            tmpfs           126898      2  126896    1% /run/shm

            user@hostname:~$ free -m
                         total       used       free     shared    buffers     cached
            Mem:           991        501        489          0         52        237
            -/+ buffers/cache:        211        779
            Swap:          255          0        255

We have ruled out the possibility of a failed mysql process that is still running with the following commands

            user@hostname:/var/run/mysqld$ ps aux | grep mysql
            user  28834  0.0  0.2   7856  2068 pts/0    S+   13:00   0:00 grep mysql
            user@hostname:/var/run/mysqld$ kill
            kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
            user@hostname:/var/run/mysqld$ kill mysql
            -bash: kill: mysql: arguments must be process or job IDs
            user@hostname:/var/run/mysqld$ kill 28834
            -bash: kill: (28834) - No such process
            user@hostname:/var/run/mysqld$ kill 7856
            -bash: kill: (7856) - No such process
            user@hostname:/var/run/mysqld$ kill 2068
            -bash: kill: (2068) - No such process
            user@hostname:/var/run/mysqld$ man kill
            user@hostname:/var/run/mysqld$ kill -l
             1) SIGHUP   2) SIGINT   3) SIGQUIT  4) SIGILL   5) SIGTRAP
             6) SIGABRT  7) SIGBUS   8) SIGFPE   9) SIGKILL 10) SIGUSR1
            11) SIGSEGV 12) SIGUSR2 13) SIGPIPE 14) SIGALRM 15) SIGTERM
            16) SIGSTKFLT   17) SIGCHLD 18) SIGCONT 19) SIGSTOP 20) SIGTSTP
            21) SIGTTIN 22) SIGTTOU 23) SIGURG  24) SIGXCPU 25) SIGXFSZ
            26) SIGVTALRM   27) SIGPROF 28) SIGWINCH    29) SIGIO   30) SIGPWR
            31) SIGSYS  34) SIGRTMIN    35) SIGRTMIN+1  36) SIGRTMIN+2  37) SIGRTMIN+3
            38) SIGRTMIN+4  39) SIGRTMIN+5  40) SIGRTMIN+6  41) SIGRTMIN+7  42) SIGRTMIN+8
            43) SIGRTMIN+9  44) SIGRTMIN+10 45) SIGRTMIN+11 46) SIGRTMIN+12 47) SIGRTMIN+13
            48) SIGRTMIN+14 49) SIGRTMIN+15 50) SIGRTMAX-14 51) SIGRTMAX-13 52) SIGRTMAX-12
            53) SIGRTMAX-11 54) SIGRTMAX-10 55) SIGRTMAX-9  56) SIGRTMAX-8  57) SIGRTMAX-7
            58) SIGRTMAX-6  59) SIGRTMAX-5  60) SIGRTMAX-4  61) SIGRTMAX-3  62) SIGRTMAX-2
            63) SIGRTMAX-1  64) SIGRTMAX

You may have corruption in your database.
Add the following to your my.cnf  under [mysqld] file and see if mysql will start

innodb_force_recovery = 1

Increment the value as needed.
See http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html


Is it possible to refresh PgAdmin 4 query without restarting a new query after disconnect? Sometimes I leave a PgAdmin session alone for a while and when I come back to issue a query, I get an error

Server closed the connection unexpectedly.
        This probably means the server terminated abnormally
        before or while processing the request.
******* Error **********

So, I know I just need to reconnect but it seems like I have to close the query window and reconnect and open a new query window for things to work.

Is it possible to reconnect and use the same query window?

It has been suggested that I click the execute button again after the error comes, and PgAdmin will reconnect the instance and execute the query. However, that does not work. PgAdmin is not reconnecting no matter how many times I try to re-execute.

Re execute the query without closing it. It will give the result. it is working for me


Insert data from 1 column to many columns in another table (using 1 line) in sql I have a table (table1) in which IDs of transactions, transaction state (there are 10 total states) and their time of entering that state are stored.

I want to feed this data into another table which would have 11 columns: 1 for the Transaction ID and the other 10 for the time that they enter each state.

I want to write this as an SQL query. I looked for it and found

insert into table2(ID, s1, s2, s3, s4 ... )
select ID, time from table1
where [];

but I don't have 10 different columns in table1 to copy from as there are 10 different rows corresponding to each ID and each state.

Please help me out in doing this is 1 query.

I am new to SQL. Thanks in advance.

Please try below if something helpful

insert into table2(ID, s1, s2, s3, s4)
select ID, time as t1, time as t2, time as t3, time as t4
from table1 where [];


Running Oracle Export Utility within a Shell script I have wasted a lot of time on this.  Any ideas of what I am doing wrong here?

Script in bash

#!/usr/bin/bash
.
.
.

/export/home/app/oracle/product/10.2.0/bin/exp $uname/$passw \
    FILE="$WORKSPACE"/"$EXP_FILE" \
    LOG="$EXP_LOG" \
    TABLES=SYSTEM_PARAMETERS\
    CONSISTENT=Y \
    ROWS=Y \
    INDEXES=Y \
    CONSTRAINTS=Y \
    GRANTS=Y ;

Error Message

/export/home/app/oracle/admin/ukediuat/exp >./exp_syspar.sh
Username: USER
Enter DPDADMIN Password:

LRM-00116: syntax error at 'TABLES' following '='

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully

Problem

The issue is with the dollar signs in the export command.  By replacing the variables which use the dollar sign with the full path the script runs.

Is there anyway to parse the shell variables into the export command?

Try changing the tables parameter to this.
You only need parentheses when you have more than one table.

TABLES='SYSTEM_PARAMETERS' \

$ exp tables=system.plan_table file=plan_table.dmp statistics=none

Export: Release 11.2.0.2.0 - Production on Tue Feb 17 08:59:05 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                     PLAN_TABLE          4 rows exported
Export terminated successfully without warnings.

#!/usr/bin/bash

TABLES=$DBUser."TBLTACCOUNTHISTORY"
directory=DATA_PUMP_DIR
dumpfile="ABC.dmp"
logfile="ABC.log"

Then run with:

expdp $SysDBUser/$SysDBPassword directory=$directory dumpfile=$dumpfile logfile=$logfile tables=$TABLES


Unattended install to listen on specified interface I'm looking to install several versions of SQL Server side-by-side to run simultaneously. I'd like to have them listen on different IP addresses.

They will be running on Windows Server 2019 Core.

I currently install using something like this:

Setup.exe /qs /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="NT AUTHORITY\Network Service" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /SAPWD="MYSECRETPASSWORD" /SQLSVCINSTANTFILEINIT="True" /TCPENABLED=1 /IACCEPTSQLSERVERLICENSETERMS /SECURITYMODE=SQL

Am I right that there are no switches on Setup.exe that specify the IP address to listen to? If not what is the simplest way of configuring this from the command line after Setup.exe has run?

With SQL Server on Windows, the registry entries can be modified using PowerShell. Below is an example with SQL Server 2017 paths for the default instance. Of course, the instance will need to be restarted for changes to become effective.

Set-ItemProperty -Path 'HKLM:\software\microsoft\microsoft sql server\mssql14.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -Name tcpdynamicports -Value ''
Set-ItemProperty -Path 'HKLM:\software\microsoft\microsoft sql server\mssql14.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -Name tcpport -Value 1433

EDIT:

One can also target specific network interfaces by identifying the desired key and changing as desired. Below is an example PS function that finds the entry by IP address and modifies the values. This can be used to enable/disable TCP for each instance and interface as needed.

Function Set-SqlInterfaceProtocol($SQLInstanceTcpPath, $IPV4Address, $Enabled, $Active, $Port) {

    $interface = Get-ChildItem $SQLInstanceTcpPath `
        | ForEach-Object {Get-ItemProperty $_.pspath} `
        | Where-Object -Property IPAddress -EQ $IPV4Address

    if($interface -eq $null) {
        throw "No entry found for IP address $IPV4Address"
    }

    Set-ItemProperty -Path "$SQLInstanceTcpPath\$($interface.PSChildName)" -Name "Enabled" -Value $Enabled
    Set-ItemProperty -Path "$SQLInstanceTcpPath\$($interface.PSChildName)" -Name "Active" -Value $Active
    Set-ItemProperty -Path "$SQLInstanceTcpPath\$($interface.PSChildName)" -Name "TcpPort" -Value $Port
}

$SQLInstanceTcpPath = "HKLM:SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp"
Set-SqlInterfaceProtocol -SQLInstanceTcpPath $SQLInstanceTcpPath -IPV4Address "10.1.1.123" -Enabled "1" -Active "1" -Port "1433"

Note this script is not well-tested so you may need to tweak it for your scenario.

You can't have more than one IP adress for standalone sql servers. Which is server's IP adress. You can (and must) only set the port after setup.


Locale is incompatible error in Postgresql 10 when replicating from Ubunu to Win 10 The locale available in my Ubuntu PG-10 DB is en_US.UTF-8. When doing PG_Basebackup to a PG-10 DB in Win-10, Psql failed to connect with error

  psql: FATAL:  database locale is incompatible with operating system
  DETAIL:  The database was initialized with LC_COLLATE "en_US.UTF-8",
  which is not recognized by setlocale(). HINT:  Recreate the database
  with another locale or install the missing locale.

The locale available in Win10 are

                          List of databases
   Name    | Owner      | Encoding | Collate | Ctype |
-----------+------------+----------+---------+-------+-
 postgres  | postgres   | UTF8     | en-US   | en-US |

                                           List of databases
   Name    | Owner    | Encoding |          Collate           |           Ctype            |
-----------+----------+----------+----------------------------+----------------------------+
 postgres  | postgres | UTF8     | English_United States.1252 | English_United States.1252 |

But the above locale are not taking when doing the Basebackup. Any fix for this issue.

You can't copy a data directory (which is what pg_basebackup does) between two different operating systems. The missing locale is just one symptom. If you need to replicate between Linux and Windows use logical replication – a_horse_with_no_name

Use this "localedef -f UTF-8 -i en_US en_US.UTF-8" and reboot.


What is the industry term for superset of "metrics" and "dimensions"? If I want to refer to measures/metrics and dimensions with a single term, what would be a good industry-accepted expression?

Metrics is used commonly in UIs as well such as Google Analytics or Tableau.

The context for my question is for a UI. I'd like to group both of them and refer to them with an industry-accepted name, if one exists.

Dimensions and Measures are the two main components of cubes. The dimensions are basically ways to look at the data while the measures are the data.

When writing MDX the measures are a member of the [Measures] dimension but that is mostly a syntax convention.

As for a common name, I would say they both are partly definitions of a cube design. The measures define the values and the dimensions define how you slice or aggregate the measures.

The combined result of a query across dimension members and measures is called a cellset but that term is used for a result rather than a cube definition (which is what measures and dimensions are).

If there even is a common term for the combination of dimensions and measures it would be physical aspects of data aggregation

If I were to design a UI where I list both in an interface for users to add a report I would probably call them 'report items' or 'data components'  or something but I think the clearest for an end user would be to follow  what Excel does and label them 'rows' or 'columns' and 'values' where things you can add to rows/columns are the dimensions and values are the measures.

I don't think it's any clearer to an end user to try and find a common name and from a UI design perspective I would make a distinction between things they should put on an axis and 'values'.

I would call the screen "Measuring" and there you can define your "Dimensions", like X, Y, Z, time and then use the dimensions to perform "Measures" (is that a word?) or "Measurements" like 5 meters, 20 seconds.


Cloning the database to another server with raw data files I'm trying to clone the database of server 1 to server 2 with raw data files. I'm using Linux 7 and Oracle 11g R2. I have the instance running with same SID in Server 2 but file structure is different. The database is about 85 GB.
I have got the data files, control file, redolog files, spfile and init.ora.  Can someone explain me the way to clone in  this way ?

One can use rman to duplicate a database. Here is the Database Backup and Recovery User's Guide.

But now let's assume that you have a copies of these files that where made when the database was down and where already copied to the places of the target system.

On the target server 1 you have the same type of perating system as on the source server 1. On the target server you have the same Oracle version with the same patch level as on the source server. You can check this by setting the oracle environment and executing the command

$ORACLE_HOME/OPatch/opatch lsinventory

and compare the output of both servers.

If you don't have the right environment you can clone the Oracle software from the source server using Oracle Universal Installer.

So let's assume that you have the correct software installed and set the correct environment of the Oracle instance of the target server.

what happens if you do a startup nomount of the database in sqlplus?

sqlplus first searches for

${ORACLE_HOME}/dbs/pfile${ORACLE_SID}.ora

if such a file does not exist it searches for

 ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora

and oracle is started with the parameters given in this files. I am not 100% sure about that. Maybe sqlplus starts oracle executable and the oracle executable searches for these files. But that does not matter for our purposes.

So the parameters of these file should have sensible values. This means, the pathes in these files should point to the correct files.

If not, you have to change them. The init.ora file can be modified with a text editor. An spfile must be transformed to a init.ora file by executing

create pfile='/tmp/myfile.txt' from spfile='&absolute_spfile_path';

in sqlplus while the instance is not running. &absolute_spfile_path must be substituted by the correct path.

then you can edit the file /tmp/myfile.tx with a text editor. You can change the pathes, if an instance name is in the file it must be modified, too. Memory parameters can be adapted. Underline parameter written by the instance should be removed, e.g. '_sga_target' and similar parameters.

and then create a modified spfile by issuing

create spfile='&absolute_spfile_path' from pfile='/tmp/myfile.txt';

Before you start you can make a copy of your files so that you can start again when something went wrong.

Make a copy of the init.ora and spfile.ora, the control files, the redo log files and the datafile of th SYS, SYSAUX and undo tablespaces. the datafile of the remaining tablespaces make readonly with the appropriate Linux command(chmod) so they can't be modified by oracle. Tempfiles are not needed from the source database.

Now you can start the instance with

startup mount

check the alert log if everything is ok. If there are errors then repair tthem.

If everything is fine then mount the dtabase

alter database mount;

Now the control file is read. You can rename all files now. In the control file their old name is stored. you can see this if you do a

select name
from v$datafile;

issue an

alter database rename file '&old_name' to '&new_name';

until all datafile are removed.

The same command can be used to rename the log files.

select member
from v$logfile;

shows you the names of the redo log files in the controlfile.

You can put the tempfiles offline to avoid eror messages when openinf the database

select name
from v$tempfile;

shows you then namesof the temp files.

alter database '&tempfileName' offline;

puts the file offline.

Now you can open the database

alte database open;

Now add new temp files with correct path and size to your temp tablespaces.

alter tablespace TEMP add tempfile ...;

and then remove the old ones

alter tablespace TEMP drop tempfile ...;

If you have made the daatafiles read only then shutdown the database, make the files read/write and start the database again.

Now you are done.

As a side note here, in case you want to copy all the files, it is not a "must" to stop the DB, you can use :

alter database begin backup;

{copy datafiles}

alter database end backup;

This way you will have the DB available and you will be able to copy the datafiles and clone in the way it was explained before.


Changing database compatibility from legacy CE to 120 Just seeking an expert/practical advise from DBA point of view where one of our application DB running on SQL 2014 after migration had old DB compatibility level i.e 100.(SQL2008)

From DEV point of view all the testing has been done and they dont see much diff and want to move to prod based on their testing.

In our testing ,For certain process where we see slowness like in SP's we found the part of statement that was slow and added query traceon hint , something like below keeping compat to 120, which helps keeping performance stable

SELECT  [AddressID],
    [AddressLine1],
    [AddressLine2]
FROM Person.[Address]
WHERE [StateProvinceID] = 9 AND
    [City] = 'Burbank'
OPTION (QUERYTRACEON 9481);
GO

UPDATE- Editing question based on more findings-

Actually we found things getting worst for a table which calls scalar function within a computed column-

below is how that column looks

CATCH_WAY AS ([dbo].[fn_functionf1]([Col1])) PERSISTED NOT NULL

and part of query where it goes weird is somewhat looking like below

DELETE t2
   OUTPUT del.col1
          del.col2
          del.col3
   INTo #temp1
FROM #temp2 t2
INNER JOIN dbo.table1 tb1 on tb1.CATCH_WAY = ([dbo].[fn_functionf1](t2.[Col1])
AND t2.[col2] = tb1.[col2]
AND t3.[col3] = tb1.[col3]
AND ISNULL (t2.[col4],'') = ISNULL (tb1.[col4],'')

I know function is being called and is slow but the problem is with current compat i.e. 100 runs OK'ish slow but when changed to 120 it gets X100 times slow and if kept at 100 its X100 faster.
What is happening ?

This regression in performance usually happens because you already had a bad query that was considered "fast enough" under the old compat, but is super slow under the new compat, due to the weird/bad way it was written.

I'd get rid of the 2 functions in your DELETE statement's ON clause, by pre-computing them and storing them into your temp tables before the DELETE happens.

You could also try to rewrite the ISNULL parts, since ISNULL is also a function.

--new temp table to store table1
SELECT col2
    ,col3
    ,col4
    ,CATCH_WAY --this scalar function gets calculated here and saved into the temp table
INTO #tempTable1
FROM dbo.table1;

DELETE t2
   OUTPUT del.col1
          del.col2
          del.col3
   INTO #temp1
FROM #temp2 t2
JOIN #tempTable1 tb1
ON tb1.CATCH_WAY = ([dbo].[fn_functionf1](t2.[Col1]) --not a good idea
AND t2.[col2] = tb1.[col2]
AND t3.[col3] = tb1.[col3]
AND ISNULL (t2.[col4],'') = ISNULL (tb1.[col4],'') --not a good idea, the ISNULL "hides" the columns from the SQL Optimiser


Check Orders that can be delievered with reducing quantity from stock Just like any retail business we have an Orders table and an Inventory table. What I am trying to do is to check Orders for which we have enough stock available to dispatch. A few things I need to consider:

If all the items in an order are available only then consider this order to be “Deliverable”

Check Order's deliverable status in the order of OrderID (int value) .i.e OrderID 1 then 2 and so on.

Before checking for deliverability of next order, reduce the available of stock for the next order. (not update the Inventory table but just take into account the stock quantity that has been already consumed by previous orders).

If we do not have enough stock for 1 or more items in the order, completely ignore the order and do not reduce available stock quantity for the next order to be checked.

In the following example:

Order 100 is fully Deliverable because we have enough stock for all products.
Order 200 is not fully Deliverable because PID 2 requires Qty 5 but we only have 3 left after 2 being consumed by the Order 100
Finally, Order 300 is also fully Deliverable because we have enough stock for all products.

Test Data
INSERT INTO @Inventory(PID, Qty)
VALUES  (1  , 10)
    ,   (2  , 5)
    ,   (3  , 2)

INSERT INTO @Order (OrderID, PID, Qty)
VALUES  (100 , 1 , 2)   --\
    ,   (100 , 2 , 2)   ----> This order is fully available
    ,   (100 , 3 , 1)   --/

    ,   (200 , 1 , 2)   --\
    ,   (200 , 2 , 5)   ----> This order is not fully available
    ,   (200 , 3 , 1)   --/     because of PID 2 only 3 QTY left

    ,   (300 , 1 , 2)   --\
    ,   (300 , 2 , 2)   ----> This order is fully available
    ,   (300 , 3 , 1);  --/

Expected Output
OrderID Status
100     Deliverable
200     NOT Deliverable
300     Deliverable

My Attempt
I know that it is far from the actual solution but I still wanted to share what I have been trying :)
WITH OrderCTE AS
    (
        SELECT DENSE_RANK() OVER (ORDER BY OrderID) AS OrderRN
            , OrderID
            , PID
            , Qty
        FROM @Order
    )
,CTE AS
(
    SELECT o.OrderID
         , o.PID
         , o.Qty
         , i.Qty - o.Qty AS QtyAvailable
         , o.OrderRN  AS OrderRN
    FROM OrderCTE o
    INNER JOIN @Inventory i ON i.PID = o.PID
    WHERE o.OrderID IN (
                            SELECT TOP 1 o.OrderID
                            FROM @Order o
                            WHERE NOT EXISTS ( SELECT 1 FROM @Inventory i WHERE i.PID = o.PID AND i.Qty < o.Qty)
                            ORDER BY o.OrderID
                        )

    UNION ALL

    SELECT o.OrderID
         , o.PID
         , o.Qty
         , o.Qty - c.QtyAvailable
         , c.OrderRN + 1
    FROM OrderCTE o
    INNER JOIN @Inventory i ON i.PID = o.PID
    INNER JOIN CTE c ON c.OrderRN + 1 = o.OrderRN AND c.PID = o.PID
    WHERE o.Qty <= c.QtyAvailable

)
SELECT *
FROM CTE

I'm not a big fan of cursor but it seems like a good case for it.
Declare OrderCursor Cursor
Local Static Read_Only Forward_only
For select distinct OrderID from @Order order by OrderID;

declare @OrderID INT;
create table #Remaining_Inventory  (PID int, Qty int);
create table #Result (OrderID int, Stat varchar(20));

insert into #Remaining_Inventory
select * from @Inventory;

OPEN OrderCursor
FETCH NEXT FROM OrderCursor INTO @OrderID;

    WHILE @@FETCH_STATUS = 0
    BEGIN

    if not exists (
    select 1
    from @Order o
    left join #Remaining_Inventory i on o.PID = i.PID and o.Qty <= i.Qty
    where o.orderid = @OrderID
    and i.PID is null
    )
        begin
            insert into #Result select @OrderID, 'Deliverable';
            update #Remaining_Inventory set #Remaining_Inventory.Qty= #Remaining_Inventory.Qty-o.Qty
            from #Remaining_Inventory
            join @order o on #Remaining_Inventory.PID = o.PID;
        end
    else
        insert into #Result select @OrderID, 'Not Deliverable';

        FETCH NEXT FROM OrderCursor INTO @OrderID
    END

    select * from #Result;

    drop table #Remaining_Inventory;
    drop table #Result;
    CLOSE OrderCursor
    DEALLOCATE OrderCursor

will return the expected result.

I'd be tempted to take an optimistic approach.
put everything in a transaction
do the UPDATE and output a resultset with the new balances - if anything is negative/overdrawn do a ROLLBACK otherwise do a COMMIT
UPDATE with OUTPUT


Scale vs. Precision - Why this definition? From the Microsoft documentation concerning T-SQL / SQL Server:

  Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.

To the naive non-expert (=me) this is always a bit confusing. I would have expected it the other way round.

How does the total number of digits relate to the everyday concept of precision (how 'exact' something is measured)?
How can I think of the number of decimals in terms of scale (how 'big' something is)?

Just to be clear: This is not a rant disguised as a question, and more than mere curiosity. I am convinced that there is a good reason for this terminology, and I think I can learn about databases by understanding the underlying thinking here. (If this question is considered too basic for here, I understand.)

Precision and Scale are based on the mathematical concept of Significant Figures.  You're not alone in that the current definition/usage for these terms is confusing.

  How does the total number of digits relate to the everyday concept of precision (how 'exact' something is measured)?

First, you must remember that everything in computers is represented in binary; however not all numbers are easily represented using binary syntax, specifically when it comes to certain fractions.  One such fraction, ⅓, is actually impossible to represent in a computer.  Even with the largest amount of memory possible, a computer will never be able to accurately represent ⅓ in binary form.  However, it can get more precise (e.g. close) to ⅓ with the more bits (e.g. and therefore decimal places) that are thrown at it.

Precision can therefore be thought of as how exact a number you want to represent.  The more digits the more precise that number becomes.

  How can I think of the number of decimals in terms of scale (how 'big' something is)?

Since Precision is a measure of how exact the number you wish to represent is and we know we're limited in how much we can store, you have to sacrifice precision for scale.  So basically the smaller the scale the larger the final value can become.

Scale may best be explained with how we look at maps.  A map of a state or province shows a much larger scale of area with less detail, whereas a map of a city shows a smaller area at greater detail.

Scale can therefore be thought of as the amount of detail.  The more detail we want to convey comes with the cost of showing less actual stuff.

I don't know if that helps you at all, but hopefully that helps provide some context behind the definitions of these terms.

You miss one critical thing. Critical brutally in the past, critical for anyone not doing trivial work these days.

Storage.

Total number of digits has direct implication on storage, and thus on GB and IOPS needed. From that then you allocate a portion to precision.

Total number of digits has no meaning at all as everyday concept. But those of us who deal with large databases often live with it. Saving a handfull of bytes on a hundred billion row dable is not exactly bad for performance.

It is just the way of thinking - start with allocated storage, then divide into precision.


Can a Key Preserved Table have a Composite Primary Key, on Oracle db? I'm trying to create an updatable view.

I understand that the db must be able to identify one single row that I'm trying to update.

I believe I satisfied this requirement. There are no aggregates. Each row in the view has a 1-to-1 relationship with rows in the source table which I'm trying to update (MY_CFG_TABLE below).

But my view is not updatable. Why not? Does it have something to do with the composite key?

CFG_VALUE is the column I want to update.

I want a join-view, rather than subquery, so that i can see, in the view, rows in ALL_TAB_COLUMNS for which there's no corresponding row in MY_CFG_TABLE (for that, it will have to be changed to outer join, but for now just trying to make the update work with an inner.)

In the end, I may resort to an INSTEAD OF trigger, but for now I would like to make an updatable join-view-- or understand why I cannot.

Here's the update statement which fails:

update VBA_CFG_JOIN_V
SET CFG_VALUE = 'TRUE'
WHERE TABLE_NAME = 'APN_GR'
AND COLUMN_ID = 1
AND CFG_NAME = 'PART_ID';

Here's the error message:

  cannot modify a column which maps to a non key-preserved table

How can I make my source table, MY_CFG_TABLE, a Key Preserved Table?

View:

CREATE VIEW VBA_CFG_JOIN_V
AS SELECT F.TABLE_NAME, T.COLUMN_NAME, F.COLUMN_ID, F.CFG_NAME, F.CFG_VALUE
FROM ALL_TAB_COLUMNS T, MY_CFG_TABLE F
WHERE F.TABLE_NAME = T.TABLE_NAME AND F.COLUMN_ID = T.COLUMN_ID;

Source tables:

ALL_TAB_COLUMNS is a built-in Oracle view. (Note, I'm not trying to update ALL_TAB_COLUMNS).
https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2094.htm

MY_CFG_TABLE:

CREATE TABLE "GSM_OWNER"."MY_CFG_TABLE"
   (    "TABLE_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "COLUMN_ID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "CFG_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "CFG_VALUE" VARCHAR2(20 BYTE) NOT NULL ENABLE,
     CONSTRAINT "VBA_FIELDS_PK" PRIMARY KEY ("TABLE_NAME", "COLUMN_ID", "CFG_NAME")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "GSM_OWNER_TS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "GSM_OWNER_TS" ;

The problem is that we know that the join will produce only 1 row (and no more) in the view for every row in MY_CFG_TABLE but Oracle doesn't. As described in OraFAQ: Updatable Join Views (emphasis mine):

  Clearly the view cannot contain DISTINCT, GROUP BY, CONNECT BY, aggregate functions, UNION, INTERSECT, or MINUS clauses: simple joins are all that is allowed. There is one other restriction: the view must be key preserved. This means that the join must meet the following restrictions:

  The join criteria must be equals (=) joins.
  Tables other than the table that is to be updated must be joined on a primary or unique key.

  If these conditions are violated, then Oracle cannot guarantee that the view will return one row only for each row in the base table. If two or more rows in the secondary table mapped to each row in the base table, then the update would be ambiguous. An attempt to update a non-key-preserved view will result in an Oracle error.

I assume that either the joined table (which is actually a system view) does not have a UNIQUE or PRIMARY KEY constraint or one of the underlying tables (when the whole view is expanded) is not joined on its unique/primary key. This might be causing the errors when we try to update the view.

The problem may be more complicated as we don't know the exact definition of the underlying ALL_TAB_COLUMNS view. It might have some non-equality joins or non-inner joins or unions that make the new view non-updatable.

ALL_TAB_COLUMNS is a built-in view, that is not updatable:

select table_name, column_name, updatable, insertable, deletable from dba_updatable_columns where table_name = 'ALL_TAB_COLS';

TABLE_NAME   COLUMN_NAME                    UPD INS DEL
------------ ------------------------------ --- --- ---
ALL_TAB_COLS CHAR_LENGTH                    NO  NO  NO
ALL_TAB_COLS CHAR_USED                      NO  NO  NO
ALL_TAB_COLS V80_FMT_IMAGE                  NO  NO  NO
ALL_TAB_COLS DATA_UPGRADED                  NO  NO  NO
ALL_TAB_COLS HIDDEN_COLUMN                  NO  NO  NO
ALL_TAB_COLS VIRTUAL_COLUMN                 NO  NO  NO
ALL_TAB_COLS SEGMENT_COLUMN_ID              NO  NO  NO
ALL_TAB_COLS INTERNAL_COLUMN_ID             NO  NO  NO
ALL_TAB_COLS HISTOGRAM                      NO  NO  NO
ALL_TAB_COLS QUALIFIED_COL_NAME             NO  NO  NO
ALL_TAB_COLS USER_GENERATED                 NO  NO  NO
ALL_TAB_COLS DEFAULT_ON_NULL                NO  NO  NO
ALL_TAB_COLS IDENTITY_COLUMN                NO  NO  NO
ALL_TAB_COLS EVALUATION_EDITION             NO  NO  NO
ALL_TAB_COLS UNUSABLE_BEFORE                NO  NO  NO
ALL_TAB_COLS UNUSABLE_BEGINNING             NO  NO  NO
ALL_TAB_COLS OWNER                          NO  NO  NO
ALL_TAB_COLS TABLE_NAME                     NO  NO  NO
ALL_TAB_COLS COLUMN_NAME                    NO  NO  NO
ALL_TAB_COLS DATA_TYPE                      NO  NO  NO
ALL_TAB_COLS DATA_TYPE_MOD                  NO  NO  NO
ALL_TAB_COLS DATA_TYPE_OWNER                NO  NO  NO
ALL_TAB_COLS DATA_LENGTH                    NO  NO  NO
ALL_TAB_COLS DATA_PRECISION                 NO  NO  NO
ALL_TAB_COLS DATA_SCALE                     NO  NO  NO
ALL_TAB_COLS NULLABLE                       NO  NO  NO
ALL_TAB_COLS COLUMN_ID                      NO  NO  NO
ALL_TAB_COLS DEFAULT_LENGTH                 NO  NO  NO
ALL_TAB_COLS DATA_DEFAULT                   NO  NO  NO
ALL_TAB_COLS NUM_DISTINCT                   NO  NO  NO
ALL_TAB_COLS LOW_VALUE                      NO  NO  NO
ALL_TAB_COLS HIGH_VALUE                     NO  NO  NO
ALL_TAB_COLS DENSITY                        NO  NO  NO
ALL_TAB_COLS NUM_NULLS                      NO  NO  NO
ALL_TAB_COLS NUM_BUCKETS                    NO  NO  NO
ALL_TAB_COLS LAST_ANALYZED                  NO  NO  NO
ALL_TAB_COLS SAMPLE_SIZE                    NO  NO  NO
ALL_TAB_COLS CHARACTER_SET_NAME             NO  NO  NO
ALL_TAB_COLS CHAR_COL_DECL_LENGTH           NO  NO  NO
ALL_TAB_COLS GLOBAL_STATS                   NO  NO  NO
ALL_TAB_COLS USER_STATS                     NO  NO  NO
ALL_TAB_COLS AVG_COL_LEN                    NO  NO  NO

Also, there are more restrictions:

Notes on Updatable Views


CASE statement multiple conditions I've got a simple table below,

Select * from message_log where  Message = 'BUILD' order by 1;

+------------+-------------+
|  **Message |     Log**   |
+------------+-------------+
|    Build   |  Day Start  |
+------------+-------------+
|    Build   |  Day End    |
+------------+-------------+
|    Build   |  Dusk Start |
+------------+-------------+
|    Build   |  Dusk End   |
+------------+-------------+
|    Build   |  Night Start|
+------------+-------------+
|    Build   |  Night End  |
+------------+-------------+

If I for example write a CASE statement

SELECT DISTINCT CASE WHEN (Log = 'Day Start') THEN 'RUNNING' END AS A
from message_log where  Message = 'BUILD' order by 1;

It'll return a Null value and a 'RUNNING' value, can I write this in such a way that it doesn't include the null row?

Just add a WHERE condition.  And if you are only displaying one value, RUNNING, then there is no reason for a CASE. Otherwise you will want to evaluate each condition in the CASE including what should display in the event none of the conditions is met; a default value.

So here is the code to your original question:

SELECT 'RUNNING' AS A
from message_log
where  Message = 'BUILD'
    AND Log = 'Day Start'
order by 1;

And here is the code for a multi-condition CASE:

SELECT CASE
    WHEN (Log = 'Day Start') THEN 'RUNNING'
    WHEN (Log = 'Day End')   THEN 'NOT RUNNING'
    ELSE 'UNKNOWN' END AS A
from message_log
    where  Message = 'BUILD'
order by 1;

SELECT DISTINCT CASE WHEN (Log = 'Day Start') THEN 'RUNNING' END AS A
from message_log where  Message = 'BUILD'
AND
(
CASE
WHEN (Log = 'Day Start') THEN 'RUNNING'
END)='RUNNING'
order by 1;


Reset a sequence every year Every time a new row is added to the table below, I want the sequence (Import Permit No) to be increased by 1....20160001, 20160002, etc and reset to 20170001, 20170002, etc in a new year.

CREATE TABLE [dbo].[tblPermits](
[ImportPermitID] [int] IDENTITY(1,1) NOT NULL,
[ImportPermitNo] [nchar](20) NULL,
[ImporterName] [int] NULL,
[Province] [varchar](50) NULL,
[LodgementDate] [datetime] NULL,
[PortofEntry] [int] NOT NULL,
[EstDateofArrival] [datetime] NULL,
[ConsignmentInvoicePONo] [varchar](50) NULL,
[OtherImportConditions] [varchar](400) NULL,
[Supplier] [int] NOT NULL,
[SupplierCountry] [varchar](50) NULL,
[CountryofOrigion] [int] NOT NULL,
CONSTRAINT [PK_tblPermits] PRIMARY KEY CLUSTERED
(
[ImportPermitID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Currently what I have is a trigger as shown below.

ALTER TRIGGER [dbo].[trgPermitsInsertImportPermitNo]
ON [dbo].[tblPermits] FOR INSERT
AS
UPDATE dbo.tblPermits
SET ImportPermitNo = 'IP' + CAST(YEAR(i.LodgementDate) AS CHAR(4)) + RIGHT('000000' + CAST(i.ImportPermitID AS VARCHAR(6)), 6)
FROM dbo.tblPermits p
INNER JOIN INSERTED i ON p.ImportPermitID = i.ImportPermitID

But I couldn't get the sequence in the trigger above to reset to 0001 in a new year.

How do I modify the trigger to reset the sequence in a new year?

Rows can be deleted from this table. Other rows would not get renumbered in that case.
The number of new rows in a year is expected to be less than 5000, but in any case the number of prefix zeros could be expanded in the final design to accommodate a larger range.
Lodgement Date is a normal datetime field to be entered by the user.
All entries are made immediately when received from the client. Dates from previous years turning up late will not be an issue.
Duplicate ImportPermitNo values are not allowed.

In SQL Server 2012 or later, I would implement this using sequence objects.

For SQL Server 2008 R2, my replacement for that missing feature is Sequence Tables. In this case, there would be a key in the master sequence table for each year, for example:

CREATE TABLE dbo.SequenceTable
(
    sequence_name   nvarchar(20) NOT NULL,
    next_value      integer NOT NULL,

    CONSTRAINT [PK dbo.SequenceTable sequence_name]
    PRIMARY KEY CLUSTERED (sequence_name),
);
GO
INSERT dbo.SequenceTable
    (sequence_name, next_value)
VALUES
    (N'PermitIDs for 2016', 1),
    (N'PermitIDs for 2017', 1),
    (N'PermitIDs for 2018', 1);

The standard allocation stored procedure to robustly allocate a key or range of keys from a sequence is:

CREATE PROCEDURE dbo.Allocate_TSQL

    @SequenceName       nvarchar(20),   -- The name of the sequence to allocate keys from
    @RangeSize          integer = 1,    -- The number of keys to allocate
    @FirstAllocated     integer OUTPUT  -- The first key allocated (output)

AS
BEGIN

    SET XACT_ABORT ON;  -- Most errors will abort the batch
    SET NOCOUNT ON;     -- Supress 'x row(s) affected' messages
    SET ROWCOUNT 0;     -- Reset rowcount

    -- Validate the range size requested
    IF (@RangeSize IS NULL OR @RangeSize < 1)
    BEGIN
        RAISERROR('@RangeSize must be a positive integer (supplied value = %i)', 16, 1, @RangeSize);
        RETURN  999;
    END;

    -- Initialize the output parameter
    SET @FirstAllocated = NULL;

    -- Update the row associated with @SequenceName, returning the
    -- current value, and then incrementing it by @RangeSize
    UPDATE dbo.SequenceTable WITH (READCOMMITTEDLOCK)
    SET @FirstAllocated = next_value,
        next_value = next_value + @RangeSize
    WHERE sequence_name = @SequenceName;

    -- If @Allocated has a non-NULL value, we know we successfully updated a row
    RETURN CASE WHEN (@FirstAllocated IS NOT NULL) THEN 0 ELSE -999 END;
END;

Then, given a simplified version of the table in the question:

CREATE TABLE dbo.ImportPermits
(
    ImportPermitID integer IDENTITY (1, 1)
        CONSTRAINT [PK dbo.ImportPermits ImportPermitID]
        PRIMARY KEY CLUSTERED,
    ImportPermitNo nchar(12) NULL,
    LodgementDate datetime NULL
);

We can assign sequence numbers per year using the following trigger:

CREATE TRIGGER ImportPermitsImportPermitNo
ON dbo.ImportPermits
AFTER INSERT AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN;   -- Return immediately if no rows affected
    SET XACT_ABORT, NOCOUNT ON; -- Most errors abort the batch; no row count messages
    SET ROWCOUNT 0;             -- Ensure all rows are visible (local to trigger)

    DECLARE
        @rc integer,
        @FirstAllocated integer,
        @RowCount integer,
        @Years integer,
        @SeqName nvarchar(20);

    -- Count rows and distinct lodgement years in the insert set
    SELECT
        @RowCount = COUNT(*),
        @Years = COUNT(DISTINCT(YEAR(INS.LodgementDate))),
        @SeqName = N'PermitIDs for ' +
            CONVERT(nchar(4), MIN(YEAR(INS.LodgementDate)))
    FROM Inserted AS INS;

    -- Check for multiple lodgement years (not implemented)
    IF @Years > 1
    BEGIN
        RAISERROR('Multiple LodgementDate years are not supported.', 16, 1);
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        RETURN;
    END;

    -- Allocate the range of sequence numbers we will need
    EXECUTE @rc = dbo.Allocate_TSQL
        @SequenceName = @SeqName,
        @RangeSize = @RowCount,
        @FirstAllocated = @FirstAllocated OUTPUT;

    IF @rc <> 0
    BEGIN
        RAISERROR('Sequence allocation failed.', 16, 1);
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        RETURN;
    END;

    -- Assign ImportPermitNo values using the sequence numbers allocated
    WITH Sequenced AS
    (
        SELECT
            IP.ImportPermitNo,
            IP.LodgementDate,
            Seq =
                @FirstAllocated - 1 +
                ROW_NUMBER() OVER (
                    ORDER BY IP.LodgementDate ASC)
        FROM Inserted AS INS
        JOIN dbo.ImportPermits AS IP
            ON IP.ImportPermitID = INS.ImportPermitID
    )
    UPDATE Sequenced
    SET Sequenced.ImportPermitNo =
        N'IP' +
        CONVERT(nchar(4), YEAR(Sequenced.LodgementDate)) +
        RIGHT(N'000000' + CONVERT(nvarchar(11), Sequenced.Seq), 6);
END;

For brevity, the trigger is limited to inserting row(s) from a single year only, but it is not difficult to extend the logic.

Demo: db<>fiddle

Old Post but with SQLExpress - store the Year value as a single field AppData in SQL Table. Before each record created of YYYY####### , Check Date Current Year against your stored year - if the Current Year is different - update your AppData Year Field with Current Year, reset your sequence counter - then you do your inserts ..

EDIT

If you have SQL Server Express Edition - you do not have agent jobs.
If you are trying to do this in SQL only - that means no power shell scripts to 'set a field' .
So that being said - you must verify that you are in a new year with in a transaction in order to determine if you should reset the sequence number. If a year value (current for the moment) is stored in a field of a control table. That field can be checked on transaction against current date getDate() and the sequence can be reset on [tblControl.Year != GetDate() Year]


Nested AD group access not working when nested more than 2 levels deep? For the setup of the security of the databases we have created a number of nested Active Directory groups:

Group
Members
Group type

RGP-EA_PROD-RW
RGP-SparxEA-RW
Domain Local Security

RGP-SparxEA-RW
BGP-FunctionalAnalyst
Domain Local Security

BGP-FunctionalAnalyst
User1
Global Security

On my database I have added the group RGP-EA_PROD-RW as a user login and given it db_datareader and db_datawriter roles.
When User1 tries to login he gets an Access Denied error.
User1 is a member of BGP-FunctionalAnalyst who is a member of RGP-SparxEA-RW, who is a member of RGP-EA_PROD-RW. So User1 is ultimately a member of RGP-EA_PROD-RW.

The SQL Server Logs record two events when the login fails:
Date        3-5-2021 08:56:17 Log       SQL Server (Current - 15-4-2021 05:21:00)
Source      Logon
Message Error: 18456, Severity: 14, State: 38.

and
Date        3-5-2021 08:56:17 Log       SQL Server (Current - 15-4-2021 05:21:00)
Source      Logon
Message Login failed for user 'MYDOMAIN\User1'. Reason: Failed to open the explicitly specified database 'AE_PROD'. [CLIENT: 100.74.0.4]

The database is online, and the AD group has been setup to have access to it. It works just fine with the other groups, just not with the group nested at the third level.
I did a little bit of experimenting and found that when setting up logins:

Login User1 directly works
Login BGP-FunctionalAnalyst works
Login RGP-SparxEA-RW works
Login RGP-EA_PROD-RW does not work

I'm not sure what is causing this problem:

Is there a maximum level of nesting supported by SQL Server?
Is there a certain property I should look for in AD? (both Domain Local groups look similar to me)
Something else?

This is a completely new Active Directory with only a handful of users and groups.
We are trying to apply the AGDLP guideline in a single AD domain.

RGxxx groups are Resource Groups, defined as Local Security Groups. They are used to assign access rights to resources, and they only have other groups as members, no users.
BGxxx groups are Business Groups defined as Global Security Groups. They have only users as members and are not used to assign access rights.

Sorry I don't get it. You write:

On my database I have added the group RGP-EA_PROD-RW as a user login and given it db_datareader and db_datawriter roles

Okay. So that means RGP-SparxEA-RW ought to be able to login and do stuff.
User1 is not a member of RGP-EA_PROD-RW so why do you expect for User1 to be able to login?

User1 is a member of BGP-FunctionalAnalyst who is a member of RGP-SparxEA-RW, who is a member of RGP-EA_PROD-RW. So yeah, User1 is ultimately a member of RGP-EA_PROD-RW.

MYDOMAIN\User1 suggests when you login you the username needs to be prefixed with a real domain.  There is a weirdness that the groups that work are all Domain Local Security Groups.  Maybe the Global Security Group only exists in Azure - not on the local domain controllers. Next step is to query group membership with VBscript or Powershell and confirm whether the group membership is what you think it is.
Standard practice is to make local groups inherit from global groups. In your case it's the other way around.


EXISTS clause in SQL I have following requirement: main_set table has all the ids, user_input table has all user entered ids.

I want result of ids that exist in user_input table. If nothing in user_input table then I want all the ids from main_input table.

Here's what I have so far:

create table main_set as
select 1 id from dual
union all
select 2 from dual
union all
select 3 from dual

create table user_input as
select 1 id from dual
union all
select 2 from dual

select *
  from main_set ms
  where exists (select null
                        from user_input ui
                       where ui.id = ms.id)

select id from user_input
union all
select id from main_set where not exists(select * from user_input);

--edit based on your comment

select *
from main_set
where id in(select id from user_input) or not exists(select * from user_input)

or, seeing as you mention performance, perhaps you should try a join as an alternative:

select main_set.*
from main_set left outer join user_input on(user_input.id=main_set.id)
where user_input.id is not null or not exists(select * from user_input)

Using a case statement should do the job instead of an exists clause.


Installing SQL Server 2014 Enterprise in Windows 7 Ultimate 64 Bit Incompatibility Error This error occurs when I run the setup.exe of SQL 2014.

It tried changing the compatibility mode of the setup from Windows 95 through Windows Vista (Service Pack 2) and run the setup as Admin but same error occurs.

I also installed .Net Framework 4.0 and 4.5. Under the Programs And Features > "Turn Windows features On and Off" > I enabled the "Microsoft .NET Framework 3.5.1" but I found a guide that there should be .Net Framework 2.0 and 3.0 under the Microsoft .NET folder but those 2 versions are missing in my pc.

https://answers.microsoft.com/en-us/newmsn/forum/newmsn_install-newmsn_updateapp/i-cant-install-sql-server-2014-on-windows-10/6d311401-796f-4b3f-b5be-d310cfa6454c?auth=1

When I tried installing those two versions I get the following error
"You must use Turn Windows features On and Off in the Control Panel to install or configure Microsoft .NET Framework 2.0 SP2"

same as the SP1 version of .Net Framework 3.0

According to Microsoft you have to pay for these versions of their operating system if you want to run their "Enterprise" SQL Server.

Windows Server 2016 Datacenter
Windows Server 2016 Standard
Windows Server 2012 R2 Datacenter 64-bit
Windows Server 2012 R2 Standard 64-bit
Windows Server 2012 R2 Essentials 64-bit
Windows Server 2012 R2 Foundation 64-bit
Windows Server 2012 Datacenter 64-bit
Windows Server 2012 Standard 64-bit
Windows Server 2012 Essentials 64-bit
Windows Server 2012 Foundation 64-bit
Windows Server 2008 R2 SP1 Datacenter 64-bit
Windows Server 2008 R2 SP1 Enterprise 64-bit
Windows Server 2008 R2 SP1 Standard 64-bit
Windows Server 2008 R2 SP1 Web 64-bit
Windows Server 2008 SP2 Datacenter 64-bit
Windows Server 2008 SP2 Enterprise 64-bit
Windows Server 2008 SP2 Standard 64-bit
Windows Server 2008 SP2 Web 64-bit

It also seems to run with the same versions in 32-bit, it just stores them in a separate column and amusing Microsoft can't fill an HTML table properly (nor can they generate Markdown-friendly urls.)
Interestingly, "SQL Server Standard"  runs on

Windows 7 SP1 Ultimate 64-bit
Windows 7 SP1 Enterprise 64-bit
Windows 7 SP1 Professional 64-bit

So the question is
What feature does Enterprise do that Standard doesn't that requires Windows Server 2008 (released in February 27, 2008) instead of the newer Windows 7 Ultimate (released in October 22, 2009)?
I assume nothing, except licensing and marketing wank. It seems you're at impasse, so I suggest upgrading your operating system, and upgrading your database server.
It may also be worth nothing that other people have managed to do this.
Sean Gallardy, whose profile claims insider information,

commented on DBA.se

it may physically install, but it's not supported at all. Kind of like those trying to use wine, etc, to get it to run on Linux ;) I'm also with you, use developer edition if absolutely needed for something like this (if dev applies to the workload). - Sean Gallardy

Of course, details omitted we have no idea why it wasn't supported or what the "serious limits" are (from the context of the conversation).. Betting it's still a hold up for more cash.
Perhaps you can find a new version of Enterprise without this software-imposed restriction.