texts: Syntax error in adding a job step in Azure SQL Job Agent

texts:
I am inserting a job step in Azure Elastic job agent. This is my code:
EXEC jobs.sp_add_jobstep @job_name='Sample T-SQL',
@command=N' declare @columns varchar(max)
select @columns = stuff(( select '],[' + [$Name]
                    from dbo.groups
                    order by '],[' + convert(varchar(max), [$_ClosingBalance]) desc
                    for xml path('')), 1, 2, '' + ']'
INSERT INTO dbo.Trial1
(   [Bank Accounts],[Bank OD A/c],[Branch / Divisions],[Capital Account],[Cash-in-Hand],[Current Assets],[Current Liabilities],[Deposits (Asset)]
      ,[Direct Expenses],[Direct Incomes],[Duties & Taxes],[Fixed Assets],[Duties & Taxes1],[Duties & Taxes2],[Indirect Expenses],[Indirect Incomes],[Investments]
      ,[Loans & Advances (Asset)],[Loans (Liability)],[Misc# Expenses (ASSET)],[Provisions],[Purchase Accounts],[Reserves & Surplus],[Sales Accounts],[Secured Loans]
      ,[Stock-in-Hand],[Stock Transfer Outward],[Sundry Creditors],[Sundry Debtors],[Suspense A/c],[Duties & Taxes3]
      ,[Unsecured Loans]
)
Select * from (
    Select [$_ClosingBalance], RowN = Row_Number() over (order by @columns) from dbo.Groups
    ) a
    pivot (max([$_ClosingBalance]) for RowN in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32])) p

INSERT INTO dbo.Date ([Date]) VALUES (GETDATE())
INSERT INTO dbo.Final
SELECT *
FROM  dbo.Trial1
INNER JOIN dbo.Date
ON dbo.Trial1.IdCol = dbo.Date.IDCol1'',
@credential_name='JobRun',
@target_group_name='DatabaseGroup5'

I am receiving the following error :
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ']'.
Msg 137, Level 15, State 2, Line 15
Must declare the scalar variable "@columns".
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near ''


texts:
Am trying to figure out was wrong with this simple query that uses MATCH_RECOGNIZE. Am getting the error ORA-00933: SQL command not properly ended. What could I be missing?

    SELECT EMPLOYEE_ID,DEPARTMENT_ID,SALARY FROM EMPLOYEES
MATCH_RECOGNIZE(
PARTITION BY DEPARTMENT_ID
ORDER BY HIRE_DATE
MEASURES
 EMPLOYEE_ID,DEPARTMENT_ID,SALARY
ONE ROW PER MATCH
PATTERN( A B* )
DEFINE
      B AS ( B.SALARY < PREV(B.SALARY))
)


texts: how do I insert timzone-pois.sql into MYSQL?

texts:
I have read about how to insert the time zone into MYSQL as below:

I can't find time zone tables in sys database of MySQL

However, after I type show databases, there was no response except showing mysql>

Thus I'd like to clarify how do I go about inserting the timzone-pois.sql into MYSQL.

I am using mysql workbench and mysql 8.0.13 server. Windows 10.
Tks.


texts: I have a contactTypeDescription and I want to find the corresponding contactTypeId for that description. I am doing the following, however it throws a warning saying that atleast one column must be mapped. What am I doing incorrectly?


texts: What is locking order of MySQL on query with join statement

texts:
When I select for update from join multiple tables, matching rows in all joined tables will be locked.  But I was wondering what is the locking order, which rows are locked first? Is it determined by where table names are in the join statement?

For example, I have two tables. user and department.

    | user  | CREATE TABLE `user`
