Logging the conditions within a conditional split transformation (SSIS in SQL Server 2012 and 2016) I'm struggling to find out how we can store the conditions inside a conditional split transformation when we run a package. I need a snapshot of the expressions (the whole expressions, not only parameters inside them).

The closest we have got is when an error triggered the SSIS logging and stored the context in the table event_message_context, but we can't figure out how to trigger this logging without an error. We don't need it specifically to come from the built-in logging, we can surely use a script task if you have any suggestions. We run both SQL server 2012 and 2016.

If you use variables or parameters in the Conditional Split, you can log the values of these.  There are several ways to do this, either insert the values into a custom logging table or use a Script Task to capture these.  The following example uses the Dts.Events.FireInformation method from a C# Script Task.  Under the Basic logging mode, this will be logged in the SYSSSISLOG table, assuming that you've chosen this as a log provider.

string taskName = Dts.Variables["System::TaskName"].Value.ToString();
string varName = Dts.Variables["User::YourVariableName"].Value.ToString();
int infoCode = 99;
bool fireAgain = true;
string loggedMsg = "Value of Conditional Split Variable:  " + varName;

Dts.Events.FireInformation(infoCode, taskName, loggedMsg, "", 0, ref fireAgain);


PostgreSQL Drop Database With Connected Users I've searched and tried a lot, but I can't figure out a proper solution to dropping all connections so I can properly drop a database.

Force drop db while others may be connected

This topic from 2012 discusses that we should first stop allowing connections to the database. If I execute the command in step 1 I can no longer run commands.

psql -h localhost -d postgres -U postgres -c "UPDATE pg_database SET datallowconn = 'false' where datname = 'postgres';"
psql -h localhost -d postgres -U postgres -c "SELECT pg_terminate_backend (pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'postgres';"
dropdb -h localhost -U postgres postgres

The result of all commands after the first is:

psql: FATAL:  database "postgres" is not currently accepting connections

Once I'm at this point I have no idea how to recover other than stopping postgresql, deleting the database folder and recreating the database using initdb.

PostgreSQL DROP DATABASE

This tutorial doesn't include anything on disallowing connections. So I run the following:

psql -h localhost -d postgres -U postgres -c "SELECT pg_terminate_backend (pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'postgres';"
FATAL:  terminating connection due to administrator command
FATAL:  terminating connection due to administrator command
connection to server was lost
dropdb -h localhost -U postgres postgres
dropdb: database removal failed: ERROR:  database "postgres" is being accessed by other users
DETAIL:  There are 6 other sessions using the database.

I've also tried revoking access like so, but this doesn't stop PGAdmin III from connecting so I assume I messed up the command.

psql -h localhost -d postgres -U postgres -c "REVOKE CONNECT ON DATABASE postgres FROM public;"

I'm not sure how to continue. I see a lot of topics on this issue, but nothing works. Note, I'm running these commands in a shell script, but if I run them from the CLI I get the same results.

Once you've set datallowconn = 'false', it does exactly what it says on the tin, and doesn't allow connections to that database.

Connecting as the postgres user to the template1 database should allow you to drop that database.

For more information check the documentation on template databases. They're quite useful. =)


Does SQL Server 2008R2 Express actually enforce the memory and CPU limit? I've been testing SQL Server 2008R2 Express to see if it is limited to 1GB of RAM and 1 CPU.

It certainly seems to be using more than one CPU, as I see spikes on all 4 CPUs in Task Manager when I run a big query. I've selected * from all the tables in the database I have, and it got it to use 1.4GB RAM when looking at the Total Server Memory in perfmon (I gather this is the correct stat to look at). This is showing as 1.5GB Mem Usage on Task Manager (which I gather isn't reliable).

However, it does not seem to want to go any further than 1,441,792KB. This seems odd, so I am not convinced either way.

Anyone have any knowledge of why this might happen and whether the limits are actually enforced?

SQL Server Express does enforce the limits, but the limits are not what you expect:

CPU: SQL Express enforces one socket, not one CPU. Multi-core and hyper-threaded machines will leverage multiple cores on one socket and SQL Express will use 2 or 4 cores on that socket. See Maximum Number of Processors Supported by the Editions of SQL Server:

    SQL Server supports the specified number of processor sockets multiplied by the number of logical CPUs in each socket. For example, the following is considered a single processor for purposes of this table:

    A single-core, hyper-threaded processor with 2 logical CPUs per socket.
    A dual-core processor with 2 logical CPUs.
    A quad-core processor with 4 logical CPUs.

Memory: Express restricts the max size of the buffer pool to only 1GB. The buffer pool accounts only for some of the overall process memory.

See Thread and Task Architecture and Memory Management Architecture


statistics are up to date, but estimate is incorrect When I do dbcc show_statistics ('Reports_Documents', PK_Reports_Documents) I get the following result for Report ID 18698:

For this query:

SELECT *
FROM Reports_Documents
WHERE ReportID = 18698 option (recompile)

I get a query plan that makes a Clustered Index Seek on PK_Reports_Documents as expected.

But what baffles me is the incorrect value for Estimated Number of Rows:

According to this:

  When the sample query WHERE clause value is equal to a histogram
  RANGE_HI_KEY value, SQL Server will use the EQ_ROWS column in the
  histogram to determine the number of rows that are equal to

This is also the way I would expect it to be, however it seems not to be the case in real life. I also tried some other RANGE_HI_KEY values that were present in the histogram provided by show_statistics and experienced the same. This issue in my case seems to cause some queries to use very unoptimal execution plans resulting in an execution time of a few minutes whereas I can get it to run in 1 sec with a query hint.

All in all: Can someone explain me why is EQ_ROWS from the histogram not being used for the Estimated Number of Rows and where does the incorrect estimate come from?

A bit more (possibly helpful) info:

Auto create statistics is on and all the statistics are up to date.
The table being queried has about 80 million rows.
PK_Reports_Documents is a combination PK consisting of ReportID INT and DocumentID CHAR(8)

The query seems to load a total of 5 different statistics objects, all of which contain ReportID + some other columns from the table. They have all been freshly updated. RANGE_HI_KEY in the table below is the highest upper bound column value in the histogram.

+-------------------------------------------------------------------------+----------+--------------+--------------+---------------------+--------------+------------+----------+---------------------+----------------+
|                                  name                                   | stats_id | auto_created | user_created | Leading column Type | RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS  | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+-------------------------------------------------------------------------+----------+--------------+--------------+---------------------+--------------+------------+----------+---------------------+----------------+
| PK_Reports_Documents                                                    |        1 |            0 |            0 | Stationary          |        18722 | 0          | 2228,526 |                   0 | 1              |
| _dta_index_Reports_Documents_42_1629248859__K1_K63_K14_K13_K22_K23_72_6 |       62 |            0 |            0 | Stationary          |        18698 | 0          | 2228,526 |                   0 | 1              |
| _dta_stat_1629248859_1_1_59                                             |       76 |            0 |            1 | Stationary          |        18686 | 50,56393   | 1        |                   0 | 13397,04       |
| _dta_stat_1629248859_1_22_14_18_12_6                                    |       95 |            0 |            1 | Stationary          |        18698 | 0          | 2228,526 |                   0 | 1              |
| _dta_stat_1629248859_1_7_14_4_23_62                                     |       96 |            0 |            1 | Stationary          |        18698 | 56,63327   | 21641,5  |                   0 | 14526,44       |
+-------------------------------------------------------------------------+----------+--------------+--------------+---------------------+--------------+------------+----------+---------------------+----------------+

sp_updatestats is scheduled to run every night to update stats.

There is a simple solution to this:

Drop all of the _dta_... statistics and stop blindly applying DTA recommendations.

More information

The particular problem was that there were multiple sets of statistics for the column in question. The extra dta statistics were created by sampling the data (the default behaviour for statistics not associated with an index).

As is often the case with sampled statistics, the resulting histograms did not cover the full range of the unerlying data. The query in the question happened to choose a value that was outside the histogram, resulting in a 1-row estimate.

The exact behaviour of the query optimizer when multiple sets of statistics exist for the same column is not fully documented. It does tend to prefer 'full scan' statistics over sampled, but it also prefers more recently-updated statistics to older ones.


what is the difference between db_datareader and db_denydatawriter? I am trying to assign a SQL Server database role to a user who just needs to be able to see data across all tables.
I was wondering what is the difference between db_datareader and db_denydatawriter?
From the documentation I don't seem to realize any difference.

Thanks,

I am trying to assign a SQL Server database role to a user who just
  needs to be able to see data across all tables.

You should only make him a member of db_datareader.

By default, any newly created user has no permissions at all (unless you grant something to public or the corresponding login has some permissions at the server level: for example, view any definition at the server level implies view definition at database level once the login is mapped to a database).


How to get to know that db files are inaccessible? Let me explain my case.

I have a mount with database files on my local PC. I attach the database (from this mount). And then close this mount (in other word i attach db and then delete db files). So I have the database without files. And any ALTER query gives me something like this:

Database 'TestTableDatabase2005' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

Basically I need to detach a database but in this situation this database can only be deleted(or repaired but it is not my case). Dropping database works perfectly:

DROP DATABASE [TestTableDatabase2005]

But my goal is get to know which state does this database have. And here I have problem. Query:

SELECT state
FROM sys.databases
WHERE name = N'TestDB'

