texts:
This is implemented in Oracle, if it matters.

I understand how to build foreign keys to tables with composite primary keys. Is it possible to have a foreign key reference a table with a composite primary key with a static value for one of the key values?

Imagine I want to have a table of status codes. I could easily have a table of status codes for each purpose (i.e. one for ThingStatus, one for OtherThingStatus, etc.). Trivial, but leads to lots of small tables. I could also have a single table with all the status codes, with each record also identifying what the status code applies to (shown below).

create table StatusCode (
  StatusTable  varchar2(20) not null,
  StatusValue  varchar2(20) not null
);

alter table StatusCode add constraint pkStatusCode primary key (StatusTable, StatusValue);

create table Thing (
  ThingId integer not null, -- primary key
  TableName varchar2(20) default 'Thing',
  ThingStatus varchar2(20) not null
);

alter table Thing add constraint fkThingStatus
  foreign key (TableName, ThingStatus)
  references StatusCode(StatusTable, StatusValue);

Pretty straightforward, easy to implement, annoying to have a field (present in every record) that exists solely to satisfy the foreign key definition.

Is there a way to do something like

create table NewThing (
  NewThingId integer not null, -- primary key
  NewThingStatus varchar2(20) not null
);

alter table add constraint fkNewThingStatus
  foreign key ('NewThing', NewThingStatus)
  references StatusCode(StatusTable, StatusValue);

I could also just treat StatusCode as a look up table and not implement the foreign key reference explicitly, but I'd rather see it explicitly implemented.


texts: UPDATE records in one table, several times the message (1 line affected) and then the actual message of the update

texts:
I have discovered the following oddity:
When I execute a SELECT statement, I get my desired 753 records displayed.
But when I convert this SQL to an UPDATE and I execute an SQL with the following structure, I get "(1 line affected)" several times in the Messages tab (SQL Server Management Studio) Only after several of these messages is the correct message displayed below (753 lines affected).

UPDATE table
SET column = value
WHERE   DATEDIFF(MONTH, value1, value2) <> value3
        AND deletedate IS NULL
        AND CONVERT(DATE, date) > '20200101'

What's happening in the background?


texts: Missing "public" schema when converting a "regclass" value to text

texts:
I am writing a script in which I need to parse the name of a table (in regclass). The parsing (with parse_ident()) works so far. However, the script fails when the table is in the public schema because PostgreSQL (10.3) automatically removes the schema name.

For example, if a table tt is in a non-public schema ex, the text value of the regclass is the same as the original:

=> select 'ex.tt'::regclass::text;
 text
-------
 ex.tt

When it's in public, the schema name is lost:

=> select 'public.tt'::regclass::text;
 text
------
 tt

Is there an way to disable this behavior, or to convert to text without losing the schema name?


texts: Trigger to increment a column value after update in a column of same table

texts:
I have a table with two columns:

C1  |  C2
 1  |   3

Whenever C1 gets updated to 0, C2 should be incremented by 1.

I have developed a trigger:

create trigger increment
after update on T1
begin
  if new.C1=0 then
    update T1 set c2 = c2 +1;
  end if;
end;

But this raises an error whenever I try to update column C1:

  ERROR 1442 (HY000): Can't update table 'T1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

I have only one update trigger on this table. How can I make this work?


texts: Aborting long running query under AG configuration

texts:
Currently we have a process where any query running over 2 mins (only select) in primary database participating in db mirroring is killed, running every 5 mins.
As we have moved to Availability groups now secondary has become readable replica so users can run queries there coming with application_intent ='read only'
However we still need the job to kill long running query over 2 mins on primary replica as new users onboard there are always chances of them hitting primary database. How can i change my current job on both primary and secondary replica so that it only kills long running query on P replica and not readable without me changing job configuration every-time.
Thanks,


texts: How to login in database if we have lost password?

texts:
Possible Duplicate:
  gain admin access to sql express 2008

Is there any idea if we lost our password to login in database using sql express management studio?
Is there any place where username and password saved on installation time of any sql server?


texts: SQL Server 2008 R2 error: 15023, user, group, or role already exists

texts:
I have a test database that I'm having permissions issues with.
I can't access the reporting database, and the application's help documentation says to do the following:

Resolution:

1. Launch the SQL Server Management Studio and connect to the database server(s) hosting   the Vision and Reporting Server databases.
2. Expand the security folder.
3. Select logins and right click on the <username> user and choose properties.
4. Click the User Mapping tab
5.Make sure the following databases are selected in the Users mapped to this Login:

    ReportServer
    ReportServerTempDB
    Your Vision databases
    This maps the login/user to the respective databases.
6. As you select each database (including your Vision database), select the db_owner role in the Database role membership for: section. You must select this option for each database.

When I do this, I get the following error:

"Create failed for user '<servername>\<username>'.  User, group, or role '<servername>\<username>' already exists in the current database. (Microsoft SQL Server, Error: 15023)"

I have googled this error and tried the following command on each database:

 ALTER USER [<username>] WITH LOGIN = [<username>]

The message indicated that the commands completed successfully but I am still getting the above error when I try to map each database as instructed above.

What am I missing?

Per Kin's comment (thanks) I tried this:
- I right clicked the user and selected: Script Login As > Drop and Create To > New query window.
- I ran the resulting query and tried to map the user roles by selecting the other two databases and db_owner again but I am still getting the same error message as above.

Thoughts??


texts: Oracle 11g Fast Add Column Taking 11 Hours

texts:
To the best of my understanding, a new feature of Oracle 11g is the ability to add a not null column with a default value very quickly as it would no longer auto-update every row in the table with the default value in the new column. However, the following command takes ~11 hours on a table with ~1.2 billion rows on an 11.2.0.4 database that had just been upgraded from 10.2.0.5:

alter table table_name add column_name varchar2(6) default "DEFLT' not null;

Although I've seen the following in the documentation:

"However, the optimized behavior is subject to the following restrictions:
•The table cannot have any LOB columns. It cannot be index-organized, temporary, or part of a cluster. It also cannot be a queue table, an object table, or the container table of a materialized view.
•The column being added cannot be encrypted, and cannot be an object column, nested table column, or a LOB column."

to the best of my knowledge my table does not meet any of these conditions. Describe shows no LOBs, ORGANIZATION INDEX isn't in the create statement, it isn't a temporary table, isn't a cluster, queue, object, or container table. And the column of course is a varchar column. Is there something I'm missing, or is the answer just that I'm mistaken about my table meeting one of these requirements?

ETA: Not sure if this is helpful, but I noticed in a paper that the tell-tell sign of fast column adds is the use of NVL in the filter predicate. From an explain plan on a test table, it does not appear that my database is executing fast adds:

SQL_ID  f78gwf6cz50uq, child number 0
-------------------------------------
select count(1) from t where z = 123456

Plan hash value: 1842905362

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   133 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   110K|  1406K|   133   (4)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("Z"=123456)

Note
-----
   - dynamic sampling used for this statement (level=2)


texts: Synapse: bulk copy from STAGE to DATAMART

texts:
I've fact table with rows count ~1 billions/year (~3 millions/day). ETL loads data into STAGE layer/schema with help of Polybase or COPY INTO. Then I'm copying data from STAGE to DATAMART table with help of INSERT INTO DATAMART.my_table (<cols>) SELECT (<cols>) FROM STAGE.my_table.
What is the best way to do bulk copy from STAGE table to DATAMART in Synapse?
Partition switching ISN'T an option. That's because the table is too small to be partitioned by a day (can be partitioned by a month only), while ETL is daily. With default 60 distributions Synapse suggest to have 60+ millions of rows per partition.
AFAIK, current approach: INSERT INTO DATAMART.my_table (<cols>) SELECT (<cols>) FROM STAGE.my_table should have a good performance (no transaction log, CTAS-like). But in the same time it keep data in intermediate state for some time, and requires a burden of defining stored procedures with columns list for each fact.
P.S. is it a common practice to use intermediate STAGE layer before uploading to a busy DATAMART? Is it necessary if at this point we're loading ony nightly?


texts:
Can Reporting Services be installed on a clean remote server, then create new SSRS databases on existing SQL Instance with SSRS installed?
Have a SQL Instance with Reporting Services working - Lets call it "SQLinstance1".
A new vendor/product needs its own instance of SSRS/ cannot share SSRS.
Is it possible to take a clean Windows server (no SQL installed)lets call it "Hal" , run SSRS installation ,then create ReportServer and ReportServertempdb on the SQLinstance1 (with different db names)?
Resulting in 2 separate SSRS envs:
http://Sqlinstance1/ReportServer2016
http://hal/ReportServer
If this isn't possible what are other options other than installing using another SQL instance?
thanks


texts: Restore from SQL Server 2008 R2 to SQL Server 2014 fails

texts:
I have a SQL Server database backup of SQL Server 2008 R2. I want to restore that backup file that is around 40 GB on to a Microsoft SQL Server 2014 Express Edition (64-bit) and it runs till 95% and then gives me this error:

  CREATE DATABASE or ALTER DATABASE failed because the resulting
  cumulative database size would exceed your licensed limit of 10240 MB
  per database


texts: db2 database alias not listed via list database directory but still unable to create that database

texts:
I am trying to create a database by logging on to the linux server on which the db2 server is installed, as the instance owner

db2 => list database directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = SAMPLE
 Database name                        = SAMPLE
 Local database directory             = /home/mylogin/sqllib
 Database release level               = f.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

db2 => create database testdb2
SQL1005N  The database alias "TESTDB2" already exists in either the local
database directory or system database directory.
    db2 => list database directory on /home/mylogin/sqllib

 Local Database Directory on /home/mylogin/sqllib

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = SAMPLE
 Database name                        = SAMPLE
 Database directory                   = SQL00001
 Database release level               = f.00
 Comment                              =
 Directory entry type                 = Home
 Catalog database partition number    = 0
 Database member number               = 0
db2 => quit

I also checked if there are any other instances that exist other than mine, but there aren't.

DB20000I  The QUIT command completed successfully.
-bash-4.1$ db2ilist
mylogin

While the alias testdb2 is not listed by list database directory , db2 claims that this alias already exists in either the system database directory or the local database directory.

If this is true, how do I get the equivalent of list database directory that lists testdb2 as well?


