db_owner Member Can't Delete on Tables SQL server 2008 R2.

A user is part of an AD group. That AD group has a SQL account in this instance. That group has the following access to a Database:

db_datareader
db_datawriter
db_ddladmin
db_executor
db_owner
MSReplPal_7_1
public

When that user runs "Delete" on this database, it throws an error:

  The DELETE permission was denied on the object '', database '', schema 'dbo'.

User belongs to some other groups but none of those groups has permission to this DB in question.

This group does not have explicitly deny on the table in question.

What could the reason the user cannot delete on tables?

Also, I don't understand how this group has given this many access on this database. Doesn't having "db_owner" imply the group has maximum access to the database and other permission don't need to be added?? such as db_datareader, db_datawriter, etc.

Thanks for your time.

Unless the users is mapped to the sysadmin fixed server role (where they will connect to the database as the user dbo) then they can be limited by security on the database.  (i.e. A member of db_owner will receive a security check where dbo will not.)

A quick test to see if it is a permissions issue is to temporarily grant a user sysadmin role and then see if they can delete data....  if not then I'd start looking for constraints on tables.

If they can then run the following against the database (you may get more you may need to add DELETE to your where clause):

select * from sys.database_permissions
where state_desc = 'DENY'

If permissions are set it will give you something like:

class class_desc                                                   major_id    minor_id    grantee_principal_id grantor_principal_id type permission_name                                                                                state state_desc
----- ------------------------------------------------------------ ----------- ----------- -------------------- -------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- ----- ------------------------------------------------------------
0     DATABASE                                                     0           0           10                   1                    DL   DELETE                                                                                D     DENY
1     OBJECT_OR_COLUMN                                             1501607433  0           10                   1                    DL   DELETE                                                                                D     DENY

(2 row(s) affected)

If deny delete is set at the DATABASE level then well then there is your problem.  Adjust permissions so required users are not actually DENYed access.


Whitelisting access to a single table Is it possible to whitelist access to a specific table (for example containing password hashes plus salts)?

My ideal situation would be zero permissions for anyone with access to insert, update, delete and select achieved through stored procedures.

How do I go about setting this up?

Access to databases in SQL Server is managed mainly through two principals:

Logins - Security principal that allows access to the instance.
Users - Security principal linked to a login that allows access to a specific database.

By default, SQL Server follows the practice of least privilege, meaing that when you create these principals (further documentation on that here), they have minimal access to a database and the objects within that database.  Initially, these principals will only be able to connect to an instance or database but nothing else, such as selecting or modifying data.

This means that you, as an administrator, will need to GRANT permissions to principals that need access.  For example, if you had a table and you wanted to give a user the ability to read data on that table:

GRANT SELECT ON table_foo TO user_bar;

Once this is in place, your user can select from table_foo, but do nothing else.  You can further grant additional permissions to other users as necessary, or REVOKE/DENY permissions as well.  See here for further explanation of this.

Finally, you can leverage roles to additionally manage your security.  Roles are essentially containers that have permissions granted to them and you can assign users to these roles, much like Active Directory groups.  SQL Server has several server and database roles that are available by default and are commonly used, but you can also create custom database level roles and assign specific permissions to those roles.

Overall, database security is a large topic that is outside the scope of a StackExchange answer, but the basic idea is you can manage a "whitelist" by assigning specific permissions on your database objects and tightly controlling the security around those objects.


Import database from MSSQL export dump on Ubuntu localhost I have an export file from an online database server that I need to use to generate the database locally on my system. I'm running MSSQL 2017 on Ubuntu 16.04.

The export file has Program(application/octet-stream) in file properties dialogue box.

I want to use this file with SQLCMD command-line tool. Is it possible? If yes, how to do it? Please inform me if there's anything else I can specify before closing this question, if it seems vague or anything.

The problem was that the export dump file didn't have any extension and the SQLCMD couldn't recognize the file and didn't accept it.

I added the a .bak extension to the file and the SQL Server Management Tool restored the database from this modified file. It's still doesn't work on Ubuntu but that's a permissions problem. I'll maybe ask a separate question or look for it if it's already asked.


MySQL trigger - delete data from multiple tables I would like to delete data in multiple tables, from a trigger but it seem like my syntax is incorrect.

I got the error: "you've got a error in your MySQL syntax please check the manual".

This is the SQL for the trigger:

CREATE TRIGGER `delete_organisation`
BEFORE DELETE ON `organisations`
FOR EACH ROW
DELETE FROM contacts WHERE OLD.id = organisation_id ;
DELETE FROM orders WHERE OLD.id = orders.organisation_id;
DELETE from organisations_products where OLD.id = organisation_id ;
DELETE FROM organisations WHERE OLD.id = organisations.parent ;

Any idea what I'm doing wrong?

You're missing a BEGIN ... END, as follows:

DELIMITER $$

CREATE TRIGGER `delete_organisation`
BEFORE DELETE ON `organisations`
FOR EACH ROW
BEGIN
  DELETE FROM contacts WHERE OLD.id = organisation_id ;
  DELETE FROM orders WHERE OLD.id = orders.organisation_id;
  DELETE from organisations_products where OLD.id = organisation_id ;
  DELETE FROM organisations WHERE OLD.id = organisations.parent ;
END $$


SQL Server - Multiple running totals I've got a base table with transactions and I need to create a table with running totals. I need them to be per account and and also have a few running totals for each account (depending on the transaction type), and inside that, some running totals per sub-account.

My base table has these fields (more or less) :

AccountID  |  SubAccountID   |  TransactionType  |  TransactionAmount

Considering I've got about 4 types of running totals per Account/TransactionType and 2 more running totals per Account/SubAccount/TransactionType , and I've got about 2M accounts with about 10 sub accounts each, and I'm getting about 10K transactions every minute (at maximum load), how would you do it?

It's also a must that this run asynchronously via an SQL job, creating the aggregations without being part of the transactions themselves.

I'm pretty stuck using a cursor here - which takes way too long. I'd really appreciate any advice / articles that are doing more or less the same.

Asynchronous implies that the running totals do not need to be completely accurate at all times, or your data change patterns are such that a one-off running total build will be valid and accurate until the next load.  Anyway, I'm sure you have thought that part through, so I won't labour the point.

Your main options for a high-performance, supported, method are a SQLCLR function/procedure, or an UPDATE based on Hugo Kornelis' set-based iteration method.  The SQLCLR method (implemented in a procedure, but reasonably easy to translate) can be found here.

I haven't been able to find Hugo's method online, but it is detailed in the excellent MVP Deep Dives (Volume 1).  Sample code to illustrate Hugo's method (copied from one of my posts on another site you may not have a login for) is shown below:

-- A work table to hold the reformatted data, and
-- ultimately, the results
CREATE  TABLE #Work
    (
    Acct_No         VARCHAR(20) NOT NULL,
    MonthDate       DATETIME NOT NULL,
    MonthRate       DECIMAL(19,12) NOT NULL,
    Amount          DECIMAL(19,12) NOT NULL,
    InterestAmount  DECIMAL(19,12) NOT NULL,
    RunningTotal    DECIMAL(19,12) NOT NULL,
    RowRank         BIGINT NOT NULL
    );

-- Prepare the set-based iteration method
WITH    Accounts
AS      (
        -- Get a list of the account numbers
        SELECT  DISTINCT Acct_No
        FROM    #Refunds
        ),
        Rates
AS      (
        -- Apply all the accounts to all the rates
        SELECT  A.Acct_No,
                R.[Year],
                R.[Month],
                MonthRate = R.InterestRate / 12
        FROM    #InterestRates R
        CROSS
        JOIN    Accounts A
        ),
        BaseData
AS      (
        -- The basic data we need to work with
        SELECT  Acct_No = ISNULL(R.Acct_No,''),
                MonthDate = ISNULL(DATEADD(MONTH, R.[Month], DATEADD(YEAR, R.[year] - 1900, 0)), 0),
                R.MonthRate,
                Amount = ISNULL(RF.Amount,0),
                InterestAmount = ISNULL(RF.Amount,0) * R.MonthRate,
                RunningTotal = ISNULL(RF.Amount,0)
        FROM    Rates R
        LEFT
        JOIN    #Refunds RF
                ON  RF.Acct_No = R.Acct_No
                AND RF.[Year] = R.[Year]
                AND RF.[Month] = R.[Month]
        )
-- Basic data plus a rank id, numbering the rows by MonthDate, and resetting to 1 for each new Account
INSERT  #Work
        (Acct_No, MonthDate, MonthRate, Amount, InterestAmount, RunningTotal, RowRank)
SELECT  BD.Acct_No, BD.MonthDate, BD.MonthRate, BD.Amount, BD.InterestAmount, BD.RunningTotal,
        RowRank = RANK() OVER (PARTITION BY BD.Acct_No ORDER BY MonthDate)
FROM    BaseData BD;

-- An index to speed the next stage (different from that used with the Quirky Update method)
CREATE UNIQUE CLUSTERED INDEX nc1 ON #Work (RowRank, Acct_No);

-- Iteration variables
DECLARE @Rank       BIGINT,
        @RowCount   INTEGER;

-- Initialize
SELECT  @Rank = 1,
        @RowCount = 1;

-- This is the iteration bit, processes a rank id per iteration
-- The number of rows processed with each iteration is equal to the number of groups in the data
-- More groups --> greater efficiency
WHILE   (1 = 1)
BEGIN
        SET @Rank = @Rank + 1;

        -- Set-based update with running totals for the current rank id
        UPDATE  This
        SET     InterestAmount = (Previous.RunningTotal + This.Amount) * This.MonthRate,
                RunningTotal = Previous.RunningTotal + This.Amount + (Previous.RunningTotal + This.Amount) * This.MonthRate
        FROM    #Work This
        JOIN    #Work Previous
                ON  Previous.Acct_No = This.Acct_No
                AND Previous.RowRank = @Rank - 1
        WHERE   This.RowRank = @Rank;

        IF  (@@ROWCOUNT = 0) BREAK;
END;

-- Show the results in natural order
SELECT  *
FROM    #Work
ORDER   BY
        Acct_No, RowRank;

In SQL Server 2012, you could use the windowing function extensions e.g. SUM OVER (ORDER BY).


How to get back value passed to HASHBYTES()? I want to use the SQL Server HASHBYTES() function.

But I don't know how to get back the value passed to HASHBYTES...

This is not impossible but you should consider that the original data is lost and you cannot easily get the original data back from the hash value.

Once you get the hash H1 for a value V1, you only work with the hash value.
Ie. knowing hash value H1 for value V1, you can match V1 with a second value V2 by computing H2 using the same hash algorithm. When V1 = V2 then H1 = H2. (see collision below)