Gives me ONLINE (it's not true). But as soon as I execute for example this script(of course it finishes with error):

ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Msg 5120, Level 16, State 101, Line 10
Unable to open the physical file "C:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca\TestDB.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
File activation failure. The physical file name "C:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca\TestDB_log.ldf" may be incorrect.
File activation failure. The physical file name "C:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca\TestDB_log-2.ldf" may be incorrect.
Msg 5181, Level 16, State 5, Line 10
Could not restart database "TestDB". Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 10
ALTER DATABASE statement failed.

the state of my 'TestDB' will be changed on RECOVERY_PENDING.

Do you have any idea how I can check are these db files inaccessible or not?
Or may be i have an opportunity to refresh sql info about this db ?

P.S.
Following query says that files have actual size.

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = N'TestDB'

DatabaseName Logical_Name Physical_Name                                                     SizeMB
------------ ------------ ----------------------------------------------------------------- -------
TestDB       TestDB       C:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca\TestDB.mdf          3
TestDB       TestDB_log   C:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca\TestDB_log.ldf      3
TestDB       TestDB-2_log C:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca\TestDB_log-2.ldf    3

If your file system is dropping out, that is a major problem. What if your database is partway through committing a transaction? This could cause corruption and potential data loss. This would be my first reaction.

If this is an issue in your test system only, and you have verified that the underlying file system fault is localized (IE definitely not affecting production systems) my feeling is that a SQL Agent job with step 1 configured to check for the physical files on disk could do the job.

Step 1 could consist of something like the following powershell:

if (Test-Path $path) {
   Return
} else {
   Throw "File doesn't exist"
}

The above code will throw an error if the file in the $path variable doesn't exist.

Step 1 on failure would move to step 2, but on success would quit the job reporting success. Step 2 would then be whatever action you wish to take.


Cannot view sql server agent in sql server 2005? I have installed full setup of SQL Server, and start the service for SQL Agent through SQL Server surface area. But still I can't get SQL Agent tree node in my SQL Server connection treeview.

So please help me what I have to do for getting SQL Agent in treeview.

Minimum rights needed to see the agent node you need to have sysadmin rights on the instance or rights to the agent database roles in msdb.


There's a number of different ways to do this, but if you only need it to be a monthly period starting on a given date, I would use a variable for the day of the month to start on and just calculate a new column with that month. Something like:

DECLARE @DayStart TINYINT

WITH cteMonth AS
(
SELECT DateColumn,
CASE
WHEN @DayStart < DAY(DateColumn) THEN CASE WHEN MONTH(DateColumn) = 1 THEN CAST(YEAR(DateColumn) - 1 AS VARCHAR(4)) + '/12' ELSE CAST(YEAR(DateColumn) AS VARCHAR(4)) + '/' + CAST(MONTH(DateColumn) - 1 AS VARCHAR(2)) END
ELSE CAST(YEAR(DateColumn) AS VARCHAR(4)) + '/' + CAST(MONTH(DateColumn) AS VARCHAR(2))
END AS DateMonth
FROM SomeTable
)
SELECT DateMonth, [aggregate columns]
FROM cteMonth
GROUP BY DateMonth

Apologies for this being T-SQL syntax (I'm not a PL/SQL guy), but in case you need help on declaring variables:
http://www.postgresqltutorial.com/plpgsql-variables/


MERGE with OUTPUT doesn't seem to be doing the right thing I'm adding a foreign key to a table, and removing any rows that violate the FK, copying them into a ModifiedTable_invalid table. As part of the script, I've got the following MERGE command:

MERGE ModifiedTable t1
USING TargetTable tt
ON t1.JoinColumn = tt.JoinColumn
WHEN MATCHED THEN
    UPDATE SET t1.FkColumn = tt.FkSource
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT DELETED.* INTO ModifiedTable_invalid;

However, this command seems to be inserting EVERY row from ModifiedTable into ModifiedTable_invalid, not just those deleted by the MERGE command. What's going on, and how do I get it to only put the deleted rows in ModifiedTable_invalid?

When you update a row, it will appear in both the inserted (post-update value) and deleted (pre-update value) pseudo-tables.  If this seems odd, consider that an update is logically a deletion followed by an insert (though the update may not be physically performed that way).

When used with MERGE, the OUTPUT clause can include an extra column named $action. Adding this column to your query will show which action was taken ('INSERT', 'UPDATE', or 'DELETE') for each row.

For example:

insert into ModifiedTable_invalid(Id /* And other columns */)
select
    Id
    /* And other columns */
from
(
    merge ModifiedTable t1
    using TargetTable t2 on t1.JoinColumn = t2.JoinColumn
    when matched then update set t1.FkColumn = t2.FkSource
    when not matched by source then delete
    output
        $action as DMLAction,
        deleted.Id as Id /* And other columns... */
) outputs
where
    DMLAction = 'DELETE';

Updated rows will have $action = 'UPDATE'.

Also see Adam Machanic's post on using OUTPUT with the MERGE statement for some other nice examples.


Validity period of an open ssrs report We have SSRS 2008 r2 rdls. I open an SSRS report and keep it open in my browser for more than 30 mins. If I now click any drill downs either through an action or go to report. I don't want the report to get me that info. It should state that the report is expired.

Is there a solution already available? Do future version SQL server have a solution for this?

That's not how it works.

When you click a link in an existing report it basically opens the linked report with the parameters in the URL.

The target report has no clue about the rendering date of the source report.

If you want to accomplish anything like this you would have to pass the rendering datetime of the main report as a parameter to the subreport and have the subreport check that (every single one of them).

As far as I know there isn't a general setting and you would have to modify every drill-through action to send a parameter based on the datetime of rendering and modify each target report to check on it and render an error message if the parameter exceeds a certain threshold.


"Untrusted Domain" error when connecting to SQL Server localhost and specifying port I have two SQL Server 2016 instances running on localhost, and I can connect to both with no issues (localhost and localhost\test) using windows authentication. However, if I specify the port explicitly in SSMS ("localhost,1433" or "localhost,1435", respectively), I receive the following error:

  Login failed. The login is from an untrusted domain and cannot be used
  with Windows authentication. (Microsoft SQL Server, Error: 18452)

I'm connecting to a local instance (it's not a domain PC, but it shouldn't matter) and I'm a local admin, so there are no permissions issues - when I connect without the port specified, I connect without any issues at all.

I've seen this error when connecting across domains or in a workgroup, but that's not what's happening here as I can connect if I remove the port number.

So, we just had this problem, adding details in case it helps someone else and they don't spend time going down the rabbithole of Kerberos debugging like we did.

I don't have all the details because our next level tier fixed it, but apparently in our case since we had a cross-forest trust authentication, the issue was that we needed to grant the user's group "Allowed to Authenticate" permission on the server object they were trying to connect to, after that it worked fine.

we had a win2k16 server running Sql2017 in domain1, and users running win7 in domain2 with trusts setup between the two, this worked fine.

But when the users migrated to win10, they received the above error. Fix for us was granting 'allowed to authenticate' to the domain user group in domain2 on the server in domain1, then win10 worked fine.

If anyone knows why upgrading client OS makes any difference please let us know!


Update table from different table UPDATE RBI.RBI_OV_F_DETAIL A
     SET (BRANCH_ID, BRANCH_NUMBER) = (
           SELECT PRICE_BRANCH_ID, PRICE_BRANCH
             FROM RSE.RSE_SO_F_ORDERS B
                WHERE A.SOURCE_ID = B.FULL_ORDER_NUMBER AND A.METRIC_GROUP_ID='1');

I am getting error in this syntax:
single query returns multiple rows

Please suggest what to do.

Quick and dirty answer:

UPDATE RBI.RBI_OV_F_DETAIL A
 SET (BRANCH_ID, BRANCH_NUMBER) = (
       SELECT PRICE_BRANCH_ID, PRICE_BRANCH
         FROM RSE.RSE_SO_F_ORDERS B
            WHERE A.SOURCE_ID = B.FULL_ORDER_NUMBER AND A.METRIC_GROUP_ID='1'
       group by price_branch_id, price_branch);

However if you're not expecting duplicates in the RSE.RSE_SO_F_ORDERS table, the more accurate answer would be to hunt down why those duplicates are there, and work with any stakeholders to eliminate the duplicates.


Repeated values in group_concat I have two tables, first the table food and Second is Activity:

INSERT INTO food
    (`id`, `foodName`)
VALUES
    (1, 'food1'),
    (2, 'food2'),
    (3, 'food3'),
    (4, 'food4'),
    (5, 'food5'),
    (6, 'food6'),
;
CREATE TABLE Activity
    (`id` int,`place` varchar(14),`food_id` int,`timing` TIME,`date_and_time` DATETIME)
;
INSERT INTO Activity
    (`id`,`place`, `food_id`,`timing`,`date_and_time`)
VALUES
    (1, 'place1', 1, '10:30am','2013-05-01'),
    (2, 'place1', 1, '12:30pm','2013-05-01'),
    (3, 'place1', 1, '04:30pm','2013-05-01'),
    (4, 'place2', 2, '10:30am','2013-05-02'),
    (5, 'place2', 2, '12:30pm','2013-05-02'),
    (6, 'place2', 2, '4:30pm','2013-05-02'),
    (7, 'place1', 2, '10:30am','2013-05-02'),
    (8, 'place1', 2, '12:30pm','2013-05-02'),
    (9, 'place1', 2, '4:30pm','2013-05-02'),
    (10, 'place2', 3, '10:30am','2013-05-03'),
    (11, 'place2', 3, '12:30pm','2013-05-03'),
    (12, 'place2', 3, '4:30pm','2013-05-03')
;

For now I'm using the following query:

SELECT
    a.activity_type AS Activity,
    COUNT(DISTINCT p.id) AS Products,
    CONVERT(GROUP_CONCAT(p.category_id SEPARATOR ',  ') USING utf8)
      AS Categories
FROM
    food AS p
  JOIN
    ( SELECT activity_type
           , prod_id
      FROM activity
      WHERE activity_type <> ''
      GROUP BY activity_type
             , prod_id
    ) AS a
    ON p.id = a.prod_id
GROUP BY
    activity_type
ORDER BY
    Products DESC ;

Could you please help me, I need output in the below format:

place | food_id | Timings             |                              |
      |         |---------------------|Date                          |
      |         |Time1 |Time2 | Time3 |                              |
---------------+----------+------------------------------------------|
place1 | 1      | 10:30am| 12:30pm| 4:30pm |2013-05-01(MAX timestamp)|
place2 | 1      | 10:30am| 12:30am| 4:30am |2013-05-01(MAX timestamp)|

If your date_and_time values are always the same, use this query:

select place, food_id, '10:30am' as Time1, '12:30pm' as Time2, '4:30pm' as Time3, DATE_FORMAT(MAX(date_and_time), '%Y-%m-%d') as Date from Activity group by food_id, place;


Tracking down a stored procedure's parameters after execution I have been asked to identify a permissions issue with a stored procedure. This stored procedure behaves in two possible ways depending on what values are used for its parameters.

exec ps_my_stored_procedure @a=1, @b=2, @c=3

is handled very differently from

exec ps_my_stored_procedure @a=5, @b=7, @c=0

You could say that ps_my_stored_procedure is divided logically into two completely separate processes.

Using dm_exec_procedure_stats and dm_exec_query_stats, I can find the execution plan which shows the SQL of the stored procedure used.  I have not been able however, to recover how the parameters were defined and with what values.

Is it possible using dm_exec_procedure_stats and dm_exec_query_stats and any other management views to reconstruct the execution of the stored procedure that shows the values used for its parameters.

What I'd really like is to find in cache, is the actual execution of the stored procedure so that I can execute it as is using EXECUTE AS LOGIN = 'someone' to resolve the permissions issues

I often use the following query taken from http://www.sommarskog.se/query-plan-mysteries.html#otherreasons to return the sniffed values. Another way would be to open up the plan in SQL Sentry Plan Explorer and select the parameters tab. Both the XML method and plan explorer method will show the runtime values assuming that you are viewing the actual execution plan.

DECLARE @dbname    nvarchar(256),
        @procname  nvarchar(256)

SELECT @dbname  = '',
       @procname = '' -- enter proc name

; WITH basedata AS (
   SELECT qs.plan_handle,
            qs.statement_start_offset/2 AS stmt_start,
          qs.statement_end_offset/2 AS stmt_end,
          est.encrypted AS isencrypted, est.text AS sqltext,
          epa.value AS set_options, qp.query_plan,
          charindex('<ParameterList>', qp.query_plan) + len('<ParameterList>')
             AS paramstart,
          charindex('</ParameterList>', qp.query_plan) AS paramend
   FROM   sys.dm_exec_query_stats qs
   CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
   CROSS  APPLY sys.dm_exec_text_query_plan(qs.plan_handle,
                                            qs.statement_start_offset,
                                            qs.statement_end_offset) qp
   CROSS  APPLY sys.dm_exec_plan_attributes(qs.plan_handle) epa
   WHERE  est.objectid  = object_id (@procname)
     AND  est.dbid      = db_id(@dbname)
     AND  epa.attribute = 'set_options'
), next_level AS (
   SELECT plan_handle,stmt_start, set_options, query_plan,
          CASE WHEN isencrypted = 1 THEN '-- ENCRYPTED'
               WHEN stmt_start >= 0
               THEN substring(sqltext, stmt_start + 1,
                              CASE stmt_end
                                   WHEN 0 THEN datalength(sqltext)
                                   ELSE stmt_end - stmt_start + 1
                              END)
          END AS Statement,
          CASE WHEN paramend > paramstart
               THEN CAST (substring(query_plan, paramstart,
                                   paramend - paramstart) AS xml)
          END AS params
   FROM   basedata
)

SELECT set_options AS [SET]
        , n.stmt_start AS Pos
        , n.Statement
       , CR.c.value('@Column', 'nvarchar(128)') AS Parameter
       , CR.c.value('@ParameterCompiledValue', 'nvarchar(128)') AS [Sniffed Value]
       , CAST (query_plan AS xml) AS [Query plan]
       , n.plan_handle
FROM   next_level n
CROSS  APPLY
        n.params.nodes('ColumnReference') AS CR(c)
ORDER  BY n.set_options, n.stmt_start, Parameter


PostgreSQL: how can I shorten a multi-word string to few words? I'm using PostgreSQL 9.5.6.  I have space separated multi-word strings that I need to shorten to say 3 words?

I've looked at the documentation and in order to use the substring() functions I need to know the index position of the char where I want to start extraction, but the strings I'm working with are varying length.

I've also looked at split_part() and that only returns one word.

How can I get something like:

hello everyone out there somewhere

shortened to

hello everyone out

Turn the sentence into an array, then pick the first three elements and concatenate them back to a single string:

select array_to_string ((regexp_split_to_array(title, '\s+'))[1:3], ' ')
from book;

alternatively, just use split_part() three times:

select split_part(title, ' ', 1)||
       split_part(title, ' ', 2)||
       split_part(title, ' ', 3)
from book;

split_part() has the disadvantage, that you can't properly deal with multiple spaces between the words.


Always Encrypted on Sql 2017 Express Edition I am so tentative to decide to use Always Encrypted tool, however I have a question → I'm working on a project with SQL Server 2017 - 14 Express Edition.

And I'm pretty sure they aren't going to buy the Enterprise Edition.

Even so, Can I use this tool? Or is there any limitation related to the final version of SQL? I didn't find any limitation at the official documentation from Microsoft

Thanks a lot.

As per this link, Always Encrypted is supported in SQL Server 2017 Express Edition.


Create table using select statement Truncated incorrect datetime value: I have looked up different answers on similar question before posting it here.

So i have a table with user id and date column

I would like to store the results of select query to a temporary table.
On using the below statement:

Create Temporary table dummy_table (
select User_Id , DATE_FORMAT(Date,'%M %Y')
from result_table.user_signup_date );

throws error
Error Code: 1292. Truncated incorrect datetime value: '2019-06-16 00:00:00 UTC'

I noticed the error occurs only when i format date column. I tried converting date to string. I also tried with str_to_date(DATE_FORMAT(Date,'%M %Y') ,'%M %Y %H:%i:%s') and few more syntaxes but with no result.

I am new to Sql. Any help would be appreciated. Thanks in advance

From the looks of the error message, the UTC should not be part of the date. My guess is that date is not using the DATETIME or TIMESTAMP data type. It must be a string.

Try running it like this :

Create table dummy_table AS
select Cust_ID User_Id , DATE_FORMAT(LEFT(`Date`,19),'%M %Y') DT
from result_table.user_signup_date;

The expression LEFT(Date,19) will drop off the everything after the timestamp.


MongoDB MMAPv1 vs WiredTiger storage engines In mongoDB3 appeared a new storage engine: WiredTiger. Yet, MMAPv1 is still the default choice in Mongo.

One might not be better than the other, it's often a matter of use case and choosing the right tool for the job. But which engine is right for what job?

In fact, while MMAPv1 is the default engine, WiredTiger seems better in almost every field. It has the same features as MMAPv1 plus:

better write performance,
document level concurrency,
compression,
snapshots and checkpoints system.

I found a comparing table on MongoDB's blog:

So except if you are on Solaris, is there a reason not to choose WiredTiger?

EDIT

Here are two videos that explain in details the internals of
WiredTiger and MMAPv1.

Personally, I prefer the mmapv1 storage engine as of now for three reasons.

Reason 1: Maturity

It isn't that WiredTiger is immature. But mmapv1 is well understood and battle tested all way up and down, back and forth and above and beyond. WiredTiger has had some serious issues (see http://jira.mongodb.com for details) fairly recently, and I am not willing to have my customers find the next one the hard way.

Reason 2: Features

Given, WT has some f... undamentally awesome features. The thing is: I have not seen anyone benefitting from them. Compression? Either way, you sacrifice rather hard to achieve performance for rather cheap disk space. Lack of the document migration problem for expanding documents? Well, we still have the 16MB size limit and added complexity for embedded documents, especially when embedding is overdone.

There are other features, but in general: I do not see to much benefit from them as of now.

Reason 3: Total cost of ownership

For new projects, WT might be fine, especially since 3.2 , since the following does not apply.

Doing data migrations is expensive. It needs to be planned, the plan needs to be agreed upon by all stakeholders, emergency contingency plans have to be created and agreed upon, the migration needs to be prepared, executed and reviewed. Now multiply the time needed with the stakeholders who are part of this process, and the costs for the data migration skyrocket. The return on investment on the other hand seems rather small. You can scale quite a bit instead of doing a migration if you take those factors into account. To give you an impression: I'd estimate roughly one "man-week" per stakeholder if a migration is planned, executed and reviewed properly. With costs of $100 per hour per person, and only three people involved (manager, DBA and developer), that amounts to $12.000. Note that this is a conservative estimate.

Conclusion

All those factors above have brought me to the conclusion not to use WT whatsoever. At the moment.

Update

This post is some months old now, so it deserves an update

On maturity

My original comments on maturity are sort of obsolete. WiredTiger has not had any major issues for a while now and has become the default storage engine as of MongoDB 3.2

On Features

My original comments still hold some validity, imho.

Compression

However, when being tight on budget or, more generally speaking, performance is not the primary concern, the performance tradeoff is rather small, and you basically trade slight performance impacts (when compared to uncompressed WT) for disk space, utilizing what otherwise would idle around: the CPU.

Encryption

MongoDB 3.2 Enterprise introduced the ability to have WiredTiger storages encrypted. For data with enhanced security needs, this is a killer feature and makes WT the only storage engine of choice, both technically (MMAPv1 does not support encryption) and conceptually. Setting aside the possibility of encrypted disk partitions, of course, although you might not have that option in some environments.

Document level locking

I have to admit that I basically omitted that feature of WT in my above analysis, mainly because it did not apply for me or my customers when I wrote the original answer.

Depending on your setup, mainly when you have many concurrent writing clients, this feature may provide a great performance boost.

On Total cost of ownership

Doing migrations is still expensive. However, taking the changes in maturity and the changed view on the features into account, a migration might be worth the investment if:

You need encryption (Enterprise Edition only!)
Performance is not your absolute primary concern and you can save money in the long run (calculate conservatively) using compression
You have a lot of processes writing concurrently, since the increase in performance might save you vertical or horizontal scaling.

Updated Conclusion

For new projects, I use WiredTiger now. Since a migration from a compressed to an uncompressed WiredTiger storage is rather easy, I tend to start with compression to enhance the CPU utilization ("get more bang for the buck"). Should the compression have a noticeable impact on performance or UX, I migrate to uncompressed WiredTiger.

For projects with a lot of concurrent writers, the answer to wether to migrate or not is almost always "Yes", too – unless the project's budget prohibits the investment. In the long run, the performance increase should pay for itself, if the deployment was otherwise reasonably planned. However, you need add some development time to the calculation, since in some cases the driver needs to be updated, and there might be problems which need to be dealt with.

For projects which are tight on budget and can not afford more disk space for the moment, migrating to a compressed WiredTiger can be an option, but the compression puts a bit load on the CPU, something unheard of with MMAPv1. Furthermore, the migration costs might be prohibitively expensive for such a project.


FULLTEXT CONTAINS search returns no results in SQL SERVER 2014 with multiple search terms When I try to use multiple search terms in a CONTAINS clause I get no results.

SELECT *
FROM dbo.Address
WHERE CONTAINS([Address].*,'"Detroit"') AND CONTAINS([Address].*,'"48226"')
OPTION(QUERYTRACEON 9481)

SELECT *
FROM dbo.Address
WHERE CONTAINS([Address].*, ' "Detroit" AND "48226" ')
OPTION(QUERYTRACEON 9481)

The first query returns almost 20k rows.  The second, which should be logically the same returns nothing.  When I try the same test on my 2012 server, it returns the same number of rows, so I'm pretty sure it isn't syntax.

My version is Microsoft SQL Server 2014 (SP1-CU6) (KB3144524) - 12.0.4449.0 (X64)
    Apr 13 2016 12:41:07
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: )

There is no stopword list set up for this fulltext index.

The QUERYTRACEON 9481 forces the optimizer to use the cardinality estimator for SQL Server 2012 - this solves another issue we have had with FULLTEXT indexes and TOP in 2014, but makes no difference in this case; the queries return the same number of rows with or without it.

Turns out these are not logically the same; the second requires the results to be in the same column, while the first is across all columns


How can I count all children & grandchildren until the 7th level? I have 3 tables right now

 1. members
 2. members_parents
 3. members_status

How can i count the children & grandchildren until to the depth level and separate the count() for the status 0 = free and 1 = subscriber

table: members - list all the records of members
table: members_parents - list of members & the parent member
table: members_status - list the members & parents status where 0 or 1

tbl_members_parents:

id   member     parent
1    10000      NULL
2    10001      10000
3    10002      10001

and so on ...

How can i count the child & grandchildren until to the 7th level of grand children ?

Thank You

I have bad news and good news
BAD NEWS
MySQL does not support recursive CTE queries
Like @RickJames, I don't have the energy to write it for you
GOOD NEWS
Once upon a time, I did have the energy and time to write stuff like this
Here are my posts I have written discussing Stored Procedures and Recursive Queries

Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs
Dec 10, 2012 : MySQL: Tree-Hierarchical query
Jul 11, 2013 : Recursive self joins
Jan 31, 2014 : Recursive Query in MySQL using stored proceedure and CURSOR
Nov 28, 2014 : Implement a Parent-Level Count in Binary Search Tree

From the first link, I addressed this table structure
CREATE  TABLE IF NOT EXISTS `testdb`.`observations` (
  `id` INT NOT NULL ,
  `parent_id` INT NULL ,
  `name` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

that was holding this data

I made three Stored Procedures centered around them

GetParentIDByID
GetAncestry
GetFamilyTree

What's interesting about the code is that I do not use recursion. I use a String as a comma-separated list of node IDs and process it like a queue. This technique is known as PreOrder Tree Traversal except each tree node can have more than two children.
SUGGESTION
What you need to do is read the code from that first post and apply it with your fields and keys in mind.
GIVE IT A TRY !!!


Modeling Subclass (or Subtype) Constraints in an Entity-Relationship Diagram Scenario description

Can someone help me out on how to model the situation that follows:

There is a superclass (or supertype) called PRODUCTS, which has the subclasses (or subtypes) named FOOD and SHOES.
There exists another superclass denominated CUSTOMERS, with the subclasses denominated SHOE STORE and GROCERY STORE.
I need to represent some rules that indicate that:

FOOD can only be delivered to GROCERY STORES.
SHOES can exclusively be distributed to SHOE STORES.

Tentative diagrams

I have tried the two diagrams shown bellow:

Diagrams considerations

Top diagram

I really do not like the duplication of (a) the same entity type, i.e., ORDERS, and (b) the relationships around it.

Bottom diagram

I am pretty sure this not the proper notation. Should I instead create the relationship on the superclass level and make a note that the restrictions will be implemented at the programming level?

I think you can make an Orders entity that is a relationship between Products and Customers. The Orders would have subclasses FoodOrders and ShoeOrders, which respectively would be the relationships between the subclasses of Food - GroceryStores and Shoes - ShoeStores.

                                            ┌───────────┐
                                            │ Employees │
                                            └─────┬─────┘
                                                  │
                                                  │
      ┌──────────┐               ┌────────┐       │       ┌───────────┐
      │ Products ├─── contain ───┤ Orders ├─── deliver ───┤ Customers │
      └────┬─────┘               └────┬───┘               └─────┬─────┘
           │                          │                         │
           │                          │                         │
          isA                        isA                       isA
          │ │                        │ │                       │ │
     ┌────┘ └────┐             ┌─────┘ └─────┐            ┌────┘ └────┐
     │           │             │             │            │           │
 ┌───┴──┐    ┌───┴───┐         │             │    ┌───────┴───────┐   │
 │ Food │    │ Shoes │         │             │    │ GroceryStores │   │
 └───┬──┘    └───┬───┘         │             │    └───────┬───────┘   │
     │           │       ┌─────┴──────┐      │            │     ┌─────┴──────┐
     └───────────│───────┤ FoodOrders ├──────│────────────┘     │ ShoeStores │
                 │       └────────────┘      │                  └─────┬──────┘
                 │                     ┌─────┴──────┐                 │
                 └─────────────────────┤ ShoeOrders ├─────────────────┘
                                       └────────────┘


DB2 doesn't start after change db2 hostname After changing the hostname of my db2 server, the db2 services won't start anymore.

After reading the notes on how to rename the db2 hostname I understand what went wrong, some things should have been done before restarting the server.

What are my options to fix in this scenario?

You could just try applying those steps.
If that fails, another option would be to create a new instance and then restore the database(s) over to the new instance.
Another option could be uninstalling the instance and then reinstalling it.
Another could be taking a backup of the database, saving it off somewhere and then doing an image restore (pending you have one of those) on the system.

If it were me, I'd just try applying the steps in the rename the DB2 hostname link first. Then I'd probably go for either creating a new instance or uninstalling and reinstalling the existing instance.


How to create a form that allows user to enter a variable number of items I am a total beginner to Microsoft Access 2010. I need a form that allows a user add items from as list to a list. The items can be added until initial list is exhausted.

Here's the most basic example I can give. Lets suppose that two persons attend a car show. As they are leaving, they are asked their name, and what cars they saw. First person saw only two cars. The second one saw fifty cars.

On the form, they would write their name one time (at the top of the page) and then they have to fill the list of cars they saw from the list of all cars. They would not write their name next to each car.

How do I create an equivalent MS Access 2010 form?

You need your answer to be an application - this is too broad.

I try to give basic idea.

You need one form which will be saved to the database each time a person fills the list.

It must contain 1 text box for Persons names. One List with all cars that are on the show and one empty list. And 2 buttons - cancel and save.

For each new person operator write his name of the person. Then operator selects cars from fisrt list and transfer its to the second. This can be done by double click or by selecting a car and pressing additional button. This must work in the opposite direction too.

Then when pressing save button the data will be saved inside database and the form will be reset - first list will contain all cars from the show, second will be empty and text box will be empty.

If operator presses cancel button the form will be reset without saving data to database.

You can try serach internet how to implement those lists in MS Access 2010.


SQL Server 2012 Import Wizard appending new data only I am using SQL Server 2012's Import wizard to import data from DatabaseA to DatabaseB.

Source: DatabaseA table A
Destination: DatabaseB table B

I have written a source query that does the job fine, but I have only 2 options

Delete destination data - and pull in everything
Append data - but this creates duplicates (i.e. pulls in everything all over again)

So I wanted to try modify my source query to be something like below

select col1, col2, col3 from A where col1 not in (select col1 from
DatabaseB.dbo.B)

In other words I want to test that the data I am pulling from DatabaseA's table A does not already exist Database B's table B where col1 is a unique value.

I would like to be able to do this over and over again, only ever pulling across the delta data

Whenever I try this  I get an error stating

  Deferred prepare could not be completed.
    Statement(s) could not be prepared.
    Incorrect syntax near the keyword 'in'. (Microsoft SQL Server Native Client
      11.0)

Some extra things worth noting (might give some clues)

DatabaseA and DatabaseB are on separate servers
DatabaseA.A.col1 is an identity column
DatabaseB.B is a table I created to match the columns of A, it has no
identity  column, or primary keys etc as it is only a staging table for a migration script I will run after

The Import/Export wizard builds out an SSIS package behind the scenes to perform EL (Extract and Load). It really doesn't have a Transform capability since it's a streamlined tool.

What you're trying to do is conditionally load data based on existence. Which is fine, that's a common pattern.

You're attempting to solve it by a non-working query.

select col1, col2, col3 from A where col1 not in (select col1 from

DatabaseB.dbo.B)

This assumes that DatabaseB is on the same instance as A. If you established a Linked Server between A and B, then your query would work - whether it performs at acceptable levels is a different matter.

select col1, col2, col3 from A where col1 not in (select col1 from

Server2.DatabaseB.dbo.B)

A more SSIS native way of doing this would be to add a Lookup between the Source and Destination that matches based on col1 but that cannot be done using the Import Export wizard. You'd have to edit the package using Visual Studio/SQL Server Data Tools (SSDT).

If you absolutely cannot edit the package for reasons, then you have to build 2 packages. The first will export all the Col1 IDs from Destination back to Source. You can then modify the source query to reference the local cache of ids instead of trying to cross servers.

Good all purpose read -> Stairway to Integration Services I think step 3 is an incremental load which is what you're looking for


Whats the difference between information_schema.user_privileges and mysql.user? I have the notion that privileges set in mysql.user should reflect in information_schema.user_privileges but when I try to alter some privileges in mysql.user and queried information_schema.user_privileges they did not match? Am i understanding it wrong?

INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.

Source - http://dev.mysql.com/doc/refman/5.5/en/information-schema.html

You should be taking at the proper commands for modifying accounts and flush PRIVILEGES to reload the PRIVILEGES.

http://dev.mysql.com/doc/refman/5.5/en/account-management-sql.html
http://dev.mysql.com/doc/refman/5.5/en/flush.html


MS SQL 2008 Express - Database Tuning Advisor I want to use DTA, but on my website I have an Express Server version.
Can I backup my DB on Express server, restore it on my personal computer with Enterprise Version, make some tuning by DTA and then restore it back on webserver Express MS SQL? Are they compatible? Both MS SQL 2008 R2, but one is Express and second not.

As long as you don't enable any features that aren't supported on Express Edition, you should be fine. For example I think you can use DTA to recommend table partitioning which obviously won't work on Express edition. The other thing to think about is that the server running Enterprise Edition might have a lot more memory or CPUs, so the optimizer on that server might make much different plan choices than it will on your web server.


How do I move all tables from one Postgres schema to another I want to move all tables from schema xyz to the default schema public.

I can move individual tables with

ALTER TABLE table_name
    SET SCHEMA public;

And I can get all tables with

SELECT table_name FROM information_schema.tables WHERE table_schema='xyz'

Is there any way to combine these two together?

Use PL/pgSQL in a DO statement:

DO
$$DECLARE
   p_table regclass;
BEGIN
   SET LOCAL search_path='xyz';
   FOR p_table IN
      SELECT oid FROM pg_class
      WHERE relnamespace = 'xyz'::regnamespace
        AND relkind = 'r'
   LOOP
      EXECUTE format('ALTER TABLE %s SET SCHEMA public', p_table);
   END LOOP;
END;$$;


how to interpret log files with high transactions I'm having this issues with too many logs files being generated. Over 60gb in the last 24 hours. It's not a massively high transactional website. What is the best way to read the log files and work out a likely cause. I'm using SQL Server 2008

Usually, these questions arise from a misunderstanding of what the log file does. Usually, a larger than expected log is caused by a database in full recovery where log backups aren't being taken.

If you are taking log backups or your database is in simple recovery, there is probably a very large or long running transaction that has required 60GB of log space. KB317375 is a good starting point for understanding why unexpected log growth may occur and includes steps for identifying transactions that could be the cause.


32-bit versus 64-bit ODP.NET to Oracle connection I seem to be having an issue connecting a 32-bit .NET application to a 64-bit server, without a full Oracle client.

If I build a 64-bit application, using the 64-bit ODP.NET dll's or a 64-bit instant client, everything seems to work fine. But if I build a 32-bit ODP.NET application or use the 32-bit instant client, it always goes "looking" for the 64-bit dll's, and errors out on me.

Has anyone encountered this before, or have any suggestions as to what issues we might be having?

We don't have the same problem connecting to SQL Server (of course, SQL server uses mixed-mode assemblies...)

Did you control PATH Environment Variable to which comes first, 32 or 64. Which ever client is installed last,  change PATH accordingly. I have encountered similar issue. Windows uses PATH environment variable to decide which dlls to load. I have two clients installed in my machine and used PATH variable to force which one to use.

C:\oracle\product\11.2.0>dir
 Directory of C:\oracle\product\11.2.0

20.09.2011  10:07    <DIR>          .
20.09.2011  10:07    <DIR>          ..
20.09.2011  10:08    <DIR>          client32Bit
20.09.2011  10:04    <DIR>          client64Bit

C:\oracle\product\11.2.0>echo %PATH%

XXXX;C:\oracle\product\11.2.0\client64Bit\BIN;


Replace column values? I recently got into SSIS and haven't been able to find what I am trying to achieve anywhere on the web.

I am trying to take some rows as input, and then replace one specific column's value with something else, and then return the rows with modified columns as output.

The problem is that I don't know which task to use.

I'm limited by SQL Server 2008.

How do I get started?

The way I would do this is with a Derived Column Transformation.  Take a look at this example (hitting the sample database, AdventureWorks2012):

As you can see on this screenshot, what I'm doing is taking the Name column from AdventureWorks2012.HumanResources.Department (this would work with your version of SQL Server/SSIS as well, I believe.  Although you would know pretty quickly if it doesn't) and using a conditional statement for my Expression:

Name == "Sales" ? "New Sales Name" : Name

What that is, is a short-hand conditional statement.  It takes the Boolean result of Name == "Sales".  If that's true (i.e. the Name is "Sales") then return "New Sales Name", or whatever string you'd want to substitute.  If that's false (the Name is not "Sales") then just return the Name as it originally was.


How to avoid delimiter character in BCP export? In my project, I need to use SQL 2008 BCP tool to export the result of a stored procedure into a fixed column width file. I used different approaches, but still couldn't get expected result. Things I tried are explained below.

(The database table from where the SP is fetching values is designed with all columns as varchar with width as needed by the specification of the requirement.)

First I tried (as per this site)

bcp "my_sample_sp" queryout "C:\table_output.txt" -S <servername> -d <dbname> -U <username> -P <password> -c -t \0 -a 10000 -L 100

This gave me the result with a single space between all columns as delimiter.
Unexpected. Dont need any delimiter.

Second, I tried

bcp "my_sample_sp" queryout "C:\table_output.txt" -S <servername> -d <dbname> -U <username> -P <password> -c -t \0x90 -a 10000 -L 100

This gave me the result with a hidden character between all columns as delimiter.
Unexpected. Most editors and the end application consuming this file read that invisible value as one character.

Third, I tried with format file in bcp tool. (followed this site)

Created native format file using below command

bcp "my_sample_Table" format nul -n -f C:\format_file.fmt -S <servername> -d <dbname> -U <username> -P <password>

Created the flat file using this format file as below.

bcp "my_sample_sp" queryout C:\table_output.txt -f C:\format_file.fmt -S <servername> -d <dbname> -U <username> -P <password>

File is created with all unreadable characters. I understand that native option is to only create files readable by SQL servers. I tried almost all options around this which are mentioned in the referred site.

Using SQLCMD tool, I was able to create the required output. As the count of records I need to export would easily cross 5 million records, I was told that SQLCMD tool is not suggested for that much count.

I tried all possible approaches before posting here. Any help which can guide me in the correct direction to create a fixed width flat file from a SP output is much appreciated.

I managed to make this work by combining all columns into one single string from SQL and return it to BCP tool.


How to find an entry from filtering with two associated tables This questions has spinned off of another question I probably did not ask correctly from the get go.

I have some tables, where attributes and attributes_entries that hold a pair of dynamic values for products.

The user table (just used as an example):

id     name       whatever
22     thanos     big guy

The products table:

id   code    price
659  0H040   99.99
660  0H040   99.99

The attributes table:

id   title           model      model_id
1    nickname        products   659
2    manufacturer    products   659
3    rhod            products   660
4    manufacturer    products   null
5    age             user       null
6    age             user       22

The attribute_entries table:

id     title      FK_attribute_id
1      windstar   1
2      ford       2
3      75.3       3

I have to find the product that matches the attributes and attribue_entries a user is looking for.

So, if the search filter is manufacturer=ford&nickname=windstar and these are found for product 659 then provide in data set.

How would I go about doing this?  I have a fiddle with queries from my previous question but I can't seem to get the proper results.

The desired result is something along the lines of:

attr_title      attr_entry_title    attr_model      attr_model_id
nickname        windstar            products        659
manufacturer    ford                products        659

But what I really need is to get the product id that simply has a match of both these value pairs manufacturer=ford&nickname=windstar.

Since I do not have a FK association to products, I think this is going to be a problem.  I didn't associate the products because some attributes are entered globally under a model like products or users, so when someone enters a product or user they can see a dynamic input under attributes called manufacturer, and can enter a value for this to associate it to the model (ex.products).

I think I'm going to have to redo this structure since a reverse lookup of attributes and attribute_entries I have no idea how to find its model id value that has no association.

Is this what you're looking for?

select
    a.title as 'attr_title',
    ae.title as 'attr_entry_title',
    a.model as 'attr_model',
    p.id as 'attr_model_id'

from
    products p
inner join
    attributes a on a.model_id = p.id
inner join
    attribute_entries ae on ae.FK_attribute_id = a.id
where
    ae.title in('ford','windstar')

You might be able to modify the where clause like this to exclude unselected terms:

ae.title not in (select title from attribute_entries where title not in ('ford', 'windstar')


Create a maintenance plan for sql database with C# We are planning to create c# jobs that perform some database maintenance actions such as backup, shrink files etc...

I have read in many blogs that database shrink is not recommended and thats why I'm posting here in order to have some recommendations to this topic.

We want also to rebuild / reorganize the indexes. This is an easy one and I already wrote the C# script for it.

The backup for the databases and shrink for the transactions log should be also done. The databases have recovery model: Full.

Some blogs recommend this order:

Backup the database
Change recovery model
Shrink the .ldf file
Re-set the original recovery model

some others recommend this not to do shrink at all, are to use recovery model: simple.

My questions are:

How often should we rebuild or reorganize indexes in the sql tables?
Is shrink for log files a good idea?
In which order should we do the database backup / logs backup / logs shrink?

I don't have enough DBA experience, so it will be great if you can give me some hints.

Thanks

Aaron Bertrand is highly regarded in the SQL community, he is asking about the source to understand the reason behind shrinking the logs. There are very few cases where shrinking a log file is a sound process. In the question you ask if it is a good idea, and then in the reply you cite it is a requirement, which implies there is no room for discourse.

2)
As a rule of thumb you do not shrink a log file, unless you understand why you should, or the technical details of the log file. If you are to shrink a log file, it should especially not be based on a time-frame or arbitrarily dictated.

The point is made Aaron or DBA01 would be amiss to give instruction that contradicts sound advice.

Basically you are only causing issues for the database, that often have a performance impact for the users, and will likely cause you or DBA01 additional grief.

Your questions elicit the golden default SQL answer 'It Depends'. You lack enough information for someone give you the advice you seek.

1)
Rebuilding indexes is an offline operation, typically done when there is 30% fragmentation, when this will occur is based on how often you are updating said indexes. Reorgs are online operations, typically done after ~5% fragmentation but to do this every 30 minutes would also be futile. There are maintenance plan walk-throughs you could look up.