(  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(4) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `status` varchar(63) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department_id` (`department_id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1

     department | CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(63) DEFAULT NULL,
  `status` varchar(31) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

I have two queries.

 explain
select  user.name as name, user.age as age, department.name as dept_name
    from  user
    INNER JOIN  department  ON user.department_id = department.id
    where  user.status='on'
      AND  department.status='operating'
    order by  user.age
    limit  1 for  update  \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: ALL
possible_keys: department_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: department
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: Using where; Using join buffer
2 rows in set (0.00 sec)

 explain
select  user.name as name, user.age as age, department.name as dept_name
    from  user
    INNER JOIN  department  ON user.department_id = department.id
    where  user.status='off'
      AND  department.status='operating'
    order by  user.age
    limit  1 for  update  \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: ALL
possible_keys: department_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: department
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: Using where; Using join buffer

SELECT user.id as user_id, order.id as order_id, department.id as dept_id
    FROM user
        INNER JOIN order ON user.id = order.user_id
        INNER JOIN department ON department.id = user.department_id
    WHERE user.age > 30 AND department.name in ('sales')
    LIMIT 10 FOR UPDATE

Not clear about the result of explain. It does not show which rows are locked.

Are rows in table user locked first, and then order and department? Or the matching row locked first?


texts: T-SQL Daylight Saving lookup table - poorly performing table-valued function

texts:
I've created a "Daylight Savings" lookup calendar table for the GMT region. The function I'm using to query the table to return the local datetime from a UTC datetime is performing poorly.

Any help to improve this, including changing the way the TVF is coded, would be appreciated.

The function will be used in queries that can return 1m+ rows on a frequent basis. The function is used when querying warehouse tables containing trip data.

The start and end datetimes of the trips are stored in UTC and the function above is used to convert them into local time. A developer, long since gone from the company, wrote a scalar function that converts UTC time to local time. I was tasked to rewrite that function using a calendar table and a TVF as TVF are supposed to better performing than scalar functions

Without the function:

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

execution plan without function

With the function:

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

execution plan with function

Here's a sample output from the table

CREATE TABLE dbo.DSTLookup
(
     [Id] int,
     [Tzid] int,
     [DT_WhenSwitch] datetime,
     [DSTOffSetSeconds] int,
     [GMTOffSetSeconds] int
)

INSERT INTO dbo.DSTLookup
VALUES (29, 2, N'2014-03-30T01:00:00', 3600, 0),
       (30, 2, N'2014-10-26T02:00:00', 0, 0),
       (31, 2, N'2015-03-29T01:00:00', 3600, 0),
       (32, 2, N'2015-10-25T02:00:00', 0, 0),
       (33, 2, N'2016-03-27T01:00:00', 3600, 0),
       (34, 2, N'2016-10-30T02:00:00', 0, 0),
       (35, 2, N'2017-03-26T01:00:00', 3600, 0),
       (36, 2, N'2017-10-29T02:00:00', 0, 0),
       (37, 2, N'2018-03-25T01:00:00', 3600, 0),
       (38, 2, N'2018-10-28T02:00:00', 0, 0)

This is the TVF:

CREATE FUNCTION dbo.FN_GetLocalTime_FromUTC_BasedOnTZId
     (@StartDateTime DATETIME, @EndDateTime DATETIME, @Tzid INT)
/*=========================================================================
*   2017-03-27
*   Returns local time from UTC time based on timeZoneId
*
==========================================================================*/
RETURNS TABLE
AS
    RETURN
        (
         WITH cteStartDate AS
         (
            SELECT
                RN = ROW_NUMBER() OVER (ORDER BY D.Id DESC),
                D.DSTOffSetSeconds 's_DST_OffSet',
                D.GMTOffSetSeconds 's_GMT_OffSet'
            FROM
                dbo.DSTLookup D
            WHERE
                D.DT_WhenSwitch <= @StartDateTime
                AND D.Tzid = @Tzid
         ),
         cteEndDate AS
         (
             SELECT
                 RN = ROW_NUMBER() OVER (ORDER BY D.Id DESC),
                 D.DSTOffSetSeconds 'e_DST_OffSet',
                 D.GMTOffSetSeconds 'e_GMT_OffSet'
             FROM
                 dbo.DSTLookup D
             WHERE
                 D.DT_WhenSwitch <= @EndDateTime
                 AND D.Tzid = @Tzid
         ),
         cteConvertStartDate AS
         (
              SELECT
                  DATEADD(SECOND, (COALESCE(S.s_DST_OffSet, 0) + COALESCE(S.s_GMT_OffSet, 0)), @StartDateTime) 'LocalStartDateTime'
              FROM
                  cteStartDate S
              WHERE
                  S.RN = 1
         ),
         cteConvertEndDate AS
         (
              SELECT
                  DATEADD(SECOND, (COALESCE(E.e_DST_OffSet, 0) + COALESCE(E.e_GMT_OffSet, 0)), @EndDateTime)    'LocalEndDateTime'
              FROM
                  cteEndDate E
              WHERE
                  E.RN = 1
         )
         SELECT
             S.LocalStartDateTime, E.LocalEndDateTime
         FROM
             cteConvertStartDate S, cteConvertEndDate E
);
GO

To query the TVF:

SELECT *
FROM dbo.FN_GetLocalTime_FromUTC_BasedOnTzId
    ('2017-03-27 10:00:30', '2017-03-27 10:15:54', 2);

Execution plan following Max's recommendations to include the primary key.


texts: Moving an ibd file temporarily to free up disk space

texts:
I want to optimize an InnoDB table but I do not have enough disk space. I have enabled innodb_file_per_table, so each table has *.ibd for each of their partitions. For example:

512MB /var/lib/mysql/ib_logfile0
512MB /var/lib/mysql/ib_logfile1
 70GB /var/lib/mysql/ibdata1
100GB /var/lib/mysql/dbname/table_a.ibd    (*)
100GB /var/lib/mysql/dbname/table_b.ibd    (*)
 12KB /var/lib/mysql/dbname/table_a.frm
 12KB /var/lib/mysql/dbname/table_b.frm
  4KB /var/lib/mysql/dbname/table_a.TRG
  4KB /var/lib/mysql/dbname/table_b.TRG

Is it okay to move table_a.ibd to another disk, optmize table_b, and move table_a.ibd back to its original place? I don't want to lose any data, or break anything.

MySQL server version 5.1.73


texts: select members with the longest running absence streak

texts:
I have a database containing a table that stores attendance data, and its columns are member_id and datetime_attended.

And another table storing members info, which is made up by the columns id and name.

Each time a member attends an event, a new row containing the member.id and the current timestamp is inserted into to the attendance table.

I would like to formulate a mysql query to pull an ordered list of the top 7 members who have the longest running absence streaks (the streaks must be currently running streaks -- past absence streaks are ignored), as well as their most recent attendance dates.

After much searching, reading and many query attempts, I've yet to stumble on a working query that's even worth sharing here, as all my attempts fell embarrassingly short of the goal (and they would probably just be confusing and frustrating to read). But the general idea I've been working at is to JOIN the two tables ON member.id with GROUP BY and ORDER BY parameters to sort and extract the single most recent attendance entry per member, then use another ORDER BY and LIMIT to get the 7 longest running streaks from the result.

This is my first question post, so I apologize if this question is lacking in any way.  Thank you.


texts: Delete rows older than x days without locking table

texts:
we have a quite big MS SQL database with millions of rows.
I created a simple script to delete rows older than 1 month, but this seems to lock the table and creates trouble for the application.
The table has an indexed "ID" PK, and also a "date" column which I will use for this task.
What is the best way to do this without causing the lock?
I was considering partitioning, but not sure if its the best way to go.
Thanks in advance.


texts: What happens when a CTE query is interrupted

texts:
I have the following query.

WITH d_rows AS (DELETE FROM t_a RETURNING * )  INSERT INTO t_b SELECT * FROM d_rows;

What will happen if i cancel this query (Ctrl+c in psql)? Will the delete run? Does it run already? Does it run in batches? I see the t_b is growing while the query is running. This is important to know since this is about a lot of data.


texts: Can't connect to Postgres through psql in Windows10

texts:
I am running psql from Windows command prompt but it is showing error.

I have to change listen address but I don't where it is? Where to find listen addresses in PostgreSQL settings?.

    C:\Users\KUMAR>psql -U postgres psql
    could not connect to server: Connection refused (0x0000274D/10061)
    Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432?

When I try to connect, it declines. After going to psql page they are telling to change listen address. The Windows service is running. I configured the default port 5432 during installation.


texts: MySQL running delete trigger on truncate

texts:
I have a table that has an on delete trigger that updates another table.  THe table I'm trying to truncate has 130,000 rows.  I have duplicate schema and data on my dev server as well as the client's deployment stating server. (5,5,49)

On my dev server (5.5.29) when I truncate the table, it's instant, as in by the time I type show full processlist, the truncate is finished.

When I run the same truncate on the staging server I see this in the process list:

| 3377 | root | localhost:46410 | database | Query   |  533 | Sending data                    | DELETE FROM PriceCode44      |
| 3378 | root | localhost:46800 | database | Query   |  521 | Waiting for table metadata lock | truncate table `PriceCode44` |

There are no foreign keys in the database related to this table.

My solution was to drop the table and recreate it, but I'm concerned that at some point the client is going to need to truncate the table, and since the delete trigger is updating a lot of data it takes a long time.

What could be the possibly cause of this, and how can I avoid it?

Adding if it's not clear - the documented truncate behavior is the desired behavior.  I just want to whack the data in the table.

Here is the create table:

CREATE TABLE `InsynchPriceCode44` (
  `PriceCodeRecord` varchar(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `PriceCode` varchar(4) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `ItemCode` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `CustomerPriceLevel` varchar(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `ARDivisionNo` varchar(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `CustomerNo` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `Description` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Method` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `BreakQty1` decimal(9,0) DEFAULT NULL,
  `BreakQty2` decimal(9,0) DEFAULT NULL,
  `BreakQty3` decimal(9,0) DEFAULT NULL,
  `BreakQty4` decimal(9,0) DEFAULT NULL,
  `BreakQty5` decimal(9,0) DEFAULT NULL,
  `DiscountMarkupPriceRate1` decimal(12,3) DEFAULT NULL,
  `DiscountMarkupPriceRate2` decimal(12,3) DEFAULT NULL,
  `DiscountMarkupPriceRate3` decimal(12,3) DEFAULT NULL,
  `DiscountMarkupPriceRate4` decimal(12,3) DEFAULT NULL,
  `DiscountMarkupPriceRate5` decimal(12,3) DEFAULT NULL,
  PRIMARY KEY (`PriceCodeRecord`,`PriceCode`,`ItemCode`,`CustomerPriceLevel`,`ARDivisionNo`,`CustomerNo`),
  KEY `PriceCodeRecord_2` (`PriceCodeRecord`,`PriceCode`,`CustomerPriceLevel`) USING BTREE,
  KEY `PriceCodeRecord_3` (`PriceCodeRecord`,`ItemCode`,`CustomerPriceLevel`) USING BTREE,
  KEY `PriceCodeRecord` (`PriceCodeRecord`,`ItemCode`,`CustomerNo`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT

This is identical to my dev server.


texts: MySQL store procedure use select responce in INSERT...SELECT where in array

texts:
I am pretty sure that this is a simple matter and that it's answered but I seems like not able to find it online, or to phrase it correctly.

I have a table (ven_data) that has a few columns: id_v, lang, more data columns
The unique constraint here is (id_v, lang) pair.

I need to find all ID's that have some languages missing, and then INSERT...SELECT from one of the languages to all the missing languages.

What I do now, I am fetching ALL ID's that has less then the number of languages I need:

SELECT id_v, COUNT( 1 ) as cnt
FROM  `ven_data`
GROUP BY id_v
HAVING cnt <8
ORDER BY cnt ASC

Then I export it to Notepadd++ and remove all unneeded data, make a line of ID's like this: 1,4,5,6

And then make the INSERT...SELECT

INSERT INTO `ven_data`(`id_v`, `lang`, data columns)
SELECT `id_v`, elementInner, data columns
FROM `ven_data` WHERE `id_v` in (1,4,5,6) and `lang` = element
        on duplicate key update lang=elementInner;

Now I have not problem iterating over the languages in the stored procedure but how do I get the result of the first SQL into the id_v in (1,4,5,6) ???


texts: Row size limits in Mysql w.r.t to TEXT and BLOB columns

texts:
There is a well-known limitation in Mysql for row size which 65,535 bytes.
While going through the docs,

I have found that

  BLOB and TEXT columns only contribute 9 to 12 bytes toward the row
  size limit because their contents are stored separately from the rest
  of the row.

I did go through the MySQL documentation but unable to understand when a TEXT column will contribute 9 bytes and when it will contribute 12 bytes. Can anyone explain how to interpret the statement?


texts: How to find the most common phrases in a varchar field

texts:
I have a table which contains support tickets. Each ticket has a title and many of them have common phrases in them (XXX cannot connect to YYY, XXX requires a new YYY, XXX requires upgrading, etc. etc.)

I would like to do some analysis on the data and extract these common phrases along with a count of their usage.

Repeated character sequences would be acceptable, but ideally I would want to split the title into words and identify repeated sequences of words. Here is a simple example:

Title
------------------------------------------------
John cannot connect to SERVER01
The backup service cannot connect to the client
Mary cannot connect her laptop to the network

From this data I would like to be able to extract the following:

Phrase                                             Count
-------------------------------------------------- -------
cannot connect                                     3
cannot connect to                                  2

I have a plan to do this using a c# application and brute force, but can anyone suggest a more elegant solution?


texts: What type of replication do I need to push webdata to offline database for analytics?

texts:
I'm new to replication, as I've been mostly an old school ETL type person.
I have a SQL SERVER database that runs a front end web presence.  There's alot of data there.
I have been tasked to perform analytics on the web database.  However, in order to do so, I must send an extreme number of queries to the database.  This is making the db sluggish.
WHen speaking with another analyst about my problem, they stated that in the past they solved this by creating another instance on another server and set replication so I can use the Replication database and not put the pressure on the web instance.
My question is the type of replication.  When researching types of replication, I see Transactional, Merge, etc....
Is there a specific type of replication that I need for this case?  If I know what I'm hunting for, I can get the steps from there.
Thanks!


texts: Error converting data type nvarchar to float. Oracle Linked Server

texts:
Upon executing the following query

select value, cast(value as float) float_value
from OPENQUERY([ORACLESERVER],
'select 3/5 as value from table')

I am getting the following error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float

.

I can run this same query on another server with the same Windows 2012 R2 OS, same SQL Server 2008 R2 Server and identical Oracle 12.1.0 client version and get back the correct result :

value   float_value
.6  0,6

The linked servers on both servers are identically configured.

The Oracle Server version is

select * from OPENQUERY([ORACLESERVER],'SELECT * FROM V$VERSION')
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE   10.2.0.3.0   Production
TNS for HPUX: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 – Production

Are there any other configuration options I have overlooked in SQL Server and/or the Oracle Client that may be causing these issues.


texts: Does extending datafiles affect performance

texts:
We have an Oracle 10.2.0.3.0 on Windows 2003 Server which is scheduled for upgrade within the next six months.  One tablespace is about 2TB in size with over 250 data files.  Some of these were created with a maximum size of 8GB, some with 16 GB.  This database is heavily used by a number of web services that regularly add large amounts of data daily.

It was suggested to extend the smaller datafiles so they are all 16GB but we are concerned that this will affect performance as this would cause new data that is regularly accessed, transformed and queried next to old data which does not see a lot of access.

Storage is using Netapp on iSCSI virtual disks.

Any thoughts on if putting new data in older datafiles will affect the speed of inserts or selects?


texts: Painless way to create a clustered index on a huge table?

texts:
So we have a customer site that is complaining about some seriously slow performance.  I took one look and it's obvious that the problem is because Somebody Else (grrrr) designed a table holding some 20 million-plus records without a clustered index.

Now I want to create a clustered index on that table - but in my test environment my create index command has been running for an hour and it's still not done.  The customer site is a shop floor that works 24/7, and cannot afford an hour of down time while I create an index.

Is there some less brute-force method of creating the index that will either finish the job quickly, or do it in some smart way that will not totally kill the server's performance while it's busy?

We are using SQL Server Enterprise Edition.


texts:
I have a table that contains columns of Country, City, Location, Description. Due to the size of the description field it's not possible to bind the four fields into a composite key to prevent duplication. I have been trying to prevent duplication by way of a trigger but no matter what I try it rolls back every insert, including inserts of unique data.

IF Exists ( Select      a.*
            From        [dbo].[Table] a
                JOIN    Inserted b
                    on  a.Country = b.Country
                    and a.City = b.City
                    and a.Location = b.Location
                    and ltrim(rtrim(a.Description)) = ltrim(rtrim(b.Description))
            )

    BEGIN
        Raiserror   ('The description that you are trying to load already exists.', 16, 1)
        Rollback
    END

Does anybody have any ideas what is wrong with the above, and what changes I need to make in order to get it to only roll back duplicate inserts?


texts: Getting Database Request Statistics on MySQL

texts:
Is there a possible way to query (or in anyway) get the number of requests (select, insert, update, delete, etc) that a specific schema is doing on a MySQL Database.

The reason behind this is that we would want to monitor and isolate a schema that uses a lot of server resource. Once identified, we would want to group all schema in a single hardware that can handle high consuming schema.

Also, we wanted to have a report showing this statistics to be presented.

MySQL Version : 5.6.16

Thanks in Advance.


texts: How to I get a list of non subscribed users from this database?

texts:
I have 2 tables.

1) subscription_types

 | type |
  'type1'
  'type2'

2) subscriptions

| user | sub_type | subscribed |
  'U1'   'type1'    'Y'
  'U1'   'type2'    'Y'
  'U2'   'type1'    'Y'

From this ERD you can see that user 'U2' does not have entry for subscription 'type2'. I'd like to have a report of users who do not have subscription record.

So far I have:

 select user, count(1)
 from subscriptions
 group by user
 having count(1) != (select count(1) from subscription_types);

Which gives me a list of users that do not have all subscriptions. But not which ones they are missing.

I've tried many different variations of join queries where the subscription_type is null, no luck.

I was hoping the following query would solve it but alas it did not

 select user, sub_type
 from subscriptions
 group by user, sub_type
 having sub_type not in (select type from subscription_types);


texts: What will be bytes occupied if we store 2 bytes value in 8 bytes column?

texts:
There is one column which has the column (Column A) type as bigint(8 bytes), and I am storing the value in smallint(2 bytes). Since I may store bigint value in Column A so defined the data type as bigint.

What will be bytes taken the particular column contains smallint value? 8 bytes or 2 bytes.

Also, there is one column (Column B) which has column type as Varchar(100), and if I trying to store a value with length 50. What will be size occupied by the column? size of varchar(100) or size of varchar(50).

PS: I am using postgres for database.


texts: How to restore everything (including "postgres" role) from pg_dumpall backup

texts:
I have a postgres 9.1 server running and I do backups with

pg_dumpall -c -f /nfs/backup/fulldump.sql

I'm testing restoring the backup to another server with

psql -f /nfs/backup/fulldump.sql

while running as user postgres and I get two errors

psql:/nfs/backup/fulldump.sql:27: ERROR:  current user cannot be dropped
psql:/nfs/backup/fulldump.sql:36: ERROR:  role "postgres" already exists

I understand that these are caused by commands

DROP ROLE postgres;
CREATE ROLE postgres;

in the backup dump.

Is it possible to somehow restore the dump from pg_dumpall -c without having a single error?


texts: Defining password complexity and its action on "grace" state USERS

texts:
I have Oracle USERS linked to external applications that came to "grace" status.
I tried to change the assword time expiration to unlimited but it doesn't apply to those account (considering I guess that the "grace" status is responsible for that)

So, in order to keep the application's configuration the same, I wish to renew the passwords without changing them but the complexity policy doesn't allow it (two special characters or/and two capital letters)

    ORA-20000: password must contain 2 or more special characters
    28003. 00000 -  "password verification for the specified password failed"
    *Cause:    The new password did not meet the necessary complexity
               specifications and the password_verify_function failed
    *Action:   Enter a different password. Contact the DBA to know the rules for
               choosing the new password

For info, I migrated the database from 11c to 18 and those policies were simpler on the previous database..

So, first I am wondering how to edit the complexity policy
And also if it would apply to USERS that are in "grace" status ?

Thank you very much for your help

Have a nice day


texts: Isn't a tab (char(9) ) whitespace in the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3)?

texts:
If there is any sane reason to ignore trailing blanks in varchar comparisons, why doesn't that apply to tab-characters too ?

On SQL Server I get

SELECT
    case when '' = ' ' then 'true' else 'false' end,
    case when '' = CHAR(9) then 'true' else 'false' end

yields

---- -----
true false

Is this a problem of the ANSI specification or a problem of the SQL Server implementation?

Edit:

I do not have access to the text of the specification. I only see it mentioned in this answer. I can't tell if it addresses only blanks or if it refers to whitespace in some vague sense.


texts:
Using MariadB, how can I combine NOT LIKE and LIKE, so that I can search for the string FHD but exclude from the searches if the string H265 is also present?

This didn't work for me:

SELECT  `id` FROM `qualities`.`streams` WHERE quality LIKE '%FHD%' AND NOT LIKE '%H265%';


texts: global unique identifier for 2 tables in relational sql

texts:
There are 2 sub-systems[ suppliers and retailers ] in our online market chain management system . Entry in any of those[ suppliers or retailers ] should give a globally unique ID .There would be listing and filtering of each system based on category , locations , nearest retailers .

FYI : The query for listing retailers or suppliers is already complex which uses lots of joins and unions. So, I don't want another table for handling Unique ID .
There is another option to combine retailers and suppliers entry in 1 table.
But chances are there it may soon run out of entries if I combine retailers and suppliers entry in 1 table

Individual business entities[retailers or suppliers] have there own accounting system ,product management system , cost management system . So its a bit complex system and some of the query operations are already slow.
I have checked the possible solutions but it wouldn't satisfy our needs because of performance reasons and complexity.
What should I do , should I go for NoSQL , would it solve the problem  or Is there any other solution


texts:
I'm just starting studying Postgres and I'm in a situation where, depending how I do the JOIN on my tables, the performance and plan output seems really strange.

Those are the table used with their indexes:

create table escola
(
    pk_codigo integer not null
        constraint pk_escola
            primary key,
    nome varchar(100),
    municipio varchar(150),
    uf char(2),
    cod_municipio integer,
    uf_id integer default 0 not null
        constraint fk_escola_uf_id
            references tb_uf
)
;

create index idx_escola_uf
    on escola (uf)
;

create index idx_escola_uf_id
    on escola (uf_id)
;

create index idx_multi_escola_uf_pk
    on escola (uf, pk_codigo)
;

create table if not exists candidato
(
    pk_numero_inscricao bigint not null
        constraint candidato_pk
            primary key,
    cod_municipio_residencia integer,
    municipio_residencia varchar(150),
    uf_residencia char(2),
    uf_nascimento char(2),
    situacao_conclusao numeric(1),
    ano_concluiu smallint,
    idade smallint,
    sexo char,
    fk_codigo_escola integer
        constraint fk_candidato_codigo_escola
            references escola,
    uf_prova char(2)
)
;

create index if not exists idx_candidato_codigo_escola
    on candidato (fk_codigo_escola)
;

create table tb_uf
(
    uf varchar(2),
    pk_id serial not null
        constraint tb_uf_pkey
            primary key
)
;

create unique index tb_uf_uf_uindex
    on tb_uf (uf)
;

create unique index tb_uf_pk_id_uindex
    on tb_uf (pk_id)
;

And the queries (with plans):

EXPLAIN ANALYZE
SELECT pk_numero_inscricao, pk_codigo
FROM escola e
  JOIN candidato c
    ON c.fk_codigo_escola = e.pk_codigo
WHERE e.uf = 'RJ'
;

Time without EXPLAIN ANALYZE: 916ms
Plan: https://explain.depesz.com/s/M6B

EXPLAIN ANALYZE
SELECT pk_numero_inscricao, pk_codigo
FROM escola AS e
  JOIN candidato AS c
    ON c.fk_codigo_escola = e.pk_codigo
  JOIN tb_uf AS u
    ON e.uf_id = u.pk_id
WHERE u.uf = 'RJ'
;

Time without EXPLAIN ANALYZE: 72ms
Plan: https://explain.depesz.com/s/E3MR

EXPLAIN ANALYZE
SELECT pk_numero_inscricao, pk_codigo
FROM escola AS e
  JOIN candidato AS c
    ON c.fk_codigo_escola = e.pk_codigo
WHERE e.uf_id = 19
;

Time without EXPLAIN ANALYZE: 961ms
Plan: https://explain.depesz.com/s/v67V

The weird thing happening for me is that queries 1 and 3 are slower than query 2, although query 2 has an extra join. Does anybody know what might be causing this?

I noticed that Index Scan on table candidato is a lot slower on queries 1 and 3 also, and that makes no sense for me, since the final result is the same.

Another point is that EXPLAIN ANALYZE is adding a lot of overhead into the queries.

Thanks in advance! If I need to provide any more information, I can edit this post if needed!


texts: Materialized view for the last 24 hour period

texts:
I am collecting spatial point data that has an associated timestamp with it. I want to create a view of this data for the last 24 hour period. Because I am piping this spatial data through ArcGIS server, there are performance problems related to just piping in a straight view (this view can contain easily 100-200k+ points). To make a long story short, to mitigate these performance issues, I have created materialized views of the data. However, I want this data to be updated every 3 - 4 minutes.

My question is, when I add the current_timestamp to the query:

SELECT *
FROM points
WHERE timestamp > current_timestamp - interval '24' hour

...this no longer can be used as a fast refresh. Is there any way to correct that, or will it always be a complete refresh? If shown, what should I be mindful of when it comes to the performance of updating the materialized view (again, this dataset can easily be 100-200k points with 3 - 6 points added every second).


texts: How to insert values into a highly normalized database without excessive duplicate checking?

texts:
The situation:

The lead for our project has decided to use a highly normalized database as our  database design. Meaning that literally every field of a big table is an ID instead of the actual value. His intention is to have no duplicates of any kind, even in places where duplicates don't hurt (peoples first names, that kinda stuff).

This does lead to a problem though: When inserting new data, we need to check every single subtable to see if the value exists (first query), then insert it if it doesn't (second query) or else retrieve the ID, do this for literally every column in the main table (so 30 times or something), and then we can create the object we actually wanted to get. (That's ~60 database hits for creating an object!).

We work in spring, so we use jdbcTemplate to actually build a database connection, and every query is expensive. When we're inserting thousands of new records, or updating them, this actually slows down the database heavily.

This entire process feels completely dirty and wrong to me, and thus I wanted to ask: Is there a better way? Is it possible to have an subquery insert an value if it doesn't exist, or not if it does, and return the actual key in both cases, which gets used immediatly to set the ID in the main table?
Is there an elegant solution to cut down on the number of queries without introducing too much complicated SQL (for team members sakes)?


texts: How do I increase the storage available for a SQL Server instance on AWS RDS?

texts:
The Amazon docs state that you cannot increase the storage allocated to a SQL Server RDS instance:

  Note that you cannot change the storage capacity nor the type of storage for a SQL Server DB instance due to limitations of striped storage attached to a Windows Server environment.

Oh well, so I guess you take a snapshot and restore it to a new instance with more storage... except that seems to be impossible too:

  Amazon RDS does not support changing the storage configuration for a Microsoft SQL Server DB instance when restoring from a DB snapshot.

That quote may refer to changing between general SSD and provisioned I/O, but there's no --allocated-storage parameter for restore-db-instance-from-db-snapshot, so it does seem to be impossible when restoring a snapshot.  For that matter, simply specifying a new number of IOPS gives an error:

  Cannot restore this instance based in Windows OS because the request has a different iops than the backup.

What is the most convenient way to increase the storage space allocated to a SQL Server instance?  Is there nothing better than Database Migration Services in this situation?

Amazon support confirmed that S3 and DMS are the only options for SQL Server.

This question has been raised on SE.  I've opened this question provisionally; if there's a consensus in Meta that we should not duplicate DBA questions which exist elsewhere on SO, I'll delete this one.

2019: Some time since I raised this question, it seems that Amazon lifted some restrictions.  I was able to resize a SQL Server instance without taking any special steps: just edit the config and allow it to reboot.


texts:
I have installed Oracle Database 11gR2 on OS Oracle Linux 7.7
while i try to start server using below command its started

[oracle@localhost dbhome_1]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
http://localhost:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control .... started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/dbhome_1/localhost_DB11G/sysman/log

But i try to display status then its display EM not running

[oracle@localhost dbhome_1]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
http://localhost:1158/em/console/aboutApplication
EM Daemon is not running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/dbhome_1/localhost_DB11G/sysman/log

How to fixed it?


texts: Exclude Generated Objects - SQL Developer

texts:
I am a software developer that supports and develops an application that connects to both Oracle databases and SQL Server databases.

I have created a connection to a database (orcl) using the SYS account.  There are hundreds of system generated tables e.g. access$, aclmv$ etc.  How do I hide all these tables so that I can see the tables I have created.  I have tried applying a filter to the tables (Exclude Generated Objects), but this has not worked.


texts:
I have written a basic stored procedure, which looks up the contact from one table, links with a membership, and inserts the membership plus an original field (from one table) into the new one.

Unfortunately the entity_id insert is always the same. 13226 for all of the records. I've gone through the logic and query step by step. It would seem somewhere the entity_id value isn't being updated, so the same value gets inserted.

DROP PROCEDURE IF EXISTS data_membership_insert;
TRUNCATE test_table;

  DELIMITER ;;

  CREATE PROCEDURE data_membership_insert()
  BEGIN

    -- Declare variables
    -- Must be first, else will fail
    DECLARE DONE INTEGER DEFAULT 0;
    DECLARE CONTACT_ID INT DEFAULT 0;
    DECLARE MEMBERSHIP_ID INT DEFAULT 0;
    DECLARE PRIMARY_KEY INT DEFAULT 0;
    DECLARE NOMINATOR_CONTACT_ID INT DEFAULT 0;
    DECLARE COUNT INT DEFAULT 0;

    DECLARE cursor_i CURSOR FOR (
      SELECT id FROM civicrm_value_membership_3
      WHERE nominated_by_4 IS NOT NULL
      AND entity_id IS NOT NULL
      GROUP BY entity_id
      ORDER BY id
    );

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1;

    SET CONTACT_ID = null;
    SET NOMINATOR_CONTACT_ID = null;
    SET MEMBERSHIP_ID = null;
    SET PRIMARY_KEY = null;
    SET COUNT = null;

    OPEN cursor_i;
      read_loop: LOOP

        FETCH cursor_i INTO PRIMARY_KEY;

      SET CONTACT_ID = null;
      SET NOMINATOR_CONTACT_ID = null;
      SET MEMBERSHIP_ID = null;
      SET COUNT = null;

      IF DONE = 1 THEN
        LEAVE read_loop;
      END IF;

      SELECT entity_id
      FROM civicrm_value_membership_3
      WHERE id = PRIMARY_KEY
      INTO CONTACT_ID;

      SELECT id
      FROM civicrm_membership
      WHERE contact_id = CONTACT_ID
      LIMIT 1
      INTO MEMBERSHIP_ID;

      SELECT nominated_by_4
      FROM civicrm_value_membership_3
      WHERE id = PRIMARY_KEY
      LIMIT 1
      INTO NOMINATOR_CONTACT_ID;

      INSERT INTO test_table ( entity_id, nominated_by_48 )
      VALUES( MEMBERSHIP_ID, NOMINATOR_CONTACT_ID );

      END LOOP read_loop;
    CLOSE cursor_i;
    END;
    ;;

DELIMITER ;

CALL data_membership_insert();
DROP PROCEDURE IF EXISTS data_membership_insert;


texts: How to prevent SQLite from blocking while using R?

texts:
I am doing an infinity loop to keep gathering data from database db, rework it and send it to another database db1

library(DBI)
library(RSQLite)

while(1==1){

  db = dbConnect(SQLite(), dbname="Data.sqlite",synchronous = NULL)
  db1 = dbConnect(SQLite(), dbname="Data2.sqlite",synchronous = NULL)
  db1
  db

Column1=dbGetQuery(db,'select * from Table1')

  #Here is some calculations#

    Result = Column1/10

  #Here calculations are finished and ready to be sent to new database#

dbWriteTable(db1, name="Result", value=Result, row.names=TRUE, append=TRUE)
   dbDisconnect(db1)
   dbDisconnect(db)
}

when I run the code, it executes about 10 loops and then rise the following Error: Error in rsqlite_send_query(conn@ptr, statement) : database is locked

Can someone please help me to solve this problem


texts: Upgrading a big database inside a transaction

texts:
Assuming a big database, where normal upgrade (structural changes and data upgrade) currently takes about 2-3 days (not a issue, is not done during production and about 1-2 times per year.

Currently, a backup is done, and if anything fails, backup is restored and upgrade scripts are checked for reason.
We are looking into the possibility of upgrading the database inside a transaction, rollbacking the changes if anything happens.

Are there any best-practices, reasons, knowledge and/or experience that would say if this is a good idea?

Thank you


texts: Is there any drawback of MIXED replication mode?

texts:
So, recent mysql server allows three types of replication mode: STATEMENT, ROW, MIXED. They all have pros/cons, and I pick the mode according to site characteristics.

When I get a replication error I sometimes need to examine the binlog files using mysqlbinlog, so I tend to use MIXED mode. ROW based replication doesn't let you see it because contents is not very readable. I'm thinking MIXED mode is 'best of both worlds'.

I haven't yet seen a lot of discussion regarding MIXED mode, much less drawbacks of using it. Can someone tell me few reasons to be cautious?


texts: Oracle : Multiple aggregate functions on a table


texts: Understanding "max_wal_size" and "min_wal_size" parameters default values from postgresql.conf file

texts:
Default values are, according to documentation for min_wal_size and max_wal_size parameters:

For max_wal_size: The default is 1 GB
For min_wal_size: The default is 80 MB

Then I look this parameters from my database config:

select name, setting, unit
from pg_settings
where name in ('min_wal_size', 'max_wal_size')

Gives result:

name         |  setting | unit
----------------------------------
max_wal_size | 64       |
min_wal_size | 5        |

I have 2 questions:

1) Why these values doesn't match default values, which are shown in docs? I never changed config settings at all.

2) Why unit column is empty/NULL for these parameters? What means 64 and 5 values in this case? MB? GB? Or what?

Why this is not like  for example  work_mem parameter, when everything is clear:

name         | setting  | unit
----------------------------------
work_mem     | 4096     | kB


texts:
I want to get 00:00:00 of a day.  Should be done without using CONCAT. What options are there?

We can use CURDATE() function in select query but need to display like 2014-05-05 00:00:00. If NOW() will give like 2014-05-05 12:05:37.

I want add default value 00:00:00 with CURDATE() in query.


texts:
I have a little application that executes select queries against a MySQL cluster.

By using 1 thread to read a record the latency is x.

By using 2 threads to read records concurrently the avg latency is ~ 2x.

By using n threads to read records concurrently the avg latency is ~ nx.

Is there a way to optimize that?

I use one mysqld for 3 Data Nodes.


texts:
At my current job, I recently discovered that our MS SQL database servers had no backup devices set up for them.  When I asked other IT members about this, the response was that the servers themselves were being backed up regularly, including the database files, so taking SQL server backups would be redundant.

I'm sure this is a very elementary question, but it got me thinking: why does MS SQL server, as well as other RDBMS systems, have their own backup system?  Most other applications don't have this.  My first thought is that only the RDBMS can perform integrity checks on the database files.

What other reasons are there?  I think we need to change our backup and restore procedures, but I need to make sure I have a solid case for doing so, since it will require additional investments.


texts: PostgreSQL execute a default query when no row are returned

texts:
I need to calculate the average score of a list of userId.

I have a table user_place_score:

(user_id, place_id, score)

Sometimes I do not have the score (no row for (user_id,place_id) in the table) so I need to fetch a "default" score from the table default_place_score:

(place_id, score)

Is is possible in PostgreSQL to do this in one query? For a given list of users and one place_id, select all their scores in the user_place_score and if no row is found then take the score found if the default_place_score table?

Basically I want to avoid doing this kind of stuff:

for (Integer userId : userIds) {
  score = (query to fetch the score for this userId)
  if (score == null)
    defaultScore = (query to fetch the default score for this PlaceId)

  total = total + defaultScore
}

//return the average
return total/numberOfUser


texts: Insert with OUTPUT correlated to sub query table

texts:
I am modifying the structure of a database. The content of several columns of the table FinancialInstitution has to be transferred into the table Person. FinancialInstitution is linked to Person with a foreign key. Each FinancialInstitution needs the Id of its corresponding Person. So, for each new line inserted in Person, the id of this new line (IDENTITY) has to be copied back into the corresponding line of FinancialInstitution.

The obvious way of doing this is an iterative T-SQL code. But I'm interested in knowing if it's possible to do it only with set-based operations.

I imagined the inner-level of such a request would be something like:

INSERT INTO Person (Street1, Number1, City1, State1, PostCode1, CountryId1, WorkDirectPhone1, Fax1, Email1)
OUTPUT inserted.Id, FinancialInstitution.Id
SELECT Id, Street, Number, City, [State], PostCode, CountryId, PhoneNumber, Fax, Email
FROM FinancialInstitution;

Unfortunately, it seems OUTPUT can't correlate that way...


texts: visual studio 2017 vs sql management studio

texts:
I just installed Visual Studio with Data Tools and removed my previously installed Management Studio, due disk constraints, and the transition has not been as easy as I expected.

Most of the time I am programming web and VS2017 is great on it. I used SSMS, though, for developing stored procedures and some power user work, as profiling and ad-hoc queries. The problem is that I don't know where to find the tools I was used to (profiler?!) and the organization of the environment doesn't feel natural for working with my utility scripts on the existing database...

So, is there a way for using VS2017 on this context or should I reinstall SSMS?


texts: Move content between filestream containers

texts:
I have a server running SQL Server 2012 with Multiple Filestream containers on a single table.

I need to split one of the existing filestream containers into two parts by creating a new filestream container and moving the data across.

How can reliably move content between filestream containers?

I have the first container set to not grow any further but how can I move the files to another container without writing to a whole new table? Is there a way to do an UPDATE statement that will cause SQL Server to write the file to disk again? - I have thought of appending an additional byte of data and then removing it afterwards to make SQL Server write the content as new files, but is there a better way?


texts:
In a MySQL database I have these tables:

Table 1: News

news_id int (PK)
category int (FK)
rating tinyint

Table 2: News Detail

news_detail_id int (PK)
news_id int (FK)
language_code char
news_title varchar
news_detail text

Sample data;

News

news_id  |  category  |  rating
--------------------------------
   1     |     1      |    3
   2     |     3      |    4
   3     |     2      |    5
   4     |     1      |    1
   5     |     2      |    5

News Detail

news_detail_id  |  news_id  |  lang_code | news_title | news_details
--------------------------------------------------------------------
      1         |     1     |    EN      | Title1 Eng | Detail 1 Eng
      2         |     1     |    GER     | Ger Titel  | Deutsch detail 1
      3         |     2     |    EN      | Title2 Eng | Detail 2 Eng
      4         |     3     |    EN      | Title3 Eng | Detail 3 Eng
      5         |     4     |    GER     | Ger Titel4 | Deutsch detail 4
      6         |     5     |    GER     | Ger Titel5 | Deutsch detail 5

All news have an English translation. In German language some of the translations are missing. Now when I query all news in German language, I want to get English translated row if German translation is missing.

Expected Output

news_id  |  rating  |  lang_code | news_title | news_details
-------------------------------------------------------------
   1     |    3     |     GER    | Ger Titel  | Deutsch detail 1
   2     |    4     |     EN     | Title2 Eng | Detail 2 Eng
   3     |    5     |     EN     | Title3 Eng | Detail 3 Eng
   4     |    1     |     GER    | Ger Titel4 | Deutsch detail 4
   5     |    5     |     GER    | Ger Titel5 | Deutsch detail 5

Is there a way to accomplish this?


texts:
I have a table which has a particular column of type JSON, where some user's contact information are stored. I can alter the table and add new columns with cached values to increase search performance if needed (this is a new system, which is in the state of designing/at the beginning of implementation).

The top-level keys in the JSON are 'name' (both first and last name as one value), 'address' (a hash containing street, state, zip etc) and 'other' (tel, email, www etc). These are the three categories of information we are storing of customers.

One property of this JSON column is, that I really can't tell what keys will be there (that's why it is a JSON, so it remains flexible in this sense), but I need to search all the values.

My main problem is, that I've been told to implement a search functionality with one single input (like google), which would search in every value, even combined (so if u search 'John Smith Slovakia', it would find customers with the name John Smith who lives in Slovakia').

Another required property is that it has to support partial searches, i.e. if you wan't to find every customer with the last name Smith who live in street named 'Somelongname', then it would be enough to type in 'smith somelong' and it would still find them.

I've looked into full text search and it looked fine, except it did not really support partial searches.

Is there a better, more efficient solution, than searching every input word separately accross all the values using LIKE '%search_token%' and then merging the results for every search token into the final result?

BTW I'm using PostgreSQL.


texts: Do Postgres GIN indexes need to be rebuilt periodically?

texts: Do they get bloated like btree indexes? If so how can the bloat be measured e.g. by an automated reindexing script. I am interested in improving query performance and saving space.


texts: What's the default password for SYSTEM in Amazon Oracle RDS?

texts:
I can only remember setting these parameters and definitely not the password for SYSTEM:

DB Instance Identifier = mydbinstanceidentifier
Master Username = mymasterusername
Master Password = mymasterpassword


texts: Changing SQL Server installation - Will it bring the server down?

texts:
I need to install SSIS on our server, one concern that I have is the database being brought down temporarily since it's mission critical that the server is always up.

Does changing the SQL Server installation disable the server?

If it does bring the database down, is there any means of installing SSIS without touching our main installation? I could always create a fresh install if this is more suitable.


texts: SQL Server: Help in writing a query to get 3 rolling months average for a table

texts:
Query Thus far:

select month
     , year
     , vol
     --, sum(vol) over (order by month,year  ROWS BETWEEN 2 PRECEDING AND current row) sumnetvol,
     , avg(vol) over ( order by  month,year
                       ROWS BETWEEN 2 PRECEDING
                                AND current row) '3monthra'
     , name
     , cnt
     , costx
from (
    SELECT month(convert(datetime, cast(monthnumber as nvarchar(10))+'01')) as [Month]
         , year(convert(datetime,cast(monthnumber as nvarchar(10))+'01')) as [Year]
         , sum(SumOfNetAmount) as vol
         , count(*) as cnt
         , costx
         , name
    FROM [ngl_OTB]
    GROUP BY costx
           , MONTH(convert(datetime,cast(monthnumber as nvarchar(10))+'01'))
           , YEAR(convert(datetime,cast(monthnumber as nvarchar(10))+'01'))
           , name
           , SumOfNetAmount
) s
where costx = '1000.002' and
 name = 'ngl-raw'
order by month,year

I need help on how to recursively set the [costx] and [name] columns to allow 3 months rolling avg of each [costx] and [name] correctly. All attempts provided wrong calculations like 3 months rolling avg was bleeding into other costx and name.
My goal is to group all the data by costx and name and order by year and month, then performing a rolling 3 months avg on each costx using the value of [vol] for that [costx]

Any help will be greatly apppreciated


texts:
I have a calendar table that shows all dates from 01/01/2015 to 12/31/2016, essentially the calendar shows the date and isworkday.  I need to be able to pass a begindate and enddate to a stored procedure and insert all the dates that are in the range in a table.  So for example something like this

Create Procedure [dbo].[DateRangeTest]
(
  @begindate date
  ,@enddate date
)
As

Set @begindate = '01/01/2016'
Set @enddate = '01/31/2016'

Now here is where my lack of knowledge comes to play, how can I then take all the dates in that range that are flagged as workdays from my workcalendar table and insert those specific dates into a temp table?


texts: SQL Server Transaction Log VLFs not being released (Status 2) on log shipped Database

texts:
Have seen a couple of posts similar to my scenario, but nothing that actually resolves my issue.

Scenario:

Routine index rebuild happens, once a week, 2 AM Saturday morning.
Log file grows about 15x the usual size.
Transaction log backup is taken (every 10 minutes, 24 x 7).
FULL backup is taken every day at 3 AM.
VLFs are remaining "active" (status 2) when i look at dbcc loginfo
log_reuse_wait_desc is reporting "LOGBACKUP" in sys.databases
dbcc opentran is reporting no active transactions
@@version: Microsoft SQL Server 2005 - 9.00.5069.00 (X64)   Aug 22 2012 18:02:46   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Problem:

So, my issue is simple, because of hardware restrictions and time parameters, i need to keep transaction log size as small as possible. i believe the reason it is not releasing the space is because the VLFs are active and it thinks it needs to take a LOGBACKUP to release, but after countless log backups, the log_reuse_wait_desc is still reporting awaiting a log backup!

I could change the recovery model to simple, shrink and change back, but this breaks my LSN chain and my Log Shipping implimentation, so its not really a viable solution!

TIA.


texts: mysql ERROR 1205: 1205: Lock wait timeout exceeded; try restarting transaction

texts:
This is a replica of my StackOverflow question

I am trying to update a row on a specific table of my database.

UPDATE `Search`.`credentials` SET `status`='0' WHERE `id`='30';

and i am getting the error

ERROR 1205: 1205: Lock wait timeout exceeded; try restarting transaction
SQL Statement: UPDATE `Search`.`credentials` SET `status`='0' WHERE `id`='30'

I have set the timeout to 200

show variables like 'innodb_lock_wait_timeout';

  +----------------------------------+-------+
  |  Variable_name                              | Value|
  +----------------------------------+-------+
  | innodb_lock_wait_timeout | 50       |
  +----------------------------------+-------+

The database is been used, but not all the time, and not the table I want to update

mysql> show open tables where in_use>0;

  +-----------+---------------------------+--------+--------------------+ |Database| Table

  | In_use | Name_locked |
  +-----------+---------------------------+--------+--------------------+ | db1          | PayInformation
           |2
          |
  0
  | | db1          | KeyMapping
  |      4        |
  0
  | | db1          | NoticeInformation
      |      2
          |
  0
  | | Search  |
  companies
  |      1         |
  0
  | | db1          |
  ContractInformation |      5
          |
  0
  |
  +----------+---------------------------+--------+---------------------+

some times there are more lines concerning the Search database, but none that is using the same table that i need to update.

and from the process list i get

mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where db="afmSearch";

  +----+------+-------------+--------+---------+------+-------+------+ |  ID  |  USER  |  HOST
     |  DB          |
   COMMAND  |  TIME  |  STATE  |
   INFO  |
  +----+------+-------------+--------+---------+------+-------+------+ |  xx  |  me      |  my.ip:44604
   |  Search  |  Sleep
   |        1  |
         |  NULL  |
  +----+------+-------------+--------+---------+------+-------+------+

Any ideas?


texts: What is the different between compression on the PK vs. on the Table?

texts:
Data compression can be set on the table:

CREATE TABLE dbo.SomeTable(
    SomeId [bigint] NOT NULL,
    OtherId [bigint] NOT NULL,
    IsActive [bit] NOT NULL,
 CONSTRAINT [PK_Some] PRIMARY KEY CLUSTERED
 (
    SomeId Desc
 )
) ON SomePartitionScheme(SomeId) WITH (DATA_COMPRESSION=PAGE)

And it can be defined on the Primary key:

CREATE TABLE dbo.SomeTable(
    SomeId [bigint] NOT NULL,
    OtherId [bigint] NOT NULL,
    IsActive [bit] NOT NULL,
 CONSTRAINT [PK_Some] PRIMARY KEY CLUSTERED
 (
    SomeId Desc
 ) WITH (DATA_COMPRESSION=PAGE)
) ON SomePartitionScheme(SomeId)

But if you put it on both then you get this error:

  The DATA_COMPRESSION option was specified more than once for the table, or for at least one of its partitions if the table is partitioned.

Is there any difference to putting it on the PK vs on the table?


texts: xp_cmdshell Date Parameter - Looks for File create Date of last modify date?

texts: While deleting files using xp_cmdshell, does the date column look for file creation date or last modified date?


texts:
I'm developing a website using PHP. I have a MySQL database and it has three columns called name, latitude and longitude. It is as follows.

Name | latitude | longitude
----------------------------
AAAA| 6.9270974 | 79.8612478
BBBB| 7.2667576 | 80.380611

My interface has three text boxes called txtname, txtlatitude, txtlongitude and they return values just like this.

txtname - CCCC
txtlatitude - 6.9271786
txtlongitude - 79.86224300000006

I want to update the “Name” column of the database table which is approximately similar to the latitudes and longitudes values of these text boxes.

(For example, according to the above database table,  I want to replace “AAAA“ with “CCCC”)

Is it possible? Any help would be appreciated.

Thanks.


texts: How do I grant Read Access to FILESTREAM data in a table but not grant SELECT access?

texts:
Problem:

I want users to be able to download data stored in a table that contains a FILESTREAM column.  Each file is typically 200 to 500 MBytes.  I however do not want to grant the user SELECT access (specifically SELECT *) to the table.  I was able to create a stored procedure that provides the Path / Transaction Context.  However if I do not grant select permissions on the table the SqlFileStream() class fails to open due to a permission error.

If I grant the SELECT permission I risk a user performing a SELECT * on the table and watch the server die as it is trying to return gigabytes of information.

I believe I have a solution.
I think I can deny select permissions to the table.  The only reasonable way to retrieve the data from the FileStream table is through the application.  I can use an Application Role which has permission to the table to perform the download.  I will try this out, I was hoping to not have to make this more complicated...


texts: MySQL - Creating an index in table DDL statement or separately

texts:
I'm making the leap from SQL Server to MySQL and I'm trying to understand what the difference would be creating an index as part of the DDL table create statement vs creating a separate DDL statement for the each index you want to create.

The MySQL documentation on create index states

  Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. See Section 13.1.18, “CREATE TABLE Syntax”. This guideline is especially important for InnoDB tables, where the primary key determines the physical layout of rows in the data file. CREATE INDEX enables you to add indexes to existing tables.

Why is it important for InnoDB table to have the index created as part of the create table DDL?  (Update with more details below)

What is the potential issues with an index being created after the table is created?

Update

My question was towards the statement on the MySQL's site about creating the index during the table creation vs creating it after creating the table but before filling it with data.  The statement on MySQL's site makes it seem like there is a benefit, performance or otherwise, when using InnoDB tables that declaring the indexes within the DDL statement for the table is best practice but they don't explain why.

Referencing how I would approach this in SQL Server, partly because of DDL limitations and partly because I like the explicit nature, is that I would make my DDL statement and assign the primary key along with any foreign key constraints.  After that I would assign my clustered index, if it it wasn't the primary key, and secondary indexes (unique and non unique).  But in either approach if I was to create my clustered index with the table DDL or separate the end result would be the same.  Their statement is making it sound like the RDMS will handle the table creation differently depending on the approach even though the result in the end is the same, a primary key and a clustered index.


texts: I am working on a system where you have users, and you have Fields of Study or 'fos' for short.  Each user can have many fos and each fos can have many users.  So we are dealing with a many to many scenario.  Here is the rub, each user can have many fos they act as an instructor for, and can also have many fos they act as a student for.  How is this relationship defined?  Would you have two junction tables one like user_instructor_fos and one like user_student_fos?


texts: Should Data and Indexes be split in separate file groups and files

texts: At what point should we introduce multiple datafiles/file groups? Right now the DB is about 2 TB and a very active ERP. We are seeing massive deadlocks and poor performance. I am wondering, at point point do I know if adding a second Data file or File group is the answer?


texts: Building slowly changing dimension on a Fact/Dimension Star Schema

texts:
I have heard textbook definitions of how to design a star schema regarding what goes in the fact table and what goes in the dimension tables, such as:

  The fact table should contain core information about an object and dimensions should contain information about the facts

(paraphrased)

However, practically in business, I have seen a star schema designed where the fact table contains a surrogate key, a business key, and all single-valued fields of an object, and each dimension stores all the multi-valued fields of an object (hence the word dimension). For example, a person may be the object represented in a fact table. A person has one name, one age, etc., which all make viable facts in a fact table. A person may own multiple cars, each with their own attributes, which would represent a person's car dimension, stored as a dimension table with several columns to describe each car's attribute. In this example, this dimension table also includes a foreign key representing the business key of the corresponding row from the fact table.

So, if we can agree that this may be a suitable design, the problem that I am trying to overcome is how to do SCD type 2 (historical) on a multivalued dimension table. For my fact table full of single facts, it is obvious. I include two extra columns, an effective date and expiration date, and I use the business key to link common records where the most recent record has a NULL expiration date, and all other historical records for the same business key have both an effective and expiration date indicating at what point in time they were the most current record.

How do I use this same concept on a dimension which represents a multivalued list? I essentially would like the same concept where I can (1) identify the current list (in this example, the cars a person owns) and (2) identify what the list was at any given moment in history. Can I just put an effective and expiration date on each dimension value? How then do I differentiate between values added after a certain time? Or deleted values?

But, if we do not agree on this design approach, please tell me what industry standard is so I can do this correctly.


texts:
I have been finding strange error messages on the sql error log:

Bocss: same deadlock taking place every hour – needs investigating

Also lots of recompiles are listed in the error log for other SPIDs as per following examples:

  09/04/2015 14:30:10,spid64,Unknown,A possible infinite recompile was
  detected for SQLHANDLE
  0x0200000059631A288882589E0C54B76404CAE1B97E08D3680000000000000000000000000000000000000000
  PlanHandle
  0x0600040059631A2860A62B654100000001000000000000000000000000000000000000000000000000000000
  starting offset 1038 ending offset 2600.  The last recompile
  reason was 2. 09/04/2015 14:30:10,spid150,Unknown,A possible infinite
  recompile was detected for SQLHANDLE
  0x02000000EF886F018C4E0B163812B8B20150FE8FC7E6A06A0000000000000000000000000000000000000000
  PlanHandle
  0x06000400EF886F01901A816E0600000001000000000000000000000000000000000000000000000000000000
  starting offset 998 ending offset 2520.  The last recompile reason
  was 2. 09/04/2015 14:30:09,spid67,Unknown,A possible infinite
  recompile was detected for SQLHANDLE
  0x0200000057C4C632D9052275CFF2B683B80F29501EE91D730000000000000000000000000000000000000000
  PlanHandle
  0x0600040057C4C63200EAC2BE3000000001000000000000000000000000000000000000000000000000000000
  starting offset 1064 ending offset 2652.  The last recompile
  reason was 2. 09/04/2015 14:30:09,spid163,Unknown,A possible infinite
  recompile was detected for SQLHANDLE
  0x02000000E7C7BF0E5D70DE55759C7842860272AD474D69AB0000000000000000000000000000000000000000
  PlanHandle
  0x06000400E7C7BF0EF0EB68A52C00000001000000000000000000000000000000000000000000000000000000
  starting offset 1028 ending offset 2580.  The last recompile
  reason was 2.

what could have caused this?

Looks like I don't have the plans in cache anymore.

following the advice of this post
http://www.sqlservercentral.com/Forums/Topic1479420-146-1.aspx

  then as a safety measure disabled the full text catalogs, this made
  no difference, so then I fully rolled back the changes (dropped the
  new objects etc). This also made no difference, in the end the only
  thing that seemed to stop it was a restart of the SQL instances, this
  immediately resolved the issue.

this sorted me out as well, however, I am still to find what caused this mess at the first place?


texts:
I am facing with a deadlock between two tables Table1 and Table2.

The deadlock occurs when an sp (sp2) delete from table2 and other sp (sp1) try to update table1.

In table2 the column by which it is deleted is the primary key of table1, however due to a bad design the Foreign Key was never added, that is, that table2 has no references to table1, however when deleting from the table2 generates an exclusive lock of table1. So how the engine knows the reference without FK?

CREATE TABLE [dbo].[Table2] (
    [IdTable1]   INT          NOT NULL,
    [IdTable3] INT          NOT NULL,
    [Sent]         BIT          CONSTRAINT [DF_Table2_Sent] DEFAULT ((0)) NOT NULL,
    [Order]        VARCHAR (50) CONSTRAINT [DF_Table2_NewID] DEFAULT (newid()) NOT NULL,
    [ToExclude]    BIT          CONSTRAINT [DF_Table2_ToExclude] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED ([IdTable1] ASC, [IdTable3] ASC) WITH (FILLFACTOR = 100) ON [FG2]
);

GO
CREATE NONCLUSTERED INDEX [IX_Table2_IdTable1_Sent]
    ON [dbo].[Table2]([IdTable1] ASC, [Sent] ASC, [Order] ASC)
    INCLUDE([IdTable3]) WITH (FILLFACTOR = 100)
    ON [FG2];

Here is the deadlock detail. https://justpaste.it/700ik

Also the tables are on different filegroups (different files)

Why is this?

Also, if I had the FK, why should need check the PK in table1 if I am just deleting from table2?

EDIT
Thanks Josh for the answer.

Now I have SP2 as

CREATE PROCEDURE SP2 @value
AS
IF @value = 1
    UPDATE dbo.Table1.....
else
BEGIN
    EXEC SP3 --This SP update Table1
    DELETE FROM Table2...
END

Now to avoid keep the locking on Table 1 could I add a transaction as follow to release the lock on table 1 on SP3 completition?

CREATE PROCEDURE SP2 @value
AS
IF @value = 1
    UPDATE dbo.Table1.....
else
BEGIN
    BEGIN TRAN T1
       EXEC SP3 --This SP update Table1
    COMMIT TRAN T1
    DELETE FROM Table2...
END

Thanks.


texts: Permanently disable OOM Killer for Postgres

texts:
Following this documentation, I am trying to permanently disable OOM Killer for Postgres 9.6.2 on Ubuntu 14.04.5 LTS.

In /etc/init.d/postgresql, I add:

echo -1000 > /proc/self/oom_score_adj

In /etc/postgresql/9.6/main/environment, I add:

PG_OOM_ADJUST_FILE='/proc/self/oom_score_adj'
PG_OOM_ADJUST_VALUE=0

I then restart the Postgres server, and the settings are applied correctly:

root# pid=`head -1 /var/lib/postgresql/9.6/main/postmaster.pid` && head /proc/$pid/oom*
==> /proc/12760/oom_adj <==
-17

==> /proc/12760/oom_score <==
0

==> /proc/12760/oom_score_adj <==
-1000

But when I restart my machine, the OOM values go back to default. How can I persist these changes?


texts: Can't get SQL Server 2012 RTM LocalDB working on Server 2008/x86/ SQL Server 2008 machine

texts:
I have installed SQL Server 2012 and LocalDB on my development machine, and am able to connect to LocalDB using both the SQL Server Management tool 2012 and from within my winform application using a newly modifed connection string Shown below:

Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\MyDatabase.mdf;Integrated Security=True;Connect Timeout=60

The problem arises when I attempt to port my application onto a Windows 2008 Server which is running SQL Server 2008, (I know I could skip the LocalDB installation and just make use of SQL Server 2008, but I am just testing the installation of my application on this machine.. )

I have installed both SQL Server 2012 LocalDB and updated .net 4.01 to .net 4.02 using the kb article located at  http://support.microsoft.com/kb/2544514

I keep getting getting an error when I attempt to connect to (LocalDB)\v11.0 from both my application and from the SQL Server Management 2008 Tool, I have also manually started up (LocalDB)\v11.0 from cmd->SQLLocalDB -s "v11.0" and then attempted to connect once again using the NP

i.e np:\\.\pipe\LOCALDB#8B2C7490\tsql\query

When I use the named pipe everything connects fine, so I am assuming that as this works then the installation of LocalDB is also correct.

What do you suggest I do to resolve this situation?

I am also concerned as to the proper implementation moving forward with the deployment of my application, am I supposed to now include .Net 4.03 as a prerequisite? If so is there already a clickonce bootstrap manifest available for this or do I need to create one.. so that my app works correctly moving forward, I'm really concerned that LocalDB might break it.. as so far I'm not having much luck..

I guess if all else fails I can go back to using SQL Server Express 2008 as this implementation worked correctly before..

Thanks,


texts: How to convert a unix time to PostgreSQL' Timstamp without timezome?

texts:
I have a PostgreSQL database running on a server which has its time zone set to India's Time Zone (i.e. UTC +5:30)

I have some data in a table which is created like this:

CREATE TABLE "CLOUDDATA"
(
  "CD_Tm_Obs" timestamp without time zone,
  "CD_Avg_Cloud" double precision
)

I want to query the data and get the values for a specific time. My Input will be an Unix Timestamp (i.e. Seconds from 1-Jan 1970)

The only way of converting the unix time to Timestamp I have found is this:
select to_timestamp(TRUNC(CAST(1395036000 AS bigint))). But this create a timestamp with timezone. My data is in timestamp without time zone, so I don't get any results.

How to convert a unix time to PostgreSQL's Timstamp without timezome?


texts: Automatically generate ALTER TABLE to syncronize schema on PostgreSQL

texts:
i Have 2 different database schema in PostgreSQL sql file from pg_dump, is there automated way to generate alter table commands so it would be in sync, without inspecting one by one?

this is the example dump data:

database #1 (the correct one)

CREATE TABLE active_students (
    id integer NOT NULL,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    deleted_at timestamp without time zone,
    logs text DEFAULT ''::text,
    log_count integer DEFAULT 0,
    krs_confirmed_at timestamp without time zone,
    krs_delivered_at timestamp without time zone,
    krs_generated_at timestamp without time zone,
    krs_timestamp character varying(250) DEFAULT ''::character varying,
    krs_revision integer DEFAULT 1,
    khs_delivered_at timestamp without time zone,
    khs_generated_at timestamp without time zone,
    khs_timestamp character varying(250) DEFAULT ''::character varying,
    khs_revision integer DEFAULT 1,
    ips double precision DEFAULT 0.0,
    ipk double precision DEFAULT 0.0,
    sks integer DEFAULT 0,
    sks_total integer DEFAULT 0,
    user_id integer NOT NULL,
    semester_id integer NOT NULL,
    prodi_id integer
);

CREATE INDEX index_active_students_prodi ON active_students USING btree (prodi_id);

ALTER TABLE ONLY student_invoices
    ADD CONSTRAINT student_invoices_active_student_fk FOREIGN KEY (active_student_id) REFERENCES active_students(id);

database #2 (the current database for production)

CREATE TABLE active_students (
    id integer NOT NULL,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    deleted_at timestamp without time zone,
    logs text DEFAULT ''::text,
    log_count integer DEFAULT 0,
    krs_confirmed_at timestamp without time zone,
    khs_delivered_at timestamp without time zone,
    khs_generated_at timestamp without time zone,
    ips double precision DEFAULT 0,
    ipk double precision DEFAULT 0,
    sks integer DEFAULT 0,
    user_id integer NOT NULL,
    semester_id integer NOT NULL,
    sks_total integer DEFAULT 0,
    khs_timestamp character varying(250) DEFAULT ''::character varying,
    krs_delivered_at timestamp without time zone,
    krs_generated_at timestamp without time zone,
    krs_revision integer DEFAULT 1,
    khs_revision integer DEFAULT 1,
    prodi_id integer,
    krs_timestamp character varying(250) DEFAULT ''::character varying
);


texts: Creating 'temporary' memory optimised tables with only read/write roles

texts:
I'm currently trying to get an application to use memory optimised tables instead of temporary tables, but have hit an issue.

The issue is related to permissions, as users of the application are only ever allowed read/write roles.

Currently, the application does the following;

Create temporary table on server
Bulk write data to temporary table
Merge table to main table

(Works fine, as temporary tables can be created by anyone)

What I want the application to do is this;

Create memory optimised table on server
Bulk write data to memory optimised table
Merge (custom merge as MERGE isn't supported) to main table

This fails as the user doesn't have the correct permissions.

Now my question is;

Is there any way I can get this to work, whilst restricting users to read/write roles?

Thanks in advance!


texts: How to login to a remote SQL Server instance via IP and specify the Server name?

texts:
I'm following instructions HERE to add a remote publisher to a Distributor server here. However, when trying to add a publisher via SQL Server Authentication I get the following error:

THIS recommends checking to see the machine name and servername is the same:
SELECT SERVERPROPERTY(N'servername')
SELECT @@SERVERNAME

They are.
And there is a stack overflow question (https://stackoverflow.com/questions/8212032/sql-server-replication-requires-the-actual-server-name-to-make-a-connection-to-t) that recommends adding an additional connection parameter SERVER='XXX'. But this also doesn't work. I get the following error screen:

Both named pipes and TCP connections are enabled on both machines. And I'm able to login via the connection manager when not trying to configure publications.
So I think my question is, how can I specify a servername using SQL Server authentication as if I were logging into the server via a Windows login?


texts: How to group SQL Server files for restoring database

texts:
I have a SQL Server database that is creating full backups once a week, differential backups once a day, and transaction log backups hourly. These are being FTP'd to another box, where I would like an automated system (which I am writing) to pick them up and restore them into a working database.

So basically every hour when a new transaction log file comes in, I want to find the appropriate full backup file, differential backup file, and other log txn files for that day use them to restore the database.

The issues is that this machine will have several weeks of backup files on it so I need a way to group them together appropriately. What is the best way to do this?

When you go into Management Studio and do a database restore, it gives you a listing off all the full, diffs, and logs grouped together so clearly it's storing this grouping data somewhere.

I've seen the RESTORE HEADERONLY command, which seems to provide some information but I'm not 100% sure how to use it.

It looks like you could make the CheckpointLSN  of the full backup to the DatabaseBackupLSN of all the other files, but I'm not totally sure about that.

My log files to have date/time stamps in the name but I would rather not rely on parsing file names to group everything together.

This article does provide some insights but I would prefer some sanctioned documentation from Microsoft before I embark on a solution.


texts: Getting the referenced object from SQL Trace

texts:
I am able to query the default trace or any SQL trace for the event "Audit Add Role Event" to see if anyone has received any instance level access escalation. What I am trying to figure out is how to get which specific user got added to the role. I am using the query below... I tried using the referenced object column but found that it was null.

SELECT TOP 1    TraceFile.[value]
                FROM sys.fn_trace_getinfo(NULL) TraceFile
                WHERE TraceFile.property = 2
---------------------------------------------------------
SELECT  TraceEvent.name AS [EventName],
        Trace.DatabaseName,
        Trace.DatabaseID,
        Trace.NTDomainName,
        Trace.ApplicationName,
        Trace.LoginName,
        Trace.SPID,
        Trace.Duration,
        Trace.StartTime,
        Trace.EndTime
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), (SELECT TOP 1    TraceFile.[value]
            FROM sys.fn_trace_getinfo(NULL) TraceFile
            WHERE TraceFile.property = 2
                                             )), 4) Trace
    JOIN sys.trace_events TraceEvent ON Trace.EventClass = TraceEvent.trace_event_id
WHERE   TraceEvent.name LIKE 'Audit Add Role Event'
ORDER BY Trace.StartTime;


texts: Subtract TotalExpense from BudgetAmount In Budget Table for a specific date

texts:
I have a Budget table which has the following columns:

BudgetId, BudgetDate, BudgetAmount

and a table Expense which has the following columns:

ExpenseID, Date, ExDescrip, Category_fk, ExAMount

How can I subtract daily expense from the budget of a sepecific date?

I am using this query to get the sum of the expenses. This query is ok to give the total expense of that date from budget.

select sum(ExMount) as totalExpense
from Budget b, Expense ex
where b.BudgetDate=ex.Date

Now, how can I subtract totalExpense from the BudgetAmount from the budget table?

CREATE TABLE [dbo].[Budget]
( [BudgetId] [int] IDENTITY(1,1) NOT NULL,
  [BudgetDate] [date] NULL,
  [BudgetAmount] [int] NULL,
  CONSTRAINT [PK_Budget]
    PRIMARY KEY CLUSTERED ( [BudgetId] ASC )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

The script of Expense table is

CREATE TABLE [dbo].[Expense](
    [ExpenseID] [int] IDENTITY(1,1) NOT NULL,
    [Date] [date] NULL,
    [ExDescrip] [varchar](50) NULL,
    [Category_fk] [int] NOT NULL,
    [ExMount] [int] NULL,
    [BudgetFk] [int] NULL,
 CONSTRAINT [PK_Expense] PRIMARY KEY CLUSTERED
(
    [ExpenseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


texts: Oracle XE 11.2.0 data pump expdb works on but impdb doesn't on Windows 10

texts:
New to Oracle DB administration here. I have a fresh localhost installation of Oracle XE 11.2.0 (x64 installation from here) on my computer using Windows 10 64-bit.
I am able to connect to the DB using both DataGrip using SYS as SYSDBA user and I am able to connect using sqlplus command-line tool. I typed an admin password on installation but somehow I am able to connect using sys password sqlplus sys/sys as sysdba (as I said, I am an Oracle DB rookie).
I have to import an exported EXP.DMP file from a colleague using the following command:
impdp.exe 'sys/sys as sysdba' FULL=Y DUMPFILE=exp.dmp REUSE_DATAFILES=Y TABLE_EXISTS_ACTION=REPLACE

Windows starts to complain:

And the following line appears in the command line:
c:\oraclexe\app\oracle\product\11.2.0\server\bin>impdp.exe 'sys/sys as sysdba' FULL=Y DUMPFILE=exp2.dmp REUSE_DATAFILES=Y TABLE_EXISTS_ACTION=REPLACE
Access is denied.

c:\oraclexe\app\oracle\product\11.2.0\server\bin>

Important note: I have tried to export instead to exp2.dmp in order to try another command and it works with no issue:
c:\oraclexe\app\oracle\product\11.2.0\server\bin>expdp.exe 'sys/sys as sysdba' FULL=Y DUMPFILE=exp2.dmp directory=DATA_PUMP_DIR logfile=exp.log

Export: Release 11.2.0.2.0 - Production on Tue Mar 9 14:41:51 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_FULL_01":  "sys/******** AS SYSDBA" FULL=Y DUMPFILE=exp2.dmp directory=DATA_PUMP_DIR logfile=exp.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 161.2 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
... omitted ...
Processing object type DATABASE_EXPORT/AUDIT
. . exported "APEX_040000"."WWV_FLOW_PAGE_PLUGS"         27.16 MB   38892 rows
... omitted ...
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  C:\ORACLEXE\APP\ORACLE\ADMIN\XE\DPDUMP\EXP2.DMP
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 14:42:55

c:\oraclexe\app\oracle\product\11.2.0\server\bin>

How to make the import successful?
Edit: I have noticed something very odd in the C:\oraclexe\app\oracle\product\11.2.0\server\bin folder. I have installed the database engine using setup.exe and everything went smoothly. What's going on? Can I download the file anywhere for 11.2.0 XE without the un/installing again which would take another hour?


texts: Automatic Failover on SQL Server Always On Availability Groups is not triggered when primary replica becomes offline

texts:
I have set up a 2-node Always On Availability Group. Both replicas are in synchronous mode with automatic failover. If I perform a manual failover, everything works as expected: Primary and Secondary servers get switched, there is no data loss and Listener works as expected.

However, if I suddenly shut down the primary server (for testing purposes), the secondary does not switch to be primary. Primary replica gets stuck at "Resolving" status and the listener is not reachable until the primary server is back online.

In short, the availability group is working fine in manual failover, but not in automatic failover. It seems like if the cluster was not aware that one of the nodes is no longer available.

Am I supposed to perform any particular configuration to enable something like "unavailability detection"? I think this is a built-in behavior.

I ran a Cluster Validation, and the following warnings were reported:

The cluster is not configured with a quorum witness. As a best practice, configure a quorum witness to help achieve the highest availability of the cluster. (Should I really change quorum settings through Failover Manager?)
This resource does not have all the nodes of the cluster listed as Possible Owners. The clustered role that this resource is a member of will not be able to start on any node that is not listed as a Possible Owner. (I read that changing this through Failover Manager is not recommended, so I didn't do it)
Node NODE01 is reachable from Node NODE02 by only one pair of network interfaces. It is possible that this network path is a single point of failure for communication within the cluster. Please verify that this single path is highly available, or consider adding additional networks to the cluster. (Is this talking about adding redundancy cards?)


texts:
I would like to use SQL Server @@langid variable to return different data based on current connection language (set in connection string or with SET LANGUAGE). This works perfectly well for built-in languages, but there are just 34 of them and I'd like to make sure this technique is extensible.

As far as I understand, languages are stored in syslanguages table. Is there a way to add custom language to running SQL Server instance?


texts: What is the difference between Logical connection and User connections?

texts:
I have a simple question.

What is the difference between Logical connection and User connection in MS SQL server ?

I am using perfmon to get SQL connection count. In that I found two options, Logical connection and User connection. But no where I am getting the exact differences between these.

So please help me.


texts: How do I create a table whose values are based on queries of same table?

texts:
This is something that interests me, but I can't seem to find an answer as to how to do it.

Basically, if I have a table and I want a column of that table to be based on a query, particularly when that query is based on information that is inside the same table, how do I do that?

I don't have access to my SQL program right now, but I suspect it would look something like this:

{
     CREATE TABLE ABCD as ABCE
     (

          A int(11) default null,
          B int(11) default sum(ABCE.A)
     );
}

or something to that effect.

Am I way off base?


texts: Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table?

texts:
I stumbled upon this question on a Twitter conversation with Lukas Eder.

Although the correct behavior would be to apply the ORDER BY clause on the outermost query, because, here, we are not using DISTINCT, GROUP BY, JOIN or any other WHERE clause in the outermost query, why wouldn't a RDBMS just pass the incoming data as it was sorted by the inner query?

SELECT *
FROM (
    SELECT * FROM table ORDER BY time DESC
) AS t

When running this example on PostgreSQL, at least, you get the same Execution Plan for both the inner query and this derived table example, as well as the same result set.

So, I would assume that the Planner will simply discard the outermost query because it's redundant or simply pass through the results from the inner table.

Does anyone think this might not be the case?


texts: is it possible to except some expect word when using distinct in MySQL 5.7

texts:
I am using this SQL to caculate transin user count :
 select

        sum(transin_num) as transinNum,
        case when count(distinct transin_user_num) -1 < 0 then 0 else count(distinct transin_user_num) -1 end as
        transinUserNum,
        sum(transin_count) as transinCount
        from (

        select
        case when consume_item in('PLATFORM_IN') then ABS(consume_num) else 0 end as transin_num,
        case when consume_item in('PLATFORM_IN') then user_id else -1 end as transin_user_num,
        case when consume_item in('PLATFORM_IN') then 1 else 0 end as transin_count

        from wallet_consume_record
        where created_time < 1595951999999
        and created_time > 1595865600000
        -- and app_id = 0
        and tenant_id = 84
        -- and consume_item = 'PLATFORM_IN'
) a

if the consume type not in PLATFORM_IN, I give -1 and not treated as real user. and using distinct to only calculate once. at last step minus 1 to except the -1 value.
Where is the problem? when the output set have consume item not just PLATFORM_IN, it works fine. But when the consume item only have PLATFORM_IN, it may cause the result 1 diff(not contain -1 but still minus 1).
what should I do to avoid this situation? I am thinking for sometime but still not have a perfect solution.


texts: Is it safe to initially rsync without pg_start_backup, provided you snapshot and rsync afterwards?

texts:
Trying to think of ways to speed up slave creation without master downtime.

The normal way of doing is:

pg_start_backup
rsync database files
pg_stop_backup
rsync wal files and start slave

Since 2 can take a long time over the network, is it safe to:

rsync
pg_start_backup
rsync again
pg_stop_backup
rsync wal files and start slave

?


texts:
I have a table on master site which only allow insert operation. I want to replicate the rows which are inserted recently so, need something which can track the last record replicated into the local site and perform the replication afterwards.

I have tried Oracle materialized view but still confuse that whether I use the Fast Refresh or Complete Refresh. I need all the newly inserted rows replicated in one transaction.

Are there any better approach to do that? Any help would be highly appreciated.

Thanks.


texts: MySQL Exporting a query to text file via Cron Job

texts:
I'm in the beginning stages of hacking together code that syncs two completely different pieces of software/webware. I'm looking for a way to run a SQL query and output the results to a text file. I'd like this information to be updated daily so I'm leaning towards a cron job.

My current line of thought is to just write a script that logs into the database(phpBB) run the query (All users that have X number of posts) and then export that list of users to a text file.

My question/s:

Do I have to worry about putting the database username/password into this cron script?
Is there another more efficient way for me to get this information from the database without logging into MySQL?
Is Cron jobs appropriate for retrieving information from a database?
Is there any MySQL specific cron/scripting issues I need to be aware of while programing this.

Please keep in mind that I will be using linux to code and execute this script and the MySQL server is hosted on a unix machine.


texts: how does SQL Server know to lock view objects?

texts:
Not sure whether this is a more or less appropriate place to ask this question, originally posed at Stack Overflow.

In SQL Server 2008 I have a view V over tables A and B that looks roughly like

create view V as
    select * from A
    union all
    select * from B

Reading from V causes a query to take intent shared locks on the base tables, but also takes an intent shared lock on the view object itself.

It is clear why we need the IS locks on the tables, and we can see that the IS lock on the view prevents concurrent modification to the tables underlying the view. That's fine.

The query plan contains no mention of the view. It's completely compiled out, and the resulting plan in this case is a simple concatenation of rows from the two base tables. Indeed the only mention of the view in the query plan XML is in the statement text.

If you add a second view U over the tables, reading from V does not cause any lock to be taken on U. This rules out that the engine just takes an IS lock on all views over A and B.

How does the database engine know to take a lock on the view?

Is the statement text parsed again?
Is there some other channel of information between the query planner and underlying execution to pass this information? If so, what?

If the latter, the details of the mechanism by which the storage engine knows to lock the view can fairly be considered internal. However the fact that it does this is user-visible, and I would expect it to be documented somewhere.


texts: psql command, how to enforce certificate check?

texts:
I have created a cockroachdb v20.1.0 cluster based on this: https://www.cockroachlabs.com/docs/stable/deploy-cockroachdb-on-premises.html

I have also created a test user like this:

CREATE USER test with password '****';
CREATE DATABASE test;
GRANT ALL ON DATABASE test TO test;
SHOW GRANTS ON DATABASE test;
SHOW GRANTS FOR test;

Then I have created a unix user called test, and tried to connect to this database.

I put this into .pgpass:

localhost:26257:test:test:*****

If I try to connect with psql client, everything seems to be fine:

psql --host=localhost -d test -p 26257

psql (12.2 (Ubuntu 12.2-4), server 9.5.0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.

test=>

The connection is encrypted with TLSv1.0, the authentication succeeds.

However, if I try to connect with cocroachdb sql command, then it fails:

$ cockroach sql --host=localhost --user=test                                                                                130 ↵
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
ERROR: cannot load certificates.
Check your certificate settings, set --certs-dir, or use --insecure for insecure clusters.

problem with CA certificate: not found
Failed running "sql"

It is clearly stated in the cockroachdb docs ( https://www.cockroachlabs.com/docs/stable/authentication.html#client-authentication ) that the cockroachdb client always needs a CA certificate to validate the node's certificate.

In the postgresql documentation ( https://www.postgresql.org/docs/12/libpq-ssl.html ) they say the following:

  By default, PostgreSQL will not perform any verification of the server
  certificate. This means that it is possible to spoof the server
  identity (for example by modifying a DNS record or by taking over the
  server IP address) without the client knowing. In order to prevent
  spoofing, the client must be able to verify the server's identity via
  a chain of trust.

This documentation also tells that when "sslmode is set to verify-full" then libpq will verify the server's certificate.

However, I do not see any command line option (for the psql command!) to turn on certification verification forcedly.

Is there an environment variable or a command line option for psql that will enforce certificate check for the connection?


texts:
I have a Student database having one table EXAM.

id    1  2  3  4  5
marks 7 10  7 10  9

Question is how to give rank like

+------+
| rank |
+------+
|    3 |
|    1 |
|    3 |
|    1 |
|    2 |
+------+

I have used a Query:-

SELECT id, marks, FIND_IN_SET( marks, (
SELECT GROUP_CONCAT( marks
ORDER BY marks DESC )
FROM exam )
) AS rank
FROM exam;

but it gives rank

+------+
| rank |
+------+
|    4 |
|    1 |
|    4 |
|    1 |
|    3 |
+------+

What should I do??