texts: Difference between LIMIT N and FETCH NEXT N ROWS?

texts:
The two queries below seem equivalent. Is one better than the other? e.g. in terms of performance.

select * from some_table offset 10 limit 10;

select * from some_table offset 10 fetch next 10 rows;


texts:
Let's say I create a schema for an e-shop what potentially sells everything: laptops, phones, tires, snacks etc.
Considering this, I cannot create a new table/entity for each good category the shop sells and describe the good's properties by new attributes.
So, I went to more generic model to describe each product

To me, it seems fine but as soon as I've tried to implement this solution, it's appeared to impossible to execute any query like

select names and prices of all laptops where ssd capacity >= 512 and weight < 2kg and price <= 2000 money

How is possible to change this schema and keep the ability to store information about any product?


texts: List the most popular item being ordered

texts:
I have the following tables:

ITEM (Item#, Item_Name, Unit_Price)

ORDER_ITEM (Order#, Item#, Qty)

How do I list the most popular item being ordered ?

I'm using Oracle SQL Developer.  It is popular by most Qty sold.


texts: Heartbeat Options with MySQL and Possibilities

texts:
I would like to understand if the following scenarios are possible in Heartbeat in Linux.

Setup: Two Database Servers running Mysql in Active/Passive mode in replication mode having Heartbeat setup for HA or failover mechanism. Application connects to DB using VIP that is started at the time of Heartbeat.

Failover VIP to passive site if primary MySQL instance is shut down.
Bring down the heartbeat in primary if the role has been given to passive/secondary site in order to avoid split brain.

Setup lies on two different datacentres in a country. Master (Primary Site) and Slave (Secondary Site).

To Ronaldo

Below is my explanation to Ronaldo for the reason why I have gone for Master/Slave+Heartbeat

The customer had agreed for RPO and RTO of 10 mins.
- With Master/Slave I have tested in my test grounds using highest TPS(Transactions Per Second) of about 250 TPS and more as a stress test using DMLs. Since we have a dedicated WAN to the datacentres for this application function, I have a bandwidth of 20 to 35 MBPS. And with the testings I see that it didn't cross more than 5 to 8 MBPS at max TPS of 260 in a range of 3 to 4 hrs. And the DMLs are applied in relay log immediately with Seconds Behind Master lesser than 5.

I didn't consider DRBD to be an option here. As when the DB size grows like 1.5 TB < at a situation in a regular peak hrs and if the Primary site is down. I observe the recovery process of innodb to build up and be available for applications takes 15 to 20 mins. Which goes beyond the bar with Customer SLA mentioned in RPO.
When you compare the case in Mysql Replication even though I have seconds behind master as 10 mins (which is a very rare possibility) . Still we come under the RPO and RTO of the Customer.

Concerns in Mysql + DRBD

Also DRBD has some constraints to bring up if any logical volume gets corrupted the same situation occurs in secondary site.
Even in DRBD how do you go for an automatic failover to secondary site?
The maintanence of Production will again cause more laboratical work.

Eg: If I need to rotate a larger table in Production but not on Slave which can be treated as backup later. Incase of Master/Slave I can go for set session sql_bin_log=0; Which will not get executed in Slave. Whereas in DRBD, I need to mount slave and take backup of that huge table to tape and then put it back in DRBD mode.


texts:
Is there a function or system view that returns all of the available property names?

I was looking for 'SYSTEMDATAACCESS' and 'USERDATAACCESS' which aren't listed on the MSDN OBJECTPROPERTY page.  I added a comment about them, but am wondering if there are others that I don't know about...given that it's SQL I'd expect them to be available as queryable metadata somewhere, but couldn't find anything.  Are the properties in the meta data somewhere?

EDIT: While it isn't really relevant to my question, here is an example usage of system data access.  select OBJECTPROPERTY(OBJECT_ID('sys.dm_db_stats_properties'), 'SYSTEMDATAACCESS')


texts: What does the Union sign in EER diagrams mean?

texts:
I know disjoint and overlapping, but what does the union like sign mean in an enhanced entity-relationship (EER) diagram...? For example:

In other words, what does this diagram mean?


texts: Upgrade certification from MCSA sql server 2012/2014 to MCSE SQL 2016

texts:
All,

I would like to know what path should I chose in order to achieve MCSE for SQL 2016. I already completed MCSA certification for SQL 2012/2014 and would like to upgrade it to MCSE SQL 2016/2017. Kindly, let me know what is the best approach for this. Is there any direct path or do I need to take n number of exams to get that certification. Also, would like to know what resources or links you'll can share that can help me possibly get through to that certification...most likely in first attempt. I only would choose one exam but it should be more focused towards honing my existing DBA skills.

https://buildazure.com/2017/02/14/mcse-data-management-and-analytics-certification/

Thanks


texts: Oracle 19c Standard Edition RPM installation

texts: I'm wondering if it's possible to install Oracle Standard Edition from RPM, not Enterprise Edition. It's possible to choose edition when you do 'runInstaller', is it possible to set some parameter during rpm installation?


texts: Managing the Service accounts in large environment

texts:
I wanted to know if there's a best practice in regards to secure a large environment of SQL Server instances (version 2000-2012).

Currently the SQL services are running with many users and I'm trying to improve the situation.
I know that the best practice is to use a domain account.

But what is the best practice for a large environment ?
Should I create a domain user that will be the service account for each service? For each Server? For the entire list of servers?

I would be happy to hear from your experience,

Thanks,

Roni.


texts:
I am trying to speed a query that grabs data to be used to display a graph for a research project. It's currently VERY slow. It has 18295603 items only and takes about 20s per query. Here is an example query:

SELECT `data_dylos`.`id`, `data_dylos`.`node_id`, `data_dylos`.`dylos_bin_1`,
`data_dylos`.`dylos_bin_2`, `data_dylos`.`dylos_bin_3`,
`data_dylos`.`dylos_bin_4`, `data_dylos`.`big_particles`,
`data_dylos`.`small_particles`, `data_dylos`.`added_on`,
`data_dylos`.`last_modified`, `data_dylos`.`reading_time` FROM `data_dylos`
 WHERE (`data_dylos`.`added_on` > '2014-08-06 23:00:24'  AND
`data_dylos`.`node_id` = 3 ) LIMIT 21

The added_on value is calculated before the call is made.

Here is my show create table data_dylos:

CREATE TABLE `data_dylos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `node_id` int(11) NOT NULL,
  `dylos_bin_1` int(11) DEFAULT NULL,
  `dylos_bin_2` int(11) DEFAULT NULL,
  `dylos_bin_3` int(11) DEFAULT NULL,
  `dylos_bin_4` int(11) DEFAULT NULL,
  `reading_time` datetime DEFAULT NULL,
  `added_on` datetime NOT NULL,
  `last_modified` datetime NOT NULL,
  `big_particles` int(11) DEFAULT NULL,
  `small_particles` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `data_dylos_1eac40ef` (`node_id`),
  KEY `data_dylos_aa73cdcc` (`added_on`)
) ENGINE=InnoDB AUTO_INCREMENT=20029122 DEFAULT CHARSET=latin1

The fields of interest ARE already indexed but it is still very slow.

Any help will be greatly appreciated.

Thanks!


texts: How to find out if entity is used in relations somewhere in database?

texts:
I have an entity/table of employees. Somewhere in database (in many places actually) this entity can be used in relations/foreign keys.

The task is simple: if the employee is used somewhere, I shouldn't show "delete" button, otherwise employee can be deleted.

The question is not about how to achieve deleting (cascade/restrict foreign keys), it's more about how to return to client side information about "if the employee is used somewhere".

I use Laravel's ORM, and the thing is it feels quite clumsy to just to join / try to find employe in many places in DB manually (like, do many joins and wheres in order to try to find out if employee is used or not).

Is there any common solution for this? Even if I do it like I described it's going to be cumbersome. Or I should add a column like "is_taken" and handle the value programmatically on the server side?


texts:
New as DBA and I feel really confused with this...

4 users working, doing stuff with macros, commited_snapshot ON, 90% cpu ran sp_blitz and found that 1 query is using more than 50 plan caches...

Ran sp_blitzcache to find that this query is using % CPU (type): 70

SELECT
999999 AS RECNO,
CAST(db_name() AS VARCHAR(128)) AS CATALOG_NAME,    CAST(user_name(o.uid) AS VARCHAR(128)) AS SCHEMA_NAME,
CAST(o.name AS VARCHAR(128)) AS TABLE_NAME,
CAST(x.name AS VARCHAR(128)) AS INDEX_NAME,
CAST(c.name AS VARCHAR(128)) AS COLUMN_NAME,
xk.keyno AS COLUMN_POSITION,
CAST(NULL AS VARCHAR(1)) AS PKEY_NAME,
(CASE WHEN x.status & 0x800 <> 0 THEN 4 ELSE 0 END) +     (CASE WHEN x.status & 0x2 <> 0 THEN 2 ELSE 1 END)     AS INDEX_TYPE,
(CASE WHEN indexkey_property(x.id, x.indid, 1, N'isdescending') <> 0 THEN 'D' ELSE 'A' END) AS SORT_ORDER,
CAST(NULL AS VARCHAR(1)) AS FILTER
FROM
sysobjects o, sysindexes x, syscolumns c, sysindexkeys xk
WHERE     o.id = x.id
AND o.id = c.id
AND o.id = xk.id
AND x.indid = xk.indid
AND c.colid = xk.colid
AND xk.keyno <= x.keycnt
AND o.xtype <> 'S'
AND LEFT(x.name, 8/*_WA_Sys_*/) <> '_WA_Sys_'
AND o.name LIKE 'Triangle'
ORDER BY x.name, xk.keyno

'Triangle' is one of the database tables, but I don't really know what the query does or if this is a query generated by the application the users are using.

This other query gets % CPU (Type): 22

SELECT
999999 AS RECNO,
CAST(db_name() AS VARCHAR(128)) AS CATALOG_NAME,    CAST(user_name(o.uid) AS VARCHAR(128)) AS SCHEMA_NAME,
CAST(o.name AS VARCHAR(128)) AS TABLE_NAME,
CAST(x.name AS VARCHAR(128)) AS INDEX_NAME,
CAST(c.name AS VARCHAR(128)) AS COLUMN_NAME,
xk.keyno AS COLUMN_POSITION,    CAST(NULL AS VARCHAR(1)) AS PKEY_NAME,    (CASE WHEN x.status & 0x800 <> 0 THEN 4 ELSE 0 END) +     (CASE WHEN x.status & 0x2 <> 0 THEN 2 ELSE 1 END)     AS INDEX_TYPE,    (CASE WHEN indexkey_property(x.id, x.indid, 1, N'isdescending') <> 0 THEN 'D' ELSE 'A' END) AS SORT_ORDER,    CAST(NULL AS VARCHAR(1)) AS FILTER   FROM
sysobjects o, sysindexes x, syscolumns c, sysindexkeys xk
WHERE     o.id = x.id
AND o.id = c.id
AND o.id = xk.id
AND x.indid = xk.indid
AND c.colid = xk.colid
AND xk.keyno <= x.keycnt
AND o.xtype <> 'S'
AND LEFT(x.name, 8/*_WA_Sys_*/) <> '_WA_Sys_'
AND o.name LIKE 'Vector'
ORDER BY x.name, xk.keyno

Vector is another Database table...

When I execute both of them returns 0 rows,

What can I do? I don't know what to do at this point...


texts:
I got below error after rebooting one of my production SQL server 2012 enterprise,

  The log scan number (151082:691:0) passed to log scan in database 'master' is not valid. This error may indicate data corruption or that
  the log file (.ldf) does not match the data file (.mdf). If this error
  occurred during replication, re-create the publication. Otherwise,
  restore from backup if the problem results in a failure during
  startup.

This seems master database is corrupted, the SQL server service is not starting, I have latest full back of master, where do I restore master when sql service is not running and can I start sql server in single user mode on same server when master corrupt? If I restore master what will happen to user dbs? is there any difference in restoring master and re-installing sql server?

Very much appreciate your replies.

Thanks


texts: log file very large how to apply changes then truncate sql server 2008 r2

texts:
As a developer from time to time I will need to restore a local copy of our live database for testing purposes. I 'm running into disk space issues because the logs are bloody 15gb. I was told the transaction log is basically a log of the operations that were executed on that db and the db never actually updates. It simply reflects whats in the log.

If this is true then how can apply those changes mention in the log then truncate it so it's not 15gb


texts:
I have a write-intensive MariaDB with both NVMe SSD and HDD disks. I recently enabled page compression (innodb_compression_default=ON). I encountered two problems:

The database gets slow after a while. INSERTs get noticeably slower after one day. When restarting the machine or MariaDB, it gets backs to normal speed.

It takes a long time to shutdown MariaDB (~30 min). During this time, the SSDs have heavy disk activity (as checked by iostat). Note that it is not flushing the data into the disk. Before shutting down, the number of dirty pages is zero, and there is absolutely no activity in show engine innodb status \G.

I think it might be due to the rearrangement of data on the SSD drives (about 50% full). Some sort of activity like SSD TRIM.
How can I diagnose the underlying causes?
This is part of my config, which might be relevant
key_buffer_size     = 20M
max_allowed_packet  = 5G
thread_stack        = 256K
thread_cache_size       = 8
innodb_buffer_pool_size = 50G
innodb_log_buffer_size = 128M
innodb_log_file_size = 10G
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_flush_log_at_trx_commit = 0
innodb_compression_level = 6
innodb_autoinc_lock_mode = 0
innodb_io_capacity=2000
innodb_io_capacity_max=30000
innodb_max_dirty_pages_pct=0
innodb_doublewrite = 0
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth=128
tmp_table_size=2G
max_heap_table_size=512M
innodb_purge_threads=8
innodb_purge_batch_size=600
innodb_change_buffer_max_size=50
innodb_buffer_pool_load_at_startup=OFF
innodb_buffer_pool_dump_at_shutdown=OFF
innodb-fast-shutdown=0
innodb-ft-result-cache-limit=4G
innodb_compression_default=ON


texts: Postgres is returning the “column reference '' is ambiguous” message inside a function even with aliases

texts:
I have a function like this:

CREATE OR REPLACE FUNCTION test() RETURNS
TABLE(my_date date, gates smallint, area_type character(1),
      area_sizes float[], area_canvases float[]) AS $$
BEGIN
   return query
   select coalesce(s.my_date, r.my_date) as my_date,  coalesce(s.gates, r.gates) as gates, coalesce(s.area_type, r.area_type) as area_type, s.area_sizes, r.area_canvases
   from (select my_date, gates, area_type, area_sizes from table1 where area_type = 'X') s
   full join (select my_date, gates, area_type, area_canvases from table2 where area_type = 'Y') r
   on r.my_date = s.my_date;
END $$ language plpgsql;

When I run it with select * from test(); what I get is:

  ERROR:  column reference "my_date" is ambiguous
  LINE 4:        my_date,

The query if not wrapped inside the function works properly, but I cannot make it work inside it. Any idea?


texts: Retrieving users by their age efficiently

texts:
I have a PostgreSQL database of users and their birthdays and want to get all users in a specific age range:

SELECT * FROM users
WHERE age(birthday) >= 21 AND age(birthday) <= 30;

How can I achieve that this query stays efficient, even when there are millions of users in the database?
Of course there are more filters, I just don't want to calculate the age for every row.

I do not want to use a materialized view.


texts:
create table #testing(id int, name varchar(50))
go

Insert Into #testing
Values(231, 'fasd')

Insert Into #testing
Values(232, 'dsffd')

Insert Into #testing
Values(233, 'xas')

Insert Into #testing
Values(234, 'asdasd')

create table #testing2(id int, name varchar(50))
go
Insert Into #testing(id)
Values(231)

Insert Into #testing(id)
Values(232)

Insert Into #testing(id)
Values(233)

Insert Into #testing(id)
Values(234)
go

update m
set name = x.name
from #testing2 m
join #testing x
on m.id = x.id
Where m.name is null

Without an index on #testing and #testing2

index on id column on both #testing and #testing2

index is

create nonclustered index ix_test1 on #testing1(id) include (name)
create nonclustered index ix_test1 on #testing2(id)

What would be the best possible index on this situation? Is it right to not have an index on #testing(name) because the write/update would be slower?


texts: Some data is missing in the table of SQL

texts:
I have given a description column datatype as NVARCHAR(MAX) but when I add the data it shows me only half  data. Not full data. For Example:-
If I have two para like below:-

As the saying goes “Seeing is Believing”, based on this premise Aditya Jyot was founded in 2005 to illuminate the lives of the less privileged people suffering ailments related to visual impairment. The organisation aims at redressing the suffering of low income groups, marginalised population living in the rural and urban slums and tribal pockets through free eye camps and treatment.
The foundation in association with us (RBL Bank) aims at spreading awareness and implementing preventive healthcare services on Retinopathy in diabetic patients causing blindness through a well-equipped outreach centre in the urban slums of Dharavi, Mumbai touching about 6 lakh beneficiaries. Thus, contributing towards creating healthier communities

I will only see the data till Treatment. I am not able to see the data which is ahead. Please suggest


texts:
I'm running SQL2019 CU4 Linux and seeing a high number of PWAIT_EXTENSIBILITY_CLEANUP_TASK. Can anyone share their insight on what this wait type means and how we can reduce it?

Select * from sys.dm_os_wait_stats
WHERE
wait_time_ms<>0
and wait_type='PWAIT_EXTENSIBILITY_CLEANUP_TASK'
order by signal_wait_time_ms desc


texts: Incorrect syntax near 'role' with generated script

texts:
I've recently had to recreate a user several times.
To facilitate the creation of the user, I would right-click the user and choose script login as, create to, New Query Editor Window.

However, after doing this I receive a syntax error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ROLE'.

What is going on here?


texts: Speed-- Restoring from Azure Blob to local database

texts:
We're being encouraged to move our database backups to Azure blobs, which we access through the Microsoft Azure Storage Explorer. We're running SQL Server 2017.

Our backup times are fine-- 20 minutes for a ~15GB database that is compressed, encrypted, and checksum'd.

However, we're getting killed on restore times. That 15GB database takes over 2 hours to restore!

I can download the full backup file from Azure to my local PC in about a minute or two, so it seems like network performance is fine.

It only takes about 12 minutes to restore the backup locally (same file, but on another VM rather than in Azure).

Any ideas or recommendations about what could cause restoring a database from Azure (via URL) to take hours and hours, and what we can do about it? Glad to provide more information if it'll help. I appreciate all of your help!


texts:
I need to generate a report that displays the account status of all schemas, which I've got.

SELECT
''''|| USERNAME ||''''     USERNAME,
''''|| ACCOUNT_STATUS||'''' ACCOUNT_STATUS,
''''|| LOCK_DATE ||'''' LOCK_DATE,
''''|| PROFILE ||''''     PROFILE
FROM DBA_USERS
WHERE PROFILE='DEFAULT'
ORDER BY 1,2;

However the requirement also states that they want the database name on every row in the report, so...

SELECT
''''|| NAME ||''''
FROM V$DATABASE

My SQL is pretty poor so I tried some sort of mismashed join, but realised that won't work... any ideas?


texts: Function execute query, manipulates result and then return the same result

texts:
I need a function that does something like this pseudocode:

function get_data() RETURN SET OF something... as
BEGIN
    myResultSet = select id, some_other_column from ...... limit 20000;
    update some_other_table set status = 2 where id in (myResultSet.id);
    RETURN QUERY myResultSet;
END;

In other words, I need to execute a query, use the IDs I got in other update statement and then return this same query result.

I was wondering if there is a way to store a resultset in some kind of variable, but my research didn't find any good results. Any other kind of solution is appreciated, since I don't need to execute my same query twice.


texts: Microsoft recommends using Rowversion over timestamp, but I can not find "rowversion" data type inside my sql server 2008 R2

texts:
Microsoft here mentioned that we should use the rowversion data type over Timestamps. Now inside my SQL Server 2008 R2, I want to create a column with the Rowversion data type. But in my SQL Server 2008 R2 Management Studio, I cannot find such a data type:

Also when I tried altering existing column named timestamp which is of type timestamp using this statement:
ALTER TABLE [SkillManagement].[dbo].[Customer]
ALTER COLUMN timestamp rowversion

I get this error:
Msg 4927, Level 16, State 1, Line 1
Cannot alter column 'timestamp' to be data type timestamp

Can anyone advise?
EDIT


texts: UPDATE statement processing records that should be eliminated by WHERE clause

texts:
I am getting a very odd error. Consider the table below:

CREATE TABLE #MyTable (
    Key1 INT , Key2 INT ,
    x SMALLINT , y INT , z INT ,
    a FLOAT , b FLOAT , c SMALLINT , s FLOAT
)

-- insert many records

CREATE UNIQUE CLUSTERED INDEX CI ON #MyTable ( Key1 , Key2 )

For some reason, the following update statement tries to divide by zero. This can only happen if c=0 or c=1. The WHERE clause clearly specifies c>1.

-- this fails with divide-by-zero error
UPDATE  #MyTable
SET s = CASE
        WHEN a - SQUARE ( b ) / c <= 0 THEN 0
        ELSE ( a - SQUARE ( b ) / c ) / ( c - 1 )
    END
WHERE   x <= 622 AND c > 1 AND ( y > 0 OR z > 0 )

The problem is completely eliminated if I redundantly check for c<=1 in my CASE expression:

-- this completes without an error
UPDATE  #MyTable
SET s = CASE
        WHEN ( c <= 1 ) OR ( a - SQUARE ( b ) / c <= 0 ) THEN 0
        ELSE ( a - SQUARE ( b ) / c ) / ( c - 1 )
    END
WHERE   x <= 622 AND c > 1 AND ( y > 0 OR z > 0 )

Has anyone encountered this before? Why would SQL Server touch the records with c>1?

The problem is also avoided if there is no index on the table (the index is useful in steps later in the procedure). Why would the existence of an index cause a condition in the WHERE clause to be ignored?


texts: Which view contains all Oracle's catalog views?

texts:
I don't remember and cannot find the name of the view containing the list of all Oracle's catalog DBA_%, ALL_% and USER_% views.
Any help?


texts: Database backup taking longer than usual to a networkshare

texts:
The SQL server does backups with this command to a network share \BackupServer\weekeklybackups"

BACKUP DATABASE DBNAME TO DISK = '\\BackupServer\weekeklybackups\DBNAME.BAK'

There is a 1 gig network between the SQL Databse server and the BackupServer. but it uses maximum 25% of the network, so the backup is taking for ever.
If I make the database locally and copy it over to the same network share, it is faster using 99% of the network bandwidh.

So, the weekly automated backups are taking a long time, which is an issues

When I check the monitor it says it is waiting with "ASYNC_IO_...". When I did some google search it says, this is an issue on tape backup. But this is on iSCSI hard

disks. and manual copy over works fine. So, what could be the issue? It was working fine for a year until last week. Not sure what happened. but where should I be

looking in to?

Thanks for your time


texts: Updating whole table once, criteria : column_name and each value

texts:
I have a table with 606 rows. The first column is simply ID (starting at 1 to 606), the next column names are name1, other1, name2, other2, etc. until name100, other100.

I'm trying to create a query that would:

update each cell if column_name like "other"
if 0< cellValue < 50 then cellValue=cellValue*10
if 50<=cellValue<120 then cellValue=cellValue*5
if 120<=cellValue<230 then cellValue=cellValue*2

I don't care much about the efficiency because it is a one-shot query.

I worked on it to try to solve it. I got a complicated pseudo-code idea to explain better what I'm trying to do, but I guess it's not needed in SQL because SQL queries check every single value:

begin
for i in 1 to 100
  for each row
    if current column is named like "other"i then
      if 10000< value then value=value/40
      elseif 0< value<50 then value=value*10
      elseif 50<=value<120 then value=value*5
      elseif 120<=value<230 then value=value*2
      elseif 230<=value<500 then value=value*2
      endif
    endif
  end for
end for
end

I asked some people and they don't know either.

The result of:

SHOW CREATE TABLE example

CREATE TABLE `example`
( `id` int(11) NOT NULL,
  `name1` int(11) NOT NULL,
  `other1` int(11) NOT NULL,
  `name2` int(11) NOT NULL,
  `other2` int(11) NOT NULL,
  --
  -- 2 * 97 columns skipped
  --
 `name100` int(11) NOT NULL,
 `other100` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

I'm using:

MySQL Workbench Community (GPL) for Windows version 6.3.5 CE build 201 (64 bit)
It is a local instance of MySQL 5.7
Cairo Version: 1.10.2
OS: Microsoft Windows 8.1


texts: Make query depend on input in PostgreSQL function

texts:
I have a simple query:

CREATE OR REPLACE FUNCTION companies_all_teams(
  IN  _company_name text = NULL,
  IN  _company_id int = NULL,
  OUT _result text
)
AS
$$
  BEGIN
  _result := (SELECT json_agg(distinct(teams.name))
                     FROM jobs
                     JOIN companies
                       ON companies.id = jobs.company_id
                     JOIN teams
                       ON teams.id = jobs.team_id
                     WHERE lower(companies.name) = lower(_company_name)
                       AND jobs.is_disabled = false
             );
  END;
$$ LANGUAGE plpgsql;

There are two inputs, but only one of those is being used in the query. What is the simplest way to run the query conditionally based on which input we have?


texts: SQL Server configuration / specification recommendations and advice!

texts:
I am about to deploy a new server at work as follows:
Dell T610
2 x Xeon 2.53 Quad core
24 GB RAM
1 x Perc H700 RAID controller with 1GB Non Volatile memory
8 x 300GB 15k SAS Hard drives

I am looking for recommendations of how to best configure this for use as a SQL server.
I have the following in mind:

Option 1
Allocate a 4GB RAM drive for TempDB
Group all 8 drives into large RAID 10 array for OS, Database and Log files

Option 2
Allocate 4GB RAM drive for TempDB
Use some mixture of RAID arrays (but only have one controller)!

Option 3
Any other suggestions!

For info, the databases in question consist: 1 x 4GB under heavy Read and Write use, 1 x 4GB clone for testing purposes, 2/3 other more minor databases.


texts: How to delete from two tables same field name in single query?

texts:
Help me,

I want to delete same filed name from two tables in single query, I Attached query but it's not working...

DELETE PersonalTrainerMasterData ,PersonalTrainerCheckboxData
  FROM PersonalTrainerMasterData INNER JOIN PersonalTrainerCheckboxData
 WHERE PersonalTrainerMasterData.ButtonName = PersonalTrainerCheckboxData.ButtonName
   and PersonalTrainerMasterData.ButtonName='" + deleteButtonForm.getButtonToBeDeleted()
                                               + "'and TabID=1";


texts: Recommended Permissions for SQL Account that will be creating new SQL Accounts

texts:
Seeking some recommendations on how to handle a SQL account, that will be creating additional SQL accounts in the future...

Scenario:

Our Admin production app connects to SQL using an account called: Admin_Main, as stated in the connection string. When somebody creates a new user in the Admin app and assigns them a Reporting license, a new SQL Account is created on the instance, for the new user. The Admin_Main account is used to create this account.

I, of course, do not want to add more permissions to the Admin_Main account than needed, so my question is;

What are the permissions that I should add to this account, so that it's able to create new SQL Server accounts, but doesn't have Admin permissions on the instance?

Thanks in advance!


texts:
I'm searching for the best way to store the following information:

There are videos with different length
There are users
Users watch videos and may stop watching videos at some point or only watch a part of the video or skip a part

I want to analyse/store which part of a video is seen more often and which are skipped.

Here are the two ideas I have

Create a table with VideoId and section1, section2, ... Each section could be 10 seconds and in every row I store the videoId and a int for each section representing the number of views.
The problem is, that I don't know which is the longest video I have to store statistics for. If it is 1h, I need 361 columns.
Create a table with VideoId, section and views. This would allow videos with all lengths but will result in a really long table.

Bonus question: How to also store which user saw which video at which positions?
 My idea: Don't store an int for views but an array of userIds?

Which option is better or perhaps my ideas are completely wrong and you have a better idea?


texts: Restoring MySQL database with db dump and binlogs

texts:
I have a database running on our production web server that I have set up as a master with a single slave in another datacenter. When reading through the MySQL replication setup instructions, they advice to take the server offline, place a read lock, do the dump, and then create the slave with it. I did that, got the replication set up and we were happy.

Today I decided that having that replicate in clear over the WAN probably isn't good practice. So I looked into setting up replication through an SSH tunnel (temporary). This required me to change master_host to 127.0.0.1 instead of my Master's IP. In doing this, I borked my replication and now I have to start over. Problem is, the Master only had expire_logs_days=1, so I can't repeat the original process since it was last week. I do have backups of the other binlogs, but using mysqlbinlog to restore all of them keeps failing due to temporary tables problems.

So now I'm trying to get the slave backup and running without taking the master down. Every 3 hours, on the master, we do a database dump for backups. We use mysqldump -v --flush-logs --single-transaction --routines .... so the current binary log is cut off and a new one created with every backup we do. However, if I restore a database dump, then try and start the replication backup with the new binary log that was created with the last --flush-logs command, I still run into key collisions, just like they warned.

Given this information, is there way that I can successfully start the slave back up with dumps we have without taking the server down again? I'm not going to be in a good place if I have to go ask for more down time.


texts: changing TEMPORARY TABLE default collation

texts: when I use CREATE TEMPORARY TABLE to create a temporary table, the collation of this table is utf8_unicode_ci by default. why by default it is utf8_unicode_ci? How can I change the default?


texts:
I'm building a DB for someone as a favor and whilst I'm perfectly OK to create a DB in Azure SQL for them and do the requisite TSQL coding they require I do also need to give them a client based way to access the data that involves no non-SQL coding from me.

Ideally this would be a light weight alternative to SSMS that is compatibile with Azure. That way I can give them a series of scripts and paramtised SPs to run. Can someone recommend something that works please?

Thanks for reading

Edit: Put on hold for "will likely solicit debate, arguments, polling, or extended discussion" but this came after the question had been answered and had not caused such.


texts: Using a table swap for a data load but I have triggers

texts:
I'm working on re-writing a table load.  The current method is a daily job that deletes from the table, then re-loads it.  The complaint is that if the load fails the users have no data.  The users have said they are ok if the load fails and they have to use yesterdays data.

My solution is to do a table swap.

Create a staging table that has exactly the same structure, indexes
etc
Then the daily job does the following

Delete from the staging table
Load the staging table
Begin a transaction to keep everyone out of the tables until I'm done
Rename Live to Backup
Rename Staging to Live
Rename Backup to Staging
Commit the transaction

This works fine on 3 of the 5 tables I'm working with.  The other 2 have triggers.  Because the trigger has code that references the table name (of course) it can't handle the rename of the object it is attached to.

I start with this:

Table1_Live
  |
  -- Trigger for Table1_Live

Table1_Stage
  |
  -- Trigger for Table1_Stage

After I do my renames I get the following

Table1_Stage
  |
  -- Trigger for Table1_Live

Table1_Live
  |
  -- Trigger for Table1_Stage

The benefits to the method are that the down time for the users is minimal.  I have yesterdays data.  And if anything goes wrong I roll back to yesterdays data.

My question is does anyone know a better method without the trigger problem, or a way that I can swap my triggers between the two tables so that the code remains correct after the rename?

Oh, and I'm somewhat embarrassed to admit but even though the database is 2005 (we are upgrading soon) the requirement is that the load be done using a DTS package.  My plan is for the rest of the process to be done in a stored procedure that can be called from the DTS package (or SSIS if I can ever talk them into it).


texts: THROW is not returning the correct line number when variables are used

texts:
An often mentioned benefit of THROW in a CATCH block is that it returns correct line number where the error occurred.
I noticed that it only does this when I use THROW without any variables.
Using the format below, with my own error number and message returns the line number of the THROW itself (just like RAISERROR does).

THROW 50055, 'my message', 1;

Is this to be expected and a known flaw or am I doing something wrong?

I'm using SQL Server 2017 CU20.


texts: How to get the second string in the ORA-02296 error to show something meaningful

texts:
Whenever I run the following script*:
ALTER TABLE ANIMAL.MONKEY MODIFY TAIL_COUNT NOT NULL;

I receive the following error:

ORA-02296: cannot enable (ANIMAL.) - null values found

I have a script that modifies a large number of tables.  It would be really helpful if the error message read:

ORA-02296: cannot enable (ANIMAL.MONKEY) - null values found

... or something else that identifies either the problem column or the table in which it resides.
ORA-02296 has the following detail about it:

ORA-02296 cannot enable (string.string) - null values found
Cause:    Obvious
Action:   an alter table enable constraint failed because the table contains values that do not satisfy the constraint.

This suggests there is a second string available somehow.  How do I get the error to display more meaningful information?
*names have been changed to protect the innocent.


texts: Load Balancing SSRS inside an AlwaysOn Availability Group

texts:
We have 3 nodes in an Availability Group in SQL 2014:

Primary Site:

Server1 (synchronous primary)
Server2 (synchronous, readable secondary)

DR:

Server3 (async, nonreadable secondary)

We use Reporting Services and have it installed and licensed on all 3 servers.

When processing reports we use the availability group name to connect to the server, so that when we failover reports will continue to work (e.g. we connect to http://sqlAAGListener/reports). This has been working great for us, but we are getting to the stage where we'd like to balance the reports between the 2 servers on the primary site.

We have tested connecting directly to http://server1/reports and http://server2/reports and all of our reports work just fine on both servers.

MSDN suggests that the right way to do this is to have the report server database as part of the Availability Group (as we currently do), and then running 2 or more Reporting Services instances on separate servers and then put a load balancer (e.g. Windows Load Balancing service) in front of them. I see the advantages of doing this, however we don't have a budget for 2 more servers of Enterprise Edition, so it's not an option.

Instead, can we keep our SQL Connections pointing to the availability group listener, and install Windows Load Balancing on Server1 and Server2 to balance SSRS or will this cause problems?


texts:
In pgadmin, I selected a table from my production database I want to export. I right clicked and selected backup. I set the filename to devices.sql and selected plain as format. Then I clicked the backup button and it created a sql file on my computer. Now I want to import the contents of that table into my development database. So I run the psql utility and enter the following:

\i /Users/GuardDog/Documents/devices.sql

But it gives me this error:

SET
SET
SET
SET
SET
SET
SET
SET
SET
psql:/Users/GuardDog/Documents/devices.sql:34: ERROR:  relation "devices" already exists
psql:/Users/GuardDog/Documents/devices.sql:37: ERROR:  role "xtremek" does not exist
psql:/Users/GuardDog/Documents/devices.sql:50: ERROR:  relation "devices_id_seq" already exists
psql:/Users/GuardDog/Documents/devices.sql:53: ERROR:  role "xtremek" does not exist
ALTER SEQUENCE
 setval
--------
      2
(1 row)

ALTER TABLE
psql:/Users/GuardDog/Documents/devices.sql:101: ERROR:  multiple primary keys for table "devices" are not allowed

I get quite a few errors here. How can I import the data from my production server to development server in pgadmin/PostgreSQL 9.1.2 ?


texts:
I have 2 tables:

Organization (2.500.000 rows)
City (35.000 rows)

'Organization' has 'city_id' set as foreign key to 'cities'.'id':

Organization:

ALTER TABLE public.organization
  ADD CONSTRAINT organization_pkey1 PRIMARY KEY(id);

ALTER TABLE public.organization
  ADD CONSTRAINT organization_city_id_381976b6f7d84e86_fk_city_id FOREIGN KEY (city_id)
      REFERENCES public.city (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;

CREATE INDEX organization_c7141997
  ON public.organization
  USING btree
  (city_id);

City:

ALTER TABLE public.city
  ADD CONSTRAINT city_pkey1 PRIMARY KEY(id);

Query below takes 6.2 seconds to finish:

SELECT
    org.id
FROM organization AS org
INNER JOIN city ON city.id = org.city_id
WHERE org.id>=1 AND org.id<=3736809
LIMIT 1000 OFFSET 100000;

Explain analyze result:

Limit  (cost=22129.97..22340.23 rows=1000 width=4) (actual time=6268.343..6274.803 rows=1000 loops=1)
  ->  Hash Join  (cost=1104.52..521879.44 rows=2476878 width=4) (actual time=29.056..6259.761 rows=101000 loops=1)
        Hash Cond: (org.city_id = city.id)
        ->  Seq Scan on organization org  (cost=0.00..465045.17 rows=2476878 width=8) (actual time=0.014..6026.781 rows=101000 loops=1)
              Filter: ((id >= 1) AND (id <= 3736809))
        ->  Hash  (cost=694.23..694.23 rows=32823 width=4) (actual time=29.014..29.014 rows=32823 loops=1)
              Buckets: 4096  Batches: 1  Memory Usage: 1154kB
              ->  Seq Scan on city  (cost=0.00..694.23 rows=32823 width=4) (actual time=0.008..13.538 rows=32823 loops=1)
Total runtime: 6275.272 ms

Is this an expected query performance ?

How to speed query execution ?

p.s. - i include some of my settings below

PG 9.3 on MacOS 10.9.5 (8GB ram)

Postgresql.conf:
default_statistics_target = 50
maintenance_work_mem = 448MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 5632MB
work_mem = 44MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 1792MB
max_connections = 80


texts: Low Query cache efficiency after mariadb 5.5 migration

texts:
We change from Percona MySQL server 5.5 to MariaDB but performance is not as good as we think.
This graph is from a day in MariaDB. You can see the low performance in terms of cache_hits:

We have others servers with Percona MySQL server 5.5. The graph below is one of them:

We don't know how there is only 46% of query cache efficiency.
With Percona MySQL server was near 90%.

This is my.conf from MariaDB server:

#
# * Fine Tuning
#

bulk_insert_buffer_size = 16M

concurrent_insert       = 2
connect_timeout         = 5

interactive_timeout     = 14400
join_buffer_size        = 1G
key_buffer              = 256M
key_buffer_size         = 256M
max_allowed_packet      = 16M
max_connections         = 200

myisam_recover          = BACKUP
myisam_sort_buffer_size = 500M

open_files_limit        = 20000
table_open_cache        = 5000
table_definition_cache  = 3000

query_cache_limit       = 16M
query_cache_size        = 1G
query_cache_min_res_unit = 4K

read_buffer_size        = 8M
read_rnd_buffer_size    = 4M

sort_buffer_size        = 512K

aria-pagecache-buffer-size = 256M
key-cache-segments      = 8

thread_cache_size       = 128
thread_concurrency      = 8
thread_stack            = 192K

tmp_table_size          = 2G
max_heap_table_size     = 2G
wait_timeout            = 14400

mysqltuner:

[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM +SPHINX
[--] Data in InnoDB tables: 8M (Tables: 10)
[--] Data in MyISAM tables: 872M (Tables: 146)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 11

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 20h 47m 59s (40M q [248.063 qps], 2M conn, TX: 87B, RX: 3B)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 2.9G global + 1.0G per thread (200 max threads)
[!!] Maximum possible memory usage: 204.3G (651% of installed RAM)
[OK] Slow queries: 1% (529K/40M)
[OK] Highest usage of available connections: 11% (23/200)
[OK] Key buffer size / total MyISAM indexes: 128.0M/92.7M
[OK] Key buffer hit rate: 100.0% (61B cached / 44K reads)
[OK] Query cache efficiency: 45.3% (28M cached / 62M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (111 temp sorts / 1M sorts)
[!!] Temporary tables created on disk: 48% (1M on disk / 2M total)
[OK] Thread cache hit rate: 99% (23 created / 2M connections)
[OK] Table cache hit rate: 68% (370 open / 541 opened)
[OK] Open file limit used: 2% (517/20K)
[!!] Table locks acquired immediately: 93%
[OK] InnoDB buffer pool / data size: 256.0M/8.4M
[OK] InnoDB log waits: 0


texts:
I want to create a cursor (without creating a temp table) to get all orphan users in my SQL Server instances. Here is what I've come up with:

Declare @Orphan_users table (username sysname)
DECLARE @orphan_users_cursor CURSOR
set @orphan_users_cursor = cursor
FOR
select * from (
insert into @Orphan_users
set EXEC SP_MSFOREACHDB' USE [?]
SELECT dp.name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp
    ON dp.SID = sp.SID
WHERE sp.SID IS NULL
    AND authentication_type_desc = ''INSTANCE'''--)

OPEN @orphan_users_cursor
FETCH NEXT FROM @orphan_users_cursor INTO  @Orphan_users


texts: Oracle 11.2.0.4 -- sql_id not found in v$sql or v$session

texts:
I was looking in v$session_longops and found an operation currently being run and its corresponding SQL_ID

SELECT sql_id, sid, to_char(start_time,'hh24:mi:ss') stime, time_remaining,
message,( sofar/totalwork)*100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1
/
SQL_ID  SID STIME   TIME_REMAINING  MESSAGE PERCENT
381hwy1dbtntp   104 09:36:57    157 Index Fast Full Scan:  SOME_USER.SOME_TABLE: 12715 out of 36702 Blocks done 34.64388861642417306958748842024957767969
0htuhsqj193gf   184 09:15:21    4488    Table Scan:  SOME_USER.SOME_TABLE: 7028 out of 13568 Blocks done    51.79834905660377358490566037735849056604

When I look for the user session running this based on the SQL_ID I didn't find any session  running the first one. (381hwy1dbtntp)

select distinct sql_id from v$session;

SQL_ID

fwxv8a99vkg9r
1cwb8j02491w0
gypnfv5nzurb0
0htuhsqj193gf
2b064ybzkwf1y

I also looked in v$sql and could not find any records with this sql_id. Could someone help out with some pointers explaining the reasoning behind this?

Thanks


texts: Four db servers all identical, one is much slower than the others

texts:
The setup

We have an application loading a lot of transaction data for later querying. It's sharded on 4 SQL Server 2008R2 Enterprise Edition on Windows Server 2008 64 bits. All servers are configured exactly the same (or so I'm told). 72GB of memory, 2x6 core CPU, each attached to their own SAN. The databases are currently about 2TB in size.

They all load about the same number of transactions daily (+/- 10%), it's mostly inserts. Almost no updates. The process is I/O bound, the CPUs sits at 10-15% usage.

The problem

One of the servers is much slower than all the others. It only inserts transactions at a quarter of the speed of the other three. It has always been like this. It eventually catches up during quieter hours, but the other servers keep up with traffic at all time.

The SAN people have told us that all four SANs are performing the same, with no errors. Looking at the I/O graph on the Activity Monitor, the I/O is about the same on all servers.

I have extracted the schema for the databases and compared them, no difference. There doesn't seem to be any difference in the SQL Server configs either. They all use about the same amount of memory with the same distribution.

The question

What else should I be looking at?

We have been looking at this for months and can't come up with anything. There must be some explanation. I've looked at a lot of different performance metrics and I can't seem to find any glaring difference except that the overall inserts are slower.


texts: How SQL determine page exists in Buffer pool (internal)

texts:
When we execute SQL query, then Relational Engine optimize it and pass query plan to Storage Engine which then ask for a page with actual data.

If data page isn't in memory, then SQL pull those pages from disk and place it in RAM.

How, in future, SQL determine if page, which Storage Engine asks, is already in memory? And which algorithm it uses to keep some free space for new pages?


texts:
I'm checking into a buffer pool issue on a MySql database. According to the query to get database size shown here, the total database size is about 11GB. Unfortunately I know I have much less RAM than that; only about 4.5GB total for this VM. So I check InnoDB buffer pool usage, expecting to see it almost at capacity. Instead I see the opposite:

show engine innodb status \G;

...
Buffer pool size    163839
Free buffers        132864
...

It seems the buffer pool is hardly touched. And, yes, these are all InnoDB tables:

SELECT Engine, COUNT(*) "Count"
FROM Information_Schema.Tables
WHERE table_schema = 'My_Database'
GROUP BY Engine;

Engine   Count
-----    -----
InnoDB    169

What could be going on here?


texts: Calling another function inside a postgres function

texts:
I have a function A and function B.

Function A Name is f_a() and the code is:

Select id,address from A;

Function B Name is f_b() code is:

Select C.address,B.name
From
(Select id,name from B,
(Select * from f_a()) as C
Where C.id = B.id;

However, when I execute the select * from f_b() the Error message says C.id does not exist. How can I run the function f_a() inside f_b().

Above function A and B are functions which returns table. I omitted the function head and end.


texts: How do I force a selection of items to a specific user/database row?

texts:
I have a database that allows a company to register, then the user of the company can register individual "user" accounts.

The company administrator (who sets up the company account) can log in with a password, however, the user accounts he/she then creates are assigned a smart card based on a smart card serial number.

this works, however, I would like to restrict the selection of smart cards to those only that have been issued for the selected "company".

Currently, I have a form that allows me to create a range of smart cards.

I have a drop down look up for the user that allows me to select a smart card, however, it will show all available smart cards that I have created.

When would it best be places, and how would it best be achieved to ensure only a range of smart cards are available when a company administrator is creating individual accounts?

I have uploaded a pic of my current schema and I am currently using Microsoft access for concept design but will be moving to SQL and using asp.net.

Thanks in advance.
Stephen


texts:
Can anybody shed some light on how these SQL products compare? No need for essays, only highlights such as "X supports this, while Y doesn't".

How is efficiency affected as the database grows larger

Inserts
Updates
Selects

What kind of transactions are supported
What support is there for multiprocessing

Access from concurrent processes
Access from concurrent threads

Data access is in-process or via server process
Key feature of either product that is worth mentioning

Also, any (positive or negative) experiences from people who have used both products (don't want opinions - just observations).

The reason I'm asking is that it's hard to penetrate the marketing fluff surrounding Microsoft's embedded SQL product.

Thank you.


texts: [INS-30131]Initial setup required for the execution of installer validations failed. Encountering this error in Fedora 20

texts:
While I was installing Oracle 12c database on Fedora 20 I encountered this error which wasted my 2 days.

"[INS-30131] Initial setup required for the execution of installer validations failed."

In the details it says this:

Cause - Failed to access the temporary location.
Action - Ensure that the current user has required permissions to access the temporary location.
Additional Information:
 - Framework setup check failed on all the nodes
     - Cause: Cause Of Problem Not Available
     - Action: User Action Not Available Summary of the failed nodes fedora
 - Version of exectask could not be retrieved from node "fedora"
     - Cause: Cause Of Problem Not Available
     - Action: User Action Not Available

I have tried a lot of things like restarting the computer and changing the permissions of the tmp folder etc. But none of these methods worked.

Plz help me out from this. Any help will be appreciated.


texts: Transactional Replication snapshot locking Publisher db from beginning

texts:
I have recently upgraded my remote distributor to 2008r2. I am getting complete system locks when attempting to generate a new snapshot in the day when we have added an article to the publication (we could do this in the past with very little issues).

I have checked sp_Helppublication and we are using synchronization method 3 (concurrent), so no locks should be held until the very end.

I have been trying to following this link as I am only adding an additional table with 26 rows but don't want to reinitialize and have the whole snapshot delivered.

http://deepakrangarajan.blogspot.co.uk/2009/01/sql-2005-transaction-replication-adding.html

I attempted to run this code

EXEC sp_changepublication
@publication = 'statics',
@property = N'immediate_sync',
@value = 'false'
GO

but this is also locking up the system completely. I guess I will have to run in the middle of the night but thought that you should be able to run this as just a config change to the publication.

Also when I try the last step in the link to add it to the subscription

EXEC sp_addsubscription
@publication = 'statics',
@article = 'dbo.SupplierCorporatePayAccountLink_tbl',
@subscriber = 'PEARLJAM',
@destination_db = 'MLT-Replicated',
@reserved='Internal'

I get the following error message

  Msg 14058, Level 16, State 1, Procedure sp_MSrepl_addsubscription_article, Line 331
  Cannot create the subscription because the subscription already exists in the subscription database. Only one subscription to the same publication is allowed in each subscription database. Drop the subscription and add it again if necessary. If the problem persists, replication metadata might be incorrect; see Books Online for troubleshooting information.

After the snapshot, how can I just have the new article added to the subscriber?


texts:
I have currently running a long update (~million rows based on a postgis st_within condition) that was expected to finish in an hour or so based on similar previous queries. However, it's been three hours already; system monitoring shows that it's pegged a single CPU at 100% for that time.
Is there any way to view the progress of this update, to estimate when it is expected to end?


texts: I cannot get my data source to connect to an SQL Server named instance using SSRS Report Builder

texts:
I have a report in SSRS and I want to update the data source to use a named instance instead of the default instance. The server host will remain the same.

I thought this would be easy. I simply changed this:

clever-sql1

to this:

clever-sql1\MSSQLSERVER02

When I click "Test Connection" in the Connection Properties, the test succeeds.

Next, I click "OK" and "OK" again to close the Connection properties. I can see my connection string has been generated, and I click "Test Connection" again, but this time, the connection fails.

These are the things I have tried to fix the problem:

Checked the SQL configuration and made sure named pipes and TCP are enabled
Can confirm that remote connections are possible via the management studio
Disabled the firewalls on my PC and the server
I discovered that the named instance does not use default SQL port, but uses port 8000 (which is open on the firewalls). I tried specifying the port number in the connection string like this:

Data Source=clever-sql1\MSSQLSERVER02,8000;Initial Catalog=cleverdataout

This generated a different error:


texts: Dynamically deciding which operator to use based on the type of the value at hand

texts:
I'm currently implementing a translation between a proprietary expression language and postgres queries at work. So far a lot of stuff fell in place quite nicely, but now I have trouble with one function.
The source language contains a function (contains) that checks whether the first argument contains the second. That means for

strings: It checks whether the second arg is a substring of the first arg.
arrays: The second arg is an element of the array (first arg)
objects/maps/dicts: The second argument is a key of the object (first arg)

Now comes the issue: The translation doesn't know anything about the DB schema, so the queries need to be generic enough to work on different kinds of schemas. So I don't know whether a given field is of a specific type. It is okay to fail in some cases, but I would at least like the code to work for the types text/varchar , text[] and jsonb. But I'm struggling to get even the first two to work.
Here is the (incomplete) WHERE clause of a query that is generated by the translator:
CASE
    WHEN pg_typeof(entity.field1)::text = ANY('{text,character varying}') THEN ( entity.field1 LIKE '%' || entity.field2|| '%' )
    WHEN pg_typeof(entity.field1)::text = 'text[]' THEN ( entity.field2=ANY(entity.field1))
    [...]
    ELSE false
END

Let's assume (for now) that entity.field2 is a text column. entity.field1 is

a) a text column
b) a text[] column

The query works fine for a), as expected. But it fails for b) with:
operator does not exist: text[] ~~ text

So I broke the query down to:
CASE
    WHEN false THEN ( entity.field1 LIKE '%' || entity.field2 || '%' )
    ELSE false
END

For a) false is returned, as expected. For b) the error is still thrown. So it seems like the operator type check is run, even if there is no way to reach the code in the THEN clause.
I tried to find the reason for that in the docs but I didn't. This paragraph contains some info on early evaluation, but not related to operator type checks.
So, is there a way to get this to work by catching errors, casting (have tried naively casting to text, that works for text[] but not for jsonb), or anything else?
Thanks in advance for your help, it is greatly appreciated.


texts: Is it possible to develop a SSIS with a SQL Server Evaluation Version

texts:
We are thinking about buying an SQL Standard Server for implementing an ETL via SSIS. Since it is very expensive for us I would like to test developing SSIS Packages on a free version. Since the express version does not integrate the SSIS I want to try it on the Evaluation 180 Expires Version of the SQL Server 2014 but cannot find anything about

- Is it possible
- Are there limitations.

Could anyone help me here?


texts: Long running READ queries stuck in "idle in transaction"

texts:
I have a JDBC connection pool in a web API that makes frequent requests to the RDS Instance. Each request is for a long running SQL query where PG decides not to use indices because the amount of data to be returned is quite large.

I'm noticing that some of these long running queries are just stuck in state idle in transaction. When look up the pid for these processes in pg_locks table they have 152 rows . With mode AccessShareLock.

The intent for this table is to be never updated -- It serves as a point in time table. So I don't see a need for idle transaction or accesssharelock. Is there I can turn these off so that my queries run faster and aren't stuck because of AccessShareLock?


texts: pg_restore ERROR: "Relation does not exist" and creating new database

texts:
I have made a backup of my specific tables that I want to restore into a new database using:

call pg_dump -Fc -h server -d database -U user -p password -v -f dump.sql -t public.table1 -t public.table2

And I have no problems.

I then want to restore the dump by creating a new database with pg_restore using:

call pg_restore --clean --create -d temp -h server -p password -U user dump.sql

This gives me a "database temp does not exist" error. This should have created the database and restored it as far as I understand.

I However then create the "temp" database manually and run:

call pg_restore --clean --create -d temp -h server -p password -U user dump.sql

This follows through, but does not create the tables and gives me an error "relation table1" and "relation table2" does not exist and only creates the corresponding id_sewuences for the two tables.

What I actually want is to not have to manually create the new database and that all tables in the backup is restored into a brand new database via pg_restore using:

call pg_restore --clean --create -d temp -h server -p password -U user dump.sql

as I understand it.

Please Help, very frustrating


texts: What are the different ways to restore a database?

texts: From the most inoffensive of the cases, to the worst of the scenarios they know. What are the different ways to restore or recover a MySQL database? I refer to the use of full backups and incremental backups, among other ways to do it. I do not know if it can be done through ibdata and iblogfiles, is what I need to know ... Thanks in advance.


texts: How to make conditional join when containing Nulls in some columns use for join

texts:
Is there a way to make the following join more efficient? It needs to join the following:

Always on dset_cob_dt and tlsn_trd_id
In addition if the meas_data.tlsn_leg_id is not null it needs to join on tlsn_leg_id as well
and in addition to that also on tlsn_vrsn_num if similar to the last one meas_data.tlsn_vrsn_num is not null.

The below query seems to be incorrect. What would be the correct way to do it?

Select...
     left outer join tlea
      on (dset_cob_dt = tlea_cob_dt
          and
          ((meas_data.tlsn_trd_id = tlea.tlsn_trd_id
          and meas_data.tlsn_vrsn_num = tlea.tlsn_vrsn_num
          and meas_data.tlsn_leg_id = tlea.tlsn_leg_id)
          or (meas_data.tlsn_trd_id = tlea.tlsn_trd_id
          and meas_data.tlsn_vrsn_num = tlea.tlsn_vrsn_num)
          or (meas_data.tlsn_trd_id = tlea.tlsn_trd_id))
          )

One possibility would be the use of nvl in oracle. But I'm not sure how I can achieve the same result as the above. It is the data on the meas_data side which sometimes contains Null for tlsn_vrsn_num and/or tlsn_leg_id, in which case it should ignore those columns for the join.


texts:
I have a table with 100M rows. I am able update my table like 850 updates / minute. How to speed this up?

I am using following approach:

mysql -uroot < query_file

where query_file contains 1M update queries.

The queries are like:

update table a set col1=1 where col2="something";

Please note, that the value of col2 is different for each query.

What would be the fastest approach to bulk update this mysql table (Innodb)?

Moreover, can I optimize this query like by col2 as a primary key or unique or index or anything else, you can suggest?

UPDATE:

Table has 8 columns.

Right now, the Primary key is a combination of url_hash(16) and domain name(say col 1 and col 6). Though, I am planning to add auto-increment id column as the primary key (col2 in the above example)

Table has 4 BTREE Type index:

Index 1 on say col 1 and col 6   <-- primary key
Index 2 on say col 1, col 3 and col 4
Index 3 on say col 1, col 5
Index 4 on say col 4


texts:
I use this query in SQL Server 2008 R2 to restore DB1 database:

RESTORE DATABASE [DB1]
FROM DISK = N'D:\new.bak'
WITH RESTRICTED_USER, FILE = 1, NOUNLOAD, REPLACE, STATS = 10;

The following error is displayed:

  Msg 3102, Level 16, State 1, Line 1
  RESTORE cannot process database 'DB1' because it is in use by this session. It is recommended that the master database be used when performing this operation.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.


texts: How to export a single schema from Oracle EE to Oracle XE?

texts:
I would like to export a schema (DDL or DDL+data) from Oracle EE (Enterprise Edition) to Oracle XE (Express Edition) for development purpose (in our case: for use as test databases for Java developers).

The source schema contains some features, that are not available in Oracle XE, but none of them is required in development. A target schema can be modified to be compatible with XE.

In particular:

partition declarations can be omitted,
storage declarations omitted or simplified (e.g. as little as possible initial space reserved for tables),
tablespace declarations omitted or replaced (a single tablespace used in XE),
materialized views with some advanced features can be simplified or replaced with simple views,
jobs can be omitted,
java functions (scarcely used but present in one pl/sql package) can be disabled or imported with compilation errors.

One way to achieve it is to export source DDL to an sql script and then modify it by hand. But I'm curious if you see any other, more efficient way.

Oracle version: 11g / 12c
size of an sql file containing DDL: 7MB

Note: There already exists a similar question, but in my case I accept some differences between source and target schema.


texts: In SQL*Plus, how do I run @https://here.is.url/script.sql behind a proxy?

texts:
In SQL*Plus, I try to run a script that is stored at a certain URL.

From where I am currently, I can http(s) connect with the internet only via a proxy.

So, if in SQL*Plus, I try

@https://here.is.url/script.sql

SQL*Plus won't find the script (as SQL*Plus doesn't seem to know about the proxy).

Is there a way to indicate what proxy to use and how to authenticate with that proxy?


texts: Why is mysql using the wrong index for order by query?

texts:
Here is my table with ~10,000,000 rows data

CREATE TABLE `votes` (
  `subject_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `subject_id` int(11) NOT NULL,
  `voter_id` int(11) NOT NULL,
  `rate` int(11) NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`subject_name`,`subject_id`,`voter_id`),
  KEY `IDX_518B7ACFEBB4B8AD` (`voter_id`),
  KEY `subject_timestamp` (`subject_name`,`subject_id`,`updated_at`),
  KEY `voter_timestamp` (`voter_id`,`updated_at`),
  CONSTRAINT `FK_518B7ACFEBB4B8AD` FOREIGN KEY (`voter_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Here is the indexes cardinalities

So when I do this query:

SELECT SQL_NO_CACHE * FROM votes WHERE
    voter_id = 1099 AND
    rate = 1 AND
    subject_name = 'medium'
ORDER BY updated_at DESC
LIMIT 20 OFFSET 100;

I was expecting it uses index voter_timestamp
but mysql chooses to use this instead:

explain select SQL_NO_CACHE * from votes  where subject_name = 'medium' and voter_id = 1001 and rate = 1 order by updated_at desc limit 20 offset 100;`

type:
    index_merge
possible_keys:
    PRIMARY,IDX_518B7ACFEBB4B8AD,subject_timestamp,voter_timestamp
key:
    IDX_518B7ACFEBB4B8AD,PRIMARY
key_len:
    102,98
ref:
    NULL
rows:
    9255
filtered:
    10.00
Extra:
    Using intersect(IDX_518B7ACFEBB4B8AD,PRIMARY); Using where; Using filesort

And I got 200-400ms query time.

If I force it to use the right index like:

SELECT SQL_NO_CACHE * FROM votes USE INDEX (voter_timestamp) WHERE
    voter_id = 1099 AND
    rate = 1 AND
    subject_name = 'medium'
ORDER BY updated_at DESC
LIMIT 20 OFFSET 100;

Mysql can return the results in 1-2ms

and here is the explain:

type:
    ref
possible_keys:
    voter_timestamp
key:
    voter_timestamp
key_len:
    4
ref:
    const
rows:
    18714
filtered:
    1.00
Extra:
    Using where

So why didn't mysql choose the voter_timestamp index for my original query?

What I had tried is analyze table votes, optimize table votes, drop that index and add it again, but mysql still uses the wrong index. not quite understand what is the problem.


texts:
As a test of theory (because I want to brush up on my SQL Server Security understanding) I want to set up a SQL Server Login that can only see a few fields on one table in one database.

I have not assigned any roles to this login except the default public. I don't want the hypothetical user to have any server role (ie not sysadmin, dbcreator)... and I don't want to use any standard role for the database (db_datareader etc)...

The login has a default database, and the login is mapped to the database (default schema dbo).  Now in the user securables I have granted access to one table and used the column permissions to specify the columns but I'm really not getting this far when testing the login.

When I log into the SSMS with SQL Server authentication and the new login, I don't see any databases except the master/tempdb...

Should I be seeing my default/mapped database?  I don't see any other questions asked quite like this so I am guessing I am missing something completely obvious.

In order to log into SSMS with a sql login, do you HAVE TO HAVE an assigned server role to see non system databases? I can see the databases when I add sysadmin but then I am right back to over granting.

CREATE LOGIN [Chris_Test2] WITH PASSWORD=N'¯\Ï<ÞZcÅÎ5¿ûY!ËDåÜ''¨Äs»¾Þja7',
DEFAULT_DATABASE=[Finance_Receivables_TEST],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO

USE [Finance_Receivables_TEST]
GO
/****** Object:  User [Chris_Test2]    Script Date: 01/18/2017 14:40:21 ******/
GO
CREATE USER [Chris_Test2] FOR LOGIN [Chris_Test2] WITH DEFAULT_SCHEMA=[dbo]


texts: Unable to get a join to pull all records from another table in MySQL

texts:
I've got this working in MSSQL but I've fallen off and been run over when it comes to MySQL.  I'm trying to search for all records in a group after a certain date in an employee info table.  I can get the records in the employee info table but I also need to return all employee numbers in another table that are part of the same group.  The purpose being to see which employees in the group have submitted some paperwork. Since the table has data from multiple years, it needs to filtered so I can search after a certain date each year.

Here are my two tables

empInfo
--------------------------------------
id             | int
date           | datetime
empNumber      | varchar
grpNumber      | int
active         | int

empReports
--------------------------------------
id             | int
date           | datetime
empNum         | varchar
grpNumber      | int
empFName       | varchar
empLName       | varchar
dateReceived   | varchar
active         | int

The idea is to search for a group number and return all employee IDs from the empInfo table and show which ones have records from the empReports table. The current query is:

SELECT r.dateReceived as Date,
t.empNumber AS Emp,
r.grpNumber AS Grp,
r.empFName,
r.empLName
FROM empInfo t
LEFT JOIN empreports r
ON t.empNumber = r.empNum
WHERE t.grpNumber = 100
AND t.active = 1
AND r.dateReceived > 1587574487
ORDER BY CAST(t.empNumber as UNSIGNED INTEGER) ASC

This returns a single record

    Date    |  Emp  |  Grp  |  empFName  |  empLName
------------------------------------------------------
1589561752  |   1   |  100  |  Fred      |  Derf

What I need to see is more like

    Date    |  Emp  |  Grp  |  empFName  |  empLName
------------------------------------------------------
1589561752  |   1   |  100  |  Fred      |  Derf
    NULL    |   2   |  100  |  NULL      |  NULL
    NULL    |   3   |  100  |  NULL      |  NULL
    NULL    |   4   |  100  |  NULL      |  NULL
    NULL    |   5   |  100  |  NULL      |  NULL

I've tried different joins, swapping the query around and just about anything else I can think of with no joy, so any assistance any MySQL gurus could offer would be greatly appreciated.

Thanks in advance.


texts: Aurora MySQL 5.7 DDL statements hangs intermittently

texts:
We are using AWS Aurora MySQL and we recently moved to MySQL 5.7 version. After moving to this version, we started seeing issues with DDL statements. The processes which are running these statements just hangs in state "Waiting for table metadata lock". This behavior is intermittent. We never saw this issue with old MySQL version. Is anyone else also facing same problem and resolved it somehow?

Thanks,
Sagun


texts: Monitor number of records returned in a long-running SELECT statement during SSAS processing

texts:
On a SQL Server 2014, I have an SSAS tabular model which is querying a relational clustered columnstore table (with 800 mio. records), when the model is being processed. The query is just a simple SELECT statement with no joins, filters, etc. I am processing the model through an XMLA command, which is being fired by a SQL Server Agent job. The processing takes a couple of hours.

To get an idea of the progress of the processing, I would like to know the number of records returned so far by the SELECT statement. Is there any way to do this, when I know the SPID of the SELECT statement?

sys.dm_exec_requests does not seem to contain any record count information.

Also, I have tried to look in the DMV's of Analysis Services, to see if any of these objects could give me the information I need, but no luck.

The reason I think it must be possible to obtain this information somehow, is that when I process the tabular model through the graphical IDE of SSMS, it actually shows the row count while processing.

Update: It turns out the sys.dm_exec_requests DMV actually has a row_count column. Unfortunately, the contents of this column does not seem to reflect reality. For the first hour or so of processing, it had the value of "3", but I know from experience that several million records would have been returned within the first hour.


texts:
I just want to know how you deal with long scripts and redundant information. Here is an example:

Suppose you have three tables: table1, table2, and sometable.

And you have

SELECT
 id  = 1
 org = 'company'
 name = 'name'
 addr = 'arr'
 lang = 1
cnt1 = if col1 = 1 then 1 else 1
 cnt2 = if col2 = 1 then 1 else 1
 cnt3 = if col2 = 1 then 1 else 1
INTO  table1
FROM   sometable

SELECT
 id  = 2
 org = 'company2'
 name = 'name'
 addr = 'arr'
 lang = 1
 cnt1 = if col1 = 1 then 1 else 1
 cnt2 = if col2 = 1 then 1 else 1
 cnt3 = if col2 = 1 then 1 else 1
INTO  table2
FROM   sometable

So the difference is that the starting stuff (not the cnt* fields) are changing. But as you can see there is a lot of overlap.

Also if I want to change the logic on the counts (which are common to both), then i have to go through both select statements and change them manually. this is error prone.

I was thinking something along this pseudocode:

   SELECT
 id  = 1
 org = @variable
 name = @variable
 addr = 'arr'
 lang = 1

  INCLUDE other columns/logic from somewhere else

INTO  table2
FROM   sometable

that way, if my logic changes for the cnt fields, I can just change it in one spot and it would change everywhere.

I am open to any suggestions. SMSS addin, stored procs, or some other ugly or beautiful method people know.


texts: A call to a package procedure fails after upgrade to PSU24

texts:
We have just upgraded an Oracle Database 11.1.0.7 from PSU6 to PSU24.
We have set a AFTER SERVER ERROR trigger to track application errors a while ago and checked its history table.

Summary

We see the following error:

  ORA-04043: object PACKAGENAME.PROCEDURENAME does not exist

Some other informations:

The application for which this error is raised uses a different schema than the one in which the package is.
The application is proprietary and we don't have access to the code.
The application uses a synonym to get to that package.
The application connects/disconnects every time, so it's difficult to start a trace...
The package is valid
Permissions are granted directly, not via roles
The packages has only two dependencies on views and user have granted privileges on these views.

We tried different things :

Grant permissions once more -> No changes
DROP/CREATE the synonym -> No changes
RECOMPILE the package-> No changes
GRANT DBA to the user-> No changes
Connect to Oracle instance with this user using SQL*Plus and try to run the procedure. -> It worked.

As tracing is difficult, we tried data mining on v$sqltextand found one entry.

Edit 4/12/2015 2:31PM

We finally traced a session and isolated the executable which experience the error. Unfortunately, we have something we don't understand: the AFTER SERVER ERROR trigger seems to be run before the actual execution of the originating package..

We found that the call to the procedure is done like this:

BEGIN
    PACKAGENAME.PROCEDURENAME(Param1,Param2)
END;

We begin to wonder if the error caught by the server trigger is the right one. We get it back via ora_server_error_msg function (see code below).

We also get the statement using ora_sql_txt but it returns null.

Edit 2015-12-08
The server error trigger is the following:

create or replace TRIGGER "SYS"."VBO_DB_ERRORS_TRGG"
AFTER SERVERERROR ON DATABASE
begin
  sys.vbo_db_errors_proc();
end;

The procedure sys.vbo_db_errors_proc is the following one:

create or replace PROCEDURE                       "VBO_DB_ERRORS_PROC"
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  muser      varchar2(100);
  mosuser    varchar2(100);
  mmachine   varchar2(100);
  mprocess   varchar2(24);
  mprogram   varchar2(100);
  sql_text   ora_name_list_t;
  mmsg       varchar2(2000) := null;
  mstmt      varchar2(2000)  := null;
  chainetemp varchar2(4000) := 'inconnu';
BEGIN

    -- recuperation des infos de session
    select username, osuser, replace(machine,chr(0),''), process, program
    into   muser, mosuser, mmachine, mprocess, mprogram
    from   sys.v_$session
    where  audsid = userenv('sessionid')
         and sid = (select sid from v$mystat where rownum <=1);

    -- recuperation du message d'erreur
    BEGIN
    for i in 1 .. ora_server_error_depth loop
    mmsg := mmsg || rtrim(ora_server_error_msg(i),chr(10));
    end loop;
        EXCEPTION
      WHEN OTHERS THEN NULL;
    END;

    -- recuperation du texte
    BEGIN
        for j in 1 .. ora_sql_txt(sql_text) loop
        mstmt := mstmt || rtrim( sql_text(j), chr(10));
        end loop;
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;

  insert into vbo_db_errors_log (error,stmt,timestamp,username,osuser,machine,process,program,quictx) values(mmsg,mstmt,sysdate,muser,mosuser,mmachine,mprocess,mprogram,chainetemp);
  commit;

END;

Questions

Could you confirm that a query that is in v$sqltext is actually
a query already executed successfully on the Oracle instance?
Do you have any suggestion ?

Thanks.


texts:
My production database suffers from a large Service Broker message queue. It is around 2 million. I would like to decrease it as small as possible.

I have tried dbcc dbreindex('sys.queue_messages_245995913') from the DAC connection, based on the advice given by Remus Rusanu here.

But when I look at the queue properties, the queue size remains the same.


texts: Using newly inserted values in trigger function in PostgreSQL

texts:
I have this table:

CREATE TABLE accounts (
  id BIGINT NOT NULL UNIQUE,
  balance INTEGER DEFAULT 0
);

And I need to update each user's balance after inserting rows to 2 different tables, transfers and deposits.

I've created 2 functions to calculate new user balances and updating it:

/* function for getting user balance */
CREATE FUNCTION get_balance(bigint) RETURNS bigint
    AS 'SELECT (
  SELECT COALESCE(sum(CASE WHEN won THEN amount * (multiplier - 1) ELSE -amount END), 0)
  FROM transfers
  WHERE user_id = $1
) + (
  SELECT COALESCE(sum(amount), 0)
  FROM deposits
  WHERE user_id = $1
) as sum;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

/* function for updating user balance */
CREATE FUNCTION upsert_balance(bigint) RETURNS VOID
    AS 'INSERT INTO ACCOUNTS (id, balance)
    VALUES ($1, get_balance($1))
  ON CONFLICT (id) DO
  UPDATE SET balance = get_balance($1);'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

But I'm not sure how to use the newly inserted values in a trigger callback, because I need to pass the id as an argument to the upsert_balance function.

How can I accomplish this?

EDIT: I fixed it, thanks to Adam's answer, here is the working version in case anyone needs it:

CREATE FUNCTION upsert_balance() RETURNS trigger AS $trigger_bound$
BEGIN
    INSERT INTO ACCOUNTS (id, balance)
    VALUES (NEW.user_id, get_balance(NEW.user_id))
    ON CONFLICT (id) DO
    UPDATE SET balance = get_balance(NEW.user_id);

    RETURN NEW;
END;
$trigger_bound$
LANGUAGE plpgsql;

CREATE TRIGGER update_balance_on_inserting_transfer
AFTER INSERT OR UPDATE ON transfers
FOR EACH ROW
EXECUTE PROCEDURE upsert_balance();


texts: Check constraint to require one of two columns

texts:
I'm using SQL Server 2008 R2 and would like to make one of two columns required on a table; in other words, if the user provides a value for one, the other isn't required but the user can provide a value for both. How do you do this using check constraints? Thanks.

Update: Here's the table definition; the two columns are alternateID and securityID:

CREATE TABLE [dbo].[Employee](
    [employeeID] [int] IDENTITY(9500,1) NOT NULL,
    [alternateID] [nvarchar](50) NULL,
    [securityID] [nvarchar](50) NOT NULL,
    [firstName] [nvarchar](50) NOT NULL,
    [middleName] [nvarchar](50) NULL,
    [lastName] [nvarchar](50) NOT NULL,
    [suffix] [nvarchar](50) NULL,
    [job] [nvarchar](50) NULL,
    [organizationalUnit] [nvarchar](50) NOT NULL,
    [costCenter] [nvarchar](50) NOT NULL,
    [notes] [nvarchar](95) NULL,
    [createdDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
    [employeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


texts:
I'm reading through the Postgres documentation, the page on SELECT statements, and I ran across an aspect of aliases that I have never encountered.

In the section on FROM clauses, subheading alias, there is a sentence stating:

  If an alias is written, a column alias list can also be written to provide substitute names for one or more columns of the table.

There are no examples given in the documentation that I could find.

I know how to set up output names as aliases, but that doesn't appear to be the same thing.

The synopsis for SELECT includes the lines:

  ...
  SELECT [ ALL | DISTINCT [ ON (expression[, ...] ) ] ]
      * |expression[ [ AS ]output_name] [, ...]
      [ FROMfrom_item[, ...] ]
  ...

And defines from_item as:

  where from_item can be one of:

  [ ONLY ]table_name[ * ] [ [ AS ]alias[ (column_alias[, ...] ) ] ]
  (select) [ AS ]alias[ (column_alias[, ...] ) ]
  ...(other forms omitted)...

Note that from_item actually includes column_alias.

It makes sense to me that the ( select ) form can be given column aliases in the FROM clause (rather than only output_names, in the "expressions" of the SELECT statement), since the "columns" of the subquery will have been explicitly chosen in most cases and thus the sequence will be known.  So I would imagine in that case the column_alias values could simply be a list of names, and they would be matched up in sequence against the columns returned by the subquery.  (Though an example would be nice.)

However, how can column aliases be used for a table_name?  Do you have to know the exact sequence of columns defined in the table, or can you set an alias just for one or two of these in the FROM clause?

What if you only want to set a column_alias for one column with a very long name (and leave the other columns not aliased); is this possible?  (If so, is this Postgres specific?)


texts: How to decode a query on a composite unary-encoded attribute?

texts:
A good early paper on the topic of bitmap indexes is "Bit Transposed Files" by Wong et al, published in 1985.

In all the query-decoding examples, it is left to the reader to understand how each decoded query is derived. One of the examples is too complex for me to figure out.

In section 5.3.3.b, the paper shows how the BTF query processor would decode the query to find all dogs receiving more than 30 dosage units.

In BTF, the query would look like this:

dosage[>30]

We are told that

  Attribute dosage is encoded as a Composite unary with 3 fields of 6 bits. Assume dosage 30 is encoded as 000111,000011,011111."

Thus the paper claims

  the query can be translated to

dosage ((b14 & b7 & b4) | (b14 & b8) | b15)

and says no more about it.

How does that work?

By my understanding, the decoded query dosage (b14 & b7 & b4) should alone select the correct values, if we interpret "more than" as "more than or equal to".

My attempt at understanding the solution follows.

Understanding the encoded value

I quote literal values in decimal unless specified otherwise.

We are told that the encoding represents the value 30.

We should remember a unary field that encodes value n+1 sets all the bits that encode n plus the next rightmost bit. A 0 is encoded by setting no bits.

To help me understand the derivation, I created a table that maps each bit of the attribute encoded value (u for unary) to its field value (d for decimal) and to its attribute ordinal position (n for n-th).

The table looks like this:

n 17 16 15 14 13 12    11 10  9  8  7  6     5  4  3  2  1  0

d  6  5  4  3  2  1     6  5  4  3  2  1     6  5  4  3  2  1

u  0  0  0  1  1  1     0  0  0  0  1  1     0  1  1  1  1  1

We can see from the table that the the values of the fields from left to right are (3, 2, and 5) respectively.

We know that 3 * 2 * 5 = 30, so we might assume that the product of the field values gives the attribute value.

If this is true, then:

the order of the fields should be insignificant.
6 * 6 * 6 = 216 is the biggest encodable value.
each value might have multiple encodings.

Section 4 (Bit Transposition) states that attribute dosage has 200 distinct values, so all attribute values are encodable.

Understanding the decoded query

The decoded query ORs together three expressions.

The first expression (b14 & b7 & b4) selects attributes whose:

first field encodes 3, 4, 5, or 6.
second field encodes 2, 3, 4, 5, or 6
third field encodes 5 or 6.

The expression selects dosage values greater than 30, but selects dosages of 30 as well. If the aim is to find values strictly greater than 30, then the expression would select values that don't match the BTF query.

The second expression (14 & b8) and the third expression (b15) don't select bits for some fields, so these fields could have any value.

If this is true, then the whole expression would select dogs who received a dose of 0 and many other values less than 30.

By my understanding, the decoded query would select many values that don't match the BTF query.


texts: Mysql user with grant option cannot grant access to another user

texts:
in my mysql server there is a user mgenkin with the following privileges:
+---------------------------------------------------------------------------+
| Grants for mgenkin@%                                                      |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mgenkin`@`%`                                       |
| GRANT SELECT ON `hughes_simulations`.* TO `mgenkin`@`%`                   |
| GRANT SELECT, LOCK TABLES ON `lab_experiments`.* TO `mgenkin`@`%`         |
| GRANT SELECT, LOCK TABLES ON `lab_experiments_dev`.* TO `mgenkin`@`%`     |
| GRANT ALL PRIVILEGES ON `mgenkin\_%`.* TO `mgenkin`@`%` WITH GRANT OPTION |
+---------------------------------------------------------------------------+

Thus, this user has all privileges with grant option on any database that starts with mgenkin_. I want to grant privileges to another user on one of these databases:
grant select, lock tables on `mgenkin_monkeydata_chand`.* to `aghamoh`@`%`;

This outputs unexpected error:
ERROR 1044 (42000): Access denied for user 'mgenkin'@'%' to database 'mgenkin_monkeydata_chand'


texts: Can stopping a backup job while its in progress affect future backup jobs? i stopped a backup because it was taking too long and affecting production, and since then all my backup jobs have failed. any way forward? i have already tried deleting the backup jobs and creating new ones and still no luck


texts:
Our database is slow in some very important use cases. I have traced it to a problem with some queries performing full text searches and single row accesses on a lot of records (db file sequential read) afterwards. Once the data is in memory, those queries become very fast.

The solution from us developers is now to put the hottest table into memory.

We currently have a SGA_MAX of 20 GB, SGA_TARGET is 14GB. ADDM and AWR recommend 25GB of SGA space.

Our infrastructure team will now upgrade our database to 40GB, however, the database admin sais that that much ram can kill oracle performance.

Is that true? I cannot possibly imagine that 20GB more RAM will make the database go slower.

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


texts: DROP TABLE ... CASCADE does not drop any dependent table

texts:
I am still relatively new to psql and I was just finishing an assignment when I decided to try something which was to drop a table which other tables depend on due to a foreign key relationship. My expectation was that any table that had a foreign key reference to the table I am dropping will have (at least) all their rows deleted and the table dropped as well.
This was not the case. The tables are still there and even though I specified CASCADE, nothing happened to the rows of those tables.

However using TRUNCATE ... CASCADE on the referenced table does in fact remove the rows in the other tables

EDIT:
Ok, I understand it does this because this is the way it has been specified by the documentation, but my question now is, when is this ever expected behaviour?

DROPing a domian with CASCADE removes every column in any table that uses that domain.
DROPing a table with a view attached to it will delete the view.

Why does it make sense that dropping a table which is referenced by other tables does not delete the tables that reference this one as well? Convienience?


texts: Printing \G on shell using mysql client..?

texts:
It is really a a headache for me from some hours i am struggling on the problem.I have also asked it on stack-overflow but not got any answer yet so i am asking here.Sorry for Not a good question.

I am Executing a Command on Linux shell.

mysql -uroot -proot -Bse "select Concat('SHOW CREATE TABLE ',TABLE_SCHEMA ,'.',TABLE_NAME,'\\G') from information_schema.tables where TABLE_SCHEMA Not IN('mysql','information_schema','performance_schema');"

I want output as

SHOW CREATE TABLE sakila.category\G
SHOW CREATE TABLE sakila.city\G
SHOW CREATE TABLE sakila.country\G
SHOW CREATE TABLE sakila.customer\G

But I am getting as

SHOW CREATE TABLE sakila.categoryG
SHOW CREATE TABLE sakila.cityG
SHOW CREATE TABLE sakila.countryG
SHOW CREATE TABLE sakila.customerG

Why it is so..?


texts: Facing Failed to read stream error - mysql_native_password error

texts:
I have been facing the following error intermittently on MySQL.

  Authentication to host '127.0.0.1' for user 'root' using method
  'mysql_native_password' failed with message: Reading from the stream
  has failed.

It comes up any time and I am at my wits end. I also posted a bug on MySQL bugs and solutions are not proving to be effective in any way.I hope you guys can help me out.

Here is the link to MySQL Bug for details: Reading from stream failed!

Some more detail: I have a client-server system but this bug occurs on the server system, where MySQL database is installed, when a local running app on the server system tries to run a query.

I had already opened a question here but since has been dead. Just a caveat I thought that skip-name-resolve solved the issue but it seems to just have lowered the frequency. Hope someone would help me out this time around.


texts:
I have a big table (61mil rows) that has an indexed not-null column ID. I want to select the distinct values of that column. I expected the

select distinct id from bigtable ;

to run fast, making use of that index. Yet it takes more than 40sec to run on a fast server.

The EXPLAIN PLAN shows that a FAST FULL SCAN of that index takes place, whereas I'd expect a INDEX RANGE SCAN  instead. The cost comes up as 93000.

Please advise. Do I perhaps need a different index type?


texts:
I open psql and write:

mydb=> \dt

and this list of tables is shown:

                   List of relations
 Schema |            Name            | Type  |  Owner
--------+----------------------------+-------+---------
 public | auth_group                 | table | foouser
 ...

The I have to press Esc to return to the prompt, and the output disappears like it was a less session.

I want to keep the output around so that I can use the information in it to write queries, but googling does not find an option to do that.


texts: Should I maintain relationships diagrams in the back-end and the front-end?

texts:
I’m developing an Access 2010 database application. It is split into a back-end, holding all the tables, and a front-end, holding all the queries, forms, etc.
I find the Relationships tool helps provide an extremely valuable visual resource (an ERD), in the back-end, and know that Access uses it to enforce integrity.

So far, however, I’ve never used the same tool in the front-end. I have many more queries than tables but have never seen them in form of an entity-relationship diagram. Have you ever found it useful to diagram your queries using the Relationships tool? Any advice on what practice has worked well for you?


texts: MySQL - Order by closest value on multiple columns

texts:
I have a table for a game lobby. In this lobby a user gets put in their connection and their level. In my SQL statement I am getting all the users in the lobby which are between +5/-5 of the user's level and connection. However I want to order this so that a user's level is the most important and then it will look at the connection. So if there are two users with the same level, it will look at the connection instead. Maybe there will be more columns in future that can be used to further refine the search. I have seen that on single column selects like this, I could use abs() however I'm not sure if this is possible on this as well?

create table users (
  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  connection INT(11),
  level INT (11)
);

create table lobby (
  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT(11),
  CONSTRAINT user_id FOREIGN KEY (id)
  REFERENCES users(id)
);

insert into users values (null,5,6);
insert into users values (null,7,10);
insert into users values (null,10,9);
insert into users values (null,10,4);
insert into users values (null,1,8);

insert into lobby values (null,1);
insert into lobby values (null,2);
insert into lobby values (null,3);
insert into lobby values (null,4);
insert into lobby values (null,5);

So if I'm using user_id = 1 as my user I would have the following select statement:

select lobby.id, lobby.user_id, users.connection, users.level
from lobby INNER JOIN users
ON lobby.user_id = users.id
where user_id <> 1 and
users.connection between 0 and 10 and
users.level between 1 and 11
order by abs(connection-5);

This select statement comes back with the following table:

| id | user_id | connection | level |
|----|---------|------------|-------|
|  2 |       2 |          7 |    10 |
|  5 |       5 |          1 |     8 |
|  3 |       3 |         10 |     9 |
|  4 |       4 |         10 |     4 |

However what I want is the following output:

| id | user_id | connection | level |
|----|---------|------------|-------|
|  2 |       2 |          7 |    10 |
|  5 |       5 |          1 |     8 |
|  4 |       4 |         10 |     4 |
|  3 |       3 |         10 |     9 |

Thanks.

EDIT: Ok so I am currently using the abs function and it's working well, however it is working on multiple fields and because of this I feel that the statement is not very efficient. Is there a better way of doing this?


texts: Adding Time Limit parameter to Ola hallengren Index Maintenance

texts:
I would like to limit the duration of the index maintenance procedure of Ola Hallengren to be 7200 seconds (2 Hours)

I found parameter TimeLimit defined as: Set the time, in seconds, after which no commands are executed. By default, the time is not limited.

Does this mean if a command is already running and the time limit reach it will force it to stop or will continue and will not run any command after ?

Thanks


texts: Could not complete cursor operation because the set options have changed since the cursor was declared - SQL Server

texts:
I get this error when I execute this cursor.

  Msg 16958, Level 16, State 3, Line 56 Could not complete cursor operation because the set options have changed since the cursor was declared.

But after that I review my login and his roles. Everything is fine. So I don't why i get this error but everything went fine.

USE MASTER
GO
  CREATE LOGIN [login1] with password = 'test12',
  CHECK_POLICY=OFF;
GO

declare @ejecutable nvarchar(100)
declare @database   nvarchar(50)
declare @DynCommand nvarchar(max)

declare cr1_database cursor
local fast_forward
for
select name
from sys.server_principals
where name like 'login1';

open cr1_database

fetch next from cr1_database into @ejecutable

WHILE (@@FETCH_STATUS = 0)

BEGIN
         SET @DynCommand =N'
        USE ?
         IF (DB_NAME () NOT IN (''DBA''))
        BEGIN
         create user [' + @ejecutable+ ']
         for login [' + @ejecutable+ '];
         exec sp_addrolemember ''db_datareader'', ['+@ejecutable+']
         exec sp_addrolemember ''db_datawriter'', ['+@ejecutable+']
         exec sp_addrolemember ''db_ddladmin'', ['+@ejecutable+']
         exec sp_addsrvrolemember ['+@ejecutable+'] , ''securityadmin''

        END '

    EXEC sp_foreachdb
    @command = @DynCommand,
    @user_only = 1

    BEGIN
        USE msdb
      BEGIN
        CREATE user [login1] for login [login1]
        EXEC sp_addrolemember @rolename= 'SQLAgentReaderRole', @membername = 'login1'
        EXEC sp_addrolemember @rolename= 'db_datareader', @membername = 'login1'
      END

    END

 FETCH NEXT FROM cr1_database INTO @ejecutable
END
CLOSE cr1_database
DEALLOCATE cr1_database


texts: Not able to create a stored procedure for a table insert in DB2 UDB 10.5

texts:
I want to create a very simple stored procedure that will only take several IN parameters and insert them into a table. To learn how to do it, I went to CREATE PROCEDURE (SQL) statement
From that I gathered that the procedure below should work, but it does not:
CREATE PROCEDURE A (IN B INT)
LANGUAGE SQL
BEGIN
  INSERT INTO TA(A) VALUES(B);
END

The error is:
An unexpected token "END-OF-STATEMENT" was found following "INTO TA(A) VALUES(B)".  Expected tokens may include:  "<psm_semicolon>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.19.80 SQL Code: -104, SQL State: 42601
Error occurred in:
CREATE PROCEDURE A (IN B INT)
LANGUAGE SQL
BEGIN
INSERT INTO TA(A) VALUES(B)

When I copied the source code above from SquirrelSQL and pasted it into a new query in IBM Data Studio, it indeed underscored the semicolon at the end of the INSERT statement and displayed the error:
DB2 for Linux, UNIX, and Windows: ";" is invalid.

If I comment out the procedure body, it compiles fine, and I can call it successfully.
I googled a lot but could not find any material difference between my code and the suggestions found, including here and on SO. Even when I took the sample procedure from the linked page, it did not compile. It throws the error:
"MEDIANSALARY" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.19.80 SQL Code: -206, SQL State: 42703
Error occurred in:
SET medianSalary = 0


texts: Check current memory_limit of Replication Server version 15.7.1

texts:
Does anyone knows how to check the currently memory_limit of replication server? I need to increase the memory_limit of my replication server, but I couldn't check the current memory limit. Can anyone help?

  W. 2018/03/22 15:58:08. WARNING #7038 SQM(199:0 pds.pdb1) -
  de/generic/mem/mem.c(2763)
          WARNING: Memory usage is above 80 percent. Increase 'memory_limit' or reduce cache sizes to avoid repserver threads from
  sleeping due to lack of memory.

  W. 2018/03/22 15:58:53. WARNING #7039 DSI EXEC(199(1) pds.pdb1) -
  de/generic/mem/mem.c(2763)
          WARNING CANCEL: Memory usage is below 80 percent.