texts: Increase memory limit when server is stopped

texts:
There was a local SQL Server 2012 instance. Once memory has decreased to critical 128 MB and then has stopped by me.

I can not start it (idea: not enough memory).

Is it possible to increase this limit in config file or in system registry?


texts: MySQL Assuming unsigned result in subtractions, what's the motive?

texts:
I just found out that if I try to subtract an unsigned integer from another unsigned, MySQL automatically assumes the result will be unsigned, which in turn generates the error BIGINT UNSIGNED value is out of range whenever the actual result of the operation is negative.

I tried googling the reason for this design decision, but didn't find any explanation.

What are the motives for such a decision? does it make sense to assume an unsigned result when subtracting two unsigned integers?


texts: SQLAnywhere: How to configure length of message output to console/client when using isql

texts:
If got an issue with message command (as well as print) on SQLAnywhere.

When running message as a single command it appears that it's completely put everything where it should be.

But when using it inside a procedure for e.g. debugging output of dynamic SQL it appears, that it is truncated at about 300 characters.


texts:
I have two tables whereby both of them use sub queries. These two tables are combined by using UNION ALL.
I have two other tables that only uses a WHERE clause and both of these are combined using UNION ALL.
I am struggling to join all 4 tables as I keep getting below error when I use UNION ALL and UNION.

  Msg 205, Level 16, State 1, Line 1
  All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.


texts: MySQL 5.7 doesn't start after installation on CentOS 6.7

texts:
I have just installed MySQL 5.7 on a fresh CentOS 6.7(vm).

When I start the MySQL service with this command:

sudo service mysqld start

It doesn't start and raise this error:

  Initializing MySQL database: 2015-12-07T11:00:51.060114Z 0 [Warning]
  TIMESTAMP with implicit DEFAULT value is deprecated. Please use
  --explicit_defaults_for_timestamp server option (see documentation for more details). 2015-12-07T11:00:51.062305Z 0 [ERROR]
  --initialize specified but the data directory has files in it.
  Aborting. 2015-12-07T11:00:51.062347Z 0 [ERROR] Aborting [FAILED]

Do you know what is the problem?


texts: Efficient query to get last row group by multiple columns

texts:
I have a table like the following:

spread_id serial NOT NULL,
game_id integer NOT NULL,
sportsbook_id integer NOT NULL,
spread_type integer NOT NULL,
spread_duration integer NOT NULL,
home_line double precision,
home_odds integer,
away_line double precision,
away_odds integer,
update_time timestamp without time zone NOT NULL,
game_update_count integer NOT NULL

I'm trying to get the last row inserted (max update_time or game_update_count), for each group of sportsbook_id, spread_type, spread_duration, and game_id.

The following query gets me close, but I am not able to select the lines/odds without Postgres complaining.

SELECT
     spreads.game_id, sportsbook_id, spread_type, spread_duration,
     MAX(game_update_count) AS game_update_count
FROM spreads
LEFT JOIN schedule ON
        schedule.game_id = spreads.game_id

WHERE date >= '2012-01-01' AND date <= '2012-01-02'
GROUP BY
     spreads.game_id, sportsbook_id, spread_type, spread_duration
ORDER BY
    spread_duration, spread_type, sportsbook_id, spreads.game_id,
    game_update_count DESC;

Anyone have any thoughts on a better approach to take?


texts: how to change the driver of a ole_dbconnection from oracle_32bit to oracle_64bit

texts:
i have the connection string of:

connection= new OleDbConnection("Provider=OraOLEDB.Oracle;UserId=userid;Password=password;
Data Source=database;OLEDB.NET=true");

However after Oracle_64bit driver was installed on my computer in another process this doesn't work anymore. I read something about making a connection string like:

Driver={Oracle in OraClient12Home1_32bit};Dbq=database;Uid=userid;Pwd=password;"

I can't make that work however, and would like to hear if there's some simple fix for 32-bit to 64-bit of oledbconnection?


texts:
We are conducting a datawarehouse project, moving data from Staging database to Kimball Data Warehouse.

What is the purpose of creating a custom stored procedure Error Log Table?

I am seeing a lot of websites recommend this.
SQL server already has an Error log table in SSMS (Option a).

So why create another error log table, seems like additional coding? (Option b). I can even customize error messages using both options, SELECT @ErrMsg = 'testabcd'. Can someone explain? Thank you,

Note:
Is there any automatic functionality in SSMS which logs errors for dbas? Is the only way to capture these errors is custom table? We are running code through sql agent jobs, and SSIS packages, thought we saw errors in agent job history and SSIS project catalog? issues like not null insert, value type conversion issues.

(a) SSMS Error Log Table

(b) Table and Stored Procedure
https://www.tech-recipes.com/rx/53462/sql-server-error-logging-and-reporting-within-a-stored-procedure/

CREATE TABLE [dbo].[ErrorLogTable]
(
    [ErrorID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [ErrorNumber] [nvarchar](50) NOT NULL,
    [ErrorDescription] [nvarchar](4000) NULL,
    [ErrorProcedure] [nvarchar](100) NULL,
    [ErrorState] [int] NULL,
    [ErrorSeverity] [int] NULL,
    [ErrorLine] [int] NULL,
    [ErrorTime] [datetime] NULL
);

CREATE PROCEDURE [dbo].[ErrorLogInsert]
AS

     INSERT INTO [ErrorLogTable]
         (
         ErrorNumber
        ,ErrorDescription
        ,ErrorProcedure
        ,ErrorState
        ,ErrorSeverity
        ,ErrorLine
        ,ErrorTime
       )
       VALUES
       (
         ERROR_NUMBER()
        ,ERROR_MESSAGE()
        ,ERROR_PROCEDURE()
        ,ERROR_STATE()
        ,ERROR_SEVERITY()
        ,ERROR_LINE()
        ,GETDATE()
       );


texts:
Explain plan in Postgres has width, which (per docs) is:

  Estimated average width of rows output by this plan node (in bytes).

My question: when will width be 0? Because in examples from docs, it sometimes is, like below:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=5.07..229.20 rows=101 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)
         Index Cond: (unique1 < 100)


texts: Why wouldn't I put the single preferred option into its own table, instead of having a 'Preferred' column?

texts:
In similar spirit to How to implement a 'default' flag that can only be set on a single row I would like to specify that, for any given list of possible names for a business, only one of the names is considered 'canonical'.

Two appealing options for representing this would include:

a single table, with a Preferred column (a boolean flag) and a 'filtered unique index' on BusinessId, Preferred(when true) to ensure that for any given business, only one of the names could be preferred, but all others could be unpreferred

BusinessNames

BusinessID | Name          | Preferred
--------------------------------------
1          | Sony Pictures | 1
1          | Oh no! Inc    | 0
1          | Facepalm Co   | 0

; OR
the plainer version of that same table (BusinessID, Name) and a peer table for the 'preferred names' (also BusinessID, Name, but unique only on BusinessID)

BusinessNames                       PreferredBusinessNames

BusinessID | Name                   BusinessID | Name
--------------------------          --------------------------
1          | Sony Pictures          1          | Sony Pictures
1          | Oh no! Inc
1          | Facepalm Co

I lean towards option 2, which seems cleaner to me.  Never mind that BusinessNames could reference a surrogate primary key in a Names table for possible space savings.  (I am going on an anti-surrogate-key binge right now to see where I end up).

I am aware of some implications for my app, such as:

To insert/update/delete a name, I have to touch 2 tables.
To do a query where I want to return only a single row per business and 'the name' of that business, I would simply involve PreferredBusinessNames, and leave BusinessNames alone.

I think I can live with those implications, but am I shooting myself in the foot by this sensible extreme normalization'?  Am I creating a monster, or will I be pleasantly surprised?  I've never seen anyone else do it that way, and surely there is a reason?


texts: Convert MySQL database from latin1 to utf8mb4 - and take care of German umlauts

texts:
I am using 5.6.28 MySQL Community Server to host WordPress + ProPhoto blog in German language at CentOS 6.7 Linux server:

mysql> show tables;
+-----------------------+
| Tables_in_blog        |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
11 rows in set (0.00 sec)

Then recently I have started developing a Russian language app and had to change MySQL settings to utf8mb4 encoding in /etc/my.cnf:

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

Now I have a minor problem with the first database that it is still shown as latin1:

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | latin1                     | HOW TO CONVERT?
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

What is the best way to convert a latin1 database to utf8mb4 please?

And how to ensure that the German umlaut characters ( ä ö ü ) are converted properly in the process?


texts: Windows OS level scheduled disk defragment tasks and SQL data volumes

texts:
Is it considered safe to run Windows OS level scheduled tasks for disk defragment against SQL volumes?
I've inherited the management of a SQL Server from a previous DBA, and they had a Windows Scheduled Task setup to defragment the SQL Server data volumes.  I'm wondering if I can safely disable those jobs.


texts: SQL Server 2012: Login failed for user. Reason: Failed to open the database configured in the login object

texts:
Every so often, my SQL Server 2012 throws the following errors:
Error (likely related to the one below):

Source: spid99
The client was unable to reuse a session with SPID 99, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Error: 18056, Severity: 20, State: 46.

Immediately before the error above (same timestamp):

Source: Logon
Login failed for user 'myUser'. Reason: Failed to open the database 'myDatabase' configured in the login object while revalidating the login on the connection.
Error: 18456, Severity: 14, State: 46.

My ASP.NET 4.5 website (consuming the DB) throws this error at the same time:

System.InvalidOperationException: Internal connection fatal error.

What may be causing these issues and how can I fix them?


texts: How do we create spatial index on views if it is possible?

texts: Is it possible to create spatial index on sql server 2008? If yes how do we create it on table with schemabinding?


texts:
I have a PostgreSQL 9.1 database that contains 10M+ rows and some text fields that need similarity and %word%-like searches, so I decided to use trigram indexes.

Initially, I started to use GIN indexes, but now I am wondering whether I should rather use GIST.

The theory says:

GIN index lookups are about three times faster than GiST
GIN indexes take about three times longer to build than GiST
GIN indexes are moderately slower to update than GiST indexes, but about 10 times slower if fast-update support was disabled (see Section 58.4.1 for details)
GIN indexes are two-to-three times larger than GiST indexes

I have tried to create both GIN and GIST indexes and found the following in practice (on my dataset):

For LIKE '%word%' queries, GIN is about the same speed (or even 5-20% slower) as GIST for the first time (when the index for the trigrams in the query are not cached yet).
For LIKE '%word%' queries, GIN is about 5 times faster than GIST if the trigrams in the query were searched recently. GIST has always the same speed, regardless of cachedness.
For % 'word' (similarity) queries, GIN is about 5-8 times slower than GIST, depending on the cachedness of the index.
GIN is about 10% smaller than GIST. However, it seems to grow faster if there are UPDATE statements to the table. Unless, of course, I VACUUM FULL often enough.

So I see some discrepancies between theory and practice:

speed: GIST is often faster, except GIN is totally in memory (Can you ensure that in advance?), and even then only for LIKE '%word%' queries.
size: GIN is actually smaller. Is this typical for trigram indexes or is my case an outlier?
May the behaviour be significantly different in 9.4?
A last question: is it wise to use GIN if there are about 10-50k UPDATE statements and 5-10k INSERT statements daily, and weekly VACUUMis probably reasonable (not much usage on the weekend), or will it really hurt UPDATE and INSERT performance?


texts: Maximum or suggested number of bound query-parameters in InnoDB (MySQL)?

texts:
We have code that adds thousands of parameters to a query as members of an IN (...) criterion. My aesthetics say to bucket it into multiple queries, but I want an objective reason.

Does InnoDB have a maximum number of parameters per query? Does it hurt performance to have over a certain number?


texts: What are we Doing when we Connect to SQL Server?

texts:
My apologies if this is simple, or off-topic for this site:
  I just wanted to know what goes on in the background when one connects to
SQL Server, say, within Management Studio. Just what is it we are connecting to,
is it a virtual machine, or some server somewhere?
Thanks for any explanations, refs. etc.


texts: How can I create a table to lookup values for combinations of varying sized sets of numbers?

texts:
I have a table of products, each with it's own id (int) as primary key:

product_id  name
3           product1
4           product2
6           product3
10          product4

I have a table of packaging types, each with it's own id (int) as primary key that these products may be packaged/shipped in:

packaging_id   name
15             Box
20             Bag
21             Padded Mailer
26             Tube

I need a table(s) to lookup which packaging to use for a given combination of products.  For example, product_ids 3 and 6 will fit together in a bag (package_id 20).  Likewise, product_ids 3, 4, and 10 may require a Box (packaging_id 15).  The combination may be any number of product_ids.  A pseudo query would look like this:

SELECT packaging_id FROM lookuptable WHERE product_id = 3 AND product_id = 6

In other words, I must be able to query a specific combination of n product_ids and retrieve a packaging_id. Something like this would work if the number of product_ids in the WHERE clause was fixed, e.g. only two products could ever fit in a package:

product_id1   product_id2  packaging id
3             6            20

However, there may be n product_ids to query, so this won't work.

Another solution may be to create some type of hash for the primary key, like this:

product_combination_id   packaging_id
00030006                 20

But I feel like there is a more robust solution.  Ideas?

Thanks,
Casey


texts: return field name and number of rows which meet condition in one sentence

texts:
I have a table in postgres with 10 fields, and need to check for certain values in 3 of them, then return the field name which met the condition and the number of rows in each field.
So it's something like:
select count(*)
from table
where field1 ilike '%E+%'
OR field2 ilike '%E+%'
OR field3 ilike '%E+%'

and then return the field which met the condition AND the number of rows (which I'm not sure how to do it)


texts: Can I restore the dump of a PostgreSQL 9.3 database into a PostgreSQL 12.5 or Postgres-XL 10 instance?

texts:
I've got a fairly old TAR dump of a PostgreSQL 9.3 database I'd like to re-awake. After several system upgrades, I've now got version 12.5 of the PostgreSQL server. In the medium future, I'd like to migrate to a database cluster based on Postgres-XL 10.
How here's my question: Is it possible to restore the dump safely? And: Are there any pitfalls I have to look out for?


texts: Optimizing table/indices for getting the latest row (filtering by one additional column)

texts:
I have a SQL Server table (SQL Server 2012 SP3 Standard edition) that stores a bunch of configuration information (basically text blobs) for different organizations. The schema is something like this:

[ConfigurationID]       INT IDENTITY (1,1) NOT NULL,
[OrganizationID]        INT NOT NULL,
[TimestampUtc]          DATETIME NOT NULL,
[ConfigurationData]     NVARCHAR (MAX) NOT NULL,
[ChangedBy]             NVARCHAR (256) NOT NULL,
[Comment]               NVARCHAR (MAX) NOT NULL,
[ChangeType]            INT NOT NULL

The TimestampUtc will always be increasing (I won't ever be INSERTing "back-dated" entries into the table), and the rows won't ever be UPDATEd (I'm only INSERTing new rows). For some OrganizationIDs there will be lots of rows, for some very few, and a new row for any OrganizationID may be INSERTed at any time.

If needed, I can guarantee uniqueness of TimestampUtc (but it would be great to have a solution that didn't need that).

INSERTs are relatively rare (at most dozens of times per day, but typically much less than that), reads are very frequent (essentially on every web request to my application).

My goals are:

Getting the ConfigurationData with the latest TimestampUtc for a given OrganizationID should be extremely fast regardless of the size of the table
INSERT performance doesn't matter too much, but I'd like to avoid horrible index fragmentation if at all possible (so my first idea of a unique clustered index on OrganizationID ASC, TimestampUtc DESC is probably not a great idea).

Questions

I know I can denormalize and just store the latest ConfigurationData in one table and the historical log of previous values in another table, but is it possible to meet my goals with just one table? What's the best way to do it? (I.e. what's the best index structure? do I need to change anything about the table schema, etc.?)


texts: Select data where column value exists in seperate rows for same reference id


texts: How do I drop the TIME portion from DATEADD?

texts:
I am trying to create some test data where each Employee has a random birthdate. I accomplished the randomizing part but it did not return result in the format that I want.. NOTE: the DateOfBirth column uses the VARCHAR data type.

/****** Generate Random Date For Each Row (Between 1900 - 2015) ******/
UPDATE CCCINC.dbo.Employees
SET DateOfBirth = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 41975), '1900-01-01');

Result:

Feb 28 1930 12:00AM

How do I remove the TIME portion from the result? Also, if I wanted to change the DATE format to YYYYMMDD how would I accomplish that?


texts: Why does query parameter type affects mysql index choice?

texts:
I have a query like this:

select a, b, c from sometable where cateId = ? /**1**/

We can also do this:

select a, b, c from sometable where cateId = "1"

as MySQL will cast it. Interestingly I observed different performance characteristics on these two queries. Why this is the case?

I omitted quite some details regarding table structure and index details, but in general is it possible for such scenario to really occur, and why?

adding the details I mentioned. if it helps.

    select
        memberId,
        prodId,
        prodName,
        sum(count) as total
    from
        SOMETABLE
    where
        statDate >= ?
        and statDate<= ?
        and catId = ?
        and name <> 'OTHERS'
    group by
        memberId, prodName
    order by
        total desc
    limit 5

    --IDX catId, prodName
    --IDX catId, statDate
    --IDX memberId, statDate
    --IDX catId, statDate, prodName, prodId, count


texts: Keeping sales and purchase data in the same table?

texts:
If my purchase and sales tables retain similar information, is there any problem with moving them to the same table and using an INT column to denote whether a row represents a purchase or a sale?

If there's additional specific information for either, could I retain it in, say, a link table?


texts: Issue with forming correct relationship between subtype and another table in ERD

texts:
The focus is on the Cashier table and the Cash Accountability table. The rule is that only Cashiers have Cash Accountability and a Cashier ideally only cashes out once per day. Before I changed the ER diagram to what it is now, the CashID was a foreign key in the Cashier table, but there was nothing in the Cash Accountability table to identify which cashier the data belonged to. So as it is now, the relationship states that a cashier cashes out many times in a day and I want to express that a cashier only cashes out once per day.

I am unsure of how to get the relationship between the two tables correct and I would appreciate some guidance in the right direction.

Screenshot of the ERD


texts: System Health Extented Event - Security_Ring_Buffer_Error_Recorded

texts:
I've recently upgraded a Production SQL instance from 2005 SP4 to 2012 SP2 and have noticed a large amount of the following entries in the system_health extended event:-

I've been looking into this and have found the following related articles/questions:-

SQL Server 2012: Security_error_ring_buffer_recorded: ImpersonateSecurityContext

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/65e792ff-8ef5-4ff7-b8b9-e71b262d8bb7/sql-2012-sp1-systemhealth-securityerrorringbufferrecorded-events?forum=sqldatabaseengine

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/troubleshooting-specific-login-failed-error-messages.aspx

However, these point to the issue being related to failed logins. I've checked the SQL error log and no failed logins are being recorded.

I have been investigating this on a development instance and have noticed that the entries are generated whenever a SQL authenticated login connects to the SQL instance.

Has anyone come across this before? I'd like to get to the bottom of why these entries are being recorded.

If anyone wants to see this happening on their own (development) instance, log into SQL using a SQL authenticated login and then run:-

SELECT
    CONVERT (VARCHAR(30), GETDATE(), 121) as runtime,
    DATEADD (ms, (a.[Record Time] - sys.ms_ticks), GETDATE()) as [Notification_Time],
    a.* , sys.ms_ticks AS [Current Time]
FROM
    (SELECT
       x.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [ErrorCode],
       x.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [CallingAPIName],
       x.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [APIName],
       x.value('(//Record/Error/SPID)[1]', 'int') AS [SPID],
       x.value('(//Record/@id)[1]', 'bigint') AS [Record Id],
       x.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
       x.value('(//Record/@time)[1]', 'bigint') AS [Record Time]
    FROM (SELECT CAST (record as xml)
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR') AS R(x)) a
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY a.[Record Time] DESC

You should see entries that correspond with the time that you logged in.

It would be good to know if someone else can replicate what I'm seeing.

Thanks,

Andrew


texts:
My table has the following data:

2016-01-01 : 1
2016-01-02 : 0
2016-01-03 : 4
2016-01-04 : 1
2016-01-05 : 2

I want to generate a report (linear growth) like this:

date       : linear growth
2016-01-01 : 1
2016-01-02 : 1
2016-01-03 : 5
2016-01-04 : 6
2016-01-05 : 8

Are there any good ideas for a SQL statement to do this?


texts: EXCEPT & INTERSECT: Purpose of Passive Projection in Logical Plan

texts:
The following EXCEPT query produces a logical plan with a seemingly purposeless projection. This also occurs for INTERSECT.

What is the purpose of the projection? e.g. Is there a different EXCEPT query where the outer projection would specify something?

Query:

use AdventureWorks2017

select p.ProductId
from Production.Product as p
except
select pinv.ProductID
from Production.ProductInventory as pinv
option (recompile, querytraceon 8605, querytraceon 3604)

Converted Tree:

    LogOp_Select

        LogOp_GbAgg OUT(QCOL: [p].ProductID,) BY(QCOL: [p].ProductID,)

            LogOp_Project -- << ?? PASSIVE PROJECTION ??

                LogOp_Project

                    LogOp_Get TBL: Production.Product(alias TBL: p) Production.Product TableID=482100758 TableReferenceID=0 IsRow: COL: IsBaseRow1000

                    AncOp_PrjList

                AncOp_PrjList

            AncOp_PrjList

        ScaOp_Exists

            LogOp_Select

                LogOp_Project

                    LogOp_Get TBL: Production.ProductInventory(alias TBL: pinv) Production.ProductInventory TableID=914102297 TableReferenceID=0 IsRow: COL: IsBaseRow1001

                    AncOp_PrjList

                ScaOp_Comp x_cmpEq

                    ScaOp_Identifier QCOL: [p].ProductID

                    ScaOp_Identifier QCOL: [pinv].ProductID


texts: Query Optimization required - Subquery using Inner Join

texts:
Could anybody advise how can I optimize this query below please?

SELECT

IT.[iInternalTransactionID]
, IT.[iInternalAccountID]
, C.[iContractID]
, IT.[dtTransactionDate] AS TransactionDate
, (CL.[sFirstName] + ' ' + CL.[sLastName]) AS ClientName

...

-- ### I would like to optimize this by using inner join ##--
, (SELECT MAX(CPH.[iTermNo])
    FROM [tbl_ContractPaymentHistory] CPH
    WHERE CPH.[iContractID] = C.[iContractID]
    AND CPH.[iInternalAccountID] = @InternalAccountID
    AND CPH.[dtTransactionDate] = IT.[dtTransactionDate])  AS TermNo

, (SELECT (MAX(CPH.[iTermNo])  * MAX(CPH.[cMonthlyAmount]))
    FROM [tbl_ContractPaymentHistory] CPH
    WHERE CPH.[iContractID] = C.[iContractID]
    AND CPH.[iInternalAccountID] = @InternalAccountID
    AND CPH.[dtTransactionDate] = IT.[dtTransactionDate])  AS TotalAmount
-- ### end ### --
...

FROM
    [tbl_InternalTransactions] IT
    INNER JOIN [tbl_Contract] C ON C.[iContractID] = IT.[iContractID]
    INNER JOIN [tbl_Client] CL ON CL.[iClientID] = C.[iClientID]
    INNER JOIN [tbl_FinanceStructure] FS ON FS.[iFinanceStructureID] = C.[iFinanceStructureID]
    INNER JOIN [tbl_InternalAccount] IA ON IT.[iInternalAccountID] = IA.[iInternalAccountID]
    AND IA.[iInternalAccountID] = @InternalAccountID
WHERE
    IT.[dtTransactionDate] >= @FromDate
    AND IT.[dtTransactionDate] <= @ToDate

...

The query plan can be found here: http://pastebin.com/KHPdEzgi

I have tried to replace the subqueries with the following:

INNER JOIN (
SELECT
    MAX([iContractID]) AS [iContractID]
    , MAX([iTermNo]) AS [iTermNo]
    , MAX([cMonthlyAmount]) AS [cMonthlyAmount]
FROM [tbl_ContractPaymentHistory]
WHERE [iInternalAccountID] = @InternalAccountID
    AND [dtTransactionDate] = IT.[dtTransactionDate] -- not able to use this here
    AND [iContractID] = C.[iContractID]          -- not able to use this here
        ) CPH ON CPH.[iContractID] = C.[iContractID]

So I can select [iTermNo] and  [cMonthlyAmount]

However, I am not able to use columns from another tables in the inner join subquery. I am getting the following errors:
The multi-part identifier "IT.dtTransactionDate" could not be bound.
The multi-part identifier "C.[iContractID]" could not be bound.

Help would be much appreciated as the query takes an eternity to load at the moment. Thank you.


texts: How to get the nTH row in ordered result set?


texts: JOIN 2 tables, and query data from them 3 times

texts:
Basically I have 2 tables: post_meta and posts.

post_meta has a post_id, key and value:

post_id  key      value
6343     make     Chevy
6343     model    Volt
6794     make     Chevy
6794     model    Silverado

posts has a post_id and content:

post_id  content
5434     Hello World!
6343     Chevy Volt review
6794     The New Silverado

What I need to do is pull the model based on the make (pull all unique models with the make "Chevy").  The database isn't very big so it doesn't need to be a real fast query.  Basically the plan in my mind was to get the model of every post_id that has the post_meta.make "Chevy".

SELECT post_id FROM postmeta
WHERE key = 'make'
AND value = 'Chevy'

then take the post_id

SELECT DISTINCT value FROM postmeta
WHERE post_id = {an array of post_id's}

something to that extent.


texts: How to reference Foreign Key(s) to the parent table where data goes on increasing?

texts:
Hello i am very new to learning PHP and MySQL. I have just begun learning MySQL. As the question mentioned above, will be kind if anyone can give a solution. Please see the example of a simple restaurant order below.

Table 1 (parent table)

id | username | city
1  | bob      | berlin
2  | sam      | stuttgart

Table 2 (child table where 'menu_id' is a foreign key)

id | menu_name | menu_id | order_date
1  | pizza     |   2     | 2016-05-04
2  | pasta     |   2     | 2016-05-04
3  | cookies   |   1     | 2016-05-03
4  | sandwich  |   2     | 2016-05-06
5  | cake      |   1     | 2016-05-02

Now by a simple 'LEFT JOIN' with 'ORDER BY order_date ASC' i can see in which dates which user ordered what.

But here is my question:

I have assigned the Foreign Keys manually, but suppose in the parent table more users are coming in then do i have to add Foreign Keys manually too? That will take a long time. Is there any workaround? And if there isn't then why use the Foreign Keys at all?


texts:
I have a website that is deployed on an environment as follows:

a load balance.
two web server.
two database servers.

both of the two database server have the same structure and queries.
I found out that each one of them is storing different data than the other one and not connected.

i do not know much about this. i just need that any update that occurs on any servers be reflected in the other server.

EDIT:
I stopped one of the servers and work on only one of them until we try the replication as it seems to be a wrong configuration.


texts: mysql hardware refresh + version upgrade

texts: currently I am running MySQL 5.0.80 on windows server 2003 (x64), 64gb memory, I have a master and 2 slaves.  It's a large 400 gb Database.  I am setting up all new hardware (SAN + dedicated MySQL servers with 256 gb memory running windows server 2008 r2.  I need to come up with a good plan for moving everything over while minimizing downtime.  Is it absolute truth that you cannot go from 5.0 to 5.6 even when using mysqldump to move the data?  I was originally hoping to run mysqldump during off hours from master (don't fully trust the slaves data integrity), then make master available to users again while I source the sql files into a new 5.6 server and use it as a slave for a day or so until I knew all was well, then promote to master.  Not sure that is feasible because of the jump in versions as well as the replicating (temporarily) from 5.0 to 5.6.  Does anyone have suggestions as to a work plan for moving the master and two slaves onto new hardware with a version upgrade?  Every time i dump the data it will take 5+ hours and sourcing it in even longer so I am trying to be smart about this since my organization is so sensitive to downtime.  thank you.


texts: SQL 2008 Merge Replication and table-level data compression

texts: What would happen if you enable data compression (either row or page-level) on a replicated table (merge replication) where the publisher is SQL 2008 Enterprise (Supports data compression) and the subscriber was SQL 2008 Standard (does not support data compression). Would the compression attribute get politely dropped  and the subscriber continues happily not-compressed or would it cause replication to hit an error and stop?


texts: PostgreSQL/PostGIS 9.6 broke my compound index

texts:
In PostgreSQL 9.2 I had no problem creating the an index that had both a geography (postGIS) type and and integer as a compound index. But now (9.6) it complains on creation of the index and I don't understand the hint it is providing:

The columns and data are all created properly, Postgres is complaining on the create index.

ERROR: data type integer has no default operator class for access method "gist"
HINT: You must specify an operator class for the index
      or define a default operator class for the data type.
********** Error**********
ERROR: data type integer has no default operator class for access method "gist"
SQL state: 42704
Hint: You must specify an operator class for the index
      or define a default operator class for the data type.

The schema definition is as follows:

- Table: portal.inventory

-- DROP TABLE portal.inventory;

CREATE TABLE portal.inventory
(
  type character varying,
  pid integer,
  size bigint,
  date timestamp without time zone,
  path character varying,
  outline geography(Polygon,4326)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE portal.inventory
  OWNER TO postgres;

-- Index: portal.inventory_compound_idx

-- DROP INDEX portal.inventory_compound_idx;

CREATE INDEX inventory_compound_idx
  ON portal.inventory
  USING gist
  (outline, pid);

-- Index: portal.inventory_icompound_idx

-- DROP INDEX portal.inventory_icompound_idx;

CREATE INDEX inventory_icompound_idx
  ON portal.inventory
  USING gist
  (pid, outline);


texts: Postgres 11 - *prioritized* where clause in group

texts:
table below is given:

create table cafes (
  id uuid not null
    constraint cafes_pkey primary key,
  identity uuid not null,

  is_closed boolean,
  is_illegal boolean
)

The problem is to select one cafe from each group (grouped by identity), which:

priority #1: has is_closed = true
priority #2: has is_illegal = true

That is, if we have a closed cafe, we select it. If there are no such cafes in group, we search by is_illegal = true.

Any ideas how to solve this problem? :)


texts: Count instances of value in multiple tables

texts:
I'm attempting to write a query that returns the count of a given value across multiple tables.

Right now, I'm using the following query

SELECT COUNT(*) AS SL,
(
  SELECT COUNT(*)
  FROM [Sales Line Archive]
  WHERE [Document No_] = 123
) AS SLA
FROM [Sales Line]
WHERE [Document No_] = 123

Which returns a single result with the count of lines in Sales Line in the first column and the count from Sales Line Archive in the second.

However, I'd like to request multiple records using an IN clause, so that I could get results like the following

Document No_, SL, SLA
1, 3, 0
2, 0, 4
3, 2, 1


texts: How can I disable swappiness just for Mariadb and not for other applications on the same EC2 Instance?

texts:
I have an EC2 instance where DB(Mariadb) runs on the same EC2 instance as the other applications.
MySQL documentation suggests to disable swappiness since DB doesn't like swap. How can I disable swappiness just for Mariadb and not for all other users/apps ?
Thanks
Moe


texts: MySQL INSERT INTO SELECT vs BULK INSERTs performance

texts:
Currently, I am trying to copy data from TABLE1 to TABLE2.
In terms of insertions performance, would it be the same or faster if I would to do

BULK INSERT manually (i.e BULK insert every 10K records into TABLE2 via INSERT INTO TABLE2 VALUES (1,2), (5,5), ...), versus
INSERT INTO TABLE2 SELECT * FROM TABLE1


texts: Changing feature on a database that is a member of SQL Server AlwaysOn availability group

texts:
Our developers need a change on a database that is in the Availability group (they need Service broker in one of the databases to be enabled) but when I try to do it I am getting error saying that I can’t make the change since this database is a member of an Availability group….

Is there a way to change it without taking this database out of availability group or I have to take it out (and how to do it if I have to and not to have to re-install the database again on the secondary node).

I see that source database we copied from on our current Live server has this Service broker enabled but after restoring it's backup on Azure it picked up the False state for Enabled Service Broker feature).


texts:
I have table (2139868 rows):

COLUMN1 NUMBER(20,0)
COLUMN2 XMLTYPE
COLUMN3 XMLTYPE
COLUMN4 VARCHAR2(50 BYTE)
COLUMN5 VARCHAR2(50 BYTE)
COLUMN6 VARCHAR2(50 BYTE)
COLUMN7 VARCHAR2(50 BYTE)
COLUMN8 VARCHAR2(50 BYTE)
COLUMN9 VARCHAR2(500 BYTE)
COLUMN10 VARCHAR2(100 BYTE)
COLUMN11 VARCHAR2(50 BYTE)
COLUMN12 VARCHAR2(20 BYTE)
COLUMN13 VARCHAR2(20 BYTE)
COLUMN14 TIMESTAMP(6)
COLUMN15 VARCHAR2(50 BYTE)
COLUMN16 PROPERTIES_ARRAY_TYPE
COLUMN17 TIMESTAMP(6)
COLUMN18 TIMESTAMP(6)

In a database

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

I'm trying to select all the distinct values of COLUMN4; unfortunately this takes roughly 18 seconds despite the COLUMN4 being indexed.  There are only 5 unique items.

How can I speed up my query?

(Work doesn't let me upload images so I'll type out the execution plan, use your imagination)

Operation             OBJECT_NAME CARDINALITY   COST
---------------------- ----------- ----------- ------
SELECT STATEMENT       NIL                    4 65463
+ HASH(UNIQUE)         NIL                    4 65463
++ TABLE ACCESS (FULL) TABLE1           2010857 65334


texts: I have a table with millions of rows. This table has a column which we frequently filter(means we use it in WHERE clause frequently), the values are Insert/Update/Ignored. Is it make sense to add index to columns? Someone told me adding index on few values column have no benefit


texts: How to take backup of schema postgresql?

texts: Hi I am currently using postgresql and deleting some of the tables for testing purpose and I want to take backup of schema. can any one let me know how to do that ?


texts: MSmerge_expand_sp triggers conflict in SQL Server

texts:
I am adding new data using SQL Server merge replication over web and getting this type of error.

  Could not find stored procedure 'dbo.MSmerge_expand_sp_A749C9F4793740CEA94A470F27259788'.

Can someone give any hints to resolve this kind of issue ? It wasn't an issue before the server was running smoothly, I am not sure what might have made this kind of change.


texts: Curious Regarding filtering behaviour and performance of where condition

texts:
So i have completed the clean up of SQL code that involves four set of queries that partially do the same filtering in where condition , while the rest of the part do their own task specific to each query. I isolated this common part of the where condition and stored it into a Temporary Table and then made the Four queries connect to this temporary table and filter out their specific needs hoping that it would reduce performance.As per my hope it did. But the query seems to run 2 secs faster when i also include the common filtering condition again when querying from the temporary table.

Let me show an example:

Assume

--Query 1
select * from Table where
common conditions and
Query1 specific conditions

--Query 2
select * from Table where
common conditions and
Query 2 specific conditions

I Implemented this as

insert into #TempTable select * from table where common conditions

--Query 1
select * from #TempTable where
Query1 specific conditions

--Query 2
select * from #TempTable where
Query1 specific conditions

But due to some error i ran it as

--Query 1
select * from #TempTable where
common conditions and
Query1 specific conditions

and the query ran 2 seconds faster then the original query whose run time for 7 seconds. I am just curious why is it so? Also FYI the queries return about 7000 results and also the where condition is not just one but many.


texts: How to select columns in SQL server 2008 using WHERE and IN, having 2 columns in the where clause

texts:
How to select columns in SQL Server 2008 using WHERE and IN, having 2 columns in the where clause.
Here is what I have, however I am  getting some error message.

select * from tbl
where (col1, col2) in (VALUES ('x1','y1'),('x2','y2'),....);

  Msg 4145, Level 15, State 1, Line 2
  An expression of non-boolean type specified in a context where a condition is expected, near ','.

Please advise.


texts: MSSQL Select all parent rows for which exists a combination of children rows with desired value

texts:
I would like to select all parent rows for which exists a combination of children rows with desired value. The easiest approach is to use multiple exists clauses, but this is not optimal - anyone knows better solution? Here is a SQL fiddle: http://sqlfiddle.com/#!6/8da76/5

Schema:

CREATE TABLE [dbo].[Parents](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [GroupId] [uniqueidentifier] NOT NULL
);

INSERT INTO [Parents] ([GroupId]) VALUES ('00000000-0000-0000-0000-000000000001');
INSERT INTO [Parents] ([GroupId]) VALUES ('00000000-0000-0000-0000-000000000002');
INSERT INTO [Parents] ([GroupId]) VALUES ('00000000-0000-0000-0000-000000000003');
INSERT INTO [Parents] ([GroupId]) VALUES ('00000000-0000-0000-0000-000000000004');
INSERT INTO [Parents] ([GroupId]) VALUES ('00000000-0000-0000-0000-000000000005');

CREATE TABLE [dbo].[Childrens](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [ParentId] [bigint] NOT NULL,
    [TypeId] [int] NOT NULL
);

INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (1, 1);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (1, 2);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (1, 3);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (1, 4);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (1, 5);

INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (2, 1);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (2, 4);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (2, 5);

INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (3, 4);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (3, 5);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (3, 6);

INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (4, 2);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (4, 6);

INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (5, 1);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (5, 2);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (5, 3);

Sample query to select all parent rows for which exists a combination of children rows with TypeId = 1 and 2 (query should return parent with Id = 1 and 5):

SELECT *
  FROM Parents p
  WHERE EXISTS(
        SELECT *
          FROM Childrens c
          WHERE p.Id = c.ParentId
            AND c.TypeId = 1 )
    AND EXISTS(
        SELECT *
          FROM Childrens c
          WHERE p.Id = c.ParentId
            AND c.TypeId = 2 );

Sample query to select all parent rows for which exists children row with TypeId = 4, 5 and 6 (should return parent with Id = 3)

SELECT *
  FROM Parents p
  WHERE EXISTS(
        SELECT *
          FROM Childrens c
          WHERE p.Id = c.ParentId
            AND c.TypeId = 4 )
    AND EXISTS(
        SELECT *
          FROM Childrens c
          WHERE p.Id = c.ParentId
            AND c.TypeId = 5 )
    AND EXISTS(
        SELECT *
          FROM Childrens c
          WHERE p.Id = c.ParentId
            AND c.TypeId = 6 );

Number of conditions for children TypeId is variable.

Best Regards!


texts: MySQL query to display inserts/updates/deletes/reads per second

texts:
Within the following query we can see inserts/updates/deletes/reads per second under "ROW OPERATIONS":

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2018-04-03 17:46:05 7f85eadf8700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 5 seconds
-----------------
BACKGROUND THREAD
-----------------
...
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 22128, id 140215363086080, state: sleeping
Number of rows inserted 436126087, updated 135043544, deleted 45, read 72105720260
51.19 inserts/s, 10.00 updates/s, 0.00 deletes/s, 4545.09 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

Is it possible to perform a query that can easily grab the following data so that it can displayed within an html table some how?

51.19 inserts/s, 10.00 updates/s, 0.00 deletes/s, 4545.09 reads/s

For example to get "reads per second" I've tried querying information_schema.global_status using com_select/uptime but that gives me an all time average. I was hoping to get a per second average if possible..

Maybe I need to store results every so often and compare to those? Not sure..


texts: Count total unique rows for each date and group by date


texts: Why mongo is choosing the wrong index / execution plan?

texts:
In production mongod logfile, we can see a lot of slow queries. Most of them are not using the best index and consequently the best execution plan is not used. However, when I run the same queries by myself in mongo shell, the correct index is used. So why for same query, we don't have same execution plan ?

Mongodb version : 4.0.2 (standalone)

Excerpt of mongod logfile :

2018-12-28T13:55:28.282+0100 I COMMAND [conn1032115] command mydb.products command: find { find: "products", filter: { origins_tags: "italia" }, sort: { last_modified_t: -1 }, skip: 320, limit: 20, singleBatch: false, maxTimeMS: 0, tailable: false, noCursorTimeout: false, awaitData: false, allowPartialResults: false, $readPreference: { mode: "secondaryPreferred" }, $db: "mydb" } planSummary: IXSCAN { last_modified_t: -1 } keysExamined:721542 docsExamined:721542 cursorExhausted:1 numYields:5637 nreturned:3 reslen:23886 locks:{ Global: { acquireCount: { r: 5638 } }, Database: { acquireCount: { r: 5638 } }, Collection: { acquireCount: { r: 5638 } } } protocol:op_query 5166ms

We can see following information :

planSummary: IXSCAN { last_modified_t: -1 }
keysExamined:721542 docsExamined:721542 (basically, the whole collection is examined)
nreturned:3
duration: 5166ms

However, these indexes are existing in collection :

db.products.createIndex({"origins_tags": 1,"sortkey": -1}, { background: true })
db.products.createIndex({"last_modified_t": -1}, { background: true })

Here is the optimized execution plan (the expected one):

planSummary: IXSCAN {"origins_tags": 1,"sortkey": -1}
keysExamined:331 docsExamined: 331
nreturned:3
duration: 11ms

So we can see a huge difference !


texts: Should a multi tenant system with SQL Server 2016, Shard or have Tenant isolation via separate database per tenant?

texts:
Given the use case:

Tenant data should not cross talk, one tenant does not need another
tenant's data.
Each tenant could potentially have large historical data volume.
SQL Server is hosted in AWS EC2 instance.
Each tenant is geographically distant.
There is an intention to use third party visualization tools such as PowerBI Embedded
The data volume is expected to grow over time
The cost of the system is constrained.
The solution must be maintainable without a 24/7 production DBA
The solution should be able to scale horizontally.
Total number of tenants is less than 50

What would be a recommended architecture, are there any reference implementations for this use case? I believe many people might have already faced this problem for enterprise software development.

I think this is a different situation from Handling growing number of Tenants in Multi-tenant Database Architecture. The use case mentioned in that question deals with a higher number of tenants, which is very different from having very few (50) large tenants. The architecture mentioned might be a solution here, which is what I want to know more about.


texts: Ola Hallengren Index Script not Reindexing

texts:
First, I realize a similar question was asked and the poster had page count set to 1000 for a 679 page index; not what's going on.

I have Ola's script set as

@Databases nvarchar(max)
,@FragmentationLow nvarchar(max) = null
,@FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE'
,@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE'
,@FragmentationLevel1 int = 50
,@FragmentationLevel2 int = 75
,@PageCountLevel int = 400
,SortInTempdb nvarchar(max) = 'N'
,maxdop int = null
,fillfactor int = null
,PadIndex nvarchar(max) = null
,LOBCompaction nvarchar(max) = 'Y'
,UpdateStatistics nvarchar(max) = 'ALL'
,OnlyModifiedStatistics nvarchar(max) = 'Y'
,StatisticsSample int = null
,StatisticsResample nvarchar(max) = 'N'
,PartitionLevel nvarchar(max) = 'Y'
,MSShippedObjects nvarchar(max) = 'N'
,Indexes nvarchar(max) = 'MyDatabase.dbo.Table'
,TimeLimit int = null
,delay int = null
,WaitAtLowPriorityMaxDuration int = null
,WaitAtLowPriorityAbortAfterWait nvarchar(max) = null
,LockTimeout int = null
,LogToTable nvarchar(max) = 'Y'
,

execute nvarchar(max) = 'Y'

The index in question is as follows:

Page Fullness: 75.77%
Fragmentation: 99.14%
Avg. Row Size: 33
Depth: 4
Index Type: Nonclustered
Leaf-lvl rows: 11130800
Max Size: 33
Min Size: 33
Pages: 63585
Partition ID: 1

It's set so low because the 1000 mark isn't hitting the index. What's odd is that stats are being updated but the index isn't being reorganized or rebuilt.

I'm looking for things to explore or concepts to think about that will help me solve this issue.

Again, if this has been asked and answered, please forgive me and forgive me if the answer is so ridiculously easy and stupid that it's staring me in the face.

Thanks.

Table Schema

CREATE TABLE [dbo].[Table](
    [column1] [uniqueidentifier] NOT NULL,
    [column2] [uniqueidentifier] NOT NULL,
    [column3] [uniqueidentifier] NOT NULL,
    [column4] [uniqueidentifier] NOT NULL,
    [column5] [nvarchar](50) NULL,
    [column6] [nvarchar](128) NULL,
    [Column7] [money] NOT NULL,
    [column8] [money] NOT NULL,
    [column9] [nvarchar](128) NULL,
    [column10] [nvarchar](64) NULL,
    [column11] [image] NULL,
 CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED
(
    [Column1_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

CREATE NONCLUSTERED INDEX [IX_Index] ON [dbo].[Table]
(
    [column2] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 70) ON [PRIMARY]
GO


texts: How to run update queries saved in a permanent table?

texts:
Permanent Table Name: PermaTable1
Values:

Question is how to execute all update commands inside the table PermaTable1?

This is my query but it does not execute the update command.

DECLARE @Query1 Nvarchar(MAX)

SET @Query1= 'SELECT Query FROM dbo.PermaTable1'

EXEC(@Query1)

Hope you can help me. Thank you.


texts: Resource to learn writing most efficient and highly optimized sql queries

texts:
Possible Duplicate:
  Learning to Optimize SQL Queries and Understand Execution Plans - Resources?

Can some one point me to a great book/great books or some really solid reference/references in the internet space that precisely deals in writing the most efficient and most optimized sql queries for Oracle?


texts:
Do FOREIGN KEYs impose the uniqueness of the the referent?
I.e. if I have a foreign key that refers to a UNIQUE constrained column, will the FOREIGN KEY column be unique?
I'm particularly wondering about postgresql.


texts: Is there a better option than Union All for multiple selects from the same row?

texts:
Example Schema:

CREATE TABLE [dbo].[Base](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Option1ID] [int] NULL,
[Option2ID] [int] NULL,
[Option3ID] [int] NULL,
[Option1Name] [varchar] NULL,
[Option2Name] [varchar] NULL,
[Option3Name] [varchar] NULL,
[Option1LName] [varchar] NULL,
[Option2LName] [varchar] NULL,
[Option3LName] [varchar] NULL,)

Is there a way to get results that show up like:

ID | OptionID | OptionName | OptionLName

I have tried to achieve this using UNION ALL but this means going over the same row 3 times in my example, in my real problem I have to do it 10 times. I cannot normalize the table due to legacy code. Is there a way to only go over the Base row once?


texts: Fastest way to restore a Sql Server database

texts:
Each of our 10000 integration tests need to restore the database to its original "clean" state before each of them are done, during a cleanup stage. Running all tests takes time, and 80% of the time is spent during the database restore operation.

The restore operation simply takes a backup from disc and restores it.
I am now wondering if there is a way to optimize this.
One option I am thinking of is to change the location of the backup file from disc to memory. Is that possible?

Another option would be to take a snapshot of the table data only, and save only that as the backup data. I assume then that the backup file becomes smaller, and therefore faster to restore. Is that possible?

Ideally, I would like to avoid restoring, and just reset the affected data (the data that each test produces). Something like: "revert any table rows that were modified since the test started"

What options do i have? I am not an expert in this area but it feels like my problem should have an easy solution.


texts:
Incarnations are explained in an answer to another question on this site. The answer mentions 'orphaned' incarnations:

  …There are other factors that result in ORPHANED incarnations and OBSOLETE backups…

I see from the Oracle docs that V$DATABASE_INCARNATION includes a STATUS column which can have values of ORPHAN, CURRENT or PARENT, which must be related.

What are 'orphaned' incarnations, and what steps would result in a row with STATUS=ORPHAN in V$DATABASE_INCARNATION?


texts:
I'm trying to delete rows using another table and wondering why postgres doesn't use an index, here are the steps, on pg10.5:

CREATE TABLE parent (id int);
CREATE TABLE child (pid int);

CREATE INDEX parent_idx ON parent (id);
CREATE INDEX child_idx ON child (pid);

INSERT INTO parent SELECT * FROM generate_series(1, 100000);
INSERT INTO child SELECT * FROM generate_series(1, 100000);
VACUUM ANALYZE parent; VACUUM ANALYZE child;

EXPLAIN DELETE FROM child c USING parent p WHERE p.id = c.pid AND p.id < 10000;
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Delete on child c  (cost=464.31..2383.65 rows=10134 width=12)
   ->  Hash Join  (cost=464.31..2383.65 rows=10134 width=12)
         Hash Cond: (c.pid = p.id)
         ->  Seq Scan on child c  (cost=0.00..1443.00 rows=100000 width=10)
         ->  Hash  (cost=337.64..337.64 rows=10134 width=10)
               ->  Index Scan using parent_idx on parent p  (cost=0.29..337.64 rows=10134 width=10)
                     Index Cond: (id < 10000)

I would have expected Postgres to pick an Index Only Scan using parent_idx followed by an Index Only Scan on child_idx. What am I missing?


texts: What security concerns are with allowing pgAgent to database users?

texts:
I have a customer who would like to use pgagent, however, as a DBA I have security concerns with pgagent having the ability to execute shell scripts from within the Postgres instance.

Can anybody tell me how I might prevent pgagent from running ad-hoc shell scripts, or more generally how I can secure pgagent so that the customer can schedule any SQL against any database they have access to but not do much else?

Thanks.
Bill


texts: To cluster or not to cluster? That is the question!

texts:
I have a table with just one (nvarchar) column for tracking IDs that have been used, to ensure they aren’t duplicated. Should I use Clustered or Non-clustered Index for this? The only two operations on this table will be 1) to insert a new id and 2) to check if an id already exists in this table.

I have done some research and I understand that a clustered index sorts the table in the order of the index whereas NCI is just a pointer to the row but I'm still not sure which to use when there is just one column.


texts:
What is the significance of SORT_IN_TEMPDB when working with indexes on a table of significant size (20GB plus)?

Where else does sorting take place?


texts: Recovering from the loss of datafile in NOARCHIVELOG mode?

texts: How do you recover from the loss of datafile if the database is running in NOARCHIVELOG mode?


texts: Autopostgresqlbackup - DBEXCLUDE doesn't works

texts:
I'm trying to exclude some databases using the DBEXCLUDE option,but there's daily backups anyways.

# ===============================
# === Debian specific options ===
#================================

# By default, on Debian systems, only 'postgres' user
# is allowed to access PostgreSQL databases without password.
# In order to dump databases we need to run pg_dump/psql
# commands as 'postgres' with su.
#
# The following setting has been added to workraound this issue.
# (if it is set to empty, 'su' usage will be disabled)
SU_USERNAME=openerp

#=====================================================================
# Set the following variables to your system needs
# (Detailed instructions below variables)
#=====================================================================

# Username to access the PostgreSQL server e.g. dbuser
USERNAME=openerp

# Password
# create a file $HOME/.pgpass containing a line like this
#   hostname:*:*:dbuser:dbpass
# replace hostname with the value of DBHOST and postgres with
# the value of USERNAME

# Host name (or IP address) of PostgreSQL server e.g localhost
DBHOST=localhost

# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
DBNAMES="all"

# pseudo database name used to dump global objects (users, roles, tablespaces)
GLOBALS_OBJECTS="postgres_globals"

# Backup directory location e.g /backups
BACKUPDIR="/var/lib/autopostgresqlbackup"

# Mail setup
# What would you like to be mailed to you?
# - log   : send only log file
# - files : send log file and sql files as attachments (see docs)
# - stdout : will simply output the log to the screen if run manually.
# - quiet : Only send logs if an error occurs to the MAILADDR.
MAILCONTENT="quiet"

# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])
MAXATTSIZE="4000"

# Email Address to send mail to? (user@domain.com)
MAILADDR="root"

# ============================================================
# === ADVANCED OPTIONS ( Read the doc's below for details )===
#=============================================================

# List of DBBNAMES for Monthly Backups.
MDBNAMES="template1 $DBNAMES"

# List of DBNAMES to EXLUCDE if DBNAMES are set to all (must be in " quotes)
DBEXCLUDE="DrFabioGonzalez","DraLuisaFPaez","DraMariaVRomero","DraNhoraOrtiz","DraMyriamRamirez","Entrenamiento","GastroCenter","PsicologiaFUAC"

# Include CREATE DATABASE in backup?
CREATE_DATABASE=yes

# Separate backup directory and file for each DB? (yes or no)
SEPDIR=yes

# Which day do you want weekly backups? (1 to 7 where 1 is Monday)
DOWEEKLY=6

# Choose Compression type. (gzip or bzip2)
COMP=gzip

# Compress communications between backup server and PostgreSQL server?
# set compression level from 0 to 9 (0 means no compression)
COMMCOMP=0

# Additionally keep a copy of the most recent backup in a seperate directory.
LATEST=no

# OPT string for use with pg_dump ( see man pg_dump )
OPT=""

# Backup files extension
EXT="sql"

# Command to run before backups (uncomment to use)
#PREBACKUP="/etc/postgresql-backup-pre"

# Command run after backups (uncomment to use)
#POSTBACKUP="/etc/postgresql-backup-post"


texts: View with parameters in Sybase SQL Anywhere

texts: A view with Sybase engine cannot take parameters. Is there a way to do this?


texts: MySQL: logging queries which would execute without using indexes

texts:
I am trying to use log_queries_not_using_indexes = 1 to find queries which are not executing optimally on a MySQL server. However, I find the resulting log file of rather limited value. Apparently, queries are logged whenever the optimizer really decided not to use an index as a criterion in a WHERE clause. And not if they truly have no indexes matching the filtered columns.

So given a table with the following structure

CREATE TABLE `test` (
    `id_test`   int(11) NOT NULL AUTO_INCREMENT,
    `some_text`  varchar(255) DEFAULT NULL,
    `some_more_text` text,
 PRIMARY KEY (`id_test`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1

a query SELECT id_test from test where id_test != 69 would be logged to the slow log because of not using indexes (the optimizer has decided that a table scan is more efficient as not much could be won by using an index) but SELECT id_test from test where id_test = 69 would not.

I would expect the behavior of the latter query in the first case as well since the index is present. As is, it makes troubleshooting missing indexes rather tiresome. Ideas on how to approach this greatly appreciated.


texts: Return xml sequences where an attribute doesn't contain a specific character

texts:
Consider the following simple XML:

<xml>
  <customer name="Max">
    <email address="me@you.com" />
  </customer>
  <customer name="Erik">
    <email address="erik@your-mom.com" />
  </customer>
  <customer name="Brent">
    <email address="brentcom" />
  </customer>
</xml>

I want to get a list of <Customer> sequences where the address attribute of the <email> item does not contain an @.

So, I want output that looks like:

<customer name="Brent">
  <email address="brentcom" />
</customer>

mcve:

DECLARE @x XML = '<xml>
<customer name="Max"><email address="me@you.com" /></customer>
<customer name="Erik"><email address="erik@your-mom.com" /></customer>
<customer name="Brent"><email address="brentcom" /></customer>
</xml>';

This query:

SELECT WithValidEmail = @x.query('/xml/customer/email[contains(@address, "@")]')
    , WithInvalidEmail = @x.query('/xml/customer/email[contains(@address, "@")] = False');

Returns:

╔═══════════════════════════════════════╦══════════════════╗
║            WithValidEmail             ║ WithInvalidEmail ║
╠═══════════════════════════════════════╬══════════════════╣
║ <email address="me@you.com" />        ║                  ║
║ <email address="erik@your-mom.com" /> ║ false            ║
╚═══════════════════════════════════════╩══════════════════╝

This query:

SELECT WithInValidEmail = @x.query('/xml/customer/email')
WHERE @x.exist('/xml/customer/email[contains(@address, "@")]') = 0;

Returns:

╔══════════════════╗
║ WithInValidEmail ║
╚══════════════════╝
    (no results)

The WHERE clause in the query above is eliminating the entire set of XML because at least a single sequence exists where the email address contains an "@" sign.


texts: Strange IO stall values in sys.dm_io_virtual_file_stats

texts:
Values for io_stall_read_ms seems to be higher than actual run time for query.
The setup:

create database filestats;

use filestats;

create table t1_primary(
 a  int     identity
,b  int
,c  datetime
,d  char(780)
,constraint pk_p_a primary key clustered (a));

set nocount on;
declare @n int = 0;

while @n < 12800
begin
    insert into t1_primary select @n, CURRENT_TIMESTAMP, ABS(CHECKSUM(NEWID()))
select @n += 1
end

set statistics io, time on;

declare @before datetime

dbcc dropcleanbuffers
checkpoint

if object_id('tempdb..#vfsBefore') is not null
    drop table #vfsBefore

if object_id('tempdb..#vfsAfter') is not null
    drop table #vfsAfter

select *
into #vfsBefore
from sys.dm_io_virtual_file_stats(db_id('filestats'), null)

select @before = CURRENT_TIMESTAMP

select * from dbo.t1_primary

select DATEDIFF(ms, @before, CURRENT_TIMESTAMP)

select *
into #vfsAfter
from sys.dm_io_virtual_file_stats(db_id('filestats'), null)

select
 db_name(b.database_id)
,mf.name
,a.num_of_reads-b.num_of_reads              as NumReads
,a.num_of_bytes_read-b.num_of_bytes_read    as NumBytesRead
,a.io_stall_read_ms-b.io_stall_read_ms      as IOStallReadMS
,a.num_of_writes-b.num_of_writes            as NumWrites
,a.num_of_bytes_written-b.num_of_bytes_written  as NumBytesWritten
,a.io_stall_write_ms-b.io_stall_write_ms    as IOStallWriteMS
,a.io_stall-b.io_stall                      as IOStallTotal
,a.size_on_disk_bytes-b.size_on_disk_bytes  as SizeOnDiskBytes
,mf.physical_name
from #vfsBefore b
join #vfsAfter a on b.database_id = a.database_id and b.file_id =     a.file_id
join sys.master_files mf on b.database_id = mf.database_id and b.file_id = mf.file_id

Running this results in the following:

Virtual file stats:
num_of_reads = 26,
io_stall_read_ms = 2153

Statistics IO:
Table 't1_primary'. Scan count 1, logical reads 2571, physical reads 3, read-ahead reads 2552

Statistics time:
CPU time = 46 ms,  elapsed time = 595 ms.

Run time, DATEDIFF function:
593 ms

Not surprisingly a clustered index scan is performed, single threaded.
How come the io_stall_read_ms value is higher than the time executing the query. What is going on here?


texts:
I have a query in which I am trying to add a column which contains a percentage of records which have a certain value versus the total number of records. I can get the subquery to work on its own, but when I plug it into the main query it fails with an error :
ERROR:  subquery uses ungrouped column "ipam_addresses.host_id" from outer query

my query is:

select
    ipam_networks.ipam_id,
    ipam_networks.net_name,
    ipam_networks.net_cidr,
    zone,
    count(*),
    to_char(100*(select count(*) where ipam_addresses.host_id = 0) / (select count(*)), '999D99%') v
from
    ipam_addresses
left outer join
    ipam_networks
    on ipam_addresses.parent_id = ipam_networks.ipam_id
where
    ipam_addresses.ip_state != 'DHCP_FREE'
group by
    ipam_networks.ipam_id, ipam_networks.net_name, ipam_networks.net_cidr;

Why is it throwing this error even if I specify the table name in the subquery, and what can I do to fix this?


texts: CentOS 8 Unable to connect to PostgreSQL 12 Server

texts:
When I attempt to connect to postgres in from my linux command line, I receive the following error:
[lloyd@localhost ~]$ sudo -u postgres psql
could not change directory to "/home/lloyd": Permission denied
psql: error: could not connect to server: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
[lloyd@localhost ~]$

Here is my log file - /var/log/secure:
Nov 14 09:57:01 localhost sudo[33108]:   lloyd : TTY=pts/0 ; PWD=/home/lloyd ; USER=postgres ; COMMAND=/bin/psql
Nov 14 09:57:01 localhost sudo[33108]: pam_systemd(sudo:session): Cannot create session: Already running in a session or user slice
Nov 14 09:57:01 localhost sudo[33108]: pam_unix(sudo:session): session opened for user postgres by (uid=0)
Nov 14 09:57:01 localhost sudo[33108]: pam_unix(sudo:session): session closed for user postgres
Nov 14 09:57:12 localhost sudo[33144]:   lloyd : TTY=pts/1 ; PWD=/home/lloyd ; USER=root ; COMMAND=/bin/vi /var/log/secure
Nov 14 09:57:12 localhost sudo[33144]: pam_systemd(sudo:session): Cannot create session: Already running in a session or user slice
Nov 14 09:57:12 localhost sudo[33144]: pam_unix(sudo:session): session opened for user root by (uid=0)

Any ideas as to why I'm receiving this error msg or how to resolve it would be greatly appreciated.
Thanks,
Lloyd


texts: Use connection string to connect to Azure database from SSMS?

texts: I have a connection string for an Azure database. I'm hoping there is a place in SSMS where I can plop in that string and connect to it. Alternatively, if there is a way to do it with a T-SQL query, that would be even better.


texts: Difference between FreeProcCache and FreeSystemCache

texts: Currently I run DBCC FREEPROCCACHE when I want to wipe information out of the buffer pool between running SQL queries. However, I was reviewing this Technet article referencing DBCC FREESYSTEMCACHE.  What caches does FREESYSTEMCACHE wipe that FREEPROCCACHE doesn't?


texts: Remove single quotes from db2 column name

texts:
I have following query which i am trying to execute against db2 database server

SELECT REPLACE('\'abc\'','\'','') FROM TN_DAY_PROCESS

I am expecting the output like abc without single quotes

from the abc text, I want to remove single quote. to do that, I tried the method shown above using REPLACE function in db2, but it is giving me following error

The character "\" following "ELECT REPLACE('\'abc" is not valid.. SQLCODE=-7, SQLSTATE=42601, DRIVER=3.66.46 SQL Code: -7, SQL State: 42601

Could somebody help me to figure out the solution to remove the single quotes from a string in db2 database.Thanks in advance for any help

While searching for an answer for this question, I found following method

SELECT REPLACE('''abc''','''',SPACE(0)) FROM TN_DAY_PROCESS

so, the way to specify single quote is to write it twice


texts:
I recently added more CPU cores to a SQL Server and as best practices advise, I separated out the tempDB files into the same number of cores(8).

Also along with best practices, I expanded the size of the new tempdb files, limited auto-growth and shrunk the original files to match the size of the new files.

The problem is, since I was going from a single core to 8 cores, all of the tempdb data remained in the first file, which wouldn't allow me to shrink.  Now I have one tempdb file that is larger than the others and I would like to change that.

I suspect to fix this it will require an outage.

Is there a way to do this without an outage?
Is this problem even worth addressing?  I know what best practices say, but does this instance really cause any practical problems other than ugliness?


texts: MySQL SSL Master-Slave replication not syncing

texts:
I have originally posted this thread on stackoverflow and after a comment I moved it here. The original post can be found here.

I have setup a typical Master-Slave replication scheme for my MySQL database. Up until now the topology is consisted from only one master and one slave node, both are using SSL to securely communicate with each other.

On Master node:

On the Slave node:

I have no error in the error.log on neither side, but when I change something on the Master node and then check the slave, the change is not replicated.

I followed this tutorial so I am pretty confident that my my.cnf are properly configured.

Is there any hints on what can be wrong in this setup?


texts: I'm trying to determine the performance characteristics of citext. In Microsoft SQL Server, the nvarchar(max) has performance hits when there is excessive memory grants required to use it versus something like a varchar(50). There are other performance hits that come in to play also. I'm trying to determine if citext in PostgreSQL has similar issues. We have a PostgreSQL backed web-app that is horribly slow and I'm wondering if the extensive use of the citext data type is contributing. Any thoughts on this? Thank you!


texts: ODBC Driver for Salesforce: Access Linked Server Object in SSMS

texts:
I have problems to access a linked server for a Salesforce ODBC data source with our MS SQL Server 2016 (Standard 64-Bit) using the Devart ODBC Driver for Salesforce in SSMS and SQL Studio Management Studio (17.8.1).

I have created a linked server in SSMS as described in the documentation.

Testing the connection to the ODBC source only works in SSMS when I use the 64-bit source. When I use the 32-bit ODBC source no connection can be established and the following error is shown:

  The linked server has been created but failed a connection test.
  Cannot initialize the data source object of OLE DB provider "MSDASQL"
  for linked server "SF". OLE DB provider "MSDASQL" for linked server
  "SF" returned message "[Microsoft][ODBC Driver Manager] The specified
  DSN contains an architecture mismatch between the Driver and
  Application". (Microsoft SQL Server, Error: 7303)

Whilst I can connect to the 64-bit source I cannot access the linked server object in SQL Server Management Studio 17.x (32 bit - I believe there is no 64-bit version of SSMS):

When I try to expand the Server Object tree view I get the following error:

Select all Error: "Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server

Or when I try to query something, e.g. SELECT TOP 1 * from SFDC...[Contact]

Cannot create an instance of OLE DB provider "MSDASQL" for linked server "SF". (Microsoft SQL Server, Error: 7302)

I have tried a few solutions that are associated with error 7302/3 but nothing seems to work:

MSDASQL provider: disable Allow inprocess [this was required]
Restart (the database engine and even Windows)
SQL Server create procedure sp_tables_info_rowset_64 [does not fix
the issue]

I found out I can indeed connect to the Salesforce ODBC source and access the data using a 3rd Party Database Manager (Database5Pro 64bit, an SSMS alternative) - but this does not allow me to access both data sources at the same time (no linked objects available).

The Devart driver requires (as mentioned in the installation guide, see page 41) the same bitness of all moving parts.

  The driver, studio, and SQL Server must be of the same bitness.

Can anyone suggest a way to use a Salesforce source as a linked server along with SQL Server 2016 in an SSMS-like way? I am open to alternative solutions and 3rd-party tools.

I have also tried an older version of SSMS (2012) and the beta version of SQL Operations Studio (basically VS Code like SSMS, actually a 64-bit application) to no avail.


texts: Which mechanism is best for previous data archived in oracle?

texts:
Our company has a big data server in which large data is stored, like images, video, audio, etc. Our company wants a process running with the new year, such that in 2020 the previous year's 2019 data will be automatically archived and removed tables the live tables.  In the future, we'll take a backup of the archived data.

I am new in this field. Which Mechanism should I use for this purpose?


texts: how to restore sqlite dump file to sqlite database using java?

texts:
I generated dump file from java using .dump command so my back-up is ready now I want to restore it to once again so can anyone help me how to restore?

In stackoverflow I got this but nothing restore from this..backup-and-restore-sqlite-from-disk-to-memory-in-java

so how to restore this file?


texts: SQL identity column with unique code column

texts:
This seems a simple question but is it still necessary to include a primary key with identity even if my columns are only a unique code and its description?

CREATE TABLE table1 (
      column1 INT NOT NULL IDENTITY,
      column2 CHAR(4) NOT NULL,
      column3 VARCHAR(10) NULL
      CONSTRAINT pk_id PRIMARY KEY (column1),
      CONSTRAINT uq_column2 UNIQUE (column2)
)

Sample values:

INSERT INTO table1 VALUES ('A', 'aaa')
INSERT INTO table1 VALUES ('B', 'bbb')


texts: select group by with multiple AND conditions on the same columns

texts:
I've an sql table like this:

SID|SERVICE_SID|ATTRIBUTE_SID|VALUE
1    2           5             "VAL1"
2    2           4             "VAL8"
3    2           3             "VAL4"
4    11          5             "VAL1"
5    11          3             "VAL4"

I'd like to group by service_SID where for the same service_sid:

AND (attribute_sid = 5 && Value="VAL1")

AND (attribute_sid = 4 && Value="VAL8")

So, in this case, only a row with service_sid = 2 should be returned.

Is this a query like this even possible? What would it look like?


texts:
So I have this concept in my head of "immutable" records, which I am sure is not novel, but can't seem to find the right google keywords.

Basically, historical row values need to be preserved as 'obsolete', but remain referencable as FKs from older records. These types of modifications are infrequent. I'm using MySQL, which limits things a bit.  The four approaches I see are:

Naive

PK is an auto-generated ID
obsolete_stamp is a timestamp indicating    that the row values are
obsolete

Drawback: historical data is preserved, but there is no way to track history of changes

Better:

PK same as above
old_id is the id of the row that this row modifies (if exists)
obsolete_stamp (null if row data is current)

consequence: new rows are modifications of old rows, but the new rows have to update the old_id (if there is one)

audit:

an on update trigger creates a new row in an audit table

drawback: queries will need to check audit table for obsoleted rows

compound key:

id + obsolete_stamp

drawback: id can't be generated anew, only if the row is 'novel' (no history); not clear how to do that automatically.

I'm one of those full-stack developers, so I know just enough about everything to be dangerous.


texts:
We do not appear (yes/No) for hide instance in SSCM for all SQL Server 2012 servers.
We are unable to see (yes/No) option for hide instance . Kindly assist me here .
Thanks a lot !


texts: How to handle -904 Invalid identifier error in oracle store procedure

texts:
My proc is like this

CREATE OR REPLACE PROCEDURE TEST_INSERT
    IS
      TotalUpd pls_integer;
      err_code number;
      err_msg varchar(500);

      invalid_excep EXCEPTION;
      PRAGMA EXCEPTION_INIT(invalid_excep,-904);
    BEGIN
      insert into test_proc_tbl(un_id) /* this column doesn't exist */
        select 'asd' from dual;

        TotalUpd := SQL%ROWCOUNT;
        commit;

    EXCEPTION

        WHEN invalid_excep THEN
            INSERT INTO err_log(src,typ_ex,ERROR_DESC,ROW_COUNT,RUN_STATUS,UPDATED_DATE)
            select 'TEST','STORE PROCEDURE',to_char(err_code) || '-' || err_msg,TotalUpd,'ERROR',SYSDATE from dual;
            COMMIT;

        WHEN OTHERS THEN
            err_code := SQLCODE;
            err_msg := SUBSTR(SQLERRM,1,200);

            INSERT INTO err_log(src,typ_ex,ERROR_DESC,ROW_COUNT,RUN_STATUS,UPDATED_DATE)
            select 'TEST','STORE PROCEDURE',to_char(err_code) || '-' || err_msg,TotalUpd,'ERROR',SYSDATE from dual;
            COMMIT;

    END;

I am trying to insert Invalid identifier error in my log table, purpose of doing this some of my base table will be recreated twice in a week, other team can miss or modify column name. Here my problem is Exception doesn't catch invalid identifier error, how to fix this?


texts: What does wait_resource LOG_MANAGER mean in SQL Server 2008?

texts:
Let's say we have two Stored Procedures A & B within the same database.

Stored Procedure A:

Executes for a long time
Has transactions within
Makes Calls over Linked Server

Stored Procedure B:

Simple SP that inserts/updates a table
Quick. Within 1 second

A & B execute on the same database but DO NOT have any common tables.

Yesterday, B was blocked by A and I found that the

wait_type was LATCH_EX
wait_resource was LOG_MANAGER

What does this LOG_MANAGER mean? What kind of resource are they waiting on? Plus, does it have anything to do with with transactions within the 'A' SP.

A quick Googling indicates that it has something do with growth of logs, but I would appreciate a more indepth explanation.


texts: Does using an auto increment of 1 on numeric PK hurts performance?

texts:
A teacher of mine said that having an numeric PK with auto increment is a bad practice because it hurts performance.

He also said that Oracle database doesn't have an easy way to set up auto increment because of this.

He said that increment should be done on app instead of DB.

The performance penalty, if I remember correctly, would happen while inserting many rows, it had something to do with allocating a certain number of PKs IDs or something.

Is this true? If yes why?


texts: Best Practices - Copying Table with datetime stamped on name

texts:
For backing up purposes I need to copy a table into the same DB but with
Date and Time on the TableName.

My solution doing this with T-sql Job:

select * into dbo.tmp from dbo.something
go

declare @thedate as varchar(100)
set @thedate = 'something'+convert(nvarchar(MAX), getdate(), 13)

EXEC sp_rename 'tmp', @thedate
go

Is there a better practice doing this?

I am wondering if copying into a tmp table and changing the name may be
not a good idea!!!. I am receiving a message saying
"Caution: Changing any part of an object name could break scripts and stored procedures."


texts: How to order by then start at first non null value and select a limit for number of values

texts:
I have an environmental logger and I am trying to select every 5th row starting at where outsidetemp is not null (I want to include the null values after the first not null value). Is there a way to offset to find the first not null value?

My line of code is below everything is working except I cannot get the offset set.

SELECT * FROM (SELECT @row := @row+1 AS rownum, ttime,temperature,humidity,outsidetemp
FROM (SELECT @row :=0) r, tempHumiditydata) ranked
WHERE rownum % 5 = 1
ORDER BY rownum DESC LIMIT 2020 ??????OFFSET (WHERE outsidetemp IS NOT NULL)????????

Additional background. The outsidetemp is being logged once for every 60 of the other data points the values in between these points is Null.

My code above is being used to graph the data so it is important I start at the first not null value to capture the remaining not null values (starting at the wrong point and I could not get any data for outsidetemp).

I am skipping every 5th row to reduce the amount of data in a wider time frame graph.

Here is an example of what I need
Ttime     temp        humidity        outsidetemp
8:00      71      50          NULL
7:00     72      49          80
6:00     73      48          NULL
5:00     73      48          NULL
4:00     73      48          NULL
3:00     69      51          76

Using only every other record starting at the first non null outsidetemp yields
7:00     72      49          80
5:00     73      48          NULL
3:00     69      51      76

I can then use the above to graph temp, humidity and outside temp versus time. I just interpolate between non null outside temp values.


texts: How do you list all logins for Sql Azure?

texts:
What queries can I use to list all logins for a server and database in SQL Azure?

I tried running select * from sys.sysusers but I'm guessing that's just showing the users


texts: MySQL server stops accepting connections partially ("gone away" error)

texts:
Hope someone can help me debug this issue.
There is a MySQL server 5.6.22 running on OS X 10.2. The load isn't too heavy. All databases are stored in InnoDB. From time to time it stop accepting connections from PHP applications and said applications return with a 2006 "gone away" error. Both PHP and MySQL are on the same machine. External connections are not allowed.

It works for a while after restart and then some arbitrary amount of hours later it starts denying some connections. If I keep it running like that some time later the ratio of denied vs accepted connections grows to the point of the server becoming unusable.

Connecting via the command line works. Nothing in the error logs (warning mode turned on). Restarting the server helps. "FLUSH TABLES" helps equally good. What's strange is if I issue "FLUSH TABLES tb_name" it suddenly starts denying all connections via PHP (not just some of them) until I issue "FLUSH TABLES" without a db name. I tried flushing every table sequentially by name but it doesn't help.

None of the suggestions from MySQL documentation or tinkering with various related variables helped.

This started happening when the OS was 10.9 and I think MySQL server got upgraded to a newer version at some point, although I am not 100% certain if that was the thing that introduced the unstable behavior.

I tried looking at global status, current connections et cetera - nothing out of the ordinary. I.e. the server doesn't get flooded with connections that don't get closed for example and I set a very short timeout for it anyway. It also doesn't have too many open files. The system is also mostly idling, lots of available CPU time and memory.

Any suggestions on where to look to get the root of the problem? It's not nice that the logs don't show anything and basically the only clue I have at the moment is that "FLUSH TABLES" works its magic, for whatever reason. If only I could break that command down to bits and execute them separately to see what part alleviates the problem...

Any suggestions on where to start looking?


texts: MongoDB convert standalone mongod to sharded replica set cluster

texts:
I have a single replica set having 3 mongod instance, collection in this instance has data already.

I want to convert this instance to sharded cluster. I followed Convert a Replica Set to a Sharded Cluster but when I access my mongod at the end of the setup it doesn't show the existing data.

Any suggestions?


texts:
Why Java Pool is not a required component of Oracle SGA?

As we know SGA has many components, I mean:

Java Pool
Shared pool
Large Pool
Data Buffer cache
Others...

I took an exam and I saw that afirmation that i never understood: Java Pool is not a required component of SGA.


texts:
I'm trying to read the content of /etc/passwd from mysql using the following query: select load_file("/etc/passwd");
However, I always seem to get NULL as the output.

mysql> select load_file("/etc/passwd");
+--------------------------+
| load_file("/etc/passwd") |
+--------------------------+
| NULL                     |
+--------------------------+
1 row in set (0.00 sec)

Is there a reason for this? the permission for the passwd file is the default permission.


texts: SQL Server tempDB data file is growing very fast. 120gb

texts:
My tempDB is now over 120GB. It has grown 65GB in a couple of hours. It is not the log but the actual DB that is growing. Reports show 99% is unused space. I tried restarting the server that SQL Server is on in hopes that would clear it out but the size did not reduce. How do I fix this? I can't seem to get it to release the allocated space.

UPDATE: I have manged to gain back 1 gb from Temp db. The c: drive now has 21gb of free space and the tempDB is not growing now. Our offices are closed and I am the only working.


texts: How is the B-Tree created for a composite PK?

texts:
This is a theoretical question in regards to how SQL Server stores the data for a table that has a clustered index over two columns (both columns form a PK). Consider a table with columns PersonID, PersonAge and Name. I have a clustered index on the two columns: (PersonID, PersonAge).

How is the information stored using the index (B-Tree)? When I query this, only PersonID was sorted physically and PersonAge was left out (not sorted). Any help is much appreciated, thank you!

** Please do not give design recommendations, this is a theoretical question. **


texts: Online backups with VENDOR library: what should be set for ACTIVE LOG PATH?

texts:
My customer is using Veritas NetBackup so we use its library file in the LOGARCHMETH1 parameter for the VENDOR option. That is for archive logging path.

What path should be set for ACTIVE LOG PATH (LOGPATH)? Some location on local database storage? Will DB2 then transfer from active to archive logs between those two servers (database and Veritas NetBackup) automatically? Or how does it work?


texts: Restrict Oracle database schema to login using Toad/PLSQL

texts:
I want to restrict my Oracle database schema for example john to login to database using tools like toad or PLSQL developer. this schema is used for application login using JDBC thin client. I cannot lock or change the password because some of our senior developer have access on this schema. I want to restrict these developer to not able to login database using toad or PLSQL
kindly guide if there is any way?


texts: How to skip DMS validation on records which are deleted in target DB in DMS task “full load + ongoing replication”?

texts:
Continuously I am running DMS task with "full load + on-going replication" and enabled "validation" as well. Replication is working as expected but validation is getting failed when I delete old records from target database.
I was testing the below scenario (example). My DMS task is running continuously -

day 1 - '500' records replicated to target from source and DMS
validation task is successful.
day 2 - before loading another '4000'
new records into source database I deleted 'day 1' '500' records
from target database. But validation is failed.

Here, observed that DMS task replicated only new '4000' records to target database, which is expected but it's trying to validate all the records (500 + 4000) then it is failed.

It looks like, DMS task is trying to validate deleted '500' records as well. When we enable CDC it should perform validation on on-going changes not already replicated records. Why it's performing entire validation every time? How to control this in DMS so that it can only validate on-going changes?


texts:
I am receiving data from an external source in batches that I need to import to my PostgreSQL database. I have 2 main tables: clients and providers. Ideally, they should have a 1:1 relationship, however, this isn't always going to be true with the data I receive. There are cases where a client will be sent in a batch and it's provider counterpart will only be sent in a later batch (or vice versa).

My question is what is the best way to work around this issue? Currently, I have the two tables that are unlinked but have the potential to be if the "keys" are present in both tables but something tells me this isn't the best way to go about things.

Looking forward to hearing your thoughts!

Many thanks


texts: Is the double dollar sign ($$) quotation method in SQL standard?

texts:
In PostgreSQL, strings can be quoted very flexibly using a pair of dollar signs ($) with a tag in between. For example,

SELECT $f$ Hi $f$;

Is this quoting mechanism a part of any SQL standard, or is it postgres specific?


texts: Address database scheme - multiple links

texts:
I am currently building a databse scheme for a small company. But at the point doing it I figured out that my attept will be unacceptable due to the ability that one action will affect multiple unwated records.

To explain this. Currently I have one table addresses that links towards person, company, order etc.

The issue comes when we have a person that is linked to address ID 1, and a company address is linked to ID 1. And now when we want to change the company address - just one field like the building number from 5 to 6. The change will affect both the company and the person address - most likely all other attached to ID 1.

How should it be solved proffesionally - as the pro's build such scheme.
My attept would be multiple address tables person_address, company_address etc. All would have the same fields which is not a optimal attempt I assume.

Thanks for any hints.


texts: oralce import joinning 2 tables using query

texts:
Is it possible to join 2 tables inside the query in oracle while importing ? I have a dump for 2 tables tab1 and tab2. and I am trying to query on 1 of the tables:
Directory=backup
DUMPFILE=twotables.dmp
logfile=tablesimp.log
tables =  tab1,tab2
Query=tab1:"WHERE code= 2
AND NOT EXISTS (SELECT 1 FROM tab2 b WHERE type=b.type and numb=b.numb)"

it just give me 0 rows Imported.


texts: Clear old log entries from API while saving N of each type by user

texts:
I have a log table that quickly gets over 20 GB and was hoping to develop a cron job that clears out the old entries while keeping the last 100 of each type for each user.

 id     |   user_id   |      api_route
1506          304           api/trayapp
1504          379           api/trayapp
1502          321           api/trayapp
1496          377           api/servers
1494          382           api/trayapp
1485          289           api/trayapp
1483          235           api/trayapp
1481          318           api/trayapp
1480          296           api/trayapp
1477          117           api/trayapp
1474          311           api/trayapp
1470          312           api/servers
1467          298           api/servers
1465          377           api/trayapp
1464          303           api/servers
1463          313           api/servers
1462          102           api/servers
1461          127           api/servers
1459          383           api/servers
1458          312           api/trayapp
1457          378           api/servers
1454          308           api/servers
1453          197           api/servers
1452          298           api/trayapp
1448          100           api/servers
1447          317           api/trayapp
1435          320           api/trayapp
1434          103           api/servers
1432          211           api/servers
1431          303           api/trayapp
1429          313           api/trayapp
1428          102           api/trayapp
1425          308           api/trayapp
1423          127           api/trayapp
1422          378           api/trayapp
1416          383           api/trayapp
1414          295           api/servers
1407          380           api/servers
1402          197           api/trayapp
1399          379           api/servers
1397          100           api/trayapp
1393          321           api/servers
1388          304           api/servers
1386          319           api/servers
1384          103           api/trayapp
1379          211           api/trayapp
1378          229           api/servers
1376          289           api/servers
1375          381           api/trayapp
1373          304           api/trayapp
1372          296           api/servers
1371          380           api/trayapp
1370          295           api/trayapp
1368          382           api/servers
1367          235           api/servers
1366          117           api/servers

I know I need to do a DELETE WHERE NOT IN (SELECT... but my first thought of how to do the select would be to use GROUP_CONCAT on the id, but then how would you use the results in the WHERE NOT IN? What would be your preferred method of removing all but the most recent? I do have created_at fields available but since the id is auto incremented it seems irrelevant.


texts:
I am trying to grant DBMS_CRYPTO privilege to one of the user in DB and referred all the forums and Oracle help page to do that. I have followed below steps to grant privilege..

SQL> grant execute on SYS.DBMS_CRYPTO to ABC1;

Grant succeeded.

SQL> commit;

Commit complete;

After running the above steps, I logged into database as ABC1 and verified the privileges he own. He doesn't have DBMS_CRYPTO privilege.

Also, I ran below sql to see how many users have this privilege and surprisingly, I dont see my user in the list.

SQL> select * from dba_tab_privs where table_name = 'DBMS_CRYPTO' and owner = 'SYS';

GRANTEE     OWNER TABLE_NAME   GRANTOR PRIVILEGE GRANTABLE HIERARCHY
SYSMAN      SYS   DBMS_CRYPTO   SYS    EXECUTE   NO        NO
APEX_030200 SYS   DBMS_CRYPTO   SYS    EXECUTE   NO        NO
OWBSYS      SYS   DBMS_CRYPTO   SYS    EXECUTE   YES       NO

My oracle version: 11.2.0.4.0, Can anyone guide me to see what I am missing in getting this privilege?

Thanks


texts:
I am receiving an error 1064 on the following stored procedure:

12 DELIMITER //
13 DROP PROCEDURE IF EXISTS add_python_photo;
14 CREATE PROCEDURE add_python_photo(  IN fname VARCHAR,
15                                     IN fpath VARCHAR,
16                                     IN fdata LONGBLOB,
17                                     IN python INT,
18                                     IN is_preferred TINYINT,
19                                     OUT python_photo_id INT)
20 BEGIN
21     INSERT INTO python_photo (  file_name,
22                                 file_path,
23                                 file_data,
24                                 python_id,
25                                 preferred)
26                         VALUES (fname,
27                                 fpath,
28                                 fdata,
29                                 python,
30                                 is_preferred);
31
32     SELECT LAST_INSERT_ID() INTO python_photo_id;
33 END//
34 DELIMITER ;

mysql> source stored_procedures/add-python-photo.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
                                                                    IN fpath VARCHAR,
                                                                    IN fdata LONGBLOB,
                                                                    IN python INT,
' at line 1

I'm uncertain as to what syntactic error MySQL is referring to. The list of 'IN' parameters appears to be syntactically correct - yet, that appears to be the source of the error. It seems to fail while processing the 'IN python_id INT' parameter.

UPDATE

Regarding the DELIMITER suggestion, below is another stored procedure - which executes correctly:

12 DELIMITER //
13 DROP PROCEDURE IF EXISTS add_python_parent;
14 CREATE PROCEDURE add_python_parent( IN python_id INT,
15                                     IN mother_id INT,
16                                     IN father_id INT)
17 BEGIN
18     INSERT INTO python_parent (python_id, mother_id, father_id)
19         VALUES (python_id, mother_id, father_id);
20 END//
21 DELIMITER ;

Execution:

mysql> source stored_procedures/add-python-parent.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)