Aaron also hints at a larger issue, why C#? If anything it would be better to decouple your application from the database. One approach might be to use the tool set provided and then call the agent job or process from your C# application.

3) It Depends ...
What are you trying to accomplish with your backups, RTO? RPO? which one is the focus, what are acceptable data losses?


Half of a transaction completes, when the other half fails? I have done some searching on SO, MSDN, and various other sources, and I found plenty of questions about transactions, but none that seem to be exactly what I'm dealing with.

Admittedly I'm still very much a junior DBA, though I do understand the concept of ACID and transactions.  I'm working on a SQL script that:

Adds a row to 2 different tables, provided the values aren't already
there
Drops a column if it exists
Adds a column if it doesn't exist
Alters 2 stored procedures that generate dynamic SQL to build a report query

All of the above operations work on their own, and they also work together when wrapped up in (what I believe to be) a single transaction.  What I'm trying to understand is how "part" of the transaction completes (and presumably commits) when another part of it fails.

For the sake of brevity, assume I have working procedures called "ProcessReport" and "RetrieveReport."  The dynamic SQL in both of these procedures starts with "SELECT" and then builds from there.  Test cases:

CREATE TABLE ReportTable (FirstName VARCHAR(100), LastName VARCHAR(100), OrderId INT);
GO
CREATE PROCEDURE ProcessReport
AS
BEGIN
  DECLARE @SQL VARCHAR(4000)

  -- start creating dynamic sql
  SET @SQL = 'SELECT FirstName, LastName, '
  --create rest of dynamic SQL here...

  INSERT INTO ReportTable (FirstName, LastName) EXEC(@SQL)
