I can't open my Pharmacy management software"based on sybase database" I am stuck with my pharmacy management software running on Windows XP with a dongle as a security system. The database is Sybase.

I tried many backup copies but failed to open the program. Two windows appear:
I am trying to find an online opener for DB files or any program that can do the job.I downloaded RazorSQL but couldn't deal with it as I am not advanced user, just newbie.How can I fix this issue and open the program or at least retrieve or extract data from [the backup][3] into .txt or .csv format?

I am the pharmacy owner, the management software is licenced, and the database is mine. There is no violation of privacy or intellectual property rights of any person. Nothing confidential to be hidden. I am simply sad that the system crashed, and for some reason I have to wait 3 days until I get technical support to get this issue fixed.

You can use FreeTDS or anything that links in with it like DBD::Sybase to connect to the database.


Mysql to Oracle communication I have a website running with php and mysql on Centos(Amazon instance). The newly brought ERP(to be integrated with existing system) uses Oracle as db(located on a separate Windows Server).
The orders from the website are inserted into the Master Mysql database and which are replicated into the Slave Mysql database. These orders need to be pushed to the Oracle Db. I have arrived on 4 methods to do this.

Use mysql UDF for http communication that would send the rows on a Insert Trigger on the slave to the Oracle webservices on Oracle server

Use cron jobs(with a low interval may be 5 mins) with Php script that would get the new orders from mysql and send to the Oracle db via Oracle services/Php services on Oracle hosted server.

Use sys_exec() udf to invoke php script that inserts into Oracle db

Use memcached with MySql and let Php poll the memcached to retrieve data and send it to Oracle server, but unsure whether we could migrate existing Mysql version to new version Mysql 5.6

I already have the UDF's in place and tested them, they are good to go. but still in dilemma regarding data integrity and reliability in case of using UDF's with triggers.
Is there a better method for doing this. Or else which method shall I follow to do the same.
I am aware of the security threats of UDF's, you can't restrict it to any user
One more thing I am not allowed to introduce new changes to the existing website php code for the operation.

Try using heterogenous Connectivity to link the Oracle database to the mySQL database.
It would be easier to directly query for what you want from Oracle, rather than trying to replicate the data to Oracle. I tried doing this on Redhat 5.6, but I had issues with the ODBC driver that was written for Redhat 5.6. I later tries it with Redhat 6.3 and it worked much better. The issue dealt with how the ODBC driver read unicode characters. It is pretty easy to setup, you can install HAS as a different Oracle Home, or use the same Oracle home. Linux has a built in ODBC test program that will help you to get it working.

http://docs.oracle.com/cd/B28359_01/server.111/b28277/toc.htm


Oracle XE 18c connects with sqlplus, but not DB GUI apps I installed Oracle XE 18c on RHEL on my laptop, got it configured, created connections with two applications (DBeaver and DbVisualizer) as well as able to connect with sqlplus. It all worked "perfectly".

This morning, I had to "IT Crowd" reboot my laptop (turned it off and back on again), then started Oracle XE using sqlplus, accessed a table to confirm the engine actually came up although the appropriate startup messages were generated.

My problem is neither of the DB apps (above) can connect to the XE database again. I've checked the listener.ora file, it matches the connection configuration in both applications for the hostname and port number. The /etc/hosts file still contains the same configuration.

Note that the server is configured in listener.ora to listen on port 1539.

The message I get from both apps when I "ping the server" is effectively the same: "

Cannot connect to '<redacted>' on port 1539

along with the standard "please check and try again" advice. I can't find any log files with entries for today. It sounds trite, but none of the configuration files changed, the only difference I can see is having rebooted and possibly something did not "stick", perhaps a process did not get started.

Please accept my thanks in advance.

The listener is not running.

As the Oracle user (which may be called oracle or oraclexe) run:

lsnrctl start

... to start the Oracle listener.


Network line switch-over in Oracle Data Guard We have a Primary database with a remote Standby which established using Oracle Data Guard. They can be syncing over a private Wireless Network and a Wired LAN (There are two options as network medium)

Is there any setting in Data Guard to handle network line failure? For example, I want to set wired LAN as main line and when it is not available data guard automatically switch to wireless network.

Sure, you can configure alternate locations.

You may have this now on the primary database:

log_archive_dest_1='location=use_db_recovery_file_dest' -- FRA for local archivelogs
log_archive_dest_2='service=standby_TNS_wired_network ...'

And you can configure this:

log_archive_dest_1='location=use_db_recovery_file_dest' -- FRA for local archivelogs
log_archive_dest_2='service=standby_TNS_wired_network ... alternate=log_archive_dest_3'
log_archive_dest_3='service=standby_TNS_wireless_network ... alternate=log_archive_dest_2'
log_archive_dest_state_2=enable
log_archive_dest_state_3=alternate

Example 17-3 Defining an Alternate Oracle Net Service Name to the Same Standby Database

LOG_ARCHIVE_DEST_2='SERVICE=stby1_path1 MAX_FAILURE=1 ALTERNATE=LOG_ARCHIVE_DEST_3 DB_UNIQUE_NAME=stdby1'
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ALTERNATE
LOG_ARCHIVE_DEST_3='SERVICE=stby1_path2 DB_UNIQUE_NAME=stdby1'


Revoke and Grant Privileges on SQL Server Database I have a user that I want to grant all privileges on all database tables except for DROP, TRUNCATE, DELETE, INSERT and UPDATE.
The Database has over 300 tables and I can't do this one by one. I need something like:
REVOKE DROP, TRUNCATE, DELETE, UPDATE, INSERT ON DATABASENAME.* FROM USER1;

But its throwing an error when I put the DATABASE.* with * representing all tables.

REVOKE means is taking the privileges back. If you didn't give any permission to any user, you can not take a back.
You can use DENY, but not on the DROP and TRUNCATE. There is not a DROP and TRUNCATE permission just like you wrote.
DENY DELETE, UPDATE, INSERT ON DATABASE::[databasename] TO user1;

But, you should think about the privileges which are you want to grant and check out the database roles.


Sql logshipping Last_restored,last_copied file null in sysjobhistory I have started log shipping for few dbs from production to DR recently, but it seems like the jobs just keep running and sometimes the copy job copies few files, sometimes it says the files already exists, the restore job sometimes says skipping backup files and sometimes restores the files , i feel it has something to do with "last_restored_file from msdb.dbo.log_shipping_monitor_secondary " being NULL, i am not sure why its not being populated by the monitor server.

Any ideas?

Found the answer finally, The logshipping linked server between monitor and Secondary server was not working, Opened up rules on firewall to let them communicate. Started working


is there a way (clean or unclean) to eliminate a filestream data file on a production SQL Server database? Is there a way (clean or unclean) to eliminate a filestream data file on a production database?

I forced a garbage collection on an empty filestream :

sp_filestream_force_garbage_collection @dbname = N'DB' ,   @filename = N'db_mod';

I tried this and memory opt and filestream is't actually enabled on db

SELECT name, description FROM sys.dm_os_loaded_modules WHERE description = 'XTP Native DLL'

this script gives the files in db:

select sdf.name AS [FileName],
   size/128 AS [Size_in_MB],
   fg.name AS [File_Group_Name]
   FROM sys.database_files sdf
   INNER JOIN
   sys.filegroups fg
   ON sdf.data_space_id=fg.data_space_id

FileName Size_in_MB File_Group_Name

db           7282        PRIMARY
   db_mod       1528        db_mod

Can I manage to eliminate this empty file and filegroup that prevents me from using db mirroring?

of course I tried

-- Remove the File

ALTER DATABASE db REMOVE FILE db_mod1 ;
-- Remove the Filegroup
ALTER DATABASE db REMOVE FILEGROUP db_mod ;

I get the error:

  Msg 41802, Level 16, State 1, Line 1
  Cannot drop the last memory-optimized container 'db_mod1'.

To be clear, the FILESTREAM and In-Memory OLTP features are completely separate, and not compatible. Even though In-Memory OLTP uses some components of FILESTREAM, the FILESTREAM feature does not have to be enabled to use In-Memory OLTP, and memory-optimized tables are not able to be used with the FILESTREAM feature.

According to the error you received, you're trying to remove the memory-optimized filegroup, which is not possible.

What does the following query return?

 SELECT *
FROM [InMemTest2].sys.database_files
INNER JOIN [InMemTest2].sys.filegroups ON database_files.data_space_id = filegroups.data_space_id
WHERE filegroups.type = 'FX'


Parent/child relationship or not if the name of the field implies the relationship? I am working with the harmonized system and try to catalog this in a MySQL database. This works for example like this:

01  LIVE ANIMALS
0101  horses, asses, mules and hinnies, live
0102  bovine animals, live
0103  swine, live
010310  Purebred breeding animals
010391  Weighing less than 50 (11023 lb) each
010392  Weighing 50 (11023 lb) or more each
0104  sheep and goats, live
0105  chickens, ducks, geese, turkeys, and guineas, live
0106  animals, live, nesoi Nesoi - not elsewhere specified of indicated.

As you can see, there is a clear parent-child relationship. This relationship can always be traced back by the numbering that is used. From this thread I learned that I best use a text data field for the codes (01, 0101, etc). My question is: should I somewhere log the parent-child relationship, or would this not be necessary? After all, I can do a:

SELECT * FROM accounts WHERE code LIKE '0103%'

And get all the subaccounts just fine. And I feel that a parent-child table could make things more complicated. But I'd like to hear what other people feel make sense.

What you describe is a variant of "Materialized Path", see for example https://communities.bmc.com/docs/DOC-9902 for an example. I say variant because you have no separator token, but are instead using a fixed number of positions for each different level.

In Materialized Path you usually don't have an explicit parent/child relationship represented, instead it is encoded into the "path" string. To determine sub-categories, use like as in your example. For ancestor-categories you need some procedural part that splits the path-string. Direct parent is the first ancestor. Direct children can be located by using a combination of like and length() function. Root and leaf predicates are easy to define as well.

As a starting point, why would you like to add a parent-child relationship (if it is not for the operations described above)? If the answer is referential integrity it will be a bit backward since you already have the domain at hand (your existing table). Adding a separate relation (table) to represent the parent/child relationship would be something like:

create table ...
( category varchar(...) not null
,     constraint ...
          foreign key ... references ...
, parent_category varchar(...) not null
,     constraint ...
          foreign key ... references ...
)

You could have a trigger populate this table after insert of a new "category"
But it does not really protect you from anything, it would act more like a report of your encoded parent child relation.

The other option is to add a nullable attribute to your existing table, your "top-categories" would have null as a parent, and the others would have substr(code, 1, length(code)-2) as a parent. This would be some kind of mix between "adjacency list" and "materialized path". You would still have to protect against anomalies like ('01020304', NULL) and ('010203','0202'). It is possible to add triggers that validates this.

If you want to keep your current table as is it is probably easiest to add validation code, either in triggers or in your application that makes sure that there exists a "super-category".

If you are willing to split the code in parts you could have a look at "Nested Sets". It is described in the link I gave above, or as an additional transitive closure relation, see for example http://karwin.blogspot.se/2009/04/sql-antipatterns-strike-back-slides.html. A slight variation of the same method is described at:  http://dustbite.se/tree/ . Both "Nested Set" and "Transitive Closure" will add a significant overhead to what is probably the most common operation that you do

