texts: Best way to apply a "friend system"-like table?

texts:
I am making a website that has multiplayer games like chess, poker, etc.

Users will be able to add friends. I was thinking about how could I make this relationship in the database, and I thought of two methods:

1 - Using a row for every friendship
e.g

id   |   friend1   |   friend2

2 - Using only a row per user, adding all friends in a string separated by comma (in a text field maybe?) e.g

|id   |   user    |   friends                |
----------------------------------------------
|1    |    rellfy |   user1,user2,user3,user4|

What would be the best method to use? Using a string only, and parsing it later through the application, or querying for friends directly?


texts: Expanding AlwaysOn Availability Groups with Replication Subscribers

texts:
I have query regarding "AlwaysOn Availability Groups with Replication Subscribers"...

Can I add multiple publishers to a subscriber(which is part of availability group as a primary server)?
In which node can I configure distributer?
How do I configure the distributer?


texts: SQL Server, remove all users' access right to a table object

texts: As a clean up job, I am trying to remove all users' (including non public users) access right to a single table object (apart from superusers) and will subsequently grant new access rights. Is there a simple way to remove access rights to a table without using a cursor with system tables?


texts: Maximum number of databases for single instance of PostgreSQL 9

texts:
Developing a multicustomer application we plan to use a different database for each customer.
But it could be more than 1000 customers (applications).

Will PostgreSQL handle it without any problems?

Has anybody tried something similar?

Note:  35 tables for each one, with up to 3000 records as an average, for each database.


texts: Transfer data from standard mysql server to Percona XtraDB Cluster

texts:
My old mysql 5.5 server is having some hardware issue, so now I'm going to setup a new Percona 5.7,  two node cluster in my environment and connect my app into it.

Now the problem is,  How should I transfer all my data in mysql 5.5 server into my cluster ? Both are having InnoDB engine.
Will that be possible to take a sql dump from my old server and directly restore that in one of the nodes in my new cluster ? Or else what is the safest and effective way ?


texts:
I'm trying to set my general log file to a fifo named pipe and am getting an error

$ sudo mkfifo -m 0666 general.fifo

mysql> set global general_log_file='/var/log/mysql/dradis/general.fifo';
ERROR 1231 (42000): Variable 'general_log_file' can't be set to the value of '/var/log/mysql/dradis/general.fifo'

mysql> \! ls -l /var/log/mysql/dradis/general.fifo
prw-rw-rw- 1 root root 0 Nov 27 14:27 /var/log/mysql/dradis/general.fifo

Version I'm running 5.6.20-68.0-log Percona Server.

How can I make this work?


texts: Connecting to a new RDS instance from an EC2 instance

texts:
I'm trying to use MediaWiki and WP to connect to two MySQL databases.
The databases are working properly and so are the forms but
when I try to establish a connection to the database with MediaWiki or WP,
I just get a DB connection error: Permission denied.

Some facts:

I'm able to...

Connect to RDS by MySQL Workbrench to add/remove a users/table/Schema
Connect to RDS by ec2 Command-line ( mysql -u MYUSER -p -h ENDPOINT )

I'm NOT able to...

Check my server logs in MySQL Workbrench or Command-line Could not acquire management access for administration. Exception: Unsupported administration target type: ('windows', 'linux', 'wmi')
Remove rdsadmin or Revert the asingeded roles and privileges Error changing account rdsadmin@localhost: Access denied on rdsadmin@localhost
Grant DBA/MaintenenaceAdmin/ProcessAdmin or ReplicationAdmin to WPuser Error changing account WPuser@%: Access denied for user 'WPuser'@'%' (using password: YES)

What i have done so far:

I added the Elastic/Private and even Public IP address for the EC2 instance to the VPC security group. as follows: xx.xx.xxx.xxx/32 and xxx.xx.xx.xxx/32. I'm Not useing EC2-Classic Platform, so there is no RDS security group as i'm seeing.

