Manually documenting a database I am forced to manually document a legacy enterprise database. Are there some standard good practices or tools to do this?

To give you some more context, I have recently been given restricted access to a legacy enterprise database. There is very little documentation. I don't have permissions to view system tables or generate ER diagrams. I am exploring/using the database and manually documenting it as I go.

I would like to represent the schema with code (manually) in such a way that it can be parsed, say, with Python, to generate an ER diagram?

Are there tools to do this kind of thing? Or standard good practices to document the schema, annotate definitions and common keys etc?

I'm imagining some kind of a tree or graph like data structure that can be used to encode the schema, parsed with Python etc, and used to generate ERDs, etc, outside of the db management tool; (since I don't have permissions to do this inside the db management tool (Oracle SQL Developer)).

Since asking the question, I have found these two very nice web-based solutions:
https://dbdiagram.io/home
https://alternativeto.net/software/quickdbd/
and a basic one:
https://dshifflet.com/erd/index.html

Unfortunately due to security concerns, I am not allowed to use 3rd party cloud solutions. It's gotta be a standalone desktop software.

I also discovered ERAlchemy and it looks like I can manually generate ERDiagrams in Typora using markdown.

Any other useful ideas?

... manually document a legacy enterprise database

If this database is for a paid-for, Packaged solution, first get hold of the Licence Agreement between between your company and the Package [software] supplier.  It may well be that the "Reverse Engineering" of the database that you are being asked to perform is expressly forbidden by that licence.  It usually is.

If so, down tools immediately.

Assuming you're [legally] allowed to carry out this work ...

  ... restricted access ... I don't have permissions to view system tables or generate ER diagrams.

Get the tools and, more importantly, the permissions you need to do this job.
Without being able to query system catalogs and so discern table structures and relationships, you are working blind.

Indeed, with the [very] little you've been given to work with, I think you should deliver the diagram below as your entire "Documentation".

+---------------+
|               |
|    Package    |
| (proprietary) |
+---------------+
       |
       v
+---------------+
|               |
|   Database    |
| (proprietary) |
+---------------+

When they ask for more detail, ask them for more appropriate access to the database!


Concat a string to a variable to use as a column name in a query I have a procedure where I query table a and create variables like this
SELECT TRCODE , COLA , COLB , COLC , COLD
INTO TRANSACTION_TYPE , HOURS , RATE , FACTOR , APPROPRIATION
FROM PR . PREXEC
WHERE USRID = USER_NUMBER ;

The appropriation variable contains one character and it is a number from 1-4.
I have another table called pr.earnings. In that table I have a columns named APPROP1 through APPROP4. In this same procedure I need to query pr.earnings for by taking the string 'APPROP' and concatenating the APPROPRIATION variable from the query.
I tried
select concat('APPROP',APPROPRIATION)

FROM PR.EARNIGS;

The result I want is select APPROP1 FROM PR.EARNINGS; What happens is I get a system created column with APPROP1 inside it. I can't figure out how to concat a string to a variable to use in a query.
I tried a using dynamic sql but kept getting errors because I don't know what I'm doing.
How do I concat a string and a variable to use it as a column name in an sql query?
Based on what Lennart suggested I have this now
SET STMT = 'SELECT APPROP' || appropriation || ' pr.earnings into ' || active_appropriation ||
               ' where empnumber = ' || user_number;
    EXECUTE IMMEDIATE STMT

And am trying to insert active_appropriation like this
insert into pr.worksheet (W_EMPLOYEE_NUMBER, W_TRANSACTION_TYPE, W_HOURS, W_RATE, W_APPROPRIATION)
    values (user_number, transaction_type, hours, rate, active_appropriation);

I get the error 'Variable cannot be null' I am assuming it is the active_appropriation value.
I got it to work. here is my solution.
    SET STMT2 = 'VALUES (SELECT approp' || appropriation ||
    'FROM pr.earnings where empnumber = ' || user_number || ') INTO ?';
PREPARE S2 from stmt2;
EXECUTE S2 using active_appropriation;

The problem with your query is that you need to construct meta-data (name of a column) with data. You therefore need to first construct the query, and then execute it using dynamic SQL. Something like (untested)
CREATE PROCEDURE P
LANGUAGE SQL
BEGIN
    DECLARE STMT VARCHAR(100);
    DECLARE APPROP CHAR(1);

    SELECT APPROPRIATION INTO APPROP FROM ...;
    SET STMT = 'SELECT APPROP' || APPROP || ' FROM ... ';
    EXECUTE IMMEDIATE STMT;
END @

I used @ as a statement terminator. Note that the compiler has no way to verify that the query makes sense, so you may end up with run-time errors.
Debugging stored procedures
IBM Datastudio has a fully-fledged stored procedure debugger, i.e. you can set breakpoints, inspect variables, etc. If you for some reason does not want to install it (or like me, can not find the download page:-), you can use the DBMS_OUTPUT module:
~]$ # add some sample data
~]$ db2 "create table t (APPROPRIATION char(1) not null)"
~]$ db2 "insert into t (APPROPRIATION) values ('1')"
~]$ db2 "insert into t (APPROPRIATION) values ('2')"
~]$ db2 "insert into t (APPROPRIATION) values ('3')"
~]$ db2 "insert into t (APPROPRIATION) values ('4')"

~]$ cat p.sql
CREATE PROCEDURE P()
LANGUAGE SQL
BEGIN
    DECLARE STMT VARCHAR(100);
    DECLARE APPROP CHAR(1);

    SELECT APPROPRIATION INTO APPROP FROM T ORDER BY RAND() FETCH FIRST 1 ROWS ONLY;
    SET STMT = 'SELECT APPROP' || APPROP || ' FROM ...';
    call dbms_output.put_line('DEBUG: ' || STMT);
    -- EXECUTE IMMEDIATE STMT;
END @

~]$ db2 -td@ -f p.sql

~]$ db2 "SET SERVEROUTPUT ON"
DB20000I  The SET SERVEROUTPUT command completed successfully.
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP1 FROM ...
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP1 FROM ...
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP2 FROM ...
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP1 FROM ...
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP3 FROM ...

~]$ db2 "SET SERVEROUTPUT OFF"

Another option is to use an out variable:
~]$ cat p2.sql
CREATE PROCEDURE P2(OUT outstmt VARCHAR(100))
LANGUAGE SQL
BEGIN
    DECLARE STMT VARCHAR(100);
    DECLARE APPROP CHAR(1);

    SELECT APPROPRIATION INTO APPROP FROM T ORDER BY RAND() FETCH FIRST 1 ROWS ONLY;
    SET STMT = 'SELECT APPROP' || APPROP || ' FROM ...';
    SET outstmt = stmt;
    call dbms_output.put_line('DEBUG: ' || STMT);
    -- EXECUTE IMMEDIATE STMT;
END @

~]$ db2 -td@ -f p2.sql

~]$ db2 "CALL P2(?)"

  Value of output parameters
  --------------------------
  Parameter Name  : OUTSTMT
  Parameter Value : SELECT APPROP1 FROM ...

  Return Status = 0

One major drawback with both these methods is that they will not produce any output when you need it the most, i.e. when your procedure fails.
For future posts, always include a reproducible setup. Either include DDL (create table statements, etc) and SQL (insert table statements) or use a service like DB<>Fiddle.
Most people (including myself) usually just skip to the next question unless there an easy way to reproduce the problem. Not only will you get more answers, but they will also be ready to use since you defined the setup. As of now, if my answer is at all usable for you, you will have to adapt it to your actual setup since I invented some imaginary tables etc.

Does this work?
SELECT TRCODE , COLA , COLB , COLC , 'APPROP' || COLD
INTO TRANSACTION_TYPE , HOURS , RATE , FACTOR , APPROPRIATION
FROM PR . PREXEC
WHERE USRID = USER_NUMBER ;


The Service Broker endpoint is in disabled or stopped state - AoAG - SQL Server 2012 - 15 minute interval I receive this error message in SQL Server error log every 15 minutes. I have setup AOAG on this server. The database option "Service Broker - Broker Enabled" is turned on for the Blackberry related databases and MSDB databases on this instance. Blackberry support mentioned they do not need this enabled. I have other AOAG instance and they do not receive this error.

Please share your wisdom in this regard. I have not used service broker yet and did not find a similar post on stack exchange from my search.

Thank you

According to this link,

  It’s a default behavior. The message will show when AlwaysOn
  availability group Listener is created and SQL Server binds the
  Listener’s IP dynamically. There is no method to suppress the message.
  These messages are informational message and do not cause any harm and
  can safely be ignored.

That link also gives a query to see which databases do and do not have brokers enabled:

SELECT is_broker_enabled, name  FROM sys.databases

Please ensure [NT AUTHORITY\SYSTEM] is a sysadmin on all nodes of the availability group.

ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT AUTHORITY\SYSTEM];
GO


How to change SQL Server collation How could I change the SQL Server 2008 R2 Express Default Collation for the whole server and a particular database?

Is there a way to do it using visual interface of SQL Server Management Studio? In the Server Properties window (and in the corresponding Database Properties window), this property is not available for editing.

Yes.
You can change the default collation of SQL Server 2008 R2 express instance and individual databases, but it is a complex task.
Sadly, there is no visual option to do it via SSMS.

SQL Server 2008 supports setting collations at the following levels:

Server

Database

Column

Expression

The default installation settings are
determined by the Windows system
locale. The server-level collation can
either be changed during setup, or by
changing the Windows system locale
before installation. more...

Setting and Changing the Server Collation - SQL Server 2008

Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.

Export all your data using a tool such as the bcp Utility. For more information, see Importing and Exporting Bulk Data.

Drop all the user databases.

Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command

Create all the databases and all the objects in them.

Import all your data.

Setting and Changing the Database Collation - SQL Server 2008

Set the COLLATION option in the CREATE DATABASE statement while creating a new database.

Similarly, set the COLLATION options in the ALTER DATABASE statement to change the collation of an existing database.
ALTER DATABASE [database_name] COLLATE SQL_Latin1_General_CP1_CI_AS;

Setting and Changing the Column Collation

Some of the column collations will remain the same even after you alter the database collation. In that case, you have to modify the collation of the individual columns.

Exporting all of the data (including logins, linked servers, SQL Agent jobs, DB Mail settings, etc), and rebuilding the instance-level data, plus reloading all of the user data, is a lot of work. And, even after all of that, there's still no guarantee that you can update a database's default collation via ALTER DATABASE because there are several conditions that will prevent the operation from completing (please see the "Changing the Database Collation" section of the ALTER DATABASE documentation for details).

There is, however, an undocumented method that is much easier. The main drawback being that it's unsupported. This is not to say that anything will go wrong, just that if something does, Microsoft won't help fix it (because they never guaranteed that it would work).