Personally I would stick with what you have and add validation triggers that checks that the codes are consistent during modifications of the "tree"

As Oracle XE is mentioned as an alternative DBMS in @Vérace post, I just point out that other commersial vendors is offering free versions as well. See Microsoft SQL Server 2014 Express (http://msdn.microsoft.com/en-us/evalcenter/dn434042.aspx)  and DB2 Express-C ( http://www-01.ibm.com/software/data/db2/express-c/download.html). DB2 does not have a space restriction like Oracle, I'm not familiar with the restrictions of SQL-server.


Accumulo table design methodology I am just getting started with Accumulo and NoSQL databases and I am looking for some discussion on table design.  I get the key value structure that is seen in the manual.  However, if I am trying to recreate a relational database, I am not sure how relationships work.  Can someone explain to some degree how to setup and "Hello World" database (i.e., manager-employee database).  I want to use key-value implementation.

The first thing you should realize is that Accumulo is not a relational database. If you want to make it work like one, you need to create that functionality on top.

One way to do this would be to create rows that look like rows in a relational table, complete with foreign keys that you link together in your application's (an Accumulo client) code. Your client code must enforce the cascading updates/deletes and the other maintenance aspects of relations you may have come to expect from a relational database. Accumulo just stores and sorts Key/Value pairs for you. You decide the semantics of these Key/Value pairs.

Examples:

Note: For illustration purposes, Accumulo Key/Value pairs will be represented as:

RowID:ColumnFamily:ColumnQualifier -> Value

Employee table:

EmployeeID1:Data:Name -> Chuck
EmployeeID1:Data:Position -> Owner

EmployeeID2:Data:Name -> Barbara
EmployeeID2:Data:Position -> StoreManager
EmployeeID2:Data:Manager -> ManagerID1

EmployeeID3:Data:Name -> Tom
EmployeeID3:Data:Position -> SalesManager
EmployeeID3:Data:Manager -> ManagerID2

EmployeeID4:Data:Name -> Sally
EmployeeID4:Data:Manager -> ManagerID3

EmployeeID5:Data:Name -> Rick
EmployeeID5:Data:Manager -> ManagerID3

Manager table:

ManagerID1:Info:EmployeeID -> EmployeeID1
ManagerID1:Employees:1 -> EmployeeID2

ManagerID2:Info:EmployeeID -> EmployeeID2
ManagerID2:Employees:1 -> EmployeeID3

ManagerID3:Info:EmployeeID -> EmployeeID3
ManagerID3:Employees:1 -> EmployeeID4
ManagerID3:Employees:2 -> EmployeeID5

See how I've established foreign keys in both directions? Scanning the Manager table, you can quickly see which employees are managed by whom, but you'll have to look up each EmployeeID in the Employee table to see their information. Scanning the Employee table, you can quickly see information about a particular employee, including the ID of their manager, but you'll have to look up the manager's EmployeeID in the Manager table, and then look that up in the Employee table if you want the name of their manager.

This could be drastically simplified by combining the two tables into a single table and avoiding a separate ManagerID by using EmployeeID for both purposes. Something like:

One Employee table:

...
EmployeeID3:Data:Name -> Tom
EmployeeID3:Data:Position -> SalesManager
EmployeeID3:IsManagedBy:EmployeeID2 -> Since 2001
EmployeeID3:Manages:EmployeeID4 -> Since 2003
EmployeeID3:Manages:EmployeeID5 -> Since 2001
...

However, depending on your application, you may find that in a NoSQL database, you are better off flattening and duplicating your data, rather than normalizing with relations.

Flattened Employee table:

...
Tom:Data:Position -> SalesManager
Tom:IsManagedBy:Barbara -> Since 2001
Tom:Manages:Rick -> Since 2003
Tom:Manages:Sally -> Since 2001
...


`ts_rank` is returning a numeric/decimal/real that fails a "greater than" check The following yields (seemingly exactly) 0.1:
select ts_rank_cd(to_tsvector('cat'), to_tsquery('cat'));

Yet the following yields t:
select ts_rank_cd(to_tsvector('cat'), to_tsquery('cat')) > 0.1;

I would expect it to yield t if the operation was >=, not > How can I get the "real" value of the ts_rank_cd so that it returns f?

As background, I'm writing a full text search query that uses keyset pagination with ts_rank, so being able to order results based on a concrete, precise value of ts_rank is important. With keyset pagination, the client provides the last result from the current page as a parameter and asks for the next page. Currently, when my client passes back 0.1, the above > behavior causes erroneous results

ts_rank_cd yields a real. If you cast this to double precision, the result becomes:
SET extra_float_digits = 3;

SELECT ts_rank_cd(to_tsvector('cat'), to_tsquery('cat'))::double precision;

     ts_rank_cd
---------------------
 0.10000000149011612
(1 row)

That explains what you observe, because the real is cast to double precision if you compare it to a double precision number.
The solution is to explicitly cast 0.1 to real, so that real comparison is used:
SELECT ts_rank_cd(to_tsvector('cat'), to_tsquery('cat')) = 0.1::real;

 ?column?
----------
 t
(1 row)


Foreign key on PostgreSQL range bounds Is it possible in PostgreSQL 9.6 to apply a foreign key constraint on the lower and upper bounds of a range (of integers in my case)?

Currently I keep the lower bound and upper bound in two columns, but would like to unify them into a range column while keeping the foreign key references.

Example

I have a table containing a list of sessions, something like:

CREATE TABLE sessions (
    session_id    SERIAL      PRIMARY KEY,
    session_start TIMESTAMPTZ NOT NULL,
    description   TEXT
);

and then a table of groups of those sessions which is currently expressed as:

CREATE TABLE session_groups (
    group_id      SERIAL  PRIMARY KEY,
    first_session INTEGER NOT NULL UNIQUE,
    last_session  INTEGER NOT NULL UNIQUE,
    description   TEXT,

    FOREIGN KEY (first_session)
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT,
    FOREIGN KEY (last_session)
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT,

    CONSTRAINT last_session_after_first
         CHECK (last_session >= first_session)
);

What I would like:

CREATE TABLE session_groups (
    group_id      SERIAL    PRIMARY KEY,
    session_range INT4RANGE NOT NULL UNIQUE,
    description   TEXT,

    FOREIGN KEY (lower(session_range))
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT,
    FOREIGN KEY (upper(session_range))
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT

    -- Dropped last_session_after_first constraint since
    -- the INT4RANGE type checks it automatically.
);

which is throwing a syntax error.

It is not allowed to have arbitrary expressions in FOREIGN KEY constraints, only columns. That's why you get an error when you try the 2nd approach.

You can however, use a VIEW to access the table:

CREATE VIEW v_session_groups
    (group_id, session_range, description) AS
SELECT
    group_id,
    int4range(first_session, last_session, '[)'),
    description
FROM
    session_groups ;

You could even add triggers, and then be able to INSERT/UPDATE/DELETE from the view, as if it was a normal table.

Side notes:

The first design has 2 UNIQUE constraints, on (first_session) and (last_session), while the 2nd has 1 UNIQUE constraint, on (first_session, last_session). These are not equivalent designs, so re-examine your requirements.
I used the default '[)' (inclusive-exclusive) parameter for the int4range column. You may want to change that to '[]' (all-inclusive), depending on how you want to be saving ranges.
Examples:int4range(1,1) and int4range(1,1,'[)') is the empty range while int4range(1,1,'[]') is the [1,2) range.
If you want to enforce / avoid overlapping ranges, then you don't need a UNIQUE constraint at all but an EXCLUDE constraint:

CREATE TABLE session_groups (
    group_id      SERIAL  PRIMARY KEY,
    first_session INTEGER NOT NULL,
    last_session  INTEGER NOT NULL,
    description   TEXT,

    FOREIGN KEY (first_session)
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT,
    FOREIGN KEY (last_session)
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT,

    CONSTRAINT non_overlapping_sessions
        EXCLUDE  USING gist
        ( int4range(first_session, last_session, '[)') WITH && )
);

See also the documentation on constraints on range types. You probably need to add the extension as well:

CREATE EXTENSION btree_gist;


Sort Alphanumeric Values with dash sign I have defined a table named Inventory that consists of one varchar(45) field named ItemNameCode.  Here is the table and contains the following alphanumeric values.

Which we want to sort alphanumerically as:

MES40
PT14
PT14-403
PT14-646-649
PT14-646-649
PT15-3-11
PT15-12-42
PT15-12-572-575
PT16-164
PT16-227
PT16-62-71
PT16-136
PT16-137

I tried the bellow query but not getting exact order:

SELECT ItemNameCode
FROM Inventory
ORDER BY LEFT(ItemNameCode,PATINDEX('%[0-9]%',ItemNameCode)-1)

In this case, simply sorting by ItemNameCode doesn't give you the desired sort. Using ORDER BY ItemNameCode would sort the PT15 values like this:

PT15-12-42
PT15-12-572-575
PT15-3-11

But, you expect them sorted like this:

PT15-3-11
PT15-12-42
PT15-12-572-575

Based on your expected results, I would describe the desired sort behavior as "The string is made up of segments separated by dashes. The first segment is alphanumeric, and each subsequent segment is numeric. Sorting should be done by the first segment, then by the second segment, then by the third segment, etc"

To accomplish this, you'll need to essentially break the ItemNameCode into separate calculated columns, and cast the numeric segments to integer before sorting.

This method of splitting the string apart into separate columns is pretty ugly, but works:

SELECT ItemNameCode, c1.seg1, c2.seg2, c3.seg3, c4.seg4
FROM dbo.AlphaNumeric x
OUTER APPLY (SELECT CASE WHEN CHARINDEX('-',x.ItemNameCode,1) = 0 THEN x.ItemNameCode
                        ELSE SUBSTRING(x.ItemNameCode,1,CHARINDEX('-',x.ItemNameCode,1)-1)
                    END AS seg1,
                    CHARINDEX('-',x.ItemNameCode,1)+1 AS pos1) c1
OUTER APPLY (SELECT CASE WHEN c1.pos1 = 1 THEN NULL
                        WHEN CHARINDEX('-',x.ItemNameCode,c1.pos1) = 0 THEN SUBSTRING(x.ItemNameCode,c1.pos1,8000)
                        ELSE SUBSTRING(x.ItemNameCode,c1.pos1,CHARINDEX('-',x.ItemNameCode,c1.pos1)-(c1.pos1))
                    END AS seg2,
                    CASE WHEN c1.pos1 = 1 THEN 1
                        ELSE CHARINDEX('-',x.ItemNameCode,c1.pos1)+1
                    END AS pos2) c2
OUTER APPLY (SELECT CASE WHEN c2.pos2 = 1 THEN NULL
                        WHEN CHARINDEX('-',x.ItemNameCode,c2.pos2) = 0 THEN SUBSTRING(x.ItemNameCode,c2.pos2,8000)
                        ELSE SUBSTRING(x.ItemNameCode,c2.pos2,CHARINDEX('-',x.ItemNameCode,c2.pos2)-(c2.pos2))
                    END AS seg3,
                    CASE WHEN c2.pos2 = 1 THEN 1
                        ELSE CHARINDEX('-',x.ItemNameCode,c2.pos2)+1
                    END AS pos3) c3
OUTER APPLY (SELECT CASE WHEN c3.pos3 = 1 THEN NULL
                        WHEN CHARINDEX('-',x.ItemNameCode,c3.pos3) = 0 THEN SUBSTRING(x.ItemNameCode,c3.pos3,8000)
                        ELSE SUBSTRING(x.ItemNameCode,c3.pos3,CHARINDEX('-',x.ItemNameCode,c3.pos3)-(c3.pos3))
                    END AS seg4,
                    CASE WHEN c3.pos3 = 1 THEN 1
                        ELSE CHARINDEX('-',x.ItemNameCode,c3.pos3)+1
                    END AS pos4) c4
ORDER BY c1.seg1, CAST(c2.seg2 AS INT), CAST(c3.seg3 AS INT), CAST(c4.seg4 AS INT)

If the 2nd, 3rd, 4th, etc values are not strictly numeric, and you also needed alphanumeric sorting, you could try using this alternate ORDER BY clause on the same query:

ORDER BY c1.seg1,
        RIGHT('00000' + COALESCE(c2.seg2,''),5),
        RIGHT('00000' + COALESCE(c3.seg3,''),5),
        RIGHT('00000' + COALESCE(c4.seg4,''),5)


If PK is Non-Clustered index, how to identify clustered index candidate? I'm designing a database for insurance company. I want to make Primary key as GUID-uniqueidentifier. I want to create Index for each table in database for performance optimization.

I did research and i came to know that GUID primary key should be Non Clustered always. I agreed that clustered index on GUID will create fragmentation in memory.

I have sample tables,

CREATE TABLE [dbo].[ProductState](

    [ProductStateId] [uniqueidentifier] NOT NULL,
    [ProductId] [uniqueidentifier] NOT NULL,  -- Foreign key
    [StateId] [uniqueidentifier] NOT NULL, -- Foreign key
    [CreatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ProductState] PRIMARY KEY NONCLUSTERED
(
    [ProductStateId] ASC
)

This is a mapping table between two master table in which only foreign key columns are available and which are again GUIDs.

Another sample table,

CREATE TABLE [dbo].ProductCompare(
    [ProductCompareId] [uniqueidentifier] NOT NULL,
    [ProductRateId] [uniqueidentifier] NOT NULL,          -- Foreign key
    [ProductId] [uniqueidentifier] NOT NULL,              -- Foreign key
    [Program] [int] NOT NULL,
    [MarkUpValue] [decimal](19, 10) NULL,
    [IsSelected] [bit] NOT NULL,
    [ComparisonValue] [varchar](max) NULL,
    [ComparisonOrder] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ProductCompare] PRIMARY KEY NONCLUSTERED
(
    [ProductCompareId] ASC
)

This is child table which has reference of master table along with another column, but i don't see any column here which can work as Clustered Index key.

In my entire database there are many scenarios of this kind where i don't have any column which can work as Clustered Index key.

I need suggestion how should i manage Indexing for database of insurance company web application.

that GUID primary key should be Non Clustered always

This is not necessarily true. Indexing over effectively random data, which most GUID/UUID values are, can lead to excess page splitting and fragmentation which can be a concern for rapidly growing data. Depending on how your data grows, simple index maintenance run regularly (but not too often) my alleviate all of that practically, or it may not be a significant problem for your data at all anyway.

If you are generating the GUIDs in the database using a default constraint, and you aren't using UUIDs instead of smaller values (such as integers) for your keys for privacy reasons, you could instead use NEWSEQUENTIALID() instead of NEWID() in your defaults to reduce the amount of randomness such that it is insignificant.

If you are generating your GUIDs in your application, then to get the same effect you could use UuidCreateSequential or equivalent (many UUID related libraries wrap this function, it is the function that NEWSEQUENTIALID() itself is based upon. It is possible, though requires an inefficient little bit or hackery, to use NEWSEQUENTIALID() in a stored procedure or trigger too (though you can't use it in functions).


Moving SQL Server to different domain What are best practices/important steps when the Windows Server machine hosting SQL Server 2008 R2 is moved to another domain?   What steps definitely/might need to be done for SQL Server before and after the new domain is joined?

What do you need to take into account when migrating SQL Server to another domain?
The steps below presume:

IP address will also change
SQL Server is NOT clustered

Steps:
A.  Backup:

BEFORE: backup the databases off-machine

B.  Services:

BEFORE: depending on the nature of the change/move, you may want to set service start to Manual for all SQL Service
AFTER: Once things or up and running properly, return service start to its original setting

C.  SA account:

BEFORE: If all administrator accounts are domain accounts or groups, temporarily enable the 'sa' account with a strong password
AFTER moving: once the domain-based accounts are setup in the new domain, 'sa' can be disabled again

D.  Service Windows account:

BEFORE moving: for each SQL-Server-related Windows service, change the service to use a LOCAL windows account or one of the built-in accounts
AFTER moving: grant the necessary privileges to the service new domain accounts.  When special permissions are not needed, the SQL Service Configuration Manager can be used to change the service account

E.  Windows domain accounts used to login to SQL Server

Re-create the needed accounts or use corresponding accounts in the new domain.
BEFORE moving, script out permissions for OLD domain accounts, including SQL permissions, Windows folder and file permissions, Windows permissions, etc.
AFTER moving, apply these scripts to the corresponding NEW domain accounts so they will have the same permissions

F.  IP Address: SQL Server (unless clustered) will use the new IP address

AFTER: Client applications that reference the service by IP address will need to be configured with the new IP address.

G.  Firewall:

AFTER: OLD firewall openings that are no longer used will need to be closed, NEW firewall openings may need to be created for SQL Server, OLAP services, SSRS between servers and clients

H.  DNS entries:

AFTER: verify DNS has correctly updated
AFTER: Clients and services that reference by DNS name, may need to be restarted AND/OR their host systems may need their DNS cache flushed.  For windows workstations, this can be done with "ipconfig /flushdns"

I.  Service Principle Names (SPNs).  Some standalone (and all clustered) instances use SPNs.

AFTER: The OLD SPN must be dropped and a NEW SPN must be created.  Although it's not recommended to use a SQL Server service account to manage (its own) SPNs, if this is the case, the NEW domain service account will need to be granted WriteServicePrincipalName" privilege

J.  Client Network Utility Alias.

AFTER: Update any clients that use these will need to updated

K.  Client application and service connection configuration:

AFTER: Data Source Names (DSNs), connection strings, config files, Oracle TNS names for connections - will need to be udpated and applications and services may need to be restarted

L.  Internal machine name.

AFTER: If the machine name is also changing, SQL Server's internal machine name entry may need to be udpated
sp_dropserver 'MyOldMachineName'
go
sp_addserver 'MyNewMachineName','local'
go

M.  Merge Replication - If merge replication is in use, it will also need to be reconfigured.

BEFORE: ensure all replicas are up-to-date
AFTER: re-configure merge replation

Attributions - some information added from these sources:
https://serverfault.com/questions/49681/change-ad-domain-membership-of-a-server-2008-running-ms-sql-08
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/f3e8ff83-8fcd-4335-87fe-ea5641ae6b88


How to get SQL of single time zone Trying to get the SQL for a single time zone results in an error message:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo America/Sao_Paulo
mysql_tzinfo_to_sql: Error reading file '/usr/share/zoneinfo' (Errcode: 21)
Problems with zoneinfo file 'America/Sao_Paulo'

What am I doing wrong? Wrong syntax?

Getting the SQL for all time zones works, except for some warnings, and São Paulo is included:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | grep Sao_Paulo
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('America/Sao_Paulo', @time_zone_id);
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('posix/America/Sao_Paulo', @time_zone_id);
Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh89' as time zone. Skipping it.
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('right/America/Sao_Paulo', @time_zone_id);
Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.

Try this:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo/America/Sao_Paulo America/Sao_Paulo

Here a sample output:

INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
SET @time_zone_id= LAST_INSERT_ID();
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('America/Sao_Paulo', @time_zone_id);
INSERT INTO time_zone_transition (Time_zone_id, Transition_time, Transition_type_id) VALUES
 (@time_zone_id, -1767214412, 2)
,(@time_zone_id, -1206957600, 1)
,(@time_zone_id, -1191362400, 2)
,(@time_zone_id, -1175374800, 1)
,(@time_zone_id, -1159826400, 2)
,(@time_zone_id, -633819600, 1)
,(@time_zone_id, -622069200, 2)
,(@time_zone_id, -602283600, 1)
,(@time_zone_id, -591832800, 2)
,(@time_zone_id, -570747600, 1)
,(@time_zone_id, -560210400, 2)
,(@time_zone_id, -539125200, 1)
...
;
INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
 (@time_zone_id, 0, -11188, 0, 'LMT')
,(@time_zone_id, 1, -7200, 1, 'BRST')
,(@time_zone_id, 2, -10800, 0, 'BRT')
;

From MySQL documentation

  mysql_tzinfo_to_sql tz_file tz_name

  This syntax causes mysql_tzinfo_to_sql to load a single time zone file
  tz_file that corresponds to a time zone name tz_name


Index usage stats DMV indicates no index activity I'm trying to obtain information on indexes.  According to several websites, the below TSQL query should join information on indexes to the indexes table, returning indexes and related information.  The below query is simplified to the bare join, as I'm currently trying to return data and then from there, I will return what I need:

SELECT *
FROM sys.indexes i
     INNER JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id

This query returns nothing.  Yet, in several databases there are hundreds of tables that have at least one index, and according to various articles online, this query should return those indexes (because it's joining on the object ids).  Also, if I try to change the query to the below query, it returns nothing as well:

SELECT *
FROM DATABASENAME.sys.indexes i
    INNER JOIN DATABASENAME.sys.dm_db_index_usage_stats s ON i.object_id = s.object_id

Why isn't this query returning data?

Added

If I run the below queries, I receive data from both of these SELECT queries:

SELECT *
FROM sys.indexes

SELECT *
FROM sys.dm_db_index_usage_stats

The problem (on the original query) is the object_id join.  However, I don't know why I have objects in one table that don't exist in another.

Final: Martin had the right answer originally - even though data was being returned from the sys.dm_db_index_usage_stats table, it was on the msdb database, not the specific database I was seeking.  I ran queries on tables again, and found the database id in the table.  Unsure of why the sys.dm_db_index_usage_stats table didn't retain data from previous queries on that database.

One possibility would be that you don't have sufficient permissions to view the metadata.

Another is that sys.dm_db_index_usage_stats only reports on indexes that have been used at least once since the server was last restarted. Also

  whenever a database is detached or is shut down (for example, because
  AUTO_CLOSE is set to ON), all rows associated with the database are
  removed.

AUTO_CLOSE is the default for SQL Server Express.

You need to filter sys.dm_db_index_usage_stats by database_id to determine if it has metadata loaded for a particular database also the join should include index_id

SELECT *
FROM   sys.indexes i
       LEFT JOIN sys.dm_db_index_usage_stats s
         ON i.object_id = s.object_id
            AND i.index_id = s.index_id
            AND s.database_id = DB_ID()


Duplicated id error, key is actually NOT duplicated I have the following query:

WITH data AS (
    SELECT
        profileid as id_user,
        cast(terms_accepted as boolean) as terms_accepted_passport,
        lastname as last_name_user,
        firstname as first_name_user,
        picture_serving_url as picture_user,
        is_active as status_user,
        is_passport_active as status_passport,
        language as language_id_user,
        to_timestamp(created, 'YYYY-MM-DD HH24:MI:SS') as created_user,
        to_timestamp(modified, 'YYYY-MM-DD HH24:MI:SS') as modified_user,
        passport_completion_level as completion_level_passport,
        email as email_user,
        about_me as description_user,
        uni_code as institution_id_user,
        to_json(metadata) as metadata_misc
    FROM import_temp_table
)
INSERT INTO passport (
    id,
    public,
    status,
    completion_level,
    user_id,
    terms_accepted
)
SELECT
    (SELECT MAX(id) + 1 FROM passport),
    TRUE,
    CASE status_passport
        WHEN LOWER('true') THEN 1
        WHEN LOWER('false') THEN 2
    END AS status_passport,
    completion_level_passport,
    id_user,
    terms_accepted_passport
FROM data

So I was trying this query and it throws the following error:

ERROR:  duplicate key value violates unique constraint "passport_pkey"
DETAIL:  Key (id)=(108) already exists.

Problem is, id 108 DOESN'T exist. When you perform a single insert with the hardcoded id as 108, it performs it, however, when you try and loop it like this here, it doesn't. Any idea how can I accomplish this?

EDIT

Table definition for Passport is here:

CREATE TABLE public.passport
(
    id integer NOT NULL DEFAULT nextval('passport_id_seq'::regclass),
    public boolean,
    status integer,
    completion_level character varying COLLATE pg_catalog."default",
    user_id character varying COLLATE pg_catalog."default",
    application_id integer,
    terms_accepted boolean,
    CONSTRAINT passport_pkey PRIMARY KEY (id),
    CONSTRAINT passport_application_id_fkey FOREIGN KEY (application_id)
        REFERENCES public.application (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT passport_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES public."user" (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

You're trying to insert the same id twice, because MAX(id) doesn't get incremented until commit. What you want to do is precisely what the DEFAULT does on passport

id integer NOT NULL DEFAULT nextval('passport_id_seq'::regclass),

Hint: nextval(seq)

It'll look like this,

INSERT INTO passport (
    id,
    public,
)
SELECT
    nextval('passport_id_seq'::regclass)
    TRUE,
    ...

But even better because id has a DEFAULT just remove it entirely..

INSERT INTO passport (
    public,
)
SELECT
    TRUE,
    ...


How do I join information schema to the table's data? I need to join a table using it's column name from the INFORMATION SCHEMA. Is this possible?

I actually found another way to work around it. I am using Innodb. For the structure it's kind of complicated, but this is the query I was working with :

SELECT c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
(
    SELECT *
    FROM
    (
        SELECT
            ct.field_name,
            ct.length,
            ct.description,
            IFNULL( mfc.hide, ct.hide) as hide,
            IFNULL(mfc.required, ct.required) as required,
            ct.example
        FROM xxxxxx.config_marketplace ct
        LEFT JOIN xxxxx.merchant_field_config mfc
            ON mfc.field_id = ct.id
            AND mfc.industry = 'MP'
            AND mfc.merchant_id = '1512MP5'
    ) as t
    WHERE hide != 'Y'
) b
ON LOWER(b.field_name) = c.COLUMN_NAME
WHERE
    c.TABLE_SCHEMA = 'XXXXX_1512MP5'
    AND c.TABLE_NAME = 'marketplace_custom';

Basically, config_marketplace holds the default configuration of the field names(column name) that will be shown, while merchant_field_config does the configurations of the fields I would like to hide based on each merchant's requirements. Lastly, marketplace_custom would be placed in the individual merchant's db with the column header which I would like to join to the data in that table.

PS. I know it's not a very good explanation.

Just out of curiosity, is this possible? I am working around it, but it will be a really good thing to know for future usage!

That depends upon the type of DB you actually use.

In NOSQL like MongoDB support Dynamic schemas to join the table using column name which results in embedded documents.


Understanding multiple indexes with MySQL (and performance) I have a "phones" table with around 120 000 records, it contains phone_id, phone_number, phone_user_name, phone_last_contacted, etc.

The phone_id is index and looks like: 1,2,3,4 ... 120 000.

But I often update different fields like "phone_last_contacted":

UPDATE phones SET phone_last_contacted = '01-01-2013' WHERE phone_number = '+123456789'

The "slow query" log says that query read many records before finding the right one to update;

Would it be a performance upgrade if I set the phone_number to be the index? Considering that all phone_numbers are unique in this table. If the phone_number is the index, does this mean that mysql will know how to go directly to the row to update instead of reading many rows and find the right row ? Is that the purpose of an index or multiple indexes ?

Edit: I just changed the "UPDATE" query to find the row by phone_id (which was primary key and unique), it was a huge difference. Makes sense for me now, it no longer has to look in each row in order to find the phone_number, it can find it faster by phone_id:

You speak of "the index" so I assume you mean "the clustered index."  I'd actually start by just slapping a unique index on phone_number - if you're confident that it really is and always will be unique, and if it's the field you most often filter by.

If it is unique, it's a good example of a "candidate key" - you could, technically, use it as a primary key.  However, it's also a "business key" - driven by human logic, and therefore susceptible to change, unlike an IDENTITY or other machine-generated sequence, like your phone_id field.  Biz keys are generally poor candidates for the PK, thus sticking with your current PK/clustered key.


Sharding VS Replication To improve query response will it be better to shard the data or replicate existing shards for faster response.

In case of sharding the data might be nicely distributed and hence the queries.

In case of replicating existing shards, there will be more hosts to respond to a query request.

To "improve query" response on reading data, replication will help. You could write away to your primary and read from secondaries to distribute the queries. Also the primary then is relieved of the expensive reads, and can be busy with only writing. Just be aware that there can be some lag on the data. For example a forum, it's not 100% necessary that readers can see the post within milliseconds after the insert. 1 or 2 seconds are acceptable.

The biggest advantage of replication is "backup and high availability" in case your primary goes down.

There is a some improvement with sharding if you choose a good shard key. Writing queries away 'might' be distributed if you do that correctly.

The main reason for sharding is to "horizontally expand your database". Working with big data, and not wanting to create/insert bigger and bigger disks ... you can just create new servers next to it, as much as you want. So, instead of maintaining 1 disk of 10TB, you could have 10 server is 1TB and grow bit by bit.

So, both can improve, ... on a write intensive system you might want to choose sharding, ... on a read intensive system, replica might be advised. But if you have a lot of data, choose sharding with replicas.

Example:

Server 1 :  Shard1Repl1 + Config    (datacenter/zone/network 1) write
Server 2 :  Shard1Repl2             (datacenter/zone/network 2) read
Server 3 :  Shard2Repl1             (datacenter/zone/network 1) write
Server 4 :  Shard2Repl2 + Config    (datacenter/zone/network 2) read
Server 5 :  Shard1Arbiter + Shard2Arbiter + Config  (datacenter/zone/network 3)  small server addon


Doing an insert from the source side through a linked server is problematic for performance.  Each row inserted into the target is inserted via a discrete INSERT statement inside a cursor operation.  i.e. if you are inserting 12,000 rows into the target, SQL Server will actually execute the insert 12,000 times, once for each row.

If you re-write the archive process so it runs from the destination server, it will be much faster.

As an example from your execution plan, rewrite this:

INSERT INTO @MessageEvent (ID, DateTime)
SELECT TOP (1500) ID, TimeStamp
FROM ConnectAPI.dbo.MessageEvent (NOLOCK)
ORDER BY ID

and run it from the destination server, like this:

INSERT INTO ConnectAPI.dbo.MessageEvent (ID, DateTime)
SELECT TOP(1500) ID, TimeStamp
FROM [SourceServer].ConnectAPI.dbo.MessageEvent
ORDER BY ID

Notice I left the NOLOCK query hint out.  You may want to make sure you understand the implications for partial reads and/or duplicate reads under READ UNCOMMITTED isolation, which is the isolation level used with the NOLOCK hint.

I wrote a blog post at SQLServerScience.com with minimally complete, verifiable example code showing how a simple insert of 800,000 rows from a source server to a destination server takes over 7 minutes when executed from the source server, and only 11 seconds when executed from the destination.


Why is Postgres service failing? I'm setting up a ManageIQ appliance for development.  When I updated the 'v2_key' encryption key to be same as production, my db connection croaked.  Initially I got these errors:
Aug 31 13:26:59 miq-dev.chq.ei sh[3642]: PG::ConnectionBad: FATAL: password authentication failed for user "root"

Aug 31 13:26:59 miq-dev.chq.ei sh[3642]: FATAL: no pg_hba.conf entry for host "::1", user "root", database "vmdb_production", SSL off

I updated my pg_hba.conf to:

# TYPE  DATABASE    USER  ADDRESS METHOD
local   all     all             trust
local   all         all           peer map=usermap
local   replication all           peer map=usermap
hostssl all         all   all     md5
host    replication all   all     md5
::1     vmdb_production root            trust        # <--- added last line

Now I get a new set of errors:
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Mon 2020-08-31 18:39:44 EDT; 49s ago
  Process: 3648 ExecStart=/usr/bin/postmaster -D ${PGDATA} (code=exited, status=1/FAILURE)
  Process: 3645 ExecStartPre=/usr/libexec/postgresql-check-db-dir %N (code=exited, status=0/SUCCESS)
 Main PID: 3648 (code=exited, status=1/FAILURE)

Aug 31 18:39:44 miq-dev.chq.ei postmaster[3648]: 2020-08-31 18:39:44 EDT::5f4d7c30.e40:@:[3648]:LOG:  listening on IPv4 address "0.0.0.0", port 5432
Aug 31 18:39:44 miq-dev.chq.ei postmaster[3648]: 2020-08-31 18:39:44 EDT::5f4d7c30.e40:@:[3648]:LOG:  listening on IPv6 address "::", port 5432
Aug 31 18:39:44 miq-dev.chq.ei postmaster[3648]: 2020-08-31 18:39:44 EDT::5f4d7c30.e40:@:[3648]:LOG:  listening on Unix socket "/var/run/postgresq...QL.5432"
Aug 31 18:39:44 miq-dev.chq.ei postmaster[3648]: 2020-08-31 18:39:44 EDT::5f4d7c30.e40:@:[3648]:LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
Aug 31 18:39:44 miq-dev.chq.ei postmaster[3648]: 2020-08-31 18:39:44 EDT::5f4d7c30.e40:@:[3648]:LOG:  redirecting log output to logging collector process
Aug 31 18:39:44 miq-dev.chq.ei postmaster[3648]: 2020-08-31 18:39:44 EDT::5f4d7c30.e40:@:[3648]:HINT:  Future log output will appear in directory "log".
Aug 31 18:39:44 miq-dev.chq.ei systemd[1]: postgresql.service: main process exited, code=exited, status=1/FAILURE
Aug 31 18:39:44 miq-dev.chq.ei systemd[1]: Failed to start PostgreSQL database server.
Aug 31 18:39:44 miq-dev.chq.ei systemd[1]: Unit postgresql.service entered failed state.
Aug 31 18:39:44 miq-dev.chq.ei systemd[1]: postgresql.service failed.
Hint: Some lines were ellipsized, use -l to show in full.

which tells me postgresql service is not running.
I tried the suggestion from this SO post: Postgresql12.3 wont start on boot, systemd by adding a few lines to my postgresql.service file, but I still get the same ol errors.
Does anyone have any suggestions?  Thank you

As documented in the manual the first "column" of the pg_hba.conf must be one of local, host, hostssl, hostnossl, hostgssenc or hostnogssenc. You can't put an IP address into the first column, it goes into the 4th column (if the first is not local).
The database needs to be put into the third column, then the username, then the IP address.
So this line:
::1     vmdb_production root            trust

should be:
host   vmdb_production   root  ::1  trust

This assumes you do have a Postgres user root which would be highly unusual. It might even indicate that you are trying to run that software under root which is a really, really bad idea (espcially in combination with trust)
But the root cause, why Postgres isn't starting, is the invalid entry in pg_hba.conf


How to deny viewing definition of server level trigger? I have several server level triggers, that I want users to deny viewing definition of. I control server level permissions, users aren't sysadmins etc.

I've tried 2 options:

DENY VIEW ANY DEFINITION to public

it prevents users from even seeing that those triggers exist (and who knows what else), so I don't want to go with that option

CREATE SERVER TRIGGER ... WITH ENCRYPTION

It's an option, but it says that those triggers won't be replicated, and I need to have those triggers replicated (although I can recreate them on replicas, if necessary).

Could you advice if there any other options?

Ideally it would be nice to have something like

DENY VIEW DEFINITION ON OBJECT::trigger_name TO PUBLIC

but I can't make it work for server level trigger

in a test server I create the following server side trigger for an experiment.

this trigger prevents users from dropping logins.

    --set parseonly on
    --set parseonly off

    --=======================================================================================
    -- create a trigger that prevents dropping of logins
    --=======================================================================================

    USE [master]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    create trigger [no_dropped_logins]
    on all server
    for drop_login
    as
      insert into ##LOGIN_WATCH
        select r.*, s.login_name, s.host_name,
        EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'nvarchar(max)') AS 'CmdText'
         from sys.dm_exec_requests r
           inner join sys.dm_exec_sessions s
           on r.session_id = s.session_id
           where r.session_id = @@SPID
    GO

    ENABLE TRIGGER [no_dropped_logins] ON ALL SERVER
    GO

second step I create a login in order to use it in the experiment later on.

--==============================================================
-- create a login in order to drop it and call the trigger
--==============================================================

USE [master]
GO

--DROP LOGIN [Radhe]
--GO

CREATE LOGIN [Radhe] WITH PASSWORD=N'Shy4m', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

ALTER LOGIN [Radhe] enable
GO

as a third step I connect as Radhe and check what I can see:

as you can see on the picture above - you cannot see the server side trigger.

another test:
running a query as login Radhe I cannot see the server side trigger.

execute as login='radhe'

SELECT
       *
FROM sys.server_triggers

However, when I run the same query as myself:

NOW I grant the VIEW ANY DEFINITION and see what happens:

GRANT VIEW ANY DEFINITION TO [RADHE]
GO

now Radhe can see the server side trigger as per the picture above.

check this out:

running this script:

--==================================================================================
--GRANT VIEW ANY DEFINITION AND RADHE CAN SEE THE SERVER SIDE TRIGGER
--==================================================================================
GRANT VIEW ANY DEFINITION TO [RADHE]
GO
execute as login='radhe'

SELECT * FROM sys.server_triggers
GO
revert
GO
--==================================================================================
--REVOKE VIEW ANY DEFINITION AND RADHE CAN NO LONGER SEE THE SERVER SIDE TRIGGER
--==================================================================================
REVOKE VIEW ANY DEFINITION FROM [RADHE]
GO
execute as login='radhe'

SELECT * FROM sys.server_triggers
GO
revert
GO

and the tigger is working, as you can see when I try to drop the login Radhe:

Conclusion:

Please review your current server side permissions:

SELECT
     spr.name as loginName
    ,spr.type_desc
    ,spr.is_disabled
    ,(IS_SRVROLEMEMBER ('sysadmin',spr.name)) AS IS_SYSADMIN
    ,spm.permission_name
    ,spm.state_desc
FROM
    sys.server_principals spr
    inner join
    sys.server_permissions spm ON spm.grantee_principal_id = spr.principal_id
WHERE
    spm.permission_name = 'CONTROL SERVER'
    OR
    spm.permission_name = 'VIEW ANY DEFINITION'
    OR
    IS_SRVROLEMEMBER ('sysadmin',spr.name) = 1

Please don't grant high fly permissions to public, or use it with care.


how to avoid the "An INSERT EXEC statement cannot be nested" exception on this situation? when I run the following script, it runs fine:

declare @temp table
(
    name varchar(255),
    field varchar(255),
    filename varchar(255),
    filegroup varchar(255),
    size varchar(255),
    maxsize varchar(255),
    growth varchar(255),
    usage varchar(255)
);
INSERT @temp
   exec sp_msforeachdb  @command1='use ?; Exec sp_helpfile;'

but when I use my own sp_foreachdb procedure, the source code is on this link below:

A more reliable and more flexible sp_MSforeachdb

declare @temp table
(
    name varchar(255),
    field varchar(255),
    filename varchar(255),
    filegroup varchar(255),
    size varchar(255),
    maxsize varchar(255),
    growth varchar(255),
    usage varchar(255)
);
INSERT @temp
   exec sp_foreachdb  @command='use ?; Exec sp_helpfile;'

I get an exception (please note I have added exception handling on that procedure)

--EXCEPTION WAS CAUGHT--
THE ERROR NUMBER:8164

SEVERITY: 16
STATE: 1

PROCEDURE: sp_foreachdb
LINE NUMBER: 165

ERROR MESSAGE:
An INSERT EXEC statement cannot be nested.
------------------------------------ the sql ------------------------------------

SELECT name FROM sys.databases WHERE 1=1 AND state_desc = N'ONLINE' AND is_read_only = 0
Msg 16916, Level 16, State 1, Procedure sp_foreachdb, Line 239
A cursor with the name 'c' does not exist.
Msg 16916, Level 16, State 1, Procedure sp_foreachdb, Line 240
A cursor with the name 'c' does not exist.

(0 row(s) affected)

The source code for Aaron's sp_foreachdb contains the following line:

INSERT #x EXEC sp_executesql @sql;

According to your error message:

  An INSERT EXEC statement cannot be nested.

So code like that below will not be valid as it is nesting INSERT xxx EXEC xxx code.

INSERT @temp
exec sp_msforeachdb  @command1='use ?; Exec sp_helpfile;'


composite clustered index with partitioning I have a table with hundreds of millions of rows.  Data is regularly retrieved via various queries and inserted.
There is a clustered index on

LookupID (int)
TypeID (int)
Created (datetime)

(These 3 ensure uniqueness for each row.)

There is also a unique ID (int) which could be used as a clustered index.

I tried moving the clustered index to the unique ID and created a non clustered index on the existing composite; my thinking was it would be a more efficient index as the numbers are sequential.  It didn't seem to make a huge difference to query performance, but there are so many use cases further testing would be required to definitively answer this.

My question is 2 fold - if I introduce partitioning based on Created (for the export of data for archiving), would it be better to have the composite as a non clustered index?  I have just started reading about partitioning in detail and haven't quite got to terms with 'partition alignment'.
The second question is, am I wrong in my assumption that changing these indexes would be a worthwhile endeavour?

Apologies if this is too broad or should be in stackoverflow.

I do highly recommend the link above then.  I actually gave a presentation on this to my SQL User Group a few months ago.  Here is a possible table structure for you including a partition aligned non-clustered index, as well as the procedure I used to rotate the partitions around.  It should help you get started.

Link to article: http://kejser.org/table-pattern-rotating-log-ring-buffer/

CREATE PARTITION FUNCTION [PF_100](int) AS RANGE RIGHT FOR VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100)
GO

CREATE PARTITION SCHEME [PS_100_dbo] AS PARTITION [PF_100] ALL TO (DBO)
GO

CREATE TABLE dbo.MyLog
    (
    UniqueID UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_MyLog_UniqueID DEFAULT (NEWSEQUENTIALID())
    , LookupID INT NOT NULL
    , TypeID INT NOT NULL
    , Created DATETIME2(7) NOT NULL
    , PartitionOffset AS (CONVERT(INT, DATEDIFF(DAY,CONVERT(DATETIME, 0),Created)%(100))) PERSISTED NOT NULL
    , CONSTRAINT PK_MyLog_UniqueID PRIMARY KEY CLUSTERED (UniqueID, PartitionOffset)
    ) ON PS_100_dbo (PartitionOffset)

CREATE NONCLUSTERED INDEX IDX_MyLog_LookupID_TypeID_Created
    ON dbo.MyLog (LookupID, TypeID, Created, PartitionOffset)
    ON PS_100_dbo (PartitionOffset)

CREATE TABLE dbo.MyLog_SwitchOffset
    (
    UniqueID UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_MyLog_SwitchOffset_UniqueID DEFAULT (NEWSEQUENTIALID())
    , LookupID INT NOT NULL
    , TypeID INT NOT NULL
    , Created DATETIME2(7) NOT NULL
    , PartitionOffset AS (CONVERT(INT, DATEDIFF(DAY,CONVERT(DATETIME, 0),Created)%(100))) PERSISTED NOT NULL
    , CONSTRAINT PK_MyLog_SwitchOffset_UniqueID PRIMARY KEY CLUSTERED (UniqueID, PartitionOffset)
    ) ON PS_100_dbo (PartitionOffset)

GO

CREATE OR ALTER PROCEDURE dbo.usp_SwichPartitions
    @SourceTableName NVARCHAR(128)
    , @TargetTableName NVARCHAR(100)
    , @PartitionsToKeep INT
    , @IsDebug BIT = 0
AS
BEGIN

    /** Procedure dbo.usp_SwichPartitions

        Based on http://kejser.org/table-pattern-rotating-log-ring-buffer/

        This procedure:
        * does a few sanity checks.
        * creates a small table with all possible dates, assuming that today is the most recent partition and that the offset modulo is as described.
        * Determines how many should be kept (most recent X partitions)
        * for any partitions that we want to discard, AND has data, switch to the @TargetTableName
        * truncate @TargetTableName

        INPUTS:
            @SourceTableName NVARCHAR(100)  The base table (include schema!)
            @TargetTableName NVARCHAR(100)  The table to switch to (include schema!)
            @PartitionsToKeep INT = 5       The number of partitions to keep (keep most recent X days)
            @IsDebug BIT = 0                Debug mode?

        @SourceTableName and @TargetTableName MUST exist (they must also have the exact same definition [indexes optional]

        @PartitionsToKeep must be greater than 1.

        @IsDebug (default 0), if set to 1, shows you a "what if" scenario, scripting out all of the required commands.
            NO CHANGES WILL BE MADE

    */

    /** For Testing values...
        DECLARE @SourceTableName nvarchar(100) = 'dbo.LogTable'
        DECLARE @TargetTableName nvarchar(100) = 'dbo.LogTable_SwitchTarget'
        DECLARE @PartitionsToKeep int = 5
        DECLARE @IsDebug bit = 0
        */

    SET NOCOUNT ON

    /** INPUT VALIDATION **/
    DECLARE @ThrowMessage NVARCHAR(1000);
    IF OBJECT_ID(@SourceTableName) IS NULL
    BEGIN
        SET @ThrowMessage = 'Specified @SourceTableName: ' + COALESCE(@SourceTableName, 'NULL') + ' does not exist (include schema).';
        ;THROW 50000, @ThrowMessage, 1;
    END

    IF OBJECT_ID(@TargetTableName) IS NULL
    BEGIN
        SET @ThrowMessage = 'Specified @TargetTableName: ' + COALESCE(@TargetTableName, 'NULL') + ' does not exist (include schema).';
        ;THROW 50000, @ThrowMessage, 1;
    END

    IF COALESCE(@PartitionsToKeep, 0) <= 1
    BEGIN
        SET @ThrowMessage = 'You must keep at least one (1) partition, you specified @PartitionsToKeep: ' + COALESCE(CAST(@PartitionsToKeep AS VARCHAR(10)), 'NULL') + '.';
        ;THROW 50000, @ThrowMessage, 1;
    END

    /** VARIABLE DECLARATION **/
    DECLARE @SQLCommand NVARCHAR(4000);
    DECLARE @AllPartitionCount INT;
    DECLARE @PartitionToSwitch INT;
    DECLARE @PartitionDate DATE;
    DECLARE @PartitionRowCount BIGINT;
    DECLARE @TimeToUse_UTC DATETIME2(7);
    DECLARE @RotateLogID INT;

    SET @TimeToUse_UTC = SYSUTCDATETIME();
    SET @AllPartitionCount = (  SELECT COUNT(*)
                                FROM sys.partitions AS P
                                WHERE P.object_id = OBJECT_ID(@SourceTableName)
                                    AND P.index_id IN (0, 1)
                                );

    DECLARE @PartitionsTable TABLE
        (
        PartitionTableID INT NOT NULL PRIMARY KEY
        , PartitionDate DATE NOT NULL UNIQUE
        , PartitionOffset INT NULL
        , PartitionNumber INT NULL
        , IsKeepPartition BIT NOT NULL DEFAULT (0)
        , PartitionRowCount BIGINT NOT NULL DEFAULT (0)
        );

    IF @AllPartitionCount IS NULL
        OR @PartitionsToKeep > @AllPartitionCount
    BEGIN
        ;THROW 50000, 'Cannot keep more partitions than the current buffer size', 1;
    END

    /** Build up a table of dates (starting from today) and going back for as long as many days as there are partitions.
        - PartitionOffset should match the computed column in the log table (# of Days since epoch % Total Number of partitions)  [-1 because of 0]
        **/
    ;WITH CTE_Number AS
        (
        SELECT 0 AS N
        UNION ALL
        SELECT N + 1
        FROM CTE_Number
        WHERE N <= @AllPartitionCount
        )
    INSERT INTO @PartitionsTable
    (PartitionTableID, PartitionDate, IsKeepPartition)
    SELECT TOP (@AllPartitionCount)
        N
        , DATEADD(DAY, (N *-1), @TimeToUse_UTC)
        , CASE WHEN N < @PartitionsToKeep  THEN 1 ELSE 0 END
    FROM CTE_Number
    ORDER BY N
    OPTION (MAXRECURSION 1000);

    UPDATE @PartitionsTable
    SET PartitionOffset = CONVERT(INT, (DATEDIFF(DAY, CAST(0 AS DATETIME), PartitionDate) % @AllPartitionCount))

    UPDATE @PartitionsTable
    SET PartitionNumber = PartitionOffset + 1

    --Get Row Count...
    UPDATE @PartitionsTable
    SET PartitionRowCount = P.row_count
    FROM @PartitionsTable AS T
        INNER JOIN sys.dm_db_partition_stats AS P ON P.object_id = OBJECT_ID(@SourceTableName) AND P.index_id IN (0, 1) AND (P.partition_number) = T.PartitionNumber;

    IF @IsDebug = 1
    BEGIN
        SELECT PartitionTableID
            , PartitionDate
            , PartitionOffset
            , PartitionNumber
            , IsKeepPartition
            , PartitionRowCount
        FROM @PartitionsTable
        ORDER BY PartitionDate DESC;
    END

    IF @IsDebug = 1
    BEGIN
        PRINT '/** Variables ';
        PRINT ' @TimeToUse_UTC = ' + CONVERT(VARCHAR(20), @TimeToUse_UTC, 121);
        PRINT ' @PartitionsToKeep = ' + CAST(@PartitionsToKeep AS VARCHAR(10));
        PRINT ' @SourceTableName = ' + COALESCE(@SourceTableName, 'NULL');
        PRINT ' @TargetTableName = ' + COALESCE(@TargetTableName, 'NULL');
        PRINT ' @AllPartitionCount = ' + CAST(@AllPartitionCount AS VARCHAR(10));
        PRINT ' **/';
        PRINT ' ';
    END

    /** Loop through every partitions that needs to be deleted that also
        has a PartitionRowCount.  Order by PartitionDate, removing the oldest first.
        */
    DECLARE curPartLoop CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR SELECT PartitionNumber
            , PartitionDate
            , PartitionRowCount
        FROM @PartitionsTable
        WHERE IsKeepPartition = 0
            AND PartitionRowCount > 0
        ORDER BY PartitionDate;

    OPEN curPartLoop;

    FETCH NEXT FROM curPartLoop
    INTO @PartitionToSwitch, @PartitionDate, @PartitionRowCount;

    IF @IsDebug = 1
        AND NOT(@@FETCH_STATUS = 0)
    BEGIN
        PRINT '/** NO PARTITIONS DETECTED AS NEEDING TO BE REMOVED **/';
    END

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @SQLCommand = CONCAT('ALTER TABLE ', @SourceTableName ,' SWITCH PARTITION ', CAST(@PartitionToSwitch AS NVARCHAR(10)), ' TO ', @TargetTableName, ' PARTITION ', CAST(@PartitionToSwitch AS NVARCHAR(10)));

        IF @IsDebug = 1
        BEGIN
            PRINT '/** @PartitionToSwitch = ' + CAST(@PartitionToSwitch AS VARCHAR(10));
            PRINT ' @PartitionDate = ' + CONVERT(VARCHAR(20), @PartitionDate, 121);
            PRINT ' @PartitionRowCount = ' + CAST(@PartitionRowCount AS VARCHAR(10));
            PRINT ' */';
            PRINT @SQLCommand;
            PRINT ' ';
        END

        IF @IsDebug = 0
        BEGIN

            EXEC sp_executesql @SQLCommand;

        END

        FETCH NEXT FROM curPartLoop
        INTO @PartitionToSwitch, @PartitionDate, @PartitionRowCount;

    END --WHILE @@FETCH_STATUS = 0

    --Cleanup.
    CLOSE curPartLoop;
    DEALLOCATE curPartLoop;

    --Truncate the switch table.
    SET @SQLCommand = CONCAT('TRUNCATE TABLE ', @TargetTableName);

    IF @IsDebug = 1
    BEGIN
        PRINT '/* TRUNCATE SWITCH TABLE */';
        PRINT @SQLCommand;
    END

    IF @IsDebug = 0
    BEGIN

        EXEC sp_executesql @SQLCommand;

    END

END


How to set up development servers SQL Server My .NET development team has been massively restructured recently, and as a result of the restructure, we now have the ability to do things like set up source control and database backups, and development databases.  However, none of us is really a DBA, and so we have no clue how to do this on the DB side.

We would like to have some way to make it so that developers can be working with real data for testing, but are unable to change the data on the production server.  Right now, we just have one database, so if we need to test a change to the website, we have to make dummy records to test.

Basically, we know this is a bad setup, but we don't know any better, and we really want to change.  I can't find anything online that gives an overview of how to actually set up a development environment which can be used for both application development and database development.

I've looked at How to develop a database (workflow)? and How to setup local database development process for small web team?, but the first one doesn't address my problem, and the second one assumes a lot of knowledge that I don't have. I don't know anything about "normal" database development practices, rollout/rollback scripts, how to write tests for database development, schema comparison tools, etc. (These are all terms I've seen in my search, but I don't know where to go to learn about them.)

Does anyone know of a place where I can go to learn about this stuff?  And/or can anyone give me some advice on how to set up the environment that I don't know how to describe?

My team is very averse to using TFS.  I'm not sure why (I've never used it myself), but they like Git, and I know Git, so that is the source control we've decided on.  My main question was actually how to set up separate servers for the different environments, while having current data to work with in development.

Most of these topics aren't overly difficult but there are so many that I think you would struggle starting from scratch.

Furthermore because you're coming at it from a developer workflow point of view you'll likely discover that this kind of information doesn't get talked about much. You're likely going to have to pay for a consultant to come in, help you set up the dev server with basic backups and maintenance, and otherwise create a workflow for you.

  Does anyone know of a place where I can go to learn about this stuff?

If you were determined to do it yourself you'd search YouTube for videos on SSDT or SQL Server Database Projects which is part of Visual Studio; most of what you want is covered in some way by that (unit testing, source control through TFS or similar, deployments through msbuild, and also even schema compares). Unit testing is also commonly done with a framework called tSQLt. Deployments are sometimes done with Flyway. Database backups and maintenance are usually done with Ola Hallengren scripts or Minion Backup / Reindex.

You'd also take a look at SQL PASS which has a lot of chapters which have online training videos on each of those topics. However most of them will come up via a YouTube search.

Those are all free tools with caveats around Visual Studio of course.

For inexpensive non-tailored training Pluralsight has a video on each of these topics above and other database administration. But if you're not a DBA then you probably don't want to waste time on learning all of these, and you're still going to be stuck with creating your own workflow. There is no "developer workflow" from soup to nuts.

Red Gate does a lot of SQL Server developer software (in particular SQL Compare, SQL Source Control, and SQL Tests). You could search for their training videos for some ideas about how a workflow in these or other tools might look like. Again you can find these on YouTube, though they also publish some free downloadable books as part of their marketing.


Is using SUM() twice suboptimal? I know I have to write SUM twice, if I wish to use it in a HAVING clause (or use a derived table otherwise):

SELECT  id,
  sum(hours) AS totalhours
  FROM mytable
  GROUP BY id
  HAVING sum(hours) > 50;

My question now is, whether or not this is suboptimal. As a programmer, this query looks like the DB will calculate the sum twice. Is that so, or should I rely on optimizations the DB engine will do for me?

Update: an explain of a comparable query:

postgres=> explain select sum(counttodo) from orderline group by orderlineid having sum(counttodo) > 100;
                             QUERY PLAN
--------------------------------------------------------------------
 HashAggregate  (cost=1.31..1.54 rows=18 width=8)
   Filter: (sum(counttodo) > 100)
   ->  Seq Scan on orderline  (cost=0.00..1.18 rows=18 width=8)
(3 rows)

The sum is only computed once.

I verified this using

create table mytable (id int, hours int);
insert into mytable values (1, 60);
select sum(hours) from mytable group by id having sum(hours) > 50;

and then used a debugger to check how many times int4_sum (the transition function behind the sum aggregate) was called: once.


Migrating database from old software to new software I am a young web developer and I am facing a problem I cannot solve on my own and need some help and/or orientation.

My client has an agenda (Lotus), which is a very old software, and I have developed a web-based agenda (which uses MySQL). What I need to do is to migate all the contact database from the old agenda to the new one.

I managed to export the database from Lotus(old software) in the following formats: ANSII, ASCII, DBASE, VCARD. Which one will be the most useful?

There is another problem, my new database is slightly different (It does not have all the same fields, and, per example, it has another table for other numbers or other e-mail addresses, and some other differences.)

I thought of importing the same exact database (both structure and data) from the old software to SQL, and then, write a .php script whichs converts it to my new structure. (This because I am used to sql and php, and I can't figure out a better way to do it)

Is my idea a good way to do? Can you recommend a better one? How do I import this database to SQL?

EDIT: Answering to Max Vernon, in my new software I am using MySQL database. I am not sure if that was your question, I am a novice to databases and had to google up RDBMS. And to the other question, the ansii and asci backups looks like the table column names between quotes separed by comas, and values between quotes separed by comas

I edited the whole post because it has been marked as unclear.

Create a second MySQL database. This will be used for the migration and then dropped. These are known as staging databases. Make it an exact, table-for-table copy of your source. This way it is easy to export form the source and import into staging without worrying about mapping columns. The documentation will tell you how to load flat files into tables, and what formats it supports.

Once staging is fully populated write however many INSERT .. SELECT statements you need to map staging columns to the new DB columns and copy the data.

Do all this in scripts, not through the UI. Then it will be reproducable, debugable and audited. If you find a problem in the new app you can fix the script and re-run.


Updating a table from a temporary table where clause error in MySql Community I am trying to update an existing table named "residential_summary" from a temporary table called "a". I can query the temporary table a but when I try to use it in an update statement I get

Error Code: 1054 Unknown column 'a.meter' in 'where clause'

Is this something that you just cant do or am I somehow misinterpreting the syntax for Update? Why is my column unknown? Please elaborate because I am new at databases. Here is my code and a screenshot.

UPDATE residential_summary
SET residential_summary.load_factor = a.load_factor
WHERE a.meter = residential_summary.meter;

SQL queries always explicitly reference their tables by name, apart from any references to the columns in the tables. Since your query doesn't include a reference to the 'a' table explicitly as a table, the engine has no idea what a is. I'm a SQL Server guy, but based on some quick Googling of "MySQL update join", looks like the statement should be:

UPDATE residential_summary
         INNER JOIN a ON residential_summary.meter = a.meter
SET residential_summary.load_factor = a.load_factor;

The UPDATE ... INNER JOIN ... tells the engine that this query will be working with the residential_summary and a tables. The INNER JOIN ties residential_summary and a together based on the values of meter in both tables, so that only residential_summary rows that have a matching row in a will be updated.


Procedure to wholly migrate SQL Server 2005 to a new server I'm tasked with 'rebuilding' a database server, because the old one is dying. To reduce potential for the new server f**king up, we had decided to rebuild the server as similar as possible to the old one, hence, SQL Server 2005 Enterprise on top of Windows Server 2003.

Now, the original dev/maintainer of the old server has gone the way of the dodo (as in, he's left the company quite some time ago, and nowhere to be found now). So, we had to do the migration in-whole.

To make matters worse, the old server is a hodge-podge of: Databases, a mix of user credentials (SQL Server authentication and Windows authentication), a crowd of Linked Servers, and a gaggle of SQL Server Agent Jobs.

So, how do I approach this monumental task? In my mind, I had the following half-baked plan:

Copy the databases using the Copy Database Wizard
Copy the users using Script > CREATE To
Re-create all Linked Servers
Copy the Jobs using Script > CREATE To

For every step above, I might have to do it one-by-fricking-one :(

Is there an easier (as in, less tedious) way to do the migration?

Do not use copy database wizard. Instead use backup/restore. Also, if you want to minimize the downtime then below steps would be sufficient :
Prepwork on the New server :

Create blank databases with the same name as the old server. This is because if any logins or jobs has that database as default, there would be errors.

Script out all the logins using the sp_help_revlogin from the old server to the new server and create them on the new server.

Script out all the jobs. You dont have to Script > CREATE To for each jobs. Instead right click in Object Explorer and script all the jobs in one shot :-) Remember to disable the jobs until you failover to the new server.

Scrip out all Linked servers and recreate them on the destination server. Remember that passwords will not be scripted out.

Once 1-4 is set up then set up either Logshipping or database mirroring (Async mode) from source server to destination server.

On the day of failover

if using logshipping then manually failover to new server.

if using Mirroring then change the mode to synchronous and then initiate a failover to new server.

This way you will do all the prep work before hand and then during the day of failover, the downtime will be minimal.
You can refer to my answer HERE for more steps (they are for migration from old version to higher version, but still will help you).


Relationship with a default value Firstly, I know my title is somewhat misleading, but I couldn't think of a better title.

Here is my current relationship

CREATE TABLE Events
(
    ID INT IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(64) NOT NULL,
    DefaultLocationID INT NULL,
    PRIMARY KEY(ID)
)

CREATE TABLE Locations
(
    ID INT IDENTITY(1,1) NOT NULL,
    EventID INT NOT NULL,
    Name NVARCHAR(64) NOT NULL,
    PRIMARY KEY(ID)
)

ALTER TABLE Events ADD FOREIGN KEY(DefaultLocationID)
    REFERENCES Locations(ID)
    ON UPDATE CASCADE ON DELETE SET NULL

ALTER TABLE Locations ADD FOREIGN KEY(EventID)
    REFERENCES Events(ID)
    ON UPDATE CASCADE ON DELETE CASCADE

My question is knowing that an Event can have a list of 0 or more Locations, and each Location can only be associated with exactly 1 Event.

I want the ability for an Event to have a default Location, but the integrity is that if an Event has a Location then that Locations foreign key must be the same Event.

My initial thought was to put a nullable foreign key in the Event table DefaultLocationID, but it dawned on me that there is no integrity to prevent that Location from being associated with a different Event.

My other idea was to have a IsDefault field on the Location table, but that doesn't stop multiple Locations from having their IsDefault field to true.

How would I enforce this relationship, preferabily without triggers and complex sub-queries?

For your second option, you could put a filtered unique index on:

CREATE UNIQUE INDEX ix_DefaultLocation ON dbo.Locations(EventID, IsDefault)
WHERE IsDefault = 1

This will allow unlimited non-default locations, but only one default location per event id.


Recover deleted table from the database Currently I deleted one table from the database. But now I want some of the information from that table.

Please suggest how to restore or get back the table.

I am using SQL Server 2008 R2.

You can restore the BACKUP with a different name in the same instance and follow steps below.

1) restore the database ( right click on databases > Restore ( it can be in the same instance )) with a differente name.

2) In this new database ( The restored one ) , open tables , search the table you've deleted, right button, Script table as > CREATE TO. This will crate a Script. Execute it on the old database ( the one you have deleted the table). It will create the structure of that table.

3) Now you can do something like this:

USE [Old Database]
GO
SELECT * INTO [the blank table you've created in the new database]
FROM [the table on the backup, that has Data]

This will copy the data from the backup table, to the table you created.


How to reduce LOG file after switching from full recovery mode to simple mode? I have a database with full recovery model and without TLog backup.

The log file have grown to 160GB and I found out that it needs TLog to be periodically backed up or the recovery model switched to simple. Now that I have switched to simple recovery model, the .ldf file is still of the same size.

How can I reduce it?

You need to perform a shrink operation on the log file.

You can follow the instructions there.

PS: Don't forget to switch back to FULL Mode.


How do I migrate varbinary data to Netezza? I got a warning message while migrating DDL from SQL Server to Netezza:

  Warning: [dbo].[spec_binarymessage].[blobdata] data type [varbinary] is not supported the target system and will be scripted as VARCHAR(16000).

I'm wondering whether this kind of data conversion will cause some issues such as truncation of data etc.?

Best is to test it as Netezza does not support varbinary. So it has to be converted to a supported varchar(16000) or VARCHAR(64000), VARCHAR(n), NVARCHAR(16000), NVARCHAR(n).

Test your migration plan before hand.


MySQL EXPLAIN doesn't show 'use index' for FULLTEXT I have a basic table  :

create table fullTextTest
(
    id INT(11) NOT NULL,
    superText CHAR(255) NOT NULL,
    superLongText TEXT NOT NULL,
    primary key (`id`),
    FULLTEXT KEY `superText` (`superText`),
    FULLTEXT KEY `superLongtext` (`superLongtext`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into fullTextTest
    set id=1,
    superText="Hi guys, how is it goin'?",
    superLongtext="Please give me some dummy text to search on!!!"
;

show index from fullTextTest;
| fullTextTest |          0 | PRIMARY       |            1 | id            | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| fullTextTest |          1 | superText     |            1 | superText     | NULL      |        NULL |     NULL | NULL   |      | FULLTEXT   |         |
| fullTextTest |          1 | superLongtext |            1 | superLongText | NULL      |        NULL |     NULL | NULL   |      | FULLTEXT   |         |

Now , let's see if MySQL is using this index correctly :

EXPLAIN select * from fullTextTest where match(superText) AGAINST ("guys" IN BOOLEAN MODE);
| id | select_type | table        | type     | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+--------------+----------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | fullTextTest | fulltext | superText     | superText | 0       |      |    1 | Using where |

The "Using Where" shows me that EXPLAIN don't understand FULLText.. Which is a shame, really.

Did I miss something ?

I can understand why this happening
You only have one row in the table.
MySQL Query Optimizer will execute a plan around this rule-of-thumb : If the number of rows needed to examine the fulfillment of a query through an index exceeds 5% of the total of number of rows, the Query Optimizer will not use the index and will go with a table scan or an index scan instead.
How many rows in the table ? One
How many rows are indexed ? One
Since you will be examining 100% of the rows, a keyed lookup will not chosen. The query will be executed. You simply do not have enough rows to justify getting an index involved in the search. This applies in general for anything indexed. FULLTEXT indexes tends to get thrown the Query Optimizer under the bus at the most inconvenient times. I wrote about that back in Jan 26, 2012.
Try loading more rows into the table, at least 21 rows, and try again.


MySQL - Alter Table Fails As Workbench Tries To Create Existing Table I have been trying to add another column to a table in my database using MySQL Workbench. This generates an 'ALTER TABLE' command which then fails as  it fails attempting a 'CREATE TABLE, which it should not be doing as the table already exists!  The output is below:-

ERROR 1114: The table 'Analysis' is full
SQL Statement:
ALTER TABLE `Patient`.`Analysis` ADD COLUMN `TestDateTime` DATETIME
NULL AFTER `idResults`

ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'Patient' already exists
SQL Statement:
CREATE TABLE `Patient` (
`idAnalysis` int(11) NOT NULL,
`idResults` int(11) DEFAULT NULL,
`ResultPDF` mediumblob,
`ResultCSV` mediumblob
) ENGINE=InnoDB DEFAULT CHARSET=latin1

How can I add this extra column as this database will evolve over time as user requirements expand?

Thank you.

If you have a lot of records in your table, take a look at my answer to a related question here.

With respect to MySQL Workbench, I have always found the tool to be excellent.

Having said that, what MySQL Workbench 'may' be doing in the background is creating an empty temporary table with the extra field, copying over all the records, the deleting your old Patient table and renaming the temp table to Patient and then having problems creating the "new" Patient table. You'd have to check the source code to find out for sure what's going on - or maybe run SHOW TABLES; continuously against your system while the update was being performed.

Where the message "The table "Analysis' is full" is coming from, I don't have a clue - does it exist in your schema? Do you have spare disk space for your MySQL datadir, or is it getting tight?

In any case, if a GUI tool fails, the first thing to do is to is to run the command from the mysql command line tool (another reason to prefer the command line), just to ensure that you're really dealing with a MySQL problem and not an issue with Workbench. Do you have the output of the error log? That might also be helpful.


Security of streaming replication? If I want to use streaming replication between two Postgresql databases on different parts of the internet, is there a way to secure or encrypt the connection between the two? Or are there reasons other than security to not do that?

I'd like to prevent eavesdropping via a man-in-the-middle attack.

Streaming replication in PostgreSQL uses the same security mechanisms as a normal frontend-backend connection.  So in order to encrypt the traffic, SSL should be used, and access control should be set up.

Alternatively, or additionally, consider a VPN of some kind.  PostgreSQL instances aren't really meant to be run facing the open internet.


How to backup Sharepoint databases I am responsible for backups of normal DBs, but SharePoint is handled by others. Bit worried that we miss something here.

Is SharePoint backup handled completely through some tool in SharePoint, or should I also make regular SQL Backups of the databases outside SharePoint?

Open SQL Server Management Studio and connect to the database server. Right-click the database that you want to back up, point to Tasks, and then click Back Up.


Query to change all field in a given mysql table? I have a table with a timestamp field called "time".

I have many records in this table and "time" has value like: 1343296642, 1343308689...

I need to run a query to subtract 2 years of time to those value.

Can I do this.

Perhaps this UPDATE may do it:

UPDATE tablename SET `time` = `time` - INTERVAL 2 YEAR;


how to remove Mariadb temp files I have a /mariadb_tmp directory (as specified in my mariadb-server.cnf) which is filled with files dating back to last November.

-rw-rw---- 1 mysql mysql   1032192 Nov 19 07:47 #sql899_5d5_0.ibd
-rw-rw---- 1 mysql mysql    278528 Nov 19 09:30 #sql899_ab8f_0.ibd
-rw-rw---- 1 mysql mysql    278528 Nov 19 09:30 #sql899_ad17_0.ibd
-rw-rw---- 1 mysql mysql    638976 Nov 19 09:32 #sql899_baf3_0.ibd
-rw-rw---- 1 mysql mysql    409600 Nov 19 09:32 #sql899_bbec_0.ibd
-rw-rw---- 1 mysql mysql    262144 Nov 19 09:35 #sql899_c96a_0.ibd
...
-rw-rw---- 1 mysql mysql    704512 Jan 16 10:08 #sql899_603c0c4_1.ibd
-rw-rw---- 1 mysql mysql    540672 Jan 16 10:08 #sql899_603c123_0.ibd
-rw-rw---- 1 mysql mysql 494927872 Jan 16 10:20 #sql899_603eb7b_0.ibd
-rw-rw---- 1 mysql mysql    606208 Jan 16 10:27 #sql899_60470ff_1.ibd

Googling on 'delete old mariadb temp files' leads to advice such as:

drop table `#sql899_5d5_0`;

which results in the error 'no database selected.'  Of course, I don't know what database each tmp files comes from, so I don't know if that would work.

There are no *.frm files to go with these, and they are in the specified tmpdir directory, not the datadir.

Any idea how to clean these up?  They are taking up a bunch of space on a disk that we really can't expand (for non-technical reasons) nor should we have to keep these around that long.

Thanks,

Ed Greenberg

After checking they aren't attached to an active process, ls -la /proc/$(pidof mysqld)/fd, or fuser {filename}, they can be removed with rm.

From MariaDB-10.4 onwards you shouldn't see these created on disk again ( MDEV-15584).


Cannot execute stored proc linked to C routine in DB2 DB2 version: 9.7.0.6
OS: RHEL 5

Here are the steps I took to create and bind a C routine to DB2:

1> Created myprog.sqC file
2> Executed db2 prep myprog.sql bindfile
3> Executed db2 bind myprog.bnd
4> Compiled the C code by executing

gcc -I /.../sqllib/include -c -fPIC myprog.C

5> Created a shared library i.e. myprog.dll by executing

 gcc -shared -o myprog.dll myprog.o -L /.../sqllib/lib64 -ldb2

6> Copied over the dll file created from Step 5 to /.../sqllib/function/. Set the permission 755 on the myprog.dll file

7> Created stored proc as follows:

create procedure myschema.test_func (IN INT_VAL1 INTEGER, OUT INT_VAL2 INTEGER) SPECIFIC test_func1 DYNAMIC RESULT SETS 0 NO SQL NOT DETERMINISTIC LANGUAGE C FENCED THREADSAFE PARAMETER STYLE SQL EXTERNAL NAME 'myprog!myfunc'

8> However, when I run the db2 command line : call myschema.test_func(10, ?), I get the error:

SQL0444N  Routine "myschema.test_func" (specific name "test_func") is
implemented with code in library or path ".../sqllib/function/myprog",
function "myfunc" which cannot be accessed.  Reason code: "4".
SQLSTATE=42724

I have been looking around for quite some time and frankly, I have given up.

Can anyone please help me out here to debug/understand the reason behind error in Step 8?

Your compiled shared library is named myprog.dll, but you've told it to look for one called myprog in the create procedure call (the .dll suffix is a Windows-ism).

Rename it from myprog.dll to myprog.


Why is CDC capture failing due to a missing dbo principal I am having a problem running the CDC change capture job against some of my test databases.

The error message I'm getting is the following:

  Executed as user: NT AUTHORITY\NETWORK SERVICE. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. [SQLSTATE 42000] (Error 15517).

How can I fix this?

dbo is mapped to an invalid login. Re-map it to a valid one:

ALTER AUTHORIZATION ON DATABASE::[<dbname>] TO [sa];


How do I determine if a column is defined as a serial data type instead of an integer based off the catalog? So I'm currently creating some SQL to read through the postgres (9.1) catalogs to build table definitions. However, I am encountering a problem with SERIAL/BIGSERIAL data types.

Example:

CREATE TABLE cruft.temp ( id BIGSERIAL PRIMARY KEY );
SELECT * FROM information_schema.columns WHERE table_schema='cruft' AND table_name='temp';
"db","cruft","temp","id",1,"nextval('cruft.temp_id_seq'::regclass)","NO","bigint",,,64,2,0,,,,,,,,,,,,,"db","pg_catalog","int8",,,,,"1","NO","NO",,,,,,,"NEVER",,"YES"

It gives me database name (db), schema name (cruft), table name (temp), column name (id), default value (nextval( ... )), and data type (bigint and int8 .. NOT bigserial) ... I realize that I could just check to see if the default value was a sequence - but I don't believe that would be 100% accurate since I could manually create a sequence and create a non serial column where the default value was that sequence.

Does anyone have a suggestion for how I might accomplish this? Anything other than checking the default value for a nextval(*_seq)?

Edited for SQL solution added here in case of TL;DR or new users unfamiliar with the pg_catalog:

with sequences as (
  select oid, relname as sequencename from pg_class where relkind = 'S'
) select
  sch.nspname as schemaname, tab.relname as tablename, col.attname as columnname, col.attnum as columnnumber, seqs.sequencename
from pg_attribute col
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid and deps.deptype = 'n'
left join sequences seqs on deps.refobjid = seqs.oid
where sch.nspname != 'information_schema' and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
  and col.attnum > 0
  and seqs.sequencename is not null -- TO ONLY VIEW SERIAL/BIGSERIAL COLUMNS
order by sch.nspname, tab.relname, col.attnum;

Let me add to efesar's answer that the documentation states the following:

  The data types smallserial, serial and bigserial are not true types,
  but merely a notational convenience for creating unique identifier
  columns (similar to the AUTO_INCREMENT property supported by some
  other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

  is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Which means that if

a column's data type is integer (bigint) and
it is NOT NULL and
its default value comes from a sequence owned by the column in question

then it is a serial column.  So checking these factors in the catalogs, as you proposed (with the addition of NOT NULL), are sufficient to identify a serial column.

For an actual query to find the (big)serials, see the excellent answer of Erwin Brandstetter.


Querying with multiple where conditions and multiple columns I have a Policies table.

policyId
status ("Pending" or "Issued")
pendDate
issueDate

Now I need get all pending Policies with a pendDate in the month of October AND all issued Policies with a issueDate in the month of October.

I'm using postgreSQL 9.6. These dates are nullable so I cant use a simple AND. I need only the pending and issued in that month so ORs dont work.

I solved with two queries but now more statuses are being added and I want to consolidate somehow. Possible?

If I understand correctly you can get it by using an OR condition in WHERE clause:

create table Policies
(
    policyId int,
    status   varchar(100),
    pendDate date,
    issueDate date
);

insert into Policies values
(1, 'Pending', '20171001', null),
(2, 'Pending', '20171002', null),
(3, 'Pending', '20171003', null),
(4, 'Pending', '20171101', null),
(5, 'Pending', '20171102', null),
(6, 'Issue', null, '20171002'),
(7, 'Issue', null, '20171003'),
(8, 'Isuue', null, '20171101');

✓

8 rows affected

select policyId,
       status,
       pendDate,
       issueDate
from   Policies
where  (status = 'Pending' and extract(year from pendDate) = 2017 and extract(month from pendDate) = 10)
or     (status = 'Issue' and extract(year from issueDate) = 2017 and extract(month from issueDate) = 10)

policyid | status  | penddate   | issuedate
-------: | :------ | :--------- | :---------
       1 | Pending | 2017-10-01 | null
       2 | Pending | 2017-10-02 | null
       3 | Pending | 2017-10-03 | null
       6 | Issue   | null       | 2017-10-02
       7 | Issue   | null       | 2017-10-03

dbfiddle here


DB2 for i equivalent of MSSQL's Show Query Execution Plan I'm a DB2 newcomer and I'm hoping that DB2 for i has an equivalent to MS SQL's Show Query Execution path.
If so, what's it called, how do I access it, and what permissions do I need to use it?
Thank you!

Per @mustachhio's comment, the tool I was looking for was VisualExplain in the System i Navigator.