texts:
Mysql server crashed on my ec2 server, Sometimes it works without problems and sometimes it crashes suddenly, which causes bugs in my project.
My Server Details

Ubuntu 20.04.1 LTS
1 GB Ram

This is mysql error log:-
2020-10-12T16:06:15.527729Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21-0ubuntu0.20.04.4) starting as process 64634
2020-10-12T16:06:15.550683Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-10-12T16:06:16.347250Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-10-12T16:06:16.700702Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2020-10-12T16:06:16.741103Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2020-10-12T16:06:16.754582Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2020-10-12T16:06:16.826186Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-10-12T16:06:16.826443Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-10-12T16:06:16.878163Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.21-0ubuntu0.20.04.4'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu).


texts: SQL Server weird issue with indexes and query planner

texts:
We had a bunch of crashes and restorations a few days ago and following those, the SQL Server DB has been acting strangely. We know that there were some issues with the failover clusters due to which we've had to boot the servers again to finally get the database seemingly working.

One of the problems following that is that, we ran a big script that dynamically dropped existing indexes and re-created them with the only difference being that they were now filtered with WHERE column NOT NULL. For some reason however, when I pick the SCRIPT INDEX -> CREATE TO -> NEW QUERY WINDOW from SSMS Object Explorer, it offers the basic index creation script where the index is NOT filtered. When our customer with full permissions does the same, the creation script correctly shows that it's filtered.