The method I speak of is running sqlservr.exe with the -q {new_collation_name} switch. There's a little more to it than that, but that is the basic idea. This method simply updates the system meta-data, which has benefits and consequences, the main ones being:

BENEFITS

pretty fast
bypass most restrictions that prevent ALTER DATABASE from working
likely far more accurate than any script that people have come up with over the years to drop and recreate objects

DRAWBACKS

unsupported if something goes wrong
VARCHAR data can change, IF the code page is different between the old and new collations, and characters with values of 128 - 255 (0x80 - 0xFF) exists, and those characters do not exist as the same character with the same value on the new code page. So the potential is there for data loss, and your data needs to be research first to ensure that this condition does not exist. But, this also means that there are plenty of cases with only characters having values of 0 - 127 which are not in any danger, even if the code page changes.
User-Defined Table Types (UDTTs) are skipped and need to be updated manually.

For a detailed description of what the sqlservr.exe -q method does and does not do (including details on how collations work at the various levels, and potential issues to watch out for), please see my post:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

To change only the instance (including the system databases: master, model, msdb, and tempdb) and one or more databases (but not all databases), simply detach the database(s) that you want to exclude from this operation, and then re-attach them once the collation update completes.


"Cut" records from one table into another in one oracle sql statement I have a very big table T1 and i would like to copy every 15000 records to T2.
After that I want to delete these same records.
I want a fast and efficient way.
I tried this but it wouldn't work :

merge into T2 T
using (select col1,col2 from T1 where rownum<15000) m
on (1=1)
when matched then
insert values (m.col1,m.col2)
delete where 1=1;

It's giving me a syntax error. Any ideas??
Please help.
Thanks

Try (works on sql server)

INSERT T2 (
      col1,col2
) SELECT col1,col2
      FROM    (
           DELETE T1
           OUTPUT
                   DELETED.col1,
                   DELETED.col2
           WHERE rownum<15000
      ) AS RowsToMove;


Difference between commit and checkpoint I was confused about the difference between a commit operation and a checkpoint. I referred to some of the articles like This Article

But they are advanced for my understanding. I mean both appear to be same i.e. saving data like a "Save" action. If we rollback, we can rollback only to the most recent checkpoint/commit. Am I right in my statements? Can someone explain?

A commit is a user initiated action that tells the database (Oracle in this case) that the transaction is completed and that the changes may be committed and any locks/resources released. Normally the changes are committed to in memory data buffers and to the redo log buffer.

A checkpoint though is a database initiated action that writes all of the data to the actual physical disk file based on the changes recorded in the redo log buffer. Some good articles can be found here and here.

To complete some of the previous answers:

A commit will end the current transaction in the current section. It will guarantee the consistency of the data that was "touched" during the transaction.

A checkpoint writes all commited changes to disk up to some SCN that willl be kept in the control file and datafile headers. It guarantees the consistency of the database.

EDIT 1

As some of the comments point out that the concepts aren't completely clear I will provide some more information here.

We have 3 major structures involved in the commit and checkpoint concepts.

Memory (Database Buffer Cache & Redo Log Buffer)
Redo Logs (Redo log files)
Database (Datafiles)

So changes are made to blocks kept in the Database Buffer Cache (DBCache). Once commited, the changes are pushed to the Redo Log Buffer (RLB) which is dumped on a regular basis to the Redo Log Files (RLF) and, eventually to the database storage files (DF).

Also on a regular basis and not completely unrelated to commit, the checkpoint process dumps the dirty blocks  from the DBCache to permanent storage DF. During the checkpoint process the SCN associated with the latest DB block written to storage is written on the DF headers and the control file. That will be from that moment on the latest consistent estate of the database.


SQL query for FIFO allocation of items I have a real world business scenario but for the sake of clarity let me rephrase it as a simpler fictional problem.
We have Buyers and Items.
DECLARE @Buyer TABLE
(
 BuyerId INT IDENTITY(1,1),
 Name NVARCHAR(100),
 Budget INT
);

INSERT @Buyer (Name, Budget) VALUES
('Anna', 100),
('Brett', 50),
('Conor', 20)

DECLARE @Item TABLE
(
 ItemId INT IDENTITY(1,1),
 Cost INT
);

INSERT @Item (Cost) VALUES (50),(30),(20),(40),(10),(40),(30),(10),(5);

The goal is to craft a query (no loops or cursor) which allocates items to buyers, based on their budget.
The most expensive items should be allocated in priority.
Expected result:
ItemId BuyerName
1  Anna
4  Anna
6  Brett
7  NULL
2  NULL
3  Conor
5  Anna
8  Brett
9, NULL

From a logical perpective, this is what happens.
First, the items are sorted by descending cost
ItemId Cost
1   50
4   40
6   40
7   30
2   30
3   20
5   10
8   10
9   5

Then we go through each item and try to allocate it to a buyer that has enough budget left.

Item 1 => Can be assigned to Anna. Anna's remaining budget is 100 - 50 = 50
Item 4 => Can be assigned to Anna. Anna's remaining budget is 50 - 40 = 10
Item 6 => Can be assigned to Brett. Brett's remaining budget is 50 - 40 = 10
Item 7 => No one has enough budget left
Item 2 => No one has enough budget left
Item 3 => Can be assigned to Conor. Conor's remaining budget is 20 - 20 = 0
Item 5 => Can be assigned to Anna. Anna's remaining budget is 10 - 10 = 0
Item 8 => Can be assigned to Brett. Brett's remaining budget is 10 - 10 = 0
Item 9 => No one has enough budget left

Nasty, but will do what you wanted:
DECLARE @Out TABLE (BuyerId int, ItemId int);
DECLARE @BuyerId int, @Budget int, @ItemId int, @Cost int;

WHILE EXISTS(SELECT TOP 1 Budget FROM @Buyer WHERE Budget >= (SELECT MIN(Cost) FROM @Item))
BEGIN
    SELECT TOP 1 @BuyerId = BuyerId, @Budget = Budget
    FROM @Buyer
    WHERE Budget >= (SELECT MIN(Cost) FROM @Item)

    SELECT TOP 1 @ItemId = ItemId, @Cost = Cost
    FROM @Item
    WHERE Cost <= @Budget
    ORDER BY Cost DESC, ItemId ASC;

    DELETE FROM @Item WHERE ItemId = @ItemId;
    UPDATE @Buyer SET Budget -= @Cost WHERE BuyerId = @BuyerId;
    INSERT INTO @Out VALUES(@BuyerId,@ItemId)
END
SELECT * FROM @Out

UPDATE (2020-10-16):
Based on OPs comment I decided to try to do as much as possible using set based data flow. First of all solution requires to create all possible permutations without duplicates where the order doesn't matter. For that I used a recursive CTE, then out of all possible price sums we needed only these in the Buyers budget, then I ordered them form the most expensive set (as asked by OP) where we want most expensive item first then the most expensive still in the budget and so on. I ended up with having the order column, JSON array for ItemId and all other base data:
;WITH totalCostPermutations AS(
SELECT
ItemIndex = CAST(CONCAT('"',ItemId,'') as varchar(max)),
X = CAST(ROW_NUMBER() OVER (ORDER BY Cost DESC, ItemId ASC) as varchar(max)),
ItemId, Cost
FROM @Item
UNION ALL
SELECT
ItemIndex = CAST(CONCAT(i1.ItemIndex,'", "',i2.ItemId) as varchar(max)),
X = CONCAT(i1.X, ROW_NUMBER() OVER (ORDER BY i2.Cost DESC, i2.ItemId ASC)),
i2.ItemId, Cost = (i1.Cost + i2.Cost)
FROM totalCostPermutations i1
JOIN @Item i2 ON 1=1
AND i1.ItemId < i2.ItemId
)
SELECT
RN = ROW_NUMBER() OVER(PARTITION BY b.BuyerId ORDER BY Cost DESC, X ASC),
ItemId_JSON = CONCAT('{"ItemId":[',ItemIndex,'"]}'), TotalCost = Cost,  b.*
FROM totalCostPermutations c
JOIN @Buyer b ON c.Cost <= b.Budget;

At this stage our data looks like this:

But from this point I can't think of how could this be possible to tell which ItemId have already been "used" and which not, without looping.
Still, from this output it won't be hard to either loop it in T-SQL or via the app.

aka bin packing
There is a recent article on solving this type of problem here :-
https://www.sqlservercentral.com/articles/a-set-based-solution-for-the-bin-packing-problem


Distinct Random select Hi can anyone help with this little MS SQL Puzzle.

I basically need to get a 1 random answer from each of the submitters.

id,   submitter,   answer

1,    James,        Blue

2,    James,        Red

3,    James,        Orange

4,    Ian,        Yellow

5,    Ian,        Pink

6,    Robert,       Red

I have tried the following but obviously this will always get the min(id) or Max(id)  what I need is a random(id)?

select * from #tmpData
 where id in (
  select min(id ) FROM #tmpData
   group by submitter
            )

Thanks in advance..

Assuming a not very old version of SQL-Server, you can use ROW_NUMBER() to find one row per submitter:

; with r as
  ( select id, submitter, answer,
           rnd = row_number() over (partition by submitter order by newid())
    from #tmpData
  )
select id, submitter, answer
from r
where rnd = 1 ;

Test at: SQLfiddle

SELECT TOP 1 * FROM #tmpData
ORDER BY NEWID()

This should work. Let me know if there are further questions!


Database design for tracking referral dollars and referred dollars Dropbox has a great referral incentive program that gives a storage bonus based on whether a person is referred to Dropbox or refers others to Dropbox.  What data base design is good for managing information of this nature but with a slight twist.

Say that getting referred to a service gives you a fixed storage bonus of 100 MB.
Further, say that a referrer gets a storage bonus equivalent to the purchased amount by the referral.

So if Tom refers Jerry, Jerry gets 100 MB. If Jerry buys another 300 MB, Tom gets 300 MB of storage free of charge for as long as Jerry is paying for the incremental 300 MB.  Tom might also refer other people and get more free storage according to the same terms and Jerry can do the same.  Given that the people that are paying at any given time is changing, you need to track what each person is entitled to based on their referrals and the amount their referrals are purchasing at any given point in time.

Any suggestions?

It's a Multi-Level Marketing system!  Jeff Moden has written a a pair of articles here and here on efficient implementation of Hierarchical Reporting against a SQL Server database. There are a number of ways to store the hierarchical information but the two main ones are Adjacency List (each child has a parent foreign key) and Nested Sets (each parent stores details of its child hierarchy). Adjacency List is more intuitive and faster to update, while Nested Sets provides faster reporting.

Jeff has explained this topic far better than I can, and developed efficient SQL Server algorithms for converting an large Adjacency List tree into a Nested Set representation,