END
GO

CREATE PROCEDURE RetrieveReport
AS
BEGIN
  DECLARE @SQL VARCHAR(4000)

  -- start creating dynamic sql
  SET @SQL = 'SELECT FirstName, LastName '
  --create rest of dynamic SQL here...

  SET @SQL = @SQL + ' FROM ReportTable '
  EXEC (@SQL)
END
GO

Now that the procedures exist, here's what has me kerfuffled:  when I purposely enter the wrong name for the first ALTER PROCEDURE, the edits I made to the second procedure are completed.  Below is the short version of the transaction I've written:

BEGIN TRANSACTION
GO

ALTER PROCEDURE ProcessReport2  -- this procedure does not exist, causing an error
AS
BEGIN
  DECLARE @SQL VARCHAR(4000)

  -- start creating dynamic sql
  SET @SQL = 'SELECT FirstName, LastName, '
  --create rest of dynamic SQL here...

  INSERT INTO ReportTable(FirstName, LastName) EXEC(@SQL)
END
GO
ALTER PROCEDURE RetrieveReport
AS
BEGIN
  DECLARE @SQL VARCHAR(4000)

  -- start creating dynamic sql
  SET @SQL = 'SELECT DISTINCT FirstName, LastName '  --DISTINCT added
  --create rest of dynamic SQL here...

  SET @SQL = @SQL + ' FROM ReportTable '
  EXEC (@SQL)