texts: Oracle - How to issue desc command in a procedure within package?

texts:
It's possible to see only the parameters of a especified procedure into a package?

For example a procedure into dbms_scheduler, something like this:

desc dbms_scheduler.create_job

It's possible?

Currently I get to see parameters viewing all objects into the package like this:

desc dbms_scheduler


texts: How do I Create Tablespace in Oracle 11g?

texts:
I am not too savvy in Oracle, and I have been trying to create a TABLESPACE, which keep on giving me an error message of invalid file name. Below is the syntax that I have used:

SQL> create Tablespace HRMT
2 datafile
3 size
4 ;
size
*
ERROR at line 3:
ORA-02236: invalid file name

My book and all websites that I have been to suggest that I specify the file path for the DATAFILE, and when I specify a path, I get another error message.

SQL> create tablespace vania
2 datafile 'home/oracle/hait/'
3 size 10M
4 ;
create tablespace vania
*
ERROR at line 1:
ORA-01119: error in creating database file 'home/oracle/hait/'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory

Do I need to create the datafile first before I can create the TABLESPACE? Are there pre-existing datafiles in the database that I can use? What would be the proper syntax to create a tablespace and/or a datafile? How and where do I create a .dbf file to create the tablespace?


texts: According to the currently accepted answer to this question a partial solution to an efficiency problem was to "inline" the query. I can't tell what that is, I can't ask in comments (too little rep) and Googling 'postgresql inline query' isn't very decisive (some seem to think it's synonymous w/"subquery" but others don't).


texts:
When importing raw files for internal conversion, should I use a secondary database or just isolate them within the database?

I've got between 1.25 and 2GB of CSV files to be imported (and already have most of the process running smooth as butter) so my question is: does it make sense as a "best practice" to use a "secondary" database for the import or just load them into the database that I'm going to be working in?

Example:

FinalDatabase -- this is the one I'm wanting to do, unless there's a net negative
  core.Users
  core.Addresses
  core.Emails
  core.OtherTables
  staging.UsersImport
  staging.ContactImport

OR

FinalDatabase
  core.Users
  core.Addresses
  core.Emails
  core.OtherTables
StagingDB
  dbo.UsersImport
  dbo.ContactImport

Obviously I'll be migrating from one table to the others via scripts, so it doesn't make much difference what the four-part name is going to be, whether it's just

INSERT INTO core.Users (fieldlist) SELECT fieldlist FROM staging.Users

or

INSERT INTO core.Users (fieldlist) SELECT fieldlist FROM StagingDB.dbo.Users

The cons I've identified with the first style are the following:

Increases backup size
Increases file size (including increasing the file size of the existing database -- autogrowth issues)
Negates the potential use of multiple spindles (in my case not an option :( )

Some of the pros I've identified with the first style:

The original import data stays with the database in case of future questions (until someone truncates it, of course)
It gets backed up with the latter data in case you need to recover something

What would be considered a best practice in this situation and why?


texts:
We are considering pessimistic locking for our project (SELECT ... FOR UPDATE); as we are used to optimistic locking, we are afraid about the operation of the server being blocked on such a lock (lock not getting released ever).

The most common concern that people have is obviously deadlocks, however we have another concern: what if the client dies, or looses connection to the server, while holding a SELECT ... FOR UPDATE lock?

In many cases it would not be relevant because if the client dies the application dies, however in this case we will have a cluster of servers with failover, so we wouldn't want other nodes in the cluster to be overly affected if one node dies.

On this topic I have found this 2003 message, concerning postgreSQL:
http://www.postgresql.org/message-id/3E81D7F0.2010604@xythos.com

We have also tested the tcp_keepalives_idle postgreSQL configuration parameter, and confirmed that if we leave it low enough and kill the client, the lock gets released. However if we didn't touch that parameter, the lock did not get released (but maybe we didn't wait long enough).
And that's without considering the concern raised by this 2003 message, about the difference between the lifecycle of the client application and the life-cycle of the connection pool (we'll keep the default on this but we expect each node to have its own connection pool, no sharing across the cluster).

The problem gets even more complicated because we intend to support several database servers (at least Microsoft SQL Server and PostgreSQL, possibly Oracle).

Our solution would use JavaEE and Glassfish as an application server.

So to summarize the question: Is it safe to assume that apart from deadlocks, it is not possible that a lock stays held forever, the lock will be released after a reasonably short time (which range of duration?), even in case of a sudden death or loss of connectivity to a client holding a lock?


texts: Save output from multiple SQL SELECT commands to a file

texts:
Within a Postgres function I would like to run a number of SQL SELECT commands and append the output to the same variable. After all the SELECT commands have run this output should be written to a file so that the user could read this report.

What is the best way to implement this?


texts: view current session roles for another session

texts:
In Oracle, I can view the currently set roles for my current session using the query SELECT * FROM SESSION_ROLES.

Is there any way that I can see this information for another session?


texts: How do I use pg_dump and pg_restore to move a large database to another machine?

texts:
I'm a bit unclear on how to use the pg_dump/pg_restore duo to move a large-ish database.

Here is the command I use to dump the database (the host and username are taken from environmental variables):

pg_dump --dbname=mydb --format=custom > mydb.dump

I use the custom format because I do this over the network and want to use compression.

When I now try to restore it, I tried this:

pg_restore --dbname=mydb --no-owner mydb.dump

But I get:

pg_restore: [archiver (db)] connection to database "mydb" failed: FATAL:  database "mydb" does not exist

I tried next with the --create option, but that yields the same error message.

Finally I tried passing template1 as a --dbname option:

pg_restore --dbname=template1 --no-owner --create mydb.dump

But here is what I get:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2125; 1262 16395 DATABASE mydb mydbuser
pg_restore: [archiver (db)] could not execute query: ERROR:  invalid locale name: "en_US.utf8"
    Command was: CREATE DATABASE mydb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8';

pg_restore: [archiver (db)] could not reconnect to database: FATAL:  database "mydb" does not exist

What am I doing wrong here?


texts:
I currently have an instance of SQL running as NETWORK SERVICE which for various reasons I can't change.

I have a maintenance plan running under a proxy account which has access to a network share to write backup files.  The proxy is set to run SSIS and cmdExec commands which are enabled on the server also.

If I run the job I get an error as below:

 Executed as user: xx\proxyaccount   Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Error: 2015-01-14 10:44:23.24     Code: 0xC002F210     Source: Back Up Database (Full) Execute SQL Task     Description: Executing the query "EXECUTE master.dbo.xp_create_subdir N'\\share..." failed with the following error: "xp_create_subdir() returned error 183, 'Cannot create a file when that file already exists.'". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  Error: 2015-01-14 10:44:23.54     Code: 0xC002F210     Source: Back Up Database (Full) Execute SQL Task     Description: Executing the query "BACKUP DATABASE [master] TO  DISK = N'\\share..." failed with the following error: "Cannot open backup device '\\share\xxx\master\master.bak'. Operating system error 5(Access is denied.).

The file definitely does not exist and so I think this is a bogus message, and the account definitely has access as I can map to the share and write files using it.  I suspect the proxy account is not functioning correctly and is trying to use NETWORK SERVICE even though it claims its trying to run the job as the proxy account at the start of the message.

Does anyone know if proxy accounts don't work if running SQL as a local account?

I have since added the compute account to the share and it works.  However for me this seems to be completely missing the point that it should run as the proxy account :s

Some more info.  I have recreated the problem on my local machine, and changed it from NETWORK SERVICE to a domain account and still get the issue.  If I grant the SQL Service account access then it works.  However why isn't it running as the proxy account?  I notice that the command being run is a xp_create_subdir, can you not run these as a proxy account?


texts: Query to categorize rows based on a "time" column without using a CASE expression

texts:
There is a ProductTT table as you can see below:

[dbo].[ProductTT] (ID int , Product Varchar(50) , Time Int)

...which contains the following rows:

1   XX   0030
2   UY   0354
3   YY   0517
4   ZZ   0712
5   WW   0415
6   GG   1112
7   MM   1030
8   HH   0913

Note: The format of the data in time column is hh:mm so 0030 is 00:30.

I want to write a query to categorize the rows based on their time value. I need to have 4 categories like this:

category1   00 to 03
category2   03 to 06
category3   06 to 09
category4   09 to 12

I need to see how many rows pertain to each category.

My attempt so far

What I've written so far is like this:

With CTE
AS (select ID,
       product,
       [time],
       Case
         When left(time,2)>=00 and left(time,2)< 03  then  'group1'
         when left(time,2)>=03 and left(time,2)< 06  then  'group2'
         when left(time,2)>=06 and left(time,2)< 09  then  'group3'
         when left(time,2)>=09 and left(time,2)<=12  then  'group4' End AS groupID
from [dbo].[ProductTT]

)
 select groupid,count(*) as recordcount
 from cte
 group by groupid

My question

That query works fine but I just want to know whether there are better ways to write this query and avoid using a CASE expression.


texts: Will the slave use source port 3006 and then connect to master on port 3306 to start replication? Or is random source port is being used to connect to master on 3306?


texts: Check ruby gem and SQLite3 for FTS3 or FTS4 support

texts:
I have installed SQLite3 on Mountain Lion via MacPorts and Ruby along with sqlite3 gem using RVM.

I'd like to know if there's any way for me to understand if my SQLite3 installation supports FTS4.

i.e. The only way I found online was issuing FTS3 commands and not getting errors, but there must be a more 'clean' way... Also the mentioned way doesn't show if you have FTS3 or 4 working.


texts:
I am new to MongoDB. I am trying to deploy a MongoDB cluster. I am following:

https://docs.mongodb.org/manual/tutorial/deploy-shard-cluster/

I initiated Config Server Replica Set and after that I tried to addShard but I receive always error:

mongos> sh.addShard("rs1/10.1.10.12:27017,10.1.10.13:27017,10.1.10.14:27017")
{
        "ok" : 0,
        "errmsg" : "could not find host matching read preference { mode: \"primary\" } for set rs1",
        "code" : 133
}

I have tried to add a shard but I receive the following error:

mongos> sh.addShard("firstRS/centos-m-1.ovm:27017")
{
        "ok" : 0,
        "errmsg" : "Cannot add firstRS/centos-m-1.ovm:27017 as a shard since     it is part of a config server replica set",
    "code" : 96
}