CREATE PROCEDURE dbo.RebuildNestedSets AS
/****************************************************************************
 Purpose:
 Rebuilds a "Hierarchy" table that contains the original Adjacency List,
 the Nested Sets version of the same hierarchy, and several other useful
 columns of data some of which need not be included in the final table.

 Usage:
 EXEC dbo.RebuildNestedSets

 Progammer's Notes:
 1. As currently written, the code reads from a table called dbo.Employee.
 2. The Employee table must contain well indexed EmployeeID (child) and
    ManagerID (parent) columns.
 3. The Employee table must be a "well formed" Adjacency List. That is, the
    EmployeeID column must be unique and there must be a foreign key on the
    ManagerID column that points to the EmployeeID column. The table must not
    contain any "cycles" (an EmployeeID in its own upline). The Root Node
    must have a NULL for ManagerID.
 4. The final table, named dbo.Hierarchy, will be created in the same
    database as where this stored procedure is present.  IT DOES DROP THE
    TABLE CALLED DBO.HIERARCHY SO BE CAREFUL THAT IT DOESN'T DROP A TABLE
    NEAR AND DEAR TO YOUR HEART.
 5. This code currently has no ROLLBACK capabilities so make sure that you
    have met all of the requirements (and, perhaps, more) cited in #3 above.

 Dependencies:
 1. This stored procedure requires that the following special purpose HTally
    table be present in the same database from which it runs.

--===== Create the HTally table to be used for splitting SortPath
 SELECT TOP 1000 --(4 * 1000 = VARBINARY(4000) in length)
        N = ISNULL(CAST(
                (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)*4+1
            AS INT),0)
   INTO dbo.HTally
   FROM master.sys.all_columns ac1
  CROSS JOIN master.sys.all_columns ac2
;
--===== Add the quintessential PK for performance.
  ALTER TABLE dbo.HTally
    ADD CONSTRAINT PK_HTally
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;

 Revision History:
 Rev 00 - Circa 2009  - Jeff Moden
        - Initial concept and creation.
 Rev 01 - PASS 2010   - Jeff Moden
        - Rewritten for presentation at PASS 2010.
 Rev 02 - 06 Oct 2012 - Jeff Moden
        - Code redacted to include a more efficient, higher performmance
          method of splitting the SortPath using a custom HTally Table.
****************************************************************************/
--===========================================================================
--      Presets
--===========================================================================
--===== Suppress the auto-display of rowcounts to prevent from returning
     -- false errors if called from a GUI or other application.

SET NOCOUNT ON;

--===== Start a duration timer
DECLARE @StartTime DATETIME,
        @Duration  CHAR(12);
 SELECT @StartTime = GETDATE();

--===========================================================================
--      1.  Read ALL the nodes in a given level as indicated by the parent/
--          child relationship in the Adjacency List.
--      2.  As we read the nodes in a given level, mark each node with the
--          current level number.
--      3.  As we read the nodes in a given level, convert the EmployeeID to
--          a Binary(4) and concatenate it with the parents in the previous
--          level's binary string of EmployeeID's.  This will build the
--          SortPath.
--      4.  Number the rows according to the Sort Path.  This will number the
--          rows in the same order that the push-stack method would number
--          them.
--===========================================================================
--===== Conditionally drop the final table to make reruns easier in SSMS.
     IF OBJECT_ID('FK_Hierarchy_Hierarchy') IS NOT NULL
        ALTER TABLE dbo.Hierarchy
         DROP CONSTRAINT FK_Hierarchy_Hierarchy;

     IF OBJECT_ID('dbo.Hierarchy','U') IS NOT NULL
         DROP TABLE dbo.Hierarchy;

RAISERROR('Building the initial table and SortPath...',0,1) WITH NOWAIT;
--===== Build the new table on-the-fly including some place holders
   WITH cteBuildPath AS
( --=== This is the "anchor" part of the recursive CTE.
     -- The only thing it does is load the Root Node.
 SELECT anchor.EmployeeID,
        anchor.ManagerID,
        HLevel   = 1,
        SortPath =  CAST(
                        CAST(anchor.EmployeeID AS BINARY(4))
                    AS VARBINARY(4000)) --Up to 1000 levels deep.
   FROM dbo.Employee AS anchor
  WHERE ManagerID IS NULL --Only the Root Node has a NULL ManagerID
  UNION ALL
 --==== This is the "recursive" part of the CTE that adds 1 for each level
     -- and concatenates each level of EmployeeID's to the SortPath column.
 SELECT recur.EmployeeID,
        recur.ManagerID,
        HLevel   =  cte.HLevel + 1,
        SortPath =  CAST( --This does the concatenation to build SortPath
                        cte.SortPath + CAST(Recur.EmployeeID AS BINARY(4))
                    AS VARBINARY(4000))
   FROM dbo.Employee      AS recur WITH (TABLOCK)
  INNER JOIN cteBuildPath AS cte
          ON cte.EmployeeID = recur.ManagerID
) --=== This final INSERT/SELECT creates the Node # in the same order as a
     -- push-stack would. It also creates the final table with some
     -- "reserved" columns on the fly. We'll leave the SortPath column in
     -- place because we're still going to need it later.
     -- The ISNULLs make NOT NULL columns
 SELECT EmployeeID = ISNULL(sorted.EmployeeID,0),
        sorted.ManagerID,
        HLevel     = ISNULL(sorted.HLevel,0),
        LeftBower  = ISNULL(CAST(0 AS INT),0), --Place holder
        RightBower = ISNULL(CAST(0 AS INT),0), --Place holder
        NodeNumber = ROW_NUMBER() OVER (ORDER BY sorted.SortPath),
        NodeCount  = ISNULL(CAST(0 AS INT),0), --Place holder
        SortPath   = ISNULL(sorted.SortPath,sorted.SortPath)
   INTO dbo.Hierarchy
   FROM cteBuildPath AS sorted
 OPTION (MAXRECURSION 100) --Change this IF necessary
;
RAISERROR('There are %u rows in dbo.Hierarchy',0,1,@@ROWCOUNT) WITH NOWAIT;

--===== Display the cumulative duration
 SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Cumulative Duration = %s',0,1,@Duration) WITH NOWAIT;

--===========================================================================
--      Using the information created in the table above, create the
--      NodeCount column and the LeftBower and RightBower columns to create
--      the Nested Sets hierarchical structure.
--===========================================================================
RAISERROR('Building the Nested Sets...',0,1) WITH NOWAIT;

--===== Declare a working variable to hold the result of the calculation
     -- of the LeftBower so that it may be easily used to create the
     -- RightBower in a single scan of the final table.
DECLARE @LeftBower INT
;
--===== Create the Nested Sets from the information available in the table
     -- and in the following CTE. This uses the proprietary form of UPDATE
     -- available in SQL Serrver for extra performance.
   WITH cteCountDownlines AS
( --=== Count each occurance of EmployeeID in the sort path
 SELECT EmployeeID = CAST(SUBSTRING(h.SortPath,t.N,4) AS INT),
        NodeCount  = COUNT(*) --Includes current node
   FROM dbo.Hierarchy h,
        dbo.HTally t
  WHERE t.N BETWEEN 1 AND DATALENGTH(SortPath)
  GROUP BY SUBSTRING(h.SortPath,t.N,4)
) --=== Update the NodeCount and calculate both Bowers
 UPDATE h
    SET @LeftBower   = LeftBower = 2 * NodeNumber - HLevel,
        h.NodeCount  = downline.NodeCount,
        h.RightBower = (downline.NodeCount - 1) * 2 + @LeftBower + 1
   FROM dbo.Hierarchy h
   JOIN cteCountDownlines downline
     ON h.EmployeeID = downline.EmployeeID
;
RAISERROR('%u rows have been updated to Nested Sets',0,1,@@ROWCOUNT)
WITH NOWAIT;

RAISERROR('If the rowcounts don''t match, there may be orphans.'
,0,1,@@ROWCOUNT)WITH NOWAIT;

--===== Display the cumulative duration
 SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Cumulative Duration = %s',0,1,@Duration) WITH NOWAIT;

--===========================================================================
--      Prepare the table for high performance reads by adding indexes.
--===========================================================================
RAISERROR('Building the indexes...',0,1) WITH NOWAIT;

--===== Direct support for the Nested Sets
  ALTER TABLE dbo.Hierarchy
    ADD CONSTRAINT PK_Hierarchy
        PRIMARY KEY CLUSTERED (LeftBower, RightBower) WITH FILLFACTOR = 100
;
 CREATE UNIQUE INDEX AK_Hierarchy
     ON dbo.Hierarchy (EmployeeID) WITH FILLFACTOR = 100
;
  ALTER TABLE dbo.Hierarchy
    ADD CONSTRAINT FK_Hierarchy_Hierarchy FOREIGN KEY
        (ManagerID) REFERENCES dbo.Hierarchy (EmployeeID)
     ON UPDATE NO ACTION
     ON DELETE NO ACTION
;
--===== Display the cumulative duration
 SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Cumulative Duration = %s',0,1,@Duration) WITH NOWAIT;

--===========================================================================
--      Exit
--===========================================================================
RAISERROR('===============================================',0,1) WITH NOWAIT;
RAISERROR('RUN COMPLETE',0,1) WITH NOWAIT;
RAISERROR('===============================================',0,1) WITH NOWAIT;

and then to report hierarchical subtotals (as you require here) from the Nested Set representation:

--===== Start a "Timer" to see how long this all takes.
DECLARE @StartTime DATETIME;
 SELECT @StartTime = GETDATE();

--===========================================================================
--      1.  Read ALL the nodes in a given level as indicated by the parent/
--          child relationship in the Adjacency List.
--      2.  As we read the nodes in a given level, mark each node with the
--          current level number.
--      3.  As we read the nodes in a given level, convert the EmployeeID to
--          a Binary(4) and concatenate it with the parents in the previous
--          level’s binary string of EmployeeID’s.  This will build the
--          SortPath.
--===========================================================================
--===== Conditionally drop the work table to make reruns easier in SSMS.
     IF OBJECT_ID('dbo.Hierarchy','U') IS NOT NULL
         DROP TABLE dbo.Hierarchy;

--===== Build the new table on-the-fly including some place holders
   WITH cteBuildPath AS