Could this be a permission issue (didn't find any such on Google), or is there a possibility that while the script logged the changes correctly, the nodes or the query optimizer or whatever is somehow out of sync?

Similarly, several queries that previously performed fine (and still perform fine in a different DB that's a copy of this one), now show through execution plan that they're behaving differently. One of them has for example the following issues:

By default, execution planner shows that SQL Server uses the wrong index, producing millions of rows in the nested loops as opposed to 2 as it should.
Another index is correct, but produces millions of nested loops where in the copy of this server we only get 3.
When the copy is forced to use the same wrong index as the problem DB in section 1, it still only returns 2 rows in nested loops.

What could possibly be the cause of these issues and how to even begin to diagnose the problem? As said, the databases are copies of each other. The only difference is that the problem DB crashed where the DB was moved to a failover cluster and was then returned to the correct node again. The indexes are not fragmented, the statistics have just been updated, and there seems to be an extreme load from SQL Server's query planner.

I would really appreciate some expert advice on what could be the cause and how I would go about to diagnose the actual problem, thanks.


texts: SQL query or method to list all sql services running

texts:
This might be a simple one, but i searched a lot and unable to find one.

Is there a sql query or a way i can run on registered server to find the list of all sql related services running like, ssis , ssas, ssrs, sql, agent, broswer, etc...

Tried googling out and can find sys.dm_server_services dmv but it does not show all the services running


texts: join to other table multiplied by loop foreign key in mysql

texts:
i have 2 table one node and relation and Second store buy each node. my code work fine but when sum multiple buy for each node result multiplied by number of purchases .

table nodes

+-------------+---------------+-------------+
| ancestor_id | descendant_id | path_length |
+-------------+---------------+-------------+
|           1 |             1 |           0 |
|           1 |             2 |           1 |
|           1 |             3 |           2 |
|           1 |             4 |           1 |
|           1 |             5 |           2 |
|           1 |             6 |           3 |
|           1 |             7 |           4 |
|           2 |             2 |           0 |
|           2 |             3 |           1 |
|           2 |             5 |           1 |
|           2 |             6 |           2 |
|           2 |             7 |           3 |
|           3 |             3 |           0 |
|           4 |             4 |           0 |
|           5 |             5 |           0 |
|           5 |             6 |           1 |
|           5 |             7 |           2 |
|           6 |             6 |           0 |
|           6 |             7 |           1 |
|           7 |             7 |           0 |
+-------------+---------------+-------------+

table buy

 +-------------+---------------+-------------+
   |      userid |        amount |
   +-------------+---------------+-------------+
   |           2 |          1500 |
   |           7 |          2000 |
   +-------------+---------------+-------------+

mysql code

SELECT

    DISTINCT users.descendant_id  ,
    SUM(CASE WHEN ances.ancestor_id = buys_ances.userid THEN 1 ELSE 0 END) level_compress

    FROM webineh_prefix_nodes_paths as users

    join webineh_user_buys as buys on (users.descendant_id = buys.userid  )

    join webineh_prefix_nodes_paths as ances on (users.descendant_id = ances.descendant_id )

    join webineh_user_buys as buys_ances on (buys_ances.userid = ances.ancestor_id )

    WHERE  users.ancestor_id = 1

     and
    (SELECT SUM(g2.amount) as amount FROM webineh_user_buys g2 where  g2.userid = ances.ancestor_id     group by  g2.userid ) >= 1000

     and
    (SELECT SUM(g1.amount) as amount FROM webineh_user_buys g1 where  g1.userid = users.descendant_id   group by  g1.userid ) >= 1000

group by buys.userid ,ances.ancestor_id

result when in curent purchases data

users.descendant_id   |  users.ancestor_id   |      level_compress

2                     |                      |         1
6                     |                      |         2

i need to show ancestor_id compressed id


texts: Where's my missing Sql Server Express 2005 Database space? Size and space available dont add up to 4G?

texts:
I recently deleted some data from a table that was not needed and growing to too big a size with this query which provided to me by a support tech...

 declare @mdate datetime
 set @mdate='04/04/2014'
 delete from CacheDiffResults where CacheID in (select CacheID from ComparisonCache
 where TimeStamp < @mdate)
 delete from ComparisonCache where TimeStamp < @mdate
 delete from CacheDiffResults where CacheID not in (select CacheID from ComparisonCache)

so it deleted all data before 04/04/2014. After I ran this query I saw that I gained back about a Gig of space from this table, then I ran a shrink on the database.

When I looked at the properties of the database the figures didn't add up? I'm no DBA (Networks) but I don't understand why the DB size and space left figures add up to 4G which is the size of SQL express 2005 DB?

Have a look at these screen shots...

As you can see 2569.73MB + 43.96MB != 4G

And what are these figures in the bottom picture? These don't add up to 4G either? Confused. Any help appreciated. TIA!


texts:
Need help troubleshooting pgpool issue and reset the status.

The “show pool_nodes” shows node 1 is down:

postgres@node-0:~$ psql -p 9999 -c "show pool_nodes"

 node_id |        hostname        | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | last_status_change
---------+------------------------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------

 0       |                 node-0 | 5432 | up     | 0.333333  | primary | 245104     | true              | 0                 | 2018-12-03 11:58:49

 1       |                 node-1 | 5432 | down   | 0.666667  | standby | 198341     | false             | 0                 | 2019-01-30 09:02:54

(2 rows)

I can access both node 0 and node 1 from pgpool:

postgres@node-0:~$ psql -h node-0
psql (10.6 (Ubuntu 10.6-1.pgdg16.04+1))
Type "help" for help.
postgres=# \q

postgres@node-0:~$ psql -h node-1
psql (10.6 (Ubuntu 10.6-1.pgdg16.04+1))
Type "help" for help.
postgres=# \q

From the syslog, I found the following event happened:

  Jan 30 09:02:54 node-0 pgpool[7565]: 2019-01-30 09:02:54: pid 13516:
  DETAIL:  postmaster on DB node 1 was shutdown by administrative
  command

  Jan 30 09:02:54 node-0 pgpool[7565]: 2019-01-30 09:02:54: pid 13516:
  LOG:  received degenerate backend request for node_id: 1 from pid
  [13516]

  Jan 30 09:02:54 node-0 pgpool[7565]: 2019-01-30 09:02:54: pid 7565:
  LOG:  Pgpool-II parent process has received failover request

  Jan 30 09:02:54 node-0 pgpool[7565]: 2019-01-30 09:02:54: pid 7565:
  LOG:  starting degeneration. shutdown host node-1(5432)

  Jan 30 09:02:54 node-0 pgpool[7565]: 2019-01-30 09:02:54: pid 7565:
  LOG:  Do not restart children because we are switching over node id 1
  host: node-1 port: 5432 and we are in streaming replication mode

  Jan 30 09:02:54 node-0 pgpool[7565]: 2019-01-30 09:02:54: pid 7565:
  LOG:  child pid 13516 needs to restart because pool 1 uses backend 1
  Jan 30 09:02:54 node-0 pgpool[7565]: 2019-01-30 09:02:54: pid 7565:
  LOG:  failover: set new primary node: 0

  Jan 30 09:02:54 node-0 pgpool[7565]: 2019-01-30 09:02:54: pid 7565:
  LOG:  failover: set new master node: 0


texts: How do I check my sp_Blitz/sp_WhoIsActive versions across multiple servers/databases?

texts:
I have a bunch of SQL Servers registered within my SSMS, lots of different SQL versions, editions and DB compatibilities.

I want to check out if any of those servers have any of the sp_Blitz or sp_WhoIsActive stored procedures installed, and which versions they are.

I know I can right-click on my Registered Servers group and open one query window that connects to all the servers in the group, but is there a reliable way to check which SProcs and versions I have?


texts: How to setup SQL Server 2014 replication for a demo site

texts:
Background: We have been looking at replication of our SQL Server 2014 databases for a couple of scenarios.One which just works and its hunky dory and the other which just doesn't seem to work, moreover I, after trawling through loads of unhelpful Microsoft technet pages, feel that the only way I will get an answer is to ask somebody who has done this before: - Hence why I have turned to stack overflow.

We have two servers, our main DB and a second DB both running SQL Server 2014 (12.0.4100.1), on Windows Server 2012. These machines are in an Active Directory and can see each other fine.

Scenario 1: We have successfully set up transactional replication from our main database to a reports database. This means our reports application can read this data from the replicated machine and its read only. This works fine. The ID columns are all present and are identical to the ID column on the publisher.

Scenario 2: We have a demo-*. set of sites where we want yesterday's data, and also we want to be able to play with the data, i.e. adding new rows in, deleting rows.. in essence demonstrating, but based on realistic real data... our customers love it when we demo using their real data but in a safe environment.

This helps us with learning and demonstration. We set-up a snapshot replication and the first time we did this it worked fine, however subsequent updates to this are removing or are not publishing the identity property on the ID column (some ID columns are being replicated with the IDENTITY property intact but for some tables the IDENTITY property is being removed). This leaves some tables (not all) in a state where I cannot insert a new row.

This is not an issue with identity ranges, as these are being re-seeded with a new range that will not conflict, the issue is that the IDENTITY property is not present on some of the tables.

Everything I am reading seems to be telling me that I can't do this and I am puzzled because the scenario seems to be obvious to me as I want to have a complete copy of the database from the night before and then I want to play with that data adding and deleting at will (in our scenario the changes are NOT to be pushed back to the publisher) and then the next day it's all wiped out and demo is a fresh clean copy.

However, the issue I face is that after the snapshot replication the published database is unusable and we cannot insert new rows... this is very limiting. We can't find any errors, we cant find anything obvious in Google. So.... How should I set up a Snapshot replication of database a to database b, so that I can do anything I like in database b and it not affect database a. Then the next day database b is reset again to be last nights data?

Reading technet and microsoft pages is frustrating and pointless as it doesn't really "Tell" you anything, it just talks about features and scenarios which don't seem to match mine.. If any stackoverflow'er has a "This is how you should set this up" I would be grateful of the pointer.,

If you are able to say Server a needs to be setup as "x" and server "b" needs to be set up as Y and the type of replication for this database needs to be "z"


texts: How can i write a stored procedure which will perform a calculation and store the result in a table?

texts:
I have two tables:

CREATE TABLE `siteArea` (
  `length` decimal(6,2) NOT NULL,
  `breadth` decimal(6,2) NOT NULL,
  `Area` decimal(6,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `roadArea` (
  `Area` decimal(6,2) NOT NULL,
  `roadWidth` decimal(6,2) NOT NULL,
  `roadArea` decimal(6,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Where

Area = length * breadth;
roadArea = Area * road_width;

The Column Area from siteArea table has to be updated into Area column of the roadArea table.

Any changes to the Area of siteArea table has to be updated to the roadArea table and roadArea has to perform the calculation automatically without input from user.


texts:
I need to run a sql server logging db with 2 main tables in 2 seperate datacentres writing to both at the same time.

I had the idea of restoring the db to the new datacentre and then reseeding the identity column to -1 and setting the increment to -1, that way there would never be any chance of duplicate id's when the data needs to be combined.  DATACENTRE1 would be positive integers, DATACENTRE2 negative integers

would an increment of -1 cause any issues?


texts: Postgres CTE query for a single row not optimized

texts:
From my database I am trying to get a weighted sum of a user's stats, and I will only be querying the table one or two users at a time, so I wrote it as a view.

Since it's a view, I pretend to calculate the sum for every single row in the table, and then I was hoping that the optimizer would realize when I am only asking for a single row and would optimize the query. However my query plan is massive, and is calculating 17 billion rows at its innermost point, where I think there should be at most a thousand.

Here is the query:

CREATE OR REPLACE VIEW weighted_stats AS
WITH
    clf AS (SELECT * FROM classifiers order by time_trained desc limit 1),
    weights AS (SELECT kv.key, kv.value from clf, each(clf.weights) AS kv),
    kvs AS (
        SELECT stats.player_id, kv.key, kv.value FROM
        stats, each(stats.hstore_column) AS kv),
SELECT
    stats.player_id,
    SUM(kvs.value :: numeric * weights.value :: numeric) AS stats
FROM
    kvs JOIN weights USING (key)
GROUP BY kvs.player_id;

This is the query plan:

explain analyze select * from weighted_stats where player_id=76561197960269296

GroupAggregate  (cost=53645.35..299471.72 rows=1 width=72) (actual time=1014.016..1014.016 rows=0 loops=1)
   Group Key: kvs.id
   CTE clf
     ->  Limit  (cost=20.65..20.65 rows=1 width=84) (actual time=0.017..0.018 rows=1 loops=1)
           ->  Sort  (cost=20.65..22.43 rows=710 width=84) (actual time=0.014..0.014 rows=1 loops=1)
                 Sort Key: classifiers.time_trained
                 Sort Method: quicksort  Memory: 25kB
                 ->  Seq Scan on classifiers  (cost=0.00..17.10 rows=710 width=84) (actual time=0.003..0.005 rows=1 loops=1)
   CTE kvs
     ->  Seq Scan on stats  (cost=0.00..53572.18 rows=10318000 width=722) (actual time=0.037..530.337 rows=336036 loops=1)
   CTE weights
     ->  Nested Loop  (cost=0.00..20.02 rows=1000 width=64) (actual time=0.036..0.046 rows=2 loops=1)
           ->  CTE Scan on clf  (cost=0.00..0.02 rows=1 width=32) (actual time=0.020..0.023 rows=1 loops=1)
           ->  Function Scan on each kv  (cost=0.00..10.00 rows=1000 width=64) (actual time=0.011..0.013 rows=2 loops=1)
   ->  Hash Join  (cost=32.50..241344.73 rows=257950 width=72) (actual time=1014.012..1014.012 rows=0 loops=1)
         Hash Cond: (kvs.key = weights.key)
         ->  CTE Scan on kvs  (cost=0.00..232155.00 rows=51590 width=72) (actual time=0.044..1013.877 rows=62 loops=1)
               Filter: (id = 76561197960269296::bigint)
               Rows Removed by Filter: 335974
         ->  Hash  (cost=20.00..20.00 rows=1000 width=64) (actual time=0.060..0.060 rows=2 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 1kB
               ->  CTE Scan on weights  (cost=0.00..20.00 rows=1000 width=64) (actual time=0.040..0.054 rows=2 loops=1)
 Planning time: 0.286 ms
 Execution time: 1017.671 ms

This is still a lot slower than what I'd expect. The optimization is working partially by filtering before the join, instead of before the group by, but it seems like the kvs CTE (which should itself be filtered) is still being calculated for everyone.


texts: postgres+JDBC: fire trigger with non autocommit transaction

texts:
i have a jdbc client that make an two INSERT query on database whitout autocommit, and a trigger on database that fires when a insertion is done on a certain table

public foo() throws MyException {
    String INSERT1 = "INSERT INTO table1 VALUES(?,?)";
    String INSERT2 = "INSERT INTO table2 VALUES(?)";
    try {
        Connection conn = ConnectionManager.getConnection();
        PreparedStatement pstm1 = conn.prepareStatement(INSERT1);
        PreparedStatement pstm2 = conn.prepareStatement(INSERT2);
        conn.setAutoCommit(false);
        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

        pstm1.setString(1, "string1");
        pstm1.setString(2, "string2");
        pstm1.executeUpdate();

        pstm2.setString(1, "string3");
        pstm2.executeUpdate();

        conn.commit();
    }catch (ClassNotFoundException ex) {
        throw new MyException("foo error: " + ex.getMessage());
    } catch (SQLException ex) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException ex1) {
                throw new MyException("foo error: " + ex.getMessage());
            }
        }
    }catch (Exception e) {
        System.out.println(e.getMessage());
    } finally {
        try {
            if (conn != null) {
                conn.setAutoCommit(true);
                conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
            }
            new CloseConnection(pstm1, pstm2, null, conn).closeAll();
        } catch (SQLException ex) {
            throw new MyException("foo error: " + ex.getMessage());
        }

and the trigger looks like

CREATE FUNCTION my_procedure() RETURNS TRIGGER AS $my_trigger$
DECLARE new_field VARCHAR(30);
BEGIN
    new_field = NEW.field;
    --field is a column of table 2
    --retrieve some data from table1 and table2 then execute some insert on
    --another table
COMMIT;
END;
$my_trigger$ LANGUAGE plpgsql;

CREATE TRIGGER my_trigger
AFTER INSERT ON table2
EXECUTE PROCEDURE my_procedure();

now, everytime I execute the jdbc code, the insertion always fail. With some debug with println() I see that my code fail on

pstm2.executeUpdate();

but no exception was caught. I think it's a trigger problem that try to start execution when my jdbc transaction is not yet committed, in fact, the error on postgresql is

  record new is not assigned yet

There's a way to fix it (start trigger when my trasaction is committed)?


texts: Extract entries from table where specific datetime criteria are satisfied SQL

texts:
I have a table DATATABLE that contains a timeseries that looks something like this:

2018-01-11 09:00:00.000
2018-01-11 09:00:00.000
2018-01-11 09:45:00.000
2018-01-11 09:45:00.000
2018-01-11 10:00:00.000
2018-01-11 10:00:00.000
2018-01-11 10:15:00.000
2018-01-11 10:15:00.000

So if I want to extract the timeseries, I would query:

SELECT * FROM DATATABLE

Now what I want, is to be able to extract part of the timeseries that satisfies specific criteria, which changes each time. The criteria are stored as ranges of values, and so far I have created a procedure that extracts those criteria. One sample criterion would be that the time part of the timeseries should have a value that is contained int he following:

00:00:00
00:30:00
01:00:00
01:30:00
02:00:00
02:30:00
03:00:00
03:30:00
04:00:00
04:30:00
05:00:00
05:30:00
06:00:00
06:30:00
07:00:00
07:30:00
08:00:00
08:30:00
09:00:00
09:30:00
10:00:00
10:30:00
11:00:00
11:30:00
12:00:00
12:30:00

In other words, the values 2018-01-11 09:45:00.000 and 2018-01-11 10:15:00.000 should not be part of my final result. The final result would therefore look like:

2018-01-11 09:00:00.000
2018-01-11 09:00:00.000
2018-01-11 10:00:00.000
2018-01-11 10:00:00.000

The criterion that I want to set, in English would be:

''Select all the values from my timeseries that have a time part that is (exactly) contained in the series of times I provide you.''

What I wrote so far is:

SELECT * FROM DATATABLE
WHERE DATEPART(MINUTE,[DATATABLE].[ISSUETIMES])=(SELECT IssueTimes FROM @IssueTimes)

where @IssueTimes is a temporary table variable where I store the range of values I want to use as criteria and [DATATABLE].[ISSUETIMES] is the column that contains the timeseries that I want to filter.

The error I get is the following:

Operand type clash: time is incompatible with int

So i get that this probably means that am trying to compare times with int, but i do not know how to proceed as I am kind of new to SQL.

I hope my formulation was good enough to be understandable :)


texts: Unable to join secondary replica to the availability group by SQL Server error 47106

texts:
We were trying to create availability group with 3 synchronous replicas, where we couldn't give the names specified in /etc/hosts as node specifiers in the create availability group in the primary replica. We got the following error:

  Msg 35237, Level 16, State 1, Server , Line 1

  None of the specified replicas for availability group  maps to
  the instance of SQL Server to which you are connected. Reenter the
  command, specifying this server instance to host one of the replicas.
  This replica will be the initial primary replica.

but based on the following link:
What is causing this error: None of the specified replicas for availability group ag1 maps to the instance of SQL Server to which you are connected

I changed the node specifier to the hostname and that worked, but then I couldn't connect the secondary replicas to the availability group.

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = EXTERNAL);

I have received the following error:

  Msg 47106, Level 16, State 3, Server '', Line 1

  Cannot join availability group 'ag1'. Download configuration timeout.
  Please check primary configuration, network connectivity and firewall
  setup, then retry the operation.

  Msg 41158, Level 16, State 3, Server '', Line 1

  Failed to join local availability replica to availability group 'ag1'.
  The operation encountered SQL Server error 47106 and has been rolled
  back. Check the SQL Server error log for more details. When the cause
  of the error has been resolved, retry the ALTER AVAILABILITY GROUP
  JOIN command.


texts:
How can I get the sum of TotalAmount as single value for example

Product Name UnitPrice  Quantity    Discount%      Amount     SharingCost%  ShippingCost    CustomsCost  TotalAmount
pro1        250.00      12          10.00           3000.00   75             375.00         0.00             3075.00
pro2        100.00      10          10.00           1000.00   25             125.00         125.00           1025.00

from the above sample data sum of TotalAmountis 4100.00.

How can I get sum value which is 4100.00 in single column for specific Purchase_ID

Note

Parent table is Stock_Purchase

Child table is Stock_Purchase_Details

Query

SELECT
  S.Product_Name,
  SPD.*,
  Sp.Delivery_Status,
  CONVERT(decimal(10, 2), (SPD.Quantity * SPD.UnitPrice)) Amount,
  CONVERT(int, (SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * 100) [Cost%],
  CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Shipping_Cost AS decimal(10, 2)) AS [ShippingCost],
  CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Customs_Cost AS decimal(10, 2)) AS [CustomsCost],
  CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Shipping_Cost + (SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Customs_Cost + SPD.Quantity * SPD.UnitPrice - (SPD.Discount / 100) * SPD.Quantity * SPD.UnitPrice AS decimal(10, 2)) AS [TotalAmount]
FROM dbo.Stock_Purchase SP
INNER JOIN dbo.Stock_Purchase_Details SPD
  ON SP.Purchase_ID = SPD.Purchase_ID
INNER JOIN dbo.Store S
  ON SPD.Pro_ID = S.Pro_ID
WHERE SPD.Purchase_ID = '1'


texts:
I have a jsonb structure on postgres named data where each row (there are around 3 million of them) looks like this (I have GIN index on it):

[
    {
        "number": 100,
        "key": "this-is-your-key",
        "listr": "20 Purple block, THE-CITY, Columbia",
        "realcode": "LA40",
        "ainfo": {
            "city": "THE-CITY",
            "county": "Columbia",
            "street": "20 Purple block",
            "var_1": ""
        },
        "booleanval": true,
        "min_address": "20 Purple block, THE-CITY, Columbia LA40"
    },
    .....
]

I would like to query the min_address field in the fastest possible way. In Django I tried to use:

APModel.objects.filter(data__0__min_address__icontains=search_term)

but this takes ages to complete (also, "THE-CITY" is in uppercase, so, I have to use icontains here. This also has the problem that it ONLY searches the first elements - I'd want to search all the elements.

I tried dropping to rawsql like so:

cursor.execute("""\
    SELECT * FROM "apmodel_ap_model"
    WHERE ("apmodel_ap_model"."data"
    #>> array['0', 'min_address'])
    @> %s \
    """,\
    [json.dumps([{'min_address': search_term}])]
)

but this throws me strange errors like:

LINE 4:       @> '[{"min_address": "some lane"}]'
              ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

I am wondering what is the fastest way I can query the field min_address by using rawsql cursors.


texts: In a SSDT project how to avoid redeploying the assembly?

texts:
I have a SSDT project that has a few sql clr elements. They rarely get altered but every time I redeploy my db project it decides it must drop the assembly and recreate it.

I think its somewhat wasteful to keep doing that. Is there anyway I can configure it so that it will only recreate the assembly anytime I actually change the correlated C# sprocs?

If this will be helpful here is the profile I am using to generate the change script:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>#{DatabaseName}</TargetDatabaseName>
    <DeployScriptFileName>#{DatabaseScriptName}</DeployScriptFileName>
    <TargetConnectionString>Data Source=#{DatabaseServer}</TargetConnectionString>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <ScriptDatabaseOptions>True</ScriptDatabaseOptions>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <GenerateSmartDefaults>True</GenerateSmartDefaults>
  </PropertyGroup>
</Project>

I use octopus to do the deployment.


texts: Best way to select rows containing a substring

texts:
I've a table named comments where comment is a field where comment will be stored.

Now I want to find -1 within that comment.

the -1 should be independent. That is, it should not be joined with other word like 2-1.

I just want to know whether a comment contains -1.

I'm currently using where instr(comment,' -1 ') != 0.

I want this because, users can downvote a post by commenting with -1

Any ideas on how to do this even more efficiently.

I'm using MySql 5.1, if that helps.

Summary:

I want to select all rows that contain -1


texts: Can you let me know why a transaction log would be a lot smaller in size after restoring from a full backup.  Currently we have a massive log file on the live server, but after a restore to a test / development server the log file is considerably smaller.  Im guessing this is because the log was truncated as part of the backup however why would the original log file still be so huge.


texts: SELECT permission on view querying table from another database

texts:
SQL Server 2008 R2 SP1

My company uses the Great Plains (GP) financial system with several customizations.  Our Value-Added-Reseller (VAR) for GP has setup most of these customizations in a separate database from the GP data called DYNCUSTOM.

In the DYNCUSTOM database is a View that is merely a SELECT * FROM a table in the GP company database (called PARTS).  I was approached by a user having problems trying to select on this view.

The error he was getting:

Msg 229, Level 14, State 5, Line 2
SELECT permission denied on object 'BM010415', database 'PARTS', schema 'dbo'

I looked at how his login was mapped and he had a database user in the DYNCUSTOM database belonging to a database role that was granted select on the view.

However, he did not have a database user in the PARTS database.

Usually the intent of a view is so that one can grant select permissions on the view without exposing access to the underlying table.  With SQL Server however, if the view crosses databases does it then change the security context being used?  Therefore, the user would also need a database user in the PARTS database as well as granted select access to the underlying table?


texts: Using Identity Insert on a linked server

texts:
We have a database hosted in Microsoft Azure and using this database connection details I have created a linked server object in SSMS on our on-premise SQL box and I can insert into it as following syntax:

insert into [Azure].[budget].dbo.Bill

I wanted to set identity insert on and have tried the following:

SET IDENTITY_INSERT [Azure].[budget].dbo.Bill ON
insert into [Azure].[budget].dbo.Bill

I was then informed that I would have to use dynamic SQL for this so I changed to the following:

EXEC [Azure].[budgetenergy].dbo.sp_executesql N'SET IDENTITY_INSERT Bill ON;insert into Bill ([FIELD LIST]) select [FIELD LIST] from [DUOS3NF].dbo.bill where dateOfIssue > ''10 mar 2015''';

The [DUOS3NF] is a database that is on the SMSS box it is not a linked server. Using this approach is there any way that I can insert into the linked server the results of the select statement of another database?

When I execute the query the error it does not recognise the database.


texts: How To Update Random ID to a table in mysql?

texts:
I have table like this:

ID | City     | Prize |
----------------------------
1  |Surabaya  | HP
2  |Surabaya  | Watch
3  |Surabaya  | Bag
4  |Semarang  |
5  |Semarang  |Watch
6  |Semarang  |
7  |Bandung   |
8  |Bandung   |
9  |Bandung   |

My Question is How to creat Update Query for Fill The Prize column where City = '(already in settings)' AND ID =  Random.

ex: I want Update to fill Watch in City = 'Bandung' But ID = 'random' maybe (7,8,9).

I Hope you know What I Mean.

Im Very Apreciated your Advice

Thanks


texts: Recover database from uninstalled SQL Server

texts: My SQL Server was accidentally uninstalled. I didn't take a backup of all the databases. Is it possible to recover them?


texts: Importing huge dumps into a mysql server 5.5

texts:
I want to import a couple of huge .sql dumps (~600G each) into a mysql server 5.5 running on an Ubuntu 14.04.

The machine has about 8G of RAM allocated to it;

The previous machine had 128G;

Storage space considerations aside, what else should I take into account before performing the mysql import command?


texts: What are the intended use cases for Remote Blob Storage (RBS)?

texts:
Microsoft's two main sources on RBS do not mention SharePoint explicitly:

https://technet.microsoft.com/en-us/library/gg316768(v=sql.105).aspx
https://docs.microsoft.com/en-us/sql/relational-databases/blob/remote-blob-store-rbs-sql-server

However, aside from those top two search results, it seems that most of the other questions, sample code (excluding Microsoft's toy code on Codeplex), and third party providers are tailored towards SharePoint. What types of implementations were intended for this package? Does it work to support more generic solutions, or is it meant as an upgrade to larger SharePoint instances of SQL Server?


texts: Is it safe and reliable to backup MySQL databases by backing up the data folder?

texts: I have a MySQL database [server version 5.0.x, type InnoDB] which I want to backup. I don't have username/password to back it up using mysqldump. My question, is it safe to back up the database by backing up the entire MySQL's data folder (of course, after stopping the MySQL server service in Windows 7)?


texts: Simple Oracle query hangs depending on WHERE clause

texts:
Not used to using Oracle, but we have a large database where a non-unique query like

SELECT * FROM employees where department = 'HR'

is working, results list up no problem.

But when I do

SELECT * FROM employees where employeeID = '3HVtxO-F3004728F87EF61E'

the oracle database query is hanging (I am expecting one record to come back, and I am certain it exists because I copy pasted the employeeID from another query).

For the columns, only difference between the two is:

department is VARCHAR2(25)  NULL
employeeID is VARCHAR2(50)  NULL

Also department would have many matches while employee ID would most likely have 1 (I can't see that it is particularly defined to be unique in the table definition).

Other table characteristics:

not actually an employee table, it has work related data so I renamed it here...
contains more than a million rows and a few dozen columns
a bit of an old database, not sure who designed it at work ages ago, could have database integrity / indexing issues?
a regular select * from employees with no where clause would not work either because it freezes up around the half a million rows found mark.

Any idea why this could be happening? Should I design my query better?  How would you recommend diagnosing some issue related to problems with the database itself?   Trying to get a hang of this oracle database but a bit awkward to get used to since a simple query which should return one item is hanging.  Thank you.

Update 1:
Responding to comments, there is no indexing for this column.  Fortunately this is an outdated table which will not be in use at all, with a new version being made which will have indexed columns, so I think this was one issue.

Regarding the plan for the second query, it looks like:

Plan hash value: 123724717

------------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |    12 |  4416 |  2376K  (1)| 07:55:19 |       |       |
|   1 |  PARTITION RANGE ALL|          |    12 |  4416 |  2376K  (1)| 07:55:19 |     1 |    14 |
|*  2 |   TABLE ACCESS FULL | [Employee|    12 |  4416 |  2376K  (1)| 07:55:19 |     1 |    14 |
------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / Employees@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("EmployeeID"='3R5MMN-0F9345L9IK8A349A043')

Column Projection Information (identified by operation id):
-----------------------------------------------------------
-- The list of all the columns follows.

My guess is that this is just a really awefully designed table and not designed to have gotten so big, or for someone to actually use a lot :)  For practical purposes, I will move on to more recent incarnations of this table which will have indexing and hopefully that will fix it.


texts: Why can you only drop a SQL Server view in the current database?

texts:
Just noticed this and was having a hard time coming up with a technical reason for it.

Tables can be dropped using three part names, but views are restricted to two part names.

What is the reasoning behind this?


texts:
I'm designing a database whose data will be generated daily by an ETL.
After some research, I found that one can use partition switching or schema transfer to maximize the uptime of the "live" data. Just for completeness of information, this database will be deployed on Azure SQL Database and developed on SQL Server 2019 Developer Edition on Docker. It consists in ~ 20 tables, each one having less than 10M rows (expected).
So now in my design there are two schemas:

[App]: containing multiple tables (w/o foreign keys) with the live data used by the application.
[AppShadow]: containing the exact same tables as the [App] schema (but w/ foreign keys), usually empty, used as destination for the ETL.

My flow looks like this:

Perform the ETL using the tables in the [AppShadow] schema as a destination. The foreign keys ensure that my data is consistent.
set transaction isolation level serializable; begin transaction;
Disable all the constraints on the tables in the [AppShadow] schema using alter table [AppShadow].[<table_name>] nockeck constraint all;
Truncate all the tables in the [App] schema
Switch the partition of all the tables from [AppShadow] to [App]
Re-enable all the constraints on the tables in the [AppShadow] schema using alter table [AppShadow].[<table_name>] with check check constraint all;
commit transaction;

This solutions seems to work fine and the downtime is minimal, but I can't figure out any advantage or disadvantage over transferring the entire tables between schemas using a [Swap] schema and the alter schema <schema_name> transfer <table_name> statement.
SQL Server being SQL Server, I'm pretty sure there are some hidden caveats that I'm unaware of that should let me prefer a solution over the other.
Do you have any advice or suggestion?


texts: MySQL - maximum of sum over different months with ties over multiple years

texts:
This question was inspired by this one [closed] and is virtually identical to this one but using different RDBMS's (PostgreSQL vs. MySQL).

Suppose I have a list of tumours (this data is simulated from real data):

CREATE table illness (nature_of_illness VARCHAR(25), created_at DATETIME);

INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung',   '2018-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2018-02-03 17:50:32');
-- 2017, with 1 Cervix and Lung each for the month of Jan - tie!
INSERT INTO illness VALUES ('Cervix', '2017-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung',   '2017-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2017-02-03 17:50:32');

You want to find out which particular tumour was most common in a given month - so far so good!

Now, you will notice that for month 1 of 2017, there is a tie - so it makes no sense whatsoever to randomly pick one and give that as the answer - so ties have to be included - this makes the problem much more challenging.

The correct answer is:

  Year    Month  Tumour count      Type
  2017        1             1    Cervix  -- note tie
  2017        1             1      Lung  --   "   "
  2017        2             3      Lung
  2018        1             5    Cervix
  2018        2             3      Lung

A further bonus would be to have the month name appear as text rather than an integer.

I have a solution but it's quite complex - I'd like to know if my solution is optimal or not. The MySQL fiddle is here!


texts: Limiting number of results in a Partition using OVER(PARTITION BY)

texts:
In the following query, why is it that we have to limit the results returned from each Partition by using the clause WHERE foo.row_num < 3 outside of the subquery foo but not from within the subquery with WHERE row_num < 3?

Query

SELECT pid, land_type, row_num, road_name, round(CAST(dist_km AS numeric), 2) AS dist_km

FROM (
    SELECT ROW_NUMBER()
    OVER (
        PARTITION by loc.pid
        ORDER BY ST_Distance(r.the_geom, loc.the_geom)
    ) as row_num,
    loc.pid, loc.land_type, r.road_name,
    ST_Distance(r.the_geom, loc.the_geom)/1000 as dist_km
    FROM ch05.land AS loc
    LEFT JOIN ch05.road AS r
    ON ST_DWithin(r.the_geom, loc.the_geom, 1000)
    WHERE loc.land_type = 'police station'
) AS foo

WHERE foo.row_num < 3
ORDER BY pid, row_num;

Query that does not work

SELECT pid, land_type, row_num, road_name, round(CAST(dist_km AS numeric), 2) AS dist_km

FROM (
    SELECT ROW_NUMBER()
    OVER (
        PARTITION by loc.pid
        ORDER BY ST_Distance(r.the_geom, loc.the_geom)
    ) as row_num,
    loc.pid, loc.land_type, r.road_name,
    ST_Distance(r.the_geom, loc.the_geom)/1000 as dist_km
    FROM ch05.land AS loc
    LEFT JOIN ch05.road AS r
    ON ST_DWithin(r.the_geom, loc.the_geom, 1000)
    WHERE loc.land_type = 'police station'
    AND row_num < 3
) AS foo

ORDER BY pid, row_num;

Error: ERROR: column "row_num" does not exist


texts:
Microsoft SQL Server 2016 (SP1) (KB3182545)

  Server Memory : 61GB  SQL

  Server Max Memory : 56320 MB

I have three database (OneDB,TwoDB,ThreeDB) in the my production environment.

I running sp_BlitzCache on OneDB

  Available memory %7-8

I create 'Blitz' db for FirstResponderKit, sp_WhoIsActive and so on dba tools.

I running sp_BlitzCache on Blitz database

  Available memory %0

Differences between two databases:

SQL Server why exceeds the maximum memory limit? How can I resolve it?


texts: Insert a large TEXT. Is INSERT the only way?

texts:
I am not a strong expert in databases at this level, so I was wondering the following.

A database I am currently working on has a large TEXT field where potentially hundreds of megabytes are stored in each row. I understand this is not optimal design but it predates me and I can't change it now.

The insertion of the data happens with a single SQL query INSERT INTO, which of course results in a gigantic query. I was wondering the following:

Is INSERT the only option in this case when submitting large blob of data? maybe there's a SQL extension to handle such large transfers that is better optimized.
Having a single query of this size obviously requires that the whole transfer of these hundreds of megabytes goes smooth without any network issue for the whole duration of the transfer. How resilient is this to network issues, and can it be restarted in case of failure without having to reissue the whole query again (and therefore restart the whole transfer from scratch)?

I am using the latest MySQL, but I wonder also about other databases for personal awareness.


texts: What events still occur within a transaction that gets rolled back?

texts:
Is it true that all data modifications are undone when the transaction they are a part of gets rolled back?

For example if a cursor that just executed a stored procedure 100 times that updated a table on each iteration, would all those updates get rolled back?

Would a DDL statement get rolled back?...such as DROP TABLE or CREATE VIEW?
What about DROP DATABASE?

I know certain statements still execute though like PRINT "MESSAGE".

I'm just trying to get an idea of what kinds of events still occur.


texts: How to force MySQL master to overwrite the slave

texts:
We have a LAMP architecture website.

Currently we have a production server running on the cloud and each developer has a complete copy of both the code and the database. Code is synced through Git and it's working fine. However, the database is synced by dumping the production server nightly and import everything into the local database. As the database grows larger, this process is taking unreasonably long time.

So I tried to set up a master slave replication where the production server is the master and my developing workstation as a slave. The problem is, during developing, we have to write to the slave for testing purpose. And some write operations causes errors during the replication such as primary key conflict, etc.

I am wondering if there is a way, other than dump/import, with master-slave replication where we can force the master to overwrite the slave in case any conflicts happens. For example, during the replication, the slave should delete anything that has conflict with the master and apply the changes happened on the master.

Or if this is not possible, is there a better way that I can refresh my local database with the server? This does not need to be 100% identical or real-time. I understand that the process I described above may lead to more discrepancies over time if the local changes do not cause conflicts. This can be solved by dump/import method as long as this does not happen too frequently.


texts: Special syntax sth.name() in the CREATE FUNCTION statement, what does it mean?

texts:
In this SO question about stored procedures in plpgsql, the stored procedure look like sth.name(). I don't know what is the meaning of the prefix sth.

For example:

create or replace function something.function_name()
returns setof record as
$$
-- code
$$ language plpgsql;

Looking in this book in the "Part IV: Programming with PostgreSQL" I have found no mention of this type of creation declaration with a function name in two parts.

In the postgresql documentation, at the create function section, the only similar thing is when they deal about argtype or regtype than could be written in the form: table_name.column_name%TYPE. But it's not related to the name of the function.

So, what is this syntax related to ?


texts: Deleting a Column from a Table in Production

texts:
We have a situation where we need to change the relationship between 2 tables from m:1 to m:n.

So, we need to create a Cross-reference table in between those two tables.

After migrating all existing data from the "child" table into the Cross-reference table, would it be a bad idea to delete the original foreign key column in the child table?

If we leave it there, we basically have technical debt. But I'm not a dba and don't have a good grasp of the implications of deleting a column from a table. (I know it is possible, but is it a bad idea? Will my database hate me for it?)

Thanks


texts: Microsoft Sql Server - How to replicate a table from Linked Server?

texts:
I have an Origin Database - #1 with very restrictive access due to security.

I provisioned a new Windows desktop machine with Microsoft Sql Server Management where I created a local database with all privileges, so I can create a Linked Server to database #1.

I have access to the Database #1 via Linked Server but I need to replicate some tables in realtime; I tried using the Replication option in the Object Explorer, but it does not allow me to do anything with the Linked Server.

How can I replicate some of those tables via Linked Server? So, I can copy those tables and updates and store them in local database?


texts: Tempdb is getting full very quickly in microsoft sql server 2008

texts:
I am developing a log analyzer tool and After queering it and
generating reports Tempdb is getting full very quickly .

Is there any solution to clean the tempdb file in microsoft sql server
2008 on a timely basis other than restarting the server ?

Thank you so much in Advance


texts: Oracle Global Temp table - data is truncated but the table remains in the DB ! is this an expected behavior or a configuration?

texts:
We are trying to create global temp table using

    Create global Temporary table t1 ( id NUMBER, description VARCHAR2(20) )
ON COMMIT Delete Rows;

and trying to drop it at the end manually  but the temp table created cannot be dropped by after but the session the data is truncated at the end.

Not able to drop it  - is this how oracle works with GTT ?
What are the alternatives ??


texts: Table properties in SSMS and sp_spaceused don't match

texts:
I have a table Table_A where when I right-click the table in SSMS and go to Properties > Storage, it shows the following:

Data space: 5 GB
Index space: 250 MB
Number of partitions: 400
Partitions using default columnstore compression: 1-400

if I do sp_spaceused Table_A, it shows entirely different numbers (rounded):

Name    rows    reserved        data            index_size  unused
Table_A 5000000 100000000 KB    100000000 KB    25000 KB    28000 KB

What could be causing the difference in space consumed?

The database's properties page is showing that 100 GB reserved space as "Available", but fails to reclaim any of it during a dbcc shrinkdb

This table "grew" suddenly while rebuilding a clustered columnstore index where the index size grew to ~20 GB, but shrank back down after the job completed. I'm still trying to wrap my head around columnstore compression and the indexes, so I feel like I'm missing something simple


texts: MongoDB backup sizes same with MMAPv1 and wiredTiger

texts:
while migrating from 3.0.10 to 3.2.8, I came to know that data backup sizes for MMAPv1 and wiredTiger are same.

Is this how it should work, or am I doing something wrong?

Version 3.0.10 with MMAPv1

Backup size --> 22 GB

Database size --> 55.927 GB

Upgraded Version 3.2.8 with wiredTiger

Backup size --> 22 GB

Database size --> 23.215 GB

db.stats()

{
    "db" : "db",
    "collections" : 7676,
    "objects" : 9291151,
    "avgObjSize" : 379.11313657479036,
    "dataSize" : 3522397398,
    "storageSize" : 2143838208,
    "numExtents" : 0,
    "indexes" : 7679,
    "indexSize" : 210493440,
    "ok" : 1
}


texts: How to find a SQL Job details in Production environment?

texts:
There is a stored procedure being executed daily which inserts a record in a specific table. Our Production database servers has lot of SQL jobs scheduled to run daily at different intervals.

How to find out which job is running this particular stored procedure? Is there a way to find using SQL scripts?


texts: How do I open an ERwin Naming Standards file (.nsm) in Excel?

texts: I have a Naming Standards file in ERwin (.nsm format) that I want to be able to open up in Excel (for example, in order to compare 2 .nsm files). How would I be able to do that?


texts: sp_BlitzCache: Plan Cache 100% in last 4 hours

texts:
This is a re-post of my question here

BlitzCache suggests there may be memory pressure, but I think there is another problem. I just don't know what that problem is!

sp_blitzFirst @ExpertMode = 1; says there isn't a problem, but

SELECT MIN(creation_time) FROM sys.dm_exec_query_stats;

says nothing is lasting longer than an hour.


texts: Setting up Data Level Application Alerts in SQL Server

texts:
Solutions are available for providing alerts when any job fails in SQL Server.

Is there any solution available for setting up data level alerts - for e.g.

if data hasn't updated in USER tables for lets say 2 hours, we get an ALERT
if any USER table, specific column lets say has aggregate of more than 100% (which is not right) then we get an alert
if any specific column which should not have value (lets say greater than 10), then we get an alert

We can create a table for this type of alerts and then run may be any SSIS package or TSQL every 15 mins - and anything which doesnt match the benchmark value set for alerts we get email.

Is any aware of any solution developed for this type of need

Regards


texts: Pull data from sys.columns in a different database

texts:
I am running the following on one instance on the server:

SELECT c.*
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(@tablename);

Everything works great.

I would like to run the same statement but point it to another database on the server.

  databaseName.dbo.tablename

I've tried the following (and other variations), but it does not work.

SELECT c.*
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('databaseName.dbo.tablename');

Is there a way to get the column information on one database from another?

Code:

SELECT @SQL = @SQL + 'INSERT INTO myTable
  (ColumnName,ColumnValue,SID,SName,RID,RName)
  VALUES (''' + QUOTENAME(c.name) + ''',NULL,0,NULL,0,NULL);'
            FROM sys.columns c
            WHERE c.object_id = OBJECT_ID(@tablename);

EXEC(@SQL);

Is there a way to do this dynamically?  If I had a @DBName parameter that I was passing in.


texts: Execute stored procedure remotely using linked server

texts:
We have 3 SQL Server 2012 instances, 2 of them we're not db_owner. Some of our stored procedures use DMV and sys views and stored procedures that only work when executed locally.

I'd like to centralize the execution of these (our) stored procedures in our main instance, and from it execute these stored procedures remotely. I tried using SQL Server Agent jobs, but it requires some configuration I'm not comfortable doing, and probably won't have access anyway.

Is there any way to remotely execute a stored procedure, preferably using linked server?

Edit: we can use linked server, openrowset, anything that can be run inside simple SQL. I'd rather not use SSIS, because doing so would separate part of the process from simple SQL and require multiple platforms to run it.


texts: SQL Server full safety mirror commit overhead less then Time to send?

texts: How is it possible that the time to send value is greater then the mirror commit overhead? The mirror operates under full safety so this means that the commit is only done @ principal when the commit is successful @ mirror. When the time to send is 2 seconds, this should mean that that overhead is at least 2 seconds. Or am I missing something?


texts: How to create a server alias in SQL Server 2012

texts:
We have a Windows Server 2012 server running with 2 instances of SQL Server 2012 installed on it - Live and System. Both instances are accepting connections on 1433 & Live is the default instance (1433 set in the ListenAll port).

We have a program that needs to connect to the System instance, however it does not support named instances of SQL Server.

From what I have read, we have 2 options -

install an extra network card, create a separate DNS record for it and have the System instance listen to all connections to 1433 on that card
Create a DNS record pointing to the server and use SQL Alias to point to System instance

We'd prefer to go with option 2 as it means we don't need to setup any more hardware, but I can't get it to work. The network team has set up an extra DNS A record ("programDB") to point to the server.

On the SQL Server I have created an alias using the SQL Server Configuration Manager:

Alias Name="programDB" [I've also tried "programDB.domain.com.au"]
Port No = 1433
Protocol = TCP/IP
Server = ServerName\System

I have set this up in both the 32 and 64 bit configuration

When I connect to programDB from SSMS, it connects me to the Live instance, not the System instance.

Have I miss understood how aliases work, or have I missed something in the setup?

Update: This is what I have set up:

DNS A Record set up to point "programdb" to the IP address of the server
SQL Aliases (x32 & x64):

TCP/IP Settings (port set to 1544):

I've restarted the services a number of times. If I try to connect to "programdb,1544" then it connects to the System instance


texts: Not able to connect to Mongodb vie mongo shell

texts:
I am no able to connect to mongodb via mongo shell.
command:
sudo mongo --tls --tlsCertificateKeyFile server.pem --tlsCAFile CA_INTERMEDIATE --host "mongodb://xxx:xxx@some-url:27020/data_base?ssl=true"

error:
MongoDB shell version v4.4.3
connecting to: mongodb://some-url:27020/data_base?compressors=disabled&gssapiServiceName=mongodb&ssl=true
{"t":{"$date":"2021-01-08T14:55:17.158Z"},"s":"E",  "c":"NETWORK",  "id":23256,   "ctx":"js","msg":"SSL peer certificate validation failed","attr":{"error":"SSL peer certificate validation failed: unable to verify the first certificate"}}
Error: couldn't connect to server some-url:27020, connection attempt failed: SSLHandshakeFailed: SSL peer certificate validation failed: unable to verify the first certificate :
connect@src/mongo/shell/mongo.js:374:17
@(connect):2:6
exception: connect failed
exiting with code 1

However, this works when I am using the same keys, certificate and parameters with mongoDB Compas.

can someone please tell me what I am doing wrong?


texts: Join query using OR on two different columns

texts:
Is there a more efficient way (performance wise) to write this?

select *
from stat.UniqueTeams uTeam
Left Join stat.Matches match on match.AwayTeam = uTeam.Id
                             or match.HomeTeam = uTeam.id


texts:
I've doing some tests in SQL Server and Oracle.

When I created a temporary table in SQL Server I don't needed grant select on the temporary table for the specified user. Any user get to query the temporary table.

In Oracle I need to issue grant for the users.

Is this the behaviour? I always need to issue grant on the temporary table?

After this I thought that one way to have the same behaviour of SQL Server would be grant to public role.


texts: is there a way i can set password on a table i am creating in mysql. i am working with rather sensitive data , and i have to use mysql on a server on which i am obviously not the admin. so people using mysql on that server can pretty much see the tables other users are creating. is there a way i can make a table which requires a password to access the particular table, if not can anyone suggest another alternative. admin has not given rights to create a new database.


texts:
Say  we have three nodes(simple replica no sharding) i.e. N1,N2 and N3 with replication factor as 3.

As read and write request can go to any node, Say Request  write request(to update the same record at same time) comes and  goes
node N1.
Read request comes to read the same document and  goes to node N2. I believe it will return stale data as write is not replicated to N2 yet ?
Consider the default configuration where i have not tuned the read and write concerns.
I believe problem in step2 can be resolved with  either of below options

a. In synch replication, Good for strong consistency but bad for performance

b. Tuning read and write consistency as explained at read and write consistency

c. With the help of sharding(if required), where shard key will ensure all read and writes goes to particular node for particular shard key ?

Is my understanding in point 2 and 3 correct?


texts: Get the rows where id_user_rq exists in id_user_ap and viceversa

texts: I have this table structure: http://www.dropmocks.com/mBjNvF and need to get the id_user_rq if not exists in id_user_ap as the title said. I need to get the rows where id_user_rq = 1 and id_user_ap = 2 and id_user_rq = 2 and id_user_ap = 1 meaning that I send a invitation and the other part accept me and inverse the relation, any help?


texts: Oracle dbms_xplan.Display() equivalent to SQL Server 2017

texts:
I am converting some sql from Oracle to SQL Server 2017.

One of SQL code is having dbms_xplan.Display() inside the script.

I want to know what is the equivalent of dbms_xplan.Display() in SQL Server 2017.


texts: Central Management Server - can it manage itself?

texts:
I'm setting up a SQL Server 2012 lab on Windows 2008 R2 which has 5 SQL Servers. I registered SQL1 as my central management server and created a group under this server that includes all the servers and the CMS itself. I'm a bit confused since the documentation says the CMS cannot be a member of a group it maintains. However, I'm able to include it in the group, run multiple queries and evaluate policies successfully.

Reference: http://msdn.microsoft.com/en-us/library/bb895144.aspx see under "Related tasks"

I've seen other forum posts where users have issues registering the CMS itself but I don't have this problem at all. Has something changed?

I'm running SQL Server 2012 Enterprise SP1 and SSMS 2012.

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

Here's the CMS returning queries for the whole group.


texts:
Trying to set up Kerberos Authentication between two linked server to get past the double-hop issue.
Server A is in a domain with several other SQL Servers. All SQL instances on all servers in this domain are using the same service (user) account for running the instance.
Server B is in another domain using a separate domain account to run its instance. It is currently the only SQL server running with a domain account. Other are using local accounts.
I am configuring Server B's service account to allow for self managing SPN's which should be easy. My problem is I will need to do this with Server A's service account as well.
I can't find what I would like to make this clear to me but I am concerned about how Server A's service account will work once I allow it to "self manage" its SPNs i.e. give the AD account read/write ServicePrincipalNames of itself.
Can an AD service account host SPNs for multiple SQL Instances? Currently the account is not hosting any when I query it via setspn -L [domainname]\sqlengine
All SQL servers are running 2008 or higher. Most are 2016.


texts: Linked server available to users without permissions

texts:
I have users seeing a linked server which they are not supposed to see.

The linked server is defined so that only I will have access to it, but everybody can see and use it.

I have created the new linked server using the following steps:

Connect SSMS Object Explorer to the SQL Server instance
Expand Server Objects, right click Linked Servers and left-click New Linked Server...
Under General tab choose SQL Server for Server type and write the Server's name
Under Security tab in the upper part click Add, choose "sa" as Local Login, enter Remote User's name and Remote Password
Under Security tab in the lower part (under For a login not defined in the list above, connections will:) choose the first option: Not be made
Click OK and start testing

Now the only person supposed to see the linked server is me ("sa"), but somehow other users can see it and use it.

Note 1: The users who can use the linked server have permissions on the remote server, they are not seeing data that they should not see, they are just able to access it from the linked server when they should not be able to.

Note 2: I am the only sysadmin on both instances.


texts: Galera cluster slightly slower than single database?

texts:
I recently set up a MariaDB galera cluster for our production. I used sysbench to benchmark the cluster against the old database which is on a single server.
On my PRD Galera Cluster I got the following results:
SQL statistics:
    queries performed:
        read:                            3914980
        write:                           0
        other:                           782996
        total:                           4697976
    transactions:                        391498 (1304.77 per sec.)
    queries:                             4697976 (15657.22 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0492s
    total number of events:              391498

Latency (ms):
         min:                                    5.37
         avg:                                   12.26
         max:                                   66.20
         95th percentile:                       15.83
         sum:                              4798745.23

Threads fairness:
    events (avg/stddev):           24468.6250/414.77
    execution time (avg/stddev):   299.9216/0.01

Meanwhile our old single database production got this results:
SQL statistics:
    queries performed:
        read:                            5306060
        write:                           0
        other:                           1061212
        total:                           6367272
    transactions:                        530606 (1768.51 per sec.)
    queries:                             6367272 (21222.18 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0266s
    total number of events:              530606

Latency (ms):
         min:                                    3.87
         avg:                                    9.04
         max:                                   59.99
         95th percentile:                       12.08
         sum:                              4798278.00

Threads fairness:
    events (avg/stddev):           33162.8750/440.14
    execution time (avg/stddev):   299.8924/0.01

Now I'm wondering why does the cluster operate a bit slower than the single database? They have the same specs: Quadcore CPU, 32GB RAM and vm.swappiness=1. Here's my cluster configuration (same across 3 servers) and is using HAProxy to load balance between 3 servers:
max_connections = 3000

wsrep_slave_threads=4
innodb_lock_wait_timeout=8000
innodb_io_capacity=2000
innodb_buffer_pool_size=25G
innodb_buffer_pool_instances=25
innodb_log_buffer_size=256M
innodb_log_file_size=1G
innodb_flush_log_at_trx_commit=2
innodb_flush_method = O_DIRECT_NO_FSYNC

innodb_read_io_threads=8
innodb_write_io_threads=4

thread_handling = pool-of-threads
thread_stack = 192K
thread_cache_size = 4
thread_pool_size = 8
thread_pool_oversubscribe = 3

wsrep_provider_options="gcache.size=10G; gcache.page_size=10G"

I used sysbench on a spare server, does the latency between servers also affect the outputs? I would appreciate any inputs, thank you.


texts: How to translate high level entity relationship into schema

texts:
According to the following articles written by Dr. Chan, we can describe high level entity relationship. (see page-10,11 of doc-A and page9 of doc-B)

•doc-A) English Sentence Structure and Entity-Relationship Diagram.

http://www.maltercorp.com/DrPeterChen/english.pdf

•doc-B) English, Chinese and ER diagrams.

http://www.csc.lsu.edu/~chen/pdf/ER_C.pdf

These articles said ERD is translated from English sentence like below.

-- The product is shipped to the customer.

-- This action “Shipping” is performed by the clerk.

Then think about another example.

-- The customer is identified their bank card.

-- This action “identifying” is Authenticated by the bank.

-- The bank issues the "bank card" to their "customer" with similar interpretation.

Now my questions are:

How can I express this situation with an ERD?

Relationship “issue” has possibility to connect with two entities "Bank card" or
"customer".

But I don't want to use ternary relationship as a restriction.
How can I translate those entities mixed with different dimensions to a database schema?
Basically, is it possible to connect to the entity at another level ?
Are there any good tools for describing high level entity relationship and generating DDL?


texts: Cannot remove idle connections to a Postgres database


texts:
I updated the timezone on an AWS MySql instance, and tech support told me I also had to runt his command for each MySql user:

GRANT EXECUTE ON PyupROCEDURE `mysql`.`store_time_zone` TO 'some_user'@'localhost';

I tried running without selecting a database and got this error:

ERROR 1046 (3D000): No database selected

So then I ran:

use db2;
GRANT EXECUTE ON PyupROCEDURE `mysql`.`store_time_zone` TO 'some_user'@'localhpst';

And I got this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON PyupROCEDURE `mysql`.`store_time_zone` TO 'some_user'@'localhost'' at line 1

I looked at the docs (http://dev.mysql.com/doc/refman/5.6/en/grant.html) and I don't see what would be wrong - here I want to grant access to a procedure and that seems to correspond to the docs:

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

And also this is how we created the procedure:

DELIMITER |
CREATE PROCEDURE mysql.store_time_zone ()
IF NOT (POSITION('rdsadmin@' IN CURRENT_USER()) = 1) THEN
SET SESSION time_zone = 'America/New_York';
END IF |
DELIMITER ;

Where did I go wrong? Thanks for any suggestions.


texts: How do I implement insert-if-not-found for transactions at serializable isolation level?

texts:
I'm having a hard time figuring out how to exactly implement a 'insert if not found' function. Consider the following.

We have a table called artist with 2 columns, (name, id) where name is the unique and id is a serial primary key. It's a contrived example, but it illustrates my problem:

    SESSION A                     SESSION B
1.                                SELECT id FROM artist
                                    WHERE name = 'Bob';
2.  INSERT INTO artist (name)
      VALUES ('Bob')
3.                                INSERT INTO artist (name)
                                    VALUES ('Bob')
4.   code that users 'Bob'
     (e.g., a FK to Bob's ID)
5.                                ??? Bob already exists, but we
                                  can't find it
4.  COMMIT

Session B begins by trying to find an artist called Bob, which fails. However, Session A then creates Bob. Session B tries to insert an artist called Bob, which fails as it violates the primary key. But here's the bit I don't get -- if I change operation 3 to be a select on artist the table is still empty! This is because I'm using the serializable isolation level, but how can I handle this case?

It seems the only option I have is to abort the entire transaction and try again. If this is the case, should I throw my own 'could not serialize' exception, indicating the application should retry? I already wanted this 'find-or-insert' in a plpgsql function, where I would INSERT, and if that failed SELECT but it seems impossible to find the conflicting row...


texts:
Suppose we have a relation

Employee={empId,name,department, salary}

and we are interested in finding the employees who get more salary than anyone in department 5. Would the following query work?

Select e.empId
from employee e
where not exists (select * from employee s where s.department = 5 and s.salary >= e.salary)

Can this fail in any case? If so how?


texts:
I have a table column with the data type timestamptz. Time zone of server is America/Denver. Going through some strange issue while inserting a value in that column.

When I update the column to the value '03/11/2018 02:00:00' it results in '03/11/2018 03:00:00' (+ 1 hour!).

UPDATE details
SET    interval_start_timestamp = '03/11/2018 02:00:00'::TIMESTAMP
WHERE  id = 2395

The issue is only with the time 02:00:00. 02:30:00 and other values like 01:00:00 or 01:30:00 are inserting properly.

PostgreSQL 9.5.7 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit

Any one can help me to understand this behavior?
And how can I insert the proper values?


texts: Speedup DROP huge MySQL replicated table

texts:
I've a huge InnoDB table customer_users_old (150G) that it's not in use anymore in the database, it was renamed from customer_users.

My setup has a Master-Slave replication of MySQL 5.5 with innodb_file_per_table activated, so the table has it's own files.
As the DROP command takes a lot of time, I'm afraid it can have some inpact on the prodcution database.

So I'm thinking on disaling unique and foreign key checks for the session as it was suggested on DROP TABLE on a huge InnoDB table:

SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;

And disabling the binary log to drop the table first on the master and then on the slave as it was suggested on Deleting data from a large table with replication:

SET sql_log_bin = 0;

Will this speedup the process and minimize the impact on the servers load?


texts: Postgresql logical replication reapeted failures, replication slot is active for PID x

texts:
Postgres 13.2 running on Windows server 2019.
Every few days or sometimes even more frequently logical replication is stopped by an error
replication slot ""sbscr_name"" is active for PID 4754 (some id) and the WAL files are being cumulated.
Restart of postgres on replication doesn't help, seems that the only working solution is to drop and recreate a subscription.
What might be the root of these problems? Any suggestions where to look at? And maybe some other problem solving procedure can be applied?


texts: How do I correctly group output from multiple tables?

texts:
I have more than 2 tables where entries are associated with dates.  Each table can have multiple entries per date.  I want to produce output that lists a date, then lists the entries of each table for that date.  e.g.

date      |   tbl1_col
1/1/2015  |     dat1_1
1/2/2015  |     dat1_2
1/2/2015  |     dat1_3
1/3/2015  |     dat1_4

date      |   tbl2_col
1/1/2015  |     dat2_1
1/2/2015  |     dat2_2
1/2/2015  |     dat2_3
1/3/2015  |     dat2_4

date      |   tbl3_col
1/1/2015  |     dat3_1
1/2/2015  |     dat3_2
1/2/2015  |     dat3_3
1/3/2015  |     dat3_4
1/3/2015  |     dat3_5

I want output like this:

date      |  tbl1_col  |  tbl2_col  | tbl3_col |
1/1/2015  |  dat1_1    |  dat2_1    | dat3_1   |
1/2/2015  |  dat1_2    |  dat2_2    | dat3_2   |
          |  dat1_3    |  dat2_3    | dat3_3   |
1/3/2015  |  dat1_4    |  dat2_4    | dat3_4   |
          |            |            | dat3_5   |

The problem is I'm missing the association between individual table records (I don't know if dat1_1 really corresponds to dat2_1.  So the only useful way for me to look it is by date.  If I use a join I get output that creates every possibility:

1/2/2015  |  dat1_2    |  dat2_2    | dat3_2   |
1/2/2015  |  dat1_2    |  dat2_2    | dat3_3   |
1/2/2015  |  dat1_2    |  dat2_3    | dat3_2   |
1/2/2015  |  dat1_3    |  dat2_3    | dat3_3   |
1/2/2015  |  dat1_3    |  dat2_3    | dat3_2   |
1/2/2015  |  dat1_3    |  dat2_2    | dat3_3   |


texts: Database transaction log growth who is the cuplrit?

texts:
There are abundant information on how to shrink a growing Transaction log, from frequent log backups, to changing the RECOVERY from FULL to SIMPLE and truncate. A daily background job takes care of this process and it's working fine. By now, most people know how and why Transaction Log works. There is a complete explanation here.

I have a misbehaving application that, somehow, create a growing transaction log by not releasing, not sure if it's not committing or not closing or what, at the end of the (whatever) action.

It doesn't look like there is data loss or inconsistencies and not any unusual situation on the DB.

Upon restarting SQLSERVER show the database in 'recovery mode' for a while and then back to normal.

Doing DBCC LOGINFO, shows over 100 entries, all of them with 'Status=2' so I understand, they are in use. Interesting but on some entries, the 'filesize' is even larger that the Database itself!

The subject database on SELECT name,log_reuse_wait_desc,recovery_model_desc FROM sys.databases show log_reuse_wait_desc as 'NOTHING' and recovery_model_desc as 'FULL'

My question is: Is there any method, documented or not, to 'read' the log file and find what table, application, data or whatever info is on the VLF to help finding the culprit application?


texts:
I have been facing problem with my database server quite a month, Below are the observations that I see when it hits the top.

 - load average 40 to 50
 - CPU % - 400%
 - idle % - 45%
 - wait % - 11%
 - vmstat procs r-> 14 and b-> 5

And then drains down within 5 minutes. And when I check the show processlist I see queries for DML and SQL are halted for some minutes. And it processes very slowly. Whereas each query are indexed appropriately and there will be no delay most of the time it returns less than 1 second for any query that are being executed to server the application.

Mysql Version : 5.0.77
OS : CentOS 5.4
Mem: 16GB RAM (80% allocated to INNODB_BUFFER_POOL_SIZE)
Database Size: 450 GB
16 Processor & 4 cores
Not in per-table model.
TPS ranges 50 to 200.
Master to a Slave of the same configuration and seconds behind is 0.

Below url shows show innodb status \G and show open tables; at the time spike. And this reduced within 5 minutes. Sometimes rare scenarios like once in two months I see the processes takes more than 5 to 8 hours to drain normal. All time I notice the load processor utilization and how it gradually splits its task and keep monitoring the process and innodb status and IO status. I need not do anything to bring it down. It servers the applications promptly and after some time it drains down to normal. Can you find anything suspicious in the url if any locks or OS waits any suggestion to initially triage with or what could have caused such spikes ?

http://tinyurl.com/bm5v4pl  -> "show innodb status \G and show open tables at DB spikes."

Also there are some concerns that I would like to share with you.

Recently I have seen a table that gets inserts only about 60 per second. It predominantly locks for a while waiting for auto-inc to get released. And thus subsequent inserts stays in the processlist tray. After a while the table gets IN_USE of about 30 threads and later I don't know what it makes to free them and clears the tray. (At this duration the load goes more than 15 for 5 minutes)
Suppose if you say application functionality should be shapped to best suite the DB server to react. There are 3 to 5 functionalities each are independent entities in schema wise. Whenever I see the locks it gets affected to all other schemas too.
Now what makes best fuzzy is the last one. I see slave keeps in synch with master with a delay of 0 second all time whereas slave has a single thread SQL operation that is passed from relay IO that which acts in FIFO model from the binary logs where Master had generated. When this single headed slave can keep the load less and have the operations upto-date, should the concurrent hits are really made to be concurrent for the functionalities which I assume making the possible IO locks in OS level. Can this be organized in application itself and keep the concurrent tenure density thinner?


texts: when is it necessary to create aliases in the SQL Configuration Manager?

texts:
I am having a problem with connectivity in one of my transactional replications and when checking the situation with the SQL Server configuration manager

I see there are aliases.
I was wonder why they have been created...

What aliases are used for in the SQL Server Configuration Manager?

Does it have anything to do that in this particular case the machines (publisher and subscriber) are on different domains?


texts: Test connection for linked server fails on desktop but not on server

texts:
On SQL Server 2012 I have a linked server to Oracle 12c. On the server a test connection succeeds. Security is set to be made using this security content with an Oracle user and no local logins are defined. On my desktop the test connection fails with

Cannot create an instance of OLD DB provider "OraOLEDB.Oracle" for linked server. (Microsoft SQL Server, Error: 7302)


texts: Queries that ran, and the number of times they ran

texts:
I want to profile a running web application in dev on a mysql 5.5 database.  The web application uses hibernate and I am able to see that a rather large number of queries are run against the database.

I'm able to see long running queries with logging, but that is not a concern as they are all running fairly quickly.

What I would like to know is, what queries are being run, and how many times they are run.


texts:
I have a SQL Server 2017 database that currently has an 80GB data file and a 90GB log file. I want to get the log file down to a more manageable size, because I am about to run some big scripts which will hit the log file hard. The database is also replicated to another database.
If I run dbcc sqlperf('LOGSPACE') then it tells me that utilisation of the log file sits at 99%, and this is preventing me from shrinking the log file down to a more manageable size. For instance, if I run dbcc shrinkfile(mydb_log, 4096) then the error is Cannot shrink log file 2 (mydb_log) because of minimum log space required.
I have tried backing up the database then backing up the transaction log, in full recovery mode. I have tried switching to simple recovery mode, then backing up the database then shrinking the database, but the log file utilisation still sits at 99%.
Does anyone have any suggestions as to what to try next? Is replication likely to be causing an issue? The databases currently sit on a test system, so I can take more extreme measures than if they were in production.


texts:
I'm looking to import large (100Mb -- 1 GB) multi-channel time-series data into a PostgreSQL database. The data comes from EDF format files that chunks the data into "records" or "epochs" of typically a few seconds each. Each epoch's record holds the signals for each channel of data as sequential arrays of short integers.

I'm mandated to store the files within the database, in the worst case as BLOBs. Given that, I'd like to investigate options that would allow me to do something more with the data within the database, such as facilitating queries based upon the signal data.

My initial plan is to store the data as one row per epoch record. What I'm trying to weigh up is whether to store the actual signal data as bytea or smallint[] (or even smallint[][]) types. Could anyone recommend one over the other? I'm interested in storage and access costs. Usage is likely to be insert once, read occasionally, update never. If one were more easily wrapped up as a custom type such that I could add functions for analysing of comparing records then so much the better.

No doubt I'm low on detail, so feel free to add comments on what you'd like me to clarify.


texts: How to query a database for empty tables

texts:
Due to some 'developers' we had working on our system we have had issues with empty tables.  We have found that during the transfer to the cloud several tables were copied, but the data in them wasn't.

I would like to run a query the system tables to find what user tables are empty.  We are using MS SQL 2008 R2.

Thanks for the help.


texts: Searching for pairs of date/time values in a single row

texts:
I have a legacy IBM DB2 database table that contains lots of records of phone calls.  It has columns for ID, customer number, employee who took the call, etc.  The date/time for each call is kept in a pair of columns, ROSDAT and ROSTIM.  ROSDAT is an 8 byte numeric column, and ROSTIM is a 6 byte numeric.  Example:

ROSDAT    ROSTIM
20111006  163243
20111007  012335

So for the first row the date is 2011-10-06, and the time is 16:32:43.  There are millions of records in this table, and I'm trying to make a query that will search it by time and date.  The only way I've found to do that so far is to concatenate the time/date columns into one big number like so:

select * from PHONELOGTABLE
where ROSDAT * power(10, 6) + ROSTIM >= 20111015124500
and ROSDAT * power(10, 6) + ROSTIM <= 20111116013000

This searches for everything from 2011-10-15 12:45:00 to 2012-11-16 01:30:00.  It works, but the concatenation means (as far as I know) that the indexes on the ROSDAT and ROSTIM columns cannot be used, so the query is slower than it should be.  It seems to me that there should be a way to do this with a join or something.

I tried this but couldn't get it to work, and I know I'm missing something:

select a.* from PHONELOGTABLE a join PHONELOGTABLE b on a.ID = b.ID
where a.ROSDAT >= 20111015 and b.ROSTIM >= 124500
and a.ROSDAT <= 20111116 and b.ROSTIM <= 013000

Anybody have an idea?


texts: How can I trace Oracle database transaction

texts:
I'm developping a JavaEE app deployed on IBM WebSphere 7.

We are facing performance issues, especially a high amount of transaction commits.
We are using JPA with Hibernate as a provider.

Does somebody know a way to trace the content of the DB transaction produced by Hibernate?

We know how many transactions we have globlally but we are not able to investigate the content of those transactions.


texts: Problem with sorting matrix groups in SSRS

texts:
I've hit the following issue in SQL Server 2005 Reporting Services

This is a slightly odd and complicated situation: I have a table containing various groups and within each group is also embedded a matrix. The rows of each matrix are sorted by a different field to their group expression. So far, so good.

The problem arises in the table header: it needs to mirror exactly the format of each group to give a "Grand Total" type functionality. I've copied exactly the matrix from the table's groups, but it refuses to sort the data correctly. I've tried changing the sort direction - even removing the sort altogether - but it makes no difference to the order in which the data is displayed.

Since I originally hit this issue, I've found a workaround involving simply grouping by the "sort" column - this works since it was only a calculate field created to sort the matrix groups.

Would still be interested to know why this is an issue for a matrix in the table header but not the group header.

Any ideas?


texts: Why do the databases go into the "recovery pending" state when the share permissions are changed from "everyone"?

texts:
Yesterday we had a power failure and after the power was back our financial databases on the SQL did fall into "Recovery Pending" state. After struggling a while I've remembered that a few days ago we did change the sharing permissions of the database directory to certain users from "everyone" and tried to revert the permissions back to "everyone" and everything was OK again in a few minutes.

What reminded me of this directory share permission change were these error messages in the event log:

  FileMgr::StartLogFiles: Operating system error 2(The system cannot
  find the file specified.) occurred while creating or opening file
  '\\SERVER\xxx.ldf'. Diagnose and correct the operating system error,
  and retry the operation.

  FCB::Open failed: Could not open file \\SERVER\xx._MS for file number
  1.  OS error: 5(Access is denied.).

Why did this happen?


texts: Why is my database 12 times bigger than inserted data?

texts:
I have a short question concerning my database size. I need to insert data in a database. Before the insert, some calculations need to be done.

The point is: from 50 mb plain data (~700,000 lines), this results in 600 mb db size. This is a factor 12! I am sure I am doing something wrong here. Could you help me to reduze the size of my db? Source of database size is the web postgres admin interface.

Here's the insert:

CREATE TYPE CUSTOMER_TYPE AS ENUM
('enum1', 'enum2', 'enum3', '...', 'enum15');       ## max lenght of enum names ~15

CREATE TABLE CUSTOMER(
   CUSTOMER_ONE    TEXT PRIMARY KEY NOT NULL,       ## max 35 char String
   ATTRIBUTE_ONE   TEXT UNIQUE,                     ## max 35 char String
   ATTRIBUTE_TWO   TEXT UNIQUE,                     ## max 51 char String
   ATTRIBUTE_THREE TEXT UNIQUE,                     ## max 52 char String
   ATTRIBUTE_FOUR  TEXT UNIQUE,                     ## max 64 char String
   ATTRIBUTE_FIFE  TEXT UNIQUE,                     ## 1-80 char String
   CUSTOMER_TYPE   PRIVATEKEYTYPE                   ## see enum
);

I don't really need that enum since I can insert it without, too. Does a enum have an effect on the database size?

Is there a way to reduce the size? Is it possible to reach factor x4 (instead of x12)? If not, I could delete some of the columns, if necessary.

Maybe there are other Postgres data types for character data?

After feedback here, my updated table looks now like this:

CREATE TABLE CUSTOMER(
   CUSTOMER_ONE    TEXT PRIMARY KEY NOT NULL,       ## max 35 char String
   ATTRIBUTE_ONE   TEXT UNIQUE,                     ## max 35 char String
   ATTRIBUTE_TWO   TEXT,                            ## max 51 char String
   ATTRIBUTE_THREE TEXT,                            ## max 52 char String
   ATTRIBUTE_FOUR  TEXT,                            ## max 64 char String
   ATTRIBUTE_FIFE  TEXT,                            ## 1-80 char String
   CUSTOMER_TYPE   PRIVATEKEYTYPE                   ## see enum
);

Before: 12x

Now: 7x :)

Are there any more possible optimizations? (Except deleting columns?) Maybe other data types using less space?


texts: Multiple m:m relationships LEFT JOIN giving duplicate records

texts:
DB noob here.

I have a table named invoice with a m:m relationship with a few other tables via junction tables:

invoice_customer
invoice_creator
invoice_deposit
invoice_withdrawal
invoice_payment_method

I'm trying to execute a query that will LEFT JOIN all of the data of these m:m relationships, however, seemingly at random, I'm getting duplicate results. Sometimes it gives the right amount, then when some state is modified it will give duplicates, and I can't seem to figure out why.

For example, this query gives me a result of "1" given I have one record matching the query:

SELECT COUNT(*)
FROM invoice
JOIN account_invoice
    ON account_invoice.account_id = ?
    AND account_invoice.invoice_id = invoice.id
WHERE invoice.deleted_at IS NULL

But this query gives me 4 duplicate records instead of 1:

SELECT invoice.*, account.*, customer.*, payment_method.*, deposit.*, withdrawal.*
FROM invoice
LEFT JOIN account_invoice
    ON account_invoice.invoice_id = invoice.id
LEFT JOIN account
    ON account.id = account_invoice.account_id
LEFT JOIN invoice_customer
    ON invoice_customer.invoice_id = invoice.id
LEFT JOIN customer
    ON customer.id = invoice_customer.customer_id
LEFT JOIN invoice_payment_method
    ON invoice_payment_method.invoice_id = invoice.id
LEFT JOIN payment_method
    ON payment_method.id = invoice_payment_method.payment_method_id
LEFT JOIN invoice_deposit
    ON invoice.id = invoice_deposit.invoice_id
LEFT JOIN transaction AS deposit
    ON deposit.id = invoice_deposit.transaction_id
LEFT JOIN invoice_withdrawal
    ON invoice.id = invoice_withdrawal.invoice_id
LEFT JOIN transaction AS withdrawal
    ON withdrawal.id = invoice_withdrawal.transaction_id
WHERE account.id = ?
    AND invoice.deleted_at IS NULL
ORDER BY invoice.id DESC
LIMIT ?,?


texts: I hit the EXEC sp_configure 'show advanced options', 1; But the "backup checksum default" is not showing

texts:
I followed the steps in Sql server 2012

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sys.sp_configure 'backup checksum default', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

I get the following error message:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 69
The configuration option 'backup checksum default' does not exist, or it may be an advanced option.

Please help me


texts: How to clear SQL Agent Job Scheduler Cache

texts:
I have to submit tickets to alter job scheduling, so rather than a walk through of the GUI I try to update msdb..sysschedules via tsql script.  I always see the alterations immediately via the GUI.  The first time I used this method though, it took a day for the changes to propagate.  More recently it's been longer.  I suspect something's cached in agent/scheduler, but I can't find documentation on what to do to flush this in the least intrusive manner on a production system.  I was hoping something rudimentary like disable/enable the job would trigger a refresh, but it did nothing and I'd rather issue something in T-SQL with my tickets to ensure changes get used.  I've requested a restart of agent when nothing's on the horizon to see if that works, but ideally would prefer something less intrusive.  This is a 2014 instance.

A sample script:

USE msdb;
update s set
    s.freq_subday_interval=3
from msdb..sysjobs j
    left join msdb..sysjobschedules js on j.job_id = js.job_id
    left join msdb..sysschedules s on js.schedule_id = s.schedule_id
    where j.name ='SomeDb.RunMeEvery3MinsBusta';

----edit-----

I guess taking from answers provided I tested below and it seems to cascade immediately.  Just wanted to make it a little more dynamic.

declare @jobid nvarchar(50), @scheduleid int
select @jobid=j.job_id,@scheduleid=s.schedule_id from msdb..sysjobs j
    left join msdb..sysjobschedules js on j.job_id = js.job_id
    left join msdb..sysschedules s on js.schedule_id = s.schedule_id
    where j.name ='test';

EXEC msdb.dbo.sp_attach_schedule @job_id=@jobid,@schedule_id=@scheduleid
EXEC msdb.dbo.sp_update_schedule @schedule_id=@scheduleid,
        @freq_subday_interval=1
GO


texts: In SQL Server 2016 what is sys.tables is_external field?

texts:
In SQL Server 2016 the sys.tables appears to have a new field called is_external.  But what does is_external actually mean and what is it used for?

MSDN says the following, which is not very helpful:

  Indicates table is an external table.
  0 = The table is not an external table.
  1 = The table is an external table.


texts:
I have strange problem, I have 3 SQL Server 2012 with Sp1 instances, and on all of them, the versions are identical.

I am trying to query Active Directory services, on 2 of them this is working correctly, but one of them is throwing an error

  Cannot get the column information from OLE DB provider "ADsDSOObject" for linked server "ADSI_TEST".

If I don't use Active Directory Server Name in the query it works fine.

Do you have any idea?


texts:
I have a problem with a database design:

I have an EReport. This EReport will have a QAPCategory.
User must select a QAPCategory from an set of QAPCategory.

To know which QAPCategory can be selected I have done this design:

Is this correct? Is this a circular reference?


texts: Why can't object names start with a number?

texts:
For example, if I'm creating view with a name '4aii', why does SQL Server care that it starts with a 4? I could call the table Fouraii or IVaii.

Additionally, what does [] do behind the scenes to allow for any string to be used as a name?

A string's a string, amirite?


texts: Dynamically changing an order number to end in _ERR

texts:
I'm trying to update multiple orders at once instead of doing them one at a time. Is there a way to dynamically update them all at once?

For example: I have 5 orders ABC, BCD, CDE, DEF, EFG.

I need to update them to look like this ABC_ERR, BCD_ERR, CDE_ERR, etc.


texts: Why is this select statement not returning what I've provided?

texts:
I have 3 tables in my SQL Server I am wondering why is my select statement not working or giving the result with my condition in the where clause. I learned back there to use this kind of method..

Subject table
SubjectID      CourseCode
   6            SubjectA
   7            SubjectB
   8            SubjectC

Schedule table
ScheduleID     Room
   15          Room1
   16          Room2
   17          Room3

AssignSchedule table
AssignScheduleID       ScheduleID       SubjectID
    14                     15               6
    15                     16               7

I want to get the room of the subject SubjectA. In order to get that I came up with this select statement query

Select Subject.CourseCode,
Schedule.Room from Subject,
Schedule, AssignSchedule
where
AssignSchedule.ScheduleID = (Select Schedule.Schedule.ID
from Schedule where Room = 'Room1')
AND AssignSchedule.SubjectID = (Select Subject.SubjectID from Subject where CourseCode = 'SubjectA');

What happens is like this

CourseCode      Room

  SubjectA      Room1
  SubjectB      Room1
  SubjectC      Room1
  SubjectA      Room2
  SubjectB      Room2
  SubjectC      Room2
  SubjectA      Room3
  SubjectB      Room3
  SubjectC      Room3

My expected output would be like this

CourseCode     Room
SubjectA       Room1

What is wrong? Help guys..