END
GO

IF @@ERROR = 0
  COMMIT TRANSACTION;
ELSE
  ROLLBACK TRANSACTION;

When running the above statement, I get the below error:

  Msg 208, Level 16, State 6, Procedure ProcessReport2, Line 2
  Invalid object name 'ProcessReport2'.

  Msg 3902, Level 16, State 1, Line 3
  The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

With regard to ACID and transactions, it was my understanding that either everything between BEGIN TRANSACTION and COMMIT TRANSACTION completes, or nothing does.  In SSMS, when I click "Modify" on the RetrieveReport procedure, I now see the dynamic SQL starting with "SELECT DISTINCT..."

Can anyone tell me where I'm going wrong here?  Is it something in my

IF @@ERROR = 0
COMMIT TRANSACTION;

ELSE
ROLLBACK TRANSACTION;

My intent with that was "if there are no errors, commit the whole thing, if there are errors, don't do anything."

I'm using SQL Server 2012 Enterprise.

Any help and/or insight will be greatly appreciated.

@@ERROR is reset after every statement. The error from your attempt to alter the first procedure is no longer detectable via @@ERROR after you successfully altered the second procedure. Here is an even simpler repro:

SELECT 1/0;
GO
SELECT @@ERROR; -- 8134

However if I put a successful statement in between:

SELECT 1/0;
GO
SELECT 1/1;
GO
SELECT @@ERROR; -- 0

In your case, you are doing this:

ALTER dbo.p1 ... -- fails
GO
-- @@ERROR here would be <> 0, but you're not checking it here!

ALTER dbo.p2 ... -- succeeds
GO
-- You're checking @@ERROR here, but success is 0

The only way your current logic would roll everything back is if the last ALTER failed. You need to check for @@ERROR after every ALTER. Here is an example.

USE tempdb;
GO
CREATE TABLE #x(err INT);
GO

BEGIN TRANSACTION;
GO

CREATE PROCEDURE dbo.foo -- fails
AS
  SELECT foo FROM sys.objects;
GO
IF @@ERROR <> 0
  INSERT #x(err) SELECT 1;
GO

CREATE PROCEDURE dbo.blat -- succeeds, but will get rolled back
AS
  SELECT 1;
GO
IF @@ERROR <> 0
  INSERT #x(err) SELECT 1;
GO

IF EXISTS (SELECT 1 FROM #x)
  ROLLBACK TRANSACTION;
ELSE
  COMMIT TRANSACTION;

DROP TABLE #x;


Managed backups in Azure SQL Managed Instance The Managed Instance allows the admin to ENABLE Compress backups at the instance level.

Does that affect the managed backups?
How does the Managed backups work? as i don't see any record in backupset table.

I doubt that the "compress" setting affects the managed backups either way.  But there is no way to check as you don't have direct access to it.  I have caught the system taking backups before, the commands show using sp_whoisactive, but can't say that I've ever looked at the full command string before.  I will try and catch one of mine at it and update my answer if I can.

As for how managed backups work.  The management system periodically takes full and log backups according to it's own schedule.  There are some events that seem to spark a full backup to be taken that we have observed.  Creating/Restoring a database, failing between failover group nodes and when the service restarts (probably Azure balancing it's internal workload).

These backups are stored in a hidden storage account and can only be accessed through powershell.  Cross-subscription restores are not possible through powershell using managed backups.

You can additionally take COPY_ONLY backups to URL and then restore from there.  But the backups can only be restored onto another managed instance (so no restore to on-prem of any version).  However, if you have enabled TDE with Azure managed key rotation, this option is locked out for you.  Enabling TDE is the default for databases created through CREATE DATABASE, although databases restored (as in restored from on-prem) carry their TDE setting with them.


Find out who ran a query using transaction log backups We have a SQL Server 2012 Enterprise database running in Full Recovery Model with transaction log backups running every minute. A query was run last night which means we need to recover data from a certain point in time - so we restore transaction logs to that point in time, recover the data we need, all good.

What we want to know, is there a way that we can tell what login ran a query using the transaction log backups?

Converting my comment as answer :

You have to use undocumented fn_dblog Begin Time, SUSER_SNAME([Transaction SID]) or fn_dump_dblog -- when reading from T-log backups. Be careful and try it on a NON PROD system as every time fn_dump_dblog is called, it creates a new hidden SQLOS scheduler and up to three threads, which will not go away (and will not be reused) until a server restart.

Also, its not that straight forward to be able to read T-log and come on a conclusion - If you have not done it or are new to it. You are better off trying a 3rd party tool (like Apex SQL Log) if it is that critical for you to find out who did the mess.


MongoDB Fails to get TGT I've setup MongoDB enterprise on CentOS 7 with Kerberos support. Additionally I've setup a Kerberos service on CentOS 7 for the authentication. I know that Kerberos is setup correctly as I now have multiple CentOS 7 clients SSH authenticating against kerberos. When I start MongoDB with Kerberos, I get the following error in the MongoDB Enterprise logs.

2016-04-05T16:30:50.515-0500 F CONTROL  [main] Failed global initialization:
UnknownError: gssapi could not acquire server credential for
mongodb/mongodb.centos7.vm@CENTOS7.VM; Major code 851968; Unspecified GSS failure.
Minor code may provide more information; Minor code 39756033; No key table entry
found for mongodb/mongodb.centos7.vm@CENTOS7.VM;

I've also verified that a ticket has been generated via kinit

[root@mongodb mongodb]# kinit mongodb/mongodb.centos7.vm@CENTOS7.VM
Password for mongodb/mongodb.centos7.vm@CENTOS7.VM:
[root@mongodb mongodb]# klist
Ticket cache: FILE:/tmp/krb5cc_0
Default principal: mongodb/mongodb.centos7.vm@CENTOS7.VM

Valid starting     Expires            Service principal
05/04/16 17:17:11  06/04/16 03:17:11  krbtgt/CENTOS7.VM@CENTOS7.VM
    renew until 06/04/16 17:17:09

I then verified the principals exist on both client and server

[root@mongodb mongodb]# kadmin -p root/admin
Authenticating as principal root/admin with password.
Password for root/admin@CENTOS7.VM:
kadmin:  listprincs
K/M@CENTOS7.VM
...other principals
mongodb/mongo.centos7.vm@CENTOS7.VM
mongodb/mongodb.centos7.vm@CENTOS7.VM
root/admin@CENTOS7.VM

What is preventing mongo to see and use this principal? I've verified my DNS is working correctly and all other Kerberos functionality seems to be working as expected. I'm unbelievably stumped with something that seems like it should be a simple fix.

I think that you missed one step. You should add an entry into the keytab file for your principal mongodb/mongodb.centos7.vm@CENTOS7.VM

Suppose that the path for your keytab file is /etc/krb5.keytab. Now run $ktutil

ktutil: add_entry -password -p mongodb/mongodb.centos7.vm@CENTOS7.VM -k 1 -e des-cbc-md4
ktuitl: wkt /etc/krb5.keytab
ktuitl: quit

Now you can run $klist -k /etc/krb5.keytab
and the new entry should appear on the output.

Now, I think you can start mongod service.


Create a key-preserved view across Oracle tables with descending indexes I'm trying to create an updatable view between two Oracle tables which have exactly the same key structure. The existing tables are created by an ERP system and:

Do not have primary keys - only a unique index.
All fields are non-null, but one of the composite index fields is descending.

Is there any way I can create an updatable view across these two tables?

The real tables concerned contain a lot of data and are heavily used, so any changes / additions to the indexes would require significant regression testing, so I need to confine any solution to the view itself.

Problem seems to be something to do with Oracle creating separate composite function based indexes for the descending KEY2 fields. I've tried throwing a few hints into the view but am out of my depth here, and really not much idea what I'm doing.

Here's a simplified example. If I remove the "desc" from the two KEY2 index fields the final update works - if I leave it in it fails with: "ORA-01779: cannot modify a column which maps to a non key-preserved table".

create table TEST_KEY_PRES_A (
   KEY1 varchar2(8) not null,
   KEY2 varchar2(8) not null,
   VAL1 smallint not null,
   VAL2 varchar2(8) not null
)
;

create unique index TEST_KEY_PRES_A_IDX
on TEST_KEY_PRES_A (KEY1, KEY2 desc)
;

insert into TEST_KEY_PRES_A values ('K11', 'K21', 1, 'V2-1');
insert into TEST_KEY_PRES_A values ('K15', 'K25', 5, 'V2-5');

create table TEST_KEY_PRES_B (
   KEY1 varchar2(8) not null,
   KEY2 varchar2(8) not null,
   VAL3 varchar2(8) not null
)
;

create unique index TEST_KEY_PRES_B_IDX
on TEST_KEY_PRES_B (KEY1, KEY2 desc)
;

insert into TEST_KEY_PRES_B values ('K11', 'K21', 'V3-1');
insert into TEST_KEY_PRES_B values ('K15', 'K25', 'V3-5');

create view TEST_KEY_PRES_VW (KEY1, KEY2, VAL1, VAL2, VAL3) AS
select pa.KEY1, pa.KEY2, pa.VAL1, pa.VAL2, pb.VAL3
from   TEST_KEY_PRES_A pa
join   TEST_KEY_PRES_B pb on pa.KEY1 = pb.KEY1 and pa.KEY2 = pb.KEY2
where  pa.VAL1 > 3
;

update TEST_KEY_PRES_VW
set    VAL2 = 'V2-5-X'
where  KEY1 = 'K15'
;

SOLVED

Using INSTEAD OF trigger as suggested by Eduard:

create or replace trigger TEST_KEY_PRES_VW_UPD_TR
instead of update on TEST_KEY_PRES_VW
for each row
begin
    update TEST_KEY_PRES_A
    set    VAL1 = :new.VAL1, VAL2 = :new.VAL2
    where  KEY1 = :new.KEY1 and KEY2 = :new.KEY2;

    update TEST_KEY_PRES_B
    set    VAL3 = :new.VAL3
    where  KEY1 = :new.KEY1 and KEY2 = :new.KEY2;
end;

if you are not required to create view, but just to update using join, then we can use a workaround with a MERGE statement.

merge into TEST_KEY_PRES_A pa
using TEST_KEY_PRES_B pb
  on (pa.KEY1 = pb.KEY1 and pa.KEY2 = pb.KEY2 and pa.VAL1 > 3 and pa.KEY1 = 'K15')
 when matched then update
   set VAL2 = 'V2-5-X';


Create failed for Availability Group 'AGN' I tried creating availability group (AOH) many times but no luck , It got failed every time. Please help me on this case.

I configured Windows cluster (Name as 'WINCLUSTER') with two nodes. They are
1. Node1
2. Node2