In replicaSet I see:

rs.conf()
{
        "_id" : "firstRS",
        "version" : 3,
        "configsvr" : true,
        "protocolVersion" : NumberLong(1),
        "members" : [
                {
                        "_id" : 0,
                        "host" : "centos-m-1.ovm:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 1,
                        "host" : "centos-m-2.ovm:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 2,
                        "host" : "centos-m-3.ovm:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                }
        ],
        "settings" : {
                "chainingAllowed" : true,
                "heartbeatIntervalMillis" : 2000,
                "heartbeatTimeoutSecs" : 10,
                "electionTimeoutMillis" : 10000,
                "getLastErrorModes" : {

                },
                "getLastErrorDefaults" : {
                        "w" : 1,
                        "wtimeout" : 0
                },
                "replicaSetId" : ObjectId("572e55c00408eaa72066cef3")
        }
}

I cannot understand. Could you help me?

MongoDB shell version: 3.2.6
connecting to: 10.1.10.12:28017/test

mongos> rs.status()
{
        "info" : "mongos",
        "ok" : 0,
        "errmsg" : "replSetGetStatus is not supported through mongos"
}

If I connect to config server and I give: rs.status() I found all nodes with one node highlighted as PRIMARY. After than I try to connect to a mongos instances and I try to add a shard:

sh.addShard( "rs1/10.1.10.14:28017" )

and I get error I reported previously and in log I found:

  2016-05-06T12:37:33.787Z W NETWORK  [conn1] No primary detected for set rs1
  2016-05-06T12:37:33.787Z I NETWORK  [conn1] All nodes for set rs1 are down. This has happened for 1 checks in a row. Polling will stop after 29 more failed checks


texts: Network Maintenance and High Availability Group Prep

texts:
We have had a RAID card on one of our switches die, and the replacement had issues which caused my availability groups to failover multiple times between my 2 nodes like a game of tennis and left a few databases in an unhealthy state.

Obviously, the card needs to still be replaced again and I want to be able to set a maintenance setting to not have the databases failover repeatedly for what will be a brief blip. This will take place at a time of low activity this time, not peak activity.

Are there any best practices relating to this?
What would be the safest way to leave the databases online for most of the time (except the blip) and not have them failover multiple times?

Do I shut down the inactive node? Do I stop data movement?

We have 2 nodes running SQL 2016 Standard and 50 availability groups.

Any tips or ideas would be great.
Thank you very much


texts: Create a custom db roles that uses a lookup table

texts:
Can you create a custom DB_role like truncate, select, insert, delete permission to tables that are stored in another table(like lookup) rather than altering each table? just wondering I'm not a DBA sorry, the reason why is the old database has only one schema.
the only thing I can think of is creating an SP and do a loop on that table.

Thanks


texts: Is it possible to build PostGIS extensions without building the entire project?

texts:
PostGIS has an extension called postgis_tiger_geocoder, I have a few questions about it (all pretty tied together),

Is it possible to build that without building the rest of PostGIS?
If so, can I use the copy I build with my distro-installed PostGIS.

Ubuntu packages required to buildpostigs

xsltproc libgeos-dev libproj-dev libgdal-dev libjson0-dev docbook-xsl


texts: Azure SQL Database - dedicated administrator connection (DAC)

texts:
If you check the setting in any of your Azure SQL databases, you will see that the value_in_use column value is zero for Remote admin connections. Meaning ‘Remote admin connections’ are not allowed from remote clients. There is no way to change that at the time of writing this question. sp_configure is not available for Azure SQL Database.
SELECT * FROM sys.configurations
WHERE NAME = 'remote admin connections'
ORDER BY NAME;

Does that mean Remote admin connections are not allowed for Azure SQL Databases?


texts: Relationships between subtypes of an entity

texts:
I have a very big (hundred of milions rows) table which represent an entity (event) having many subtypes (different kind of recorded events).
Each row is identified by an id (obj_id) and has an attribute obj_type that records the type of event.

All the subtypes share the most important attributes (represented on the main table) but a few of them (a minority) have aditional attributes that are represented in additional children tables.

I have to represent new relationships involving rows from the main table. For example record that an event of type 'A' is in relation with an event of type B and an event of type 'C' or 'D', or an event of type 'X' or 'Y' is in relation with an event of type 'Z'.

Which one of the two schemas below is better? (Is any of the two not advisable?)

Schema 1:

create table supertype(
obj_id varchar2(30) primary key,
obj_type varchar2(1) check (obj_type in ('A','B','C','D',..)),
attribute1  varchar2(1),
attribute2  varchar2(1),
attribute3  varchar2(1),
...
attributen  varchar2(1)
)

create table relation_A_with_B_and_cd(
obj_id_subtype_A varchar2(30) primary key  references supertype(obj_id),
obj_id_reference_to_subtype_B varchar2(30) references supertype(obj_id),
obj_id_reference_to_subtype_C_or_D varchar2(30) references supertype(obj_id),
attribute_det_a1  varchar2(1),
attribute_det_a2  varchar2(1),
)

create table relation_XY_with_Z(
obj_id_subtype_X_or_Y varchar2(30) primary key  references supertype(obj_id),
obj_id_reference_to_subtype_Z varchar2(30) references supertype(obj_id),
attribute_1 varchar2(1),
attribute_2  varchar2(1),
)

Schema 2:

create table supertype(
obj_id varchar2(30) primary key,
obj_type varchar2(1) check (obj_type in ('A','B','C','D',...)),
attribute1  varchar2(1),
attribute2  varchar2(1),
attribute3  varchar2(1)
)
alter table supertype add constraint UQ_type unique(obj_id,obj_type)

create table relation_A_with_B_and_cd(
obj_id_subtype_A varchar2(30) primary key ,
obj_type_subtype_A varchar2(1) check (obj_type in ('A')),
obj_id_reference_to_subtype_B varchar2(30) ,
obj_type_reference_to_subtype_B varchar2(1) check (obj_type_reference_to_subtype_B in ('B')),
obj_id_reference_to_subtype_CD varchar2(30) ,
obj_type reference_to_subtype_CD varchar2(1) check (obj_type reference_to_subtype_CD in ('C','D')),
attribute_det_a1  varchar2(1),
attribute_det_a2  varchar2(1),
constraint fk_subtype_a foreign key (obj_id_subtype_A,obj_type_subtype_A) references supertype  (obj_id,obj_type),
constraint fk_ref_subtype_B foreign key (obj_id_reference_to_subtype_B ,obj_type_reference_to_subtype_B) references supertype  (obj_id,obj_type),
constraint fk_ref_subtype_CD foreign key (obj_id_reference_to_subtype_CD ,obj_type_reference_to_subtype_CD) references supertype  (obj_id,obj_type),
)

create table relation_XY_with_Z(
obj_id_subtype_XY varchar2(30) primary key,
obj_type_subtype_XY varchar2(1) check (obj_type in ('X','Y')),
obj_id_reference_to_subtype_Z varchar2(30) ,
obj_type_reference_to_subtype_Z varchar2(1)  check (obj_type reference_to_subtype_Z in ('Z')),
attribute_1 varchar2(1),
attribute_2  varchar2(1),
constraint fk_subtype_XY foreign key (obj_id_subtype_XY,obj_type_subtype_XY) references supertype  (obj_id,obj_type),
constraint fk_ref_subtype_Z foreign key (obj_id_reference_to_subtype_Z ,obj_type reference_to_subtype_X) references supertype  (obj_id,obj_type)
)

Considerations

The second one duplicates some information but the constraint ensures the object ids in the relations are of the correct subtype. It leaves me perplexed however to declare unique (obj_id,obj_type) since this is not a minimal key, because obj_id is already the primary key.
The first one doesn't duplicate data but doesn't perform any check on the correctness of event types participating in the relation.


texts:
I'm playing around with the USING clause in RMAN, but cannot seem to get it to work.  Here is my RMAN command:

rman target username/password @${RMAN_DIR}/backup.rman using "db1"

When I run that (with double quotes, single quotes, no quotes, using a parameter), I get the error below.

I'm on Oracle 11.2.0.4 on Linux.

Argument     Value          Description
-----------------------------------------------------------------------------
target       quoted-string  connect-string for target database
catalog      quoted-string  connect-string for recovery catalog
nocatalog    none           if specified, then no recovery catalog
cmdfile      quoted-string  name of input command file
log          quoted-string  name of output message log file
trace        quoted-string  name of output debugging message log file
append       none           if specified, log is opened in append mode
debug        optional-args  activate debugging
msgno        none           show RMAN-nnnn prefix for all messages
send         quoted-string  send a command to the media manager
pipe         string         building block for pipe names
timeout      integer        number of seconds to wait for pipe input
checksyntax  none           check the command file for syntax errors
-----------------------------------------------------------------------------
Both single and double quotes (' or ") are accepted for a quoted-string.
Quotes are not required unless the string contains embedded white-space.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00552: syntax error in command line arguments
RMAN-01009: syntax error: found "db1": expecting one of: "double-quoted-string, identifier, integer, single-quoted-string"
RMAN-01007: at line 2 column 1 file: command line arguments


texts: Oracle Data Pump for extracting DDL as a set of files – one per object

texts:
I use Oracle Data Pump to extract the DDL for all schema objects – impdp with sqlfile parameter. It generates one huge SQL file. I want to split it up into several files – one file per one schema object. E.g., a package P should be stored in P.pks and P.pkb – the specification and the body, a table T – in T.tbl, etc.

These files are going to be put into some repository for revision control. That is why one huge SQL file containing all the DDL is inconvenient compared to the set of files for each object. I have not find any tool for splitting the Data Pump SQL file. Therefore I will have to write my own program for splitting the Data Pump SQL file and organizing folder structure for the repository.

Is there any Data Pump options/parameters to generate such set of files? Is there any other tool to do so?


texts:
I'm trying to import part of an xml file into a SQL Server database, but while the query successfully executes there is nothing in the table.

XML:

<?xml version="1.0" encoding="utf-16"?>
<config>
<company>
0514
<Name>Company A</Name>
<ContactName>Alan Smith</ContactName>
<ContactTel></ContactTel>
<ContactMail>random@random.net</ContactMail>
<System>type A</System>
</company>
</config>

Then here is the query I run

DECLARE @XML XML = 'C:\Users\Ramdon\file.xml';
INSERT INTO #tmp_Company2
SELECT
Code = Events.value('@Code', 'char(6)'),
Name = Events.value('@Name', 'varchar(100)'),
ContactName =Events.value('@ContactName', 'varchar(100)'),
ContactTel =Events.value('@ContactTel', 'varchar(15)'),
ContactMail =Events.value('@ContactMail', 'varchar(100)'),
System =Events.value('@System', 'char(20)'),
FROM @XML.nodes('//config/company') as  XTbl(Events);

select * from #tmp_Company2;

Everything I've read suggests it should work but I get nothing.


texts:
I would be very grateful if someone can help me here: in my database, I have prices recorded at a particular time in a day over years. So for each vendor or station, there can be several price data points within a day. The data is stored in a timestamp format so I can see which specific time within a specific day that the price was recorded.

My problem: I want to be able to select the price data for a period e.g. between 2014-01-01 and 2014-03-31 and at the same time select all the data within a specific time period e.g., between 08:00:00 and 20:00:00 for each day. I attempted it with this:

SELECT  station.id,
        DATE(item_price.date_time) as mydate,
        TIME(item_price.date_time) as mytime,
        item_price.price
    FROM  station
    INNER JOIN  item_price  ON station.id=item_price.station
    WHERE  date_time >='2014-01-01'
      AND  date_time < '2014-03-31'
      AND  date_time >= '08:00:00'
      AND  date_time <  '20:00:00'
    ORDER BY  item_price.date_time;

But, I'm unable to obtain the price data between '08:00:00' and '20:00:00' for each day in the specified period. Can someone please help me out?


texts: Does SQL server support an in-built mechanism to copy a row to a log or history table when it is updated?

texts: Is there an in-built mechanism that copies a row to a log or another table when it is updated. Or is the only option to write a custom trigger on update.


texts: MS access >2gb and inaccessible: Can't be repaired followed by 'Object invalid or no longer set'

texts:
We have added 2 queries that should drop and load tables into our DB. For an (at this time) untracable 'mistake', it is likely that the queries only loaded and did not drop any tables to end up with a DB over 2GB. At first, the debugger showed the line of the loading query, but after a restart Access gives the error "Can't be repaired", followed by the error "Object invalid or no longer set".

I have tried numerous workarounds in order to compact the DB from excel using DAO, various repair tools (which did not return the forms and reports) but still I do not have my complete database back.

Do you have any further suggestions on how I can save this DB?


texts: Convert varchar2 column to number in Oracle

texts:
I have a table emp_details.

The column emp_salary is of type varchar2 in which certain rows are null:

emp_salary
100,34
null
20,2
30,3
null

I need a result in which the column type is number:

emp_salary
100.34
null
20.2
30.3
null

My attempt in code is:

select emp_salary
from emp_details
where emp_salary = to_number(emp_salary ,'9999D99','nls_numeric_characters=,.');

Error:

  ORA-01722: invalid number
  01722. 00000 -  "invalid number"
  *Cause:    The specified number was invalid.
  *Action:   Specify a valid number.

How can I fix it?

I don't mean to compare the values. I just want to change the varchar to number. I will use it in a small part of my bigger function, using TO_NUMBER.


texts: Is incrementing/decrementing a column from a large set of rows bad practice?

texts:
I'm designing a queue system and trying to decide between decrementing a column of a large set of rows or giving the row a priority and calculating the queue position with this.

That said, I'm wondering if decrementing a position column for a large set of rows is considered bad practice?

UPDATE <table>
  SET position = position - 1
  WHERE event = <event_id>;

Edit: To provide details on what I'm designing

I'm keeping track of who is next in line. If a car shop can only work on 3 cars at once then any new customer that shows up will need to wait in line. The ordering of these new customers is important as it is a first come first serve basis (FIFO).

When a garage opens up, the next person in line is popped off the queue and all other waiting customers get moved up one position in line.


texts:
take a look at this:

> cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
umask 022
#DISPLAY=:0.0
#export DISPLAY
TMP=/tmp
TMPDIR=/tmp

ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_HOME

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:/usr/local/bin
export PATH

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

ORACLE_SID=SSAE
export ORACLE_SID

looks like working

~ # service dbora status

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 24-APR-2012 10:01:40

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date                09-APR-2012 13:19:59
Uptime                    14 days 20 hr. 41 min. 41 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=t2libora1)(PORT=1521)))
Services Summary...
Service "xxxx" has 1 instance(s).
  Instance "xxxx", status READY, has 1 handler(s) for this service...
