texts: Does Clustered Index on GUID create more fragmentation than Non Clustered Index?

texts:
I have a simple table:

CREATE TABLE [dbo].[UserTestGroups](
    [UserTestGroupId] [int] IDENTITY(1,1) NOT NULL,
    [Token] [nvarchar](100) NOT NULL,
    [TestId] [bigint] NOT NULL,
    [Group] [tinyint] NOT NULL,
    [InsertDate] [datetime] NOT NULL)

The table will have a relatively large number of inserts - initially 10,000 sessions with 8 rows per session for a total of 80,000 daily inserts. We expect that to increase significantly in the near future. Regardless, we try to make our platform as resilient as possible, and not just to be OK with current load.

The table will be used for reporting, which can be considered a secondary requirement.

Since we are using Entity Framework (Microsoft's ORM) all tables that are written to need a logical primary key, so I added an Identity column that I simply ignore. I don't like having composite keys, they tend to make nightmare with ORMs, so unless I absolutely have to, I add another identity column and make it the PK.

All the BI report's queries will be based on the Token, which is a sessionId - the GUID, so I created the clustered index on that token column.

Our DBA, who is a SQL Server MVP, told me that having the clustered index on a GUID column will cause fragmentation and that I should create the clustered index on the IDENTITY column, and create a nonclustered index for the Token column.

I don't understand, won't the nonclustered index have the same fragmentation problem? Why would duplicating the data into a new index be better than using a clustered index?

The token-Guid isn't the PK, the PK is a composition of all the columns together (including the InsertDate).

Currently (for I really don't know what reason) all GUID values are stored as NVARCHAR(100), I don't know what's the historical reason-justification for that, but that is what we have.

 Our DBA is overseas and our communication is a tweet long, so I can't get a proper answer until he comes back


texts: Does "Synchronous-Commit Availability Mode" ensure consistency between Replicas?

texts:
If one refers to the MSDN documentation, on Synchronous-Commit Availability Mode, you may read:

  Under synchronous-commit availability mode (synchronous-commit mode),
  after being joined to an availability group, a secondary database
  catches up to the corresponding primary database and enters the
  SYNCHRONIZED state. The secondary database remains SYNCHRONIZED as
  long as data synchronization continues. This guarantees that every
  transaction that is committed on a given primary database has also
  been committed on the corresponding secondary database. When every
  secondary database on a given secondary replica is synchronized, the
  synchronization-health state of the secondary replica as a whole is
  HEALTHY.

Lets assume I have an three node Availability Group with a synchronized database in a HEALTHY state. All replicas are using synchronous-commit mode.

Additionally assume, I have configured read-only routing so that requests with ApplicationIntent=Read-Only connect to a secondary replica.

If I commit a change via a Read-Write connection then, very quickly, select the changed record via another connection using a ApplicationIntent=Read-Only connection, can I expect consistent results to be returned from both replicas, every time?

EDIT - Further Information supporting the accepted answer.

In the Microsoft Technical Paper "AlwaysOn: Offloading Read-Only Workloads to Secondary Replicas (Sunil Agarwal, July 2012)" The section under heading Data Latency reads (emphasis mine).

  The reporting workload running on the secondary replica will incur
  some data latency, typically a few seconds to minutes depending upon
  the primary workload and the network latency. The data latency exists
  even if you have configured the secondary replica to synchronous mode.
  While it is true that a synchronous replica helps guarantee no data
  loss in ideal conditions (that is, RPO = 0) by hardening the
  transaction log records of a committed transaction before sending an
  ACK to the primary, it does not guarantee that the REDO thread on
  secondary replica has indeed applied the associated log records to
  database pages. So there is some data latency. You may wonder if this
  data latency is more likely when you have configured the secondary
  replica in asynchronous mode. This is a more difficult question to
  answer. If the network between the primary replica and the secondary
  replica is not able to keep up with the transaction log traffic (that
  is, if there is not enough bandwidth), the asynchronous replica can
  fall further behind, leading to higher data latency. In the case of
  synchronous replica, the insufficient network bandwidth does not cause
  higher data latency on the secondary but it can slow down the
  transaction response time and throughput for the primary workload.

  If your reporting workload cannot tolerate any data latency, you must run
  it on the primary replica. The good news is that generally most
  reporting workloads can tolerate some data latency and therefore can
  safely be migrated to secondary replica.

Whilst the breadth of Microsoft documentation is not contradictory I feel it could be more explicit. "Synchronous" does not imply Atomicity and Consistency, as used in the ACID acronym.


texts:
I'm planning to go for a 3-node active-active MySQL cluster which will not have more 100 databases. Many experts here have already told there are issues with MySQL 5.6 and 5.7 where circular replication happens which is very vulnerable due to increase in failures, in what way Percona XtraDB provides edge to MySQL cluster? Percona doesn't do the circular way? Just trying to understand. The application which i'm going to use is not very much resource intenseful.
What is the best option for 3 node active-active cluster?


texts: How to load data from another sql file into a main sql file? - MYSQL

texts:
I am trying to process the scripts dbcreate_f.sql and dbload_f.sql to create and to load data into a script solution6.sql.
I am doing this in MYSQL command line.
In solution6.sql I have two statements:
source dbcreate_f.sql;
source dbload_f.sql;

These two files contain data to create tables and insert data into those tables. These two files are perfectly fine - no errors.
The problem I have though is when I run the actual solution6.sql script in MYSQL command line.
My steps are as shown:
tee solution6.rpt;
source solution6.sql;

However, when the script processes in the command line I receive this error message everytime data is attempting to be inserted into each tabe:
ERROR 1146 (42S02): Table 'bh576.AIRLINE' doesn't exist

I have run both the dbcreate_f.sql and dbload_f.sql script in mysql workshop and they run perfectly. The  problem only occurs in the command line interface.


texts: Postgres Transaction ID Wraparound and Autovacuum

texts:
I have the postgres (9.3) logs showing

  database xxx must be vacuumed within 3017422 transactions

My database age is as shown below

 datname   |    age
 ----------+------------
 template1 | 1642961202
 template0 | 1642946792
 postgres  | 1692961164
 xxx       | 2144467989

Note :

We know the exact table which has aged, it is an audit log table.
We also do not mind losing all the data in that table, if deleting or dropping that table could help

The Problem:

Autovacuum is always running (as soon as we start postgres autovacuum starts off). Therefore, we cannot run Vacuum Freeze on the table - This is blocked on autovacuum.

datid            | 16384
datname          | xxx
pid              | 11587
usesysid         | 10
usename          | postgres
application_name |
client_addr      |
client_hostname  |
client_port      |
backend_start    | 2018-01-27 11:28:27.288297+05:30
xact_start       | 2018-01-27 11:28:27.313791+05:30
query_start      | 2018-01-27 11:28:27.313791+05:30
state_change     | 2018-01-27 11:28:27.313795+05:30
waiting          | f
state            | active
query            | autovacuum: VACUUM myschema.mytable (to prevent wraparound)

Also, the size of the table in question and its index is unusually high compared to another table which has same structure and content.

What is the best way to bail out before the transaction wraparound
limit runs out?
Is it OK to drop the table - will it cause any inconsistency with
respect to transaction ID?
Is it OK delete records from the table (2.5 million) with only 3 million
transactions left before the shutdown? Will it just consume all
remaining transactions?


texts:
I'm trying to build off of this answer How do I run a large script with many inserts without running out of memory?

and break up a query into smaller ones with BEGIN TRANSACTION and then GO's

BEGIN TRANSACTION;

MERGE ghcnd.dbo.us_APCP as target
using
(values
  ('US1CASN0123','2018-03-22','--N','251'),
  ('US1KSGO0013','2018-03-22','--N','41'),
  ('US1WYFM0039','2018-03-22','--N','0'),
  ('US1SCCF0008','2018-03-22','--N','10'))
as source(cell,[date],valueFlag,[value])
on target.cell = source.cell
AND target.date = source.date
when matched then
    update
    set valueFlag = source.valueFlag ,
        value = source.value
when not matched then
    insert (cell,[date],valueFlag,[value])
    values (cell,[date],valueFlag,[value])

COMMIT TRANSACTION; GO

However I get this error

  Msg 102, Level 15, State 1, Line 3
  Incorrect syntax near ')'.
  Msg 102, Level 15, State 1, Line 3
  Incorrect syntax near 'GO'.


texts: want to use logical replication with a postgresql10 cluster (that is not using logical replication)

texts:
We presently have an active/standby postgresql 10 cluster (based on PAF/clusterlabs). Anyways, the servers are kept in sync using:
wal_level = replica
This is fine and working.
However, we would like to replicate a single database from the cluster to a standalone instance (for analytics), for this we figured a logical replication of the single database would do nicely, however we then realized we would need change the replication type on the cluster (which would essentially break it)
wal_level = logical
Is the above assumption correct (cant mix "replica and logical")? Are there any other suggestion on how to achieve the same result (replicating a single database out of the cluster with many databases)
Thanks for any feedback/suggestions


texts:
I know the contents of @message_body are:

<inserted DocNo="xxxx99999"/>

I want to put 'DocNo' into a variable I created @DocNo and have tried various this such as:

SET @DocNo = (SELECT ref.value('@DocNo', 'nvarchar(255)')
              FROM @message_body.nodes('//Sample_Message') T(ref))

with no luck. I don't have much experience parsing XML with SQL. Any suggestions?


texts: How to avoid repetition of columns of same type in database

texts:
I am building a database that will save entities such as: orders, cart, customers etc.

All these tables share some common attributes such as: a date, the user agent that was used to create the new element and the ip address of the client (I am tracking the actions of the user).

Would it be considered "ok" to create a single separate table that will store these common attributes instead of repeating adding it in all the tables that require it (it would be the same field types and specifications for these common attributes):

log_id | log_date_added | user_agent_id | ipaddress

Then the field log_id would be added in the tables orders, carts, customers etc.

My first worry is that I will have to make a JOIN operation on the log table to get the date of any order creation, cart creation, or customer creation.

My second worry is that the more the log table gets bigger, the more it will impact on performances when retrieving the date, user agent, ipaddress for any entity that requires it.

In the same time, I feel it would be very redundant to add the fields date_added, user_agent_id, ip_address in all the tables that need those information.

Any best practice recomendation on that matter is more than welcome, thank you


texts: Can a database be created with no user and/or owner?

texts:
Maybe this is a good or a bad question but in my Google research I wasn't able to find out a good answer (maybe because I suck at performing a good search). In a few words:

can a DB be created without a user and/or owner?

I am trying to answer a question on Stack Overflow and the user has shared it DB configuration and something caught my attention:

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = '';
$db['default']['password'] = '';
$db['default']['database'] = 'gotche';
$db['default']['dbdriver'] = 'mysqli';

The database does not have a username!

Regardless the DB used - which is in scenario is MySQL (don't know the version) - is this possible?

I don't think the username and password were removed for security reasons: It would be better to change it to something like ***** - in that case everyone would know there is a username/password but I can ask the user if that's the reason.


texts: Are there design patterns/best practices in writing dynamic SQL in stored procedures?

texts:
I'm an experienced programmer but I'm new to SQL/databases so please bare with me :).

One of my current tasks involve editing (and refactoring, if needed) a dynamic generated SQL statement in a stored procedure. Are there any "best practices" or "design patterns" that I can follow for writing stored procedures in general or better yet, stored procedures that generate long (300 lines) dynamic sql statements?


texts: We have several MySQL backup files of a database in *.SQL format and we want to restore the database by using these files. However, as the sql files contains "DROP TABLE" definitions, running one script cause to remove the data created by the previous script. In addition to these sql files, we have also some other backup folders containing *.FRM and *.MYD files. In all of two cases I think these backups are Differential or Incremental and we have to merge/combine all of them. If so,  how can we combine them without losing data? As I have no experience on MySQL, could you explain a way by using MySQL Workbench if it is possible? Thanks in advance.


texts: Lock Master Log Position in Master-Master replication

texts:
I have two servers connected via private IPs. Replication is partially working, however the problem I'm running into is when I change the MASTER_LOG_POS on Server B using Server A's SHOW MASTER STATUS; command, Server A changes the log position immediately after.

Is there any way to stop MySQL from changing this position everytime? I just want them to sit still!

Below is what Server A's master status is currently. It'll increase (seemingly randomly) to something like 1583 if I try to apply that log position on Server B.

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1451 | db1,db2,db3  |                  |                   |
+------------------+----------+--------------+------------------+-------------------+


texts:
DBCC CHECKDB is failing on a particular database because of

Could not allocate space for object 'dbo.SORT temporary run storage:  143479689576448' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Tempdb is currently 5GB. The database in question is 56GB. The biggest table in that db is 15GB.

How big shall I make tempdb?

Thanks


texts: How to match all words of one column against words in another column

texts:
I have two tables.

Table A:

Company_name
-------------------------------
Tata consultancy
Infosys tech
Atm service
Air force firm

Table B:

Company1_name                    |  Id
--------------------------------   -----
Atm and service.                 |   1
Honey tech.                      |   2
Tata & consultancy.              |   3
Graft soft.                      |   4
Atm, service.                    |   5

I want this result:

Id
----
1
3
5

Description:

  For each Company_name in table A, find a row in table B whose company1_name value has all the words from table A's value, and return the matching row's Id.

How to design a query for this in MySQL?


texts: Need FLOAT datatype with 2 digits after decimal point when importing from a CSV file

texts:
I am inporting a CSV file into SQL Server using the import/export wizard. The data in the CSV file looks like this:

...0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,164051.00,...

All these values are dollar amounts.
Since this is packaged finance software (JD Edwards 9.2 to be more precise), I have to live with the data type for all columns being FLOAT and no, I can't change it.

After importing using the wizard, the table has 0 and 164051 respectively in those columns.

When running the pre-packaged JDE finance report, it contains 0 and 1640.51 as the values, i.e. it interprets the last two digits as "cents" and there is nothing I can do about it. Not a problem if the value is 0, but an issue for everything else.

So my only option would be to add two trailing zeroes to the column values after the import. Is there any way this can be done?

If the dollar amount is 16578.98 in the CSV file, it gets imported fine and shows up correctly in the report. The problem is only with even dollar amounts or amounts like 5.80 or 6.10.

Any ideas?


texts:
I have problems with queries in MySQL.

Problem 1:

Query:

SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA="mysql" and TABLE_NAME="servers"

MySQL says:

  #1054 - Unknown column 'mysql' in 'where clause'

Problem 2:

Query:

desc servers; // command no work

Can someone help me?


texts: postgres window functions: using CASE to partition by age group

texts:
I am using postgres window functions to get a list of users taking part in a competition and their corresponding ranks based on a number of columns, all good so far....now I need to get rank based on 'age group' which are a number of pre-defined categories (eg. 0-15 years, 15+ years etc...), where age is calculated based on a timestamp column 'dob'.

I am pretty sure I can do this using CASE statements but I am unable to get the query structured correctly, I have written the query below which defines the 'age groups' in a separate FROM subquery so I can reference it in the WINDOW definition, but this doesn't work as the two FROM's are independent

SELECT
cu.* as compUser,
cus.time_in_seconds as timeInSeconds,
rank() OVER allTimes as overallRank,
u.gender as gender,
rank() OVER genderTimes as genderRank,
ageGroup as ageGroup,
rank() OVER ageGroupTimes as ageGroupRank,
FROM
competition_users cu,
(SELECT CASE WHEN usr.dob>'2000-01-01' AND usr.dob<now() THEN '0-15' ELSE '15+' END FROM users usr WHERE usr.user_id = cu.user_id) ageGroup
LEFT JOIN users u ON cu.user_id = u.user_id
FULL JOIN current_competition_sessions ccs ON cu.competition_user_id = ccs.competition_user_id
WHERE cu.left_competition = false
AND cu.competition_id = :compId
WINDOW
allTimes AS (PARTITION BY cu.competition_id ORDER BY cus.time_in_seconds ASC),
genderTimes AS (PARTITION BY u.gender ORDER BY cus.time_in_seconds ASC),
ageGroupTimes AS (PARTITION BY ageGroup ORDER BY cus.time_in_seconds ASC)

(above produces: ERROR:  invalid reference to FROM-clause entry for table "cu"...HINT:  There is an entry for table "cu", but it cannot be referenced from this part of the query.)

Can someone point me in the right direction? (sample query above is simplified, there are many age categories)


texts: Get The Most Recent Record In A Table By Date And User

texts:
I have a table as below:

SELECT [EffectiveDate]
      ,[Rate]
      [ModUser]
      FROM [Vision]

there are many rows with the ModUsers name. I need to provide the ModUsers name and get the latest rates.

How do I do that?


texts: Struggling to create an efficient index for this query

texts:
I am struggling to create an index for a query and unfortunately I cannot change the query at all as it part of an ERP system. The problem is this query has over 1m reads and sometimes a duration of 10s

I Have tried using just the predicate, the seek predicate and a combination of both in different orders to no success.

Here is the query that cannot be changed:

SELECT  TOP (@0) "timestamp","Link ID","Record ID","URL1","URL2","URL3","URL4","Description","Type","Created","User ID","Company","Notify","To User ID"
FROM "Database".dbo."Record Link" WITH(READUNCOMMITTED)
WHERE ("Link ID">@1 AND (("Company"=@2 OR "Company"=@3) AND "Notify"=@4 AND ("To User ID" COLLATE Latin1_General_100_CI_AI LIKE @5 OR "To User ID" COLLATE Latin1_General_100_CI_AI LIKE @6)))
ORDER BY "Link ID" ASC OPTION(OPTIMIZE for UNKNOWN, FAST 50)

Here is the script for the table if it helps:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Record Link](
    [timestamp] [timestamp] NOT NULL,
    [Link ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Record ID] [varbinary](448) NOT NULL,
    [URL1] [nvarchar](250) NOT NULL,
    [URL2] [nvarchar](250) NOT NULL,
    [URL3] [nvarchar](250) NOT NULL,
    [URL4] [nvarchar](250) NOT NULL,
    [Description] [nvarchar](250) NOT NULL,
    [Type] [int] NOT NULL,
    [Note] [image] NULL,
    [Created] [datetime] NOT NULL,
    [User ID] [nvarchar](132) NOT NULL,
    [Company] [nvarchar](30) NOT NULL,
    [Notify] [tinyint] NOT NULL,
    [To User ID] [nvarchar](132) NOT NULL,
 CONSTRAINT [Record Link$0] PRIMARY KEY CLUSTERED
(
    [Link ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Here is the stats for the time and IO

  SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 4 ms.

(0 rows affected)
Table 'Record Link'. Scan count 1, logical reads 1018402, physical reads 3, read-ahead reads 1018391, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

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

Here is the execution plan:

I would greatly appreciate it if someone could help me create an efficient index for this.

Here is a link to the execution plan:
https://www.brentozar.com/pastetheplan/?id=BkTxCbdN4


texts: Why is this SSRS Report rounding a BIGINT Value to the nearest Thousandth when exporting to Excel?

texts:
I have a SQL Server Reporting Services (SSRS) 2012 report that is populated via stored procedure (SP).  The SP returns various fields, one of which is a BIGINT datatype.  When I execute the SSRS report directly the value is properly displayed, but when I export the output to Excel, it gets rounded to the nearest thousandth.

For instance, this is an example of what I'm seeing.

The proper value: 480758225903628660

The value shown in the Excel File: 480758225903629000

It's rounding 8660 to 9000.  Truly frustrating, and Microsoft's documentation mentions nothing about this behavior.  I've also dug everywhere on the report that I can think of that may affect this, such as the Text Box Properties:

...the Placeholder Properties:

... and the Value of the field itself:

I can "fix" it by converting the field to a string by wrapping the field value in a CStr() or .ToString() function, but that also treats the output as a string and not a number.  Is there a way to export this to Excel so that the rounding doesn't occur but still keeps this value numeric?


texts: How can I assign different random values to each row in a SELECT statement?

texts:
Please look at this code:

create table #t1(
  id int identity (1,1),
  val varchar(10)
);

insert into #t1 values ('a');
insert into #t1 values ('b');
insert into #t1 values ('c');
insert into #t1 values ('d');

Now, whenever you execute this

select *,
    ( select top 1 val from #t1 order by NEWID()) rnd
from #t1 order by 1;

you will get a result with where all rows have the same random value. e.g.

id          val        rnd
----------- ---------- ----------
1           a          b
2           b          b
3           c          b
4           d          b

I know a way using a cursor to loop throw the rows and get different random values, but that is not performant.

A clever solution to this is

select t1.id, t1.val, t2.val
from #t1 t1
    join (select *, ROW_NUMBER() over( order by NEWID()) lfd from #t1) as t2 on  t1.id = t2.lfd

But I simplified the query. The real query looks more like

select *,
    ( select top 1 val from t2 where t2.x <> t1.y order by NEWID()) rnd
from t1 order by 1;

and the simple solution doesn't fit. I'm looking for a way to force repeated evaluation of

( select top 1 val from #t1 order by NEWID()) rnd

without the use of cursors.

Edit:
Wanted output:

perhaps 1 call

id          val        rnd
----------- ---------- ----------
1           a          c
2           b          c
3           c          b
4           d          a

and a second call

id          val        rnd
----------- ---------- ----------
1           a          a
2           b          d
3           c          d
4           d          b

The value for each row just should be a random value independent from the other rows

Here is the cursor version of the code:

CREATE TABLE #res ( id INT, val VARCHAR(10), rnd VARCHAR(10));

DECLARE @id INT
DECLARE @val VARCHAR(10)
DECLARE c CURSOR FOR
SELECT id, val
FROM #t1
OPEN c
FETCH NEXT FROM c INTO @id, @val
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #res
    SELECT @id, @val, ( SELECT TOP 1 val FROM #t1 ORDER BY NEWID()) rnd
    FETCH NEXT FROM c INTO @id, @val
END
CLOSE c
DEALLOCATE c

SELECT * FROM #res


texts: SQL to choose a price between two points in time

texts:
Given a time-series of datetime price changes and another disparate time-series of N events, how do I query the last known price at the time of each event without running N queries? Essentially, I need to write some kind of CROSS-JOIN BETWEEN query that matches the most recent known price.

For example, price change history:

Changed At (time) | Price (money)
                1 |    10
                5 |    20
               10 |    30
               20 |    40

Events:

Event Time | Nearest Matched Price (from above)
         0 | n/a
         3 | 10
         6 | 20
        10 | 30
        15 | 30

The real-world use-case is that I tracked a time series of stock movements with costs and prices, but neglected to store costs alongside my invoice lines. My stock movements usually occurred right before the time of invoice.

I have a similar calendaring report query that reports sales across calendar days/weeks/months, but only works because the CROSS JOIN condition (> now && < next-day) will only ever return one row. Pardon the noisy query (will clean it up):

SELECT [t3].[FirstDateOfWeek] AS [Date], [t3].[value] AS [Total], [t3].[value2] AS [Count]
FROM (
    SELECT SUM([t0].[Total]) AS [value], COUNT(*) AS [value2], [t2].[FirstDateOfWeek]
    FROM [dbo].[vw_Invoices] AS [t0]
    LEFT OUTER JOIN [dbo].[Cases] AS [t1] ON ([t1].[CaseId]) = [t0].[CaseId]
    CROSS JOIN [dbo].[Calendar] AS [t2]
    WHERE ([t0].[Date] >= [t2].[CalendarDate]) AND ([t0].[Date] < [t2].[NextDayDateTime]) AND ([t0].[Date] >= @p0) AND ([t0].[Date] < @p1) AND (NOT ([t0].[IsVoided] = 1))
    GROUP BY [t2].[FirstDateOfWeek]
    ) AS [t3]
ORDER BY [t3].[FirstDateOfWeek]

Is this even possible in SQL, perhaps with a "running total" query grouped by extracted hour?


texts: query for each row from another arbitrary query, and union results

texts:
I'm trying to build a query that will take another query. Then, perform another query based on those results. Union the results so that there are no duplicates. All on the same table. I've seen some similar posts here and here, but the "input" query could be almost anything and the number of columns may change.

Here's a sample dataset:

  num  |  sub  |                      valid_time                      | col1 | col2  |  ...
-------+-------+------------------------------------------------------+------+-------+------
   1   |   1   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   X  |  ...  |  ...
   1   |   1   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   Y  |  ...  |  ...
   1   |   2   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   Z  |  ...  |  ...
   2   |   3   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   X  |  ...  |  ...
   2   |   4   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   Y  |  ...  |  ...
   2   |   3   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   Z  |  ...  |  ...

Based on some of the examples I was seeing with JOINs, a subset of columns were typically picked out.  I'm not sure how to return all columns with a JOIN. The number of columns may change over time, and all the columns need to be returned.  I would rather not have to update this query if a new column is added.

This does not work (I think because I need to JOIN somehow), but it shows where my head is at.

with cte as (
    select *
    from table
    where col1 = 'X' and
    now() <@ valid_time
)
select * from cte
union
select * from table
    where table.num = cte.num and
    table.sub = cte.sub and
    now() <@ valid_time;

I mentioned earlier that the "input" query could be almost anything.  I plan to templatized similar to:

with cte as (%(query)s)
select * from cte
union
select * from table
    where table.num = cte.num and
    table.sub = cte.sub and
    %(query_time_part)s;

Finally based on the example query, I would expect something like so:

  num  |  sub  |                      valid_time                      | col1 | col2  |  ...
-------+-------+------------------------------------------------------+------+-------+------
   1   |   1   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   X  |  ...  |  ...
   1   |   1   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   Y  |  ...  |  ...
   2   |   3   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   X  |  ...  |  ...
   2   |   3   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   Z  |  ...  |  ...


texts:
I'm currently writing to a datetime XML field from MySQL data. The MySQL field is populated with current_timestamp() but this only outputs - 2014-02-18 15:34:02 whereas I need something like 2014-02-14T13:59:33. Can this be done without hacking the 'T' in there?

Thanks, Ste


texts: TradeDate import tinytext how to show Date Format 'yyyymmdd' of 20210111 or 2021-01-11


texts:
Im trying to have the City and Postal Code depend on eachother, but i get this error time and time again.
MySQL Error1822 failed to add the foreign key constraint. Missing index for constraint 'Student_address_ibfk_1' in the referenced table 'Student_Information'
################
CREATE TABLE IF NOT EXISTS Student_Information(

    Attendance_ID INT NOT NULL AUTO_INCREMENT,
    Student_ID INT NOT NULL,
    Student_name VARCHAR(99) NOT NULL,
    Phone_number INT NOT NULL,
    Email VARCHAR(99) NOT NULL,
    City VARCHAR(99) NOT NULL,

    PRIMARY KEY(Attendance_ID, Student_ID, City)

);

CREATE TABLE IF NOT EXISTS Post_Address (

    Student_ID INT NOT NULL,
    Street_Address VARCHAR(99) NOT NULL,
    Postal_Code INT NOT NULL,
    City VARCHAR(99) NOT NULL,

    PRIMARY KEY(Student_ID, City)

);

CREATE TABLE IF NOT EXISTS Attendance(

    Attendance_ID INT NOT NULL AUTO_INCREMENT,
    4_jan INT NOT NULL,
    6_feb INT NOT NULL,
    7_mar INT NOT NULL,
    18_apr INT NOT NULL,
    20_may INT NOT NULL,
    21_jun INT NOT NULL,
    10_jul INT NOT NULL,
    30_aug INT NOT NULL,
    16_oct INT NOT NULL,
    12_nov INT NOT NULL,
    15_des INT NOT NULL,

    PRIMARY KEY(Attendance_ID)
);


texts: Is there a slave configuration that stops replication of mysql.mysql and grants?

texts:
I have a mariadb slave that cannot have the same permissions and users as the the mysql master. Is there a way to configure the slave so that it does not replicate changes to the mysql system tables and does not try to run GRANT statements?

I am currently using replicate_wild_do_table=mydb.%, and I tried using replicate_wild_ignore_table=mysql.% but that did not seem to ignore the GRANT statements.

I noticed replicate_wild_do_table=mydb.% seems to be sufficient on a mysql slave to not replicate changes to the mysql system table, but it is not sufficient on a mariadb slave.


texts: pg_relation_size does not show any difference after VACUUM ANALYZE

texts:
I wrote a script to backup a postgresql using COPY and then delete those records. I have used pg_relation_size before and after delete.

pg_relation_size
DELETE
VACUUM ANALYZE
pg_relation_size

But both pg_relation_size shows same results. Does this mean VACUUM ANALYZE was not helpful?


texts:
Some background

I had an issue with my replica set the last 24 hours, where the members could not connect to each other, and I got errors such as; could not connect to .... the machine actively refused it; read error; connect error etc.

Things I did to try and resolve it:

I rebooted my PC
I restarted the servers a bunch of times

But nothing seemed to work. I could connect to each member via the shell, and each member status was SECONDARY.

Why I did this

On one of my members a kept getting a message that there was a problem or failure with my shutdown, and that there was some issue with the mongod.lock file (I can't remember the exact message...).

My resolution

Finally, I deleted the mongod.lock file in each member directory, boot up the members, and a connection was established and a vote took place.

My Question

Was this a wise move? What would be the preferred method?

Thank you for your input and help!


texts: I have a Windows server running MongoDB. My database has big indexes and all RAM is usually busy. As I know, it can be a cause of Windows swapping and performance issues. Does it make sense to limit Mongo service RAM (e.g. use only 80%)?


texts: Where does each MySQL data file type belong?

texts:
I'm in the process of transferring a database from one server to another.
I'm moving the data files directly, instead of using a SQL dump. To do this, I:

ensured that both servers use the same MySQL version
set innodb_fast_shutdown = 0
stopped the MySQL server
rsynced the data to the new server

I'm almost ready to start the new server, but I have a doubt. The old server uses the same data dir /var/lib/mysql for data, binlogs, etc., whereas the new server uses 3 different dirs for data, binlogs and tmp:
datadir=/mysql/data
tmpdir=/mysql/tmp
log-bin=/mysql/binlog/mysql-bin

The question is: where should each of the files go?
My reasoning is:

/mysql/tmp: there should not be any temporary files left after a slow (or even a normal?) shutdown, so nothing should go there right now
/mysql/binlog: I should only move the mysql-bin.* files
/mysql/data: everything else should go there

Is that correct?

Here is the file list, for information (nxxxxxxx is my main database):
# ls -al /var/lib/mysql
total 38518860
drwxr-x--x.  9 mysql mysql       4096 20 nov.  13:50 .
drwxr-xr-x. 37 root  root        4096 29 déc.   2019 ..
-rw-rw----.  1 mysql mysql         56 21 déc.   2014 auto.cnf
-rw-------   1 mysql mysql       1680 27 déc.   2018 ca-key.pem
-rw-r--r--   1 mysql mysql       1112 27 déc.   2018 ca.pem
-rw-r--r--   1 mysql mysql       1112 27 déc.   2018 client-cert.pem
-rw-------   1 mysql mysql       1676 27 déc.   2018 client-key.pem
-rw-r-----   1 mysql mysql     589824 20 nov.  13:50 #ib_16384_0.dblwr
-rw-r-----   1 mysql mysql    8978432 19 nov.  18:28 #ib_16384_1.dblwr
-rw-r-----   1 mysql mysql      91522 20 nov.  13:50 ib_buffer_pool
-rw-r-----.  1 mysql mysql 9143582720 20 nov.  13:50 ibdata1
-rw-r-----   1 mysql mysql 1073741824 20 nov.  13:50 ib_logfile0
-rw-r-----   1 mysql mysql 1073741824 20 nov.  13:50 ib_logfile1
drwxr-x---   2 mysql mysql       4096 20 nov.  13:50 #innodb_temp
drwxr-sr-x.  2 mysql mysql       4096 19 nov.  18:28 mysql
-rw-r-----   1 mysql mysql 1073742002  5 nov.  21:45 mysql-bin.002418
-rw-r-----   1 mysql mysql 1073742033  6 nov.  12:00 mysql-bin.002419
                                    (...)
-rw-r-----   1 mysql mysql     341943 20 nov.  13:50 mysql-bin.002446
-rw-r-----   1 mysql mysql        179 20 nov.  13:50 mysql-bin.002447
-rw-r-----   1 mysql mysql        570 20 nov.  13:50 mysql-bin.index
-rw-r-----   1 mysql mysql   62914560 20 nov.  13:50 mysql.ibd
-rw-r--r--.  1 mysql mysql          6 19 nov.  18:28 mysql_upgrade_info
drwxr-sr-x.  2 mysql mysql      69632 17 nov.  17:15 nxxxxxxx
drwxr-x---   2 mysql mysql       4096 19 nov.  18:28 performance_schema
-rw-------   1 mysql mysql       1676 27 déc.   2018 private_key.pem
-rw-r--r--   1 mysql mysql        452 27 déc.   2018 public_key.pem
-rw-r--r--   1 mysql mysql       1112 27 déc.   2018 server-cert.pem
-rw-------   1 mysql mysql       1680 27 déc.   2018 server-key.pem
drwxr-x---.  2 mysql mysql      12288 27 déc.   2018 sys
drwxr-x---   2 mysql mysql       4096  1 mars   2019 test
-rw-r-----   1 mysql mysql  109051904 20 nov.  13:50 undo_001
-rw-r-----   1 mysql mysql  109051904 20 nov.  13:50 undo_002


texts: PostgreSQL custom operator UUID to varchar

texts:
I have a rather complicated Postgres database in which many UUID fields were incorrect stored as VARCHAR. I'd like to migrate them over in piecemeal, but unfortunately, doing so breaks all my views as Postgres doesn't have a built in operator for varchar = uuid. Rather rewrite all my views or attempt a single massive migration, I wanted to temporarily create a uuid = varchar operator  until the migration is completed.

I've never created a custom operator before and my attempt to below is not working:

CREATE OR REPLACE FUNCTION uuid_equal_varchar (varchar, uuid)
RETURNS boolean AS 'SELECT $1::text = $2::text;' LANGUAGE sql IMMUTABLE;

CREATE OPERATOR = (
    leftarg = character varying,
    rightarg = uuid,
    procedure = uuid_equal_varchar,
    commutator = =
);

However this operator breaks everything. Including a simple varchar = varchar comparison (see below):

SELECT * FROM test WHERE pk_test = '123';
ERROR:  invalid input syntax for uuid: "123"

Can someone explain to me what I am doing wrong? Am I trying to attempt something that is not possible?


texts: Can an EXISTS clause be improved when the condition is checked for the same value multiple times?

texts:
Assume that I'm modelling a Blog engine. Blogs have n Posts. Each User can be granted a role on different Blogs (Admin, Approver or Editor stored as enums/ints). Each Post can have a State (Draft, PendingApproval, Published stored as enums/ints). Here's how it's modeled:

Blogs
-----
Id (PK, int, not-null)
Name (varchar, not-null)

Users
-----
Id (PK, int, not-null)
Name (varchar, not-null)

Posts
-----
Id (PK, int, not-null)
BlogId (FK, int, not-null)
State (int, not-null)
Title (varchar, not-null)
Body (varchar, not-null)
(and there is an index on (BlogId, State))

UserBlogRoles
-------------
Id (PK, int, not-null)
UserId (FK, int, not-null)
BlogId (FK, int, not-null)
Role (int, not-null)
(and there is a unique index on (UserId, BlogId))

And here are the enums:

UserBlogRole
------------
Editor = 0
Approver = 1
Admin = 2

PostState
---------
Draft = 0
PendingApproval = 1
Published = 2

As an Approver, I want to view a list of all Posts that have the State = PendingApproval for all blogs that I have at least the Approver role.

Here's the query:

declare @approverRole int = 1;
declare @pendingApprovalState int = 1;
declare @myUserId int = 12345;

select
    p.Id, p.BlogId, p.State,  p.Title, p.Body
from Posts as p
where
    p.State = @pendingApprovalState and
    exists (
        select r.Id
        from UserBlogRoles as r
        where r.Role >= @approverRole and r.BlogId = p.BlogId and r.UserId = @myUserId
    )

This works as expected. But, I've been wondering about the performance of the exists(). If the same values are passed to the exists() multiple times, is it cached? For example, there could be many pending approval posts for a particular blog. Will it need to check the permissions for each of those posts and their associated blogs?

I've been thinking of changing it to a where in (query) instead:

select
    p.Id, p.BlogId, p.State,  p.Title, p.Body
from Posts as p
where
    p.State = @pendingApprovalState and
    p.BlogId in (
        select r.BlogId
        from UserBlogRoles as r
        where r.Role >= @approverRole and r.UserId = @myUserId
    )

But I'm not sure if that'd be better or worse performance? Does SqlServer execute the "in" query once and then use those values to check against the BlogId?

I guess this could be changed into a join too.

The execution plans of both showed the same (50% index scan on Posts PK and 50% index scan on UserBlogRoles PK) I think because my sample database doesn't have any data in it.


texts: Why does adding LIMIT to my query make it crawl?

texts:
Simple query:

select sum(score) total,name,gender,dob,country
from users join scores on users.id = scores.user_id
where date between '2012-01-01' and '2012-01-31 23:59:59'
group by scores.user_id having sum(score)>=1000 order by sum(score) desc limit 50

So trying to get a list of accumulated scores for Jan 2012, order them by scores descending and paginate them.

Without limit: slowish but OK: searches 69348 rows. (Be nice to work out how to avoid the temporary table but I can't). Explain says:

1, 'SIMPLE', 'scores', 'range', 'user,date,user+date', 'date', '8', '', 69348, 'Using where; Using temporary; Using filesort'
1, 'SIMPLE', 'users', 'eq_ref', 'PRIMARY', 'PRIMARY', '8', 'scores.user_id', 1, 'Using where'

With limit: it's the same but the rows search are now 1806794 and it takes forever.

It's a partitioned InnoDB if that makes any difference, with all data being on one partition.


texts: Dev/Test Environment For Databases With Cross-Database Joins

texts:
We have existing databases under source control in SQL Server Database Projects with a number of these databases referencing each other.

Getting these into projects and source control was challenging enough though I'm finding it difficult to setup Dev/Test environments without using a new SQL server instance.

At the moment we have a copy of the databases on a test SQL instance which is being used as part of continuous deployment. What I am looking to do is to develop in a branch and work against databases with a Dev prefix on the same instance, though realise that these would be referencing the databases used for continuous deployment.

Would this be possible by using Databases variables in Database References rather than just the name, if so how and is there a way to share SQLCMD variables across projects? Are there any alternative ways to consider?


texts: Query to partially summarize data in a table using SQL

texts:
I'm learning how to handle databases, so this is a trivial question, and quite likely has been answered already. I'm just not familiar to the lingo and have no idea how to search for it (although I tried).

My data is on LibreOffice, which runs HSQLDB v1.8 (very old engine version). For the moment, I have to stick to it. Using a more up-to-date SQL engine is not possible.

I have a payment table with the following structure

member_id  pay_year pay_unit
0          2016     1
0          2017     2
0          2017     1
0          2017     1
0          2018     2
1          2015     1
1          2018     2

I need to sumarize this table into something like

member_id  pay_year pay_unit_year
0          2016     1
0          2017     4
0          2018     2
1          2017     1
1          2018     2

The pay_unit_year is the number of years of membership purchased. So eventually I need to operate with the years and units, and getting a single line per member

member_id  expire_year
0          2023
1          2020

I'm using

SELECT DISTINCT member_id,
pay_year,
pay_unit
FROM payments

and I'm getting

member_id  pay_year pay_unit
0          2016     1
0          2017     2
0          2017     1
0          2018     2
1          2017     1
1          2018     2

Is there any way to perform an operation on a subset of pay_unit that has the other columns the same information?

I'm aware that SQL has many available date operations, but HSQLDB v1.8 has not them implemented.

Thanks for any help, and for your patience


texts: Suppose I want to perform data migration operation for which I would like to load the parent tables first and then the child tables for the obvious reason. Some of these tables play dual role. Assume the number of tables is huge in a schema. What would be the query that gives me the list of tables in that order which I can perform migration.


texts: X-devAPI not working inside of Google cloud SQL

texts:
I am tryng to use X-devAPI inside of Google cloud SQL but getting the following error from MySQL client

comp@user:~$ sudo mysqlsh -u root -h XX.XXX.XX.XXX --mysql --dba enableXProtocol
[sudo] password for philo:
Creating a Classic session to 'root@XX.XXX.XX.XXX'
Enter password: ~~~~~
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is XXXXX
Server version: 5.7.14-google-log (Google)
No default schema selected; type \use <schema> to set one.
enableXProtocol: Installing plugin mysqlx...
enableXProtocol: Error installing the X Plugin: ClassicSession.runSql: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

What am I doing wrong?


texts: Oracle: Connects with sqlplus but not from perl

texts:
I need to connect to an Oracle database from a perl script, it works in my tests and when using sqlplus, but not in production. This is a summary of the situation:
Oracle 11g XE (11.2.0.2.0) : (Test environment)
   sqlplus : OK
   perl    : OK

Oracle 11g    (11.2.0.4.0) : (Production environment)
   sqlplus : OK
   perl    : ERROR

The error reported is: Connect failed because target host or object does not exist
I have triple checked the connection settings and are not mistaken.
Specifics:

Test Server: Windows Server 2012
Production Server: Windows Server (Unknown version)
Client: Ubuntu 18.04 x64
Perl: 5.26.1
SQL*Plus: 12.2.0.1.0

SQL*Plus command:
sqlplus64 user/password@192.168.0.1/db_sid

Perl script:
#!/usr/bin/perl
use DBI;
$driver = "Oracle";
$sid    = "db_sid";
$host   = "192.168.0.1";
$dsn    = "DBI:$driver:sid=$sid;host=$host";
$dbh    = DBI->connect($dsn, "user", "password");

Note: I won't be able to test or execute anything in the production environment until next month.
What could be the issue?


texts: Postgres streaming replication between hosts with different timezone

texts:
I am running Postgres 9.1 on Windows 2012 servers. I am using postgres's streaming replication to replicate data between 2 servers.

It has so happened that the time zone of the slave has not been set correctly. The master time zone is UTC+4:00 where as the slave is on UTC-5:00.
The master time zone is correct whereas slave timezone is incorrect.

Would like to know -

1) what will be the effect of this on fields of type "timestamp with timezone"?

2) what will be the effect of this on fields of type "timestamp without timezone"?

3) what will happen to replication if we change the slave timezone?

Thanks for your insights.


texts: Join to a Culture Table vs Select Culture ID

texts:
I have a query that is executed several hundred thousand times a day. Due to legacy support, the incoming variables on this SP cannot change. Currently it looks like this:

Select id,
       name,
       otherdata
FROM Table1 t1
--<Various joins with aliases>--
INNER JOIN Culture c on c.id = t1.ID_Culture
WHERE
c.CultureTag = @CultureTag -- Usually 'en-US'

My question is: Would it be more efficient to remove the join to the culture table, and instead, SELECT the value of the primary key on the culture table and use THAT in the where clause? Would it be negligible?

EDIT: To clarify, would changing the code to this be more efficient?

DECLARE @CultureIdLocal int = (Select id from Culture where CultureTag = @CultureTag)

Select id,
           name,
           otherdata
    FROM Table1 t1
    --<Various joins with aliases>--
    --INNER JOIN Culture c on c.id = t1.ID_Culture
    WHERE t1.ID_Culture = @CultureIdLocal


texts: Replication applying daily snapshots to subscriber

texts:
I've setup a very basic snapshot based publication and have a single subscriber and that all works ok for the initial snapshot. I've scheduled the snapshot agent to generate nightly snapshots and that is all good as well. However, the subscriber didn't take the snapshot generated on the 2nd evening. So I set the subscriber to reinitialise and the new snapshot was applied so all good.

My question therefore is: what did I miss? Why did the subscriber not take the new snapshot and I had to force a re-init. I have found that I can do the following programatically and if this is the way, then I can schedule this:

exec sp_reinitsubscription @publication = 'My-Snapshot-Publication', @subscriber = 'all'

But it feels like I missed something, a setting maybe to force invalidation/reinitialize on every snapshot generation? does such setting exist? are there other ways? or do I have to force a re-init like the above for each publication to apply the nightly snapshot?

Thanks in advance.


texts:
I have inherited an Availability Group and have found that a linked server was set up between the two nodes of the availability group. It is using sa for authentication unfortunately.

Simultaneously, some of the team's developers use sa to query the live DB. I want this to stop.

My question is:

Are linked servers in any way required by availability groups? I know I have some bespoke moving parts like Agent jobs that use the linked server to sync jobs between the two nodes. I'm more concerned about something internal to SQL Server that I might not be aware of. Anything wrong with just doing normal due diligence and changing the linked server user to use non-sa and also changing the sa password?


texts: How to solve SIDs mistmatching in SQL or orphaned users?

texts:
I moved and restored a database from a backup into another  SQL Server 2008R2 about a year ago. I wasn't aware of the concept of orphaned users. From what I understood, is that we can end up having mistmatched SIDs? So I run the following below query and I see that sid names are not the same for the server and the database for the same login user. So far, users are not complaining but how would I fix this? is there any stored procedure that list them for me? or what problems that I could be facing if I leave everything like it is.

select d.name, d.sid, s.name, s.sid
from sys.database_principals d
full join sys.server_principals s
on d.principal_id = s.principal_id

Any advice is much appreciated.


texts: SQL query to list software by major versions

texts:
I have the following code i am tiring to get it to only show one line in the results for each major version, i need to combine all of the rows that are the same Like so.

SELECT
COUNT(arp.displayname) AS 'Count'
,arp.displayname
,swc.MajorVersion
,arp.Publisher

FROM
Inv_AddRemoveProgram arp
INNER JOIN
vComputer vc
ON arp.[_ResourceGuid] = vc.[Guid]
inner join
[Inv_Software_Component] swc on arp._SoftwareComponentGuid = swc._ResourceGuid

WHERE
arp.DisplayName NOT IN
(
'Power Scheme Plug-in Setup'
,'Altiris Inventory Agent'
,'Patch Management Agent'
,'Deployment Solution Agent'
,'Software Management Solution Agent'
,'Altiris Application Metering Agent'
,'Symantec pcAnywhere'
,'Symantec_pcAnywhere_plugin_installer'
,'Software Management Solution Plugin'
)
AND
arp.DisplayName NOT LIKE 'Security Update%'
AND
arp.DisplayName NOT LIKE 'Update for%'
AND
arp.DisplayName NOT LIKE 'Hotfix for%'
AND
arp.DisplayName NOT LIKE '%SQL_PRODUCT_SHORT%'
GROUP BY
arp.DisplayName, arp.Publisher, swc.MajorVersion

order by
arp.DisplayName asc

Results :

Count   displayname MajorVersion

41  Adobe Reader 8  8

1   Adobe Reader 8.1.1  8

40  Adobe Reader 8.1.3  8

1   Adobe Reader 9.3    9

1   Adobe Reader 9.3.3  9

1   Adobe Reader 9.4.0  9

1   Adobe Reader 9.5.5  9

4   Adobe Reader X (10.1.13)    10

1   Adobe Reader X (10.1.4) 10

1   Adobe Reader X MUI  10

7   Adobe Reader XI 11

139 Adobe Reader XI (11.0.02)   11

1   Adobe Reader XI (11.0.03)   11

2   Adobe Reader XI (11.0.06)   11

28  Adobe Reader XI (11.0.07)   11

1   Adobe Reader XI (11.0.08)   11

62  Adobe Reader XI (11.0.09)   11

5527    Adobe Reader XI (11.0.10)   11

3   Adobe Reader XI (11.0.10)  MUI  11


texts: Float datatype with 2 digits after decimal point

texts:
In SQL server float datatype does not display trailing zeros after decimal point

declare @num as float
set @num=5.20
select @num

will dispaly only 5.2
But i want it to display as 5.20

After a little research i found that it can be solved by using

select CAST(@num AS numeric(10,2))

But this is not the solution i am expecting. Is there any method like setting datatype or changing datatype so that i can achieve the same?(It will be better if any variants are there in float which can do the same)

If it is possible then i need not change the SQL statement and include cast etc. So please help me on this.


texts: Copy data from one table into other on some condition

texts:
I have two table as follows.

Table : city

city_id  city_name   state_id
  1       Cachar       1
  2      Darrang       1
  3       Nicobar      1

Table 2 : locality

pincode  Address       city
110020   abc           Nicobar
110021   abcd          Cachar
110024   abcd          Cachar
110023   abc           Nicobar

in table city i have 1430 rows , containing different cities in India.
And in table locality each city contains near about 100 pincode .

What i want to do ?
i want to replace each city(column) in table : locality with its corresponding city_id in table : city .

Question : How can i do this ? Is there any fast way to this ? I don't want to use any programming language ie. php ,java  . Is there any procedure , looping in Mysql ?

Result looks like ie.

Table 2 : locality

pincode  Address       city
110020   abc             3
110021   abcd            1
110024   abcd            1
110023   abc             3


texts: I only have the original files of database but no .bak file, how do I restore the database?

texts:
We were upgrading a server from SQL Server 2012 to 2014 at work and it was giving us trouble so I passed it off to someone else who, when backing up the SQL Server database, didn't create a .bak file. All he did was save the files from the folder Microsoft SQL Server (110, 120 and client SDK), the regular and x86 version.

My question is there any way I can just transfer the contents of these files to their respective folders on my new server or is this just completely lost?

Thanks.


texts: Memory-Optimized Table Memory Allocated issues

texts:
I have Some In-Memory OLTP objects in SQL Server and it's version SQL Server 2016 SP2-CU4.
I have a problem because of data row-size of some tables greater than 8060bytes, so there was some off-row storage in internal xtp DMVs and those tables allocated a memory about 120GB.

I alter that tables and reduce row-size to less than 8060 bytes, the memory allocated to those tables reduce and the allocated memory to object_id=0 is being grow in sys.dm_db_xtp_table_memory_stats and it's about 50GB. I think The garbage collector thread cant find this orphaned object , thus it cant deallocate memory.

another issue is  'Memory Allocated To Memory Optimized Objects' is 25GB  where 'Memory Used By Memory Optimized Objects' is 62GB, may be I think this is a bug

how I can deallocate this memory ?!


texts: Do I need SQS queues to store remote data in the Amazon Web Services (AWS) cloud?

texts:
My first question is, do I need SQS queues to receive my remote data, or can it go directly into an Amazon cloud storage solution like S3 or EC2?

Currently, my company uses a third-party vendor to gather and report on our remote data. By remote data, I mean data coming from our machines out in the wilderness. These data are uploaded a few times each day to Amazon Web Services SQS queues (setup by the third party vendor), and then the third-party vendor polls the data from the queues, removing it and saving it in their own on-premises databases for one year only. This company only provides reporting services to us, so they don't need to store the data long-term.

Going forward, we want to own the data and store it permanently in Amazon Web Services (AWS). Then we want to use machine learning to monitor the data and report any potential problems with the machines.

To repeat my first question, do we need SQS queues to receive this data, or can it go directly into an Amazon cloud storage solution like S3 or EC2?

My second question is, can an SQS queue send data to two different places? That is, can the queue send the data to the third party vendor, and also to an Amazon Web Services database?

I am an analyst/data scientist, so I know how to use the data once it's in a database. I just don't know the best way of getting it into a database.


texts: How to categorize records in groups in PostgreSQL?

texts:
Here is a same schema with data,

CREATE TABLE groups (
  id       int            PRIMARY KEY,
  name     varchar(30)
);
CREATE TABLE emp (
  id       int            PRIMARY KEY,
  name     varchar(50),
  group_id int            REFERENCES groups
);
INSERT INTO groups(id,name) VALUES
  (1, 'Managers'),
  (2, 'Teachers'),
  (3, 'Cleaners'),
  (4, 'Drivers');
INSERT INTO emp(id,name,group_id) VALUES
  (1,'john',1),
  (2,'Smith',1),
  (3,'Evank',2),
  (4,'Doe',3),
  (5,'Jane',4);

Now I need to categorize groups by the title of Site and have the following result:
I want to count all managers and teachers as site1, cleansers as site2, Drivers as site3 and so on.

Site      Employees
Site1         3
Site2         1
Site3         1


texts: update an id to max+1 in current table? (MySQL)

texts:
I've got a 'jobs' table where each row has a 'worker_id' that is either zero (job is not being worked on) or some unique ID that needs to be incremental.

Basically, when assigning this worker_id, I want to give it the first next available worker_id.

Here's what I'm trying:

UPDATE jobs SET worker_id=(SELECT MAX(worker_id)+1 FROM jobs) WHERE jobname='Abc'

The idea behind this is: if the current highest worker_id in my jobs table is 14, this query would set the worker_id for my job 'Abc' to 15.

But instead, this results in an error:

#1093 - You can't specify target table 'jobs' for update in FROM clause

What should I do?

FYI, the output of SHOW CREATE TABLE jobs is:

CREATE TABLE jobs (
  jobname varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  worker_id int(11) NOT NULL DEFAULT '0',
  email varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  customer_id int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

(I just removed the ` chars around table and column names for pasting as code here on StackExchange)


texts: Connecting Apollo Server to OrientDB Graph Database -- How to Connect?

texts:
I'm attempting to create a web application that will rely on fetching data from a Graph Database. My graph database is hosted by OrientDB 3.0. I'm trying to set up Apollo Server on the same machine to handle clients' GraphQL-formatted queries.

However, I cannot find any documentation describing how one can connect an OrientDB database to an Apollo Server. The "Getting Started" tutorial on the Apollo website sets up a constant to host the data rather than connecting to an external database:

And this tutorial uses MongoDB and a module called "Mongoose" to create the connection between Apollo Server and the Database:

I'm rather new to the concepts for Graph Databases and GraphQL in particular, so I'm not sure if I'm missing something obvious, or if I've perhaps misunderstood the relationship between Apollo and OrientDB. I've tried searching the web for "OrientDB" and "Apollo Server" and could not find any documentation linking the two together.

Any insight is greatly appreciated!


texts:
I'm trying to run this query to change my latitude coordinates, which is saved as  text into a double precision

ALTER TABLE mapdata ALTER COLUMN lat TYPE double precision USING (lat::double precision);

However, I keep getting this error: invalid input syntax for type double precision: ""

My assumption is that there is a blank entry and that postgres doesn't know to treat it as a double precision. How would I make this out to be a NULL or is there another way I can approach this to turn my lat column into a double precision? Thanks!


texts: INSERT INTO with SELECT subquery db2 9.5

texts:
I have the following query that takes 4 seconds to execute:

SELECT TX.* FROM TABLE_X TX
  INNER JOIN TABLE_Y TY
    ON TY.VALUE_ID = VALUE_ID_IN
      AND TY.PROD_ID = TX.PROD_ID
WHERE (TX.PLACE_ID1 = VAR_PLACE_ID OR TX.PLACE_ID2 = PLACE_ID)
  AND (TX.DATE > TY.FIRST_DATE)
  AND (TX.TYPE_ID != VAR_TYPE_ID1_IN AND TX.TYPE_ID != VAR_TYPE_ID2_IN)
  AND (TX.COL1 != COL1_IN OR TX.COL2 != COL2_IN)

However, I want to store it in a TEMPORARY TABLE.  When I execute the following query it takes 50 seconds:

INSERT INTO SESSION.TEMP_TABLE_X   /*new line to store result*/
    SELECT TX.* FROM TABLE_X TX
      INNER JOIN TABLE_Y TY
        ON TY.VALUE_ID = VALUE_ID_IN
          AND TY.PROD_ID = TX.PROD_ID
    WHERE (TX.PLACE_ID1 = VAR_PLACE_ID OR TX.PLACE_ID2 = PLACE_ID)
      AND (TX.DATE > TY.FIRST_DATE)
      AND (TX.TYPE_ID != VAR_TYPE_ID1_IN AND TX.TYPE_ID != VAR_TYPE_ID2_IN)
      AND (TX.COL1 != COL1_IN OR TX.COL2 != COL2_IN)

TABLE_X = 300 000 rows

TABLE_Y =  90 000 rows

How can I reduce the 50 seconds running time?

The server is running DB2 v9.5.


texts:
I'm creating a Support Ticket system and it need to have a history of all changes made on that ticket. For example:

Status change;
Changed the responsible assistant;
Sector/Department change;
Etc..

This is the structure I have at the momment:

I was thinking about it and tought it may be very confusing, or at least not the best option, to create a table structure to store the respective type of change and the id from the correspondent table. For example:

Type of changes:
1-> status change;
2-> Department change;
3-> Assistant change;
[etc]

id  |  ticket_id  |  history_type  |  old_value  |  new_value  |  create_date
1   |      2      |       1        |      3      |      6      |  ....
2   |      7      |       3        |      5      |      2      |  ....

The other option I have, is to write plain text, instead of saving the Id's, for example:

id  |  ticket_id  |  message  |  create_date
1   |     1       |  Status change from 'Open' to 'Closed'  |  ....
2   |     2       |  Assistant change from 'John' to 'Kyle'  |  ....

Is there anything wrong with one or another? Or am I missing a important consideration on doing this type of history structure? Or what would be the best option?


texts: Update Table A row if Table B row is changed

texts:
Is is anyhow possible to tell the database to execute a little script, if a row is changed in a table?

Scenario:
The database constantly monitoring Table B to see if rows are inserted, updated or deleted. If a row is inserted, updated or deleted: Update the row in Table A with the referring identifier.

Setup:

Table A
------------------------
UserID   Name   SalesSum
1        Carl   5
2        Peter  0
3        Oscar  3

Table B
--------------------------
UserID   Timestamp   Sales
1        01:00:00    3
1        02:01:00    1
1        03:54:00    1
3        01:20:00    2
3        02:45:00    1

The point of this being not to do a subquery every time the SalesSum is needed in the application, like:

Select A.Name, SalesSum = (select sum(B.Sales) from [Table B] as B where A.UserID = B.UserID) from [Table A] as A

But instead "just" doing it easy, nice and clean:

Select Name, SalesSum from [Table A]

Or is there anything I might have overseen or am wrong about?

EDIT:

Many of described the uses of inner joins for these sub-calculations.
But when "trying" this, I get a couple of errors: First I have to include each column in the GROUP BY clause unless it is defined by via an Aggregate function. But when trying to do this, then it errors when having a NTEXT datatype column in the clause, because it cannot be compared.

Here is an example of the original query:

Select
 U.UserID,                                                                 ' Int - Primary Key
 U.Name,                                                                   ' Nvarchar(MAX)
 B.OrderID,                                                                ' Int - Primary Key
 B.Comment,                                                                ' Ntext
 SalesAmount = (select sum(C.Sales) from OrdersDetails as C),
 SalesPriceSum = (select sum(C.Sales * C.Price) from OrdersDetails as C)
From
 Orders as B
Inner join
 Users as U on B.UserID = u.UserID
Where
 ' Clauses on finding specific columns based on the Orders table and the Users table
Order By
 U.Name asc
 B.Period asc

The "new" query, with another inner join, could be something like:

Select
 U.UserID,                                                                 ' Int - Primary Key
 U.Name,                                                                   ' Nvarchar(MAX)
 B.OrderID,                                                                ' Int - Primary Key
 B.Comment,                                                                ' Ntext
 SalesAmount = sum(C.Sales),
 SalesPriceSum = sum(C.Sales * C.Price)
From
 Orders as B
Inner join
 Users as U on B.UserID = u.UserID
Inner join
 OrdersDetails as C on B.OrderID = C.OrderID
Where
 ' Clauses on finding specific columns in Order table
Group By
 U.UserID
 U.Name
 B.OrderID
 B.Comment     <------- This errors because of the Ntext datatype!
Order By
 U.Name asc
 B.Period asc

As written, this is only an example, and therefore there can be a large number of selected columns - specially from the Orders table. All these needs to be in the Group By clauses. But this cannot be done with the Ntext datatypes. So what do I do?


texts: Why use custom CLR aggregate functions for string concatenation and geometry unions in SQL Server?

texts:
I often need to do string concatenation or geometry unions over a column in SQL Server 2008 and I'm aware that you can write custom aggregate functions in .NET and register them with  SQL Server to do these things.

However, you can take a very simple approach to the problem using a local variable and a select e.g.

CREATE TABLE Test(
    ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    geom geometry NOT NULL,
    attribute nvarchar(max) NOT NULL,
)
GO

INSERT INTO Test(geom, attribute)
VALUES ('POLYGON ((0 0, 1 0, 2 2, 0 0))', 'shape1'),
('POLYGON ((0 0, 0 1, 2 2, 0 0))', 'shape2'),
('POLYGON ((2 2, 3 2, 3 3, 2 3, 2 2))', 'shape3')
GO

-- string concatenation
DECLARE @mytext nvarchar(MAX) = '';

SELECT @mytext = @mytext + ' ' + attribute
FROM Test;

SELECT @mytext;

-- geometry union
DECLARE @mygeom geometry = 'POLYGON EMPTY';

SELECT @mygeom = @mygeom.STUnion(geom)
FROM Test;

SELECT @mygeom;

Nulls etc. aside, these seem to work fine. So I don't understand why these methods aren't suggested at all in most articles.

What is wrong with calculating aggregates values like this?

Thanks very much.


texts: How to write and read files from client machine SQL Server 2019

texts:
I need to prepare a stored procedure which export as many files as many tables I have in schema, but files need to be written on local machine on specific path e.g. C:\Temp\ not on SQL Server destination.
When file exists it should be overwritten, if not simply created.
I need also read from local files: with parameters and template, which will be used in procedure.
I know how to do everything except the method of read/write files.
I found some ideas for export to files:

xp_cmdshell executing bcp command I could only write files on Sql Server 2019 machine.
I found also sp_configure 'Ole Automation Procedures', 1;

    DECLARE @OLE INT
    DECLARE @FileID INT
    EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT
    EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, 'D:\data\sqltotext.text', 8, 1
    EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, 'Today is wonderful day'
    EXECUTE sp_OADestroy @FileID
    EXECUTE sp_OADestroy @OLE
    GO

Do you know easier way to achieve that?


texts: Update one table from another table while sorting that table based on one column

texts:
This is the problem I'm trying to figure out in MySQL. We have an old table contains some forms submitted by our users. Somehow, the previous decision was each time a user comes to this survey, a new form will be submitted. So in the old table we easily have several rows with the same Firstname, Lastname, but different values in the other columns, and there's a timestamp column Date_Submission as well.

Now we are trying to move everything to a new table, but this time, for each person we only keep one row. And we want to keep some of the the latest old data of that user (like email, phone number, etc)

I could do the following:

  update newtable, oldtable set newtable.email = oldtable.email where newtable.firstname = oldtable.firstname and newtable.lastname = oldtable.lastname;

Apparently this won't give me the "latest" old date for each person.

So I tried this one:

  update newtable, oldtable set newtable.email = oldtable.email where newtable.firstname = oldtable.firstname and newtable.lastname = oldtable.lastname order by oldtable.Date_Submission;

But they MySQL will complain with:

  "ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY".

So I'm wondering, what's the correct way to achieve this?


texts: Does RSS Profile have an effect on SQL Server with NUMA setup?

texts:
I'm curious whether the RSS Profile which is default NUMAStatic on Windows 2012 R2 could/should be changed for SQL Server with a NUMA setup.
Has anyone done performance benchmarks on high load SQL Servers with NUMA or Conservative settings?


texts: Allow refresh of lower environment from production when using a HSM

texts:
We currently use an Oracle Wallet for Transparent Data Encryption of certain critical columns and are migrating to a HSM for compliance reasons.  Our issue is how to handle encryption on the development systems that get refreshed frequently from the production system.  These refreshes are completely automated and rely on image file backups of our production system.  The development systems use the same Oracle Wallet and the refresh process handles manipulating the data in the sensitive columns so that the data does not have to be encrypted, however, we leave the columns encrypted so we can get similar behavior to production.  Here are some options I am aware of.

Have the development systems use the same HSM Partition as production.  This would be the most obvious solutions, but Oracle does not recommend it and this could potentially allow development to affect production.
Drop encrypted columns or drop them and add them back.  - This would be relatively fast, but leaves the columns empty, preventing processes that use the data from being tested.  It also changes the column order, which could be problematic.
Upgrade to 12c and use the reverse migrate command to move back to a wallet from an HSM. - Same problem as #1, albeit for a shorter time, but also requires an upgrade which we cannot do.  As I understand it, this functionality is not available in 11g.
Drop/recreate the encrypted columns and populate them using queries from production as part of the data manipulation part of the refresh.
Drop the tables and import them from an export of production.  Rather time consuming.

Are there other options I am missing or things that would make one of these options more desirable?


texts: Create foreign key constraint on indirect data

texts:
I am trying to enforce relational integrity across some database design containing sensor data. The relevant parts of the database:

CREATE TABLE logger (
    id INTEGER PRIMARY KEY
);

CREATE TABLE sensor (
    logger_id INTEGER REFERENCES Logger(id),
    logger_sensor_id SMALLINT,
    PRIMARY KEY (logger_id, logger_sensor_id)
);

CREATE TABLE measurement (
    id BIGINT PRIMARY KEY,
    logger_id INTEGER REFERENCES logger(id)
)

CREATE TABLE sensor_measurement_data (
    measurement_id BIGINT REFERENCES measurement(id),
    logger_sensor_id REFERENCES sensor(logger_sensor_id) -- problem is here
)

Since logger_sensor_id is not unique in the sensor table, I can't create the foreign key in sensor_measurement_data and the above won't work. I want to create a foreign key on both the logger_sensor_id and the logger_id which is included in the measurement table.

Is it possible to create such an "indirect foreign key" constraint? I guess it's possible to ensure referential integrity through triggers and checks, but I'd like to know if it's possible with foreign keys - it looks it would be a lot less error-prone to me. If it's not possible, is there a technical reason for this?

I'm currently using postgresql, but I'd surely want to know if other systems would be able to implement the above.


texts: Changes the default database owner in SQL Server 2008

texts: How can I change the default database owner when creating a new database in SQL Server 2008.


texts:
I encounter the following error:

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

when I was trying these commands

USE ASPState
GO

GRANT EXECUTE ON CreateTempTables TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON DeleteExpiredSessions TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON GetMajorVersion TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON GetHashCode TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetAppID TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetStateItem TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetStateItem2 TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetStateItem3 TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetStateItemExclusive TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetStateItemExclusive2 TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetStateItemExclusive3 TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetVersion TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempInsertStateItemLong TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempInsertStateItemShort TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempInsertUninitializedItem TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempReleaseStateItemExclusive TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempRemoveStateItem TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempResetTimeout TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempUpdateStateItemLong TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempUpdateStateItemLongNullShort TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempUpdateStateItemShort TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempUpdateStateItemShortNullLong TO [R2Server\AAOUser]
GO

So, how do I know what type of permissions my account R2Server\AAOUser have for the database ASPState? and what are the ways to solve the error (so that I can grant these permissions to my account)?


texts: Postgres - Return default value if regex match fails

texts:
I'd like to attempt a regex match, and return null if it fails.

The following query attempts to find the first number in a string. The result ignores the entry with text 'blah'. I'd prefer it to return a null value instead.

This question is probably less about regex and more about set algebra. My hunch is that there is an elegant way to do it, without needing to left join anything, though googling is proving futile.

with test_data as (
  select 'abc 123' as txt
  union
  select 'abc 456' as txt
  union
  select 'blah' as txt
)

select
  txt,
  (regexp_matches(txt, '\d+'))[1] as first_num
from
  test_data


texts: Complex query with multiple normalized fields

texts:
We have a fairly simple table structure, but with a LOT of fields per table (talking 40+).  This data is initially produced in plain-text, user-readable tables, but then it is translated into higher-performance, easier to query tables before being installed for use in production.

What we do is, wherever possible and reasonable we translate certain fields into enumerated values, and keep track of the enumerations in a MasterEnum table.  There are usually 20-25 enumerated fields out of 40 or so.

Sample table structure:

Plain text version:

    |  PartNumber   |  Manufacturer  |  SomeData  |  SomeMoreData  |  SomeTextData ...
    ----------------------------------------------------------------------------------
    |  1x9kdah      |  GizmoCorp     | ThisIsData |  OtherData     |  ThisStaysText ...
    |  8xcjkzh      |  GadgetInc     | MoreData   |  OtherData2    |  ThisTooStaysText ...

Target table sample structure:

    |  PartNumber  |  Manufacturer  |  SomeData   | SomeMoreData  |  SomeTextData ...
    -------------------------------------------------------------------------------------
    |  1x9kdah     |       1        |    1        |      1        |  ThisStaysText ...
    |  8xcjkzh     |       2        |    2        |      2        |  ThisTooStaysText ...

Master Enumeration Table Structure

    |  FieldName     |  InputText  |  ValueCode |
    ---------------------------------------------
    |  Manufacturer  |  GizmoCorp  |  1         |
    |  Manufacturer  |  GadgetInc  |  2         |
    |  SomeData      |  ThisIsData |  1         |
    |  SomeData      |  MoreData   |  2         |
    |  SomeMoreData  |  OtherData  |  1         |
    |  SomeMoreData  |  OtherData2 |  2         |

We have a means of doing this translation that works and works well; however it's a little on the slow side since all the processing is done in Java via Spring/Hibernate.  My question is:

Is there a way to write a single query that would accomplish all the above translations?  (Note that we have an excellent way of keeping track of our field definitions programmaticly, so generating complex SQL queries on the fly is not an issue).  If it is not possible to do it in a single query, how would I structure queries to iterate over the individual fields and make sure that as the translations happen the data is inserted into the new table remains associated with the correct rows?

Note that it is safe to assume the target table is always empty at the beginning of the process.


texts: Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables

texts:
Is it possible to configure an Oracle user in Enterprise Manager, so that he can later create views on any future, yet unknown table in his schema?

I am currently running into "ORA-01031 insufficient privileges" error, because no individual SELECT or data modification rights have been granted (only through role, plus the CONNECT role including CREATE VIEW - EDIT: Wrong! Does no longer!).

However, I run a DB creation script, executed from SQL Developer, which creates tables and is now supposed to create a view based on the previously created tables (just like another table). The script does not "know" the user account who is executing it, and is basically doing no rights/privileges settings at all.

It's basically like:

CREATE TABLE Members (
    ID        number(10),
    Name      varchar2(100),
    Street_No varchar2(100),
    ZIP       number(5),
    City      varchar2(100)
);

CREATE VIEW MembersNamesOnly AS
    SELECT ID, Name FROM Members;

Detecting the current user (executing the script) and grant himself the rights does not work, and it would result in annoying code overhead, granting rights for many, individual tables. And problems again, when other users shall use the view, but get their rights only through roles, without tedious user-table-right assignments for each user and table.

Is such a setting possible? Create tables and then views based on them, in a single script? I'm currently afraid I can't use any views at all, without heavy re-writing of all the existing database creation code.


texts: Next Steps to Diagnose ODBC SQLAllocHandle on SQL_HANDLE_DBC failed

texts:
An application intermittently fails with generic ODBC error. The client application is using ODBC to connect to SQL cluster. Enabling ODBC tracing shows this error:

StorageOnlineOp 46ec-44a8       ENTER SQLDriverConnectW
                              HDBC                0x012E2FD8
                              HWND                0x00000000
                              WCHAR *             0x6C228B34 [      -3] "******\ 0"
                              SWORD                       -3
                              WCHAR *             0x6C228B34
                              SWORD                       -3
                              SWORD *             0x00000000
                              UWORD                        0 <SQL_DRIVER_NOPROMPT>

StorageOnlineOp 46ec-44a8       EXIT  SQLDriverConnectW  with return code -1 (SQL_ERROR)
                              HDBC                0x012E2FD8
                              HWND                0x00000000
                              WCHAR *             0x6C228B34 [      -3] "******\ 0"
                              SWORD                       -3
                              WCHAR *             0x6C228B34
                              SWORD                       -3
                              SWORD *             0x00000000
                              UWORD                        0 <SQL_DRIVER_NOPROMPT>

                              DIAG [IM005] [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC failed (0)

According to Microsoft the error is explained:

  (DM) During SQLConnect, the Driver Manager called the driver's
  SQLAllocHandle function with a HandleType of SQL_HANDLE_DBC and the
  driver returned an error.

Any recommended steps for further narrowing down the problem?

According to ODBC logs version information is:

03.80.0000 = SQLGetInfoW (SQL_ODBC_VER)
03.52 = SQLGetInfoW (SQL_DRIVER_ODBC_VER)


texts:
I would like to present a banner motd (message of the day) to users that connect to a Postgresql database. Similar to cisco router and switch cli where you use:

banner motd "Seien Sie bitte vorsichtig....."

And then upon logging into the switch/router you see that message.

I'd like to see something similar when connecting to a database.

example:

$ psql postgres
psql (12.0)
Type "help" for help.
Seien Sie bitte vorsichtig.....

postgres=#


texts: Should developers be able to query production databases?

texts:
Should developers be given permission to query (SELECT / read only) production databases?  The previous place I worked, the development team had the db_datareader role; where I work now the development team can't even connect to the production instance.

One of the test instances is a copy of production restored from a production backup once a week, so there aren't any problems with developers actually seeing the data.

What good reasons are there for not allowing developers to query production (except for simply not wanting them to have access to read sensitive data)?


texts: Is it possible to grant role privileges to a user by document (not collection) in MongoDB?

texts:
I am working on a database for investigation purposes. I want to make some of that information public but other pieces of information are highly confidential.

I want to create a user and grant access only to the public information inside a document of a collection. For example in my collection "people", i want to show the name but not the phone number:

{
   "_id" : ObjectId("5910e7abce41fe759ebb1720"),
   "name" : "name",
   "phone" : "999999999"
}

How can i achieve this? I only found how to grant User-Defined Roles by collection.

db.createRole(
{
 role: "myClusterwideAdmin",
 privileges: [
   { resource: { cluster: true }, actions: [ "addShard" ] },
   { resource: { db: "config", collection: "" }, actions: [ "find", "update", "insert", "remove" ] },
   { resource: { db: "users", collection: "usersCollection" }, actions: [ "update", "insert", "remove" ] },
   { resource: { db: "", collection: "" }, actions: [ "find" ] }
 ],
 roles: [
   { role: "read", db: "admin" }
 ]
},
{ w: "majority" , wtimeout: 5000 }
)

Should i create a "public information" collection or is there a better way?


texts: IBM data studio 4 - how to open a new sql script editor?

texts:
I have to use IBM Data studio 4 and for the life of me I cannot figure how to open another SQL script editor window. Luckily it opens with one, but then I am constrained to that single window.

I don't want to go thru the trouble of having a project etc, just open another script and write a few statements.

PS: I know of a workaround of right-clicking an object in the connection tree and using Data/New Select Script, but that is a PITA as I have to open Table/View trees and point at one of the objects, and of course I am not always there (on the SP/Functions tree for instance) and it takes a while for the tree to open all objects.


texts:
We are designing a database in SQL Server to handle sales commissions. A diagram of the current schema looks like this:

There are only a couple of us on the project, and our boss dropped this design on us. I'm a novice, but I was concerned about the Slot table because it seems like we're heading toward an EAV model and I don't think our team is experienced enough to navigate the pitfalls.

A Slot can be a company, location, region, route or position (based on its SlotType). We have tables for these entities, but management wants the "flexibility" to assign slots to each other (SlotHierarchy). I'm not going to get into the mess of effective dates that apply to every slot, slot type, slot level, slot level type, and employee's position assignment. All the effective dates are because the management wants control over every piece.

The plan is to have an Employee get assigned to a position slot (SlotPositionAssignment) that can then be assigned to a location, route or region slot. A position can only have one employee assigned at a given time. A position can be assigned to any number of slots, though.

So, we may have two positions (slots) of PositionType "Salesman" and SlotDesc of "Sales 1" and "Sales 2", each with an employee assigned to it. These two positions may be assigned to multiple route, region, location or company slots. The SlotHierarchy helps sort this out.

My concern:
I'm afraid we may shoot ourselves in the foot with this. I don't mind writing additional application logic, but this seems "too clever". Would it not make more sense to call a spade a spade and use tables that map to real-life instead of obscuring them as "slots"?

I wanted to do away with having positions as slots and use a Position table, or perhaps use just PositionType though we'd lose the ability to report on a specific position in the company like "Rt 1 Manager". When I brought this up, I was told that it wouldn't be "flexible" and that was the end of it.

Is there a better way to organize this, or make some compromise, or am I worried about nothing? I am the novice and don't mind being wrong, as long as I know why I'm wrong.


texts: DB2 - Error accessing a table space SQL 57048

texts:
Please can you assist me,I am mighty new at DB2, I done a backup on the prod server in order to mirror the prod server on another server. Backups ran fine. I did the restore on my test server. but trying to connect to the database brings back an roll forward pending error . I then did the 'db2 rollforward db database to end of backup and complete' command.
This command than brought back an error of SQL0293N error accessing a table space container.

Please can you ad advise me.

Thanks


texts: Postgresql and UTF8 to Latin1 conversion?

texts:
We have a Postgresql database where the data is in Latin1. Apparently someone loaded some UTF8 encoded data into it since when I select data from some of the columns, I get stuff like:

SELECT symptoms from client
- -
"huvudvÃ¤rke"
- -

where "Ã¤" should be "ä". I could handle it with replace, ie.:

UPDATE client SET symptoms=replace(symptoms, 'Ã¤', 'ä');

I tried a couple of solutions I found, like using convert and convert_to but that leaves the data looking like "huvudv\303\244rke".

Is there an existing solution to my problem?

EDIT:

SELECT symptoms, symptoms::bytea from client
- -
"huvudvÃ¤rke";"huvudv\303\203\302\244rke"
- -

After the UPDATE above, query with symptoms::bytea outputs:

SELECT symptoms, symptoms::bytea from client
- -
"huvudvärke";"huvudv\303\244rke"
- -

EDIT2:

select version();
"PostgreSQL 9.2.9, compiled by Visual C++ build 1600, 64-bit"

show client_encoding; --I'm using pgAdmin III 1.18.1
"UNICODE"

show server_encoding;
"UTF8"

SELECT symptoms, convert(convert_to(symptoms, 'utf-8'), 'latin-1', 'utf-8')::text from client;
"huvudvÃ¤rke","huvudv\303\203\302\203\303\202\302\244rke"


texts: Archiving a huge SQL Server table with change data capture and foreign key constraints on multiple columns

texts:
We are trying to archive a huge table with ~170 million rows, which is partitioned, has change data capture (CDC) enabled and has ~5 foreign key constraints.

We decided on the archival since the daily full backup is taking a long time, and so we are planning to keep the archival table in different drive.

We tried the below ways, and all are taking really long:

We can’t go for inserting latest data to another table and then rename the tables accordingly because of the foreign key constraints.
SSIS package to insert to archive table old data is taking ~6 days in batch insert
BCP is also taking ~7- days.

Please suggest any best idea/suggestions considering CDC and foreign keys.


texts: Restoring encrypted database on another server (using Backup Encryption)

texts:
I have two SQL Server instances on same machine. I want to create an encrypted backup on one of the databases and then restore it on the second instance. I am doing the following steps:

Create and backup database master key in the master database which is going to be used to encrypt our certificates

USE MASTER;
GO

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'MasterKey_Password';
GO

BACKUP MASTER KEY
TO FILE = 'E:\GKKeys\MASTER_KEY.key'
ENCRYPTION BY PASSWORD = 'MasterKey_BACKUP_Password';
GO

Create and back up the certificate that is going to be used for encryption:

USE MASTER;
GO

-- създаваме сертификат, който ще използвам за криптиране на backup-a
CREATE CERTIFICATE BackupEncryptTestCert
WITH SUBJECT = 'smGK_BackupCertificate'
GO

BACKUP CERTIFICATE BackupEncryptTestCert
TO FILE = 'E:\GKKeys\SMGK_BACKUP_CERTIFICATE.cer'
WITH PRIVATE KEY
(
    FILE = 'E:\GKKeys\SMGK_BACKUP_CERTIFICATE_PRIVATE_KEY.key'
   ,ENCRYPTION BY PASSWORD = 'smGK_BackupCertificate_BACKUP_Password'
);

Creating the backup:

BACKUP DATABASE smGK
TO DISK = 'E:\GKKeys\smGKFULLEncrtypted.back'
WITH COMPRESSION, STATS = 10, ENCRYPTION (ALGORITHM = AES_256,SERVER CERTIFICATE = BackupEncryptTestCert)

Now on the second instance I want to restore the master key but I am not allowed:

USE MASTER;
GO

RESTORE MASTER KEY
FROM FILE = 'E:\GKKeys\MASTER_KEY.key'
DECRYPTION BY PASSWORD = 'MasterKey_BACKUP_Password'
ENCRYPTION BY PASSWORD = 'smGK_MasterKeyPassword';

  Msg 15317, Level 16, State 2, Line 4 The master key file does not
  exist or has invalid format.

Could anyone tell what is causing this?

Microsoft SQL Server 2014 - 12.0.4100.1 (X64)
    Apr 20 2015 17:29:27
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on
    Windows NT 6.3  (Build 9600: ) (Hypervisor)


texts: How to get data from multiple tables in one row

texts:
I am looking for a way to get the table name and description field from all tables in my database that start with T_. What I have up till now is this:

DECLARE @tables TABLE(table_name VARCHAR(100), column_name VARCHAR(100), row INT);

INSERT INTO @tables (table_name, column_name, row)
   SELECT
       tables.name AS table_name,
       columns.name AS column_name,
       ROW_NUMBER() OVER (ORDER BY tables.name)
   FROM
       sys.tables
   INNER JOIN
       sys.columns ON tables.object_id =  columns.object_id
   WHERE
       tables.name LIKE 'T_%' AND columns.name = 'DESCRIPTION'

DECLARE @i INT
DECLARE @count INT

SELECT @i = MIN(row) -1, @count = MAX(row)
FROM @tables

WHILE @i < @count
BEGIN
    SELECT @i = @i + 1
    DECLARE @q VARCHAR(400)
    SELECT @q = 'SELECT  ''' + (SELECT table_name FROM @tables WHERE row = @i) + ''' AS TABLE_NAME, '+ (SELECT column_name FROM @tables WHERE row = @i) + ' FROM ' + (SELECT table_name FROM @tables WHERE row = @i)
    EXEC(@q)
END

This results in the data I need, however in multiple tables. How can I get that data in one table?


texts: How to calculate avg of some column in join?

texts:
i have three tables

teacher

id name
1  ali
2  reza

course

id title
1  math
2  litrature

gradetbl

id courseid teacherid grade
1  1        1         20
2  2        1         10
3  1        2         17

the result:

teacherid avg(grade)
1         15
2         17

i try something but it did not work


texts: Is it recommended to clear the query plan cache

texts:
Pretty much as the title describes. I have just checked a SQL Server and noticed there is a lot of query plan bloat that can be fixed by proper parametrisation.

After making the changes to the code, is it recommended that we clear the query plan cache and let it rebuild itself? Or will it clear itself up in time?

NB: Out of the top 50 biggest queries, 47 of them were the same but with different parameters.


texts: Periodic snapshot fact table with monthly grain - Question on dimensions

texts:
I am fairly new to data-warehousing but have read my fair share of books and online tutorials to gain an introductory understanding of the basic components that build up a data warehouse.

My end goal is to be able to do a headcount of employees at the end of the month, therefore I designed my fact table as a monthly snapshot where each end-of-month I will populate my fact table (one row per employee per month).

My dimension table includes a DimEmployee table which is an SCD2 (any change made to an employee's information causes a new row to be introduced in the data source and I am keeping track of the most recent employee information via an is_current flag set to 'Y' or 'N').

My question is: do I need to update the dimensions daily and only insert whatever data I have at end-of-month into the fact table? Or do I update the dimensions end-of-month as well?

Thank you in advance!


texts:
I am new to sql (using mysql) and I have the following problem. I need to create a table where first column is some months, and the next three are :
first column: total amount of previous months (from the one stated in 1st column)
second: total amount of current month
third: total amount of the following months.

Now I have done it in three seperate queries, and what I want to do next, is combine them somehow. I am totally new to sql, so my initial idea was to create three tables and inner join them, but I guess this is not neat approach. Is there any way I can do that properly?

I am attaching my 3 queries, plus my output:

# 1 column
 select month(rdate),
(SELECT SUM(ramaount)
 FROM my_table
 WHERE (month(rdate) <(month(a.rdate)))
 and year(rdate)=2015
) as total_previous
FROM my_table a
where year(rdate)=2015
group by month(rdate)
order by year(rdate),month(rdate);

# 2 column
select
month(rdate) as month_2015,
sum(ramaount)
from     my_table
where    year(rdate) in (2015)
group by   month(rdate);

# 3h colum
SELECT month(rdate),
(SELECT SUM(ramaount)
 FROM my_table
 WHERE (month(rdate) >(month(a.rdate)))
 and year(rdate)=2015
) as total_next
FROM my_table a
where year(rdate)=2015
group by month(rdate);

My three results currently are:

1,
3, 375
5, 1325
6, 3055
7, 4255
8, 10255
9, 10555
10, 10755
12, 10935

1, 375
3, 950
5, 1730
6, 1200
7, 6000
8, 300
9, 200
10, 180
12, 1200

1, 11760
3, 10810
5, 9080
6, 7880
7, 1880
8, 1580
9, 1380
10, 1200
12,


texts: Connection error while connecting to Oracle XE

texts:
This just started happening this morning, and I'm not sure what the cause is.  IT just installed some sort of Windows update but I'm not sure if this is related to the issue.  Basically, when I try to connect to Oracle I get a connection timeout error.  When I start to connect, I immediately see the following error message in the alert_xe.log file:

***********************************************************************

Fatal NI connect error 12560, connecting to:
 (LOCAL=NO)

  VERSION INFORMATION:
        TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
        Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
        Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
  Time: 28-JUL-2016 09:04:38
  Tracing not turned on.
Thu Jul 28 09:04:38 2016

***********************************************************************
  Tns error struct:

Fatal NI connect error 12560, connecting to:
 (LOCAL=NO)
    ns main err code: 12560

  VERSION INFORMATION:
        TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
        Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
        Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production

  Time: 28-JUL-2016 09:04:38
TNS-12560: TNS:protocol adapter error
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12560

TNS-12560: TNS:protocol adapter error
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
opiodr aborting process unknown ospid (9876) as a result of ORA-609
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
opiodr aborting process unknown ospid (9316) as a result of ORA-609

I'm not really an Oracle expert, I'm a C# programmer and we just happen to use Oracle as our backend.  Any ideas on how to get me unstuck so I can continue my work?  Thanks!


texts: is this normalization correct for UNF to 3NF