SQL Statement Layer Filter Queries Limit Access Goal: Want to create a so called "SQL Statement Layer" which checks/filters all incoming queries. If incoming query is invalid, should be denied.

Use-case: Limit database access and avoid unrevertable queries. No duplicate code in multiple applications about the same "triggers or procedures" (centralized).

Research so far:

GRANTS/Privileges
limit to Stored Procedures

Description: I am standing before a new project and we want to limit and minimize the access to the database. For example: The developer can create a DROP query inside the Application layer but the database will deny this query because DROPqueries aren't allowed.

I worked some years ago with Laravel and did most of the "procedure and trigger" work inside the Application Layer like the Controllers instead of using a Mutator or Accessor. Now we want to cut it down to the lowest possible level to avoid duplicate code like Mutators and Accessors in different Applications.

Question: Are Grants and stored procedure the only way? If not, which ones?

I am not quite sure if using the built-in privilege system in combination with encapsulation of some or all of the SQL code in stored procedures is the only way, but it certainly is the sane way of going about restricting access to the database. You are free to introduce your own system, of course, but from what I have learnt this far, there is no need to reinvent the wheel.

Take your time deciding what different levels of access you may need, and create database roles accordingly, granting different kinds of privileges to different objects according to the role definitions. Create the user accounts and assign them to the roles. Have your application provide a dialogue box requesting login credentials and use those to invoke whatever SQL instruction needs to be executed, be it a stored procedure call or an ad-hoc query. The instruction will be executed – or not – based on the privileges decided at the server level.

Again, this is the usual way of managing database access. Unusual scenarios may well call for unusual approaches, but if the reason for not using the built-in security is, for instance, simply because it makes the developer's life easier, my advice would be to reconsider.


Postgres database zero down time production deployment I need some idea how I can deploy a financial application in a production environment using PostgreSQL with zero down time.

I have two servers in Master-Slave design. Currently I am giving downtime while deploying applications and executing database script.

During deployment, I don't want to turn off my database because of transactions. If I turn off one node for changes and second for real transactions, now both database nodes have some changes and there will be sync issue. I am looking for a solution where I can deploy DB changes and real time transactions too.

For zero downtime, do we need any configuration or any third party software or PostgreSQL utility that will handle this scenario, so that in production realtime environment we don't lose any transactions, and I also can update database script simultaneously?

"Zero downtime" means "no loss of transactions" and "no interruption of services" both. I understand it's challenging, and also possible like Google never shows any downtime. But still it's not clear how to do it.

Answer originally left as a question comment.

For zero downtime during tables refactoring, we create new tables along with old ones, and switch in small steps.

  From Developing Low-Maintenance Databases by Alex Kuznetsov on Simple Talk.

  Instead of making all the changes at once, we make multiple incremental changes, as follows:

  Make sure that all the modules accessing the table are well covered with automated tests.
  Create new tables. Alter all procedures that modify the old table, so that they modify both old and new tables.
  Migrate existing data into new structure. Do it in smallish batches, so that it does not seriously impact the overall performance on the server.
  Verify that the migration of data succeeded.
  Redirect some of the selecting procedures from the old table to the new ones. Use automated tests to make sure that the changed modules are still correct. Make sure their performance is acceptable. Deploy the altered procedures.
  Repeat the previous step until all the reports use the new tables.
  Change the procedures that modify the tickets, so that they only access the new tables.
  Archive the old table and remove it from the system.

  It is true that this approach has many steps and is not very simple; yet we love it – it is far better than its alternatives. Each step incurs very little risk, and if needed, we can roll it back in a split second. We can set the migration aside and switch to another task, or enjoy our weekend, at any time – the system keeps working in any of these intermediate states for as much as needed. Our users do not experience any disruptions.

See the original article for full details.


Persistent inserts in a UDF even if the function aborts I have a rather complicated UDF (moves around a bunch of tables as well as creates a bunch of new ones) where multiple aborts may occur. Before every operation I would like to record the time when an operation takes place and the query itself. The UDF looks like the following then:

log function_start

log sql1
execute sql1

log sql2
execute sql2

...

log sqlN
execute sqlN

log function_end

Every log statement means inserting a new record into the following table:

CREATE TABLE backup_logs
(
  id serial NOT NULL,
  t timestamp with time zone default now(),
  sql text,
  CONSTRAINT backup_logs_pkey PRIMARY KEY (id)
)

In case of abort I'd like sql1, sql2, ... sqlN to be rollbacked, but the inserto into backup_logs to persist. Question: how can I achieve that?

When an exception occurs everything is rolled back. You want to execute selected code "outside" the current transaction context, which is commonly referred to as autonomous transactions. This is not currently implemented (as of pg 9.4). There is an item in the Postgres TODO wiki, but it's a tricky matter, don't hold your breath.

For now you can use the additional module dblink. It provides functions to connect to another DB in a separate connection (and thus also separate transaction) to execute SQL there. What's done is done and cannot be rolled back. By connecting to the same DB another time you effectively achieve autonomous transactions (with some extra overhead). It's decently fast.

Proof of concept

Preparations

You need to install dblink once per database (where you run the function):

CREATE EXTENSION dblink;

For convenience I encapsulate the connection info in a FOREIGN SERVER plus USER MAPPING:

CREATE SERVER myserver FOREIGN DATA WRAPPER dblink_fdw
OPTIONS (hostaddr '127.0.0.1', dbname 'test');

CREATE USER MAPPING FOR role_source SERVER myserver
OPTIONS (user 'role_target', password 'secret');

There are various options for the connection string. The example is for a user named role_source to connect as role_target in a DB test on localhost 127.0.0.1. Could be the same role name for source and target. I use a password here (simple example) but I much prefer password-less access for the purpose, so we don't have to save a PW (and it does not end up in backups etc.):

Run batch file with psql command without password

However, per documentation:

  Only superusers may use dblink_connect to create
  non-password-authenticated connections. If non-superusers need this
  capability, use dblink_connect_u instead.

Or create a SECURITY DEFINER function to encapsulate relevant parts. See below.

Main function

CREATE OR REPLACE FUNCTION f_work()
  RETURNS void AS
$func$
DECLARE
   sql1 text := 'SELECT 1';  -- dummy code
   sql2 text := 'SELECT 2';

BEGIN
   PERFORM dblink_connect('myserver');  -- name of foreign server
   PERFORM dblink_exec($f$INSERT INTO backup_logs(sql)
                          VALUES ('f_work() start')$f$);   -- log start

   PERFORM dblink_exec('INSERT INTO backup_logs(sql)
                        SELECT ' || quote_literal(sql1));  -- log sql1
   EXECUTE sql1;

   -- RAISE EXCEPTION 'foo'; -- unquote to test error case

   PERFORM dblink_exec('INSERT INTO backup_logs(sql)
                        SELECT ' || quote_literal(sql2));  -- log sql2
   EXECUTE sql2;

   PERFORM dblink_exec($f$INSERT INTO backup_logs(sql)
                          VALUES ('f_work() end')$f$);     -- log end
   PERFORM dblink_disconnect();
END
$func$  LANGUAGE plpgsql VOLATILE;

Be sure to have a USER MAPPING for the role you are running this function with, or make it a SECURITY DEFINER function owned by the role you want to work with:

Permission for sequence in another schema
Creating UUID extension as the non super privileged user


How to get distinct day number with window function (Postgres 9.6) I am trying to get the distinct day number with a set of data that contains different dates that are not always consecutive. For example, if I have this data:

      date | id
2019-01-01 | 1
2019-01-02 | 2
2019-01-02 | 3
2019-01-04 | 4
2019-01-07 | 5
2019-01-07 | 6

I need to get the day number, where the day_num is the next unique data from the fist, and NOT the count of days SINCE the first .. e.g.

      date | id | day_num
2019-01-01 | 1  |   1
2019-01-02 | 2  |   2
2019-01-02 | 3  |   2
2019-01-04 | 4  |   3
2019-01-07 | 5  |   4
2019-01-07 | 6  |   4

Use case:
1) I need to know how many distinctive dates someone logged time .. so above I could see that the time was logged over 4 days (last day_num value).
2) I need to see which work day each separate date group is .. for example, above I can see that work done on 2019-01-04 was the 3rd time which this person worked.

for brevity, columns for user_id, hours etc, are not shown

In trying to figure this out, I got as far as

  COUNT(1) OVER (
     ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING
       AND CURRENT ROW
  ) day_num,

.. which of course is not grouping by the date. This is PG 9.6 so I dont have frame_exclusion available .. not sure if that would even have helped.

dense_rank() seems to be a perfect fit.

SELECT date,
       id,
       dense_rank() OVER (ORDER BY date) day_num
       FROM elbat;

db<>fiddle


Multi Insert vs insert into Select I have about 2000 rows to be inserted at a time. Which is Optimized method Multiinsert or insert into .. SEelec colum1, column2 from table .
In case if Multi-insert, I will SELECT generate multiple statments in my application.
So in short, which is Optimized?
INSERT INTO table_name(column_list)
SELECT
   select_list
FROM
   another_table
WHERE
   condition;

OR
INSERT INTO table(c1,c2,...)
VALUES
   (v11,v12,...),
   (v21,v22,...),
    ...
   (vnn,vn2,...);

For only 2,000 records, one INSERT will be most performant. Inserting batches of records is generally the best way to do it (with batch sizes up to as much as roughly 100,000 typically). It shouldn't matter if you use VALUE syntax or SELECT query syntax, as long as they're in the same batch.
This article does a good job going in depth on additional INSERT optimization techniques.


Why is this rollback needed when using sp_addextendedproperty in a stored procedure? In enter link description here I showed, how I document databases.

To insert the extended properties, initially I uses plan sequences of sp_addextendedproperty calls.

But lately I wanted to catch the errors about not existing objects when I run the script on an older version of the database.

I found the following working code by trial and error

begin try
drop procedure dbo.BK_add_Tableproperty
end try begin catch end catch

go
create procedure dbo.BK_add_Tableproperty (
    @table_name sysname,
    @property_name nvarchar(max),
    @Property_value sysname
)
as
    declare @error integer;
begin try
    EXEC sys.sp_addextendedproperty @name= @property_name,
    @value = @Property_value,
    @level0type=N'SCHEMA',
    @level0name=N'dbo',
    @level1type=N'TABLE',
    @level1name=@table_name
end try begin catch
    SET @error = @@ERROR;
    if @error = 15135
        Print 'Table missing no extended properties added: ' + @table_name
    else if @error = 15233
        Print 'Table has already property: ' + @table_name + ' - ' + @property_name
    else
        Print 'unexpected error ' + str(@error)
    ROLLBACK;
end catch

go

What me confuses, is the fact, that I have to add a ROLLBACK to the catch path. At msdn I found no hint, that sp_addextendedproperty uses transactions.  Can anyone explain this?

Answer to gbn:

But return  exits unconditionally the procedure. I see only 1 commit in each path:

BEGIN TRANSACTION

begin
    EXEC %%ExtendedPropertySet().AddValue(Name = @name, Value = @value, Level0type = @level0type, Level0name = @level0name, Level1type = @level1type, Level1name = @level1name, Level2type = @level2type, Level2name = @level2name)
    IF @@error <> 0
    begin
        COMMIT TRANSACTION
        return (1)
    end
end

COMMIT TRANSACTION
return (0)

Reported as bug to Microsoft connect:

https://connect.microsoft.com/SQLServer/feedback/details/658556/sp-addextendedproperty-leaves-on-open-transaction-when-an-error-occured

Improved version (better error handling and fixed parameter type.
(the original problem is unaffected, but this handels NLS in erropr messages)

create procedure dbo.BK_add_Columnproperty (
    @table_name sysname,
    @Column_name sysname,
    @property_name nvarchar(max),
    @Property_value sql_variant
)
as
begin try
    --SET XACT_ABORT ON
    EXEC sys.sp_addextendedproperty @name= @property_name,
    @value = @Property_value,
    @level0type=N'SCHEMA',
    @level0name=N'dbo',
    @level1type=N'TABLE',
    @level1name=@table_name,
    @level2type=N'COLUMN',
    @level2name=@Column_name
end try begin catch
    -- SELECT XACT_STATE()    -- returns 1 or -1 when SET XACT_ABORT ON
    if ERROR_NUMBER() = 15135 -- object invalid
        print '      ' + str(ERROR_NUMBER()) + ' -- ' + ERROR_MESSAGE()
    else
        print '##### ' + str(ERROR_NUMBER()) + ' -- ' + ERROR_MESSAGE()
    ROLLBACK;
end catch

go

This line..

SELECT OBJECT_DEFINITION(OBJECT_ID('sp_addextendedproperty'))

...shows that sp_addextendedproperty has 2 x COMMIT. This won't be a problem if the transaction can be COMMITed (i.e. isn't "doomed") but will leave a transaction open otherwise.

This could well be a bug to be reported on MS Connect

A catch block like this will show if the transaction is doomed

begin catch

SELECT XACT_STATE() -- -1 = doomed, 0 = none, 1 = commit or rollback

    SET @error = ERROR_NUMBER();

    if @error = 15135
        Print 'Table missing no extended properties added: ' + @table_name;
    else if @error = 15233
        Print 'Table has already property: ' + @table_name + ' - ' + @property_name;
    else
        Print 'unexpected error ' + str(@error);

end catch

I've not seen this (an open txn) at work (still SQL Server 2005 SP3) and got the details from a local SQL Server 2008 on a VM. Could be version related.

Edit: just seen the Q update

Using SET XACT_ABORT ON will force a rollback anyway


Postgresql: Sortable index on array values Suppose the following table:

CREATE TABLE foo (id serial, category int, bar int[]);
INSERT INTO foo VALUES (1, 1, '{4,10,20}');
INSERT INTO foo VALUES (2, 1, '{1,8,9}');

We want to query sorted individual values from bar column. This can be achieved with this query:

SELECT unnest(bar) AS u, id FROM foo ORDER BY u;
 u  | id
----+----
  1 |  2
  4 |  1
  8 |  2
  9 |  2
 10 |  1
 20 |  1

How we can build an index for this query?

UPDATE:

Version of postgres is not important, lets say we want to implement with latest version (9.5)
bar column is variable-length, different rows may have different number of elements, (but no empty array).
Elements are not null.
We want an index to support efficient queries with LIMIT (so not retrieving all rows in queries, see blow)
This is a simplified version of an actual (in design) use-case.

UPDATE:
Example with LIMIT clause

SELECT unnest(bar) AS u, id FROM foo WHERE category = 1 ORDER BY u LIMIT 3;
 u  | id
----+----
  1 |  2
  4 |  1
  8 |  2

If your use case is to retrieve all array elements of all rows, an index is not going to help with that. Postgres indexes can only hold a single index entry for a single table row. And a GIN index would not support the sort.

The best option I see would be a MATERIALIZED VIEW - which needs a refresh after any write operations on the underlying table that might influence the result. So probably only useful for read-only (or mostly) tables.

CREATE MATERIALIZED VIEW foo_elements AS
SELECT unnest(bar) AS u, id
FROM   foo
ORDER  BY u;


MS Sql Server Management Studio gives error on start When i try to connect my sa account on sql server management studio it gives me this error:

*ps: the error message is turkish but you will see english version in 3rd image

Then i clicked on "Get Help For" thing as its shown above. It showed me this:

So i clicked to "Yes" button. Then it opened web page below:

*ps: error message is the same in first image but its english.

After that, i tried to start sql server process in services windows regarding 3rd image. But it gave me this error message:

It means "Windows couldn't start SQL SERVER service on local computer. For more information see event logs. If this is not a Microsoft service, call provider and give this special error code: 3417"

I wanted to look system event logs from C:\Windows\System32\winevt\Logs directory but there was 84 different .evtx files, I couldn't know what to do. How can i fix this?

*ps: I'm also not little bit confused about this problem's scope. I also thought to post this on superuser and/or stackoverflow but this place looked more convenient to me. So, if i'm mistaken please warn me.

edit:

I opened Event Log application in Control Panel\Administrative Tools according to Jon Siegel's suggestions and filtered all event logs from MSSQLSERVER source. There are 2 different error event.Below you can see event properties and link for event log online help documents.

Error 1:

Event Log Online Help Document for Error 1: Link

Error 2:

Event Log Online Help Document for Error 2: Link 2

Error code 3417 means that you have problems with your master database. More detailed information on this error can be found here: (http://msdn.microsoft.com/en-us/library/aa337262%28v=sql.105%29.aspx). The list of SQL server errors can be found in the MSDN by following address: http://msdn.microsoft.com/en-us/library/cc645601(v=sql.105).aspx. The solution is to recover the master database from the last backup. After this the SQL server will start and you will be able to connect.


Getting distinct list of records based on the MAX of a column I have a status table (tblTestActionStatus) which has three columns

ID_TestAction:  references some test action
ID_Status:   a look up table reference for the variety of possible statuses for the action
StatusDateTime:  a datetime field which logs the exact time a status update was made for the test action.

So while a test action is happening, periodic status updates are made.

I am interested to know the current status which would be the last status update of a test action as of the moment I run the query.
For example, test action # 100 has gotten 4 status changes so far.  The first was 1, then 2, then 3, and most recently 4.  And test action # 101 has gotten 3 status updates so far.

So I would like to write a query that returns for me all the columns in the table but for only the most recent StatusDateTimes.

I attached a pic which shows the table contents and the rows I would like to see coming back from the query highlighted.

This answer was originally posted by the OP as an edit to the question; it is now reposted as a Community Wiki answer.

I was able to find an answer with some more searching in the archive.

https://stackoverflow.com/questions/1049702/create-a-sql-query-to-retrieve-most-recent-records

So the SQL I created from that post which works in my case is as follows...

SELECT tblTestActionStatus.ID_TestAction, StatusDateTime, ID_Status
FROM tblTestActionStatus
INNER JOIN
    (
        Select MAX(StatusDateTime) as LatestDate, ID_TestAction
        FROM tblTestActionStatus
        Group By ID_TestAction
    ) SubMax
on tblTestActionStatus.StatusDateTime = SubMax.LatestDate
and tblTestActionStatus.ID_TestAction = SubMax.ID_TestAction


Poor performance by LINQ to SQL generated query execution plan Can you please check the attached execution plan from my SQL Server 2016 and suggest me where to improve.
The query is generated from the .Net application using LINQ to SQL.
I can see large sort operation in the plan and i have indexes for SORT columns.Also i see OR in where conditions.
Can anyone please direct me where the issue is?I am still learning on how to debug from the execution plan.
Execution Plan
Even after hard-coding the linq parameters it takes 23 seconds to run.
Since it is generating from .net application,i have less control over the query and i tried adding different indexes(some covering indexes to avoid key lookups) etc.,but of no help till now.
Appreciate any kind of help in providing more insight into the plan on problem areas.

Can you please check the attached execution plan from my SQL Server 2016 and suggest me where to improve.

I don't see any indication that there is likely to be useful optimizations that involve just indexes and statistics.  The entire query isn't included in the plan, but it's a monstrous, frightful thing, and it needs to be rewritten.

The main problem with the query is that it's a TOP 100 query, but is not able to cheaply identify the 100 rows, and then perform the rest of the JOINs.  Instead it creates a 7M row result, sorts it and takes the first 100 rows.

This really needs to be addressed by whoever wrote the query (here the application developer).  You can perhaps help by helping them articulate what the intent of the query is, and suggesting a better way.


Missing Edit Privileges Option In PhpMyAdmin The Action column of the PhpMyAdmin is not displaying the Edit Privileges. I did not get the solution for this problem. I even tried to upgrade and degrade the PhpMyAdmin version, but nothing worked. I have attached a screenshot of PhpMyAdmin for verification. Please verify it and give me the solution to this problem.
Operating System: Windows 10,
Php Version : 7.2.34,
Message : You do not the privileges to administrate the users!

It appears as if the user you are logged in as does not have the privileges needed to edit access. The edit links are only shown if your user is able to modify permissions.


Improving this table Using Microsoft SQL Server Management Studio 2012

I need some help in improving my db structure.

Currently I have a table that looks kinda like this:

Id       Table_Name         Table_Id        Next_Id
1        Login              1               2
2        Logout             10              3
3        Close              2               4
4        Open               3               null
5        Login              4               null
6        Login              3               7
7        Logout             4               8
8        Close              9               null
9        Login              2               10
10       Logout             3               null

Each row has a unique Id, a not null Table_Name, not null Table_Id and a Next_Id.

The idea behind this table is, that the user can add a "sequence" for my desktopapplication. If we look at Id 1 for example, we will see the Table_Name Login which contains the value Login and the Table_Id 1. My programm will now read the row in the Table Login that contains the Id 1
(Login will contain logininformation and some other stuff) and will afterwards look into the colum Next_Id to check if this is the end of the sequence. Since Next_Id contains the number 2 my programm will now read the row 2 of this table and will continue to do so until it hits the Next_Id value null

Is there any way I can improve my solution attempt in any way? If there are still questions regarding what I tried to do with this table then go ahead and ask :)

If I'm reading you right, the function of Next_Id is to group the entries into ordered sets.  The way you've done it is the way an old fashioned network database would have done it.  Each next_id "points to" another entry.

The relational model groups items into (unordered) sets by putting a common value in a foreign key.  I don't have a model for your data, so I'll take a blind stab at a design.  We use the Id of the first entry in the set to identify the group membership.  The first entry doesn't have a null, instead, it points to itself.

Now, the table looks like this:

Id       Table_Name         Table_Id        Set_Id
1        Login              1               1
2        Logout             10              1
3        Close              2               1
4        Open               3               1
5        Login              4               5
6        Login              3               6
7        Logout             4               6
8        Close              9               6
9        Login              2               9
10       Logout             3               9

(I fixed the presumed bug in row 9).

This offers one advantage at storage time:  The set membership is known when a new value is inserted.  The next_id is not known, because the next event hasn't happened yet.  This means you only have to store it once, instead of storing it once and then updating it when the Next _id changes from NULL to something else.

It also offers an advantage at retrieval time.  Once you figured out the Set_id you are looking for, you can get all the members with a single trip to the database,  using something like this:

select *
from mytable
where set_id = somevalue
order by Id

I have guessed at the correct order for the retrieved set, based on the idea that later events will have a higher id.  But I don't know that that's true in your case.

If this select runs too slow, you could consider adding an index on Set_id.

A better solution would require knowing more about your data and your process.  Hope this helps.


Installation SQL Server 2016 error I just uninstall SQL Server 2012 (but Microsoft SQL Server 2012 didn't uninstall completely), now I want to install SQL Server 2016 but it causes this error:

  vs shell installation has failed with exit code 1638.

I google it but can't find a true answer.

Exit code 1638 is Another version of this product is already installed. Installation of this version cannot continue. To configure or remove the existing version of this product, use Add/Remove Programs on the Control Panel


PostgreSQL DISTINCT ON forces a specific order Why DISTINCT ON is sorting the rows? E.g. the query
SELECT DISTINCT ON (a) a
FROM (
  VALUES (2), (3), (1)
) x(a);

will have the result
1
2
3

while without DISTINCT ON
SELECT a
FROM (
  VALUES (2), (3), (1)
) x(a);

I'll get the correct order
2
3
1

How can I preserve the natural order of defined values?  By "natural order" I mean "the order in which the values were defined". I understand that DBMS may change the order if I don't specify it explicitly with an ORDER BY, but I was not expecting that DISTINCT ON my interfere in this way.

Why DISTINCT ON is sorting the rows?

Distinct removes "duplicate" rows from the result set.  That's a whole lot easier to do if the items being compared arrive neatly sorted into order.
Check the Explain Plan for this query - it will probably contain extra "Sort" and "Unique" steps.  Guess what those are doing.
"select distinct" is always a Red Flag for me.
It is almost always used poorly, a band-aid to get around a bigger problem and introduces problems of its own.  Remember, the performance of ...
select distinct a, b, c
. . .

... is roughly comparable to that of ...
select a, b, c
. . .
group by a, b, c
order by a, b, c

Ask yourself why [you think] you need to use "distinct".


user can select all structure from pg_catalog.*, but can't make a dump -s Given: Postgres RDS 9.3, pg_dump 9.3, RDS user (not SU)

I have:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation rel
pg_dump: [archiver (db)] query was: LOCK TABLE public.rel IN ACCESS SHARE MODE

When I run :

-bash-4.2$ pg_dump -s --section=pre-data -h hostname -d db -U not_su

(tried both --schema-only and --section=pre-data in desperate hope they would differently not require a lock, but no success)

Yet:

When I login to db with same user, I can \dt+ rel and see the structure, so I can see definition from pg_attributes and pg_class for this rel and all related relations...

Is there any way to overcome this limitation?.. Or I have to build DDL based on pg_catalog?.. (seems not reasonable that my user can select all structure, but can't make a structure only dump)

Update:

I must be very unclear In my question. Here is example:

As superuser:

t=# create table so15(i bigserial primary key);
CREATE TABLE
t=# create user so15;
CREATE ROLE

As user without permissions:

postgres@vao-VirtualBox:~$ psql -U so15 -h localhost -d t
psql (9.6.1)

t=> \d+ so15
                                            Table "public.so15"
 Column |  Type  |                    Modifiers                     | Storage | Stats target | Description
--------+--------+--------------------------------------------------+---------+--------------+-------------
 i      | bigint | not null default nextval('so15_i_seq'::regclass) | plain   |              |
Indexes:
    "so15_pkey" PRIMARY KEY, btree (i)

So I can see the structure of so15 relation without read permissions, now as same user I pg_dump -s:

postgres@vao-VirtualBox:~$ pg_dump -s -t so15 -U so15  -h localhost -d t
pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation so15
pg_dump: [archiver (db)] query was: LOCK TABLE public.so15 IN ACCESS SHARE MODE

DUMP pg_catalog

Here's simple script to dump objects' structures in pg_catalog.

pg_dump -p 5432 -U postgres -d postgres -s --table=pg_catalog.* > catalog.sql

And the result looks like that:

CREATE TABLE pg_authid (
    rolname name NOT NULL,
    rolsuper boolean NOT NULL,
    rolinherit boolean NOT NULL,
    rolcreaterole boolean NOT NULL,
    rolcreatedb boolean NOT NULL,
    rolcanlogin boolean NOT NULL,
    rolreplication boolean NOT NULL,
    rolbypassrls boolean NOT NULL,
    rolconnlimit integer NOT NULL,
    rolpassword text,
    rolvaliduntil timestamp with time zone
);
ALTER TABLE ONLY pg_authid REPLICA IDENTITY NOTHING;
ALTER TABLE pg_authid OWNER TO postgres;

UPDATE from Vao Tsun:

From pg_dump doc:

  pg_dump internally executes SELECT statements. If you have problems
  running pg_dump, make sure you are able to select information from the
  database using, for example, psql

From psql doc

  By default, only user-created objects are shown; supply a pattern or
  the S modifier to include system objects.

  Note: If \d is used without a pattern argument, it is equivalent to
  \dtvsE which will show a list of all visible tables, views, sequences
  and foreign tables

In terms of your case  "user without permissions". I guess the so15 table belongs to public schema. That's why so15 user can see table structure (\d+ so15).

For example: if you login admin user and create new table on new schema, then login so15 user to test ( remember to set search_path = new_schema before doing \d+). You cannot see this new table because of permission.

Besides, you cannot dump so15 table due to pg_dump will execute SELECT and it needs permission on this table.

Solution: to dump so15 table you should grant privilege to so15 user.


Is it possible to query uncommitted data of a different session? For interactive debugging purposes I'm wondering whether it's possible to query the data another session has currently put into the database but not yet committed.

Say I have an my application open in a debugger and when I'm stopped in a certain breakpoint I would like to check interactively what the session of the app "is seeing" via simple queries from SQLDeveloper (or sqlplus).

Is this possible with Oracle XE? (As it is for debugging, there's no DBA or security constraints.)

no, "Oracle Database doesn't use dirty reads, nor does it even allow them"


Immutable field/cell in Postgres Is there a way to create an immutable field/cell in a Postgres record? AKA, only allow the field to be written once, perhaps just on insert?
Use case: I have a "uuid" field for a record, and it should never ever be changed (same with the PK as well).

There are several techniques for that:

Using column permissions, grant UPDATE only on the other columns.

Create an UPDATE trigger that throws an exception when OLD.id <> NEW.id.

Note that all these measures can never deny the table owner access, since the owner can always change permissionsand drop triggers.


How do I Loop through a table and update a field in SQL I need to update a column with an sequential number starting with 1 based on the accountId in the table. How do I do this?

so OrderID is NULL to start.
so for each row of AccountID I need to update the OrderID to start from 1 and sequentially update so my data comes out like this.
The table is called Renewals

RowID  AccountID  OrderID
1      A          1
2      A          2
4      A          3
5      B          1
6      B          2
7      C          1

Using a common table expression with row_number() to partition by AccountId and order by [RowId]:

;with cte as (
  select *
    ,  NewOrderId = row_number() over (
          partition by AccountId
          order by [RowId]
    )
  from Renewals
)
update cte
  set OrderId = NewOrderId;

Without using the common table expression:

update r
  set OrderId = NewOrderId
  from (
    select *
      ,  NewOrderId = row_number() over (
            partition by AccountId
            order by [RowId]
      )
    from Renewals
      ) as r

test setup: http://rextester.com/FSUD49402

create table Renewals (
    [RowId]   int     not null
  , AccountId char(1) not null
  , OrderId   int         null
);
insert into Renewals (RowId, AccountId) values
  (1,'A'), (2,'A'), (4,'A')
, (5,'B'), (6,'B'), (7,'C');

with cte as (
  select
       [RowId]
    ,  AccountId
    ,  OrderId
    ,  NewOrderId = row_number() over (
          partition by AccountId
          order by [RowId]
    )
  from Renewals
)

update cte
  set OrderId = NewOrderId;

select * from Renewals;

results in:

+-------+-----------+---------+
| RowId | AccountId | OrderId |
+-------+-----------+---------+
|     1 | A         |       1 |
|     2 | A         |       2 |
|     4 | A         |       3 |
|     5 | B         |       1 |
|     6 | B         |       2 |
|     7 | C         |       1 |
+-------+-----------+---------+

To answer the question in the comment of this answer:

  I am using this data to update the previous row. [...] I am trying to show that Row 1 above was renewed by row 2 was replaced by row 4 and row4 has not been renewed as yet

Can be done with:

select
    r.*
  , RenewedBy=p.RowId
from Renewals as r
  left join Renewals as p
    on p.AccountId = r.AccountId
   and p.OrderId = r.OrderId+1

returns:

+-------+-----------+---------+-----------+
| RowId | AccountId | OrderId | RenewedBy |
+-------+-----------+---------+-----------+
|     1 | A         |       1 | 2         |
|     2 | A         |       2 | 4         |
|     4 | A         |       3 | NULL      |
|     5 | B         |       1 | 6         |
|     6 | B         |       2 | NULL      |
|     7 | C         |       1 | NULL      |
+-------+-----------+---------+-----------+

If this is the only reason for the previous update, we don't have to update the table at all. This will get the same results as above:

;with cte as (
  select *
    ,  rn = row_number() over (
          partition by AccountId
          order by [RowId]
    )
  from Renewals
)
select
    r.RowId
  , r.AccountId
  , OrderId = r.rn
  , RenewedBy=p.RowId
from cte as r
  left join cte as p
    on p.AccountId = r.AccountId
   and p.rn = r.rn+1


SQL Audit Log In Repeats Results I'm using SSMS's Audit feature to see who's successfully logging in and logging out of the server and I'm currently testing it in a server in which only I have access to it.
I used for SUCCESSFUL_LOGIN_GROUP and LOGOUT_GROUP for my server audit specifications.

Every time I view the audit logs, why is it that I see that my account constantly logging in and out of the server when in reality I didn't even log out?

It looks like you're using SQL Server Management Studio (SSMS) to query your server. SSMS will open one session for your query tab, plus another session to populate the Object Explorer.

By default, SSMS also has IntelliSense enabled. IntelliSense will make a high volume of very quick database queries in order to validate your query against database schema metadata. Each of the queries will involve a login to the server. I suspect this is responsible for the majority of what you are seeing. One quick way to verify is to disable IntelliSense on your SSMS. Alternatively, you could run you test from another tool that is less "chatty" with the database (such as the SqlServer PowerShell module or sqlcmd CLI).


Optimizing GROUP BY Query I'm running the following query:

SELECT `type`, MAX(`time`) AS `last_updated` FROM `stocks` GROUP BY `type`

On the following table:

CREATE TABLE `stocks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` int(11) NOT NULL,
  `type` varchar(6) NOT NULL,
);

And I have the following indexes:

PRIMARY KEY (`id`),
KEY `type` (`type`),
KEY `time` (`time`)

Yet the query takes about 4 mins to run and there's only about 3.6 million rows.

Here is my explain:

+----+-------------+--------+------+---------------+------+---------+------+---------+---------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra                           |
+----+-------------+--------+------+---------------+------+---------+------+---------+---------------------------------+
|  1 | SIMPLE      | stocks | ALL  | NULL          | NULL | NULL    | NULL | 3684211 | Using temporary; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+---------+---------------------------------+

Why aren't my indexes getting used? Should i have different indexes?

Your indexes won't be used because they won't narrow down the table scan.
As a matter of fact, you want the max time for each type, so even if type index were used as a first intention, you'd still have to fully scan the segment at this index to get the max time for a given type. As a result, MySQL would have scanned the whole table, as a sum of type-indexed segment scans.

Using (type, time) index would in theory give a direct access to the last (max) time, for each type sub-tree. But as far as I know, some older MySQL versions have trouble regarding index selection for this kind of queries.


How to count the rows from 1 table but from two different column SELECT distinct Major, COUNT(Major) AS MajorCount FROM
[College Management System].[dbo].[ApplicantCourseDetails]
group by Major

SELECT distinct MinorI, COUNT(MinorI) AS MinorCount FROM
[College Management System].[dbo].[ApplicantCourseDetails]
group by MinorI

Result should be like this.

╔═══════════╦════════════╦════════════╦════════════╗
║   Major   ║ MajorCount ║   MinorI   ║ MinorCount ║
╠═══════════╬════════════╬════════════╬════════════╣
║ Botany    ║ 1          ║            ║            ║
║ Chemistry ║ 2          ║ Chemistry  ║ 2          ║
║ English   ║ 2          ║ English    ║ 2          ║
║ History   ║ 2          ║ History    ║ 1          ║
║           ║            ║ Philosophy ║ 1          ║
║           ║            ║ Zoology    ║ 1          ║
╚═══════════╩════════════╩════════════╩════════════╝

You can use a cross apply and values to create a row for each value and an extra column in the values clause to keep track of what value is a major and what value is a minor.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE ApplicantCourseDetail(
    [Major] [nvarchar](50) NULL,
    [MinorI] [nvarchar](50) NULL
)
INSERT INTO ApplicantCourseDetail
           ([Major]
           ,[MinorI])
     VALUES
           ('Chemistry','History'),
('English','Chemistry'),
('Botany','Zoology'),
('History','English'),
('Chemistry','Chemistry'),
('English','Philosophy'),
('History','English')

Query 1:

select T.Value,
       count(case when T.IsMajor = 1 then A.Major end) as MajorCount,
       count(case when T.IsMajor = 0 then A.MinorI end) as MinorCount
from dbo.ApplicantCourseDetail as A
  cross apply (values (A.Major, 1),(A.MinorI, 0)) as T(Value, IsMajor)
group by T.Value;

Results:

|      VALUE | MAJORCOUNT | MINORCOUNT |
|------------|------------|------------|
|     Botany |          1 |          0 |
|  Chemistry |          2 |          2 |
|    English |          2 |          2 |
|    History |          2 |          1 |
| Philosophy |          0 |          1 |
|    Zoology |          0 |          1 |


innodb_autoinc_lock_mode = 2, real danger if not using AUTO_INCREMENT columns I've got a MySQL RDS database that uses pure generated GUIDs for key columns. The current value of the innodb_autoinc_lock_mode is 1, and due to some performance bottlenecks we want to change it 2. However, in MySQL documentation it says that:

If you are using statement-based replication, set innodb_autoinc_lock_mode to 0 or 1 and use the same value on the source and its replicas. Auto-increment values are not ensured to be the same on the replicas as on the source if you use innodb_autoinc_lock_mode = 2 (“interleaved”) or configurations where the source and replicas do not use the same lock mode.

Our replication mode is set to Mixed (default in RDS). So what I read here is telling me to keep innodb_autoinc_lock mode to 0 or 1. However the question is: Given that we are NOT using AUTO_INCREMENT columns, what would be the issue with setting innodb_autoinc_lock_mode to 2 with regards to statement-based replication and binlog replay (for recovery) ?

As far as my understanding goes, if you're truly not using any auto-increment identity fields, then that isn't a concern and you should be ok to switch to innodb_autoinc_lock_mode = 2.
The warning is purely about the fact that auto-increment fields can't have 100% guarantee of staying in sync across replicas when interleaved is set because it allows multiple INSERT statements to occur at the same time so the order of INSERTs (and auto-generation of the IDs) can occur in different orders between replicas and the source.
This article goes into pretty good derail about the different values for that setting and their recommendations on when to use interleaved mode for performance reasons.


How to store list of unknown size in database I have trouble with database design and don't know how to overcome this. The problem is with list of cities between two chosen city. User chooses start and finish city, and OPTIONALLY, user can write which cities between he will pass, so it can be 0,1,...N. How can I store this in my database ? I already have table with list of States and another table with list of cities.

I thought a bit, and if there is no better option, I will have row in which I will store IDs of cities in format "ID1,ID2,ID3,..." and in software I will separate IDs, but I think this is not normal.

You should keep your waypoints (midway cities) in a separate list, with the trip key and the sequence of the stop as the primary keys.  Consider the following ERD:

This lets you keep the initial origin and the final destination in your trip table and it lets you add zero or more waypoints along the way without storing any redundant data (i.e. the schema is in third normal form).


Zip based search in mysql. I want to implement a zip based search in mysql database, I am populating the location column in my database based on a drop-down where in I have predefined zip codes, now if a user wants to search for a commodity which is in the radius of 75 miles of his present location, I want ideas as how and which is the best way to accomplish this. I am new to mysql so a detailed answer would be much appreciated. I would be much glad if I am given some example too.

There are a couple of ways you could do this

a. Geocode your postcodes and calculate the distance between the 2 points (or use an existing API to do this).  There are several places you can geocode small quantities of addresses for free, or you can purchase databases. Note that this method is not entirely trivial, but is probably the best for the long term.

You can get Geocoding done via Google at https://developers.google.com/maps/documentation/geocoding/

Then, to calculate the distance, use https://developers.google.com/maps/documentation/distancematrix/

b. Manually set which zip codes are bordering other zip codes in the database. You say you are supplying predefined zip codes so this may be a simpler solution (though for a large number of zip codes it would be costly)
The idea is that you have a new table zip_neighbours as follows

 Zip code,     neighbour
  100,       101, 102, 103
  101,       100, 102, 104
  102,       100, 101, 104

This wont give an accurate '75 miles' boundary, but can be used as a pretty good indication. When a user wants to include nearby zip codes you lookup the neighbours in this table and include them in the search.


postgresql prepared statements with JSONB containment I'm trying to write a prepared statement using a date held in JSONB.  A prepared statement doesn't allow the containment operator @> so I used the function name itself jsonb_contains which executes, but now postgres doesn't use the gin index.
Testing in a psql session performance is excellent when I do this
select *
from tab
where jsonb_obj @> '{"date_el":"2001-01-31"}'

because it uses the gin index.  But that won't parse in a prepared statement in java.
When I run this in psql:
select *
from tab
where jsonb_contains(jsonb_obj->>'date_el', '2001-01-31')

performance is very bad.
Has anyone ever had this problem?

the error we see is "operator does not exist: jsonb @> text"

I assume you are passing the JSON string via PreparedStatement.setString().
Postgres expects a JSONB value, not a text value. You can easily overcome this by casting the parameter to a jsonb:
select *
from tab
where jsonb_obj @> cast(? as jsonb)


Is this an error in SQL Server's documentation for ROLLBACK? This is the SQL Server's document talking about the ROLLBACK statement. On that page it states that the syntax of it is as follows:
ROLLBACK { TRAN | TRANSACTION }
     [ transaction_name | @tran_name_variable
     | savepoint_name | @savepoint_variable ]
[ ; ]

Apparently the stuff inside square brackets are optional and it seems you should choose one from TRAN or TRASACTION. But in fact you can completely omit both and ROLLBACK is a perfectly valid statement. Is this an error or intentionally?

The ROLLBACK TRANSACTION documentation is correct, albeit not clear without additional context.
ROLLBACK is shorthand for the similar ROLLBACK WORK statement rather than ROLLBACK TRANSACTION. Below is an excerpt from the ROLLBACK WORK doc page:

ROLLBACK [ WORK ]   [ ; ]
This statement functions identically to ROLLBACK TRANSACTION except
that ROLLBACK TRANSACTION accepts a user-defined transaction name.
With or without specifying the optional WORK keyword, this ROLLBACK
syntax is ISO-compatible.
When nesting transactions, ROLLBACK WORK always rolls back to the
outermost BEGIN TRANSACTION statement and decrements the @@TRANCOUNT
system function to 0.

Note the same applies to the similar COMMIT TRANSACTION and COMMIT WORK statements.


MS SQL Server does not create tables in a specified filegroup I have three filegroups in my database:

Default is TERTIARY. Now I want to create table on PRIMARY and it shows the following error

  Incorrect syntax near the keyword 'primary'.

create table tb1
(
    id int identity primary key nonclustered,
    somecolumn nvarchar(15)
)
on PRIMARY;

When I change the default filegroup option to PRIMARY it lets to create tables on PRIMARY.
Why does it show this error when my syntax is not wrong?

Thanks for your time.

I think your problem stems from the fact that PRIMARY is a reserved word.  There are several options for the ON clause of a CREATE TABLE statement and putting square brackets around the word [PRIMARY] eliminates confusion about what you are actually asking SQL Server to do.


Converting unix timestamp to an sql timestamp in postgres I have passed Date.now() and other plain unix timestamps like 1534360109480 into sql and ran them through this.

"@converted_date" = to_timestamp("@date") AT TIME ZONE 'UTC';

And it consistently returns 50591-11-28 22:32:38.

I do not understand how the year could possibly be 50591 and the date/time is not accurate beyond that anyways.

What am I doing wrong?

to_timestamp accepts the epoch value ("Unix time") with second precision, while your value seems to have millisecond precision. You need to do something like

to_timestamp(@date/1000) AT TIME ZONE 'UTC';


MySQL: Error while reverse engineering database from a sql file I'm trying to reverse engineer a DB in MySql with a sql file generated from an OpenOffice database. while trying to import and run the sql file, I get the following error:

    ERROR: Line 1: syntax error, unexpected IDENT_QUOTED, expecting EVENT_SYM or FUNCTION_SYM. Statement skipped.

I tried many a things including removing double quotes and adding delimiter to the statements, yet the issue persists. For your reference, please find the excerpts from the .sql file.

CREATE SCHEMA PUBLIC AUTHORIZATION DBA;

CREATE CACHED TABLE Users(UserID NUMERIC(15) NOT NULL PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
MobileNumber VARCHAR(12) NOT NULL,
EmailAddress VARCHAR(50) NOT NULL,
CompanyName VARCHAR(50),
City VARCHAR(50),
StateOrProvince VARCHAR(50),
CountryOrRegion VARCHAR(50),
PostalCode INTEGER,
PassPhraseID NUMERIC(10) NOT NULL,
Gender CHAR(1) NOT NULL,
Age NUMERIC(3) NOT NULL,
MaritalStatus CHAR(10),
Height NUMERIC(4,1),
Weight NUMERIC(3),
Complexion INTEGER,
PreferredMessageMode VARCHAR(10) NOT NULL,
isPaid BOOLEAN NOT NULL,
FBID VARCHAR(50),
BBM VARCHAR(8),
PreferredVerticals VARCHAR_IGNORECASE(100),
EmergencyNos CHAR(60) NOT NULL,
DOB DATE,
PhotoID INTEGER,
CategoryID NUMERIC(2) NOT NULL,
isActive BOOLEAN NOT NULL,
ActivationDate TIMESTAMP(0));

CREATE CACHED TABLE UserLocation(
UserID NUMERIC(15) NOT NULL PRIMARY KEY,
Location NUMERIC(22) NOT NULL,
Timestamp TIMESTAMP(0) NOT NULL,
CONSTRAINT SYS_FK_101 FOREIGN KEY(UserID) REFERENCES Users(UserID));

I have no clue about this. Please help, Thanks in Advance

There are a few things wrong with the DDL Open Office has generated for you:

MySQL doesn't have a VARCHAR_IGNORECASE datatype
The CREATE CACHED TABLE statement isn't valid
TIMESTAMP(0) should be TIMESTAMP

The following should work:

CREATE TABLE Users(
  UserID NUMERIC(15) NOT NULL PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  MobileNumber VARCHAR(12) NOT NULL,
  EmailAddress VARCHAR(50) NOT NULL,
  CompanyName VARCHAR(50),
  City VARCHAR(50),
  StateOrProvince VARCHAR(50),
  CountryOrRegion VARCHAR(50),
  PostalCode INTEGER,
  PassPhraseID NUMERIC(10) NOT NULL,
  Gender CHAR(1) NOT NULL,
  Age NUMERIC(3) NOT NULL,
  MaritalStatus CHAR(10),
  Height NUMERIC(4,1),
  Weight NUMERIC(3),
  Complexion INTEGER,
  PreferredMessageMode VARCHAR(10) NOT NULL,
  isPaid BOOLEAN NOT NULL,
  FBID VARCHAR(50),
  BBM VARCHAR(8),
  PreferredVerticals VARCHAR(100),
  EmergencyNos CHAR(60) NOT NULL,
  DOB DATE,
  PhotoID INTEGER,
  CategoryID NUMERIC(2) NOT NULL,
  isActive BOOLEAN NOT NULL,
  ActivationDate TIMESTAMP
);

CREATE TABLE UserLocation(
  UserID NUMERIC(15) NOT NULL PRIMARY KEY,
  Location NUMERIC(22) NOT NULL,
  Timestamp TIMESTAMP NOT NULL
);

ALTER TABLE UserLocation
ADD CONSTRAINT FK_UserLocation
FOREIGN KEY (UserID) REFERENCES Users(UserID);


Drop user did not reduce size of tablespace file I have a  development environment in which I frequently copy a certain schema.

When I'm done with the copy, I will use OracleEM to drop that user/schema.

My problem is that this does not seem to reclaim the space that the schema was occupying from my tablespace file.

I read doing an DROP SCHEMA .... CASCADE would do this, but the users are already deleted.

Now my entire drive is filled up.

How can I reclaim this space now?

I don't think there's much value in me copying what others have done so well in explaining in the past, so I'll just point you at a brilliant blog post: Reclaiming Unused Space in Datafiles.

Essentially you just need to do:

ALTER DATABASE DATAFILE '/path/to/datafile.dbf' RESIZE 111M;


Availability Group Read Only Routing Not Working for Azure App Service that connects over Hybrid Connection We have following config of Availability Group On-Premise:

server1.domain.com - primary
server2.domain.com - sync secondary
server3.domain.com - async secondary

sql-listener.domain.com - availability group listener DNS name

Recently we have setup Read Only Routing (ROR)
Read Only Routing URLs:

server1 - TCP://server1.domain.com:1433
server2 - TCP://server2.domain.com:1433
server3 - TCP://server3.domain.com:1433

Read Only Routing Lists:

server1: server2, server3, server1
server2: server1, server3, server2
server3: server2, server1, server3

Issue/Question:

One of our Azure App Services (Application), is using hybrid connection to connect to on-premise AG Listener (sql-listener.domain.com)
Hybrid connection settings:

Endpoint host: sql-listener
Entpoint port: 1433

Application is using connection string:

Server=sql-listener,1433;Initial Catalog=MyDatabase;User Id=SQLAuthLogin;
Password=SQLPassword;App=MyApp;MultiSubnetFailover=True

And without "ApplicationIntent=ReadOnly" part, application CAN connect to on-prem DB just fine
When I am adding "ApplicationIntent=ReadOnly" part to connection string, app can't connect to on-prem db and throws error:

Error: A network-related or instance-specific error occurred while
establishing a connection to SQL Server.
The server was not found or was not accessible.
Verify that the instance name is correct and that SQL Server
is configured to allow remote connections.
(provider: TCP Provider, error: 0 - No such host is known.)

Additional Info: Application is not integrated to any VNET
Application CANNOT connect when I replace "Server=sql-listener,1433" part by "Server=192.168.xxx.xxx" (AG Listener's IP Address)

After googling similar topics, I found that when app connects to AG Listener with "ApplicationIntent=ReadOnly", listener responds and provides readable secondary database server's address to Application (I assume it is "server2.domain.com" DNS address, not IP address ?)

So I created extra hybrid connections in Azure, that have endpoint hosts and port as "server1" "server2" "server3" and 1433, and made sure these hybrid connections are added to Application and connected to hybrid connection manager at on-prem server

I hoped this way "server1" "server2" "server3" DNS names would "become visible" to Application and when AG listener responds to Application (with conn string "ApplicationIntent=ReadOnly") option and directs it to "server2" (server2.domain.com), Application would be able to connect

But Application still throws error "A network-related or instance-specific error..."

What I am missing ?
How to make Read Only Routing work with Azure App Service that connects over Hybrid Connection ?

Microsoft Support engineer replied below on my case:

  I (Support engineer) have reached my technical leads to have an urgent update on this and I was able to get it.
  Unfortunately, the Product Group confirms my initial suspicions about the setup you want to achieve not being possible due to Hybrid Connections limitations therefore not supporting this. It’s a by-design behavior and currently Hybrid Connections does not support this scenario. The Product Group engineers confirmed me that this feature is being considered however they have no ETA to share.

Hybrid Connections do not support AG read-only routing - as simple as that

I have created feature request on Azure website, please login and up-vote it if you can
https://feedback.azure.com/forums/169385-web-apps/suggestions/38917648-enable-hybrid-connections-to-work-with-read-only-r


Grouping results and filtering duplicates by the row's date I'm trying to write a query that gets all the records grouped by code and the rows that are duplicated take the one with the most recent date.
+----------+---------+-------------+
| code     | name    | date        |
+----------+---------+-------------+
| a        | Mike    | 2009-01-05  |
| b        | Jones   | 2010-02-06  |
| b        | Who     | 2009-01-05  |
+----------+---------+-------------+

expected result:
+----------+---------+-------------+
| code     | name    | date        |
+----------+---------+-------------+
| a        | Mike    | 2009-01-05  |
| b        | Jones   | 2010-02-06  |
+----------+---------+-------------+

I've gotten this far:
SELECT code, name, date
FROM table
GROUP BY code

I'm not sure how to aggregate a column based on another column's value.

Because you have other columns that you're not aggregating (name) but are eliminating rows based on the date column (latest record only), using GROUP BY is not ideal.
You can use windowing functions to find the specific record based on the grouping (code) and return only those records.
Example:
WITH CTE AS
(
  SELECT code
    , name
    , date
    , ROW_NUMBER() OVER (PARTITION BY code ORDER BY date DESC) AS [RowNumber]
  FROM Records
)

SELECT *
FROM CTE
WHERE RowNumber = 1

You can see a working example here. Basically it is using a CTE and the ROW_NUMBER function to assign a row number to each row within the code groups in a date descending order. This means you can simply select each row where the ROW_NUMBER value is 1.


mysql Query Columns to Rows unitid | row1    | row2   | row3   | row4    | row5    | row6
-----------------------------------------------------------------
200    | sample1 |sample2 | sample3| sample4 | sample5 | sample6

expected OUTPUT

200
sample1
sample2
sample3
sample4
sample5
sample6

The only easy way to do this is with UNION ALL queries, but with more than one row in your database you may not get the answer you are expecting.

SELECT unitid FROM your_table
UNION ALL
SELECT row1 FROM your_table
UNION ALL
SELECT row2 FROM your_table
UNION ALL
SELECT row3 FROM your_table
UNION ALL
SELECT row4 FROM your_table
UNION ALL
SELECT row5 FROM your_table
UNION ALL
SELECT row6 FROM your_table

If you have two rows, you will get two unitid values, then two row1 values, etc.


how to shrink(and also reduce size on disk) a very big log file in SQL server 2016 I have a datawarehouse with database log of 800 GB.now I want to shrink it or reduce the disk space occupied by Log.ldf file. I tried to shrink file in several ways - took full backup , performned transaction log back up, modified recovery mode from Full to Simple, executed dbcc command however none of them seems to help in reducing log size at disk.

I also detached database, deleted log file but due to memory-optimize file container I got error while I was trying to attach it again (I had read SQL server will automatically add a log file but seems it doesn't work when database has a memory_optimize file).

After all the above attempts database log file is still 800 GB and I am not really sure how to clean up space consumed by database log. Appreciate if I could get some expert opinion on this issue or guide me in case I am missing something.

I completely agree with Biju, I had similar case when log size was not reducing at all however this was in Sybase, nevertheless concept is same, you may refer the case here:

Database Backup size not reducing

You can also use sp_WhoIsActive to check what is currently running at your database and what is the underlying command, who is running it, from how long this is running and many other details; this would help you in finding whether you should kill the specified spid or should consult with user before taking any action. I hope this should help.


How would one merge two databases that use different keys? I have Database 1, and Database 2. Database 1 is the companies native data on clients and Database 2 is an external database being ported over from a different company. I'm trying to find what customers (Database 1) in our database, exist in their database (Database 2).
Database 1 has: customer_id (PK), customer_name, customer_email, customer_phone, customer_location, etc...
Database 2 has: customer_key (PK), customer_name, customer_email, customer_phone, customer_location, etc...
The question is: Find a way to link Database 1 to Database 2 so that each person in Database 1's information is joined with their respective information in Database 2.
The problem is: these databases have two different primary keys (or ids assigned to an actual real life person). So you can't just simple query them based on the PK "customer_id" because "customer_id" does not exist in Database2 and vice versa.
Initially I think of using a variable that is unique (i.e email) and matching on that...
But... what if the email they provided in one of the databases is "shared", meaning, in Database 1, that email is actually just an email the business shares for a bunch of different departments... Then it's not really helpful in linking back to a single person. Or if in Database 2 they provided a shared email, so many different records if grouped together on email will show they have multiple names, and phone numbers associated with them... This gets even more complicated when all the variables in the database (phone, name etc...) also have this "shared" problem for some of their entires.
People can also change companies (changes their email and possibly phone) or how people change their first and last name (more so last name).
The only solution I can think of at the moment is to use some sort of combination of verifying the email matches, then verifying that phone, name and location do as well. The more items that they match on between databases, the more certain one can be that "customer a" in Database 1 is "customer 123" in Database 2.
How do you guys deal with this? Do you use a LIKE condition or something similar and just set a threshold for similarity when joining?

There's not one specific right answer to your question but the thought process you went through in it is the correct way to try to solve the problem.
In lieu of a randomly generated unique primary key, sometimes tables have something known as the natural key. If the natural key is composed of multiple columns, it is known as a composite key as well. An example of this is a UserAccounts table that has a UserId primary key, but also has a LoginEmail column with a unique constraint on it, making it a natural key.
The best thing you can really do is come up with an algorithm, which generally can be as simple as picking a set of fields that make the best composite natural key candidate and joining the two tables between the two databases on those fields.
Then you have 3.5 cases you have to handle:
1: When there is a truly distinct match of one and only one record as a result of the join. In this case you can be fairly confident it's correct and no need for further processing.
2: When there are multiple rows that match between the two tables. Usually human intervention is needed if no other process exists to decide how to proceed. E.g. if there was a CreatedDate for the records, you could throw away everything but the most latest CreatedDate records (if it made business sense). Human consolidation is not uncommon for this case.
3.5: When no records match in Table1 for Table2 (and visa-versa, there's records in Table1 that don't find a match in Table2). If that's the case then you basically need no further action since there's only one source of truth.


MySQL Replication via DNS Failure I'm having some trouble using replication when the machines are configured to use host names instead of IP addresses. The "skip-name-resolve" is OFF.

If I use IP addresses, everything is fine. Using DNS names, it all stops.

On the Master

Replication is configured with the following command:

GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'slavename.internal.domain' IDENTIFIED BY 'repl_pwd';

On the Slave

The slave connection is configured as such:

change master to
    master_host='mastername',
    master_user='repluser',
    master_password='repl_pwd',
    master_log_file='mastername.000001',
    master_log_pos=1;

The Error I am Getting

When starting the replication slave, the log file shows the following messages:

120127 14:07:48 [Note] Slave SQL thread initialized, starting replication in log 'mastername.000001' at position 4, relay log './mysql-relay-bin.000001' position: 4
120127 14:07:48 [ERROR] Slave I/O: error connecting to master 'repluser@mastername:3306' - retry-time: 60  retries: 86400, Error_code: 1130

If I then try to connect using the command line from the client machine using:

mysql -h mastername -u repluser -prepl_pwd

I get the error:

ERROR 1130 (HY000): Host 'the.ip.address.here' is not allowed to connect to this MySQL server

I really hope someone out there can help me with my domain resolution issues!!!

I feel terrible for forgetting about this question!!!

I have located and fixed the problem as follows.

When the DNS names were added to DNS, the corresponding reverse lookup was not created.

This resulted in MySQL not being able to perform the reverse lookup from the IP address to the correct DNS name, and therefore rejecting the connection.

So, we added a set of reverse lookups from IP address to DNS names, ran FLUSH HOSTS; on all of the MySQL boxes, and everything started working.

We require the use of DNS names for all connections, due to our disaster recovery solution being in a separate data centre, and a virtually identical VM farm, but with the IP addresses of all the machines modified only slightly. If / When we have a complete fail-over to the other data centre, all the software and communications will just "work", as the DNS resolution will always give the correct address based on the data centre.

You just need to be careful not to have TWO (or more) names reverse looking up from the same IP address, as there is no guarantee which of the two names will be returned - sometimes your connection will work, and other times it won't.

Hope this helps someone with the same problem!

Regards,
Dave


Is it possible to have indexes for like '%*%' type queries Now for my thesis project i need to make a lot of queries like the below

select * from tblMyTable where ItemName like '%item%'

Now my question : is that possible to create indexes for such queries ? If so what is the way of doing it ? Thank you.

For LIKE queries this is not available.

But for making such queries SQL Server provides functionality called Full-text search. More info in MSDN.


ORA-00922 encountered in Oracle 11gR2 installer I've just run the installer for Oracle 11gR2 on a Windows Server 2008 (64-bit) OS.

I encountered the "ORA-00922: missing or invalid option" error at the last stage of installation when it was doing something like "cloning the database". I ignored the error and then immediately got a "ORA-28000: the account is locked" error. The installer then completed successfully.

I looked in the logs for the installation ("C:\app\administrator\cfgtoollogs\dbca\orcl") and found one file named "postScripts.log" which contains these two error messages. Here is the contents of that file:

     1
     1
alter user CTXSYS account unlock identified by ******
*
ERROR at line 1:
ORA-00922: missing or invalid option

ERROR:
ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.
dropping default lexer preference...
SP2-0640: Not connected
dropping default wordlist preference...
SP2-0640: Not connected
dropping default stoplist preference...
SP2-0640: Not connected
dropping default policy...
SP2-0640: Not connected
SP2-0640: Not connected
Enter value for lang_abbr: SP2-0310: unable to open file "C:\app\Administrator\product\11.2.0\dbhome_2\ctx\admin\defaults\drdefset.sql"

Has anyone else encountered this error before, or know how to resolve it? Will the database function correctly?

Thanks in advance!

I found a solution on my own!

It seems that there is a bug in the Oracle installer - it doesn't like it if you start your system password with a number.

I completely uninstalled Oracle using the deinstall utility and then reinstalled Oracle, this time specifying a system password beginning with a letter. This solved the problem!

I hope this helps anyone else encountering this annoying issue.

Additionally, user HanFromBs found that the installer doesn't like special characters (- or +) at the start or end of the password, so watch out for this too.


how to drop a db2 database that is in an inconsistent state I have a DB2 database that suffered a power outage in the storage array, its a test system I just want to kill it and rebuild.
db2 connect to mydb
SQL1015N  The database is in an inconsistent state.  SQLSTATE=55025

db2 drop db mydb
SQL1035N  The operation failed because the specified database cannot be
connected to in the mode requested.  SQLSTATE=57019

db2 restart db mydb
SQL1015N  The database is in an inconsistent state.  SQLSTATE=55025

db2stop
SQL1025N  The database manager was not stopped because databases are still active.

db2 backup db mydb to /dev/null
SQL1015N  The database is in an inconsistent state.  SQLSTATE=55025

Since the database seem to be of no value, you can try:
db2stop force
ipclean
db2start
db2 drop database mydb

You may be interested in the following post/answer. It is not exactly your situation but may be of some help anyhow:
unable-to-restore-database-because-it-exists-unable-to-drop-because-it-does-not


Is there any way to detect and get information on a push-subscription on subscriber's side? I've got a database in Azure SQL. There may be a push-subscription from on-premises database. I have no access to on-premises servers where distribution/publication/subscription is created and configured.

There are many many ways to get information about subscription from dictributor or publisher's side, they are easily googled. At the same time, I did not manage to find a way to detect subscription existence/health at the subscriber's side.

My current way of detection is indirect. Namely, I know the table where new records appear often and just count the number of the records and then again a minute after. This does not help in the night, when no activity in the database, and this is inconvenient due to necessity to wait a minute or more.

What I need is a script that clearly and directly shows me, if the subscription exists, and probably/if possible some additional information:
 * if it is active (does push-side work properly at the moment)
 * which articles are configured, etc

You have 4 tables For transactional replication (push subscription) at the subscriber's side:

MSreplication_objects

  contains one row for each object that is associated with replication
  in the Subscriber database

MSreplication_subscriptions

  contains one row of replication information for each Distribution
  Agent servicing the local Subscriber database

MSsubscription_agents

  is used by Distribution Agent and triggers of updateable subscriptions
  to track subscription properties

MSsnapshotdeliveryprogress

  is used to track files that have been successfully delivered to the
  Subscriber when a snapshot is being applied

you can use this columns to monitor the status for your replication at the subscriber's side

SELECT login_time,
       last_sync_status,
       last_sync_summary,
       last_sync_time,
       spid
FROM dbo.MSsubscription_agents


Analytic Functions in SQL Server I need to implement an ETL to move from Excel to SQL Server some information. In Excel, users have some analytical functions that we would like to have also in SQL Server (sigh...)

Does anybody know if I can find these functions in SQL Server?

RSQ (calculates the r-squared value)
LOGEST (calculates an exponential curve)
LOGNORM.INV (returns the inverse of the lognormal cumulative distribution function given specified parameters: mean/average and standard deviation)
STDEV (calculates the sample standard deviation)
SUMIFS (sums values based on multiple conditions)
COUNTIFS (counts values based on multiple conditions)
SUMPRODUCT (returns the sum of the products of corresponding ranges or arrays)

The following are natively available in SQL Server:

SUMIFS

-- SUM of a column or expression
SELECT SUM(IIF(so.[type] = 'S', so.[schema_id], 0)) AS [SumIFs]  -- 293
FROM   [master].[sys].[objects] so;

COUNTIFS

-- SUM as a counter, COUNT counting everything, and COUNT counting non-NULLs
SELECT SUM(IIF(so.[type] = 'S', 1, 0)) AS [SumIFs],  -- 74
       COUNT(IIF(so.[type] = 'S', 5, 0)) AS [CountIFsGotcha], -- 103
       COUNT(IIF(so.[type] = 'S', 1, NULL)) AS [CountIFsCorrect] -- 74
FROM   [master].[sys].[objects] so;

SUMPRODUCT

SELECT so.[type], SUM(CONVERT(BIGINT, so.[object_id]) * so.[schema_id])
FROM   [master].[sys].[objects] so
GROUP BY so.[type]
ORDER BY so.[type];

STDEV

SELECT STDEV(so.[schema_id]) AS [StandardDeviation]
FROM   [master].[sys].[objects] so;

Please note that the IIF statement -- introduced in SQL Server 2012 -- is just shorthand for: CASE WHEN expression THEN true_result ELSE not_true_result END. Example:

SELECT IIF('a' = NULL, 1, 2); -- 2

The following are not available natively in SQL Server, but can most likely be created as SQLCLR User-Defined Aggregates (UDAs).

RSQ
LOGEST
LOGNORM.INV


Am I able to create table using EM Express Oracle 12c Please if someone can let me know if I could create table using Oracle 12c EM Express or also known as dbconsole? In 11g there user will go to schema -> db objects -> tables, but I don't see schema in 12C .

You can not do that in EM Express.

Oracle Database 12c: EM Database Express

I'm trying to perform a task I used to do in DB Control.  Where is it in EM Express?

  Although EM Express provides support for basic administrative tasks,
  it is not a direct replacement for DB Control.  As explained in the
  Architecture section, EM Express is built inside the database server
  and cannot perform actions outside the database.  Also, EM Express is
  focused primarily on providing performance management and monitoring
  functionality, and is not meant to be an all-around database
  administration tool.


Does MySQL reserve memory for MEMORY tables based on max_heap_table_size Currently I have a set of large MEMORY tables, and have previously calculated how much space I believe each will require and assigned max_heap_table_size accordingly so that the sum is not greater than 12GB.

If I were to assign all tables (approx 19x) to have a max_heap_table_size = 12*1024*1024*1024; -- 12GB, then would that then result in MySQL attempting to reserve 228GB of memory, or would it instead allow this however then just run into issues if the tables collectively grew too big?

To rephrase, does MySQL only reserve memory for MEMORY tables as and when it's needed, with max_heap_table_size just used as a throttle on that individual table size if it were to grow too big?

max_heap_table_size defines maximum size of table, but mysql do not allocate that in advance.


How to tell when DBO was changed (and if possible, by whom) I am looking for a way to track when the DBO account is changed.  For example, if someone uses sp_changedbowner is there a way, after the fact, to determine when this occurred, and even better, what user was used to make the change?

This would be useful in tracking changes in the event of a database breach where teh attacker changed permissions, for example, so I suspect the possibility must be there.

I've been attempting to google it and look for it in the MSDN documentation, but I'm just not finding this.

This is in the default trace. Whether you use sp_changedbowner or the proper, modern syntax, it will show up as the latter:

alter authorization on database::[foo] to [bar]

You can retrieve the information this way:

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT * -- whittle down to the meaningful trace columns
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 152
ORDER BY StartTime DESC;

Now, the event might have happened a long time ago, so it won't necessarily still be in the trace.


How can I determine which permissions needs to be assigned to the newly created login? We have 4 databases which contain different procedures and functions. Till now the actual application can access the database using only the sa account, now I would like to change the app to use something other than the sa account. For that I need to create a new SQL Login and assign to that login certain permissions. Is there any way like script to determine what permissions I need to assign to the newly created user?

The link by @Ross will definitely help you determine syntax and what all of your options are, but for a little more guidance:

There are two database roles that will probably be useful to you, they are db_datawriter and db_datareader.  The two of them will probably handle the majority of your CRUD (Create, Read, Update, Delete) operations.

ALTER ROLE db_datareader ADD MEMBER <user>
ALTER ROLE db_datawriter ADD MEMBER <user>

You can get more granular than this if you want/need.  For example, allowing inserts, but not updates or deletes to tables.

You mentioned stored procedures in the tags, so those can be a bit trickier.  One of the easier methods is to grant execute on the schema.  But you can also grant them one at a time.

--all stored procedures in schema dbo
GRANT EXECUTE ON SCHEMA::dbo TO <user>

--specific stored procedure
GRANT EXECUTE ON dbo.uspDoMyThing TO <user>

Finally, if you are allowing the application to do TRUNCATE then they will actually need the ALTER TABLE permission in addition to db_datawriter.

GRANT ALTER TABLE ON dbo.MyTable TO <user>

Congratulations on taking control of the security of your server properly.  Even if you have to end up granting db_owner in the database, that's far better than sysadmin.

Some final notes:

Explicit deny permissions take precedence over grants.
Assigning permissions to schemas will grant permissions to all objects in that
schema (even if they get created later).
I would start with db_datareader, db_datawriter and EXECUTE on all stored procedures and that will get you going the fastest with the least maintenance
required later.  It may be broader than necessary but the damage that
can be done is minimized.


Modify SQL Query Text How can I make below query work for virtual attached HDD. As of now this query return only C drive even though I have other drives also.

SELECT DISTINCT dovs.volume_mount_point AS Drive,
CONVERT(decimal,dovs.available_bytes/1048576/1024) AS FreeSpaceInGB,
convert(decimal,dovs.total_bytes/1048576/1024) as TotalSpaceInGB,
cast(CONVERT(decimal,dovs.available_bytes/1048576/1024)/convert(decimal,dovs.total_bytes/1048576/1024)*100 as decimal(38,2)) as FreeSpaceInPct
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInGB ASC

sys.master_files is a DMV for databases, so it is by design that it does not show drives that don't have databases.

If you need this functionality, consider writing an xp_cmdshell script inside your job to get this information. You'll need to know Powershell.


Activating standby DR after opening it in read-write mode I have a primary and a standby database. Both two node RAC database.

As some testing had to be done in standby database, I had to open it in read-write mode.

I cancelled the recovery in standby and activated standby database and opened the two node database in read-write mode.

Now, the DR is broken. And to make the standby as a proper physical standby, full reconfiguration of DR is required?

If not, help me out in setting up my standby back in track!

If you had flashback enabled or created a restore point before activating the standby database, you can flashback the database to that point/SCN, and the standby database can continue from there like nothing happened.

You can do this manually, or if you have Data Guard Broker configured, you can use the REINSTATE DATABASE command to do this.

In case you have not prepared for this, you will need to rebuild (duplicate/restore) the standby database.

Next time do not do this, there is a feature called Snapshot Standby designed for testing tasks like this, that works just as I described above.


What is a "Reporting Task for Subplan" task in SSIS, and how can I create one? I'm trying to script a daily full backup to a job using an SSIS package. I've inherited several databases from a previous DBA, and I'm trying to emulate the ones he made in order to be consistent. Most of it is pretty straightforward, but there is one task called "Reporting Task for subplan {GUID}" which I am not sure how to create or use:

No task by that name appears to be in the Toolbox pane in BIDS, nor does any task appear to exist which has that icon. Neither Books Online nor Google searches are returning any real documentation or discussion about it. Presumably it's used for logging, since a logfile is specified in its properties.

What is this task and how can I create one, so that I can build packages similar to what my predecessor was building?

Please open them in SSMS Maintenance Plans instead of using BIDS to open them & you won't have to worry about the GUIDs. The task you're asking about is created automatically when you make a maintenance plan in SSIS.


Why do I get The transaction log for database 'X' is full due to 'ACTIVE_TRANSACTION' w/ Simple Recovery and auto grow log? Why do I get  The transaction log for database 'X' is full due to 'ACTIVE_TRANSACTION' on a database with Simple recovery model and auto-growing log file?

The failing operation is executing many, many Insert statements. The insert statements are not occurring inside a transaction. I don't understand why the transaction log has to be so large for this and I don't understand why it's unable to grow.

When I see this error it's usually because there was a long running transaction that has not been committed yet. Note that the size of the long running transaction isn't necessarily the problem. If the transaction changes any amount of data it will prevent the transaction log from clearing past that VLF. So if your transaction log is 1 GB and you do activity in the database that requires more than 1 GB of transaction log since your oldest active transaction you can run into an issue.
For a simple demo I have a database with a recovery model of SIMPLE and a log limited to 100 MB. In one session I insert and delete data in a loop:
create table dbo.X_DUMMY_TABLE (COL VARCHAR(4000));

SET NOCOUNT ON;

-- truncate and insert data in a loop
WHILE 1 = 1
BEGIN
    TRUNCATE TABLE X_DUMMY_TABLE

    BEGIN TRANSACTION
    INSERT INTO dbo.X_DUMMY_TABLE
    SELECT REPLICATE('Z', 4000)
    FROM dbo.getNums(100);
    COMMIT TRANSACTION;

    CHECKPOINT;
END;

With a recovery model of SIMPLE this doesn't cause any issues. Using DBCC SQLPERF(LOGSPACE) I can see that the log is between 1 - 7% full. Note that the transaction log file itself stays at 100 MB.
Now I'll start a transaction in another session that inserts just a single row. I won't commit the transaction:
create table dbo.X_DUMMY_TABLE_2 (COL VARCHAR(4000))

BEGIN TRANSACTION

INSERT INTO dbo.X_DUMMY_TABLE_2
SELECT 'Z';
-- note the lack of a COMMIT

After about 3 seconds the first session throws this error:

Msg 9002, Level 17, State 4, Line 11
The transaction log for database 'SE_DB3' is full due to 'ACTIVE_TRANSACTION'.


Drop the last character of string, if the last character is s Working through an odd issue that the moment and trying to find a solution. If we have a string variable (@string) how might we drop the last character of the string, ONLY if the last character is an s?

It seems a bit odd issue but here's how to do it.
DECLARE @string NVARCHAR(10)
SET @string = 'Tests'

SELECT
  CASE
    WHEN RIGHT(@string,1) IN ('s','S') THEN SUBSTRING(@string,1,LEN(@string)-1)
    ELSE @string
  END AS string


What is the difference between TCP port and TCP dynamic port? Can anyone tell me the difference?

I tried to set either 1 port and left another blank also can work. I wonder what is the difference, or anything I missed out?

The reason both work for you is due to the SQL Browser Service running in your services (start - run - services.msc).  The default instance of MS SQL Server will listen to port 1433, but named instances will use a 'dynamic' port and the SQL Server Browser service will map you to it.  This is not good in large environments where you have security concerns and want to keep using the same ports so you can configure your ACLs.

To test this, you will want to review the links at the bottom, which makes you remove the '0' in the dynamic ports in all of your network interfaces in SQL Server Configuration Manager, and then set a hard coded port in the 'port #' section at the very bottom.  You will then need to restart the MS SQL Server service and stop the SQL Server Browser service.

After doing this you can connect to the port number, NOT the instance name.  So for example it would change from:
SQLServer1\InstanceName

to:

SQLServer1,41450 (whatever custom port number you gave).

You can also make it pretty and clear by:

SQLServer1\InstanceName,41450 (include both the name and port # but it will always go to the port number, not the name).

Configure SQL Server To Listen In On  A Specific Port #

SQL Server Browser


SSRS 2016 on SQL Server 2014 I have question about using SSRS 2016 on SQL server 2014

First of all, I don't have much experience with Reporting services.

Company use SQL server 2014 and they have SQL database on it (DB is around 100GB). I could install SSRS 2014 on this server (because they have licensed SQL 2014), but I want to use SSRS 2016, because SSRS 2016 has many new features (web portal, mobile reports, custom design, KPI,..).

My idea is to install SQL 2016 Express version (It's free - 10GB) to install SSRS 2016 on it, only to store this two meta ReportServer DB and then connect to this production SQL 2014 DB to create queries on it.

Is this acceptable solution? Are there any other possibilities or they must buy SQL 2016 license to upgrade their SQL?

Thank you and best regards

SSRS Express will only connect to its own SQL Server instance as a data source. You can’t use it in the way you’re describing it. Sorry.


Update a binary field How to update a binary field from 0x54 to 0x46 (T to F)?

Update dbo.table1
set field = convert(varchar,field,0x46)
where fieldID = '1'

Assuming the field is of BINARY datatype:

UPDATE [dbo].[Table]
SET [field] = CONVERT(BINARY,'F')
WHERE [fieldID] = 1;

Here's my proof of concept:

CREATE TABLE #TempBinTable
 ([Identity] INT
,[BinColumn] BINARY);

INSERT INTO #TempBinTable
VALUES(1,CONVERT(BINARY,'T'));

SELECT [Identity], [BinColumn]
FROM #TempBinTable;
--RESULT
----Identity    | BinColumn
----1           | 0X54

UPDATE #TempBinTable
SET [BinColumn] = CONVERT(BINARY,'F')
WHERE [BinColumn] = CONVERT(BINARY,'T');

SELECT [Identity], [BinColumn]
FROM #TempBinTable;
--RESULT
----Identity    | BinColumn
----1           | 0X46

DROP TABLE #TempBinTable;


How to create a citation/quote from a Postgres text field? I have a database with a lot of text and I want to find the surrounding text for a word that I am searching for i.e. If I search this text for "text" I should get something like (and more consistent than):

.. lot of text and ..
.. the surrounding text for ..
.. this text for "text"..
.. for "text" I should ..

I can write this in a program but I wondered if there is a good SQL trick to get something approaching this with just a query for a quick and dirty report.

what you need is the ts_headline function. it does exactly what you need it seems. here is the documentation: http://www.postgresql.org/docs/9.4/static/textsearch-controls.html


Can't change ORACLE_SID for container-registry.oracle.com/database/enterprise I'm running oracle database in docker like this
docker run -d --rm --name oracle_online -e ORACLE_SID=xxx -e ORACLE_PDB=xxx -e ORACLE_PWD=xxx -v /home/data/oracle:/opt/oracle/oradata -it -p 1521:1521 container-registry.oracle.com/database/enterprise:12.2.0.1

When it's started, the ORACLE_SID is unchanged, it's always ORCLCDB:
TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0/dbhome_1/admin/ORCLCDB/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/95c1ad3d33bd/listener/alert/log.xml

...

#ip-address : IP address of the host where the container is running.
#port       : Host Port that is mapped to the port 1521 of the container.
#
# The mapped port can be obtained from running "docker port <container-id>"
# ===========================================================================
Completed:     alter pluggable database ORCLPDB1 open
2020-11-14T05:42:36.136965+00:00
    alter pluggable database all save state
Completed:     alter pluggable database all save state
2020-11-14T05:42:36.426957+00:00
ALTER SYSTEM SET encrypt_new_tablespaces='DDL' SCOPE=BOTH;
2020-11-14T05:42:38.664560+00:00

Does anyone know how to change the SID?

Sure, you need to change pfile/spfile and set ORACLE_SID before the startup of the database to change the name of the instance. Meaning you need to change how the docker image starts the database, if you want this done automatically.
Simply passing some environment variables through docker after the database was started will not change anything.


How often to backup the master database? The suggestion in BOL is fairly vague:

  Back up master as often as necessary to protect the data sufficiently
  for your business needs. We recommend a regular backup schedule, which
  you can supplement with an additional backup after a substantial
  update.

If you venture further, you will find these details:

  The types of operations that cause master to be updated, and that
  require a backup to take place, include the following:

  Creating or deleting a user database.
  If a user database grows automatically to accommodate new data,
  master is not affected.
  Adding or removing files and filegroups.
  Adding logins or other operations that are related to login
  security.
  Database security operations, such as adding a user to a database,
  do    not affect master.
  Changing server-wide or database configuration options.
  Creating or removing logical backup devices.
  Configuring the server for distributed queries and remote procedure
  calls (RPCs), such as adding linked servers or remote logins.

So if all our logins are added through Windows groups, and we don't make any other changes to the database, does that mean that a one-time backup of the master is sufficient?

If not, what is the standard backup interval for the master database?

Your master DB is generally very small. Just back it up with the rest of your databases, at least daily. Does it matter?

Personally, when SHTF I'd like a master db backup that is a few hours old, even if I have 400 other identical ones going back 400 days. I don't want to think too much in the case I have to restore it...


How do I copy my SQL Azure database to a local SQL Server instance? I have an OLTP database hosted on a SQL Azure instance. I want to pull a copy of the database down from the cloud so I can run some heavy extracts and OLAP-style queries against it without impacting the source database.

How do I pull a copy of the database down to a local SQL Server instance?

Use the SQL Azure Migration Wizard:

  The SQL Azure Migration Wizard (SQLAzureMW) gives you the options to
  analyzes, generates scripts, and migrate data (via BCP) from:

  SQL Server to SQL Azure
  SQL Azure to SQL Server
  SQL Azure to SQL Azure


How to select values side-by-side I have a table like this

id_project    id_field    value
1             1           abc
1             2           xfe
2             3           ufj
2             4           osj

And I have to export the data in a CSV-like manner

field_1    field_2
abc        xfe

I tried this

SELECT

(SELECT  value
 FROM    table
 WHERE   id_field = 1) AS field_1,

(SELECT  value
 FROM    table
 WHERE   id_field = 2) AS field_2

FROM table
WHERE id_project = 1;

but it does not work.

EDIT: thanks for all the answers, I think I figured out  after reading this comment https://stackoverflow.com/a/13065203/1822157

You don't mention what it is that does not work, but clearly:

 WHERE id_project = 1

matches 2 rows, so if the problem is duplicates, that is the root cause. Easiest to solve by adding distinct

SELECT DISTINCT
(SELECT  value
 FROM    table
 WHERE   id_field = 1) AS field_1,
(SELECT  value
 FROM    table
 WHERE   id_field = 2) AS field_2
FROM table
WHERE id_project = 1;

Note that if id_field is not unique, you need a correlation against the outer select:

SELECT DISTINCT
(SELECT  value
 FROM    table
 WHERE   id_field = 1
   AND   id_project = t1.id_project ) AS field_1,
(SELECT  value
 FROM    table
 WHERE   id_field = 2
   AND   id_project = t1.id_project ) AS field_2
FROM table as t1
WHERE id_project = 1;

Another option would be:

with t (id_project, id_field, value) as (
    values (1,1,'abc')
         , (1,2,'xfe')
         , (2,3,'ufj')
         , (2,4,'osj')
)
SELECT MAX(x.val1), MAX(x.val2)
FROM t
CROSS JOIN LATERAL (
    VALUES (CASE t.id_field WHEN 1 THEN value END
           ,CASE t.id_field WHEN 2 THEN value END)
) x (val1, val2)
WHERE t.id_project = 1;


Is COUNT(DISTINCT PRIMARY_KEY) faster than COUNT(*)? I haven't got a particular problem here, I'm just interested. One of my associates uses COUNT(DISTINCT(PRIMARY_KEY)) every time he needs to select all the data from a table (with 100m + rows) is it any quicker to do it this way rather than just count(*)? If it's the primary key surely the database knows that it's just pulling a count for the whole database and why?


Transaction Log maintenance when switching to Simple Recovery Background:

I recently inherited 50+ SQL Servers with 450+ databases. The nightly backups are roughly 8TB and, needless to say, we're using more disk space than we'd like. All of the databases are set to FULL recovery and the transaction logs have never been backed up.  I've gone through all of the SQL Servers and identified low priority ones that only need a nightly backup and a where a day of data loss is acceptable.

Question:

I'm switching a lot of low priority databases to SIMPLE recovery mode from FULL. Will the existing transaction logs be truncated (when checkpoints are created)? Some of the existing transaction logs are 50-100GBs; what is the best approach in determining what I should shrink them down to for the purposes of moving forward? I obviously don't want to keep them that large. Or, will they shrink on their own over time (I don't think they will)?

I'm switching a lot of databases to SIMPLE recovery mode from FULL recovery mode (T-Logs and point-in-time recovery is not necessary). Will the existing transaction logs be truncated (when checkpoints are created)?

In simple recovery model, the database engine will issue automatic checkpoints and its frequency is determined by the recovery interval (advanced server config setting) or if the log becomes 70% full.

Unless you have some long running transactions occuring that will delay log truncation, an automatic checkpoint will truncate the unused portion of the T-log.

  Some of the existing transaction logs are 50-100GBs, what is the best approach in determining what I should shrink them down to for the purposes of moving forward. I obviously don't want to keep them that large.

If you have the database recovery model set to FULL for those database with 50-100GBs of T-logs, then you have to start doing frequent T-log backups. Remember in Full recovery model, once a log backup chain has been established, even the automatic checkpoints wont cause the log to truncate.

As a last resort, you can truncate the log file and then immediately take a full backup and then start taking T-log backups so that you can do point-in-time recovery if a disaster happens.

  Or, will they shrink on their own over time(I don't think they will)?

As @TomTom pointed out, its a manual operation.

Read up :

Manage the Size of the Transaction Log File
HOW TO SHRINK THE SQL SERVER LOG - Excellent Write up.
How do checkpoints work and what gets logged - By: Paul Randal


Job fails overnight intermittently because of a windows authentication failure to log on to SQL Server. How do I troubleshoot? The failed job is a Litespeed transaction log backup, but also happens to fulls and diffs occasionally.

This same setup works on other servers without issue, and it's only a few times overnight that the error occurs, but it does happen every night at some point:

Error in SQL Server Agent Log:

"Logging on to SQL Server 'SQLFOO' with Windows Authentication  TWideSafeCallErrorException - Timeout expired  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\slssqlmaint.exe"

Error in Litespeed log:

"Msg 49999, Level 19, State 1, Line 0 Failed to login to SQL Server".

Is there anything I can look for on the server that would give me a clue as to why there is an intermittent login error?

EDIT: More information - A scheduled query was discovered that runs across this time period for several hours, all the failures happen during this time period. The transaction log backups that do complete are taking 10 minutes compared to less than a minute to complete as they normally do.

EDIT2: No transaction log failures when the job isn't running. There is one update script that while running still causes the trxlog backup to fail with the logon authentication error. It's an update script that hits a ton of records, I guess it's time to put on my junior_dev hat.

There might be something useful in the SQL Server ERRORLOG file but probably not.  It is probably a network timeout between the SQL Server and the domain controller.