( --=== This is the "anchor" part of the recursive CTE.
     -- The only thing it does is load the Root Node.
 SELECT anchor.EmployeeID,
        anchor.ManagerID,
        HLevel   = 1,
        SortPath =  CAST(
                        CAST(anchor.EmployeeID AS BINARY(4))
                    AS VARBINARY(4000)) --Up to 1000 levels deep.
   FROM dbo.Employee AS anchor
  WHERE ManagerID IS NULL --Only the Root Node has a NULL ManagerID
  UNION ALL
 --==== This is the "recursive" part of the CTE that adds 1 for each level
     -- and concatenates each level of EmployeeID's to the SortPath column.
 SELECT recur.EmployeeID,
        recur.ManagerID,
        HLevel   =  cte.HLevel + 1,
        SortPath =  CAST( --This does the concatenation to build SortPath
                        cte.SortPath + CAST(Recur.EmployeeID AS BINARY(4))
                    AS VARBINARY(4000))
   FROM dbo.Employee      AS recur WITH (TABLOCK)
  INNER JOIN cteBuildPath AS cte
          ON cte.EmployeeID = recur.ManagerID
) --=== This final INSERT/SELECT creates an iterim working table to hold the
     -- original Adjacency List, the hierarchal level of each node, and the
     -- SortPath which is the binary representation of each node's upline.
     -- The ISNULLs make NOT NULL columns
 SELECT EmployeeID = ISNULL(sorted.EmployeeID,0),
        sorted.ManagerID,
        Sales      = ISNULL(CAST(0 AS BIGINT),0), --Place Holder
        HLevel     = ISNULL(sorted.HLevel,0),
        SortPath   = ISNULL(sorted.SortPath,sorted.SortPath)
   INTO dbo.Hierarchy
   FROM cteBuildPath AS sorted
 OPTION (MAXRECURSION 100) --Change this IF necessary
;
--===== You'll be tempted to add the following index because it seems so
     -- logical a thing to do for performance, but DON'T do it! It will
     -- actually slow the rest of the code down by a factor of 2!!!!
 --ALTER TABLE dbo.Hierarchy
 --  ADD CONSTRAINT PK_Hierarchy PRIMARY KEY CLUSTERED (EmployeeID)
--;
--===== Populate the Hierarchy table with current Sales data.
 UPDATE h
    SET h.Sales = s.Sales
   FROM dbo.Hierarchy h
  INNER JOIN dbo.CurrentMonthlySales s
     ON h.EmployeeID = s.EmployeeID
;
--===== Conditionally drop the final table to make reruns easier in SSMS.
     IF OBJECT_ID('dbo.PreAggregatedHierarchy,'U') IS NOT NULL
        DROP TABLE dbo.PreAggregatedHierarchy
;
--===== Now, build "Everything" into the PreAggregatedHierarchy table.
WITH
cteSplit AS
(--==== Splits the path into elements (including Sales and HLevel)
     -- so that we can aggregate them by EmployeeID and HLevel.
     -- Can't aggregate here without including the SortPath so we don't.
 SELECT EmployeeID = CAST(SUBSTRING(h.SortPath,t.N,4) AS INT),
        h.HLevel, h.Sales
   FROM dbo.HTally         AS t
  CROSS JOIN dbo.Hierarchy AS h
  WHERE t.N BETWEEN 1 AND DATALENGTH(SortPath)
),
cteAggregate AS
(--==== Creates the aggregates and introduces the "Relative Level" column.
     -- NodeCount = Count of nodes in downline for each EmployeeID by Level
     -- Sales = Total Sales in downline for each EmployeeID by Level.
 SELECT EmployeeID,
        HLevel,
        RLevel    = ROW_NUMBER() OVER (PARTITION BY EmployeeID
                                           ORDER BY EmployeeID, HLevel),
        NodeCount = COUNT(*),
        Sales     = SUM(CAST(Sales AS MONEY))
   FROM cteSplit
  GROUP BY EmployeeID, HLevel
)
--===== Adds a "Rollup" to create all the subtotals that we need.
     -- We couldn't do this in the previous step because we didn't know what
     -- the "Relative Level" was for each row, yet.
     -- The HAVING eliminates unnecessary subtotals that are created.
 SELECT EmployeeID = ISNULL(a.EmployeeID,0), --Convert NULL total lines to 0
        HLevel     = MIN(a.HLevel), --Just so we don't have to GROUP BY
        RLevel     = ISNULL(CAST(a.RLevel AS TINYINT),0),
        NodeCount  = SUM(a.NodeCount), --Just so we don't have to GROUP BY
        Sales      = SUM(a.Sales) --Just so we don't have to GROUP BY
   INTO dbo.PreAggregatedHierarchy
   FROM cteAggregate a
  GROUP BY EmployeeID, RLevel WITH ROLLUP
 HAVING EmployeeID > 0 --Eliminates the NULL total lines for cleaner output
;
--===== Add the Clustered Index as a Primary Key
  ALTER TABLE dbo.PreAggregatedHierarchy
    ADD CONSTRAINT PK_PreAggregatedHierarchy
        PRIMARY KEY CLUSTERED (EmployeeID, RLevel) WITH FILLFACTOR = 100
;
--===== Display how long it all took
  PRINT 'Duration: ' + CONVERT(CHAR(12),GETDATE()-@StartTime,114) + ' (hh:mi:ss:mmm)';

It's simple, no?  You have one table of users cust (id, mb), and one table of referrers, ref (id, ref_by) and a view

select c.id, c.mb + coalesce(sum(c2.mb), 0) as mb
from cust as c
left join ref  as r  on c.id = r.ref_by
     join cust as c2 on r.id = c2.id
group by c.id, c.mb

The rule could be more complicated.  For example, you might get 50% of the space for anyone you refer, and 25% for anyone they refer, etc.  If there's no fixed limit to the depth, you'd need a recursive query using a common table expression.  As expressed, though, a simple join will do.


Bypassing Password Verify Function I am considering making a recommendation to enforce a password verify function that will not allow users to change their own passwords.  My understanding of the PASSWORD_VERIFY_FUNCTION in Oracle is that the function only restricts the password criteria when the user changes his or her own password.  It would not restrict a privileged user from changing someone else's password or a third party IAM software from changing the password, but I cannot find that expressly documented.  Am I correct?

The PASSWORD_VERIFY_FUNCTION is used, even when a privileged user changes another user's password, see below.

SQL> @?/rdbms/admin/utlpwdmg.sql
SQL> create user u1 identified by Oracle123#;

User created.

SQL> select profile from dba_users where username = 'U1';

PROFILE
------------------------------
DEFAULT

SQL> select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';

LIMIT
----------------------------------------
VERIFY_FUNCTION_11G

SQL> show user
USER is "SYS"

SQL> alter user u1 identified by 1;
alter user u1 identified by 1
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 8

ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 16
If you are getting such a error, please  follow the below.

change the password with correct 16 character,will satisfy the password policy.
change the profile from default to user defined profile/or create new profile
alter user  profile .
now change the password as u wish, which not needed to full fill the password policy.
now change the profile  back to default.


Data Recovery in Mysql Let the data have been backed up at time interval of 2 hr and taking backup at time "A" and after 1 hr Database crashes so how can I recover in MySql from Logs.

First You have to restore your backup file which was taken at time A , then
you have to read all MySQL binary logs using mysql utility mysqlbinlog.

For example, if your log files were numbered 1 to 49 before the backup and logs 50 to 52 were written after the backup,you will need to process logs 50 to 52 after restoring the backup. If your binary logs are named with a prefix of bin, the log processing command looks like this:

shell> mysqlbinlog bin.000050 bin.000051 bin.000052 | mysql

All the binary log files that you want to process should be handled in a single mysqlbinlog command. There may be inter-file dependencies that will not be satisfied if you process them separately.

If a given binary log file was in the middle of being written during backup, you must extract from it only the part that was written after the backup, plus all log files written after that. Also, if you accidentally drop a table (or database, or some other act that causes data corruption), then you want to restore your backup and the incremental activity in binary logs.

If you do not want to re-execute the dropped table, you can run everything up to the bad command by capturing the binary log file only up to that point. To handle partial-file extraction, mysqlbinlog supports options that enable you to specify the time or log position at which to begin extracting log contents:

The --start-position option can be used to specify extraction beginning at a given log position.

After restoring a backup file, use the position numbers to process the binary log file. For example, you would use commands something like these:

mysqlbinlog --start-position=23456 binlog.000004 | mysql -uroot -p

It depends upon what backup method you maintain. How are you maintaining backups? Is it a full mysqldump or using binary logging? In your case I'd prefer opting for binary logging and you may take the the sqls triggered into binary log to construct your DR to the last point of your DB crash even if its too critical and no time eating process to build DR, you can go for Master/Slave still you might require binary logs for it.


How can I convert an Oracle dump file into SQL Server? I want to ask about converting Oracle dump files (.dmp) into SQL Server files (.bak) where during conversion I don't have to be connected to any database server.

I've searched for related technologies, such as Oradump to SQL Server. Do you have another suggestion to solve this? Open source ones I mean.

Thanks for both of your response.
I see how difficult it will, but is there any possibility to use another way in converting oracle dump file? because all of solution's converter tools always provide a connection database server. I'm so thankful what if you can suggest another tool. thanks anyway

"is there any possibility to use another way in converting oracle dump file?"

Import the dump file into Oracle. Transfer the data from Oracle to SQL Server using any number of tools (flat files, SSIS, linked servers, replication)

Forget about processing a dump file. The only thing that can process a dump file is Oracle. Or you can spend a year reverse engineering it then have to start over again when the format changes. If you want to convert Oracle data to SQL, you aren't going to be able to use a dump file unless you import into Oracle first.

I realize this post is over a year old, but there is a tool called OraDump-to-MSSQL by Intelligent Converters that will at least take an Oracle dump file and import it in to an MSSQL database. You do have to have the MSSQL server up and running but it will import in to a SQL Server Express database which is free to download and install.  You can get the tool here http://www.convert-in.com/ord2mss.htm.  It's $79 which is cheap.


List objects in SQL file We are trying to migrate a database from SQL 2014 to Azure SQL.

In SSMS, you can right click on the database and then on a task to "Deploy Database to Windows Azure SQL Database..".

This task fails because the database uses a SECONDARY filegroup. I've gone through the process to move all indexes, heaps, etc. out of the SECONDARY file group into the PRIMARY filegroup.

However, I still can't delete the file in the SECONDARY filegroup because there is 43MB of data in it. Every script I've found to list all the objects in a filegroup say there is nothing remaining in the file.

How can I determine what's still in this filegroup so I can delete the file and remove the SECONDARY filegroup?

I found this script which lists LOB objects in each filegroup. Based on the results I was able to find the table still using the SECONDARY filegroup.

SELECT
    au.*,
    ds.name AS [data_space_name],
    ds.type AS [data_space_type],
    p.rows,
    o.name AS [object_name]
FROM sys.allocation_units au
    INNER JOIN sys.data_spaces ds
        ON au.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p
        ON au.container_id = p.partition_id
    INNER JOIN sys.objects o
        ON p.object_id = o.object_id
WHERE au.type_desc = 'LOB_DATA'

Can you post the result of the following sql statement?

SELECT o.[name], o.[type], o.[type_desc], i.[name], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id
AND i.data_space_id > 1
GO


Who or what initiated the failover We have our clusters set to High Availability and manual failover. I have an alert to email DBAs whenever a failover occurs. What we would like to also see in the email is WHY and/or WHO. We have other teams in there that restart, test and delete things all the time and we would like to know if this is someone working on something vs something wrong. Right now the email is "The availability group database "TESTIN2" is changing roles from "PRIMARY" to "RESOLVING" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required."
Thanks

What we would like to also see in the email is WHY and/or WHO.

Then you have quite a good bit of development work ahead of you. Seriously.

Everyone asks this same question, but it is not an easy one to answer with any more resolution than "It failed over at this time." Things such as virtualization make this even harder as the tools and the hypervisor itself may do things that are outside the purview of your sandbox.

However, to get you started, places to scrub for data:

SQL Server Errorlog
Application Event Log
System Event Log
Cluster Log/Clustering Event Log
Hypervisor Logs/Database
Internal Lists of Downtimes/Upgrades/Patches/Etc.

Best of luck.

  We have other teams in there that restart, test and delete things all the time and we would like to know if this is someone working on something vs something wrong.

That's an organizational information sharing/Administration issue and not one you're going to have much help in dealing with through a simple SQL Server Alert. In fact, to do this properly, you'll want to take SQL Server out of the picture!

Most likely you're going to need to write a service that constantly consumes event logs, traces, error logs, etc., and then takes action when issues occur.

If there is a manual failover then below log gets captured in SQL error logs

Message
The state of the local availability replica in availability group 'XXXXXXXXXXX' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_PENDING_FAILOVER'.  The state changed because of a user initiated failover.  For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.

But if you further look into Event viewer and AlwaysOn health events, besides below nothing gets captured and that sucks because if you are looking to find the person who initiated the fail over there is just no other way

AlwaysOn Health Events

statement   ALTER AVAILABILITY GROUP [XXXXXXXXXXX] FAILOVER;

Event Viewer
The state of the local availability replica in availability group 'XXXXXXXXXXX' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_PENDING_FAILOVER'.  The state changed because of a user initiated failover.  For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.


Steps to upgrade MySQL from version 5.0 to version 5.5 I have been developing under MySQL v5.0 for a while now and need to upgrade to v5.5 to be in step with the hosted production server.

After having searched for precise steps to do this migration, I am still unclear as to how to go ahead.

Can anyone provide a clear list of actions needed to upgrade to version 5.5 ?

Thank you

Thank you for these answers.
You are right, I should have given fuller info in my question.
Notably - I am under Windows XP SP3.

Yes I did search through Google beforehand, hence my statement

  "After having searched for precise steps to do this migration,
  I am still unclear as to how to go ahead."

To RolandoMySQLDBA : Thanks for the script, but I cannot see how I could adapt it.

Step 01 : Run this script to Dump Everything from MySQL 5.0

cd /root
MYSQL_CONN="-uroot -p..."
SQLSTMT="SELECT schema_name FROM information_schema.schemata WHERE"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')"
MYSQL_OPTIONS="--skip-column-names -A"
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} -e"${SQLSTMT}" > /tmp/dblist.txt
DB_OPTIONS="--databases"
for DB in `cat /tmp/dblist.txt` ; do DB_OPTIONS="${DB_OPTIONS} ${DB}"
MYSQLDUMP_OPTIONS="--routines --triggers ${DB_OPTIONS}"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > MySQLData.sql
SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''"
SQLSTMT="${SQLSTMT}@''',host,''';') FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} -e"${SQLSTMT}" > GetGrants.sql
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} < /root/GetGrants.sql | sed 's/$/;/g' > Grants.sql
rm -f GetGrants.sql