From Hash function (Wikipedia):

  A hash function is any function that can be used to map data of arbitrary size to data of fixed size. The values returned by a hash function are called hash values, hash codes, hash sums, or simply hashes. One use is a data structure called a hash table, widely used in computer software for rapid data lookup. Hash functions accelerate table or database lookup by detecting duplicated records in a large file. An example is finding similar stretches in DNA sequences. They are also useful in cryptography. A cryptographic hash function allows one to easily verify that some input data maps to a given hash value, but if the input data is unknown, it is deliberately difficult to reconstruct it (or equivalent alternatives) by knowing the stored hash value. This is used for assuring integrity of transmitted data, and is the building block for HMACs, which provide message authentication.

In theory, it may be possible to revert some mathematical functions used to compute some hashes. It is also possible to use dictionary and brute force with some tools.
But in most cases, it would be too difficult or it would take too long to get them back.

There is also no guarantee that you will get the same value back. It may also get a value with the same hash because hash are fixed length values and input data is of infinite length. See Hash Collision (Wikipedia):

  a collision or clash is a situation that occurs when two distinct pieces of data have the same hash value, checksum, fingerprint, or cryptographic digest


assign "all values" instead of "null" in Conditional Where clause I have to extract from a table based on user input. I have a table named Vehicle_Register with vehicle details and users are allowed to filter the data using vehicle type (car, bus, truck...). If no filer is selected all the data should be displayed.

Now for this I have to use two queries, 1 with filter and 2 for all:

select * from vehicle_register where vehicle_type = 'car';
select * from vehicle_register;

Can these two queries be merged? Just want to know how to use a conditional where clause using "case" or "if" statement but to assign vehicle_type = all types when no filter is selected.

You can do something like this :

SELECT *
FROM vehicle_register
WHERE (vehicle_type = @param OR @param is null)

You are using parameterized queries and not just a query build by string concatination in the frontend I hope.


How to make snapshot for new tables only If there is a replication running for a large set of data, and I want to add one or more new tables, I normally add the articles to the replication and start the snapshot agent.

Since there's a lot of data, this takes a while, interrupting users that do the normal operations on the database.

Is there a way to have the snapshot agent create the snapshot data only for these new (empty) tables?

There sure is. There are two settings on the publication that need to be set correctly to get the behavior that you want. allow_anonymous and immediate_sync must be set to false. You can change both with a call to sp_changepublication. Note that the tables needn't be empty for this to work.


One table or duplicate table with same properties but different dependencies Disclaimer: I'm new to database modeling so I apologize in advance if the question is unclear or causes any confusion.

I am using .NET Entity Framework and have the following tables:

An Order can have multiple Items, but Item itself is not existence dependent on Order. That said, should I have two separate tables for Item, e.g. InventoryItem and OrderItem? If not, how should I manage the relationship?

Any help is appreciated!

When you have a many to many relationship it's better to introduce a table which holds the relationship between the two tables which is much more normalized.


Match multiple components of a subquery CTE in PostgreSQL I've got an app that grants access to users in varying different ways, and I'm attempting to write a query that determines access in all the different ways, but is readable, by breaking the access out into CTEs.

This is the basic pattern I'm talking about:

WITH user_allowed_data1 AS (
    SELECT u.id user_id, c.id content_id FROM …
),
user_allowed_data2 AS (
    SELECT u.id user_id, c.id content_id FROM …
)
SELECT u.name, c.name FROM user_table
LEFT OUTER JOIN content_table ON 1=1
WHERE u.id, c.id in (SELECT user_id, content_id FROM user_allowed_data_1)
OR u.id, c.id in (SELECT user_id, content_id FROM user_allowed_data_2)

So, two questions:

Is there a better/faster/cleaner way to think about this query?
Is there a way to match multiple fields pulled out of a subquery in a where clause like that?

There are several ways to rewrite the query, even with keeping the CTEs. But please consider that CTEs in Postgres are pre-evaluated, so they might cause efficiency problems.

first, the LEFT join is equivalent to an inner JOIN, as you have a column from the right-side table in the following WHERE clause.
second, the FROM user_table LEFT OUTER JOIN content_table ON 1=1 is awkward. You could use the simple FROM user_table CROSS JOIN content_table. Not any performance gain, just more simple. I think I would prefer even the (frawned upon) FROM user_table, content_table from the LEFT ... ON 1=1.
after doing that, it's easy to see that FROM a CROSS JOIN b WHERE <x> can be replace with FROM a JOIN b ON <x>
the IN (SELECT user_id, content_id FROM user_allowed_data_1) can be replaced with the more elegant IN (TABLE user_allowed_data_1)

Now, to get rid of the OR, one way would be to combine the two ctes in one:

-- query 1 --
WITH user_allowed_data AS (
    SELECT u.id AS user_id, c.id AS content_id FROM …
  UNION DISTINCT
    SELECT u.id AS user_id, c.id AS content_id FROM …
)
SELECT u.name, c.name
FROM user_table AS u
  JOIN content_table AS c
    ON (u.id, c.id) IN (TABLE user_allowed_data) ;

But since we did that, we can just use joins in the main query and not the fancy ON ... IN (...):

-- query 2 --
WITH user_allowed_data AS (
    SELECT u.id AS user_id, c.id AS content_id FROM …
  UNION DISTINCT
    SELECT u.id AS user_id, c.id AS content_id FROM …
)
SELECT u.name, c.name
FROM user_table AS u
  JOIN user_allowed_data AS ad
    ON ad.user_id = u.id
  JOIN content_table AS c
    ON ad.content_id = c.id ;

Another way - to get rid of the OR - would be to use UNION in the main query instead:

-- query 3 --
WITH user_allowed_data1 AS (
    SELECT u.id user_id, c.id content_id FROM …
),
user_allowed_data2 AS (
    SELECT u.id user_id, c.id content_id FROM …
)
SELECT u.name, c.name
FROM user_table AS u
  JOIN user_allowed_data1 AS ad
    ON ad.user_id = u.id
  JOIN content_table AS c
    ON ad.content_id = c.id
UNION DISTINCT
SELECT u.name, c.name
FROM user_table AS u
  JOIN user_allowed_data2 AS ad
    ON ad.user_id = u.id
  JOIN content_table AS c
    ON ad.content_id = c.id ;

Any of the above of course can be written without ctes - which I stress again - can be a barrier to efficiency. So, your original query, slightly altered and without CTEs (keeping the OR):

-- query 1b --
SELECT u.name, c.name
FROM user_table AS u
  JOIN content_table AS c
    ON (u.id, c.id) IN (SELECT u.id AS user_id, c.id AS content_id FROM …)
    OR (u.id, c.id) IN (SELECT u.id AS user_id, c.id AS content_id FROM …) ;

The OR variants can also be expressed by replacing the IN with a correlated EXISTS subquery:

-- query 1c --
SELECT uu.name, cc.name
FROM user_table AS uu
  JOIN content_table AS cc
    ON EXISTS (SELECT u.id AS user_id, c.id AS content_id FROM …
               WHERE (u.id, c.id) = (uu.id, cc.id) )
    OR EXISTS (SELECT u.id AS user_id, c.id AS content_id FROM …
               WHERE (u.id, c.id) = (uu.id, cc.id) ) ;


SQL Server 2016 SP1 CU2 AlwaysOn AG Secondary always disconnected I'm trying to setup an AlwaysOn AG with T-SQL commands but my secondary is always in a disconnected state (when I failover, my old primary becomes disconnected).
This is not an firewall/network issue, I'm able to send messages from SQL1 to SQL2 with a TCP-sender/receiver.

The option New Availability Group... in SMSS also generates the same problem.
However when I'm creating an AG with New Availability Group Wizard... it works perfect.

This is the T-SQL statement I'm using:

--on primary
CREATE AVAILABILITY GROUP [AG_SQL]
FOR
DATABASE SetupAG
REPLICA ON 'sql1'
    WITH (ENDPOINT_URL = N'TCP://sql1.mydomain.com:5022',
        FAILOVER_MODE = AUTOMATIC,
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
     --   BACKUP_PRIORITY = 50,
      -- SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
        SEEDING_MODE = AUTOMATIC),
    N'sql2' WITH (ENDPOINT_URL = N'TCP://sql2.mydomain.com:5022',
        FAILOVER_MODE = AUTOMATIC,
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
     --   BACKUP_PRIORITY = 50,
       -- SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
        SEEDING_MODE = AUTOMATIC);
GO
--on secondary
ALTER AVAILABILITY GROUP [AG_SQL] JOIN
GO
ALTER AVAILABILITY GROUP [AG_SQL] GRANT CREATE ANY DATABASE
GO

This is the output from different DMV's:

dm_hadr_availability_group_states:

dm_hadr_availability_replica_states:

dm_hadr_database_replica_states

On the secondary dm_hadr_database_replica_states is empty and the database doesn't show up in sys.databases. When I look in SSMS the database is shown in 'Availability Databases'

Is this a bug in SQL Server 2016 SP1 Standard CU2 or is there something wrong with my T-SQL statement?

CREATE AVAILABILITY GROUP [AG_SQL]
FOR
--DATABASE SetupAG

If this is what you're using (copied from your question) then I would expect it to say disconnected. You've created an empty availability group (no databases, or database only on the primary) so there is nothing to send or receive other than some metadata about the AG.

The fact that it works when you choose automatic seeding means you're adding a database into the AG, it's now no longer empty and should show connected (if it truly is connected).

  On the secondary dm_hadr_database_replica_states is empty and the database doesn't show up in sys.databases. When I look in SSMS the database is shown in 'Availability Databases'

This is because there is no database on the secondary server, which completely falls inline with what you've posted. The "database" shows up under the Availability Groups folder on the secondary because that's the metadata of the Availability Group, not because the database is on the secondary.

The reason it "works" with automatic seeding is because automatic seeding creates the database and seeds it on the secondary for you versus what you're doing now which is creating the AG but not preparing the database on the secondary. Thus, no database on secondary.

  Is this a bug in SQL Server 2016 SP1 Standard CU2 or is there something wrong with my T-SQL statement?

Neither, it's a process issue where the secondary wasn't prepared properly.


LAST_INSERT_ID without AUTO_INCREMENT I have following table, which does have an UUID as primary key, which also acts as foreign key.