Service "xxxxDB" has 1 instance(s).
  Instance "xxxx", status READY, has 1 handler(s) for this service...
Service "xxxx_XPT" has 1 instance(s).
  Instance "xxxx", status READY, has 1 handler(s) for this service...
The command completed successfully

but cant connect

:~ # su oracle
>  sqlplus / as sysdba
bash: sqlplus: no se encontró la orden

I'm not a oracle guy ... any help I appreciate.. (sorry my english)


texts:
We got a production issue last week (high CPU spike) on 25-Feb due to which server reboot was performed but ticket landed our DBA queue just today for analysis on CPU spike. Could you pls help me in modifying the below script to determine the resource which caused on 25th or pls suggest any alternatives

DECLARE @ts_now bigint
SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks)  FROM sys.dm_os_sys_info
SELECT top 20 record_id, EventTime,
  CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization,
  CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization
FROM
(
  SELECT
    record.value('(Record/@id)[1]', 'int') AS record_id,
    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,
    100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,
    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 ,
    100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,
    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2
  FROM (
    SELECT timestamp, CONVERT (xml, record) AS record
    FROM sys.dm_os_ring_buffers
    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'
      AND record LIKE '%<SystemHealth>%') AS t
) AS t
ORDER BY record_id desc


texts: Join or Replace colum value based on query

texts:
I have this query:

SELECT h.name, i.name, i.delay, i.delay_flex, i.status, i.templateid, i.params, i.description
FROM items as i
LEFT JOIN hosts as h
ON i.hostid = h.hostid
AND i.type = 11
AND i.status = 0
WHERE h.name LIKE "ABC%" OR h.name LIKE "123%"
ORDER BY templateid DESC
LIMIT 1;

Result is:

h.name | i.name | i.delay | i.delay_flex | i.status | 43206 | i.params | i.description |

I need to replace value 43206 with this query:

SELECT name
FROM hosts
WHERE hostid = (SELECT hostid
                FROM items
                WHERE itemid = (SELECT '43206'));

The result is: Template XPTO

And expected result is:

    h.name | i.name | i.delay | i.delay_flex | i.status | Template XPTO | i.params | i.description |


texts: In what cases is using ClickHouseDb and the like a neccessity?

texts:
An open source for website analytics - https://github.com/plausible/analytics
They use Postgresql and ClickHouseDb.
When it comes to web analytics, there're tons of events going that need to be tracked. From the point of view of database, why is using ClickHouseDb in this project neccessity? Why wouldn't Postgresql, which is relational database, alone do?
Yes, ClickHouseDb has been created specifically for analytical processing. But still, why wouldn't Postgresql alone do? Are Postgresql, MySql and the like uncapable of handling lots of inserts that occurr simateneously?


texts:
I'm looking for a solution in postgresql to insert or update a row, based on the timestamp of that row.
What I'm looking for is the following:
I want to insert the use of a specific hashtag, and count this use, based on the last hour.
+--------------------------------------------------+
| hashtag     | count | timestamp                  |
+--------------------------------------------------+
| hello       | 1     | 2020-01-01 12:00:00+00.00  |
| world       | 3     | 2020-01-01 12:00:00+00.00  |
| hello       | 2     | 2020-01-01 11:00:00+00.00  |
| ...                                              |
+--------------------------------------------------+

What I want to achieve:
I want to insert a new row if there is no row for the specific hashtag in the last hour created (based on the timestamp). If a row exists with timestamp > NOW() - INTERVAL '1 HOUR', I just want to update the count of that row.
How can I achieve this? Can I do this with a CHECK-constraint?


texts: User and login mapping after an SQL Server restore

texts:
I need to restore a DB from an SQL Server instance to another and I am well aware that the DB users will be transferred but the logins won't. I have created a script to be able to create the logins on the destination SQL Server instance, including the password hashed and the login SID. However, I am uncertain if it makes a difference whether the logins are created on the destination instance prior to the DB restore or after the DB is restored.

If the logins are created first and then the DB is restored, the DB users will map with the existing corresponding logins accordingly due to SID, however, if I do the DB restore and create the logins afterwards, will they still map automatically or will one need to do an ALTER USER WITH LOGIN or similar?


texts:
Trying to install sp_BlitzCache on SQL Server 2008, I get:

Msg 102, Level 15, State 1, Procedure sp_BlitzCache, Line 2705 Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Procedure sp_BlitzCache, Line 2715 Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Procedure sp_BlitzCache, Line 2865 Incorrect syntax near '.'.

Line 2705 is :

/* END Testing using XML nodes to speed up processing */

Line 2865 is the FROM line in:

/* Set configuration values */
SELECT  @ctp = NULLIF(CAST(value AS INT), 0)
FROM    sys.configurations
WHERE   name = 'cost threshold for parallelism'
OPTION (RECOMPILE);

It appears to be version 5.5

The SQL Server version is 10.0.6000.29.

When I page through the code, the first error I see highlighted is on #1162 in the source file: (and 4 more after that.)

SELECT CAST('' AS XML).value('xs:hexBinary( substring(sql:variable("@individual"), sql:column("t.pos")) )', 'varbinary(max)')

"Cannot call methods on int."

The next error is on line 2032 in the source - a select statement with

q.n.query('.') AS statement

Can't find q as a column, function, or q.n.query is ambiguous.

After that there are more cascading errors.


texts: Error writing to file c: windows\syswow64\mfc100.dll during installation of SQL Server 2014

texts:
During installation of SQL Server 2014 there is error appears Error writing to file c: windows\syswow64\mfc100.dll verify that you've access to that directory as you can see below image,