Step 02 : Backup config file

cp /etc/my.cnf /etc/my.cnf.50

Step 03 : Backup /var/lib/mysql

mv /var/lib/mysql /var/lib/mysql50

Step 04 : Uninstall MySQL 5.0

Step 05 : Install MySQL 5.5

Step 06 : service mysql start

Step 07 : Login to mysql

mysql -uroot

Step 08 : At the mysql prompt enter the following:

mysql> source /root/Grants.sql
mysql> source /root/MySQLData.sql

That's it.

Give it a Try !!!

I have suggested this before : MySQL upgrade 5.0.88 to latest

You need to follow the steps described in these documentations:

Upgrading from MySQL 5.0 to 5.1
Upgrading from MySQL 5.1 to 5.5


How can i fix the Oracle Enerprise Manager error - Agent is blocked? I have installed OEM 12c and after restarting my oms the agents got the following error:

Heartbeat Status : Agent is blocked
Blocked Reason : Plugin mismatch found between agent and repository.

I have restarted the agent but sill no success.
Any ideas there ?

I suggest you re-sync your agent using the EM GUI !
Here is a article showing how this is done !

In short :

Check your agent status :

./emctl status agent

Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 12.1.0.3.0
OMS Version : (unknown)
Protocol Version : 12.1.0.1.0
Agent Home : /em_agent12.3/agent_inst
Agent Binaries : /em_agent12.3/core/12.1.0.3.0
Agent Process ID : 64164
Parent Process ID : 64110
Agent URL : https://bih002:3872/emd/main/
Repository URL : https://dcg023:4900/empbs/upload
Started at : 2013-11-13 15:26:08
Started by user : em_user
Last Reload : (none)
Last successful upload : (none)
Last attempted upload : 2013-11-13 15:26:14
Total Megabytes of XML files uploaded so far : 0
Number of XML files pending upload : 72
Size of XML files pending upload(MB) : 0.11
Available disk space on upload filesystem : 91.16%
Collection Status : Collections enabled
Heartbeat Status : **Agent is blocked**

Next re-sync your agent using the GUI:

Now check you agent status again!

Deploy plugin to agent.

Go to Setup -> Extensibility -> Plugins -> Databases
Deploy On -> Management Agent (add your agent)
.. wait for a while.


Convert table from MyISAM to InnoDB and keep MyISAM rows order I'm currently rewriting old project and I've got this strange scenario.

It's an online store, it has products with images. The images are stored in separate table. Here is example query. The part after the id- in the img column is the same for every row for pid.

I want to migrate this table to InnoDB, but keeping this order. InnoDB orders by id by default.

SELECT * FROM products_gallery WHEREpid= :productID

This is MyISAM

There is no particular order, probably they are stored in the way the are inserted, altrought if that was the case they should be ordered by id. I don't know that is really happening, but I think MyISAM has it's own order then no ordering is specified. I want to migrate this to InnoDB and keep this same order.

The only solution coming to my mind is to go trough product images before changing the storage engine and record the position rows are returned. After that I'll add new column to the table position and update it with the recorded position and sorting by position after migration.

So my question is there any easier solution?

@a_vlad has your answer... create a new table with the same structure without keys and autoincrement (it is faster to manage indexes in batch than one row at a time).  Then insert into the new table select * from the old.  After population, any indexes (including the pk) can be applied and add autoincrementation advanced to the next value you want to generate.


How to determine if an Index is required or necessary I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now have a list of recommendations for indexes that need creating.

Edit: The Indexes described above take information from the DMV's that tell you what the database engine would use for indexes if they were available and the scripts take the Top x recommendations (by seeks, user impact etc.) and put these in a table.

(Edit above partially taken from Larry Coleman's answer below in order to clarify what the scripts are doing)

As I am new to database admin, and having had a quick search around the net, I am reluctant to take the plunge and blindly add the recommended indexes. However, not being experienced in  the field, I am looking for some advice on how to determine whether the recommendations are necessary or not.

Do I need to run the SQL Profiler, or is it better to examine the code that queries the tables? And do you have any other advice?

I use Jason Strate's index analysis scripts (Old location). They tell you how much your existing indexes are used as well as how much missing indexes would have been used. I typically don't add indexes unless they make up more than 5 or 10% of the queries on a table.

Most importantly, though, it's about making sure the application responds fast enough for the users.

Update: Jason Strate's index analysis blog articles for newer scripts (New location)

Double Update: These days, I use sp_BlitzIndex® when performing index analysis.

It depends on how that table is used. e.g. lets say I've a table which is read a lot of times but updates and inserts are rare. Plus I always query the table on some foreign key column. It will make sense to create (non clustered) index over that foreign key to speed up read queries. But the downside is, your insert,update will become slow.

There are few statistics queries which tell how much time queries are taking. Start with slowest ones. If the query predicate has no index, creating one will help.


Large transactional log won't shrink after transactional backup I'll start by saying our databases use full recovery mode and we run SQL Server 2014.

Over the last few days our backups appeared to spiral out of control to the point that our full/differential backups were failing and only extremely large transactional backups were getting created. This of course caused a disk space issue and required a fair bit of manual intervention to restore order.

I manually ran a full backup on each database and then tried to follow up by running a transaction backup. During this period, one database's log file was increasing in size to the point that it ran out of disk space. Usually the log file would be around 1 GB, it is now sitting at about 30 GB.

Running a transactional backup did not reduce the size of the log file and I was stuck in a position where the log file was full and there was no room left on the disk. As a last ditch effort, I ran the SHRINKFILE( dbname, 4096) which instantly reduced the size and all appeared to be normal.

After doing so, I noticed my backups were no longer working and I was receiving an error:

Data error (cyclic redundancy check) failed.
I tried running CheckDB and was presented with the following message:

  A severe error occurred on the current command. The results, if any, should be discarded.

Given I was unable to back the database up, I decided to try and restore from earlier in the day off a full backup and 1 single transactional backup I had created. Both these files were large. The reason I only had 1 transactional backup was because I had disabled the job from running to avoid things spiralling further out of control.

New DB is up and running and backups work fine, but the issue of the large log file still prevails. My transactional backups are very small now but I can't seem to shrink the DB.

Anyone have any ideas? Alternatively, if there's a way to resolve the redundancy issue on the original database that would be a preferable solution as I'd have no data loss then.

No open transactions. The log_reuse_wait_desc is "LOG_BACKUP" which my understanding means that it is waiting for a log backup to run. This is madness because I have done several ;) dbcc sqlperf(logspace) is claiming I'm using 92% space.

Log backups run every 15 minutes in normal circumstances, but like I mentioned, these had been failing over the weekend. I've turned them off for the time being to stop the drive filling up and have just been manually running transactional backups. The database is not live at the moment so no additional data is going in there, I've run maybe 4 transactional backups as a test in the last hour or so.

None of replication, mirroring, or AOAG is configured here.

What is the frequency of the log backups ?

Use dbcc sqlperf(logspace) to check how much % is free space in the logs.

There is something that is holding the logs up. Check the log_reuse_wait_desc for that database and take action accordingly.

Follow the below article :

https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/

Put the database in single recovery mode.
Shrink the transaction log.
Put the database back into full recovery mode.
Make a full database backup.
Start again your transactional backups regular jobs.

PS : Also make sure your transactional backups are not "copy-only" tagged.


SQL Server FCI won't start after OS Upgrade I have a two node sql server 2008 r2 cluster that we just did an OS upgrade from windows 2008 r2 to windows 2012 r2, the OS upgrade was done in place so the SQL Server Installation stayed in tack.  The cluster role won't come back online saying it's couldn't find any IP address that this instance depends on.