CREATE TABLE `xxx` (
    `guid` char(36) NOT NULL,
    ....
    PRIMARY KEY (`guid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT ;

CREATE TRIGGER before_insert_xxx
  BEFORE INSERT ON xxx
  FOR EACH ROW
  SET new.guid = uuid();

Every time when I insert a new dataset, I need to know which UUID was generated, so I can use this UUID for references. Alas, LAST_INSERT_ID() does not work.

INSERT INTO `xxx`(...) VALUES (...);
SELECT LAST_INSERT_ID();

I also tried to modify the trigger like this:

CREATE TRIGGER before_insert_xxx
  BEFORE INSERT ON xxx
  FOR EACH ROW
  SET new.guid = LAST_INSERT_ID(uuid());

But it doesn't help either, and other people have the same problem.

A few solutions I can think of, but I don't like them very much ...

Solution A:

Generate the UUID by the application which writes the data. Not a good solution, because:

it is hard to generate a real UUID (which fulfils all criteria of RFC 4122) in PHP.
Also, I want to ensure (directly in the DBMS) that people get an unique UUID.
With the trigger, I cannot use a custom UUID in the INSERT statement

Solution B:

Add a INT primary key and let GUID be UNIQUE only.
After inserting, receive the INT primary key with LAST_INSERT_ID
Run a SELECT statement, which searches the GUID for the given INT primary key.

Since I want to insert a lot of datasets in short time, having a SELECT after each INSERT would make the progress much slower, since the amount of queries doubles.

Is there an acceptable solution C?

What are the 'requirements' of your UUID?  Why isn't an AUTO_INCREMENT sufficient?  I believe the use (in C) of mysql_insert_id() does not incur an extra round-trip to the server, but rather provides the value from the previous INSERT.  See http://dev.mysql.com/doc/refman/5.6/en/mysql-insert-id.html .

If that does not suffice, consider

INSERT ... VALUES (..., @uuid = UUID(), ...);
SELECT @uuid; -- or simply use @uuid in whatever needs it next.

A note about UUIDs -- They are very "random".  That is, for tables/indexes too large to be cached, they incur a lot of I/O.  More discussion in my uuid blog.


How to get more information from dm_exec_query_stats I am running SQL server 2012 on windows azure VM (windows server 2012)

I suspected that some of my web appliactions are attacked via SQL injection,

So far i could not trace the relevant text with my app's logs or with SQL Profiler (found nothing), yet i have a high ammount of traffic so it was not easy to search it and the proccess might have been wrong.

Anyway, i used the following query :

select *
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
where text like '%the_sql_injected_text%'

And did find the following :

set ansi_warnings off
DECLARE @T VARCHAR(255),@C VARCHAR(255)

DECLARE Table_Cursor CURSOR FOR
select
    c.TABLE_NAME,c.COLUMN_NAME
from INFORMATION_SCHEMA.columns c,
INFORMATION_SCHEMA.tables t
where c.DATA_TYPE in ('ntext','text')
    and t.table_name=c.table_name
    and t.table_type='BASE TABLE'

OPEN Table_Cursor

FETCH NEXT FROM Table_Cursor
INTO @T,@C

WHILE(@@FETCH_STATUS=0)
BEGIN
    EXEC('UPDATE ['+@T+'] SET ['+@C+']=CONVERT(VARCHAR(8000),['+@C+'])+''</title>
Payday lenders and asking payday max loans be the amount you experience it.
'' ')

    FETCH NEXT FROM Table_Cursor
    INTO @T,@C
END

CLOSE Table_Cursor
DEALLOCATE Table_Cursor

I am intersted in knowing more about the source of the query, is there any more information I can get from it ? How should I go on from here ?

The information I am after may be an IP address, a user name or anything else that can help me find the source of this attack.

First, do your have columns of data type TEXT or NTEXT in your database? If

select
c.TABLE_NAME,c.COLUMN_NAME
from INFORMATION_SCHEMA.columns c,
INFORMATION_SCHEMA.tables t
where c.DATA_TYPE in ('ntext','text')
and t.table_name=c.table_name
and t.table_type='BASE TABLE'

returns any rows, you probably lost a lot of data during this attack, as the code truncates the existing values to 8000 characters. In that case you need to restore from a backup.

To answer your question, there is no direct way to get to that information from SQL Server. However, using the T-SQL code that was originally intended to be executed (the code that the above code was injected into), you should be able to identify the place in your app where the injection happened. From there you can look through the web site logs to find submissions to that particular page or form that took a long time or that submitted an unusual amount of characters. You can use the columns creation_time and last_execution_time in sys.dm_exec_query_stats to narrow down the time of the attack(s).

Important, once you have the place identified that allowed for the injection to happen, you need to close that gap immediately. You should also review the rest of the application to make sure that no other vulnerable spots lurk in your code. There is a little more information about what to look out for when injection-proofing your app here: http://sqlity.net/en/2547/sql-injection/


Size of Log Backup is not reducing from past three days Recently we implemented Incremental(Enabled allow incremental dumps) and Log Backup(Disabled trunc Log on chkpt) for Sybase ASE(15.7 SP139) running on HP-UX Itanium server and to our great surprise, Size of Log backup is increasing from past 3 days continuously. Even after Full/Cumulative backup, size of the Log file kept on increasing at disk and even size at database is not clearing.

Checked logsize at different interval by running command sp_helpsegment logsegment within database and noticed that used_pages is constantly increasing and free_pages is on decline.

We even checked dbcc gettrunc to get details however it was of no use. Below is size of files

We are using 2 stripe for database dump and compress 5 for compressing size on disk while taking dump.

Replication is running on this database and is working fine.

Appreciate if I can get expert advise on this issue.

Thank you markp, this was spot-on. There was a hanging transaction from past 3 days and that was the cause of this increment in log-size.

After investigation, we found out that there is flaw in application wherein application doesn't disconnect itself if user doesn't close them. We called user and once he closed his session, everything started smoothly and logisze came down from 9GB to 300MB.

I am posting this here as an answer, it could helps others in case they face similar issues. Thanks once again.


Postgres query performance - hash aggregate costly operation I'm running Postgres 9.6.6 and I have a relatively simple query, however, I'm finding that it is slow with the hash aggregation function which is the most costly operation:

    select
      program_schedule_source_count.full_name,
      program_schedule_source_count.country,
      sum(program_schedule_source_count.displays) as displays,
      program_schedule_source_count.source_id,
      program_schedule_source_count.source_region
    from program_schedule_source_count
    where program_schedule_source_count.original_title = 'How I Met Your Mother'
    and program_schedule_source_count.show_type in ('SM', 'SE')
    and program_schedule_source_count.start_date between '20200101' and '20200217'
    group by
      program_schedule_source_count.source_id,
      program_schedule_source_count.full_name,
      program_schedule_source_count.country,
      program_schedule_source_count.source_region;

Below is the query plan:

HashAggregate  (cost=1139676.26..1139725.25 rows=3919 width=46) (actual time=18769.670..18770.066 rows=736 loops=1)
  Group Key: source_id, full_name, country, source_region
  ->  Index Scan using title_date_show_type_country on program_schedule_source_count  (cost=0.70..1139186.45 rows=39185 width=46) (actual time=0.098..18733.005 rows=42654 loops=1)
        Index Cond: ((start_date >= '20200101'::bpchar) AND (start_date <= '20200217'::bpchar) AND ((original_title)::text = 'How I Met Your Mother'::text))
        Filter: (show_type = ANY ('{SM,SE}'::bpchar[]))
Planning time: 0.223 ms
Execution time: 18770.252 ms

The table has indexes on all of the fields in the where clause:

CREATE UNIQUE INDEX program_schedule_pkey ON public.program_schedule_source_count USING btree (source_id, start_date, program_id);
CREATE INDEX title_date_show_type_country ON public.program_schedule_source_count USING btree (start_date, original_title, release_year, show_type, country);

I have tried changing the order of the group by function but this did nothing to alter the performance.
I tried to disable hash aggregation to see if this would speed up the query but it still runs in roughly the same amount of time.
I assume adding an index to the group by fields would not have any benefit because I am not searching these fields.

I've seen that clustering could benefit but in the documentation it says you would cluster by an index does this mean I would have to create another index of the group by fields and then cluster the data by this?

Is there an alternative way I can write the query which could make it faster?

Thanks all for your help

Your time is spent in the index scan. Both of your indexes are not perfect for that query.

Try this one:

CREATE INDEX ON program_schedule_source_count (start_date, original_title);

If the third condition is always the same, you might put it into a WHERE clause:

CREATE INDEX ON program_schedule_source_count (start_date, original_title)
   WHERE show_type in ('SM', 'SE');


MongoDB covered query needs to fetch and examine documents when exists operator is used I need to execute following query on mongo db:
db.testCollection.find({ dataType: { $exists: true } }, { _id: 0, date: 1 })
   .sort({ date: -1 })
   .limit(1)

so basically only I need to do is to find newest date from documents that have dataType field.
I have ~20 milion documents in that collection and this query last something like 4-5 minutes.
I tried to cover all query needs by index, so I created index like this:
db.testCollection.ensureIndex(
   { dataType: 1, date: -1 },
   { partialFilterExpression: { dataType: { $exists: true } } }
)

As far as I know, this index contains all data the query needs:

information that all documents in this index contains dataType field exists
date field value needed to retreived it from query
date field value needed to sort operation

Unfortunately, mongo still have FETCH stage in query plan to check if dataType filed exists:
"winningPlan" : {
            "stage" : "PROJECTION_SIMPLE",
            "transformBy" : {
                "_id" : 0,
                "date" : 1
            },
            "inputStage" : {
                "stage" : "SORT",
                "sortPattern" : {
                    "date" : -1
                },
                "limitAmount" : 1,
                "inputStage" : {
                    "stage" : "SORT_KEY_GENERATOR",
                    "inputStage" : {
                        "stage" : "FETCH",
                        "filter" : {
                            "dataType" : {
                                "$exists" : true
                            }
                        },
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "dataType" : 1,
                                "date" : -1
                            },
                            "indexName" : "dataType_1_date_-1",
                            "isMultiKey" : false,
                            "multiKeyPaths" : {
                                "dataType" : [ ],
                                "date" : [ ]
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : true,
                            "indexVersion" : 2,
                            "direction" : "forward",
                            "indexBounds" : {
                                "dataType" : [
                                    "[MinKey, MaxKey]"
                                ],
                                "date" : [
                                    "[MaxKey, MinKey]"
                                ]
                            }
                        }
                    }
                }
            }
        }

So my question is why mongo still needs to fetch document from collections?
EDIT: When I changed $exists operator to $ne: null it doesn't need to execute FETCH stage.

I see similar results when tried on MongoDB v4.2.8 - using the posted info.
In general, the $exists in a query filter uses the index on that field. For example, if there is a field fld1 and you run a query with a filter { fld1: {$exists: true}}, the query uses the index.
In the query plan output, the in-memory sort ("stage" : "SORT") is because - for a compound index the filter must be an equality condition on the index prefix field (dataType) and then only the index will be used in a sort operation using the date field. The FETCH is for the SORT operation to happen in the memory (i.e., the index is not being used). So, the query is not covered.
For example, a query like this will use the index (and it will be covered):
db.test.find({ dataType: "a" }, { date: 1, _id: 0 }).sort({ date: -1 })

It is important to note that the query filter { dataType: "a" } uses an equality condition - and, {dataType: {$exists: true}} does not.
See Sort and Non-prefix Subset of an Index


Is it possible to exclude rows from a select based on conditions of another row My data set is quite simple;

Ref          Value      Voucher No
------------ ---------- ----------
M019922      -154.80    100081968
M019922A     154.80     100081968
M019922B     -154.80    100081991

These represent three lines in a sales ledger.  The ref without a suffix is the original transaction.  The ref with the A suffix is a reversal of the original transaction (it doesn't matter why, just that it was reversed).  The ref with suffix B is the new, correct transaction.

So I have three scenarios to cover, and for simplicity I would like to use just one query, but if that's not possible so be it

Scenario 1

There is only a single transaction with no suffix.  This means it's just an invoice we have processed, so I want to return the first row.

Scenario 2

There are two rows, one with no suffix and one with an A suffix.  In this case I am not interested in either row, as we have just reversed the transaction.

Scenario 3

As above, I have three rows.  In this case, I am only interested in the row with the B suffix.

There must be something I can do using the Voucher No, excluding the duplicates, bit I can't see what the answer is.

I don't think this is possible, but I am happy to be proved wrong.

Before you ask, I have interrogated the rest of the database to see if I can find a flag that tells me a transaction has been reversed out.  This is a very old system......(I have the original invoice from 1984)

Given your requirements, this should work for you, I think.  If you have another table with just the vouchers in it, that could help it along, as well as an index on the VoucherNo column.

DECLARE @Sample TABLE
    (
    Ref VARCHAR(10) NOT NULL
    , Amount DECIMAL(10,2) NOT NULL
    , VoucherNo INT NOT NULL
    )

INSERT INTO @Sample
(Ref, Amount, VoucherNo)
VALUES ('M019922', -154.80, 100081968)
    , ('M019922A', 154.80, 100081968)
    , ('M019922B', -154.80, 100081991)

;WITH CTE_Voucher AS
    (
    SELECT S.VoucherNo
        , SUM(COALESCE(S.Amount, CONVERT(DECIMAL(10,2), 0.0))) AS TotalAmount
    FROM @Sample AS S
    GROUP BY S.VoucherNo
    HAVING SUM(COALESCE(S.Amount, CONVERT(DECIMAL(10,2), 0.0))) <> CONVERT(DECIMAL(10,2), 0.0)
    )
SELECT C.VoucherNo
    , S.Ref
    , S.Amount
FROM CTE_Voucher AS C
    INNER JOIN @Sample AS S ON S.VoucherNo = C.VoucherNo


How can I prevent SSMS from re-writing my code when saving a view? I'm creating a view that uses a statement with a WHERE clause similar to this:

WHERE
    (
        col1 IS NOT NULL
        OR
        col2 IS NOT NULL
    )
    AND
    NOT EXISTS (SELECT ...)

which takes an average of 10 seconds to run.  However, when I try to save this query as a View, SQL Server (or the MS SQL Server Management Studio Client) "optimizes" the query to use this structure, instead:

WHERE
    (col1 IS NOT NULL AND NOT EXISTS (SELECT ...))
    OR
    (col2 IS NOT NULL AND NOT EXISTS (SELECT ...))

Slowing down the query to 6+ minutes.  Is there any way to disable this behavior, so that the view uses exactly the SQL Query I give it?

Don't use the SSMS wizards.

just type your CREATE VIEW dbo.Foo AS SELECT ... statement into a new query window and execute it.

This will stop it rearranging the query syntax to semantically equivalent but worse performing alternatives.


where is the index physically located in MySQL database Just wondering where is the index info physically located in MySQL database if I have indexed a table with ALTER TABLE contacts ADD KEY (columnName); ?

it will stay with that table and add a new spacial column to it;
stay separately from that table;

I think the #2 is the correct answer.

Can we index columns from more than one tables? how?

For the MyISAM engine, a table's indexes are stored in the .MYI file (in the data directory, along with the .MYD and .frm files for the table).

For InnoDB engine, the indexes are stored in the tablespace, along with the table. If innodb_file_per_table option is set, the indexes will be in the table's .ibd file along with the .frm file.

No, it's not possible to create an index that references more than one table. An index is specific to a table.


Declaring and using variables in triggers I have been testing something with triggers today, but I am stuck with it and I do not have any idea what is causing my problem.

DELIMITER $$

USE `pucko`$$

CREATE TRIGGER `after_delete_Alien`
AFTER DELETE ON `Alien` FOR EACH ROW BEGIN
    DECLARE vapenid INT;
    DECLARE alien_count INT;
    DECLARE rymdskepp_count INT;

    SET vapenid = (SELECT vapen FROM `Alien_Använder_Vapen` WHERE idAlien = OLD.idAlien);

    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = vapenid; // Debug, this shows that vapenid is null?

    DELETE FROM `Alien_Använder_Vapen` WHERE idAlien = OLD.idAlien;

    SET alien_count = (SELECT count(*) FROM `Alien_Använder_Vapen` WHERE vapen = vapenid);
    SET rymdskepp_count = (SELECT count(*) FROM `Rymdskepp_Använder_Vapen` WHERE vapen = vapenid);

    if(alien_count < 1 && rymdskepp_count < 1) THEN
        DELETE FROM `vapen` WHERE idVapen = vapenid;
    END IF;
END$$

The problem I am having is when vapenid is/should be set. I know that there is an alien with id "abc". I know that there is a vapen with id 1. In Alien_Använder_Vapen there is a row with alienId abc and vapen as 1. I use the SIGNAL to debug a bit what the value of vapenid is, and mySQL returns

  delete from Alien where idAlien = "abc"    Error Code: 1231. Variable 'MESSAGE_TEXT' can't be set to the value of 'NULL'

This makes me think that NULL is returned from the query in vapenid, but that doesn't make sense, as SELECT vapen FROMAlien_Använder_VapenWHERE idAlien = 'abc' in a normal query returns 1 correctly.

I've been trying to figure this out for quite some time now but I just can't see what I'm doing wrong. Is my syntax or logic wrong/weird?

As per my comment above, the chances are that you have a foreign key that is set to delete on cascade. This is what is causing your lookup to fail.

I hope this helps you.


SSAS How to calculate Standard Deviation I'm trying to calculate a variety of statistical calculation across a range of data such as Standard Deviation and Average.

The data comes from many different "Data sources". These are are held in a Dimension.

I tried using STDDEVP function as shown in this example which operates on a measure.

But the results were wrong. I assume it's wrong because it's working on totals rather than raw data. [Sum of Values] is a standard aggregate measure summing the data in the fact table.

Following the link supplied by Greg I am now using this:

CREATE MEMBER CURRENTCUBE.[Measures].[Standard Deviation]
 AS  (([Measures].[Sum of Values]^2 - (([Measures].[Sum of Values]^2)/[Measures].[Count of Values])/[Measures].[Count of Values])^0.5),
FORMAT_STRING = "#,##0.00;-#,##0.00", VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Fact TS Data';

Which gives me this:

Which still isn't correct.

Using SQL If I calculate STDDEV for Air Temp with the following query:

  SELECT stdevp([Value])
  FROM [EnvironmentalDataMart].[DDS_HILLTOP].[factTimeSeries]
  where [DETL_DataSourceSurrogateKey] = 78

I get: 6.13770334742149

How do to get the MDX query to calculate a STDDEV per Datasource in my Dimension?

I've also tried using a Measure with "No aggregation" but it didn't return anything.

The StDev function does operate at a rollup grain per the first parameter you pass in. If you always want to calculate the StDev at the lowest level then you can write some mathematically equivalent measures and avoid the StDev MDX function. This approach is described here. Let me know if that doesn't make sense. The MDX would be:

(([Measures].[Sum of Square] - (([Measures].[Sum of Values]^2)/[Measures].[Count of Values]))/[Measures].[Count of Values])^0.5

See Peter's answer for a full explanation of the steps he took.

Also, if you do stick with the MDX approach you are currently using you will want to fix the first parameter as follows. Your code is factoring the grand total into the calculation. This MDX does not:

STDDEVP( {[Data Source].[Data Source Name].[Data Source Name].MEMBERS},[Measures].[Total])


Access oracle database from control file When I asked a client for a copy of his database he sent me a .ctl file, it seems it is a control file of an Oracle database.
I have no experience in Oracle, I'm a SQL Server guy.
I have installed an Oracle XE 18 instance in a test server. What do I have to do to  connect to this database an access the tables? Is it possible to do so only having this .ctl file or do I need more files?

A control file is a metadata file and lists some system configuration settings and all the datafiles and their paths.  A typical file is quite small and does not contain any user data.

You need a datapump export or, even better, as suggested by Philᵀᴹ, an RMAN backup.  The RMAN backup is preferable as you will not have to create users and tablespaces first which you would with an export.


Should I revoke login from the default pgsql user? I set up a localhost pgsql installation (os x catalina, homebrew, 12.*). I am developing a sql data warehouse and I’d like my dev environment to be as close to the production one as possible. Homebrew created a default superuser, chris. Is it ok (and possible) to revoke the login attribute from this user? I have set up dedicated users to interact with the data warehouse and the actual data in the system.
Thanks!

You will need a superuser that can login for administrative purposes. So don't change the existing superuser to NOLOGIN unless you create another superuser first (but tgen you could just as well rename the existing superuser). The secure thing to do is

use a good password for the user

severely restrict access for that user in pg_hba.conf, ideally to local connections

There is one scenario where you could change the only existing superuser to NOLOGIN: create a regular user admin with NOINHERIT and
GRANT chris TO admin;

Then admin can always assume superuser privileges with
SET ROLE chris;

This setup has the advantage that you don't need a shared password for the superuser. Each administrator can have her own normal user that can assume superuser privileges when necessary.


Text string stored in SQLite Integer column? I'm a database novice looking at an SQLite database which appears to be storing text in an integer column. Here's an example session at the sqlite3 command line:

sqlite> .schema mytable
CREATE TABLE mytable (
    id              integer primary key,   /* 0 */
    mycol           integer not null,      /* 1 */
);

sqlite> SELECT mycol FROM mytable;
here is some text
here is more text
[...]
it's text all the way down

I'm confused. What gives?

This is a well known "quirk" of SQLite.

SQLite uses what it calls a dynamic typing system, which ultimately means that you can store text in integer fields - in Oracle, SQL Server and all the other big hitters in the database world, attempts to do this will fail - not with SQLite.

Take a look here:

  SQLite uses a more general dynamic type system. In SQLite, the
  datatype of a value is associated with the value itself, not with its
  container. The dynamic type system of SQLite is backwards compatible
  with the more common static type systems of other database engines in
  the sense that SQL statements that work on statically typed databases
  should work the same way in SQLite. However, the dynamic typing in
  SQLite allows it to do things which are not possible in traditional
  rigidly typed databases.

The advantages of this system are outlined here.

  Note: The datatype limitations can be cumbersome, especially if you
  add time durations, or dates, or things of that nature in SQL. SQLite has
  very few built-in functions for that sort of thing. However, SQLite
  does provide an easy way for you to make your own built-in functions
  for adding time durations and things of that nature,

I think the point of SQLite is a bit like the quote about the C programming language -

  "C allows you to do very stupid things because it also allows you to
  do very clever ones."

Same goes for SQLite.

Check out the entire StackOverflow thread referenced above.

The datatype in SQLite is more of a "hint" than a command.


mysqldbcopy from one server to other I am trying to copy one database schema from one server to other. I know how to do it in same server using mysqldbcopy but i dont know how to user it while copying from one server to other.

mysqldbcopy --source=root:''@localhost --destination=root:''@localhost Test_DB:Test_DB_Copy

This is how i copy in the same server. Please help me out in copying the schema to different server using msqldbcopy command.

mysqldump -h source.machine db_name  |  mysql -h target.machine

Add -u and -p as appropriate to both.

If you need to change the db name, add the suitable arg to mysqldump to avoid including DROP DATABASE and CREATE DATABASE; plus add the new db_name on the mysql command.

If you need to copy "routines", more args are needed.


SQL rounding last zeros I need to store the exact value of a decimal:

For example 0.00500 and 0.0050.

If I use decimal (10, 5), I receive 0.00500 every time. If I enter 0.0050, I still get 0.00500

There aren't any great answers here.

I don't know of any commercial database systems that store trailing zeros after the decimal, as these are considered insignificant to meaning.

However, in scientific measurement, those zeros can be significant, and knowing if something is 0.05 or 0.050000 is a major difference in precision of measurement.

One option is to store your number as a string/varchar value. This gets icky because now you have to do extra work to protect someone from saving a non-numeric value there.  That's more work for you, and more work for the database every time a value is stored. You can add a check constraint and use TRY_CONVERT() but in my opinion, this is trying to cram a numeric peg into a string hole, and that isn't quite right from a data model perspective.

A better option might be to simply store your measurement/value in a decimal column, then also store the precision in a separate integer column. When you insert your decimal value of 0.050000, you store 6 as your precision in a separate column. Then, when you consume the data later, your query will return a decimal value of 0.05, and precision 6 ... Which is exactly the data you need to know the original value is 0.050000. Here, there is still some extra work on maintaining your precision column, and it will need to be considered carefully on display (everywhere, including reporting), and in the case of maintaining scientific measurement you'll need to determine significant digits manually when performing math operations.


Can a Postgres LIKE statement return an exact match? I'm trying to search a Postgres table using LIKE and %, but the query is not returning results.

The column local_username will contains values formatted like: username@domain.local OR username@domain.

My query is searching this one column based on both the username and domain sections.

An example query is below:

SELECT *
FROM users
WHERE local_username LIKE 'username%'
  AND local_username LIKE '%domain%';

I'm concerned that, because there may be no characters after domain in the query, the second % is going to cause it to not return any results, since the % causes it to search for more characters, which in the case of the above query are not present.

Is there another keyword other than LIKE that I should be using? Or do I need to check each possibility (example below).

SELECT *
FROM users
WHERE local_username LIKE 'username%'
AND (local_username LIKE '%domain%' OR local_username LIKE '%domain');

% will match any sequence of characters, including none. So this is a match:

SELECT 'match' WHERE 'username@domain.com' like '%.com%';
-- Result: match

If you have the exact username and the exact domain you can use the following filter:

SELECT *
FROM users
WHERE
    local_username = 'username@domain' OR   -- Exact match
    local_username LIKE 'username@domain.%';    -- Exact match until dot

If you have partial filters, you will have to add a few more % (place depends your needs):

SELECT *
FROM users
WHERE
    local_username LIKE 'username%@domain' OR   -- Exact domain ending
    local_username LIKE 'username%@domain.%';   -- Exact domain ending until dot


How to list all postgres tables in one particular schema Using the psql command line tool, how do I list all postgres tables in one particular schema


archive_command failed causing master to run out of disk space. Is there a command that prioritizes master's health I had replication set up with a simple archive_command in postgresql.conf:

archive_command = 'rsync -a %p _postgresql@serverip:/var/postgresql/wal_archive/%f'

Which worked great for 2+ years until the replica went down.  After the replica went down, the archive command failed repeatedly so pg_xlog files on master were never deleted and master ran out of disk space.

In my use case the replica is nice to have but not that important.  However ensuring master is always available is very important, so I'd like to use an archive command that will sacrifice the replica before problems arise on master.

I currently went to the other extreme and changed the archive_command to:

archive_command = 'rsync -a %p _postgresql@serverip:/var/postgresql/wal_archive/%f || true'

But this doesn't seem ideal either since even a few seconds of network outage could cause replication to fail.

Ideally I'd like to change the archive_command to something that will try to copy the WAL file to the replica a certain number of times, and if that fails, give up and remove the pg_xlog file.  Any suggestions for a new archive_command?

(postgres 10.5)

I'd recommend a slightly different approach.  Instead of testing for number of failures because a large number might lead to low disk space, test for low disk space directly.

archive_command = 'rsync -a %p _postgresql@serverip:/var/postgresql/wal_archive/%f || (df -P /var/data/pg_wal|awk "NR==2 && \$4< 400000 {exit 0}; NR==2 {exit 1}")'

But be advised that rsync is a dangerous approach in the first place, because it doesn't guarantee a successful fsync of the file written to the remote side (and the directory that that file is in) before it returns success locally.

Or an entirely different approach: set your replica up as a streaming replica, rather than log shipping replica.  Then set "wal_keep_segments" to a value a little bit less than the amount of space reserved for pg_wal.  Note that this only works if the space is hard reserved for pg_wal, that is, no one can "borrow" some free space for other purposes, like temp files.

And of course, if your replica was down for a while and you didn't notice, then you probably need a monitoring solution as well.


Intentionally corrupting an index in PostgreSQL I need to know if there is any method for intentionally corrupting an index in PostgreSQL.

I have been searching for it for a long time but never seen anyone mentioning such an act.

I think @Craig's comments are more important, addressing the intentions behind your question. In any case, to answer the question asked:

One simple way (among many others) would be to fake an IMMUTABLE function and base an index on it like outlined in this answer from yesterday:

CREATE OR REPLACE FUNCTION f_fake_immutable_ts()
  RETURNS timestamp LANGUAGE sql COST 1 IMMUTABLE AS
$$SELECT timestamp '2015-07-25 01:00'$$;

Create a partial index based on it:

CREATE INDEX tbl_created_at_idx ON tbl (tbl_id)
WHERE  created_at > f_fake_immutable_ts();

The index relies on the function to always return the same output for the same input. Changing the function (to return a different timestamp), renders the index corrupt.

There is no way to identify this index as corrupt except by testing each entry.


Iterate through all non-system databases and empty them On SQL Server 2012...I have multiple databases on a server used for training purposes.  Periodically, these need to be cleared for a fresh start.  When quantities of databases were small I would use Adam Anderson's code to remove all objects and manually change the USE statement.  With 200+ databases now, I'd rather not.  Looking to automate this process.  I'm trying to use sp_MSforeachdb, without luck so far.  Any suggestions?  Thanks in advance!

DECLARE @command nvarchar(max)

SELECT @command ='IF EXISTS
(
    SELECT 1
    FROM sys.databases
    WHERE name = ''?''
    AND name LIKE ''learndb%''        --we have learndb1, learndb2, etc...
)
BEGIN

DECLARE @stmt nvarchar(max)
DECLARE @n char(1)
SET @n = char(10)

select @stmt = isnull( @stmt + @n, '''' ) +
    ''drop procedure ['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.procedures

select @stmt = isnull( @stmt + @n, '''' ) +
''alter table ['' + schema_name(schema_id) + ''].['' + object_name( parent_object_id ) + '']    drop constraint ['' + name + '']''
from sys.check_constraints

select @stmt = isnull( @stmt + @n, '''' ) +
    ''drop function ['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.objects
where type in ( ''FN'', ''IF'', ''TF'' )

select @stmt = isnull( @stmt + @n, '''' ) +
    ''drop view ['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.views

select @stmt = isnull( @stmt + @n, '''' ) +
    ''alter table ['' + schema_name(schema_id) + ''].['' + object_name( parent_object_id ) + ''] drop constraint ['' + name + '']''
from sys.foreign_keys

select @stmt = isnull( @stmt + @n, '''' ) +
    ''drop table ['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.tables

select @stmt = isnull( @stmt + @n, '''' ) +
    ''drop type ['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.types
where is_user_defined = 1

select @stmt = isnull( @stmt + @n, '''') +
    ''drop trigger ['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.objects
where type = ''TF'' exec sp_executesql @stmt END'

EXEC sp_MSforeachdb @command

Try using sp_MSforeachdb - Aaron's version.

That is more reliable than the undocumented sp_MSforeachdb.

Also, it would be more plausible if you can just recreate the databases from scratch (like Aaron Suggested above), make sure you have Instant File initialization - Enabled.


Key column 'r_Course_id' doesn't exist in table Error in my code I used the following syntax and keep getting the following error at the end.
" Key column 'r_Course_id' doesn't exist in table"

CREATE TABLE Faculty (
Faculty_id INT NOT NULL,
First_Name VARCHAR (20) NOT NULL,
Last_Name VARCHAR (20) NOT NULL,
EMAIL VARCHAR (30) NOT NULL,
Date_of_birth VARCHAR (30) NOT NULL,
Number_of_courses int,
PRIMARY KEY (Faculty_id)
);

CREATE TABLE Courses (

Course_id  INT NOT NULL,
Discipline VARCHAR (20)NOT NULL,
Course_number INT NOT NULL,
Credit DECIMAL(7,2)NOT NULL,
Year_offerd INT ,
Course_Title VARCHAR (50)NOT NULL,
PRIMARY KEY (Course_id )
);

CREATE TABLE FacultyCourses (
Course_id  INT NOT NULL
FOREIGN KEY (Course_id) REFERENCES Courses (Course_id),
FOREIGN KEY (Faculty_id)REFERENCES Faculty(Faculty_id),
F_C_ID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (F_C_ID)
);

the first two tables are created without a flaw, however the third is giving me trouble. please help.

You were missing a few things. There was no faculty_id column in the last table and you missed a comma  after course_id.

Try running the statements below (it works for me).

CREATE TABLE Faculty (
Faculty_id INT NOT NULL,
First_Name VARCHAR (20) NOT NULL,
Last_Name VARCHAR (20) NOT NULL,
EMAIL VARCHAR (30) NOT NULL,
Date_of_birth VARCHAR (30) NOT NULL,
Number_of_courses int, PRIMARY KEY (Faculty_id)
);

CREATE TABLE Courses (
Course_id INT NOT NULL,
Discipline VARCHAR (20)NOT NULL,
Course_number INT NOT NULL,
Credit DECIMAL(7,2)NOT NULL,
Year_offerd INT ,
Course_Title VARCHAR (50)NOT NULL,
PRIMARY KEY (Course_id ) );

CREATE TABLE FacultyCourses (
F_C_ID int NOT NULL AUTO_INCREMENT,
Course_id INT NOT NULL,
Faculty_id INT NOT NULL,
FOREIGN KEY (Course_id) REFERENCES Courses (Course_id),
FOREIGN KEY (Faculty_id) REFERENCES Faculty(Faculty_id),
PRIMARY KEY (F_C_ID) );


Compare Two Rows and Find the Difference Between Them Which is In The Second Column? I have a table that has the following data:

ID      Name       LastName
1      William     Johns
2      John        Adam
4      William     Smith
3      Mark        Stephan

I want to Find all those records that their Names are the same but there Last Names are different. so the result will be something like this:

ID      Name       LasName
1       William     Johns
2       William     Smith

What would be the MS. Access Query or method to find all these records?

You could rephrase it like this:

  Return every row for which another row exists with the same Name and a different LastName

which is very easy to translate to SQL if you are familiar with the EXISTS predicate:

SELECT
  *
FROM
  YourTable AS t1
WHERE
  EXISTS
  (
    SELECT
      *
    FROM
      YourTable AS t2
    WHERE
      t2.Name = t1.Name
      AND t2.LastName <> t1.LastName
  )
;


What's a more natural column name for Tables whose name share a column name? I have a database that's used by an ASP.NET MVC project through EntityFramework. Currently there are some tables that have columns with the same name, i.e.

CREATE TABLE HairColor(
    HairColorID int IDENTITY CONSTRAINT PK_HairColor PRIMARY KEY
    ,HairColor varchar(15) NOT NULL
)

but once EF goes through its thing it changes the HairColor property to HairColor1 because classes can not have properties which share its name.

So my question is, what would be better: HairColor.Name or HairColor.Color?

Personally, I usually like my column names to be meaningful, as it makes it easier to follow in queries. I can see Scotts Hodgin's point, certainly, but you could end up with a query that looks like:

SELECT cust.full_name
      ,stat.name
      ,type.name
      ,clhd.name
      ,src.name
....

Of course, good table names or aliases can ameliorate my complaint (even in my example, you can take a stab at what these things might be). However, (again, for me) having to use 20 different columns with the same name in a query can make it easier to use the wrong one in the wrong place, and can make it harder to catch. I'd rather see:

SELECT cust.full_name
      ,stat.status
      ,type.cust_type
      ,clhd.reason
      ,src.source
....

Again - I'm presenting my perspective - this is really something that is a matter of personal taste.

It is a good idea to ask a team to be consistent. If you're the team lead, you can either dictate (your preference wins), take a vote (if most people want one option, then compliance with that option should be better), or ignore it (leading to the best chance of confusion for all).


Comparing two queries in SQL Server 2012 I am comparing two queries in SQL Server 2012. The goal is to use all of the pertinent information available from the query optimizer when choosing the best query. Both queries produce the same results; the maximum orderid for all customers.

Clearing out the buffer pool was done before executing each query with FREEPROCCACHE and DROPCLEANBUFFERS

Using the information provided below, which query is the better choice?

-- Query 1 - return the maximum order id for a customer
SELECT orderid, custid
FROM Sales.Orders AS O1
WHERE orderid = (SELECT MAX(O2.orderid)
                 FROM Sales.Orders AS O2
                 WHERE O2.custid = O1.custid);

-- Query 2 - return the maximum order id for a customer
SELECT MAX(orderid), custid
FROM Sales.Orders AS O1
group by custid
order by custid

STATISTICS TIME

Query 1 STATISTICS TIME: CPU time = 0ms,  elapsed time = 24 ms

Query 2 STATISTICS TIME: CPU time = 0 ms,  elapsed time = 23 ms

STATISTICS IO

Query 1 STATISTICS IO: Table 'Orders'. Scan count 1, logical reads 5, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Query 2 STATISTICS IO: Table 'Orders'. Scan count 1, logical reads 4, physical reads 1, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execution Plans

SELECT properties Query 1

SELECT properties Query 2

Conclusions:

Query 1

Batch cost 48%
Logical Reads 5
Physical Reads 2
Read-ahead Reads: 0
CPU Time: 0ms
Elapsed Time 24ms
Estimated subtree cost: 0.0050276
CompileCPU: 2
CompileMemory: 384
CompileTime: 2

Query 2

Batch cost 52%
Logical Reads 4
Physcial Reads 1
Read-ahead Reads: 8
CPU Time 0
Elapsed Time 23ms
Estimated subtree cost: 0.0054782
CompileCPU: 0
CompileMemory: 192
CompileTime: 0

Personally, even though Query 2 has a higher batch cost according to the graphical plan, I think its more efficent that Query 1. This because query 2 requires less logical reads, has a slightly lower elapsed time, the compilecpu, compilememory and compiletime values are lower. read-ahead reads are 8 for query 2 and 0 for query 1.

Update 12:03

Clustered Index definition

ALTER TABLE [Sales].[Orders] ADD  CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
    [orderid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Non-Clustered Index idx_nc_custid

CREATE NONCLUSTERED INDEX [idx_nc_custid] ON [Sales].[Orders]
(
    [custid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

I love your approach to careful consideration to query tuning and reviewing options and plans. I wish more developers did this. One caution would be - always test with a lot of rows, looking at the logical reads, this is a smallish table. Try and generate a sample load and run the query again. One small issue - in your top query you are not asking for an order by, in your bottom query you are. You should compare and contrast them each with ordering.

I just quickly created a SalesOrders table with 200,000 sales orders in it - still not huge by any stretch of the imagination. And ran the queries with the ORDER BY in each. I also played with indexes a bit.

With no clustered index on OrderID, just a non-clustered index on CustID The second query outperformed. Especially with the order by included in each. There was twice as many reads on the first query than the second query, and the cost percentages were 67% / 33% between the queries.

With a clustered index on OrderID and a non-clustered index just on CustID They performed in a similar speed and the exact same number of reads.

So I would suggest you increase the number of rows and do some more testing. But my final analysis on your queries -

You may find them behaving more similarly than you realize when you increase the rows, so keep that caveat in mind and test that way.

If all you ever want to return is the maximum OrderID for each Customer, and you want to determine that by the OrderID being the greatest OrderID then the second query out of these two is the best way to go from my mindset - it is a bit simpler and while ever so slightly more expensive based on subtree cost it is a quicker and easier to decipher statement. If you intend on adding other columns into your result set someday? Then the first query allows you do to do that.

Updated:
One of your comments under your question was:

  Please keep in mind, that finding the best query in this question is a
  means of refining the techniques used for comparing them.

But best takeaway for doing that- test with more data - always makes sure you have data consistent with production and expected future production. Query plans start looking data when you give more rows to the tables, and try and keep the distribution what you'd expect in production. And pay attention to things like including Order By or not, here I don't think it makes a terrible bit of difference in the end, but still worth digging into.

Your approach of comparing this level of detail and data is a good one. Subtree costs are arbitrary and meaningless mostly, but still worth at least looking at for comparison between edits/changes or even between queries. Looking at the time statistics and the IO are quite important, as is looking at the plan for anything that feels out of place for the size of the data you are working with and what you are trying to do.


Shrinking sql databases - bad idea? I have a database, that due to some silly code issues has grown hugely. It is now sitting at 24GB, most of it is very unnecessary logging information (system generated debug information).

My server lives on a cloud hosted server. I am now sitting with 2 issues because of the file size:

I pay for storage. I am happy to pay for my core business, but paying for silly data seems....silly
My backups are now huge also. I do nightly full backups, and then ship them off to an FTP server. This process is taking longer and longer.

Given my issues, is shrinking so bad? I will rebuild all my indexes.
I did a dry run, and brought my database down to 6GB.

This guy (who I have always trusted as my SQL guru), says it's bad....mkay
"SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance"

Given my issues, is shrinking so bad? I will rebuild all my indexes. I did a dry run, and brought my database down to 6gb

Most of the advice given on the Internet is copied and is disseminated, while not reading the whole topic carefully. There is no doubt that shrinking of data files is bad, but if you ask any SQL guru he would always say, "yes I have shrunk the data file". He will also tell you the scenario that "forced" him to do so. Like if you have a huge database and you are archiving old records. So you moved data out and deleted the old data from the database. Now a lot of free space is there and you need it immediately; in this case you have no option but to shrink.

In your case you would have ended up paying a large amount for files which grew because of a silly code issue. In your case you can shrink it. Its also good that you are aware that shrinking causes fragmentation and you need to rebuild the fragmented indexes.

The whole motto is do not make shrinking a habit, it should always be your last resort to reclaim space

I suggest you read Paul Randal's Article on why shrinking is bad. He has pointed out in his article other way around to shrinking, which you can follow if you face similar issues later.


Compress PostgreSQL Database I have a large PostgreSQL database over 500GB in size which is too large. Is there anyway to compress the database down to a more manageable size? I have attempted to do this with SquashFS and the database compressed down to 177GB however PostgreSQL requires that the database have write access and Squashed systems are Read-Only. Do more experienced database users have any suggestions to accomplish this goal?

The database holds GIS data for the planet and will be used locally on a deployed system. Currently it sits on a 1TB SSD, however, I am trying to avoid slapping in an additional hard drive simply to accommodate a large database. The database performs as desired with no issue, I would simply like to compress it down to a more manageable size and avoid placing it on a separate drive.

File system

A very popular method of doing this is with the file system. BTRFS and ZFS works under the database at the file system level. Both can be used in a loopback device so you can provide a compressed tablespace without having another partition. There are caveats with this, if the tablespace fails it may take your cluster too.

ZFS

ZFS is the big one here. It's what I would go for.

See also

https://blog.2ndquadrant.com/pg-phriday-postgres-zfs/
https://blogs.oracle.com/observatory/zfs-compression-a-win-win (popular on Oracle too)

Btrfs

Btrfs is a strong contender but it's been in active development for a very long time, and the lack of major distros picking it up as a default has many people questioning whether or not it's ready for "prime time."

https://btrfs.wiki.kernel.org/index.php/Compression

PostgreSQL

GIS Methods (PostGIS)

For Polygons, one method is to simplify polygons by dropping vertices with ST_Simplify.
For Points, one method is spatial clustering.

Both of these result in a loss of information. PostGIS, like most of the features of the database, doesn't have a transparent "magic compression" option.

cstore_fdw

There is also cstore_fdw which is a columnar store that offers compression. It has a different performance profile so ymmv.


hosting images on own database or 3rd party I'm working on an app where the user can upload 1-6 pictures.

I've never handled images&databases before so I wanted to know if there is anything in particular I should be careful of.

Also, I've read that images are data intensive, and was wondering whether or not I should host them on the same database as my other data. Or if I should create an entirely different database for them.

thanks

The most common solution is, as commented by @Akina, to store images in the file system, and just store some kind of reference (file path or similar) in the database that allows you to uniquely identify the image in the file system.

However, there are also use cases where it makes sense to store the images directly in the database. Maybe you don't have permissions to store files on the file system. Maybe you have a lot of RAM and you don't expect the images to take up all that much space, and putting the files in the database means you only have to backup the database and not have to worry about a separate backup of the file system.

One of the big disadvantages of storing the images in the database is obviously that they can make your database grow to an unmaintainable size where backups take a long time and you may have to take special steps to avoid disrupting users (i.e. to avoid locking) for very long while running the backup. Maybe you can also no longer fit the whole database in RAM, which will lead to slowness. In addition, images usually require BLOB columns, and these come with their own set of disadvantages.


PostgreSQL transaction locked database table: "idle in transaction" I have a web application interacting with PostgreSQL (v8.4 & Centos Linux) which suddenly started locking some of the database's tables. Still have no idea what happened, since the code is not new, has been running several time and has been tested beforehand.

I am trying to see what transaction might have cause it, or if a combination of it with an autovacuum process could have caused it. But in the meantime I would like to unlock the database tables. I tried restarting the postgreSQL service, terminating thus the processes in "idle in transaction" state which were locking the tables but it didn't work: next time my application performed the same call the tables went locked again.

Any ideas on what can I do to unlock the database gracefully?

What you are seeing are transactions which are running, holding locks, and not doing anything.  What you need to do is trace the transactions back to their source and find out what they are doing.  They may be other admins who have logged in and forgot to log out.

In general it is usually a bad thing to hold transactions open and idle for a long time due to this sort of locking problem.


Joining based on date (or date range) I'm not sure if I've completely fudged the design of my small hobby database (I'm not a DBA by any means), but I have a table like this (primary key is (staffid, effectivefrom)):

 staffid | target | effectivefrom
 --------|--------|---------------
  1      |  6.0   | 2012-01-01
  2      |  6.0   | 2012-01-01
  3      |  6.0   | 2012-01-01
  1      |  7.0   | 2012-03-01

So basically, three staff all start out with a target of 6.0, but on March, staff with ID 1 has a new target of 7.0. I want to maintain historical targets because it is relevant to other data in other tables.

I would like to have a user-defined function that takes a date as a parameter, and this function needs to join the above table with another table based on the date. Say the function is called with 1st of February as the date, I would like the result of the join to include the target column showing 6.0 for all staff.

Something like this (I think this won't work because there could be multiple rows before dateParameter):

 SELECT othertable.*, targets.target
     FROM othertable
     JOIN targets ON
         othertable.staffid = targets.staffid AND
         targets.effectivedate <= dateParameter;

Please let me know if I have done an absolute DBA 'no-no' or whether I just need some caffeine.

You need to select the most recent record that's before or on your date of interest.  A query similar to the following would do this.

declare @date datetime
select @date = '2012-02-01'

select t.StaffID
      ,t.Target
  from Targets t
  join (select StaffID
              ,max (EffectiveFrom)    as EffectiveFrom
          from targets
         where EffectiveFrom <= @date  -- Most recent record before this date
         group by StaffID) ct
    on ct.StaffID = t.StaffID
   and ct.EffectiveFrom = t.EffectiveFrom

Unfortunately I don't have a running instance of PostgreSQL to hand, so this was on SQL Server, but the same basic structure should work on PostgreSQL.


How to display database structure in phpMyAdmin? Is there any way to generate a report of my database structure using phpMyAdmin?  What I would like is a table format like the following, and an Entity Relationship table.

Table name: List
Description: Subscriber list information

Field           Type        Null        Description
ListID          Int         N
ListName        Varchar     N
ListFromName    Varchar     N           Default person represent for the mail
ListReplyTo     Varchar     N           Default return address
ListSubject     Varchar     N           Default subject title of mail
ListRemindSub   Int         N           Whether notify when people subscribe
ListRemindUnSub Int         N           Whether notify when people unsubscribe
ListCreateDate  Date        N

Table name: Campaign
Description: Mail campaign information

Field           Type        Null        Description
CampID          Int         N
CampReplyTo     Varchar     N           Specific return mail
CampFromName    Varchar     N           The person represent for the mail
CampSubject     Varchar     N           Subject title of mail
CampFb          Int         N           Whether include Facebook reminder
CampGA          Int         N           Whether include Google Analytic

select DB, go to export, select tables that you want the structure (top left),deselect "DATA"(middle-center) and select a name and you will be able to export only the structure.

or go to your "database" then "check all" (just below the tables) and change the option from "With Selected:" to "print view".  it will show the relations too, and it will looks like this:


Postgresql delete query never ends I am having an issue which I can't resolve.

  delete from bb_gamelist_league

this operation is taking too long. There are 276224 rows in that column. Delete is performed without any WHERE conditions.

Here is the table:

CREATE TABLE bb_gamelist_league (
  id SERIAL  NOT NULL ,
  bb_league_id INTEGER   NOT NULL ,
  day_number INTEGER,
  date BIGINT   ,
  team_id1 INTEGER    ,
  team_id2 INTEGER    ,
  score1 SMALLINT    ,
  score2 SMALLINT    ,
  attended_people INTEGER    ,
  is_play_off BOOL    ,
  play_off_code VARCHAR(5),
  game_status BOOL    ,
  is_finished BOOL    ,
  was_taken_by_gameserv BOOL,
  taken_by_coordinator_status BOOL,
  seed TIMESTAMP,
  managerA_watching BOOL,
  managerB_watching BOOL,
  day_period VARCHAR(10),
  group_number VARCHAR(30),
PRIMARY KEY(id)  ,
  FOREIGN KEY(bb_league_id) REFERENCES bb_league(id),
  FOREIGN KEY (team_id1) REFERENCES bb_team_info(id),
  FOREIGN KEY (team_id2) REFERENCES bb_team_info(id));

CREATE INDEX bb_gamelist_league_FKIndex1 ON bb_gamelist_league (bb_league_id);

And there is no locks happening, the delete query is active and not waiting(information from pg_stat_activity).

I waited for one day but it didn't end. Also the CPU load is 100% by postgresql, though I'm having 2.24 Ghz CPU and memory is enough (there is free memory).

How can I understand the cause?

EDIT: there is an index for id column in that table (not listed in above create statement)

The cause is probably due to I/O.

Use "iostat -xm 2" to see I/O, or iotop to monitor performance, or check the "wa" (i/o wait) in top for a rough estimate.

To delete everything immediately, use truncate:

truncate bb_gamelist_league

this will run immediately, emptying your table with the added benefit of releasing the disk space (DELETE would not release it).

Be careful with constraints/references from other tables though. Check the manual for more info.


listener can not start My listener in Oracle 11g R2 can not start . When I want to give the status of that, the error in below is shown:

Connecting to <ADDRESS=<PROTOCOL=tcp><HODT=><PORT=1521>>
TNS-12541 : TNS:no listener
 TNS-12560 : TNS:protocol adapter error
  TNS-00511 : NO LISTENER
   64-bit Windowns Eroor : 61: Wnknown error

My tnsnames.ora is :

# tnsnames.ora Network Configuration File: D:\app\SHOJAIE\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = SHOJAIE-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

My listener.ora is :

# listener.ora Network Configuration File: D:\app\SHOJAIE\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\SHOJAIE\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\SHOJAIE\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = SHOJAIE-PC)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = D:\app\SHOJAIE

Can any one help me ?

Try to Restart Oracle Service by following Commands

net stop oracleserviceORCL;
net start oracleserviceORCL;

and then start listener first, if it is not already started by this command.

lsnrctl start listener;

and update me if it help...


How to avoid locks by large transactions? We have a large application running, that requires cleanup for performance reasons. This was however not forseen when designing the application.

Basically, the delete is executed from a stored procedure, which first does a couple of selects to define the data to be deleted, after which it starts deleting from different tables. As the link between this data is essential for the deletion, it has to be avoided to delete for example and order without deleting some dependencies. Therefore it has to run in one transaction.

Problem is, whenever the script is running, the application itself become not unusable: timeouts when getting data from the web, or trying to update a certain record. Al those queries are blocked by the sessions that runs the transaction

The data being deleted is not relevant anymore, and should thus not be updated by the application.

I've tried running the transaction in different isolation levels, including snapshot, but it still doesn't work.

How can I avoid these locks? Should I use READ_COMMITTED_SNAPSHOT?

Thanks in advance...

I don't think you have to force all of your deletes to occur within a single, monolithic transaction. Instead of having a transaction that does:

BEGIN TRANSACTION;
  DELETE all the things from child table 1;
  DELETE all the things from child table 2;
  ...
  DELETE all the things from child table N;
  DELETE all the things from parent table;
COMMIT TRANSACTION;

Why not delete in chunks? You can play around with the TOP (?) parameter based on how many rows lead to what kind of duration of transaction (there is no magic formula for this, even if we did have a lot more information about your schema). Pseudo-code:

DECLARE @p TABLE(p INT PRIMARY KEY);

SELECT @rc = 1;

WHILE @rc > 0
BEGIN
  DELETE @p;

  INSERT @p SELECT TOP (?) primary_key FROM parent table
    WHERE (clause that defines the data to be deleted);

  SET @rc = @@ROWCOUNT;

  BEGIN TRANSACTION;
    DELETE child table 1 WHERE parentID IN (SELECT p FROM @p);
    DELETE child table 2 WHERE parentID IN (SELECT p FROM @p);
    ...
    DELETE child table N WHERE parentID IN (SELECT p FROM @p);

    DELETE parent table WHERE parentID IN (SELECT p FROM @p);
  COMMIT TRANSACTION;
  -- to minimize log impact you may want to CHECKPOINT
  -- or backup the log here, every loop or every N loops
END

This may extend the total amount of time that the operation takes (especially if you backup or checkpoint on each loop, or add an artificial delay using WAITFOR, or both), but should allow other transactions to sneak in between chunks, waiting for shorter transactions instead of the whole process.

I wrote a lot more about this technique here.


how to select rows where for a column there are two rows one with null and another with non-null value then select only row with non-null value Column A      Column B

117220177     BOOK9488
117220177     NULL
14061         NULL

Oracle query for selecting rows:

When Column B has two rows corresponding to column A then pick row with non-null value in Column B. When there is only one row then pick irrespective of value in Column B

Expected Output:

Column A      Column B

117220177     BOOK9488
14061         NULL

SELECT Column_A, MAX(Column_B) Column_B
FROM test
GROUP BY Column_A;

fiddle


Data Change Audit Plan My company is changing from a distributed Access Database model to using a centralized SQL Database.  The datatables were designed such that all of the tables have a modified date.  In discussion it was suggested that since we will be creating a trigger on each table to handle the modified date perhaps we should have the trigger also log some information to an audit table.

Is this the best way to setup auditing so that we can track who is changing information or is there a better way?  Links to articles on the subject are welcome.

For the audit I'm looking at capturing the table name, column name, date modified, row id and the username of the person making the change.  Is there any information I'm not thinking of that I should be capturing that might help me avoid future pit falls?

Don't bother tracking audit data on a per-field basis.  It's much easier to have an audit table with the same structure as the main table and timestamp and user information tracked on the table.

This architecture has a few advantages:

You can easily create a view across the main table and audit table to show a complete history includint current versions.
It is relatively simple to implement the audit triggers - in fact if you hae a large number of tables you can write a utility to generate them from the system data dictionary.
The audit tables can be put on a separate disk to reduce the I/O load on the disk with the main tables.
It's very easy to reconstruct an as-at version of the record, rather than having to apply a list of field-level changes in reverse.

Audit information should include at least: user who created/changed the record, date/time of the creation/change, nature of change (insert, update, delete).  You may want to use logical deletion (i.e. a 'Deleted' flag) if you have the option of doing so.  Otherwise you need to capture the user and date/time from the session and put these on the deletion record, probably along with the last state of the record.  If this is not available from the connection (often the case on N-tier apps where the user is not being impersonated at the database level) then you need to find some other way to get it.


Query optimizer recommends adding index instead of using existing index I am trying to determine why the query optimizer in SQL Server is recommending creating a new index instead of using an existing index that appears to be sufficient for the query.

First the table. Column names changed to protect the innocent :-)

CREATE TABLE [myTable] (
  [id] [int] IDENTITY(1,1) NOT NULL,
  [serialNumber] [varchar](12) NOT NULL,
  [sName] [varchar](64) NOT NULL,
  [meanValue] [int] NOT NULL,
  [range] [int] NOT NULL,
  [modifiedDate] [datetime] NOT NULL,
  CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED ( [id] ASC )
)

Create the index in question:

CREATE NONCLUSTERED INDEX [IDX_myIndex]
ON [myTable] ([serialNumber], [sName], [meanValue], [range])
INCLUDE ([modifiedDate])

Add data for testing using your generator of choice ;-) Run the following query (table only has a few million records)

SELECT TOP 1000
  [serialNumber],
  [sName],
  [meanValue],
  [range],
  [modifiedDate]
FROM [myTable]
WHERE [serialNumber] = 137802
AND [sName] = 'A Name'

The query optimizer recommends using a new index where the additional where clauses are covered in the INCLUDE instead of part of the key:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[myTable] ([sName])
INCLUDE ([serialNumber],[meanValue],[range],[modifiedDate])

I was under the impression that a broader index that encompasses more columns will be used as the index as long as the order of the WHERE clauses represents the order of the columns indexed.

If I also WHERE on the modifiedData the index gets used and the query optimizer doesn't complain:

SELECT TOP 1000
  [serialNumber],
  [sName],
  [meanValue],
  [range],
  [modifiedDate]
FROM [myTable]
WHERE [serialNumber] = 137802
AND [sName] = 'A Name'
AND ([modifiedDate] >= '2000-04-25' AND [modifiedDate] < '2019-04-30')

The DBA link
SQL Server 2008R2 - Why is my index not used suggests a closer correlation between the index key and includes with the SELECT statement helps determine index use (but in my example they are basically the same). I have lots of rows, which probably satisfies the row use probability test, and there are no NULLs - thus negating the NULL effect of the index.

I thought, maybe incorrectly, that an index A, B, C, D would cover an query where A, B, C, or A, B, or A would be run. Is this assumption wrong? I realize there could be edge conditions that throw this basic concept off-kilter, but at a fundamental level, isn't this roughly how it's supposed to work?

Thanks in advance for any help, pointing out the stupidity in my ways, recognizing I need to go (back) to DB school etc... :-)

Your index is seemingly fine and good (i.e. covering) for the query and it should be used. The real problem is the query itself and specifically this condition which hides an implicit conversion:

WHERE [serialNumber] = 137802

According to SQL Server's datatype precedence, when two values of different datatypes are compared, the value with the datatype of lower precedence is converted to the datatype of the higher precedence. Unfortunately, int is higher in the list than varchar. This blows up any hope of using the index as the column (serialNumber) values are converted to integers. The column being the 1st position of the index, leads the optimizer to not use that index and search for an alternative (and thus the suggestion.)

Solution is not to have any implicit or explicit conversions of columns in WHERE condition. Simply use:

WHERE [serialNumber] = '137802'


Stored proc getting ANSI_NULLS and ANSI_WARNINGS error when executed by application I have a stored procedure that is getting the following error when executed via an application :

  Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

This stored procedure has a SELECT statement in it that joins to a linked server table to return results. If you execute this stored procedure directly in SMS, there is no problem whatsoever. If I execute the stored procedure via a script through an ODBC connection, then there is no problem. The only way to generate the error is when the application tries to execute it.

When viewing the application, there are no errors. In fact, it looks like it runs successfully, except there are no results. Only way we were able to identify this error is through running a SQL Trace.

The trace shows that none of the proc is ever actually running, instead it is just erroring immediately with that error. Trace also shows that it is not executing in a different way (such as exec sp_executesql or something like that). I can see the stored proc start executing, then get the error, and then stop.

I have researched this a ton and the answers seem simple enough, but nothing has worked.

I tried adding SET ANSI_NULLS ON & SET ANSI_WARNINGS ON to the stored proc, even moving it around different places in it. But never able to get it to succeed

Also, tried adjusting the database level settings -

ALTER DATABASE [NGDevl] SET ANSI_NULL_DEFAULT OFF WITH NO_WAIT
GO
ALTER DATABASE [NGDevl] SET ANSI_NULLS OFF WITH NO_WAIT
GO
ALTER DATABASE [NGDevl] SET ANSI_WARNINGS OFF WITH NO_WAIT
GO

But did not work, even after rebooting the SQL Instance.

The only other thought I had would be to have the application execute the proc with the settings part of that, so like ---

SET ANSI_NULLS ON;SET ANSI_WARNINGS ON;exec sp_storedproc 0,0,'A','0001'

But unfortunately, we dont have control over the application to try that.

Also, recreated the stored procedure so that it pulled all of the data needed from the linked server over into a temp table so it was local, and then joined to there, but same issue.

Do you guys have any ideas or something to try here. The error seems like it should be something pretty simple to fix, just not sure what I am missing.

The issue is specific to ANSI_WARNINGS as the value of ANSI_NULLS is stored with the definition of the Stored Procedure when it is created. Just run:

SELECT * FROM sys.sql_modules;

To see the difference between ANSI_NULLS and ANSI_WARNINGS, run the following:

-- Check current values (SSMS defaults to ON for both unless changed in
-- Tools -> Options -> Query Execution -> SQL Server -> ANSI
SELECT SESSIONPROPERTY('ANSI_NULLS') AS [AnsiNulls],
       SESSIONPROPERTY('ANSI_WARNINGS') AS [AnsiWarnings];
-- returns: 1   1

SET ANSI_NULLS OFF;
SET ANSI_WARNINGS OFF;

SELECT SESSIONPROPERTY(N'ANSI_NULLS') AS [AnsiNulls],
       SESSIONPROPERTY(N'ANSI_WARNINGS') AS [AnsiWarnings];
-- returns: 0   0

EXEC('
CREATE PROCEDURE ##SessionSettings
AS
SELECT SESSIONPROPERTY(N''ANSI_NULLS'') AS [AnsiNulls],
       SESSIONPROPERTY(N''ANSI_WARNINGS'') AS [AnsiWarnings];
');

EXEC ##SessionSettings;
-- returns: 0   0   (as expected)

SET ANSI_NULLS ON;

SELECT SESSIONPROPERTY(N'ANSI_NULLS') AS [AnsiNulls],
       SESSIONPROPERTY(N'ANSI_WARNINGS') AS [AnsiWarnings];
-- returns: 1   0   (ANSI_NULLS is back ON for the session)

EXEC ##SessionSettings;
-- returns: 0   0   (ANSI_NULLS is still OFF due to being saved with the proc definition)

SET ANSI_WARNINGS ON;

SELECT SESSIONPROPERTY(N'ANSI_NULLS') AS [AnsiNulls],
       SESSIONPROPERTY(N'ANSI_WARNINGS') AS [AnsiWarnings];
-- returns: 1   1   (ANSI_WARNINGS is back ON for the session)

EXEC ##SessionSettings;
-- returns: 0   1   (ANSI_WARNINGS is now ON due to NOT being saved with the proc definition)

Putting the SET command inside of the stored procedure is too late; it needs to be set before the EXEC of the proc. So you were on the right track with wanting to do:

SET ANSI_WARNINGS ON;exec sp_storedproc 0,0,'A','0001'

However, you cannot change the app code and how it calls the proc. BUT, you can change the proc itself. Hence, you can rename the current proc to sp_storedprocReal and create a new stored procedure named sp_storedproc that is just a wrapper that does the following:

CREATE PROCEDURE sp_storedproc
(
  @Param1 INT,
  @Param2 INT,
  @Param3 VARCHAR(50),
  @Param4 VARCHAR(50)
)
AS
SET ANSI_WARNINGS ON;

EXEC sp_storedprocReal @Param1, @Param2, @Param3, @Param4;

I have tested the above wrapper proc after reproducing the stated error and it does resolve the issue.