I navigate to that directory and not found mfc100.dll file as you can see below,

How can i resolve this issue kindly help


texts:
I have two server users, lets say admin and notadmin. I need to test notadmin's permissions. I need set and test VIEW SERVER STATE permission on notadmin, but usually I have access only to database only as admin. My admin is not sysadmin, because of problem with default schema (sysadmins have always default schema dbo, but i need something else), but I can set almost any permissions I need.
I proceed from this
For testing permissions there is HAS_PERMS_BY_NAME and it works well. So I tried to impersonate notadmin. I set IMPERSONATE ANY LOGIN permission to admin, and it works well. I test it this way (on master db):
SELECT HAS_PERMS_BY_NAME('notadmin', 'LOGIN', 'IMPERSONATE')  -- return 1
EXECUTE AS login = 'notadmin'
    SELECT HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE')  -- return 1
REVERT

Because I don't like ANY (this way I can impersonate someone with sysadmin role), I tried set this
GRANT IMPERSONATE ON LOGIN::admin to notadmin

and then a run same query. But it failed - returned 0 and error:
Cannot execute as the server principal because the principal "notadmin" does not exist, this type of principal cannot be impersonated, or you do not have permission.
Principal have to exist and can by impersonated, with IMPERSONATE ANY LOGIN it works. So which permission I need to impersonate notadmin?
Or is there some other way how can I test some other db user permission and do not allow impersonate any other users?

Edit (16.04.2021 12:13):
I find that HAS_PERMS_BY_NAME returns same values as system views in master db. So SELECT * FROM sys.server_permissions is useless


texts: MongoDB not using all memory in Windows server

texts:
I have a stand-alone MongoDB instance running in windows. It's recently experiencing slowness and my troubleshooting has found the following symptoms.

The slowness normally occurs after a full text search query against a huge collection. I guess this is because it swaps many pages in the collection from disk into memory hence data from other collections are squeezed out of memory.
The server memory usage is capped around 55%. The server (Win2012) has 32GB of memory but MongoDB is not trying to utilize beyond 16GB.

I have seen a couple of Q&A in limiting MongoDB memory usage but not on how to encourage MongoDB to be more greedy.

Would anyone has experience with similar issue?

MongoDB: 3.2.0
Storage Engine: wiredTiger
OS: Win2012
Memory: 32GB

Thanks


texts: More than 15000 sleeping sessions in SQL Server 2014

texts:
The CPU utilization is going to 100% regularly, and when I check SP_who2, it is showing around 20000 sessions, most of them are sleeping (also utilizing the CPU).
I think the CPU load is related to the sleeping sessions

The application name is showing as Microsoft JDBC.

Most of the sessions are showing 'SOS_SCHEDULER_YIELD as last_wait_type. After restarting the Windows Server, the CPU utilzation has come down, but the session count is increasing.

The server has 32 cores.

The application team is telling they are using some 'Connection Pool' for the the connection management.

How can I investigate or solve the number of sleeping sessions?


texts: Is there any performance benefit of using ENUM instead of TinyInt in big data for MySQL?

texts:
I read a lot of articles about ENUM. but most of them are comparing ENUM to joins not tinyint.

I need fields with 2-5 different statuses.
In this situation is there any performance or storage benefit or drawback of using ENUM?


texts: Use extra column to save an unaccented copy of a string instead of using unaccent module

texts:
I am heavily using the unaccent module in a table with 500.000 rows where each row contains  proportionally a lot of accented characters.

The issue I am facing is that my queries have become time consuming monsters.

Is it utterly unacceptable -in terms of database design- to save an unaccented copy of each string in another column of the table in question and query that one instead of using the unaccent module?


texts: Select command returning different column values

texts:
I am just starting SQL and am using version SQL server 2016(few days in to learning)

I am using the WideWorldImporters DB from microsoft.

The basics of my question is I don't understand some results I am getting and why?
I run this command:
use WideWorldImporters
select top (2) OrderID
From Sales.Orders;

and it returns a table with 2 lines as expected but the OrerID column values for the two rows of 1 and 16

However when I run the command:
select top (2) OrderID, CustomerID
From Sales.Orders;

I get the additional column for customerID with 1 and 1, and for the ORDERID column it now returns row values of 2934 and 3482.

I don't understand why OrderID would return the two different results. Am I running the command incorrectly?

I am trying to learn the sum and avg aggregate commands on just the top X rows of the column OrderID with the GROUP BY CUSTOMERID for testing, but ultimately want to do below

This is what I was trying to imagine doing:
If you imagined that OrderID was in fact an total order price, I was trying to sum up each customers order total from all purchases. So sum up every row with customerid =  N and then list all the totals for every customerid. My select top 2 was for testing purposes so it wasn't final results, but led me to discrepancy.


texts: How to avoid Repetition in the Database Design?

texts:
I have currently the following database structure,

            Retailers
-----------------------------------------------------------------
ID    Name    XXX    XXXX    XXXX    XXXX    XXXX    XXXX

            ProductTypes
-----------------------------------------------------------------
ID    Name    RetailerID    XXXX    XXXX    XXXX    XXXX    XXXX

            Products
-----------------------------------------------------------------
ID    Name    ProductTypeID    RetailerID    XXX    XXXX

            Attributes
-----------------------------------------------------------------
ID    Name    ProductTypeID    RetailerID    GroupID    XXXX

            AttributesGroups
-----------------------------------------------------------------
ID    Name    ProductTypeID    RetailerID    XXXX    XXXX    XXXX

            AttributesValues
-----------------------------------------------------------------
ID    Value    RetailerID    ProductID    AttributeID    XXXX

Let say if you have a product called HP ProBook 4530s 15.6" Notebook then it may contain a Product Type called Laptop, it may belongs to Retailer1. It may contains Battery, Power, Manufacturer, Processor Name, etc attributes.  May be Manufacturer and Processor are belongs to General attribute group. These attributes values are saved in AttributesValues table.

This database schema works very fine. But now I am facing an issue. Some products are needed to be completely shared between other retailers. Some products are needed to be completely shared except for few attributes between some retailers because some retailers need to show additional attributes of the same product and some retailers need to remove some attributes of the same product and some wants to update some attributes.

Currently, the above schema works with a flaw that each product(and it's related) things are repeated for retailer which uses this product. How to avoid this situation and design the database so that it works smoothly as well as no or limited repeatation.


texts: PgAdmin4 shows instances of PostgreSQL 12.4 and PostgreSQL 10 with databases but no v10 artifacts exist on disk for v10 instance

texts:
I've installed EDB PostgreSQL v12.4 with the EDB MAC installer/de-installer on my MAC PRO Mojave server as I have done many times before.
However, I recently installed PostgreSQL ver 12.4 to support PostGIS 3.0.2. Then I noticed that an instance of PostgreSQL v10 was showing in the PgAdmin4 viewer. But the MAC /Library/PostgreSQL/ folder only contained a version 12 folder (/Library/PostgreSQL/12/data)
My previous installation was of EDB PostgreSQL v10 due to a limitation of Pentaho Server 9.03. So I'm not entirely surprised to see both versions in PgAdmin's display in Chrome.
But as a precaution, I de-installed the PostgreSQL v12 using the EDB Mac tooling. And I deleted all subdirectories of the /Library/PostgreSQL/ folder.
Then I re-installed EDB PostgreSQL v12 to a clean-empty /LibraryPostgreSQL/ folder.
However, both instances v12 and v10 are now visible again in PgAdmin4, despite the fact that there is no 10/data folder under the /Library/PostgreSQL/ folder, only a 12/data folder.
So... is this

A result of left-over artifacts such as LVM folders in the LVM-assigned Hard Drives that are designated as TableSpaces for the ingest, manipulate, extract tablespaces of the Pentaho Server and PDI data element categories?

An additional layer of pg configuration files for v10 hanging around in the /Library/PostgreSQl/12/data folders?

A phantom reference to a nonexistent instance of PostgreSQL v10 referenced in some HTML History in PgAdmin4's hidden/internal installation of Apache HTTP Server?

A file or folder reference held by the dedicated/internal Pentaho Server's TomCat Web server that is used to display the HTML interfaces of Pentaho Server 9.03 or Pentaho Data Integration tool?

or something else?
Your perspectives will be appreciated!
Thanks...


texts: overstating field size in database design

texts:
I have some fields for my tables that are strings and at the moment, most of the field size have pretty high character limits. For instance, 100 char for the street name. Is there a penalty for using large field size? If I change the limit to 30 char for this field for instance, will there be a performance gain or an efficiency with size? There would about 50 fields that could be candidates for shrinkage.

Thanks for your suggestions.


texts: Is IF ELSE better in this stored procedure than a Parameter?

texts:
I found a stored procedure in our sql server 2016 db

Create PROCEDURE XXX
   @complicatedQuery bit
AS
BEGIN
   SELECT some fields FROM
     Table A join Table B on something
     JOIN Giant Table C on @complicatedQuery=1 and SomeOtherCondition
END

Will we get bit better performance if I do this:

Create PROCEDURE XXX
       @complicatedQuery bit
    AS
    BEGIN
       IF @complicatedQuery =0 then
       SELECT some fields FROM
         Table A join Table B on something
       ELSE
       SELECT some fields FROM
         Table A join Table B on something
         JOIN Giant Table C on complicatedQuery=1 and SomeOtherCondition

    END

I think in both cases,sql search will create a plan that goes through both branches. But will second one be better or in reality there is no performance difference (as the first sp has @complicatedQuery in the table join condition anyway)?

I read another post which says that it is best to create sub stored procedures to cover different scenarios.

Is there a way to write some tests to prove it?

Many Thanks


texts: Need advice on how to migrate from existing database mirroring to availability groups

texts:
Need some practical/general guidance on moving from database mirroring to AOAG

Current setup - 2 standalone servers with database mirroring configured as async.

Server A: Windows 2016 running SQL Server 2017, no cluster, simple standalone server as Primary
Server B: Windows 2016 running SQL Server 2017, no cluster, simple standalone server as Mirror
File share server will be provided in different DC

Distance between the servers is about 150 miles.

We need to use the above servers only and convert database mirroring to AG's with automatic failover option to provide HA as a bonus because with the above setup it's mostly a DR solution.

Clusterless AG came to my find but it seems HA wont be available as automatic failover might not work. Am I missing something here or is it possible?
We can have an outage up to 8 hours to convert db mirroring to AG if needed.

How should I plan or if any resources available to do this? I tried searching for lot of articles but could not find any, please suggest


texts:
My problem is a relatively simple one; unfortunately, I haven't been able to find any solutions from searching around.

I have a SQL table called alerts from which I'm trying to export data into CSV files. Everything works fine except when including the final column custom_info. This column contains a lot of important diagnostic information, but it's in JSON format, which means there are commas everywhere. As such, my CSV file ends up having a new column for every single field separated by a comma.

Is there a way to force this data to remain in one cell (i.e., retain it's proper JSON format)? I've tried playing around with different options for FIELDS TERMINATED BY, but to no avail.

Many thanks!


texts: problem with selecting correct values from database

texts:
I have the following database and I'm having problems writing the correct select phrase. I am new to SQL and hope you could help me by showing me the correct way to select the desired data:

Employee (eid, ename, salary, did, classification)
Department (did, dname, floor, head)
dbudget (did, byear, budget)
Project (pid, pname, did, budget, ddate)
Onproject (pid, eid, fdate)

Explanation regarding the database:

Department.did = the department id of the project (what are they working on)
dbudget.byear  = the year of the budget

My assignment:

Show the name and salary of the worker with classification 4 who earns the most.

My query:

select ename ,salary from Employee where classification='4'

I don't know how to select the who earns the most....
Show the department id (did) and its name (dname) that have only two employees with classification 3

My query:

select did,dname from Department where classification='3'

I don't know how to select so that it will show only those who have two employees with classification
Show department id (did), first year, and the year after it of departments that in two consequentive years their salary was less than 700000 (hard)

I don't know how to create a query that will represent the first year and the year after it.


texts: PLSQL using cursors or functions to query repeated data

texts:
Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)?
Whats the difference?
What are the best practices?
What are the other considerations?
Or is there another better way?

1) using a package cursor