Everything comes on line except SQL Server resource and looking in the sql server log file it shows that error sql server is starting up as I can see master and model get started up.

From the sql log:

  Could not find any IP address that this SQL Server instance depends upon.  Make sure that the cluster service is running, that the dependency relationship between SQL Server and Network Name resources is correct, and that the IP addresses on which this SQL Server instance depends are available.  Error code: 0x5.

Are there additional steps that need to be taken with Windows Server 2012 r2 that need to be done to get SQL Server 2008 r2 to be able to run in a failover cluster?

Prior to Windows Server 2012 R2, doing an in-place upgrade of Windows for an FCI is not supported. Per Microsoft's documentation:

  Upgrading the Windows operating system of a failover cluster is not
  supported for operating systems before Windows Server 2012 R2. To
  upgrade a cluster node running on Windows Server 2012 R2, see
  Cluster Operating System Rolling Upgrade.

Because your starting OS was Windows Server 2008 R2, you are unable to do an in-place upgrade to Windows Server 2012 R2. Instead, you will need to build a new Windows Cluster, do a fresh SQL install for a new FCI, then migrate to that new FCI.

You can do this using existing hardware by removing a node from the cluster, doing the fresh install of both Windows & SQL, then repeating the process one node at a time to move servers from the old cluster to the new cluster.

Once you are upgraded to Windows Server 2012 R2, you will be able to do in-place OS upgrades in the future.

Given that you've already upgraded the OS, your best bet is going to be to uninstall SQL Server, and re-install the FCI from scratch.


Are these MySQL queries efficient? It took me quite a while to learn how to deal with timestamps in MySQL and I think I accomplished my goals, but I want to double-check two things:

1) Are these doing what I expect

2) Are there faster replacements for these queries / functions

Today

SELECT COUNT(*) AS count FROM log WHERE DATE(datet) = DATE(NOW())

This Week

SELECT COUNT(*) AS count FROM log WHERE YEARWEEK(NOW()) = YEARWEEK(datet)

Last Week

SELECT COUNT(*) AS count FROM log WHERE YEARWEEK(datet) = YEARWEEK(DATE_SUB(NOW(), INTERVAL 1 WEEK))

This Month

SELECT COUNT(*) AS count FROM log WHERE datet > DATE_SUB(NOW(), INTERVAL 1 MONTH)

Last Month

SELECT COUNT(*) AS count FROM log WHERE datet > DATE_SUB(NOW(), INTERVAL 2 MONTH) AND datet < DATE_SUB(NOW(), INTERVAL 1 MONTH)

The queries where you have your datet column wrapped within a function (e.g. DATE(), YEARWEEK()) are not efficient due to the fact that MySQL has to evaluate the output of the function for each row in your table. This renders the query as non-sargable (i.e. not able to utilize indexes on the datet column).

It appears you want to select the count of all records within a particular timeframe. To do this, you have to use >=, not =. Also, there is a difference between "this month" and "within one month". I'm assuming you want the former (i.e. if say today was August 3rd, you'd only want records within August, and not from July 3rd, and same thing for week, etc...).

Assuming datet is a DATETIME/TIMESTAMP column and the values of the column will never be "in the future", you can rewrite your queries like so (each of which can utilize an index on the datet column):

-- Today
SELECT COUNT(*) AS count
FROM   log
WHERE  datet >= CURDATE()

-- This Week
SELECT COUNT(*) AS count
FROM log
WHERE datet >= CURDATE() - INTERVAL CASE WEEKDAY(CURDATE()) WHEN 6 THEN -1 ELSE WEEKDAY(CURDATE()) END + 1 DAY

-- Last Week
SELECT COUNT(*) AS count
FROM log
WHERE datet >= (CURDATE() - INTERVAL CASE WEEKDAY(CURDATE()) WHEN 6 THEN -1 ELSE WEEKDAY(CURDATE()) END + 1 DAY) - INTERVAL 1 WEEK
      AND
      datet < CURDATE() - INTERVAL CASE WEEKDAY(CURDATE()) WHEN 6 THEN -1 ELSE WEEKDAY(CURDATE()) END + 1 DAY

-- This Month
SELECT COUNT(*) AS count
FROM   log
WHERE  datet >= CURDATE() - INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY

 -- Last Month
SELECT COUNT(*) AS count
FROM log
WHERE datet >= (CURDATE() - INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY) - INTERVAL 1 MONTH
      AND
      datet < CURDATE() - INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY

-- This Year
SELECT COUNT(*) AS count
FROM   log
WHERE  datet >= MAKEDATE(YEAR(CURDATE()), 1)

I don't know if it's faster, but here's some alternatives :

This Month

SELECT COUNT(*) AS count FROM log WHERE MONTH(datet) = MONTH(NOW())

Last Month

SELECT COUNT(*) AS count FROM log WHERE MONTH(datet) = (MONTH(NOW()) - 1)


pgpool replication not allowing md5 authentication I have installed both pgpool and postgresql via apt.

In pgpool.conf, I have made the changes necessary to use replication mode, as well as my two postgresql databases (currently all running on the same host).

I am unable to figure out what I need to add to my pg_hba.conf files in order to have pgpool connect to them. I keep getting this error

createdb: could not connect to database postgres: ERROR:  MD5 authentication
is unsupported in replication, master-slave and parallel modes.
HINT:  check pg_hba.conf

both of my pg_hba.conf files look like

local   all             postgres                                peer

local   all             all                                     peer
local   all             postgres                                md5

host    all             all             127.0.0.1/32            md5

host    all             all             ::1/128                 md5

local   replication     postgres                                peer
host    replication     postgres        127.0.0.1/32            md5

and my pool_hba.conf looks like

local   all         all                               trust
local   all         postgres                          md5
local   all         all                               md5

host    all         all         127.0.0.1/32          trust

What am I missing in order to have pgpool connect to my databases?

It looks like your default authentication mode for TCP/IP connections (host) is md5. So at a guess, you have PgPool connecting over TCP/IP.

Change the auth mode for loopback connections on tcp/ip, or use unix sockets with pgpool (usually by omitting the host setting).

If you're still stuck after reading:

19.1. The pg_hba.conf File and
Chapter 19. Client Authentication

in the PostgreSQL 9.4 documentation, and you can't find answers elsewhere (there are lots of related questions on Stack Overflow), let me know and I'll give you a step by step if I have a chance.

I was also facing same issue. I just do some setting in pool_hba.conf
and add md5 encrypted password in pcp.confi & pool_passwd file
after that reload the pool setting and start it again.
After these setting. MY application is working fine.


Why are [Seemingly] suitable indexes not used on a LEFT JOIN with OR I have the following [fairly meaningless, just for the purpose of demonstration] query in the StackOverflow database:

SELECT  *
FROM    Users u
        LEFT JOIN Comments c
            ON u.Id = c.UserId OR
               u.Id = c.PostId
WHERE   u.DisplayName = 'alex'

The only index on the Users table is a clustered index on ID.

The Comments table has the following Non-Clustered indexes as well as Clustered Index on ID:

CREATE INDEX IX_UserID ON Comments
(
    UserID,
    PostID
)

CREATE INDEX IX_PostID ON Comments
(
    PostID,
    UserID
)

The estimated plan for the query is here:

I can see the first thing the optimizer will do is perform a CI scan on the users table to filter only those users where DisplayName = Alex, effectively doing this:

SELECT  *
FROM    Users u
WHERE   u.DisplayName = 'alex'
ORDER BY Id

and retreiving results as such:

Then it will scan the comments CI and for every row, look to see if the row satisfies the predicate

u.Id = c.UserId OR u.Id = c.PostId

Despite the two indexes, this CI scan is performed.

Wouldn't it be more efficient if the optimizer did a separate seek on each of the indexes in the Comments table above and join them together?

If I visualise what that would look like, in the screenshot above we can see the first result of the Users CI scan is ID 420

I can visualize what the IX_UserID Index looks like using

SELECT      UserID,
            PostID
FROM        Comments
ORDER BY    UserID,
            PostID

so if I seek to the rows for user ID 420 as an index seek would:

for every row where UserID = 420, I can look if  u.Id = c.UserId OR u.Id = c.PostId of, course they all match the u.Id = c.UserId part of our predicate,

So for the second part of our index seek, we can seek through our index IX_PostID which can be visualised as follows:

SELECT      PostID,
            UserID
FROM        Comments
ORDER BY    PostID,
            UserID

If I seek to Post ID 420 I can see nothing is there:

So we then go back to the results of the CI scan, move to the next row (userId 447) and repeat the process.

The behaviour I have described above is possible using in a WHERE clause:

SELECT      UserID,
            PostID
FROM        Comments
WHERE       UserID = 420 OR PostID = 420
ORDER BY    UserID,
            PostID

Plan here

My question therefore is, why isn't an OR condition in a JOIN clause able to perform an index seek on appropriate indexes?

Rather than focusing on how to improve a query like this, which is what the other answers are doing, I'm going to try to answer the question being asked: why doesn't the optimizer produce a plan like the one you've described (that scans the Users table, and then seeks into the two indexes on the Comments table).

Here's your original query again (note I'm using MAXDOP 2 just to simulate what I saw in your execution plans):

SELECT  *
FROM    Users u
        LEFT JOIN Comments c
            ON u.Id = c.UserId OR
               u.Id = c.PostId
WHERE   u.DisplayName = 'alex'
OPTION (MAXDOP 2);

And the plan:

Scan of dbo.Users with residual predicate to get just the "alex" users
For each of those users, scan the dbo.Comments table and filter matches in the join operator
Estimated cost: 293.161 optimizer units

One attempt to get the plan you want would be to try and force a seek on the dbo.Comments table:

SELECT  *
FROM    Users u
        LEFT JOIN Comments c WITH (FORCESEEK)
            ON u.Id = c.UserId OR
               u.Id = c.PostId
WHERE   u.DisplayName = 'alex'
OPTION (MAXDOP 2);

The plans looks like this:

scan of the dbo.Users table (with a residual predicate to only get users named "alex"),
seek into each of the two indexes to get the requested Id values (which are unioned together)
followed up by a key lookup to get the rest of the columns (since we selected *)
Estimated cost: 5.98731 optimizer units

So the answer is that the optimizer is definitely capable of producing such a plan.  And it doesn't seem to be a cost-based decision (the seek plan looks much cheaper).

My best guess is that this is just some kind of limitation in the optimizer's exploration process - it doesn't seem to favor converting a left join with an or clause into an apply.   This is really unfortunate in this particular case, as performance is dismal in the scan plan (the query takes 45 seconds on my machine)  vs the apply plan (less than 1 second).

Side note: You can override the heuristic that disfavors index union plans with undocumented trace flag 8726. See https://dba.stackexchange.com/a/23779 for additional details on that front!