in that I configured SQL server cluster (Active/Active) with  two instances.
one instance named as 'HRITS\BAK' and other 'HRITS1\BAK1'.

I made sure configuration met all prerequisite and important steps before configuring AOH.

I created on database as sampledb in 'HRITS\BAK' instance in node1.
and I tried to creating Alwayson group with necessary steps.

But I am facing one error and not able to troubleshoot. Please find the below screen shot and please provide guideline for configuring it successfully.

Please let me know if any further details are required for resolving this issue.

I see you're combining Failover Cluster Instances (FCIs) and Database Availability Groups. That's fine, except that the installer won't allow you to set up an availability group on failover cluster instances that can be owned by the same servers, just like the error message says. In other words, the set of servers that can run HRITS\BAK can't overlap with the set of servers that can run HRITS\BAK1.

An availability group doesn't give you much additional fault tolerance in the scenario you described because if Node1 or NODE2 failed, both failover cluster instances would be running on the same server.

To get around the error, you could reinstall the SQL Servers as standalone instances. Alternatively, if this design is what you really want, I believe the installer might work if you change the 'possible owner' setting for resources in each cluster group. This is possible with Powershell, for example:

Get-ClusterGroup -Name "SQL Server (BAK)"|Get-ClusterResource|Set-ClusterOwnerNode -Owners NODE2
Get-ClusterGroup -Name "SQL Server (BAK1)"|Get-ClusterResource|Set-ClusterOwnerNode -Owners node1

That said, this design might not make any sense for the aforementioned reasons unless it's just for a lab.


How to automatically execute a stored procedure every day at a particular time? Can you please help me how to execute a stored procedure automatically in SQL database without any transaction in SQL? I want to execute on particular fix time.

If you are not comfortable using Powershell, then you can go for native service broker functionality.

This might sound complex, but below links has all the scripts :

Scheduling Jobs in SQL Server Express - Part 1 and

Scheduling Jobs in SQL Server Express - Part 2
Alternatively, you can use Standalone SQL Agent - still in BETA statge from codeplex.
You can use the native sqlcmd functionality and then schedule it using windows scheduler or Schtasks

sqlcmd -E -S server_name -d database_name -Q "EXEC schema_name.StoredProcedureName @p1"


Optimizing a query which runs through all hours in a day I need help optimizing a query. I'm using PostgreSQL 9.3.4 currently but can upgrade to 9.4 if needed.

I have a table with 60+ millions of records which looks like this:

                                                          Table "public.snapshots"
   Column   |           Type           |                         Modifiers                          | Storage  | Stats target | Description
------------+--------------------------+------------------------------------------------------------+----------+--------------+-------------
 id         | integer                  | not null default nextval('snapshots_new_id_seq'::regclass) | plain    |              |
 camera_id  | integer                  | not null                                                   | plain    |              |
 created_at | timestamp with time zone | not null                                                   | plain    |              |
 notes      | text                     |                                                            | extended |              |
 data       | bytea                    | not null                                                   | extended |              |
 is_public  | boolean                  | not null default false                                     | plain    |              |
Indexes:
    "snapshots_new_created_at_camera_id_index" UNIQUE, btree (created_at, camera_id)
Foreign-key constraints:
    "snapshots_new_camera_id_fkey" FOREIGN KEY (camera_id) REFERENCES cameras(id) ON DELETE CASCADE
Has OIDs: no

There can be between 0 and 3600 snapshot records in an hour. For this query I'm interested only in knowing which hours in a given day for a given camera_id have 1 or more snapshot records (the actual count is unimportant).

Currently the application is implemented to execute one query for each hour in the day, like this:

SELECT count(*) AS "count" FROM "snapshots" WHERE (("snapshots"."camera_id" = 4809) AND ("created_at" >= '2015-05-24 23:00:00 UTC') AND ("created_at" <= '2015-05-24 23:59:59 UTC'));
SELECT count(*) AS "count" FROM "snapshots" WHERE (("snapshots"."camera_id" = 4809) AND ("created_at" >= '2015-05-25 00:00:00 UTC') AND ("created_at" <= '2015-05-25 00:59:59 UTC'));
...
SELECT count(*) AS "count" FROM "snapshots" WHERE (("snapshots"."camera_id" = 4809) AND ("created_at" >= '2015-05-25 22:00:00 UTC') AND ("created_at" <= '2015-05-25 22:59:59 UTC'));

  Explain analyze for one query: http://explain.depesz.com/s/9tbP

I tried optimizing this and ended up with one query which seemed like it was what I needed:

SELECT count(*) AS "count" FROM "snapshots" WHERE (("created_at" > '2015-05-06 23:00:00.000000+0000') AND ("created_at" < '2015-05-08 23:00:00.000000+0000')) GROUP BY date_trunc('hour', created_at);

  Explain analyze for this query: http://explain.depesz.com/s/cVUK

But this is actually 10x times slower than the above 24 queries combined.

What am I doing wrong? Should I forget COUNT since I'm not interested in the actual count? How would the query look like then?

Edit: Thanks for all the comments and answers, I learned multiple things from you! I wish I could accept all three answers, I chose ypercube's as it seems most efficient and flexible.

Just an idea: create a table with all hours in the day (0-23).

create table hours(
  hr integer
);

Then find all hours that have snapshots for the given camera_id and date (of course, you will need to substitute your own camera_id and date into the query):

select h.hr, 1 as camera_id
from hours h
where exists (
    select 1
    from snapshots s
    where s.camera_id = 1
    and s.created_at between to_timestamp ('2015-05-01 ' || to_char(h.hr, '00') || ':00:00', 'YYYY-MM-DD HH24:MI:SS')
                             and to_timestamp ('2015-05-01 ' || to_char(h.hr, '00') || ':59:59', 'YYYY-MM-DD HH24:MI:SS')
)

See this on SQLFiddle. To return a count(*) from the table the database has to go through all the rows that satisfy the condition to count them; exists() should limit this to finding the first row that satisfies the condition.


Use another disk for "optimize table" temporary file I have a large table (70 Go) on a disk with only 9 Go free space, which I need to optimize ("optimize table ..").

I have changed the "tmpdir" parameter so that the temporary file is created on another disk with enough space.

However the temporary file "#sql..." is still created in the mysql data folder, where tables are located.

Is there a way to tell mysql to create the temporary file on another disk for "optimize table"?

If not, is the only solution to transfer the table onto another disk, run "optimize table" there, then replace the old table file?

Here is the row returned by "SHOW TABLE STATUS" for the table:

Engine  Version Row_format  Rows    Avg_row_length  Data_length Max_data_length Index_length    Data_free   Auto_increment  Create_time Update_time Check_time  Collation   Checksum    Create_options  Comment
InnoDB  10  Compact 218780456   176 38628507648 0   18179555328 17278435328 290029517   2016-02-25 16:33:50 NULL    NULL    utf8_general_ci NULL

If you have deleted most of the rows from a table, OPTIMIZE table needs only enough room to build a new, smaller, copy.  After the copy is finished, it will rename, then drop the old table.

If the table had its own tablespace (built with innodb_file_per_table=ON), then the freed space will be returned to the OS.  But...  The 17GB free in the .ibd cannot be used for the table copy.  The copy will be in a separate file.  So it could fail if the resulting table is more than 8GB (data+index).

If =OFF, the OPTIMIZE will try to use free space in ibdata1 before increasing the size of that file.

There is 17GB free somewhere; I can't tell where.

Also, check TABLE STATUS for other tables.  The Data_free for all tables in ibdata1 will be the same value.  What is that value?

There are tricks to work around the cramped space, but I need answers to the questions posed.


How to add 'root' MySQL user back on MAMP? On PhpMyAdmin, I removed 'root' user by mistake. I was also logged in as 'root'. How can I add the user 'root' back, on MAMP?

MySQL's suggested procedures

Assuming you went with a generic install of MySQL, my procedure for MAMP is:

1. Edit your my.cnf, probably located at: /usr/local/my.cnf, adding the lines:

skip-grant-tables
skip-networking

2. Restart mysqld

sudo su -
/usr/local/mysql/bin/mysqladmin shutdown
nohup /usr/local/mysql/bin/mysqld_safe &
exit

3. Change root privs

`mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'password' WITH GRANT OPTION;`

4. Remove the skip lines from the my.cnf

## skip-grant-tables
## skip-networking

5. Restart mysqld again

sudo su -
/usr/local/mysql/bin/mysqladmin shutdown
nohup /usr/local/mysql/bin/mysqld_safe &
exit


How to combine subqueries? There are two subqueries select a set according to certain criteria, such as

select
    id, dt_beg, dt_end
  from ...................
  where ..............

it is necessary to select data from the second if there is no record zero, if it is zero, then from the first, it was originally thought (erroneously), thinking that the first will never return the record zero, therefore the second one

more or less like this:

SELECT
    ao.id,
    DECODE(NVL(an.id,0), 0,ao.dt_beg, an.dt_beg) AS dt_beg,
    DECODE(NVL(an.id,0), 0,ao.dt_end, an.dt_end) AS dt_end
  FROM
  (.....) ao, (.....) an
  WHERE ao.id = an.id(+)
    AND ao.id = _NUMBER_
;

But, it turned out, there are situations when the first returns NULL, while the second is not NULL
and just the data from the second priority, i.e. if they are not NULL, then they must be substituted.

tried to do FULL OUTER JOIN
but nothing is fetched, although there is definitely data in the second query.

SELECT
    ao.id,
    DECODE(NVL(an.id,0), 0,ao.dt_beg, an.dt_beg) AS dt_beg,
    DECODE(NVL(an.id,0), 0,ao.dt_end, an.dt_end) AS dt_end
  FROM
  (.....) ao
  FULL OUTER JOIN (.....) an ON ao.id = an.id
  WHERE ao.id = _NUMBER_ OR an.id = _NUMBER_
;

How to combine and select data?

You may use

WITH
cte1 AS ( 1st query ),
cte2 AS ( 2nd query )
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte2 WHERE ( SELECT COUNT(*) FROM cte1 ) = 0

Of course, record structure of both subqueries must be the same.