cursor getLicenseStatus_cur (in_license_no varchar2) is
    SELECT status, status_dt from tbl_license where licence_no=in_license_no;

--use:
OPEN getLicenseStatus_cur('123');
fetch getLicenseStatus_cur into l_status, l_status_dt;
EXIT WHEN sql%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('l_status= ' || l_status || ', l_status_dt= ' || l_status_dt);
close getLicenseStatus_cur('123');

2) or using a function and passing a rowtype

create or replace function getLicenseStatus(in_license_no varchar2(10))
RETURN tbl_license%ROWTYPE
    as
    output_rec tbl_license%ROWTYPE;
begin
    SELECT * into output_rec
    from tbl_license where licence_no=in_license_no;
    return output_rec;
end;

--use
lic_rec users%ROWTYPE;
lic_rec := getLicenseStatus('123');
DBMS_OUTPUT.PUT_LINE('l_status= ' || lic_rec.status || ', l_status_dt= ' || lic_rec .status_dt);

3) or using a function and passing a sys refcursor

create or replace function getLicenseStatus(in_license_no varchar2(10))
return sys_refcursor as
    v_curs sys_refcursor;
begin
    open v_curs for SELECT status, status_dt from tbl_license where licence_no=in_license_no;
    return v_curs;
end;

--use:
v_rc := getLicenseStatus('123');
fetch v_rc into l_status , l_status_dt;
exit when sql%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('l_status= ' || l_status || ', l_status_dt= ' || l_status_dt);
close v_rc ;


texts: Do I need GRANT for INSERT to table If has EXECUTION GRANT for procedure?

texts:
I have five domain users and five roles in my database. GRANT EXECUTE adds to the appropriate roles. Roles are included in other roles, i.e. at most we have one role that has grants for two procedures, below two more roles that have access to four and three procedures, and finally we have two roles. With this solution, if I give the GRANT EXECUTE permission to the highest role, all roles will have access to this procedure.
My question:
Let's say the procedure has UPDATE and INSERT operations. Is it enough to GRANT EXECUTE to a given procedure for a given role so that the user can use this procedure and actually do UPDATE and INSERT on the table, or maybe apart from GRANT EXECUTE I need to grant the role UPDATE and INSERT to all tables within the given procedure?


texts:
What would be a typical situation under which the following error would occur, and what does it mean?

Also note that I find myself disconnected from the server when the query fails (but I have not myself observed that).

Location:     "xact.cpp":4253
Expression:   !m_parNestedXactCnt
SPID:         56
Process ID:   2208
Description:  Trying to use the transaction while there are 1 parallel nested xacts outstanding
Location:     "xact.cpp":4362
Expression:   !m_parNestedXactCnt
SPID:         56
Process ID:   2208
Description:  Trying to use the transaction while there are 1 parallel nested xacts outstanding
Msg 3624, Level 20, State 1, Line 131
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.


texts: Is it possible to increase query performance on a narrow table with millions of rows?

texts:
I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as it gets?

The Query

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31';

The Table

CREATE TABLE [dbo].[Heartbeats](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DeviceID] [int] NOT NULL,
    [IsPUp] [bit] NOT NULL,
    [IsWebUp] [bit] NOT NULL,
    [IsPingUp] [bit] NOT NULL,
    [DateEntered] [datetime] NOT NULL,
 CONSTRAINT [PK_Heartbeats] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The Index

CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats]
(
    [DateEntered] ASC,
    [DeviceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Would adding additional indexes help? If so, what would they look like? The current performance is acceptable, because the query is only run occasionally, but I'm wondering as a learning exercise, is there anything I can do to make this faster?

UPDATE

When I change the query to use a force index hint, the query executes in 50ms:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats] WITH(INDEX(CommonQueryIndex))
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31'

Adding a correctly selective DeviceID clause also hits the 50ms range:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' AND DeviceID = 4;

If I add ORDER BY [DateEntered], [DeviceID] to the original query, I am in the 50ms range:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31'
ORDER BY [DateEntered], [DeviceID];

These all use the index I was expecting (CommonQueryIndex) so, I suppose my question is now, is there a way to force this index to be used on queries like this? Or is the size of my table throwing off the optimizer too much and I must just use an ORDER BY or a hint?


texts: MSSQL Mixed-Mode Users cant connect unless they are in the server sysadmin role

texts:
SQL Server 10.0.5520 (2008 std sp3)
My goal is to enable data connections in SSMS or Visual Studio for windows users w/out them being sysadmins. Ideally this would be done through Active Directory groups, but for now I'm just targeting one use (my own) for simplicity's sake. I am using 'sa' account to make these changes.

Using either Visual Studio data connection or SSMS, my users are unable to establish a connection to the database unless they are in the server's sysadmin role.

Login failed for user 'DOMAIN\user'.
Reason: Token-based server access validation failed with an infrastructure error.
Check for previous errors. [CLIENT: xxx.xxx.xxx.xxx]

The previous error states:

Error: 18456, Severity: 14, State: 11.

In SSMS, a user's server roles are limited to public. If I attempt to add the server to Visual Studio. The above error occurs, and the Test connection button fails.

When I add the sysadmin server role the connection succeeds and I am able to browse the databases to select as the initial catalog, and the 'Test Connection' button succeeds.

I have confirmed that public has been granted 'connect' permission for every type of TSQL securable type.

I have given the user total control over the target database on the server.

It seems as though this is totally related to server-roles, and that somewhere something is rejecting non sysadmin accounts. (adding all roles other than sysadmin also causes a rejection).

Permission to connect to database engine is granted to server user.

What are some of the things I should check to help enable the ability to connect?

Follow-Up:

EXECUTE AS LOGIN = 'DOMAIN\user';
SELECT * FROM fn_my_permissions(NULL, 'SERVER')
    ORDER BY subentity_name, permission_name ;
REVERT;
GO

returned 3 records

#, entity_name, subentity_name, permission_name
1, server, , CONNECT SQL
2, server, , VIEW ANY DATABASE
3, server, , VIEW ANY DEFINITION

This may or may not be relevant. I had an AD group created and made the group sysadmin, and all the users in the group can connect... I don't think feel like AD is the culprit. There is something special about sysadmin that i am missing or that was misconfigured early on....

I was able to grant the windows user access w/out the sysadmin role by granting them CONTROL SERVER in the securables list under the server-level user properties.


texts: How can I Export Warnings generated by HeidiSQL

texts:
I am trying to do a bulk data upload into a MariaDB database.  The data file has over 3M records.  When the SQL runs the LOAD DATA INFILE statement, I get over 9600 warnings where there were problems when the data was inserted.  In order to chase down why there were problems, I need to get the row numbers of each record where an error was encountered so I can filter the source file and examine the problem columns.

I'm using HeidiSQL as my front-end, and there doesn't appear to be a way to export the list of warnings.  I tried to direct the output of the SHOW WARNINGS statement to an external file like you would a query, but that doesn't work (probably because the warnings are not persistent data like data in a table would be).

Does anyone know how I can save the list of Warnings to an external file?


texts: How to deploy SSRS reports build through Report Builder?

texts:
I would be building new reports, or correcting the already built SSRS reports through report builder. Once the change is done in Development side, then how do you publish the reports on test server/UAT or on production.

What methodology is supposed to be followed?

More information: SSRS would be used of SQL Server 2008 R2 edition, Report builder version is 3.0 and database is also SQL Server 2008 R2.

Let me know if more information is required.

Please Guide Me! Thanks!!


texts: MySQL - huge Dictionary memory allocated

texts:
Our MySQL 5.5.38 servers have 1000-3000 InnoDB databases each (with about 100 tables each). All data is in single file. Most of DBs are active. While it's expected that information schema should be huge - on those servers Dictionary memory allocated from 3 to 15 GB, competing with buffer pool.

Is this an acceptable scenario? What risks are here, what settings can be used to manage this dictionary memory?

BUFFER POOL AND MEMORY

Total memory allocated 519405568; in additional pool allocated 0
Dictionary memory allocated 3839216241
Buffer pool size   30976


texts: Pg install: "The database cluster initialisation failed"

texts:
(Note: This question has been compeltely rewritten from its original form, so it reflects the actual problem and will help others find a solution to the same issue).

I still haven't been able to successfully install PostgreSQL on my Windows 7 Ultimate x64 computer. See this prior question for some history.

I did a fresh instillation of 9.2 and the only error I got was this one:

It reads:

  "Problem running post-install step. Installation may not complete
  correctly. The database cluster initialisation failed".

This occurs even after I do a complete manual uninstall of PostgreSQL and pgAmdin-III:

Ran the PostgreSQL uninstaller, which failed with the error Error stopping service postgresql-x64-9.2.
Removed all the PostgreSQL-related registry entries for PostgreSQL under HKEY_LOCAL_MACHINE\SOFTWARE and HKEY_CURRENT_USER\SOFTWARE
Removed the postgres user account using net user postgres /delete in an Administrator command prompt (shift-right clicked on "Command Prompt" in start menu, chose "Run as administrator")
Removed the postgres user profile
Deleted the C:\Program Files\PostgreSQL\ directory, including the data directory within %appdata%
Removed some PgAdmin-III registry entries that pointed to PostgreSQL
Removed the service account for postgres from services.msc

When I reboot and try the installer again it fails with the same message.

The install-postgresql.log file from %TEMP% contains:

Executing cscript //NoLogo "C:\Program Files\PostgreSQL\9.2/installer/server/initcluster.vbs" "NT AUTHORITY\NetworkService" "postgres" "****" "C:\Program Files\PostgreSQL\9.2" "C:\Program Files\PostgreSQL\9.2\data" 5432 "DEFAULT"
Script exit code: 1

then another error a bit later:

creating template1 database in C:/Program Files/PostgreSQL/9.2/data/base/1 ... initdb: could not execute command ""C:/Program Files/PostgreSQL/9.2/bin/postgres.exe" --boot -x1 -F ": No error

See full installer log file download here.


texts: How does Log shipping know to keep track

texts:
I have set up log shipping between 2 SQL Servers (SQL Server 2008 and SQL Server 2012).

At first I took a full backup of the first one and restored it on the other one and then I set up the log shipping. The database's first backup-restore took a few hours and below are my questions that I need to understand :

How does the log shipping process know from when to start the logs it is shipping?
Does it start from when the last backup-restore process happened or from the moment the log shipping itself is set ?
Can I be sure that no information was lost in these few hours ?


texts:
If I try
select * from my_table

I get:
"Table 'my_table' doesn't exist in engine"

but
show tables

contains this table in the list.
I tried:
mysqlcheck -u root -p --check --all-databases

but also get
Table 'my_table' doesn't exist in engine

Database version:
Ver 15.1 Distrib 10.4.11-MariaDB, for Linux (x86_64) using readline 5.1

I checked /var/lib/mysql/mydatabase – *.frm and *.ibd for certain tables exist
UDPATE:
I tried this query:
select * from innodb_table_stats
Table 'mysql.innodb_table_stats' doesn't exist in engine


texts: Copy Permission From Windows Based user To Local user

texts: I want to know if it is possible to copy the permissions of a windows user to a locally created user on SQL server?


texts: Is the Advanced Security Pack required for windows authenticaion?

texts:
Can anyone tell me if the Oracle Advanced Security Pack is required for single sign in under Windows?

I've been told we are not licensed for the "Oracle Advanced Security Pack" and need to remove it, which appears to disable our existing externally identified logins.

Our version of Oracle is

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


texts: Prevent MariaDB from filling up tmpdir and hanging the system

texts:
We're running MariaDB version 10. To speed up queries we have set up tmpdir to be RAMDISK with size of 35GB.

The challenge we have is that from time to time (like once or twice a week) MariaDB fills the tmpdir, after which the whole system freezes up till we have to stop MariaDB, unmount the tmpdir and remount.

Initially the tmpdir was 15GB we have been increasing it over time with hope that more space will prevent MariaDB filling up the tmpdir but we realize it's not ending. We could still expand tmpdir but we don't have unlimited RAM and are not sure how much will be enough.

At this time we're wondering whether there's a way to prevent MariaDB from filling up tmpdir? The alternative is to move tmpdir to disk but this is a route we're avoiding since it would present a performance nightmare.

open_files_limit=5000
query_cache_size = 0
wait_timeout=200
tmp_table_size=512M
max_heap_table_size=512M
thread_cache_size=64

key_buffer_size=256M
max_allowed_packet=512M

table_open_cache=10240
table_definition_cache=32768

delayed_insert_timeout=20 # Turn on if max_connections being reached due to delayed inserts
delayed_queue_size=300 # Turn on if max_connections being reached due to delayed inserts

myisam_sort_buffer_size=512M # can be increased per sessions if needed for alter tables (indexes, repair)

query_cache_limit=32M # leave at default unless there is a good reason
query_cache_type=1

read_rnd_buffer_size=16M # leave at default unless there is a good reason
read_buffer_size=2048M

collation_server=utf8_unicode_ci
character_set_server=utf8

general_log=0
log-output=TABLE # select * from mysql.general_log order by event_time desc limit 10;

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_file_per_table=1
innodb_buffer_pool_size=16448M
innodb_log_buffer_size=100M
innodb_thread_concurrency=8 # Number of physical + virtual CPU's, preset when server is provisioned to have correct # of cores
innodb_change_buffering=all
innodb_flush_log_at_trx_commit=0
innodb_support_xa=0
innodb_doublewrite = 0
innodb-flush-log-at-trx-commit = 2


texts: Restrictions on creating new SQL tables?

texts:
I'm working on a huge database consist of more than a 1000 table, the database is increasing dramatically and I need to put some restrictions on creating new tables. thinking out loudly, a prompt message could show up when the developer create new table or creating a rule for adding the purpose behind creating the table? Also I need to find a way that temp tables or testing tables will be scheduled somewhere for a clean up process later on.
Please don't hesitate to share any idea could be useful! :)


texts:
I have 2 tables: TVSHOW and ACTOR. I want to reject records if the inserted actor in TVSHOW does not exist in the ACTOR table using DB2.

db2 "ALTER TABLE TVSHOW ADD CONSTRAINT check_actor CHECK (actor in (select id from ACTOR))"

I keep running into, During SQL processing it returned: SQL0546N  The check constraint "check_actor" is invalid.

How can I fix this?


texts: What is blocking 'SELECT... WITH(NOLOCK)'?

texts:
While following BrentOzar's "How Much is Offline During an Index Rebuild?" I was curious to run:

SELECT Quantity FROM [Production].[TransactionHistory]  WITH(NOLOCK)

while executing a rebuild from mentioned article and observe that it is being delayed until index rebuild finished

What is blocking SELECT ... WITH(NO LOCK) and how to avoid it?

Update:
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
    Enterprise Evaluation Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)


texts:
I've got this little ditty to concatenate all the columns in a table, for writing scripts quickly:

    create function [dbo].[get_columns] (@table_name varchar(255))
    returns varchar(max)

    as
    begin
    declare @name varchar(255),
    @object_id varchar(255),
    @string varchar(max)

    set @object_id = (select object_id from sys.objects with (nolock) where name = @table_name and type = 'u')

    set @string = (select stuff( (
    select ', ' + name   from sys.columns with (nolock)
    where object_id = @object_id
    and name != 'row_timestamp'
    for xml path ('')),1,2,''))

    return @string
    end

Question: how would you re-write this to get the columns of a view?


texts: How to avoid multiple joins on the same table (it has 200 columns to hold IDs. Another table has a mapping of IDs to values)

texts:
I have a table Checklist with the following columns

ChkId, Q1_ID, Comment1, Checked1, Q2_ID, Comment2, Checked2, ...
Q200_ID...

I have a table Questions with the following columns

Q_ID, Prompt

How do I replace Checklist.Q#_ID with the matching Questions.Prompt without joining them up to 200 times? Is there an elegant solution?


texts: Best approach to run a simple query against all users

texts:
So, I could easily do this in SQL Server. I created a cursor, but to be even more simple, I just ran SP_MSFOREACHDB with a simple query:

select top 1 * from Database..Table order by Date desc

I created a global temp table, and populated it with the data of the select.

But, How can I do this on oracle?

I tried with cursors, union, and creating some loops but as a SQL Server DBA, I'm pretty bad with PL-SQL.

So, How can I run a simple select over all users? Cursor? Loops? create all selects and use union ?

Thanks.


texts: Differences between "Unique Key" and "Primary Key"

texts: What are the major differences between Unique Key and Primary Key in MySQL?


texts: Fail-over or switching the Reporting server database

texts:
I have two databases in server, dbs-01.One is a transactional database and a reporting server database.Reporting is minimum,that is why we have it together in the same instance.

I have transactional replication set to replicate the data from server dbs-01 to dbs-02.Currently i am replicating only the transactional databse only.

I want to replicate the reporting server database also which i can implement.
But my questions is :
If i want my application to point to dbs-02,i can do that my changing the connection strings in the application.
But what about the reporting server database.Because we have the Server Name in the Reporting Services Configuration Manager as  shown below(as dbs-01).

If my dbs-01 is down and my application is pointing to dbs-02 , how can i make the reporting services to work from the dbs-02.?

I am not an application developer so i am not aware of any settings which will help to make that switch.
Also configuring Reporting services for dbs-02 from the scratch is not a solution i am looking for.


texts:
I created a table using German (Germany) as the Full-text language, in the settings I tried setting accent sensitivity to both sensitive and insensitive. I, however, get following odd results.

The table contains the following entries:

Müller - Muller - Mueller
Luginbühl - Luginbuhl - Luginbuehl

Executing following search

SELECT * FROM dbo.Search WHERE CONTAINS(CNameCombined, 'FORMSOF (INFLECTIONAL,  "Mueller")')

I get these results: Müller, Muller, Mueller

Executing a similar search

SELECT * FROM dbo.Search WHERE CONTAINS(CNameCombined, 'FORMSOF (INFLECTIONAL,  "Luginbuehl")')

I get zero results.

Does anyone have an idea what the reason for this behavior might be? It seems to work for some words, but not for all of them.

I'm running a SQL Server 2012 on a Windows Server 2012.


texts: Select all entries where range of two DATETIME fields includes given date

texts:
I'm looking for a way to get all entries from my reservations table to be shown in a calendar.