GRANT ALL ON%.* TO WPuser@%`; and GRANT index, create, select, insert, update, delete, drop, alter, lock tables on WIKIdb.* to
     'WIKIuser'@'ENDPOINT';
See the following picture to have a over view of the rules

Again, my questions are:

What i missed and why would be the solution to establish those applications to databases?

Also do you have anything in mind, how can i solve the "I'm NOT able to..." section to "I'm able to..." ?

I'd be grateful for any help on these


texts:
Master db MySQL db Server 2012
Slave db MySQL Win7 XAMPP
DB size 500MB
Table count 42

I have setup the replication successfully however it stopped last week and my slave was showing the error Slave_SQL_Running No. I realised that it was looking at an incorrect log file (00004 whereas it should have been 00006).

I have since sorted this by;

At the MASTER;

SHOW MASTER STATUS;
Copied the values of MASTER_LOG_FILE and MASTER_LOG_POS.

At the SLAVE;

STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98; (<- example values)
START SLAVE;
SHOW SLAVE STATUS \G;

On my master I tested the replication by editing the table members - I edited one of the row values (from 85 to 86 - this successfully replicated in my slave). However I notice that on my master members table there are 70652 members but on my slave there are only 70056.

What could be the problem? Replication seems to be working but totals aren't. New members are added to the members table each day, but the aren't being added to my slave members table.

The results of my slave status table (from phpmyadmin) are;

Slave_IO_State  Waiting for master to send event
Master_Host xxx.xxx.xxx.xxx
Master_User repl
Master_Port 3306
Connect_Retry   60
Master_Log_File mysql-bin.000006
Read_Master_Log_Pos 787956776
Relay_Log_File  mysql-relay-bin.000004
Relay_Log_Pos   624412
Relay_Master_Log_File   mysql-bin.000006
Slave_IO_Running    Yes
Slave_SQL_Running   Yes
Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno  0
Last_Error
Skip_Counter    0
Exec_Master_Log_Pos 787956776
Relay_Log_Space 788197
Until_Condition None
Until_Log_File
Until_Log_Pos   0
Master_SSL_Allowed  No
Master_SSL_CA_File
Master_SSL_CA_Path
Master_SSL_Cert
Master_SSL_Cipher
Master_SSL_Key
Seconds_Behind_Master   0

Is there something else that I could check or test?


texts:
I have a replicaset that processes largish batches of inserts (a few million) every night. Last night, the primary failed over and went into a rollback state. All clients which access this replicaset use a connection URL with w=majority. The mongo DB documentation states that this should be sufficient to stop the old primary entering the rollback state. The old primary was in the rollback state for a solid 45 minutes.

Am I missing something in the config to ensure this doesn't happen?


texts:
On server lab01 was installed SQL Server and SSRS. On second server lab02 is installed only SSRS. When I configured Reporting Services Configuration Manager I create new database and given name ReportServer02 because already have ReportServer database which is database of SSRS on lab01. I am using the same account for both connections domain\lab_ssrs. On lab01 when run Report Manager everything is ok, but on lab02 I get error:

The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable)

Account have same permissions on both databases. Any solution ?


texts: Piplelined Result Set over Database Link Alternatives

texts: Since a pipelined result set cannot be returned over a database link (see MOS note 560743.1), what workarounds/alternatives are there.  I can think of a few, but I'd rather not lead the question.  I am particularly interested in alternatives that work when the function would accept more than one parameter and return more than one row of data.


texts:
I am not sure if I get this right. I'm designing a database model for use in an application, and I'm stuck at a doubly linked list (model first; there is no code yet).

There's an order. An order has an owner who gives it to someone, an acceptor. The acceptor can decide to divide the order into suborders. He will then become the owner of these suborders and give them to other acceptors. An order can have infinite Suborders, which can also have infinite Suborders and so on. Typical hierarchy. But the (sub-) orders have to be in order (no pun intended), and it would be nice if they could refer to the predecessor and the successor. Predecessor to the first suborder would either be null oder the parent order, successor to the last order either null or the parent order.

What is the best practice in such a case? I feel that the following is a bit... too much clutter (suborders would be a collection).


texts:
I am getting this error when attempting to connect to a SQL Server 2008 R2 machine:

  Cannot connect to JUPITER. A network-related or instance-specific
  error occurred while establishing a connection to SQL Server. The
  server was not found or was not accessible. Verify that the instance
  name is correct and that SQL Server is configured to allow remote
  connections. (provider: Named Pipes Provider, error: 40 - Could not
  open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

After restarting the service on server, everything works fine.

Why does this problem arise? How to solve this issue?

Thanks in advance


texts: SQL Server 2012+ Agent Job with Parameter to determine job flow

texts:
Is it possible to have an Agent Job with an input parameter?

What I'm looking to do is have a flag to determine how the job flow will occur.
For example: I have a 10 step job. Steps 1-5 are always going to be executed. However, if the flag is set to 1, then then next step is going to be 6, and if the flag is not set, then execute step 8.

Can anyone please suggest how I could go about doing this?
Thanks.


texts: Pipelinedb: How to group stream data by each N minutes in continuous view


texts:
I have a query that calculate distance from point a to point b using gps coordinates, and would like to make it view.

Is it posible to pass variables that will replace xxx and yyy in my query with variables passed from outside of view?

Here's query.

select
    round((((degrees(acos(((sin(radians(xxx)) * sin(radians(`ag_regija_hr`.`lat`))) +
         ((cos(radians(xxx)) * cos(radians(`ag_regija_hr`.`lat`))) *
           cos(radians((yyy - `ag_regija_hr`.`lng`))))))) * 60) * 1.1515) * 1.609344), 2) AS `dist`
from
    `ag_regija_hr`
order by `dist`


texts: SQL Server Query For Getting data in Specific Format

texts:
We have two tables. One is PriceGroupTable and other is PricesTable. There is a one-to-many relationship between them. Please refer to the attached pic for more details.

I am looking for a SQL query which will give the result in the specific format which is highlighted in yellow in the attached pic.


texts:
Trying to do some full text searching with postgres and when running the following command

UPDATE "EMAIL" SET PostText = to_tsvector('pg_catalog.english', "TEXT");

I'm getting the error:

ERROR:  text search configuration name "english" must be schema-qualified

Is there something I'm missing?  I've also tried

UPDATE "EMAIL" SET PostText = to_tsvector('english', "TEXT");

Edit 1:
Table EMAIL has the column "TEXT" in it.  They are both in all caps FWIW


texts: Slow on searching duplicate records in millions of records

texts:
I've below three tables
CREATE TABLE [dbo].[RegisteredUser] (
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NOT NULL,
    [UserCode] char(9) NOT NULL,
    [Email] [varbinary](5000) NULL,
    [Mobile] [varbinary](5000) NULL
    etc..
)
 ## indexes
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID1] ON [dbo].[RegisteredUser]
(
     [UserID] ASC,
     [UserCode] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

CREATE TABLE [dbo].[UnRegisteredUser] (
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NOT NULL,
    [NonUserCode] varchar(80) NOT NULL,
    [Email] [varbinary](5000) NULL,
    [Mobile] [varbinary](5000) NULL
    etc..
)

CREATE TABLE [dbo].[User] (
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varbinary](5000) NULL,
    [UserStatusID] [int] NULL
    etc..
)

and one View.
CREATE VIEW [dbo].[vUsers]
AS
   SELECT
      [ID],
      [UserID],
      [UserCode],
      CONVERT(varchar(200), CONVERT(varchar(2000), DECRYPTBYKEY(Email))) AS Email,
      CONVERT(varchar(20), CONVERT(varchar(2000), DECRYPTBYKEY(Mobile))) AS Mobile
   FROM
   [dbo].[RegisteredUser]
   UNION
   SELECT
      [ID],
      [UserID],
      [UserCode],
      CONVERT(varchar(200), CONVERT(varchar(2000), DECRYPTBYKEY(Email))) AS Email,
      CONVERT(varchar(20), CONVERT(varchar(2000), DECRYPTBYKEY(Mobile))) AS Mobile
   FROM
   [dbo].[UnRegisteredUser]

Registered and Unregistered table have parent-child relationship with User table and already got existing indexes on Registered table as well but when I use below query to find duplicate email or mobile, it tooks around 15 seconds for 4 millions+ records.
CREATE PROCEDURE [dbo].[uspIsDuplicate]
    @email varchar(200),
    @mobile varchar(20),
    @userCode varchar(30),
    @isDupEmail bit output,
    @isDupMobile bit output
AS
   BEGIN

 IF EXISTS(select 1 from [dbo].[vUsers]
          INNER JOIN [dbo].[User] ON [dbo].[User].[UserID] = [dbo].[vUsers].[UserID]
          WHERE [dbo].[User].[UserID] = 1 AND [dbo].[vUsers].[Email] = @email
          GROUP BY [dbo].[vUsers].[Email] HAVING COUNT(*) > 1)
    set @isDupEmail = 1;
ELSE
   set @isDupEmail = 0;

 IF EXISTS(select 1 from [dbo].[vUsers]
          INNER JOIN [dbo].[User] ON [dbo].[User].[UserID] = [dbo].[vUsers].[UserID]
          WHERE [dbo].[User].[UserID] = 1 AND [dbo].[vUsers].[Mobile] = @mobile
          GROUP BY [dbo].[vUsers].[Mobile] HAVING COUNT(*) > 1)
    set @isDupMobile = 1;
ELSE
   set @isDupMobile = 0;

END

I've tried another query by passing user code to the stored procedure. It executes around 6 seconds for duplicate records but it still takes 11 seconds if there is no duplicate record. Any methods I can use to improve execution time?
select 1 from dbo.vUsers with (nolock)
inner join dbo.User with (nolock) on dbo.vUsers.UserID = dbo.User.UserID
where dbo.User.UserStatus = 1 and dbo.vUsers.email = @email and dbo.vUsers.UserCode <> @userCode


texts: How could sequence.nextval be null in Oracle?

texts:
I have an Oracle sequence defined like so:

CREATE SEQUENCE  "DALLAS"."X_SEQ"
    MINVALUE 0
    MAXVALUE 999999999999999999999999999
    INCREMENT BY 1 START WITH 0 NOCACHE  NOORDER  NOCYCLE ;

It is used in a stored procedure to insert a record:

PROCEDURE Insert_Record
                (p_name    IN  VARCHAR2,
                 p_userid  IN  INTEGER,
                 cur_out   OUT TYPES_PKG.RefCursor)
    IS
        v_id NUMBER := 0;
    BEGIN
        -- Get id value from sequence
        SELECT x_seq.nextval
          INTO v_id
          FROM dual;

        -- Line below is X_PKG line 40
        INSERT INTO X
            (the_id,
             name,
             update_userid)
          VALUES
            (v_id,
             p_name,
             p_userid);

        -- Return new id
        OPEN cur_out FOR
            SELECT v_id the_id
              FROM dual;
    END;

Occasionally, this procedure returns an error when executed from application code.

ORA-01400: cannot insert NULL into ("DALLAS"."X"."THE_ID")
ORA-06512: at "DALLAS.X_PKG", line 40
ORA-06512: at line 1

Details that may or may not be relevant:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
The procedure is executed via Microsoft.Practices.EnterpriseLibrary - Data.Oracle.OracleDatabase.ExecuteReader(DbCommand command)
The application does not wrap the call in an explicit transaction.
The insert fails intermittently - less than 1%

Under what circumstances could x_seq.nextval be null?


texts:
i have a database sample that looks like this:-

as you can see my student master id is repeated. i want student master id to appear only once (in one row) can corresponding roll numbers to appear in columns. like this:-


texts: Creating a PostgreSQL SERIAL column using pgAdmin3

texts:
When I use pgAdmin3 to create a column of type serial, why does it instead create nextval('prices_id_seq'::regclass)?

Should we keep this type defined by pgAdmin? Manually create the table with SQL defining id column as type serial causes the pgAdmin SQL pane to again display it as nextval('prices_id_seq'::regclass).

pgAdmin SQL Pane

id integer NOT NULL DEFAULT nextval('prices_id_seq'::regclass)


texts: Read Committed Snapshot Isolation blocking during table modification

texts:
This is using SQL server 2014 SP2 (it was true in SP1 as well).

The database is configured to have Snapshot Isolation on and Is Read Committed Snapshot Isolation is set to true. The processes accessing the database do not set their isolation level, so they are defaulting to read committed.

I have a process that is modifying a specific table by deleting values (80,000 or so on average) from it and then inserting new ones, in a transaction.

I have an application that reads this same table, and only this table, in the database in Read Committed Snapshot Isolation.

The read is blocked with a LCK_M_IS. The Activity monitor shows that it is blocking on Object ID 1171535257 which is this table. The lock in the table is exclusive (Lock Mode X).

There is no foreign key on the table to explain the lock.

Can I prevent the Read process from issuing the LCK_M_IS or otherwise blocking, and have it read the committed data instead?

In a previous version of the question, an index was suspected as the culprit. Further testing has shown that even without any indexes this behavior is observed.


texts: How can I prevent oracle to create minidumps?

texts:
For some reason, our application sometimes loses connections with the Databases, and then a LOT of minidumps are generated.

How can I track the reason for these minidumps? how can I enable something to prevent this to be created?

Reading those files there is only a picture of all windows events running. there's no useful information.

I'm new with oracle and i'm pretty sure there is  some button to disable it.

Across all websites I could see people upgrading clients, service packs and etc. Like this one:

https://superuser.com/questions/438315/process-keeps-creating-dump-files

EIDT: Even after upgrading the software and environment, I have a lot of minidumps.


texts: I want to count all the rows for todays / yesterdays date based on a DATETIME - mysql+php

texts:
I have database "db2" with table "menjava"

In table menjava have "id", "author" and "date_submitted" field

id - auto_increment
author - int(11)
date_submitted - datetime

I want to count all the rows for todays date and all the rows for yesterdays date (so there will be two codes with conditions) based on a DATETIME field called 'date_submitted' that holds the date and time of each record's creation.

In the file result.php, there is this count displayed, but it does not work. In the same file (result.php) I have some other code to display data from different database, so I think that povezava.php is working ok.

My code:

 <?
    require "povezava.php";
    $q=mysql_query(" SELECT COUNT(*) AS total_number FROM menjava
WHERE date_submitted >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)",$link2);
// now you can
if ( $nt = mysql_fetch_array($q)){
echo $nt["total_number"];
$q=mysql_query($nt) or die(mysql_error());
}

    ?>

my file povezava.php looks like this:

<?
$servername='localhost';

$dbusername='user';
$dbpassword='pass';

$dbname1='db1';
$dbname2='db2';

$link1 = connecttodb($servername,$dbname1,$dbusername,$dbpassword);
$link2 = connecttodb($servername,$dbname2,$dbusername,$dbpassword);

function connecttodb($servername,$dbname,$dbusername,$dbpassword)
{
    $link=mysql_connect ("$servername","$dbusername","$dbpassword",TRUE);
    if(!$link){die("Could not connect to MySQL");}
    mysql_select_db("$dbname",$link) or die ("could not open db".mysql_error());
    return $link;
}
    ?>

Error that I get:

A PHP Error was encountered

Severity: NoticeMessage: Array to string conversionFilename: templates/master.phpLine Number: 231 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Array' at line 1

Fixed:

<?
    require "povezava.php";
    $q=mysql_query("SELECT COUNT(*) AS total_number FROM menjava WHERE date_submitted >= DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY)",$link2);
// working
if ( $nt = mysql_fetch_array($q)){
echo $nt["total_number"];
}

    ?>

Thank you!


texts: What are the undocumented values for referencing_class in sys.sql_expression_dependencies?

texts:
In SQL Server, the sys.sql_expression_dependencies system catalog view "Contains one row for each by-name dependency on a user-defined entity in the current database", to quote the docs. One of the returned columns is referencing_class, of which the docs say this:

referencing_class   tinyint     Class of the referencing entity.

1 = Object or column

12 = Database DDL trigger

13 = Server DDL trigger

Is not nullable.

However, I'm looking at a database right now in which there is a row with referencing_class equal to 7. What does 7 mean, and what is the correct list of possible values here?


texts: Design database to query a date range for scheduling

texts:
I am designing a db that should include a table with fields for datetime scheduling, so as I know what kind of queries will be made periodically I would like to design the table for them to be as simple as possible.

Every minute I will check table to see if "it's time!" but..

if I put fields like begin_time, end_time in every row, then when I do the query the database engine has to walk all rows, for example with a consult like

SELECT * FROM table WHERE begin_time<=actualTime AND end_time>=actualTime

Perhaps I am thinking as a closer-to-hardware programmer and overthinking but I would like to avoid some naive design that overload the server with a simple scheduler..

Other idea I have is to make a table with ranges alone and a key so if I make a query at the moment of insert perhaps there are repeated ranges and this will reduce the db work.

I've read this related question but doesn't adress same issue and have few answers Unique time range or database schema for a scheduling system so I hope to get some clues on this.


texts: MySQL: How to find MySQL 5.1 (with built-in InnoDB) or MySQL 5.1 (with InnoDB-plugin)?

texts:
My DB server has MySQL 5.1.58-community-log and I am planning to upgrade it to MySQL 5.6 with specific purpose of improving my system performance and removing the bottleneck issue.
So, I have gone through mulitple posts about the differences between MySQL 5.1 and MySQL 5.6

Below article indicates that MySQL 5.1 has better performance than 5.6
https://serverfault.com/questions/362388/mysql-5-1-vs-mysql-5-5-5-1-twice-as-fast/362706#362706

http://www.mysqlperformanceblog.com/2011/10/10/mysql-versions-shootout/

There are two versions of MySQL 5.1 as below:

MySQL 5.1 with built-in InnoDB
MySQL 5.1 with InnoDB-plugin

What are the differences between these?
How will I know that My server has built-in InnoDB or InnoDB-plugin?
How to change from built-in InnoDB version to InnoDB-plugin version and vice-versa?
Should I not leave my plan to upgrade MySQL?


texts: Cannot use the special principal 'dbo' when addressing SQLMI risk VA2108

texts:
We've just set up a new SQL Managed Instance on Azure, and have restored a database to it for testing.  After running an initial Azure Vulnerability Assessment, the results include this:

VA2108 - Minimal set of principals should be members of fixed high
impact database roles
Fixed database roles may have administrative
permissions on the system. Following the principle of least privilege,
it is important to minimize membership in fixed database roles and
keep a baseline of these memberships.
Remediation - Remove members who should not have access to the database role
ALTER ROLE [db_owner] DROP MEMBER [dbo]

I don't want to ignore this if its a real concern, but am worried about inadvertantly damaging something due to a lack of experience.  There are many web sites showing how to 'fix' the error, but each is based on its own scenario.
Within SQLMI we have the following logins:

SqlMiAdmin (the Azure-created user account that owns the instance)
DbUser1 - See below

Within the database itself we have the following users:

DbUser1 - Mapped to the above login for CRUD operations via stored procedures only
dbo - not used (directly, AFAIK)

The owner of the database (in Properties) is shown as SqlMiAdmin.  Any schema changes and database updates are performed by me using SqlMiAdmin.
I'm unsure about how to tackle VA2108 and it's implications in doing so.  If the dbo user isn't used in the database then I figured I'd try following the Azure recommendation, but it fails:
ALTER ROLE [db_owner] DROP MEMBER [dbo]
-- Cannot use the special principal 'dbo'.

Can anyone please recommend an appropriate course of action?


texts: SSRS 2016 Connect to SQL Server with Force Encryption Enabled

texts:
I have 2 Windows 2012 R2 servers in different VLANs.  One is an IIS app with SSRS 2016 installed and the other is running SQL Server 2016.  I can successfully open the SSRS site on the app server side and authenticate in IE.  When the IIS app (third party using ASP.net) tries to use the SSRS I see the below error in the event logs:

  System.Net.WebException: The underlying connection was closed: An unexpected error occurred on a send.
  System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
  System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host

My SQL Server instance is configured with a valid SSL Cert that's signed by our internal CA.  The app server does trust both the root and intermediate CAs in the chain so I don't believe that's the issue.

I've tried changing the rsreportserver.config file value for SecureConnectionLevel from 0-3, but none make any difference.

Connectivity from the app to DB on TCP port 1433 is successful.  I Don't think any other ports would come into play here.

Any suggestions on how an SSRS instance can successfully connect to a SQL Server that's forcing encryption?

When I ran the SSRS config manager utility I successfully created the DB and can open the web portal URL in IE on the app server successfully too.

UPDATE: I did try setting SQL Server "Force Encryption" to no and restarting SQL Server, but it made no change in the behavior.


texts:
Sql server log file stores the every log in information.is it possible to revert the query operation which we doing through query editor or by application based on the session we are logged in?

Thanks in Advance


texts: Increasing the size of differential backups

texts:
I have a DB in which some transaction remains open that is increasing the size of differential backups, I had to kill those transaction frequently, I want to drill down to the transaction and find out why it is remaining open so I am running some DMVs to achieve this task but to no avail. Following are the commands :

DBCC OPENTRAN

Oldest active transaction:

SPID (server process ID) : 17s
UID (user ID)            : -1
Name                     : Set File Size
LSN                      : (357764:44144:5)
Start Time               : July 17 2015 9:32:43:240 AM

Replicated Transaction Information:

Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (357472:25:1)
DBCC execution completed. If DBCC printed error message, contact your system administrator.

Please help me to solve this issue.


texts: OPENROWSET shuting down SQL Server service, but only running on the server

texts:
I've never seen such issue.
when i'm using SSMS from my own machine, normally conected to the server I want to run the query, I can run openrowset to select from an excel file with no  problems:
SELECT top 10 *  FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\path\ecelFile.xlsx', [Planilha1$])

it retrieves me the result.
BUT if I connect with for example remote desktop or mRemote to the server, and run this query inside it, the server just shuts down:

Msg 64, Level 20, State 0, Line 0 A transport-level error has occurred
when receiving results from the server. (provider: TCP Provider,
error: 0 - The specified network name is no longer available.)

I've never seen this. even testing with an excel with 10 rows and 3 columns, the SQL server service just shut down. and running from my machine to the server via SSMS, it never fails.
What could this be? theres nothing useful in the server log.


texts:
I am currently trying to configure the informix REST API, as specified here.

Overall, I came across two error messages:

Unsupported listener type of rest specified
OP Code is not valid: Check Message format

Can anybody help me understand the following error messages, or point me to a website or documentation for further research? Any help is greatly appreciated.

Current Progress

I have configured the INFORMIXDIR/etc/jsonListener.properties

#wire listener port
listener.port=27017

#wire listener type
listener.type=rest

#connection string for the wire listener
url=jdbc:informix-sqli://localhost:9088/sysmaster:INFORMIXSERVER=<servername>;USER=<username>;PASSWORD=<password>

#anybody can connect
listener.hostName=*

Upon starting the service with a script, I am told, the type of wire listener is unsupported.

2018-05-03 17:19:59 [main] ERROR com.ibm.nosql.informix.server.JsonListenerCLI - NOSQL_LISTENER_TERMINATED_UNEXPECTED
java.lang.Exception : java.lang.RuntimeException: Unsupported listener type of rest specified
at com.ibm.nosql.informix.server.JsonListenerCLI.start(JsonListenerCLI.java:116)
at com.ibm.nosql.informix.server.JsonListenerCLI.run(JsonListenerCLI.java:76)
at com.ibm.nosql.informix.server.JsonListenerCLI.main(JsonListenerCLI.java:34)
Caused by: java.lang.RuntimeException : Unsupported listener type of rest specified
at com.ibm.nosql.informix.server.JsonListenerCLI.start(JsonListenerCLI.java:108)
at com.ibm.nosql.informix.server.JsonListenerCLI.run(JsonListenerCLI.java:76)
at com.ibm.nosql.informix.server.JsonListenerCLI.main(JsonListenerCLI.java:34)

This did strike me as odd, as  it clearly states rest is a valid value for the property listener.type (as can be seen here).

If I remove the listerner.type=rest property, the default value (mongo) will be used and I can start the wire listener.

If I now try to curl the url http://localhost:27017/, I recieve an empty reply from the server. The corresponding error message in my log file goes as follows:

2018-05-03 18:19:19 [JsonListener-1-thread-1] ERROR com.ibm.nosql.informix.server.LwfMessageHandler - NOSQL_GENERIC_EXCEPTION
Error processing message;
java.lang.RuntimeException : OP Code is not valid: Check Message format
at com.ibm.nosql.informix.internal.request.MessageHeader.<init>(MessageHeader.java:119)
at com.ibm.nosql.informix.server.LwfMessageHandler.handleMessage(LwfMessageHandler.java:283)
at com.ibm.nosql.informix.server.LwfMessageHandler.run(LwfMessageHandler.java:123)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:736)

The bash script to start the wire listener:

#!/bin/sh

${INFORMIXDIR}/extend/krakatoa/jre/bin/java -jar ${INFORMIXDIR}/bin/jsonListener.jar -config ${INFORMIXDIR}/etc/jsonListener.properties -logfile ${INFORMIXDIR}/jsonListener.log -start & sleep 2
exit


texts: Oracle SQL - find the character '&' in a string

texts:
I am intending to do an update (Oracle SQL) for any customer records that includes an ampersand in their name (eg: Joe Bloggs & Co.) to replace this ampersand with the actual word 'and' (eg: Joe Bloggs and Co.).

See script below:

UPDATE acuheader
  SET apar_name = REPLACE (apar_name, '&', 'and')
where client = 'W5'
  AND apar_id = 'x'

HOWEVER, prior to doing this, I would have liked to run a select statement to identify all the customer records that have this issue.

I understand that the ampersand sign & is recognised by Oracle SQL as a regular expression & so was looking to see if someone could assist with a select statement.

I had initially thought this would work but not 100% certain:

SELECT *
from acuheader
where regexp_like (apar_name,'[&]')
  and client = 'W5'


texts: Postgres query plan has high rowcount, slow execution

texts:
I've identified this snippet of a query as running at incredibly slow speeds, although it's quite simple. It aims to find all the records in Table1 which don't have a more recent entry in Table2 with the same SessionID.

SELECT t1."VisitorID", t1."Timestamp"
FROM "Table1" t1
JOIN Table2 s ON t2."Timestamp" > t1."Timestamp"
            AND t2."SessionID" = t1."SessionID"
GROUP BY sgs."VisitorID", sgs."Timestamp"
HAVING COUNT(*) = 0

Where Table1 is a temporary table with 2640 rows and Table2 is permanent with 3756955 rows.

Explain analyze gives the following output:

HashAggregate  (cost=119823801.74..119823830.53 rows=2303 width=24) (actual time=5172.891..5172.891 rows=0 loops=1)
Group Key: t1."VisitorID", t1."Timestamp"
Filter: (count(*) = 0)
Rows Removed by Filter: 2228
->  Nested Loop  (cost=0.43..97379087.60 rows=2992628552 width=24) (actual time=0.036..3986.857 rows=2909911 loops=1)
        ->  Seq Scan on "Table1" t1  (cost=0.00..80.40 rows=2640 width=61) (actual time=0.007..1.588 rows=2640 loops=1)
        ->  Index Scan using "IX_Table2_Timestamp" on "Table2" t2  (cost=0.43..25191.47 rows=1169452 width=45) (actual time=0.036..1.120 rows=1102 loops=2640)
              Index Cond: ("Timestamp" >= t1."Timestamp")
              Filter: ((t1."SessionID")::text = ("SessionID")::text)
              Rows Removed by Filter: 71
"Planning time: 0.243 ms"
"Execution time: 5172.947 ms"

I'm guessing that the slow execution time is down to the high row count, estimating nearly 3bn in the loop with actual count close to 3m, and 1.1m in the index scan and finding 1102, but I can't find the reason for this. This is just a small dataset for testing, when I used it on the full dataset (340,000 rows in Table1), it filled 45GB of temp files before I cancelled it.

What I've tried so far:

VACUUM ANALYZE on all tables involved before that part of the query runs.
Indexing all columns involved.
Replacing the greater-than with equals on Timestamp
Removing the GROUP BY and HAVING COUNT(*) statements

These gave performance increases but the snippet was still far slower than the rest of the query and the row count was still just as high.

If it's relevant, I'm running PostgreSQL 9.4.1 on an Amazon RDS instance.

EDIT: Adding the statements for creating tables and indexes. I can't see anything out of the ordinary here.

CREATE TABLE Table2
(
  "VisitorID" uuid NOT NULL,
  "Timestamp" timestamp without time zone NOT NULL,
  "SessionID" character varying(50),
  CONSTRAINT "Table2_pkey" PRIMARY KEY ("VisitorID", "Timestamp")
)
WITH (
  OIDS=FALSE
);

CREATE INDEX "IX_Table2_SessionID"
  ON Table2
  USING btree
  ("SessionID" COLLATE pg_catalog."default");

CREATE INDEX "IX_Table2_Timestamp"
  ON Table2
  USING btree
  ("Timestamp");

CREATE INDEX "IX_Table2_VisitorID"
  ON Table2
  USING btree
  ("VisitorID");

CREATE TEMP TABLE Table1 ON COMMIT DROP AS
    SELECT
        s."VisitorID",
        s."Timestamp",
        s."SessionID",
    FROM Table2 t2
  -- This date range is just to ensure a small dataset for testing
    WHERE s."Timestamp" > '2015-07-29 16:55:00' AND
             s."Timestamp" < '2015-07-29 17:00:00';

    CREATE INDEX "IX_Temp_Table1_SessionID"
    ON "Table1"
    USING BTREE ("SessionID");
    CREATE INDEX "IX_Temp_Table1_Timestamp"
    ON "Table1"
    USING BTREE ("Timestamp");


texts: MySQL - Small table popping up in slowlog, refuses to use index

texts:
So I have two very very small tables (~20 rows) that are hit very frequently and pop up in the slowlog (I'm also logging tables not using indexes).

I tested using a large multipart index (that's just all the columns being selected) on this query:

SELECT type_templates.name, type_templates.is_default, type_templates.created_at, type_templates.updated_at, type_templates.id FROM type_templates;

However the query is still being logged (the QEP in this case shows an index hit):

+----+-------------+----------------+-------+---------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table          | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------------+-------+---------------+------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | type_templates | index | NULL          | idx_typetmpl_mp1 | 785     | NULL |   13 |   100.00 | Using index |
+----+-------------+----------------+-------+---------------+------------------+---------+------+------+----------+-------------+

Now, I understand 100% that the sequential scan is probably faster, but I think the problem I'm having is...  why in the world are these being logged?


texts:
I know other answers here (and here) say to order by newid(). However if I am selecting top 1 in a subquery - so as to generate a random selection per row in the outer query - using newid() yields the same result each time.
That is:
select *,
    (select top 1 [value] from lookupTable where [code] = 'TEST' order by newid())
from myTable

... yields the same lookupTable.value value on each row returned from myTable.
I'm trying to pick a random value from lookupTable. That table has only a few rows. In the real world I want to update myTable set someColumn =  ... a random lookupTable.value value such that each row in myTable is set with a random value and not such that a single random value is generated and assigned to all rows.


texts: Insert much slower when target table has clustered index

texts:
I've just had to debug an issue which has me thoroughly confused.

An ETL Transform process on our Development Data warehouse has just failed after working successfully every day for months. The same SSIS job calling the same stored proc, with the same table schema, indexes and data works fine in production.

This step would normally take less than 2 min. Today, after 4 hours the job hasn't completed, but hasn't failed either. There are no reported errors. Nothing in the SQL log, and sp_who2 doesn't show anything blocking.

Here is a link to the estimated plan when the query performs well.
Here is a link to the estimated plan when the query does not finish.

The job truncates a staging table then inserts about 600,000 rows of data. The ETL process has exclusive access to the table. When I checked, all I could see was waits on CXPACKET.

I have traced the fault to a unique clustered index.

The table has a non clustered primary key on an identity column (see below)

CREATE TABLE [dbo].[Transform_JobCosting_Transaction](
    [ETL_TransformKey] [int] IDENTITY(1,1) NOT NULL,
    [TransactionId] [varchar](255) NOT NULL,
    [KeyType] [varchar](255) NOT NULL,
    [FinancialYear] [varchar](255) NOT NULL,
    [Job] [varchar](255) NOT NULL,
    [Subjob] [varchar](255) NOT NULL,
    [AnalysisCode] [varchar](255) NULL,
    [etc] [varchar](255) NOT NULL,
    [etc] [varchar](255) NOT NULL,
    [etc] [varchar](255) NOT NULL
     CONSTRAINT [PK_Transform_JobCosting_Transaction] PRIMARY KEY NONCLUSTERED
(
    [ETL_TransformKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

The problem clustered index is:

CREATE UNIQUE CLUSTERED INDEX [IDX_Unique] ON [dbo].[Transform_JobCosting_Transaction]
(   [FinancialYear] ASC,
    [KeyType] ASC,
    [TransactionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

There is a 3rd non-clustered index which doesn't affect the insert at all.

Two of us have been working this for 4 hours. I have dropped and re-added the Index 20-30 times trying options in different combinations.

Summary: Clustered index blocks inserts. Non clustered works fine.

We have tried:

Server restart made no difference.
Tables truncated and sp run manually from SSMS no diff. (With SA privs)
Rebuilding the index didn't help.
If I drop the clustered index the insert works.
After insert above, I can add the clustered index without error.
If drop and re-add the index as non-clustered it works.
I have checked the data and it is unique grouped by those 3 fields.
Changing index so that its not unique didn't make any difference.
Adding/removing with tablock hint didn't help.
I tried sorting the data before inserting and it made no difference.

Running: Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64)
Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3

Any ideas or suggestions would be greatly appreciated.


texts: Is DISTINCTROW in MS-Access the same as selecting all the fields and doing a group by on all of them?

texts: I was looking though some queries in MS Access, and I found an SQL keyword I was unfamilar with called DISTINCTROW, from what I can tell, it's the same as an SQL GROUP BY which includes all the fields, but I'm not entirely certain about that.  Is this the case, or is there some small difference?


texts: A table with many null values VS many tables

texts:
Basically my level of DB knowledge enables me to create, select, delete and some simple things. When it comes to designing for performance, I have absolutely 0 knowledge.

So my question here is let say I have a table to record substances in water:

Here I am only showing 7 columns, it could be more than 100 columns. As you can see here, those empty fields represent the researchers are not conducting tests on those. The researches will choose a few of them so basically this design will leave a lots of null fields and takes up a lot of space.

Should I normalize the large table into smaller ones like these which only have an entry when there is data being inputted? This action is called normalization, am I right? However, this way will need me to join so many tables if I want to display the data in a report.

I am using PostgreSQL and I couldn't find the maximum limit of table can a query joins online. But I have a feeling that joining too many table is a slow operation. Should I keep the full table with nulls in it or should I break it down into smaller tables? Or is there a better way to do this or how would you do it?

Edited:

Edited 2:


texts: Is there a MySQL equivalent of the WITH clause in Oracle?


texts:
I'm just wondering if either the spec of the implementation (PostgreSQL) provides for generating identity columns from UUIds or the like.

Is there an alternative to,

CREATE TABLE f (
  id uuid DEFAULT gen_random_uuid()
);

Especially one that can protect the column under GENERATED ALAWYS


texts: PostgreSQL table structure design to minimize table size

texts:
I have a single table that could get very large. Because of the simple structure, it will eventually get unmanageable. How can I change the table structure so that it reduces table size while speeding up writes and searches?

It has four fields, one being a name field. The search is on the name field using this SQL as an example:

SELECT id, name, flavour
FROM mytable
WHERE name LIKE '%$1%'
ORDER BY id ASC
LIMIT 20

So this is a two part question:

Will streaming incoming items for this table to a breakout of first letter ('a' for 'alpha_item' into the 'a' table) actually reduce the overhead and increase stability?
Is this approach good for applying the same search to 26 tables and consolidating records for presentation? It sounds very heavy.

This is the only way I see managing such a large table while also spreading the workload onto different tables.  I'm sure there's something to be gained here, but I'm not sure if this is the best way of spreading information around for stability and speed increase on a search.


texts:
We would like to implement best practice on our SQL Server instances. All of our instances still run with the default setting of MAXDOP = 0.

Having read several pages on the recommended settings for this (including Microsoft Support and Erik Darling's posts on brentozar.com), we wanted to check that we have understood these recommendations correctly in terms of our instances. All of our instances are running on virtual machines, and many have only one socket and only a handful of virtual processors.

Example 1:

1 x socket 2.30 GHz
6 x virtual processors
Running 1 x SQL Server instance (2016 SP1 CU8, Web Edition 64-bit)

Change MAXDOP from 0 to 6, or from 0 to 3?

Since this instance only has a single socket anyway, a change from 0 to 6 will surely make no difference? Are we likely to gain anything from changing this setting?

Example 2:

2 x socket 2.30 GHz
6 x virtual processors per socket
Running 8 x (don't ask!) SQL Server instances (2012 SP4, Standard Edition 64-bit)

Change MAXDOP from 0 to 6? Are we likely to gain anything from changing this setting?


texts: Implementing a family database using Access 2010

texts:
A while ago I asked this question about using Access 2007 to implement a family tree model. I had all the tables and relationships mapped out in a UML diagram but realised it was getting out of hand (for a "fun" project), so I carried on with what I was doing (storing all my data in Excel).

I've realised now that I still want to find a way to do this in Access 2010, simply to be able to query the data in a number of ways using SQL. The forms will also be a much better way for me to capture the data and represent it visually. Before I start drawing up the UML diagram again (want to start it with a fresh approach), I would like to know if anyone has any advice about the feasibility of doing such a project in Access?

I have been using Family Tree Maker (genealogical software) to store the family data as well, but I do not like some of its features, and there are features I feel it is lacking. I'm also not too concerned about actually having a physical family tree drawn and populated in front of me, but merely a way to store all the data in perfectly normalised tables and relating them correctly so I can query the data in a variety of ways.

edit I should clarify that it is not strictly a family database, but also a "membership" type database as well, where people are divided into various groupings not necessarily dependent on family relations. The TRIBE -> CLAN -> FAMILY -> SIM relations are quite simple, but adding in the CLASS cuts across family lines.


texts: Permission denied to create new tablespace for PostgreSQL data

texts:
I can't set my PostgreSQL data folder to a drive other than the C drive (Windows 8 machine).

1) If I try to do so when installing PostgreSQL, the installation fails. Therefore I need to choose the C drive to make installation work.

2) If I try to create a tablespace in the D drive, I am not allowed to do so. Permission error pops up.

   CREATE TABLESPACE drive_D
   OWNER postgres
   LOCATION 'D:\PostgreSQL\data'

I get the following error:

   ERROR:  could not set permissions on directory "D:/PostgreSQL/data": Permission denied
   SQL state: 42501

I am an admin on that machine and I gave 'NETWORK SERVICE' full control on the D drive, but that didn't solve the problem.


texts: How to Store a Many-to-Many Relationship Between Fact Tables in a Data Warehouse

texts:
What are some structures and models to store many-to-many relational data between two fact tables in a data warehouse? Currently, I am using a mapping table which includes the primary keys from both tables, but I am wondering if there is a better approach?
My specific use case is relating invoices issued to payments received. I have an invoice fact table (each record designated with a unique InvoiceId) and a payment fact table (each record is designated with a unique PaymentId). My mapping table has columns for the InvoiceId and PaymentId as well as other useful information, but InvoiceId and PaymentId are not necessarily unique in their respective columns (because a payment can apply to multiple invoices and an invoice can be paid with multiple payments).
Thanks in advance for any advice and suggestions!


texts: Does full text search works with characters?

texts:
I have successfully created an FTS index on a column where we have stored the name of medicines e.g:

Amoxicillin 250mg capsules 21 capsule
Verapamil 160mg tablets 100 tablet

When I search with the parameters  Amoxicillin  or tablets it works fine.

But when I search for '%Amoxi%' or blets it doesn't come up with the answer. However the same keywords work with Like operator.

select * from AMPP as a
WHERE Contains(A.[NM] ,'%Amoxici%')

Above query has no results . however below query returns 140 rows in result.

select * from AMPP as a
WHERE Contains(A.[NM] ,'%Amoxicillin%')

select * from AMPP as a
WHERE a.nm like '%Amoxici%'


texts: Case Statements, subqueries, and datediff

texts:
First time posting on this website so please let me know if you require any other information. This first section of code worked perfectly and resulted in correct information.

[Field 1] = (CASE
                 WHEN DATEDIFF(dd, (
                   SELECT TOP 1 convert(varchar(10),bn1.bn_date,101)
                   **FROM BILLING_NOTES bn1**
                   **WHERE bn1.bn_invnum=inv1.i_id**
                   ORDER BY bn1.bn_id desc), @Today) >= 45
                 THEN 'N'
                 WHEN DATEDIFF(dd, (
                   SELECT TOP 1 convert(varchar(10),bn1.bn_date,101)
                   FROM BILLING_NOTES bn1
                   WHERE bn1.bn_invnum=inv1.i_id
                   ORDER BY bn1.bn_id desc), @Today) IS NULL
                 THEN'N'
                 ELSE 'Y'
             END),

I needed to add an additional parameter and split this column into two columns. The following is not giving me correct results, in fact it is just displaying every row as 'Y' for the following two columns. Can anyone assist me with how/why the addition of extra WHERE conditions and FROM joins caused this to go haywire?

[Field 2] = (CASE
                WHEN DATEDIFF(dd, (
                  SELECT TOP 1 convert(varchar(10), bn1.bn_date,101)
                  **FROM BILLING_NOTES bn1**
                   ** LEFT OUTER JOIN Invoices inv1 on inv1.i_id=bn1.bn_invnum**
                    **LEFT OUTER JOIN Client_episode_fs cefs1 on **
                   ** cefs1.cefs_id=inv1.i_cefsid **
                   ** LEFT OUTER JOIN PPS_ResubmitEBHistory prh on **
                   ** prh.invnum=inv1.i_id
                  **WHERE inv1.i_cefsid=cefs1.cefs_id AND **
                    **bn1.bn_invnum = inv1.i_id AND prh.rap=1 **
                  ORDER BY bn1.bn_id desc), @Today) >= 45
                THEN 'N'
                WHEN DATEDIFF(dd, (
                  SELECT TOP 1 convert(varchar(10), bn1.bn_date,101)
                  FROM BILLING_NOTES bn1
                    **LEFT OUTER JOIN Invoices inv1 on inv1.i_id=bn1.bn_invnum**
                    **LEFT OUTER JOIN Client_episode_fs cefs1 on **
                    **cefs1.cefs_id=inv1.i_cefsid**
                    **LEFT OUTER JOIN PPS_ResubmitEBHistory prh on **
                   ** prh.invnum=inv1.i_id **
                 ** WHERE inv1.i_cefsid=cefs1.cefs_id**
                   ** AND bn1.bn_invnum = inv1.i_id**
                    **AND prh.rap=1**
                  ORDER BY bn1.bn_id desc), @Today) IS NULL
                THEN 'N'
                ELSE 'Y'
             END),


texts: DB2 Express on Windows 7 - Disable security?

texts:
I'm trying to install DB2 Express-C on my PC (Windows 7 pro), but no matter how I've installed or configured it, I can't grant the ability to create databases to my windows domain account. No matter what I try, I get the errorSQL1092N my-windows-id does not have the authority to perform the requested command when I try to create a database.

My PC is centrally administered by my company, and I can't (permanently) create local acccounts on the PC. My domain account is in the local administrators group, and I've added my account to the DB2ADMNS group created during the DB2 install, but DB2 doesn't seem to be able to see that. I've spent about 12 hours going through the DB2 instructions and trying different things, but no matter what I've tried, DB2 won't allow my domain account to create databases.

I'm the only user of the PC and I'm not really concerned about security. I just need to get DB2 working in order to run some software that requires it. Is it possible to open up DB2 security and allow anyone to do anything? I tried changing the FED_NOAUTH config setting, but that didn't seem to have the desired effect.


texts: Blank (but not null) columns - PostgreSQL 9.2

texts:
Doing by:

select * from ja_customers where id = 8154501

I receive:

You can see that all the required fields are "blank" with some invisible character, so they're not null, so they're valid (technically)

Question:

How can I see which characters are in there?


texts: MySQL 'copying to tmp table' while enlarging VARCHAR

texts:
One user called today because he has tried to enlarge a VARCHAR column (from 20 to 100) in a slightly large table resulting in a write-locked table.

I watched PROCESSLIST and saw that ALTER TABLE sentence was being locked for 5 minutos in 'copy to tmp table' state.

I'm wondering why MySQL needs to copy table to a tmp table to enlarge a VARCHAR.
I mean, isn't supposed that one of the benefits of VARCHAR is that every row only uses needed bytes for this column as opposed to CHAR that uses all available bytes?.

From http://dev.mysql.com/doc/refman/5.1/en/char.html:

VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value

I understood that, while enlarging a CHAR will need to modify all table data to add new bytes in each row, enlarging a VARCHAR will only need modifications in table schema leaving data as is.

Why does MySQL need to copy the table to a tmp table?


texts: Elevate Database - Script out all tables structure

texts:
Could some one please help/guide me how I can script out all the tables structure from a single database in Elevate database. (In SQL there is a way to do it but not sure how to do it in Elevate DB)
I am new to elevateDB and tried to find out at online portals but didn't get any useful information. Any help will be appreciable.
Note: This ElevateDB is on elevate software, just read online about this.
Elevate Software develops and markets the DBISAM and ElevateDB embedded database engines for database application developers, and the Elevate Web Builder development environment for web application developers.


texts: How to get the current line number from an executing stored procedure

texts:
Is there a function or keyword that will allow me to get the current line number in a stored procedure?

I know there's an undocumented LineNo function that allows you to set the line number, and affect the output of system error messages
https://stackoverflow.com/questions/4054511/what-exactly-does-the-t-sql-lineno-reserved-word-do

I know there's a function ERROR_LINE() that's available inside of a BEGIN CATCH ... END CATCH.

ERROR_LINE() does what I need, but I want to use it from outside a CATCH block. Anywhere in the file.

 DECLARE @InsertSource VARCHAR(1000) = object_name(@@procid)
 EXEC PROC_Accounting_Transaction_Insert ... other parameters..., @InsertSource

Currently I'm just hard-coding the call # as it appears in the stored procedure body, but it's getting old quickly.

DECLARE @InsertSource VARCHAR(1000)

SET @InsertSource = object_name(@@procid) + '#1'
EXEC ...

SET @InsertSource = object_name(@@procid) + '#2'
EXEC ...


texts: Arithmetic overflow for Variable in Openrowset

texts:
I try to create a table to store the used storage Information for every Database.
Since theese are hosted on different Cluster / Instances, I created a table with all Instance / Server Information.
 _________________________
|________DBInstance_______|
|____ID___|_Instance_______|
|____1____|Server\Instance__|
|____2____|AG-Listener_____|

I then create a cursor and send my query to every server listed in the Instance-Table.

DECLARE @ID nvarchar(2), @Instancename VARCHAR(50), @cmd NVARCHAR(MAX)
DECLARE DBCursor CURSOR FOR
SELECT ID, Instance
from DBInstancelist;

DECLARE @ID nvarchar(2), @Instancename VARCHAR(50), @cmd NVARCHAR(MAX)
DECLARE DBCursor CURSOR FOR
SELECT ID, Instance
from DBInstancelist;

OPEN DBCursor
FETCH NEXT FROM DBCursor into @id , @Instancename
WHILE @@FETCH_STATUS = 0
BEGIN
select @cmd = N'INSERT INTO DBSIZE
select a.*
from OPENROWSET (''SQLNCLI'',''Server=' + @Instancename + ';Trusted_Connection=YES;'',''select
        [ID] = '+@ID+',
        [Database Name] = DB_NAME(database_id),
        [Type] = CASE WHEN Type_Desc = ''''ROWS'''' THEN ''''Data File''''
                      WHEN Type_Desc = ''''LOG''''  THEN ''''Log File''''
                      ELSE ''''SUMME'''' END,
        [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL ),
        GETDATE() AS Scantime
FROM   sys.master_files
WHERE      database_id > 4
GROUP BY      GROUPING SETS
              (
                     (DB_NAME(database_id), Type_Desc),
                     (DB_NAME(database_id))
              )
ORDER BY      DB_NAME(database_id), Type_Desc DESC'')
AS a;'
--select @cmd
exec sp_executesql @cmd
--select @cmd
FETCH NEXT FROM DBCursor into @ID, @Instancename
END
CLOSE DBCursor
DEALLOCATE DBCursor

for the first 4 Instances I receive the Information I except, for the 5th Instance however, I reveice following Error.

 "Arithmetic overflow error converting expression to data type int.

This error only Appears, If I add the Line

 "[ID] = '+@ID+',",

but the ID of the Instance is needed for further Reporting.

Why is the converting appearing, and how can I stop it (Where is my mistake)?


texts: I am using Neor Profiler to inspect auto-generated queries for MySQL server. This tool is good but not that flexible. Often it freezes when my application hits with more queries. So I am looking for an alternative which runs on a windows machine with better options.


texts:
I've sent FOIA requests to various public institutions in my area.

I'm only asking for the schema of the SQL databases their financial services might be using (column name for every table for every database).

I'm getting answers along the line that what I'm asking for is a security risk, therefore the organization will refuse my request.

I'm not a DBA. I use MySQL to process data in the public interest, but am only beginner level. I'd like to understand exactly how knowing only the schema of an SQL database could be a security risk. The general guidelines detailed in the answer to this question don't mention it.

Thank you very much in advance for your help!


texts: SET LOCK_TIMEOUT, is it session or statement based?

texts:
For what period does the LOCK_TIMEOUT last?

I do a SET LOCK_TIMEOUT 10 and a SELECT @@LOCK_TIMEOUT in one command after a login and get 10 returned. Immediately after this I do a SELECT @@LOCK_TIMEOUT again and it returns -1. I would have thought it still be 10.

I did some looking on the MSDN site and could not find if the LOCK_TIMEOUT is session based or statement based.


texts: how to check list of users present in all postgres database

texts:
I have around 50 of postgres database. I need to check all users with their respective database name. I know \du gives the list of all users like,

                                    List of roles
            Role name             |            Attributes             |      Member of
----------------------------------+-----------------------------------+----------------------
foo                             Superuser                              {}
..
..

which doesn't display database name. I can manually login to each database and run \du to see the list of users present in that database, but that would take lot of time. So is there single line command or simple workaround which can display user list with their respective database name ?


texts:
How can I use the following statement to return only the record with the maximum PersonID from the table?

/* Create the prepared statement */
if ($stmt = $mysqli->prepare("SELECT Max(PersonID) AS PersonID,familyname,firstname,othernames,gender,CountryID,dateofbirth,nrcno,passportno,address,occupation,village,chief,district,StatusID,imgurl

FROM t_persons")) {

/* Execute the prepared Statement */
$stmt->execute();

/* Bind results to variables */
$stmt->bind_result($PersonID,$familyname,$firstname,$othernames,$gender,$CountryID,$dateofbirth,$nrcno,$passportno,$address,$occupation,$village,$chief,$district,$StatusID,$img);

/* fetch values */
while ($rows = $stmt->fetch()) {
 // display records in a table


texts: Is this a normal set of MySQL privileges?

texts:
Using phpmyadmin on a local test wamp setup, and well as pretty much the title states, there are 3 users marked as user = ANY, password = NO, such as:

USER | HOST    | Password | Global Priv | Grant
-----+---------+----------+-------------+------
Any  | %       | No       | USAGE       | No
Any  | Local   | No       | USAGE       | No
Any  | Domain  | No       | USAGE       | No
-----+---------+----------+-------------+------

None of the 3 any users have any specific privileges for any tables, so it appears they can't do much. Is this some sort of generic security measure, to explicitly state that 'Any' user that doesn't fall into any other user group has no privileges?


texts:
I am working on an sql queries where i have a temp table named

 DECLARE @tcal table(TagIndex smallint,TagName nvarchar(20),Total decimal(18,2))

in which decimal numbers are used to stored. The values coming are:

284.45
233.97
240.17
81.39
67.55
0.00
71.57
72.91

I used to sum all the above decimal number by using following query:

select  sum(CAST(Total AS decimal(18,2)))  FROM @tcal where TagIndex>44

The sum coming after the query is :-

767.56

But the actual result should be:

1052.01

How to solve it?


texts:
We have a table of

id|school_id|parent_ids

where parent_ids is an array of ids.

If we don't have the school_id and only parent_id to search for, then the query will search through all the table rows in parent_ids array, there might be thousands of rows, and parent_id might actually be within just few of them.

Does using IN in query for the array column could be a performance barrier in this case?

EDIT

Here is the dump of table structure:

-- ----------------------------
-- Table structure for schools_messages
-- ----------------------------
DROP TABLE IF EXISTS "public"."schools_messages";
CREATE TABLE "public"."schools_messages" (
  "id" int4 NOT NULL DEFAULT nextval('schools_messages_id_seq'::regclass),
  "message" jsonb NOT NULL DEFAULT '[]'::jsonb,
  "details" jsonb NOT NULL DEFAULT '[]'::jsonb,
  "school_id" int4 NOT NULL,
  "created_at" timestamp(0),
  "updated_at" timestamp(0),
  "parents_ids" int4[] DEFAULT ARRAY[]::integer[]
)
;
ALTER TABLE "public"."schools_messages" OWNER TO "prod_schools";

-- ----------------------------
-- Primary Key structure for table schools_messages
-- ----------------------------
ALTER TABLE "public"."schools_messages" ADD CONSTRAINT "schools_messages_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Foreign Keys structure for table schools_messages
-- ----------------------------
ALTER TABLE "public"."schools_messages" ADD CONSTRAINT "schools_messages_school_id_foreign" FOREIGN KEY ("school_id") REFERENCES "public"."trk_schools" ("id") ON DELETE CASCADE ON UPDATE NO ACTION;


texts: How many B-tree indexes do I need to support lookups by any subset of n columns?

texts:
First, some background ideas

Generally, an SQL database which supports multi-column B-tree indexes also supports doing a lookup by a subset of the columns in the index if and only if they're the first columns in the index. For example, if I have an index on columns (a, b, c, d) and would like to execute:

SELECT * FROM my_table
WHERE b = 7 AND a = 'foo';

then this will use the index and be fast, because the pair (a, b) is located at the start of the index and so the database can navigate the tree looking for index records starting with ('foo', 7, ... ). However, if I run

SELECT * FROM my_table
WHERE b = 7 AND c = 'bar';

then the index will not be used*, because matching records will be distributed all over the index depending upon the value they have in column a.

* (except perhaps by doing a full or partial scan of the index, as described in Evan's answer below - but since a full index scan still has the same time complexity as a full table scan, and a partial index scan potentially does too, this doesn't help much.)

The problem

I have a table with n columns and a potentially huge number of rows. I also have a front-end GUI that allows the user to filter by the exact value of an arbitrary combination of those columns and view a table of results. It is not acceptable for any query generated by this front end to cause a full table scan to get its results; every possible filter has to be supported by an index.

With n columns, what is the minimum number of B-tree indexes I need to create to ensure that every possible combination of columns is covered by some index?

Example with n=4

Suppose my table has 4 columns: a, b, c, and d. There are then 15 different combinations of columns my user might filter by:

a b c d
a b c
a b d
a c d
b c d
a b
a c
a d
b c
b d
c d
a
b
c
d

However, I can support lookups from any arbitrary subset of these 4 columns using only the following 6 B-tree indexes:

(a, b, c, d)
(b, c, d)
(c, d, a)
(d, b, a)
(a, c)
(a, d)

To illustrate this, below are the 15 possible subsets alongside the index that can be used to do a lookup by that subset:

a b c d   (a, b, c, d)
a b c     (a, b, c, d)
a b d     (d, b, a)
a c d     (c, d, a)
b c d     (b, c, d)
a b       (a, b, c, d)
a c       (a, c)
a d       (a, d)
b c       (b, c, d)
b d       (d, b, a)
c d       (c, d, a)
a         (a, b, c, d)
b         (b, c, d)
c         (c, d, a)
d         (d, b, a)

I'm unsure how to generalise this idea to tables with large numbers of columns, though, or how the number of indexes required scales with n. Hence my question: how many indexes are needed to extend this approach to n columns, and how can I determine what they are?

(I'm primarily interested in the specific theoretical problem of how to support these lookups using B-tree indexes - but I'm open to other solutions, if for example there's an index type that neatly solves this precise problem better than a bunch of B-tree indexes would.)


texts: Casting an array of texts to an array of UUIDs

texts:
How can I cast an array of texts into an array of UUIDs?

I need to do a join between two tables: users and projects.

The users table has an array field named project_ids containing the project IDs as text.

The projects table had a UUID field named id.

My initial idea was a query looks like:

SELECT * FROM projects
JOIN users ON
projects.id = ANY(users.project_ids)

But that does not work since users.project_ids are not UUIDs so I tried:

projects.id = ANY(users.project_ids::uuid[])

and even:

projects.id = ANY(ARRAY[users.project_ids]::uuid[])

but neither one works:

ERROR: invalid input syntax for type uuid: ""

UPDATE

@a_horse_with_no_name is definitely right. The best option should be using an array of UUIDs.

The question now is how can I alter an array of text into an array of uuid?

The users table is currently empty (0 records).

I have tried

ALTER TABLE "users" ALTER COLUMN "project_ids" SET DATA TYPE UUID USING "project_ids"::uuid[];

which generates

ERROR:  result of USING clause for column "product_ids" cannot be cast automatically to type uuid
HINT:  You might need to add an explicit cast.

  ALTER TABLE "users" ALTER COLUMN "product_ids" SET DATA TYPE UUID
  USING "product_ids"::UUID;

I have also tried

ALTER TABLE "users" ALTER COLUMN "project_ids" SET DATA TYPE UUID[] USING "project_ids"::uuid[];

which generates

  ERROR:  default for column "project_ids" cannot be cast automatically
  to type uuid[]

The column is set to an empty array as default.

I'm running PG version 10.4 and project_ids is currently text[] nullable.


texts: Is there a DMV that shows the aggregate logical reads on a SQL server?

texts: I want to use this metric to compare 2 servers. I've looked at sys.dm_io_virtual_file_stats, but this is missing logical. Also looked at query_stats, but that only includes cached plans.


texts: Should I turn off GTID to fix this error?

texts:
Recently, a query has started failing. It is a query in the Magento software core (not a module) that Reindexes Catalog Search. I have receive the following error.

  Catalog Search Index index process unknown error: exception
  'PDOException' with message 'SQLSTATE[HY000]: General error: 1785 When
  @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional
  tables can only be done in either autocommitted statements or
  single-statement transactions, and never in the same statement as
  updates to transactional tables.' in
  /var/www/magento-20171012/mage-webroot/lib/Zend/Db/Statement/Pdo.php:228

I am running MySQL 5.6.35 according to SELECT VERSION();.

This documentation claims _ was introduced in MySQL 5.6.9.

This MySQL documentation claims "All servers in your topology must use MySQL 5.7.6 or later. You cannot disable GTID transactions online on any single server unless all servers which are in the topology are using this version."

I don't see gtid_mode in the my.cnf and SELECT @@GLOBAL.GTID_MODE; gives me ON.

I am not sure based on the documentation what GTID exactly is. It sounds like it's used for replication or Master/Slave setups. I don't have a Master/Slave set up and I don't replicate except a backup to a sql file which I don't think is what they mean by replicate.

Running SELECT @@GLOBAL.GTID_MODE; gives me ON. Is running SET @@GLOBAL.GTID_MODE = OFF; all that I need to do to disable GTID and fix this issue? I've done this in my dev (which is not exhibiting the issue) and don't have any issues, but I want to get advice before I apply this to production without understanding the consequences.

What would it mean to disable GTID? Would it resolve this error I'm receiving from the database?


texts:
Im packing big MyISAM tables with myisampack and came up with the following issue. (MariaDB 10.1.14 64bit windows)
Indexes are rebuilt after packing the tablewhile running myisamchk -rq, so, it doesnt make sense to create/have them beforehand. After compressing a table without indexes and trying to recreate all indexes at once, i've noted that the table is uncompressed back.

Is there a way which i could modify the frm in a way i could rebuild the indexes without uncompressing the table?

Does MySQL use the uncompressed index in order to build the compressed index?

Thanks!

Alex


texts:
I've been using SQL Server Management Studio 2012 for a while (on Windows 10, if that matters) and I've collected a good bunch of saved logins.

I've now installed version 17.2, which apparently installs side by side (rather than replacing previous version), and my logins are not available in this version.

Is there a way to migrate them from 2012?


texts: Back up Full Recovery Model Without Log Backups

texts:
I have a database that I need to backup to URL, but without the log file.

 BACKUP DATABASE [MyDB] TO URL = @destination
  WITH INIT, FORMAT, COPY_ONLY, CREDENTIAL = '', stats=1

The above script includes the log file.

When I back up to disk (not what I need) with the following command, it does not however back up the Log file - which is what I want

BACKUP DATABASE MyDB
TO DISK = 'F:\SQLServer\MyDB.bak'
WITH COPY_ONLY;

Why would the top script include the log file, and the bottom script exclude the log file?


texts: Parameter Sniffing: Why Does This Become An Issue?

texts:
Today, I had an issue with a stored procedure timing out (took longer than 30 seconds) when it was run from an ASP.NET web page, but executed quickly when run from SSMS (took 5 seconds).

After suspecting parameter sniffing as the culprit, I masked the input parameters, and the query executed faster.

My question is: why did this happen?

This system has been in production for more than 5 years, and this is the first time we've seen anything like this on our stored procedures. Is this "Database wear and tear"?

We've resolved the issue, so it isn't a big deal, but I'm just curious as to why this was happening.


texts: Backing up an Oracle 10G Express on Windows 2000 Server

texts:
Oracle 10G Express installed on Windows 2000 Server Enterprise Edition.

I am trying to automate backups and on Oracle's website I have found the following documentation.

http://docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/backrest.htm

Activate Archive Log

SQLPLUS -> CONNECT SYS/password AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Backup the Database

Then using the backup.bat file provided by Oracle: D:\oraclexe\app\oracle\product\10.2.0\server\BIN\BACKUP.BAT, I can backup the database either manually or with Scheduled tasks

Using a test server, I have been able to backup and restore the database using the backup.bat and restore.bat files provided.  I have not seen anything however about recycling those backups.

Questions:

Does Oracle provide a script to cleanup backup files that are no longer needed?
or What can I do to avoid filling up the D: drive with backup files?
Is there a better method to backup Oracle 10G Express?

Thank you


texts: Create Trigger MySql update or insert in another table

texts:
I am trying to learn more about triggers in mysql, so bear with me. I have two tables,

TestTable
 (id INTEGER PRIMARY KEY AUTO_INCREMENT, ItemId INTEGER,Date date, Value REAL)

TestTable2
 (id INTEGER PRIMARY KEY AUTO_INCREMENT, ItemId INTEGER,Year INTEGER, Month INTEGER, Open REAL, Close REAL, Increase REAL)

Where the table TestTable is measurements on a certain item on a specific date. TestTable2 contains measurements for a specific month,year and the increase during this period.

I want to create a trigger which updates or insert values into TestTable2 as I insert values into TestTable.

This is what the trigger i tried to create looked like,

CREATE TRIGGER 'monthUpdateTrigger' AFTER INSERT ON TestTable
BEGIN
IF NOT (EXISTS (SELECT 1 FROM TestTable2 WHERE
(ItemId=NEW.ItemId AND Year=YEAR(NEW.Date) AND Month=MONTH(NEW.Date))))
THEN
    INSERT INTO TestTable2 (ItemId,Year,Month,Open,Close,Increase ) VALUES
    (NEW.ItemId , YEAR(NEW.Date), MONTH(NEW.Date),NEW.Value,NEW.Close,0.0);
ELSE
    UPDATE TestTable2 SET Close=NEW.Close AND Increase=(NEW.Close/(SELECT
    Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)))
    WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date))
END IF;
END;

But this does not seem to work, I get the error,

  You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use
  near 'BEGIN IF NOT (EXISTS (SELECT 1 FROM TestTable WHERE
  (ItemId=NEW.ItemId AND Yea' at line 1

Can someone please point out the error/errors. Or offer a better solution to this problem.


texts: What is the logic of a query when using like with and/or

texts:
I have a table that contains subtitles ranging from subtitles_a to subtitles_z. I'm not sure why this query isn't working?

select * from subtitles
where field_name like '%subtitle_u%' or field_name like '%subtitle_v%'

This returns back everything in the range from subtitles_a to subtitles_z.

When I use the AND, I get values from subtitle_u to subtitle_v, shouldn't using an OR have given me these results instead?

select * from subtitles
where field_name like '%subtitle_u%' and field_name like '%subtitle_v%'


texts: How to get when MySQL instance went down last

texts: Is it possible to determine when a MySQL instance went down last? Is there a log file or literally anything at all that logs the server status in MySQL? I have trolled through countless articles online and have not found a single thing that offers any sort of help on this. HELP!


texts:
I have the below table

Col1       Col2
1          ACA,ABS,PHR
2          PHR
3          MCM
4          ABC

Now I want to filter the data from this so if I have a filter parameter as say 'ABS,MCM' I want to get only the rows which have at least one matching code. So in this case I should get the filtered result as

Col1       Col2
1          ACA,ABS,PHR
2          MCM

Now I can use the query Select * from myTable where Col2 in ('ABS','MCM') but then it won't retrieve the first row ACA,ABS,PHR. Can some one please tell me how I can do a text search for codes so as long as one code matches in Col2 I get the row so I can pass in directly the , delimited list and as long as it finds a single match the row is retrieved.

Thanks


texts: How can I specify the position for a new column in PostgreSQL?

texts:
If I have a table with the columns:

id | name | created_date

and would like to add a column, I use:

alter table my_table add column email varchar(255)

Then the column is added after the created_date column.

Is there any way I can specify the position for the new column? e.g. so I can add it after name and get a table like:

id | name | email | created_date


texts: Using COUNT in triggers before insert into table

texts:
I am new to MySQL and am struggling with triggers. Say I have a table called members with the following data

MemberID(PK) (INT) | Name (VARCHAR)|
1                  | Barry         |

and another table called class with the following data

ClassID(PK) (INT) | ClassType(VARCHAR) | ClassSize(INT)
1                 | Boxing             | 20

I also have a link entity table called members_has_class with the following data

Members_MemberID(FK) (INT) | Class_ClassID (FK) (INT)|
1                          | 1                       |

I'd like to make a trigger that checks the class is not full when a user likes to sign up to a class where the information entered (Member ID and Class ID) is inserted into members_has_class table.

So for example, if boxing has  a class size of 20, the trigger checks that there are not already 20 people signed up to it. So if there are not 20 people already in the members_has_class they can sign up, if the class is full it does not allow sign up. I was thinking I would have to count how many of the same class id values there are and compare if it is bigger and smaller than the class size.


texts: Updating to CU8 on SQL Server 2012 SP3 with CU2

texts:
Our management has decided to install SQL Server 2012 SP3 CU8.
Current running version has SQL Server 2012 SP3 CU2 installed.

This is like jumping 6 CU's ahead.
Currently there are no AG setup.

Question:
Does the 6 CU jump likely to cause any kind of issues?

Could you please guide me to a checklist to install CU's?


texts: Migrated DB from 12c to 19c with IMPDP failed

texts:
I have migrated a DB from 12c to 19c with IMPDP and EXPDP. While importing I have seen some errors. Below is an extract of the errors.

Errors During Import

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

VM247_RCDB_121GA.PK_TXI : sqlerrm = ORA-20000: Unable to set values for index PK
_TXI: does not exist or insufficient privileges

VM247_RCDB_121GA.PK_ARRE : sqlerrm = ORA-20000: Unable to set values for index P
K_ARRE: does not exist or insufficient privileges

VM247_RCDB_121GA.PK_ARA : sqlerrm = ORA-20000: Unable to set values for index PK
_ARA: does not exist or insufficient privileges

VM247_RCDB_121GA.SYS_C0034921 : sqlerrm = ORA-20000: Unable to set values for in
dex SYS_C0034921: does not exist or insufficient privileges

VM247_RCDB_121GA.PK_TXA : sqlerrm = ORA-20000: Unable to set values for index PK
_TXA: does not exist or insufficient privileges

Importing statistics failed for 20 object(s);

Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 22 error(s) at Wed Aug 7 08:2
8:32 2019 elapsed 0 00:05:25

The utilities are: expdp from 12c and impdp from 19c

Commands Issued

On the 12c instance i exported using:

expdp system/admin@orcl schemas=VM247_RCDB_121GA directory=DATA_PUMP_DIR
dumpfile=VM247_RCDB_121V10.dmp logfile=VM247_RCDB_121V10.log

...and imported on the 19c instance with:

impdp system/admin@orcl19c schemas=VM247_RCDB_121GA directory=DATA_PUMP_DIR
dumpfile=VM247_RCDB_121V10.DMP logfile=VM247_RCDB_121V10_imp.log


texts: query SUM function issue/ grouping issue

texts:
I am running into an issue where using the Sum() function is not working as it should be. I am running this query.

SELECT        tServiceReps.EmployeeNumber,
              tServiceHours.JobCode,
              tServiceReports.[Date],
              tServiceHours.[Hours]
FROM          AdjustUserRoleRequestStatusLookup
CROSS JOIN    tServiceJobCodes
INNER JOIN    tServiceHours
ON            tServiceJobCodes.ServiceJobCodes = tServiceHours.JobCode
INNER JOIN    tServiceReports
ON            tServiceHours.ReportNo = tServiceReports.ReportNo
INNER JOIN    tServiceReps
ON            tservicereports.employee = tservicereps.repid
INNER JOIN    tRegions
ON            tServiceReports.Region = tRegions.RegionCode
where         Date between '2017-10-03' and '2017-10-03'
and           EmployeeNumber = '10245'
GROUP BY      tServiceReps.EmployeeNumber,
              tServiceHours.JobCode,
              tServiceReports.[Date],
              tServiceHours.[Hours]
order by      EmployeeNumber asc

Gives the below output

EmployeeNumber  JobCode                 Date           Hours
10245             82         2017-10-03 00:00:00.000    0.50
10245             82         2017-10-03 00:00:00.000    1.50
10245            215         2017-10-03 00:00:00.000    2.50
10245            215         2017-10-03 00:00:00.000    3.00

When I add the sum() to hours I get weird results back.

SELECT        tServiceReps.EmployeeNumber,
              tServiceHours.JobCode,
              tServiceReports.[Date],
              sum(tServiceHours.Hours) as Hourz
FROM          AdjustUserRoleRequestStatusLookup
CROSS JOIN    tServiceJobCodes
INNER JOIN    tServiceHours
ON            tServiceJobCodes.ServiceJobCodes = tServiceHours.JobCode
INNER JOIN    tServiceReports
ON            tServiceHours.ReportNo = tServiceReports.ReportNo
INNER JOIN    tServiceReps
ON            tservicereports.employee = tservicereps.repid
INNER JOIN    tRegions
ON            tServiceReports.Region = tRegions.RegionCode
where         Date between '2017-10-03' and '2017-10-03'
and           EmployeeNumber = '10245'
GROUP BY      tServiceReps.EmployeeNumber,
              tServiceHours.JobCode,
              tServiceReports.[Date],
              tServiceHours.[Hours]
order by      EmployeeNumber asc

I get the below faulty output

EmployeeNumber  JobCode            Date          Hourz
10245             82    2017-10-03 00:00:00.000  17.50
10245            215    2017-10-03 00:00:00.000  27.50

from what I gather, the grouping is not working properly, which is probably what is causing the Sum() to go haywire. Any help is appreciated.


texts: Left Outer Join doesn't give expected result. What am I missing?

texts:
My objective is to find records that exist in Table 1 but do not exist in Table2. No record has a NULL value.
I am using the following code, but it is not giving me the expected results.

Select
    distinct a.fld
from
    tbl1 a
left outer join
    tbl2 b
on
    a.fld= b.fld
where
    a.fld<> b.fld

Most likely the issue is with my where clause, but I can't seem to figure out what the issue is. Pls Help.


texts:
I am a newbie to oracle database. I am trying to make a connection but I receive the following error:

Listener refused the connection with following error: ORA-12505, TNS: listener does not currently know of SID given in connect descriptor.

I have done my research online and after executing the lsnrctl status and lsnrctl service I get the following. Does anyone what I need to do? All kind of help is highly appreciated.

Thanks.

C:\Users\myComputer>lsnrctl start

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 24-SEP-2012 23:33
:29

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

TNS-01106: Listener using listener name LISTENER has already been started

C:\Users\myComputer>lsnrctl service

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 24-SEP-2012 23:33
:46

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "oracle.220.8.191" has 1 instance(s).
  Instance "oracle", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:166 refused:0 state:ready
         LOCAL SERVER
Service "oracleXDB.220.8.191" has 1 instance(s).
  Instance "oracle", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: PATO-PC, pid: 6232>
         (ADDRESS=(PROTOCOL=tcp)(HOST=Pato-PC)(PORT=49537))
The command completed successfully

C:\Users\myComputer>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 24-SEP-2012 23:34
:24

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date                24-SEP-2012 21:08:10
Uptime                    0 days 2 hr. 26 min. 16 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\myComputer\product\11.2.0\dbhome_2\network\admin\list
ener.ora
Listener Log File         c:\app\myComputer\diag\tnslsnr\Pato-PC\listener\alert\log.xm
l
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "oracle.220.8.191" has 1 instance(s).
  Instance "oracle", status READY, has 1 handler(s) for this service...
Service "oracleXDB.220.8.191" has 1 instance(s).
  Instance "oracle", status READY, has 1 handler(s) for this service...
The command completed successfully


texts: Should an INSERT cause an exclusive lock on a foreign key?

texts:
I'm working on a deadlock issue.

Process A is doing a simple INSERT into TableA which has a FK to TableB

Process B is doing a complicated SELECT on a join that includes TableA and TableB

I'll include the trace info below, but basically what I think is happening is that the insert to TableA is causing an Exclusive Lock (X) on the Primany INDEX of TableB due to TableA having a FK to TableB.  We do have referential integrity enabled for that FK, but there is no update to TableB needed by doing an insert into TableA, so it seems odd to me that an exclusive lock is required just to check for the existence of the FK value.

Is this expected behavior?  If so, is there anything I can do to mitigate this?  I honestly wasn't expecting such a basic/vanilla insert to cause a deadlock.

Also, it's not my real question, but if you happen to know what "subresources=FULL" means I'd be interested to know.

EDIT: Just to be clear about the deadlock:

processInserting is inserting to TableA and has an X lock on the Primary Index on TableB (the foreign key for TableA).  ProcessSelecting is waiting for a RangeS-S lock on this index.

processSelecting is selecting from a join of many tables including TableA and TableB and has a S lock on TableA (Becuase it's joining on it).  ProcessInserting is waiting for an IX lock on this table.

EDIT 2: Giving some more details.  The "select" query I was calling processSelecting is an extremely torturous query that uses a torturous view as part of the join, so it's kind of a mess to look at.

This is the DDL for the RoutePlan (TableA) and Form (TableB) tables.

http://pastebin.com/gWftciEG

RoutePlan has a trigger on it to log historical changes:

http://pastebin.com/WjvNxPFK

The SP doing the INSERT into Routeplan is:

http://pastebin.com/c6JqNFaX

The SP doing the SELECT is:

http://pastebin.com/micNhuPf

And the View CurrentAndPriorApprovers_View is defined as

http://pastebin.com/hHSzNJyc

And here is the full trace log info

http://pastebin.com/qenwhJcN

resource-list

keylock hobtid=72057594059882496 dbid=66 objectname=TableB indexname=PK_TableB id=lock204d08500 mode=X associatedObjectId=72057594059882496

owner-list
 owner id=processInserting mode=X

waiter-list
 waiter id=processSelecting mode=RangeS-S requestType=wait

objectlock lockPartition=0 objid=516196889 subresource=FULL dbid=66 objectname=TableA id=lock2044c5080 mode=S associatedObjectId=516196889

owner-list
 owner id=processSelecting mode=S
waiter-list
 waiter id=processInserting mode=IX requestType=convert


texts: writing a trigger in order to change Table Name automatically

texts:
Imagine there are two databases named "DB1" and "DB2".
There are two tables on both databases which are the same.
I mean we have "Table1" in "DB1" and also "Table1" in "DB2" and the data is the same as well.
I need to write a trigger so that whenever i change "Table1"'s name on "DB1", the name be changed automatically by a trigger on "DB2".
thank you.


texts: no pg_hba.conf entry for replication connection from host

texts:
(Also posted to pgsql-general)

I have been configuring a slave server that needs to connect to the host. Both the master and the standby servers have a pg_hba.conf that looks like this:

# Allow anyone to connect remotely so long as they have a valid username and
# password.
host    replication     ${REP_USER}     0.0.0.0/0               md5
host    ${DB_NAME}      ${DB_USER}      0.0.0.0/0               md5

This should allow access from every IP address, right? Evidently, though, the standby server cannot connect using the REP_USER credentials via primary_conninfo

primary_conninfo = 'host=${MASTER_PORT_5432_TCP_ADDR} port=5432 user=${REP_USER} password=${REP_PASS}'

I know this doesn't work because I never see in my logs:

LOG:  streaming replication successfully connected to primary

..and I cannot connect to the master server via psql either:

psql -h $MASTER_PORT_5432_TCP_ADDR -U replication
psql: FATAL:  no pg_hba.conf entry for host "$SLAVE_PORT_5432_TCP_ADDR", user "replication", database "replication", SSL off

Why? I don't get it...


texts: Eclipse data source explorer not showing Inserted table records (Oracle)

texts:
After I installed Oracle database 11g, I went to Eclipse Database Development Perspective and successfully created a new database connection for Oracle.

Then I created a new account call "testing" and logged in with this new user "testing" through SQL Plus and created a new table.

Enter user-name: testing
Enter password:

SQL> CREATE TABLE TBLSAMPLE (NAME varchar(50), EMAIL varchar(50), AGE integer);
Table created.

When refresh the Schema in Eclipse's Data source explorer. The table added from SQL Plus shows up. (All is working well so far)

I proceeded to insert some records from SQL Plus with the same user ("testing").
A check on the table shows that the records were indeed inserted.

SQL> SELECT * from tblSample;
...
6 rows selected.

Now the problem is: The inserted records are not showing up in Eclipse's Data Source Explorer despite refreshing the schema & the tables folder.

I created a new SQL file and select the entire tblSample, and it shows 0 records as well. I then inserted 1 row of record from Eclipse SQL file. It does shows up in Eclipse, but it does not affect the 6 rows of records in Oracle SQL Plus.

Why are the inserted records not showing up in Eclipse? To troubleshoot the culprit, I created a NetBeans project using the same Oracle database and all 6 rows of records does show up in NetBeans.

So what could have gone wrong?


texts: Why does postgres use more space for a time with timezone than a timestamp with timezone?

texts:
The documentation on date/time data types state that timestamp with timezone takes 8 bytes, while time with timezone takes 12 bytes. They both have the same resolution (1 microsecond), and on the face of it timestamp with timezone is storing more information.

Can anyone explain this behavior?

I'm not planning on using time with timezone for reasons explained on the same page.


texts: Is any caching mechamism used for non-persisted computed columns?

texts:
Does SQL Server cache the result of a non-persisted computed column so it can be reused without incurring cost of recalculation?

Additional Context
I've always given the advice that if using computed columns you should use the PERSISTED option unless you're expecting a higher insert/update frequency than read frequency, or if you need better performance around inserts/updates than you do reads of the data (i.e. since the cost of calculation has to be incurred on one or the other, so the decision is really where you want to pay that cost).

There's also the consideration of the additional storage for the computed data, but typically that's pretty negligible and cheap, so not much of a consideration.

However, I wanted to check if my advice is entirely accurate, since SQL may be more intelligent... i.e. Once SQL's calculated a computed column, it could record this value in memory so that it doesn't have to recalculate the value on subsequent queries. SQL could have a timestamp on the cached value and another on the underlying record data to say if that record's been changed since the computed value was calculated to determine whether the cached value is still valid.

Is there anything like this in place / does it depend on available resources (e.g. memory), or other factors (e.g. does the cached value have a TTL beyond that of the process lifetime)? I've never read anything implying that this exists, but I'd be surprised if there weren't some optimization going on under the covers.


texts:
I am using a query to calculate total percentage but I want a '%' symbol to appear next to it. Any ideas that could help me

My query so far is

SELECT
student.StudentID,
student.`Name`,
ROUND (Count(attendance.AttendanceID)*100 / 6  ) AS Total,
student.WorkshopID
FROM
    student
LEFT JOIN attendance ON student.StudentID = attendance.StudentID
GROUP BY
    student.StudentID,
    student.`Name`


texts: Difference between SSAS 2005 and SSAS 2008

texts:
I'm asking this question since i'm planning to book a seminar, but the provider told me that they only offer trainings for SSAS 2008 or as individual training which are expensive.

So my question: would it be worthwile to book the SSAS 2008 seminar although we're using SSAS 2005 or are the differences too big?


texts:
I'm creating an event planning PHP/MySQL web application as a learning project. The whole app revolves around the idea of the event and its various components (participants, groups, lists, etc.)

I understand how to use PHP and MySQL but my theoretical knowledge is at a basic level. I have setup and used very simple MySQL databases before, with little or no relationships between tables. This project is quite different.

Let me explain my idea of lists in the context of an event. The initiator of an event can create any number of lists for that event, each list can have items added to it by any participant of the event. With my current knowledge I would create the following tables: events, users, eventuser, lists, eventlist, items, listitem, etc.

This doesn't feel right… If the app was to be used by a couple thousand users, each making a few events, each having a couple of lists, each having a few list items… well, you get the idea. The listitem table would get gigantic pretty quick. Am I totally wrong? Would that be OK? Would it be better to create a set of tables for each event? …that doesn't feel right either.

What is the right way to do this? Or at least a better way?


texts: SQL Server 2008 R2 on Windows 7 x64 Professional - what are the downsides?

texts:
I need to minimize the cost of the platform, that will run SQL Server 2008 R2 Standard Edition. I have a special licensed version of this SQL server to work with like 20 users (it's an 1C special conditions licensing to be used with 1C products only) with costs not too much. I don't want to buy Windows Server license because with this number of clients (20) I need to buy Enterprise version, which is about $5000.

SQL Server 2008 R2 Standard supports as I can see Windows 7 Professional.
Windows 7 Professional x64 supports up to 192Gb of memory so that's prettty enough.
20 inbound connections limit does not apply to TCP-IP connections as I know.

What are the downsides of using Windows 7 x64 Professional as a host OS for SQL Server 2008 R2?


texts:
How can I keep a query execution plan in SQL Server's cache and use it several times?

When I execute a query for the first time it takes 7 seconds. After that it takes only 1 second.  This query is running once on daily basis.

Is there any way I can keep the execution plan and use it every day?