Will AlwaysOn work with hundreds of databases in 1 group/multiple groups? I have inherited a SQL 2017 system that had 114 databases that were synched to a second instance via an Always-On AG, with another 13 databases needing to be added. It was supposed to be providing High Availability.
Long story short, it ended in tears as I started to add in the additional ones.
I know that Microsoft has tested Always-On with up to 100 databases. What I have not been certain about is whether that limit of 100 is a SQL instance limit or per AG.
What I would like to know is if anyone has had experience/success with Always-On with a large number of databases, say 2 or 300? If so was this in a single or multiple groups.
This installation failed due to insufficient memory - I found out the secondary has half the memory of the primary so it is no surprise it came a cropper. There were HADR waits indicating throttling of requests being sent from the primary.
There is a great blog article by Bob Dorr about Always-On and worker pool usage ( https://docs.microsoft.com/en-gb/archive/blogs/psssql/alwayson-hadron-learning-series-worker-pool-usage-for-hadron-enabled-databases )
If the secondary server is beefed up memory-wise, and possibly the primary, is it worth attempting to get this to work using the info from Bob Dorr's article? Or do I cut my losses and go with a SQL cluster, and then log shipping when management realise the costs involved?
Thanks for any thoughts
Simon

Increase the hardware capacity or look for Cloud Solutions, if possible. This is from official doc:

Maximum number of availability groups and availability databases per computer: The actual number of databases and availability groups you can put on a computer (VM or physical) depends on the hardware and workload, but there is no enforced limit. Microsoft has tested up to 10 AGs and 100 DBs per physical machine, however this is not a binding limit. Depending on the hardware specification on the server and the workload, you can put a higher number of databases and availability groups on an instance of SQL Server. Signs of overloaded systems can include, but are not limited to, worker thread exhaustion, slow response times for availability group system views and DMVs, and/or stalled dispatcher system dumps. Please make sure to thoroughly test your environment with a production-like workload to ensure it can handle peak workload capacity within your application SLAs. When considering SLAs be sure to consider load under failure conditions as well as expected response times.

I had working experience with huge databases (size), not large in numbers( Availability Groups and databases)
From my experience and based on the research, there is no limit to number of Availability Groups and number of databases by SQL instance or AG. If you have sufficient hardware capacity, you can host any number of databases. If there is not enough memory, even in SQL cluster you will face the issue.


Covering index in a partitioned table I have a table with 20 millions of rows called [transaction].

That table is partitioned by transaction_date.

Can I create a non clustered index in that table even if it is partitioned?

Example:

CREATE NONCLUSTERED INDEX [IX_TRANSACTION_ID_TRANSACTION_DATE]
ON [dbo].[transaction] ([id])
INCLUDE ([transaction_date])
GO

Here is the table schema:

CREATE TABLE [dbo].[TRANSACTION](

    [id] [numeric](19, 0) IDENTITY(1,1) NOT NULL,
    [transaction_date] [datetime] NULL,
    [transaction_datetime] [datetime] NULL
PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Are there any considerations to take into account? Or what should be the best practices on it.

The create table DDL in your question is for a non-partitioned table. It cannot be partitioned without schema changes since the partitioning column must be part of the primary key (and other unique indexes/unique constraints) and primary key columns cannot allow NULLs.

That said, you can create non-clustered indexes on a partitioned table. The index will be partitioned using the same scheme as the table by default. You can also specify a filegroup for the non-clustered index, which would create  a non-partitioned index. It is generally advisable to partition non-clustered indexes similarly to the table (align) in order to allow switching partitions between tables.

BTW, I strongly recommend you avoid using reserved keywords line TRANSACTION as object and column names.


Number of locks exceeded when adding foreign key constraint I'm adding a foreign key constraint to table a (17mil rows) that references table b (15mil rows):

>ALTER TABLE a ADD FOREIGN KEY (b_id) REFERENCES b (id);
ERROR 1206: The total number of locks exceeds the lock table size

I guess the client is acquiring a lock for each row, so I tried locking the table first without success:

>LOCK TABLES a WRITE;
Query OK, 0 rows affected (0.00 sec)

>ALTER TABLE a ADD FOREIGN KEY (b_id) REFERENCES b (id);
ERROR 1206: The total number of locks exceeds the lock table size

Why exactly are there so many locks, i.e. what is being locked in this query?
Why didn't locking the table solve the problem? Do I need to do the lock in a different way or lock something else as well?
If the only solution is to increase the innodb_buffer_pool_size, how large does it need to be for this query?

I obviously don't need a sustainable solution - just need to do this the one time. For example, the box isn't a dedicated database server but I could increase the pools size brief it for this query and then set it back to something reasonable afterwards.

According to the MySQL Performance Blog

  Lets start with a bit of background – in Innodb row level locks are
  implemented by having special lock table, located in the buffer pool
  where small record allocated for each hash and for each row locked on
  that page bit can be set. This in theory can give overhead as low as
  few bits per row, so lets see how it looks in practice

The same reference brings this out...

  So we locked over 100K rows using about 44KB. This is still quite
  efficient using less than 4 bits per locked row.

  In practice this means memory consumption by row level locks should not be
  the problem even for rather large databases – even billion of locked rows should
  take half GB of memory, which is small fraction of memory used on
  serious systems. Furtermore you would unlikely need or want to lock
  every row in your table/database which makes it even smaller problem.

Evidently, all 17 million rows need to be locked. Based on the link from mysqlperformanceblog.com:

100,000 rows takes 44 KB
17,000,000 rows should take 7480 KB, just over 7 MB

I can easily see an unconfigured innodb_buffer_pool_size (Default is 128M in MySQL 5.5, and 8M before MySQL 5.5) causing a problem.

OK Let's get back to your questions:

  Why exactly are there so many locks, i.e. what is being locked in this
  query?

Every row in an InnoDB table must be locked. This makes the data available via MVCC, ACID Compliance, and Transaction Isolation.

  Why didn't locking the table solve the problem? Do I need to do the
  lock in a different way or lock something else as well?

Same as Answer 1

  If the only solution is to increase the innodb_buffer_pool_size, how
  large does it need to be for this query?

There must be enough RAM to not only hold your working set of InnoDB data and index pages, but also small amount of overhead (44KB per 100,000 rows) for table locks.


Full-text index in partitioned table I have a table with just over a million records with a full-text index.

This table has been split in two in the past, and the data older than a certain date is moved to another table with the exact same structure in a separate database at the end of every year. This second table has approximately 3+ million records.

I can only guess why this has been done, but now I've been asked to merge this two tables back into a single one, and partition it. I'm running SQL Server 2005.

Will the full-text search work in partitioned tables?

Are there any recommendations regarding this situation, or anything I should pay attention to?

I did some more research and found a couple of useful articles with best practices regarding table partitioning and full-text searching on large tables. As I haven't received any answers to this question, I thought I'd post what I've found here for future reference.

I'm still reading those, but for what I've already read, I've found both of them useful and relevant to this particular situation:

SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned:

  The observations in this paper are based on tests run in the SQL
  Server Customer Lab for a customer who needed to scale up full-text
  search to a much greater potential volume. The paper describes the
  customer scenario, provides an overview of SQL Server 2005 full-text
  concepts that bear on the results, and offers lessons learned and
  recommendations for using full-text queries on large catalogs.

Partitioned Tables and Indexes in SQL Server 2005

  Summary: Table-based partitioning features in SQL Server 2005 provide
  flexibility and performance to simplify the creation and maintenance
  of partitioned tables. Trace the progression of capabilities from
  logically and manually partitioning tables to the latest partitioning
  features, and find out why, when, and how to design, implement, and
  maintain partitioned tables using SQL Server 2005. (41 printed pages)


How is ‘tempdb’ getting used when Always On Failover happens? How is tempdb utilized as Failover occurs?

Let's say there are two servers, server 1 is primary and server 2 is secondary. Suddenly, Failover happens, so, how does tempdb behave during this situation?

Tempdb doesn't move when an AG failover occurs. Each replica is its own standalone instance. Each has its own tempdb. Queries will break during the failover. Once the application reconnects after the failover is complete and crash recovery is also complete, the queries can then run on the other instance where it has its own tempdb.

A Failover Cluster Instance fails over at the instance level, so tempdb is affected. But not for Availability Groups.


Trying to recover/import/transfer database from MySQL data dir I used phpMyAdmin->export to create a backup of my database but I have now discovered it did not export the whole database, some tables was missed/refused.

However at the time of doing the export I also saved the whole data directory located at mysql/data/ which has many .ibd and .frm filetypes, which seem to reflect the tables I used to have.

I have tried to copy the entire data directory and paste it into my new setup, but I am not sure if the versions of MySQL / phpMyAdmin are the same, how can I find the MySQL version from my old data directory? Possibly in one of those .frm / .ibd files?

When I do paste the entire data folder, some of the tables in the database are working like normal but some display the following error.

#1033 - Incorrect information in file: '.\db\a_table.frm'

Also it seems every table which shows this same error message is marked in use under Collation in the Structure view in phpMyAdmin

What can I do to fix this? What does it mean?

I am wondering if I re-create my old OS setup, it could work?
Is there a better way?

I managed to find a workaround for this but I am not very happy about it.
I installed my previuos setup which was EasyPHP and copied my old MySQL *data* dir into the EasyPHP *MySQL* data directory.

This time around the database was able to read the .ibd / .frm files correctly.(even though these files did not change at all)

I then used phpMyAdmin to export the database(this time around all tables/data was stored in the sql file)

I then disabled EasyPHP enviroment and started up my new enviroment and used phpMyAdmin->import with the new sql file which loaded the data correctly.

Overall I think its pretty poor that MySQL cant simply output some type of notice stating that the .ini enviroment variables are not set corresponding to the .ibd / .frm files rather than just output incorrect information and leave the user in the dark with all sorts of angles to think about. After googling around it seems many people seem to believe that their data is corrupt when that's really not the case.


mysql inner join producing more results than it should I am trying to get the roles name, verbs name, and privileges enabled value where privileges is a join table joining verbs and roles through a verb_id and roles_id foreign key.
 But I am having trouble with the join:

SELECT roles.name, verbs.name, privileges.enabled FROM verbs, roles
INNER JOIN privileges on privileges.role_id = privileges.verb_id

It gives me way more records than there are. There are duplicate records for example many 'update student' records

verbs table:
id
name

roles table:
id
name

privileges table:
id
role_id
verb_id
enabled

a verb has many roles through privileges
a role has many verbs through privileges

I would like to show the role names, verb names, and privilege enabled value where the privileges role_id field is associated with the verb_id field. So for example, if role id 1 is associated with verb id 18, then the privileges table would have role_id 1 and verb_id 18 to show the relationship between role and verb. So I would like to select that relationship to display the associating role name, verb name, and privilege enabled value.

Why are you using two syntaxes, old fashioned verbs, roles and then an explicit inner join? Makes the query very hard to read and since you don't have any where clause that defines the relationship between verbs and roles (this is one of the biggest problems with using old fashioned syntax), you essentially have the cartesian product of those first two tables, and this probably explains your row counts.

You need to define the join columns between verbs and roles and, preferably, put that into an ON clause with a proper INNER JOIN. E.g.:

SELECT r.name, v.name, p.enabled
FROM verbs AS v
INNER JOIN privileges AS p
  ON v.id = p.verb_id
INNER JOIN roles AS r
  ON p.role_id = r.id;