Now I know that I will have to iterate over all days of the current week  and run a database query to select all reservations with dt_from >= start_of_the_week / start_of_the_day and dt_to <= end_of_the_week / end_of_the_day.

But that only gives me the reservations that start or end within the week / respectively within the given day.

What I am trying to figure out is:
How do I get the reservations which started earlier and/or end after the current day or week?

I have some kind of the following table design

+----+-------------------+---------------------+---------------------+
| ID |       NAME        |       DT_FROM       |        DT_TO        |
+----+-------------------+---------------------+---------------------+
|  1 | Jeff Atwood       | 2020-01-06 09:00:00 | 2020-01-16 09:00:00 |
|  2 | Terry Jones       | 2020-01-30 09:00:00 | 2020-02-16 09:00:00 |
|  3 | Brian of Nazareth | 2020-02-06 09:00:00 | 2020-03-16 09:00:00 |
+----+-------------------+---------------------+---------------------+

I have been stuck with this for a while now and would appreciate a push into the right direction.

Thank you :)


texts:
On multiple questions here and on stack overflow i saw people saying in the comments and answers that select * from table is almost always an antipattern without any explaination why. Althrough I can sort of deduce why it is an antipattern. I may be looking over a detail that someone else with better understanding of the issue noticed.

So here is my question, why people say that select * is an antipattern.


texts: Script to estimate row sizes for any table

texts:
I am trying to estimate the space requirements for a central database server that will be collecting data from about 200 identical field databases. I have the average daily row count for each table and now need to estimate the row size, including indexes, for each table.

Is there such an animal in existence or do I need to roll my own? If I do need to roll my own, can you suggest a good approach?

TIA


texts: How to create a role that cannot create or alter a table?

texts:
I want to create a role named manager in postgresql . This role can update, select, delete and insert data in all tables but users that belong to this role cannot create a table nor modify the schema.

I tried the following sql statement:

CREATE ROLE manager
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

How can I achieve this condition?.


texts: Super slow (day long) count(*) after upgrade to 5.6

texts:
After upgrading to Percona 5.6.20 from 5.5.34 I noticed counts on larger tables take a massively long time to execute.

The table is about 27G and 300M rows.  It is not compressed and was innodb before the upgrade as well.

Before a count(*) on that table would take maybe 3 minutes.  Now, it's consistently taking over a day to run.  The last one I actually let spin until it finished took 36 hours.

The DB is on the same hardware as before the upgrade.  Buffer pool size remained the same (20G).

One config change was increasing the number of buffer pools from 1 to 8.

Has anyone ran into this before?  Are there any new 5.6 cnf settings that might be related to this?

Table schema in question

CREATE TABLE `largetable` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`char1` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`int1` int(11) NOT NULL,
`int2` int(11) NOT NULL,
`char2` char(2) NOT NULL,
`mydate` date NOT NULL,
PRIMARY KEY (`id`),
KEY `char1` (`char1`(8))
) ENGINE=InnoDB AUTO_INCREMENT=296639951 DEFAULT CHARSET=latin1;

Here's a pt-config-diff of a "good" 5.5 server and the newer 5.6 server http://pastebin.com/EM22WhBR


texts:
Mysql is not writing events to binlog, I have tried change the binlog configurations, but can't solve.

Conf:

server_id = 2
log-bin = master
binlog_format = row
replicate-do-db = our_db
log-slave-updates = true

'%log%' config:
Seems that log_bin_basename does not exists in 5.5 and log-bin is used to configure the name and automatically turn on the log_bin, but I am still not receiving events in binlog, it just creates the file and write ' master.000919 |   4 | Format_desc |         2 |         107 | Server ver: 5.5.50-0+deb7u2-log, Binlog ver: 4'
    expire_logs_days = 10
    max_binlog_size = 200M
    binlog_do_db = our_db

I also verified that the events are being write to general log

Version: mysql  Ver 14.14 Distrib 5.5.50, for debian-linux-gnu (x86_64) using readline 6.2

The events that I need to be write in binlog are common inserts.

'%log%' config:

Variable_name                            Value
back_log                                 50
binlog_cache_size                        32768
binlog_direct_non_transactional_updates  OFF
binlog_format                            ROW
binlog_stmt_cache_size                   32768
expire_logs_days                         10
general_log                              OFF
general_log_file                         /opt/mysql/SRV01STM085.log
innodb_flush_log_at_trx_commit           1
innodb_locks_unsafe_for_binlog           OFF
innodb_log_buffer_size                   8388608
innodb_log_file_size                     5242880
innodb_log_files_in_group                2
innodb_log_group_home_dir                ./
innodb_mirrored_log_groups               1
log                                      OFF
log_bin                                  ON
log_bin_trust_function_creators          OFF
log_error
log_output                               FILE
log_queries_not_using_indexes            OFF
log_slave_updates                        ON
log_slow_queries                         OFF
log_warnings                             1
max_binlog_cache_size                    18446744073709547520
max_binlog_size                          209715200
max_binlog_stmt_cache_size               18446744073709547520
max_relay_log_size                       0
relay_log
relay_log_index
relay_log_info_file                      relay-log.info
relay_log_purge                          ON
relay_log_recovery                       OFF
relay_log_space_limit                    0
slow_query_log                           OFF
slow_query_log_file                      /opt/mysql/SRV01STM085-slow.log
sql_log_bin                              ON
sql_log_off                              OFF
sync_binlog                              0
sync_relay_log                           0
sync_relay_log_info                      0


texts: Get data from two tables with recursive relationships

texts:
I have a MySQL 5.1 DB, being used behind a PHP application to track permissions and other items that need to be issued to staff that hold various positions within an org. To track what privileges a position has, I'm using this table:

mysql> desc position_privs;
+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| position     | int(11) | NO   |     | NULL    |       |
| priv         | int(11) | NO   |     | NULL    |       |
| date_added   | int(11) | NO   |     | 0       |       |
| date_removed | int(11) | YES  |     | NULL    |       |
+--------------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

position refers to an id column in a positions table, and priv refers to an id column in a privs table. Some positions also inherit privileges from other positions, which may in turn inherit from yet another position, etc. I have this table for that:

mysql> desc position_inherits;
+--------------------+---------+------+-----+---------+-------+
| Field              | Type    | Null | Key | Default | Extra |
+--------------------+---------+------+-----+---------+-------+
| position           | int(11) | NO   |     | NULL    |       |
| inherit_privs_from | int(11) | NO   |     | NULL    |       |
| date_added         | int(11) | NO   |     | 0       |       |
| date_removed       | int(11) | YES  |     | NULL    |       |
+--------------------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

If the inheritances were always only one level deep, I could do this with a simple JOIN, but I'm stumped as to handling positions that inherit from a position, which in turn inherits from another position.

Is there a convenient way of doing this using MySQL, or should I just stick to SELECT * FROM $table and doing it in the application code instead?


texts:
I have a wide table (80 columns) with 70 million rows in it. I can currently trying to set a foreign key across all rows within the table.

Approach one
An update statement over the entire table such as:

BEGIN TRAN
UPDATE Cx
SET Cx.CohortId = Chx.CohortId
FROM Customers Cx
INNER JOIN Cohorts Chx ON Chx.NameField = Cx.NameField
COMMIT TRAN

I was informed this has the potential to fill up the transaction log (please note recovery mode is currently set to simple) and it would be better to batch the updates:

Approach two
Update in batches

DECLARE @Rows INT,
    @BatchSize INT;

SET @BatchSize = 20000;
SET @Rows = @BatchSize;

WHILE (@Rows = @BatchSize)
BEGIN
UPDATE TOP(@BatchSize) Cx
SET Cx.CohortId = Chx.CohortId
FROM Customers Cx
INNER JOIN Cohorts Chx ON Chx.NameField = Cx.NameField
WHERE Cx.CohortId IS NULL -- Do not update records that have already been uplifted to include the FK

SET @Rows = @@ROWCOUNT
END

Does the second approach yield any benefit in terms of the transaction log on large table updates?


texts: Setup Multiple SQL Server Tree Structure similar as RDC based on Enviornments

texts: I want to setup multiple SQL Servers like a tree structure similar to Remote Desktop connection Manager, where we can setup multiple servers based on environments we support like Test and Prod.


texts: Innodb error log sequence number is in future

texts:
2020-08-28 16:46:02 d64 InnoDB: Error: page 1 log sequence number 23113680
InnoDB: is in the future! Current system log sequence number 3181581.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.

I'm using VM and OS is Windows Server 2016 and Uniform Server.
MySQL stops upon this error and doesn't work until it is restarted.
After restarting, it runs for some hours and stops again.
How to overcome this?


texts: Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server

texts:
I'm trying to establish a linked server from SQL Server 2008 R2 to an Access database. The Access database is not password protected. As I have seen in other posts (specifically this one), this is usually due to the current user not having access to the Temp folder inside the NetworkService folder. Since we are running this in Windows Server 2008, the directory structure is quite different than what most are alluding to. I have allowed access to every temp folder in the "root:\Documents and Settings\" directory, and to no avail.

This is the procedure I am using to add the linked server:

EXEC sp_addlinkedserver
@server = N'OS_Access',
@provider = N'Microsoft.ACE.OLEDB.12.0',
@srvproduct = N'',
@datasrc = N'C:\RTBData\Data\OS.mdb';
GO

It creates the linked server with no problem, but I am not able to view the tables/views of the database. Likewise, my ASP.NET application cannot access it either.

I have tried both ACE and JET(64-bit)(by installing the Data Connectivity Components for Office), and they both do not work. I have also tried configuring the ACE provider with "Dynamic Parameter" and "Allow InProcess" to true.

Additionally, I tried upping the memory usage by the MSSQLSERVER services by adding "-g512;" in front of "startup parameters" string in SQL configuration manager to rule out memory issues.

If anyone could shed some light on this that would be fantastic! Thanks!


texts:
In my database I have two tables: projects and comp_types. When a new project is created a trigger is hit which inserts a concatenated value in a column in the projects table.

A fully working SQL Fiddle.
Please run this and you will see, it works.

However when I try to run it in SQL Server I receive the following error:

  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Screenshot: http://i.stack.imgur.com/38rKV.jpg

All tables in the SQL Server are fresh, with no data (just like the fiddle).
What am I doing wrong?