As Rob Farley helpfully pointed out, using APPLY directly (potentially with a UNION as well) is a better approach to get the plan you're looking for - both of those produce the "better" version of this plan (the FORCESEEK version).  I would say that "OR in a JOIN" is kind of a known anti-pattern, and should be avoided since it doesn't seem like the optimizer has great support for that type of query directly.

In such case I prefer doing something like this:

SELECT  u.*
        ,c.*
        ,p.*
FROM Users          u
LEFT JOIN Comments  c ON u.Id = c.UserId
LEFT JOIN Comments  p ON u.Id = c.PostId
WHERE   u.DisplayName = 'alex'

And then use fields from c and p tables depending of task.


What is faster, one big query or many small queries? I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then in the application sometimes, we only need to use only 1 column.
So would it be faster to just make simple selects, and then "join" them in the system code?
The system could be in php, java, asp, or any language that connect to the database.
So the question is, what is faster going from a server side (php, java, asp, ruby, python...) to the database and running one query that gets everything we need or going from the server side to the database and running a query that only gets the columns from one table at a time?

What would address your question is the subject JOIN DECOMPOSITION.
According to Page 209 of the Book

You can decompose a join by running multiple single-table queries instead of a multitable join, and then performing the join in the application. For example, instead of this single query:
SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id = tag.id
JOIN post ON tag_post.post_id = post.id
WHERE tag.tag = 'mysql';

You might run these queries:
SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

Why on earth would you do this ? It looks wasteful at first glance, because you've increased the number of queries without getting anything in return. However, such restructuring can actually give significant performance advantages:

Caching can be more efficient. Many applications cache "objects" that map directly to tables. In this example, if the object with the tag mysql is already cached, the application will skip the first query. If you find posts with an ID of 123, 567, or 908 in the cache, you can remove them from the IN() list. The query cache might also benefit from this strategy. If only one of the tables changes frequently, decomposing a join can reduce the number of cache invalidations.
Executing the queries individually can sometimes reduce lock contention
Doing joins in the application makes it easier to scale the database by placing tables on different servers.
The queries themselves can be more efficient. In this example, using an IN() list instead of a join lets MySQL sort row IDs and retrieve rows more optimally than might be possible with a join.
You can reduce redundant row accesses. Doing a join in the application means retrieving each row only once., whereas a join in the query is essentially a denormalization that might repeatedly access the same data. For the same reason, such restructuring might also reduce the total network traffic and memory usage.
To some extent, you can view this technique as manually implementing a hash join instead of the nested loops algorithm MySQL uses to execute a join. A hash join might be more efficient.

As a result, doings joins in the application can be more efficient when you cache and reuse a lot of data from earlier queries, you distribute data across multiple servers, you replace joins with IN() lists, or a join refers to the same table multiple times.
OBSERVATION
I like the first bulletpoint because InnoDB is a little heavy-handed when it crosschecks the query cache.

Sep 05, 2012 : Is the overhead of frequent query cache invalidation ever worth it?
Jun 07, 2014 : Why query_cache_type is disabled by default start from MySQL 5.6?

As for the last bulletpoint, I wrote a post back on Mar 11, 2013 (Is there an execution difference between a JOIN condition and a WHERE condition?) that describes the nested loop algorithm. After reading it, you will see how good join decomposition may be.
As for all other points from the book, the developers really look for performance as the bottom line. Some rely on external means (outside of the application) for performance enhancements such as using a fast disk, get more CPUs/Cores, tuning the storage engine, and tuning the configuration file. Others will buckle down and write better code. Some may resort to coding all the business intelligence in Stored Procedures but still not apply join decomposition (See What are the arguments against or for putting application logic in the database layer? along with the other posts). It's all up to the culture and tolerance of each developer shop.
Some may be satisfied with performance and not touch the code anymore. Other simply don't realize there are great benefits one can reap if they try join composition.
For those developers that are willing ...
GIVE IT A TRY !!!

I don't know if this is possible in (most) sql versions I only really know transact SQL (Microsoft)

The performance loss is mostly given by first joining everything together than filtering in the end by a where clause.

How about integrating the where into the join ON statement and if you have multiple joins intelligently order them so the most filtering is done in the first join reducing the rows to be used in the subsequent joins.

Like:

SELECT * FROM tag
JOIN tag_post ON tag.tag = 'mysql' AND tag_post.tag_id = tag.id
JOIN post ON tag_post.post_id = post.id

In t-sql this can greatly enhance the speed of the query as most rows from tag_post are filtered out in the first join step (given you have many different tags).

Can anyone comment if this is possible in other sql dialects and if it is as performant as doing multiple queries or if multiple queries still hold a performance boost?


Should a ledger DB store a separate line for each side of a transaction? I'm working on a database that needs to do manage transfers between several accounts.  We are considering using a simple ledger system for this (like what you find in Quicken).

I have two concerns with how to design the system:

Tracking the related transactions, for this I considered using a single LedgerEntry for both sides of a transaction
Keeping the totals easy to calculate, for this I want to use a separate LedgerEntry for both sides of a transaction

Here's a fiddle w/ both options and their problems.

If you decide to keep a single entry for both sides of a transaction, then by definition you are engaging in single-entry bookkeeping. This may be the most appropriate solution for some simple applications, but be clear that you are losing all the functional and robustness advantages of double-entry bookkeeping, in exchange for a simpler design.

Note that when viewed stand-alone Subledgers (though not their corresponding Journals) are often implemented as single-entry, since a control-account in the General Ledger captures the subledger total and the balancing side of the transactions are in the General Journal (GJ) and General Ledger (GL).

You also appear to be confusing the distinct concepts of Ledger and Journal in traditional double-entry bookkeeping. The various Journals (of which there will be numerous specialized varieties for specific common transactions of the business in addition to the General Journal) is a chronological history of all transactions entered into the system. The General Ledger is an ordering by account of all transactions entered into the system, and the various subledgers are an ordering by subledger-code of all transactions entered into the corresponding Journal.

Two examples of common Ledger and Journal combinations:

the General Journal and General Ledger are an aggregated summary of
all transactions used for generating Trial Balances and the Financial
Statements.
The Account Receivable subledger receives entries from the Invoicing Journal (debits to AR by Customer Number) and the Cash Receipts Journal (credits to AR by Customer Number).

Note that while both sides of every transaction will balance in toto, in general it is not required for the two sides of a transaction to be the same number of lines with the same values. For instance a simple Retail Sales transaction (in most jurisdictions) will look something like this:

Dr Accounts Receivable Customer #xxxx     $X,XXX.XX
Cr Revenue                                           $Z,ZZZ.ZZ
Cr Sales Tax Payable                                 $  YYY.YY
-----------------------------------------------------------------
Totals                                    $X,XXX.XX  $X,XXX.XX

A manufacturing Sales transaction (at the simplest level) will start like this:

Dr Accounts Receivable Customer #xxxx     $X,XXX.XX
Cr Revenue                                           $Z,ZZZ.ZZ
Cr Sales Tax Payable                                 $  YYY.YY
Dr Cost of Sales                          $U,UUU.UU
Cr Finished Goods Inventory                          $U,UUU.UU
-----------------------------------------------------------------
Totals                                    $W,WWW.WW  $W,WWW.WW

A Bond Purchase transaction will have to handle the Discount(Premium) on Face Value as either a Credit or a Debit depending on whether interest rates have risen or fallen since the bond was issued, so you won't even know in advance which side of the ledger your single-entry is on.

Once you decide to engage in single-entry bookkeeping you lose all ability to handle these transactions, which as seen here are by no means either rare or complicated.

This is an old question but doesn't appear to have been answered satisfactorily? Here is a proposal:

Simple ledger implementation with single row for each side of a transaction where both entries are linked under the Transaction ID and each side is differentiated in the account descriptor.

Transaction ID, Account List, Commodity, Amount

where

Account List is "Left Side/Asset/Cash/Checking/My Bank/Acc12345"
Transaction ID is "nnnnnn01"
Commodity is "USD" or "Shares" or "Tins of Beans"
Amount is number of commodity

the example given could be represented as:

"TX01","Left/Accounts Receivable Customer #xxxx","$",+X,XXX.XX
"TX01","Right/Revenue","$",-Z,ZZZ.ZZ
"TX01","Right/Sales Tax Payable","$",-YYY.YY
"TX01","Left/Cost of Sales","$",+U,UUU.UU
"TX01","Right/Finished Goods Inventory","$",-U,UUU.UU

Total of "TX01" "Left/." "$" is W,WWW.WW
Total of "TX01" "Right/." "$" is -W,WWW.WW

Total of all is $0.


SSIS: Passing variable from child package to parent package I have an SSIS project consisting of many packages, including a Main_Flow package which executes the other packages in the desired order.

The first package to be called from Main_Flow is Extract_Archive with a ForEach container that looks for zipped files. If one or more zip file exists, it extracts them in a location and marks a variable ArchiveFileFound to TRUE.

Now back in the Main-Flow package, I would like the next step to be executed ONLY if the ArchiveFileFound from the Extract_Archive is set to TRUE. However that variable only exists in Extract_Archive. I've been looking for information on how to pass variables from one SSIS package to another but I cannot find a good practical and recommended example. Any thoughts?

This can be done using a Script Task in the child package to update a variable from the parent package based off the value of a variable in the child package, assuming that none of the variables that are accessed have the same name in both the parent and child packages.  While the variables from the parent package won't be displayed in the ReadWriteVariables field on the Script Task, if you enter them here then they can be accessed.  Updating the variables from the parent package would follow the same syntax as doing so for variables in the same package, and the C# example details this.

Dts.Variables["User::ParentVariable"].Value = Dts.Variables["User::ChildVariable"].Value;

Here is the solution I created, I used the same logic what userfl89 used so I created one variable on the Master package called vErrorPropagation and in error handlers in SSIS I created a script task that test if this variable is set to true or not. Below screen shot


Try looking here!

  The TIMESTAMP data type is used for values that contain both date and
  time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to
  '2038-01-19 03:14:07' UTC.

You are exceeding the permitted ranges! Also, should activity_time be a duration of some sort? Maybe a begin_timestamp and end_timestamp might be an idea?

You should prefer TIMESTAMP to DATETIME, from here (Date and Time Type Storage Requirements), a timestamp without fractional seconds (i.e. most of them)  requires only 4 bytes (as opposed to 5, but hey!). Also, 99% of dates that we deal with on a day to day basis will be between 1970 and 2038.

This part of the manual should help with initialisations.

Following the small oversight so kindly pointed out by @ypercubeᵀᴹ I had a look at this issue - i.e. of '0000-00-00 00:00:00' being a special "zero" value.

The fruits of my efforts are available here.

DDL:

CREATE TABLE `user_login_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(11) NOT NULL,
  `event_type` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  -- `activity_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  -- FAILS
  `test_time1` TIMESTAMP NULL DEFAULT NULL,  -- WORKS
  -- `test_time2` TIMESTAMP DEFAULT '0000-00-00 00:00:00', -- FAILS
  -- `test_time3` TIMESTAMP NOT NULL DEFAULT 0,  -- FAILS
  -- `test_time35` TIMESTAMP NULL DEFAULT 0,
  `test_time4` TIMESTAMP NULL DEFAULT NULL,
  `test_time5` TIMESTAMP NOT NULL DEFAULT '1970-01-01, 00:00:01', -- FAILS with time = 00:00:00
  -- `test_dtime6` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',  -- FAILS
  `test_time6` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  -- `test_time7` TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00',
  -- 'test_time8` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',  -- FAILS
  PRIMARY KEY (`id`),
  INDEX my_index (created_at) -- if the TABLE is CREATEd, the INDEX is!
) ENGINE=MyISAM;

It appears to me that there is a problem/bug with MySQL - in that it appears to confuse this special "zero" with the NULL value! It won't work with DATETIME either! I looked at the documentation again and can't appear to find why many of the failing constructs in my DDL won't work. There was a time when MySQL didn't work with multiple TIMESTAMPs - maybe this is relic of that epoch? I even tried SQLFiddle - which will build the schema but then fails - not sure why! I hope this clarifies matters unless @ypercubeᵀᴹ has a better suggestion! I found this helpful.

If possible, replace timestamp with datetime first. Then reindex.


How to calculate AWS RDS Data Transfer Pricing I'm looking into the possibility of switching my database to Amazon's RDS offering.

At the moment it is just MySQL installed on a normal LEMP server along with everything else the application needs to run.

While the pricing for RDS instances is clear, I can see there is pricing for data transfer out to the internet of $0.090 per GB.

Presumably this includes query results.

But I have no idea how many bytes my query results are, or how many I might use in a day. Therefore I have no idea how much switching to RDS might actually cost.

Is there any way to calculate this?

This may very well be a stupid question but I'd hate to be stung by a huge fee because I didn't fully understand the costs involved.

Based on my experience, AWS does not charge for the query output of RDS Database of MySQL engine, but it charges for Aurora Engine.


What is Atomic Relation in First Normal Form I have read the definition of 1NF which is, "If each attribute of relation is atomic". Please tell me what is Atomic.

1NF requires that every attribute position in every tuple in every relation contains a single value of the appropriate type. The types can be arbitrarily complex. In fact, the types can be relations. (CJ Date's book Database in depth: relational theory for practitioners treats this issue in a way that's pretty easy to understand.)

"Atomic" has never really meant "indivisible", which is why that term is finally falling out of favor. Loosely speaking, "atomic" means if a value has component parts, the dbms either ignores the existence of those parts, or it provides functions to manipulate them. For example, a timestamp value has these parts.

Year
Month
Day
Hours
Minutes
Seconds
Milliseconds

That kind of value is obviously divisible, and all database management systems provide functions to manipulate those parts. They also provide a way to select a timestamp as a single value. (Which, of course, it is.)

Atomic key is that kind of Primary key which cannot decomposed.which means that this key is not divided further,like Student_ID,Employee_ID.


Which of these queries is best for performance? Which of these queries are best for performance ? Sometimes I wonder if SHORT scripts really is the best thing to focus on. These scripts performs the same task. With left joins i can achieve what i want with just a few lines. But then I tried with a longer script, using unions. Which is the best method? Between this:

SELECT p.productID, p.product, C.color, C.colorID, S.size, S.sizeID, q.qty From quantities q
        INNER join products P ON p.productID = q.productID
        LEFT JOIN colors C ON C.colorID = q.colorID
        LEFT JOIN sizes S ON S.sizeID = q.sizeID
        --WHERE q.productID = @productID

and this:

SELECT p.productID, p.product, C.color, C.colorID, S.size, S.sizeID, q.qty From quantities q
        inner join products P ON p.productID = q.productID
        INNER JOIN colors C ON C.colorID = q.colorID
        INNER JOIN sizes S ON S.sizeID = q.sizeID
        --WHERE q.productID = @productID
    UNION
    SELECT p.productID, p.product, NULL, NULL, S.size, S.sizeID, q.qty From quantities q
        inner join products P ON p.productID = q.productID
        INNER JOIN sizes S ON S.sizeID = q.sizeID
    WHERE /* q.productID = @productID AND */ q.sizeID IS NOT NULL AND q.colorID IS NULL
    UNION
    SELECT p.productID, p.product, C.color, C.colorID, NULL, NULL, q.qty From quantities q
        inner join products P ON p.productID = q.productID
        INNER JOIN colors C ON C.colorID = q.colorID
    WHERE /* q.productID = @productID AND */ q.colorID IS NOT NULL AND q.sizeID IS NULL
    UNION
    SELECT p.productID, p.product, NULL, NULL, NULL, NULL, q.qty From quantities q
        inner join products P ON p.productID = q.productID
    WHERE /* q.productID = @productID AND */ q.colorID IS NULL AND q.sizeID IS NULL

EDIT:

  SQL Server parse and compile time:     CPU time = 32 ms, elapsed time
  = 65 ms.

  (10 row(s) affected) Table 'sizes'. Scan count 1, logical reads 21,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0. Table 'colors'. Scan count
  1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical
  reads 0, lob physical reads 0, lob read-ahead reads 0. Table
  'products'. Scan count 0, logical reads 20, physical reads 1,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0. Table 'quantities'. Scan count 1, logical reads 2,
  physical reads 1, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 12
  ms.

  (10 row(s) affected) Table 'products'. Scan count 0, logical reads 20,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0. Table 'quantities'. Scan
  count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob
  logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table
  'Worktable'. Scan count 0, logical reads 0, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0. Table 'sizes'. Scan count 0, logical reads 18,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0. Table 'colors'. Scan count
  0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical
  reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms.

It seems to me that it's the second query that is best for performance, the bigger query with unions.

Do anyone of you got a clue why this happends or do I have to provide with more information (table info and such things) ?

Execution plan:

Thanks!

Sometimes I wonder if SHORT scripts really is the best thing to focus on.

The size of a script has little to do with how efficiently the query will execute. A more compact statement will likely consume fewer resources in terms of compilation, but (re)compilation is usually a rare occurrence in a live system.

Fewer table accesses is usually desirable, though, and this does lead to more compact code.

Very generally speaking, a smaller execution plan will yield better results, and a lower estimated cost will yield better results. Again, though, it's highly situational. Cost estimates in particular can be way off in some cases. It's important to measure the actual execution time, because at the end of the day, that's what matters.

  With left joins i can achieve what i want with just a few lines. But then I tried with a longer script, using unions. Which is the best method?

First of all, we need to know how much data will be in these tables in a real system. Right now there's so little it will be difficult to use the STATISTICS TIME performance metrics to figure out a winner -- the results that come back will be dominated by factors other than the query execution. With more data, it's likely the plans will change, thus rendering the comparison here moot.

Having said that, by looking at the query plans as they are now from a logical point of view, the first one is the winner.

You can see that the Clustered Index Scan of quantities appears once in the first plan, while it appears four times in the second one. The second plan also contains an expensive Distinct Sort as a result of using UNIONs (this operator could be eliminated by using UNION ALLs instead, which won't change the results).

The first query could also probably be improved, by getting index seeks on the colors and sizes tables, instead of table scans. It might be worth trying a hash match plan as well (which is what you'll probably see when quantities and products are larger), but for tables this small, the startup cost may be too much overhead to be of benefit.

What I would suggest you do is run each of the statements you want to test 10,000+ times in a loop, figure out the average execution time, and then compare.

SQL Server uses the query cost to evaluate which plan is the best for a query. Cost is a ratio of CPU and IO metrics. You can view both cost by selecting the SELECT icon on the left and either look at the yellow box or you can press F4 to get the properties and compare the "Subtree Cost".


Remote access to SQL Server 2008 R2 on Windows Server 2012 R2? I have SQL Server 2008 R2 (named instance) on a Windows Server 2012 R2:

Enabled remote access to SQL Server
Enabled shared memory & TCP/IP protocols & named pipes for the SQL Server instance.
Created a firewall rule for TCP Port 1433
Created a firewall rule for UDP Port 1434
Created a firewall rule for SQLBrowser.exe
Created a firewall rule for SQLservr.exe
Ensured SQL Browser & SQL Server are running

I'm able to remotely access SQL Server (ip\instance name,port number) but can't access with (ip\instance name).

I tried with turning off firewall, but the problem was not resolved.

How can I fix it?

It is a known issue with Sql Server 2008. Happens also in SSAS.
Search for Microsoft fix/ or ask support for it (fix applies on the OS site and not the DB site).


SSRS Encryption Key backup location I would like to know if there is any metadata table/registry entry where we can find the backup location of SSRS Encryption Key.

I am working on a power shell report to display the Encryption Key backup date. But the challenge is to find out the location so that the script can be generic.

I could see no answer to this since couple of months. I have done my research and have put together in the below link:
http://sqlgeekspro.com/ssrs-encryption-key-backup-information/


How to use mySQL to generate auto-increment ID for me This may be a bad structure. Let me know if it is, it's been a long time since I did any database design.

I have a tblGame in a mySQL database, which has two fields:

ID_Table
ID_Player

In the actual game, multiple players will sit on the one table.  So I have both fields set as a combined primary key. And ID_Table is set to auto-increment. ID_Player is an auto-increment in another table (tblPlayers).  What I'd like to do is to get the ID_Table value before any players are added to the table.  The actual game analogy is to an open table (with ID_Table already assigned) sitting there waiting for players to join. As they join, they get added to tblGame.

Is it possible to do this?

You could implement the following solution.

Game "Table"

This table stores the auto-generated values of the (playing) Tables for the game:

CREATE TABLE tblTable (
    ID int NOT NULL AUTO_INCREMENT);

Game "Players"

This table contains all the relevant Player data:

CREATE TABLE tblPlayer (
    ID         int          NOT NULL AUTO_INCREMENT,
    LastName   varchar(244) NOT NULL,
    FirstName  varchar(244) NOT NULL,
    Age        int          NOT NULL,
    PRIMARY KEY (ID)
);

Game "Games" (Matches)

This table links the tblPlayer table with the tblTable data.

CREATE TABLE tblGame (
    ID_Table   int,
    ID_Player  int
);

Creating a new table

When you create a new table in your game you just initialize a new ID by setting off the following statement:

INSERT INTO tblTable (ID) VALUE DEFAULT;

This will create a new value (ID). You would have to store this value in your program code to use when assigning players to the table.

If you are using Mysql Database this query can help you

CREATE TABLE Your_table_name (
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.

To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

ALTER TABLE Persons AUTO_INCREMENT=100;