High tempdb disk I/O during merge replication of BLOBs Having a merge publication for replicating BLOBs (type image), got very high tempdb disk I/O for my size of data. Publication is download-only and has no filters.

High disk I/O is caused by synchronization (when no subscribers are synchronizing, everything is ok), strongly correlated with number of subscribers. It happens even when no data is changed at Publisher between synchronizations, and that bothers me.

Size of replicated table: 7MB (total count of rows is about 100)
tempdb I/O : ~30 MB/sec for write (log and data files)
Number of subscribers: slightly over 100, each synchronizing every 30
minutes (more or less evenly).
Retention period set to 14 days

Using SQL Server 2008 at Publisher, SQL Server 2005-2008R2 at Subscribers.
All subscribers use Web Synchronization.

Additionally, synchronization at subscriber takes a lot of time, with multiple occurrences in replmerg.log like these:

DatabaseReconciler, 2015/04/21 12:13:40.348, 3604, 25088,  S2,
INFO: [WEBSYNC_PROTOCOL]
Sending client ReconcilerPhase WebSyncReconcilerPhase_RegularDownload

DatabaseReconciler, 2015/04/21 12:13:47.063, 3604, 25194,  S2,
INFO: [WEBSYNC_PROTOCOL]
Received server ReconcilerPhase WebSyncReconcilerPhase_LastRegularDownload

Tried setting @stream_blob_columns on and off with no effect.

The question is: Is it a good idea to use merge replication to send these blobs to subscribers? We have other publications (though they have no BLOB columns) with a lot of data without tempdb problem. Is it an SQL Server flaw, or bad setup?

Publisher and Distributor are on the same instance, SQL Server 2008 SP4, cannot be sure about Subscribers, some of them maybe not up-to-date. Anyway, I can prepare a test environment with few subscribers having controlled versions, if it seems to help.

Confirmed, that excessive tempdb usage caused by execution of sys.sp_MSenumgenerations90. Checked MSMerge_genhistory table, found over 1.2 millions of records where pubid is null.

Found this conversation with replication guru:

Executed sp_mergemetadataretentioncleanup with no effect.

Found a remark on a case like this (too much rows in MSmerge_genhistory) where deleting rows where pubid is null and genstatus=1 helped to fix replication.

Deleted rows where pubid is null (implying that all Subscribers are synchronized, and which are not - will be reinitialized in "manual mode") and disk IO is back to normal again!

I have a feeling, that this situation could be caused by the fact, that all of my Subscribers are anonymous via WebSync and most of them have the same hostname. I'll try to check, if -hostname key helps not to multiply records in MSmerge_genhistory.

There is a TechNet blog that discusses some merge replication issues with blobs on a SQL Server 2008 or higher server.

http://blogs.technet.com/b/claudia_silva/archive/2011/10/31/replication-watch-out-for-stream-blob-columns-when-setting-up-replication-on-your-sql-2008-server.aspx

Note that the author cautions about which settings to use when there are SQL Server 2005 clients, such as you have.

Brent is define why we need to increase the tempDb data file.you can refer below link

https://www.brentozar.com/blitz/tempdb-data-files/


Dropping a constraint (index) on a column How can I modify the type on a table that has an index on it? I tried to do an alter column on an empty table to modify the type from date time to varchar(15) and got errors saying that it had dependencies on the column (which turned out to be indexes).

I was able to easily get around this locally by right clicking the index and scripting a drop, but I need to roll this out on other servers where I won’t have access to the index name.

Is there a way I can make a script that will drop any index, let me make that datatype change on the column then read the index? Thanks!

You can use sys.indexes view to get indexes. You can join this table to sys.tables, sys.columns and sys.index_column to get informations to create a dynamic drop index. You can use this simple select to generate your drop index. You can use the where clause to filter the tables and the columns. If you want to change Table1.Column1 you must use those name to filter the select to get the right delete statement

use [testdb]
go
declare @sqlDropIndex NVARCHAR(1000)

select @sqlDropIndex = 'DROP INDEX ' + idx.name + ' ON ' + tbl.name
from sys.indexes idx inner join
        sys.tables tbl on idx.object_id = tbl.object_id inner join
        sys.index_columns idxCol on idx.index_id = idxCol.index_id inner join
        sys.columns col on idxCol.column_id = col.column_id
where idx.type <> 0 and
        tbl.name = 'MyTableName' and
        col.name = 'MyColumnName'
group by idx.name, tbl.name
order by idx.name desc

print @sqlDropIndex
--exec sp_executeSql @sqlDropIndex
go

Hope this will help you

There are 2 mistakes in Niko's answer (2 object_id conditions missing).
And no need to group by:

SELECT  @sql = 'DROP INDEX ' + idx.name + ' ON ' + tbl.name
FROM    sys.indexes             idx
INNER JOIN sys.tables           tbl     ON idx.object_id = tbl.object_id
INNER JOIN sys.index_columns    idxCol  ON idx.index_id = idxCol.index_id   AND idx.object_id = idxCol.object_id
INNER JOIN sys.columns          col     ON idxCol.column_id = col.column_id AND  idxCol.object_id = col.object_id
WHERE   idx.type <> 0
AND     tbl.name = 'file_transfer_log'
AND     col.name = 'tender_id';


Why does a UNIQUE constraint allow only one NULL? Technically, NULL = NULL is False, by that logic no NULL is equal to any NULL and all NULLs are distinct. Shouldnt this imply that all NULLs are unique and a unique index should allow any number of NULLs?

Why does it work this way? Because way back when, someone made a design decision without knowing or caring about what the standard says (after all, we do have all kinds of weird behaviors with NULLs, and can coerce different behavior at will). That decision dictated that, in this case, NULL = NULL.

It wasn't a very smart decision. What they should have done is have the default behavior adhere to the ANSI standard, and if they really wanted this peculiar behavior, allow it through a DDL option like WITH CONSIDER_NULLS_EQUAL or WITH ALLOW_ONLY_ONE_NULL.

Of course, hindsight is 20/20.

And we have a workaround, now, anyway, even if it isn't the cleanest or most intuitive.

You can get the proper ANSI behavior in SQL Server 2008 and above by creating a unique, filtered index.

CREATE UNIQUE INDEX foo ON dbo.bar(key) WHERE key IS NOT NULL;

This allows more than one NULL value because those rows are completely left out of the duplicate checking. As an added bonus, this would end up being a smaller index than one that consisted of the entire table if multiple NULLs were allowed (especially when it isn't the only column in the index, it has INCLUDE columns, etc). However, you may want to be aware of some of the other limitations of filtered indexes:

How filtered indexes could be a more powerful feature

One of the major purposes of a UNIQUE constraint is to prevent duplicate records.  If one needs to have a table in which there can be multiple records where a value is "unknown", but no two records are allowed to have the same "known" value, then the unknown values should be assigned artificial unique identifiers before they are added to the table.

There are a few rare cases in which a column which has a UNIQUE constraint and contains a single null value; for example, if a table contains a mapping between column values and localized text descriptions, a row for NULL would make it possible to define the description that should appear when that column in some other table is NULL.  The behavior of NULL allows for that usage case.

Otherwise, I see no basis for a database with a UNIQUE constraint on any column to allow the existence of many identical records, but I see no way to prevent that while allowing multiple records whose key values are not distinguishable.  Declaring that NULL isn't equal to itself won't make NULL values distinguishable from each other.


Mongodump: Unrecognized field 'snapshot' I am using mongodump from mongo tools to download a backup of my server. However as I run the command, the command returns "Unrecognized field 'snapshot'".

The full error is:

Failed: error reading collection: Failed to parse: { find: "data", skip: 0, snapshot: true, $readPreference: { mode: "secondaryPreferred" }, $db: "xxx" }. Unrecognized field 'snapshot'.

Mongodump --version returns:

mongodump version: built-without-version-string
git version: built-without-git-spec
Go version: go1.7.3
   os: linux
   arch: amd64
   compiler: gc
OpenSSL version: OpenSSL 1.0.2g  1 Mar 2016

I might need to downgrade my version? Otherwise unsure of where to go from here...

Add --forceTableScan
Example:
mongodump --forceTableScan -d database_name -o target_directory

http://aug2uag.blogspot.com/2018/08/mongoexport-unrecognized-field-snapshot.html

The Error comes from the mongo snapshot functionality which was introduced in mongo 4.0 (https://docs.mongodb.com/manual/reference/read-concern-snapshot/)
If you use an older mongo client (<4.0) to dump/export a more recent MongoDB you will get the error

Unrecognized field 'snapshot'

Using "forceTableScan" will prevent the snapshot functionality to become active, this you won't get the error

If you dump from Mongo Atlas, this should work.
mongodump --forceTableScan --uri "mongodb+srv://username:password@clusteraaa.xxxx.mongodb.net/db_name"  --out "backup-dir"


How to fix below blocking problem on SQL server 2014 I am having a strange blocking problem which i am not able to understand.
During a slighter more volume due to some app pooling issues we see sleeping spids with open transactions, so those SP's tend to be lead blocker and blocks few delete statements.
Yes we are aware on sleeping SPID and working with app team to fix the issue, but i am not able to understand why on below
SP's which leave their session open say EXEC spleadblocker does not access any of the table underneath within its code i.e
it blocks a simple statement like below within an SP say spblocked
DELETE from dbo.table1 WHERE Col1=@col1
OPTION (QUERYTRACEON 9481)

spleadblocker does not have any thing to do with table "table1" and should not blocking therefore above statement
Waittype is LCK_M_X
and wait_resource is database.dbo.table1.PK_table1

table 1 has col 1  as PK /Clustered index created , which is of data
type bigint not null column

Why an SP with no correlation to this table will create blocking and is there a way i can avoid that PK scan from that delete statement to avoid being blocked?
UPDATE After more research below is what we found, say the very first SP which comes SP1leablocker for spid 66 and had its transaction open with status sleeping for about a min. This session block the delete statement above and yes they have table 1 in SP1leablocker code, so explains the blocking
Later, on above spid 66, multiple times we see different SP's running and creates a blocking chain for that delete statement of around 10 mins because that SPID 66 gets connection from various other SP's with status sleeping and open tran
Is there a way to avoid blocking on DELETE statement ?

As stated by a previous user the lock type indicates that your query tries to acquire an Exclusive lock on a resource.
Is your predicate (Col1=@col1) supported by in index ?
Some options for a query referencing a specific table to acquire locks on other tables can be: triggers, fks and fk cascading.
More info is required. See this post for more ideas.
Have you tried to capture the blocked process report ?
See this post for how to run a server side trace to capture the "Blocked Process Report Event" constantly in the background so that when your issue occurs you do not miss it and have the data needed for analysis


Oracle 11gR2: ORA-01102: cannot mount database in EXCLUSIVE mode I am running Oracle 11gR2 on an AIX box.  I did a rename database and change DB ID using NID.  I can restart db in a nomount mode, but I can not mount it by the following error.

ORA-01102: cannot mount database in EXCLUSIVE mode
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01507: database not mounted

I started DB by using a new pfile.  I notice that the NID utility did not change the old DB folder names in ASM.  So this rename database effort will not be perfect.

I have already removed some easy things when I Googled this error, such as removing the lk* files under $ORACLE_HOME/dbs.

Any suggestions for me to start the database after the renaming command of NID?

Thank you so much!

ORA-01102 cannot mount database in EXCLUSIVE mode

  Cause: Some other instance has the database mounted exclusive or shared.
  Action: Shut down the other instance or mount in a compatible mode.

Find whether you have any running Oracle process.

$ ps -ef | grep smon

Kill the process then try to start again.

Since I dont have the procedure which you have followed to perform DB rename and DBID change I can not say whether there is problem with your procedure.

I would recommend to follow Oracle Knowledge Document to perform such task.
How to Change the DBID and the DBNAME by using NID

Run given command:
kill -9 ps -e | grep ora_* | awk '{print $1} ###
This will kill all processes associated with oracle, like smon, pmon, dbwriter processes.
Then start again with . oraenv & sqlplus / as sysdba


Database system vs database management system I am now taking database course in school but have worked on projects using MySQL before.

I am a bit confuse with database system and database management system. For database management system, MySQL, MS Access are some of the examples.

Can someone give me a clear explanation between the two with some examples? Most are saying they are the same.

Most are saying they are the same.

They are the same, pretty much. Most people use the two interchangeably.

You could try make a case for "database system" to cover simpler arrangements where there is just a database and not management tools (for instance in memory database libraries and/or like sqlite is often used), but there are so many grey areas here defining the difference is going to be difficult to do in a consistent manner.

Database Systems are those systems in which we can store data such as MS-ACCESS files  whereas Database Management Systems are concerned with making strategies of adding, deleting or modifying data.


Merge Replication identity field issues One of our clients is using our software with merge replication of a database on a SQL Server 2008 R2 machine. There are two production environments in separate geographical locations only one of which is live at any one time, so basically one and live one on standby. Only the live database is updated by teh applications. Every couple of months they failover between the datacentres and the standby environment become the live centre. There is an instance of SQL Server 2008 in each datacentre and merge replication is used to keep them in sync. This was all working ok until the beginning of the year when we started getting replication errors with some lTID columns in various tables that have the Identity property set.

The errors were like this one:

  The insert failed. It conflicted with an identity range check constraint in database 'GateMain', replicated table 'dbo.tGateCalcsLog', column 'lTID'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.

Then after the last failover we noticed we had an issue with the lTID values in one specific table. Our application relies on the lTID value always having incremented in order such that the highest lTID value is always the newest entry in the table. We've found that due to how the identity ranges are being managed by replication that when the system is failed over that the lTID range of the now live database server may have a range of values that are lower than those already present in the table. Is there a way to manage this in merge replication so we can guarantee that the next identity value allocated to the lTID column in greater than any lTID currently in the table? Or do we need to use a different type of replication or possibly mirroring?

You could turn off automatic identity management and allocate your ranges manually.
Another option could be forcing a range re-allocation by issuing sp_msrefresh_publisher_idrange and specifying the range boundaries.

However, both options would not guarantee that the lTID column is populated with ever-increasing values: you will always have at least one range for the publisher and one range for the subscriber and if users are inserting at both sites you can't have ever-increasing IDs.

This is by design and there's no way around it.

If your goal is keeping the identity values generated at the subscribers ever-increasing, that's a whole different story and it can be achieved with one of the above options.

Have you thought about using transaction log shipping instead of merge replication? This will provide a "constant" data flow (~15 minutes) between the two databases. It's a bit more resource intensive to the SQL pipeline, but if the systems are running with good specs, you shouldn't have an issue.


What is meant by a blind write in a schedule? I know that every conflict-serialisable schedule is view-serialisable but the converse is not true. I read in a webpage that when a schedule is view-serialisable but not conflict-serialisable then there exist some blind writes. So what is a blind write?

If there is no read that happens prior to the first write then it is said to be a blind write.

Example. Consider the following schedule:

W3(X) is a blind write, as there is no read before write [R3(X) before W3(X)]
W2(X) is not a blind write, as a read happens before write [R2(X) before W2(X)]

A blind write is a write operation e.g. W(X) by a transaction Ti after which the attribute X is not read by a transaction but some other transaction Tj performs another write operation on attribute X. Thus, the write operation by Ti becomes blind write.


Oracle 11g installation stuck at installation I'm trying to install oracle 11g on fedora by following this tutorial but it stuck after I click Next in step 8. The screen does not go away even after several hours. There's no log file in oraInventory folder.

What could be the possible problem? How can I debug it?

I've struggled the same problem and I think the problem comes from gnome failing to pop up the confirmation dialog. That's why there is nothing in the error logs.
So what I did is changing the window manager from gnome to xfce. That solved the problem.
It would probably work with another manager or there may be a fix for gnome but here is how to install xfce:

yum -y install epel-release #adding epel repository
sed -i -e "s/enabled=1/enabled=0/g" /etc/yum.repos.d/epel.repo # using it only when needed
yum --enablerepo=epel -y groups install "Xfce" #installing xfce

echo "exec /usr/bin/xfce4-session" >> ~/.xinitrc
startx

If you prefer to install oracle via vnc then skip the last two lines and change the /home/vnc-user/.vnc/xstartup ( vnc-user is oracle at my box ) like this:

#unset DBUS_SESSION_BUS_ADDRESS
#exec /etc/X11/xinit/xinitrc
/usr/bin/xfce4-session

If you don't know how to install vnc server on your CentOS 7 or Fedora box here is a guide: https://www.howtoforge.com/vnc-server-installation-on-centos-7

I just pressed left and then enter. It worked for me. This is the fix that helped me but I believe it should be fixed permanently.


SQL Server 2005 Tools makes SQL Server 2008 Express install fail When I tried installing SQL Server 2008 Express, the install failed due to this line.  How can I circumvent this, or fix the issue?

Open Run Menu and type regedit.
It will open Registry Editor
Now go to following location.

HKEY_LOCAL_MACHINE > Software > Wow6432Node > Microsoft > Microsoft SQL Server > 90

Right click on 90 and click on Delete.
Press Yes to confirm deletion.

Now try to install SQL Server 2008 and it would work this time.

If you want to keep your old installation, you can unselect Express Tools from the Feature Selection menu. Just remove all managment tools and rerun validations.


MySQL InnoDB log files on different location I am using MySQL version: 5.5.38

There are following parameters that can control InnoDB log files:

innodb_log_files_in_group - amount of log files in the group, by default 2. Will create 2 files ib_logfile0 and ib_logfile1

innodb_log_group_home_dir - location of InnoDB log files.

Is there a way to place each individual log file to it's own place?

The main idea is to increase performance and place each file to it's own HDD drive. As I have to much IO related to InnoDB log files. Too much IO to MySQL InnoDB log files

Setting the two files on two different disks may not help you, because speaking generally, they are only written serially:

  The redo log files are used in a circular fashion. This means that the redo logs are written from the beginning to end of first redo log file, then it is continued to be written into the next log file, and so on till it reaches the last redo log file. Once the last redo log file has been written, then redo logs are again written from the first redo log file.

(from the InnoDB blog).

Setting them both on a separate disk from the tablespaces is a good idea, as if your problem is lots of writes, as having exclusive IO will be an advantage on magnetic disks. They are also good candidates for SDDs because (again, if you have problems with transaction log IO) they are relatively small and usually written synchronously to disk, unlike the indexes and data.

Another way to load-balance IO is to use a RAID level with improved throughput. RAID 1 will provide you better reads and writes thanks to its striping, although the recommended setup is RAID 10 for redundancy.

One disadvantage of being on separate disks is that in most cases, you will lose the option of performing consistent MySQL datadir snapshots.

The main idea is to increase performance and place each file to it's
  own HDD drive. As I have to much IO related to InnoDB log files.

https://books.google.ru/books?id=BL0NNoFPuAQC&pg=PA291&lpg=PA291&dq=store+log+files+on+different+path+or+disks+in+mysql&source=bl&ots=CPUMvxsG2O&sig=eqGgsODsCZ1jjwM2bS6OB1JuExE&hl=en&sa=X&ved=0ahUKEwjJxeL9i7jKAhUK_XIKHRxTD4MQ6AEIOjAF#v=onepage&q=store%20log%20files%20on%20different%20path%20or%20disks%20in%20mysql&f=false

this can help you.


Where can I find good literature for beginners? I am trying to find some database tutorials,books and similar, since I have started this course in college, and received some material, but it is really hard to manage through all the info on the net. I searched through the questions on the site but didn't find any. Thankfull for all your input.

It sounds like you are looking for something on an overview level rather than a practical level.  If you are interested in learning about Oracle databases from a practical level, there is no better place to start than the Oracle Concepts Guide.

PostgreSQL is a good choice because is really open source!

The project have a great documentation, really simple and understandable.
Also, the community is very reliable and dedicated providing a great maturity to
the project.

A good and a complete option to start in database world.

If you are a kind of person that like books, I could suggest:
http://www.postgresql.org/docs/books/


Can't create user in SQL Server Hi guys when I try to create user for my database I always encounter this kind of error.

This is the sequence of task I done.

I manage to create new login using windows authentication of SQL then I use that login to access SQL Server again then I create my database and set the owner of that database to the login that I was created. After creating the database, I try to create new user and assign the login I use but I was halted by this error.

It is normal you cannot associate two different user to a single login for a database. You must understand the difference between a login and a user.

A login authenticate you on the server. Once you are authenticated, the login will impersonate a user to access the specified database of the connection string.

So a login can have only one user per database.

Sample : login [myDomain\myUser] has access on SqlServer. This login can have a user per database : userA for databaseA, userB for databaseB.

If you want to add an other user to the login for the same database, you have to drop the first user. Instead, you have to create a different login for the second user.

Hope this help you.

Right-click on SQL Server instance at root of Object Explorer,
right click and select on Properties.
Select Security tab from the left pane.
Select the SQL Server and Windows Authentication mode radio button, and
click OK.
restart the SQL Service Windows service. by (Start=> All Programs => Microsoft Sql Server 2012 =>Configuration Tool=> SQL Server Configuration Manager => SQL Server Services tab, Right click Sql Server=> Restart)
hope help you


ORA-12154: TNS:could not resolve the connect identifier specified I've initially posted this question on serverfault.com, but because lack of answers decided to post it here...

I'm trying to use bat file/CLI to run SQL script against certain Oracle database with following syntax:

sqlplus -L user_name/database_name@Password @d:\my_bat_file\sql_script.sql

If I run this following error is returned:

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

SP2-0751: Unable to connect to Oracle. Exitig SQL*Plus

At the same time tnsping database_name works fine & corresponding connection configuration strings are present in tnsnames.ora

Moreover if I type commands manually in a following way:

sqlplus /nolog
connect user_name@databasename
Passowrd

I can connect to the database too. Any idea why I'm getting error when trying to use command I mentioned in beginning & how to fix it?

P.S. I'm doing this from Windows Server 2003 SP2 box with Oracle client v10 installed.

As Munchi pointed out the issue was caused by wrong syntax. I used:

sqlplus -L user_name/tns_alias@Password @d:\my_bat_file\sql_script.sql

Instead of:

sqlplus -L user_name/Password@tns_alias @d:\my_bat_file\sql_script.sql

Try to set ORACLE_SID inside of a script, and then try with sqlplus command.

set ORACLE_SID=testdb

sqlplus -l....


How to Solve ERROR 1064 (42000)? DROP PROCEDURE IF EXISTS proc_x;
DELIMITER #
CREATE DEFINER=`root`@`localhost`  PROCEDURE proc_x(sequel CHAR)
proc_main:BEGIN
SET @SQL = CONCAT(@sequel);
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END proc_main #

Above is a syntax for creating stored procedure in my mysql database.
When I execute following statement I get error.

CALL proc_x("INSERT  INTO tbl_rec(id,name,brnachID) values(10,'Krunal',07)");

  Error : ERROR 1064 (42000) at line 93: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

Can anyone help me to solve it?

Currently I'm using Mysql on UNIX.

(sequel CHAR)
CONCAT(@sequel)

@variables are totally independent of non-@ variables.

Two fixes needed:

SET @SQL = CONCAT(@sequel); --> SET @SQL = sequel;
(sequel CHAR) --> (sequel VARCHAR(999))

Do you get error for every query or while executing this query only?

If you are facing problem executing every query,then just go to google,install XAMPP control panel and enable the MySQL button by clicking it. Then find "XAMPP->MySQL->bin" in the parted HDD where you have installed XAMPP and within XAMPP folder. Copy the path go to command prompt go to folder enter the path by "cd C:XAMPP->MySQL->bin" like this.

Actually the root@localhost is listed in XAMPP->MySQL->bin folder.

If you are facing problem with every query just uninstall MYSQL,find the latest version and install it again or update the previous one.

Hope it'll solve your problem. thanks.


How do I limit a SQL stored procedure to be run by one person at a time? I have a stored procedure that basically selects values from one table and inserts them to another, a sort of archiving. I want to avoid multiple people from doing that at the same time.

While this procedure is running, I don't want anyone else to be able to start it, however I don't want serialization, the other person to run the procedure after I am done with it.

What I want is for the other person trying to start it to get an error, while I am running the procedure.

I've tried with using sp_getapplock, however I can't manage to completely stop the person from running the procedure.

I also tried finding the procedure with sys.dm_exec_requests and blocking the procedure, while this does work, i think it's not optimal because on some servers I don't have the permissions to run sys.dm_exec_sql_text(sql_handle).

What is the best way for me to do this?

To add to @Tibor-Karaszi's answer, setting a lock timeout doesn't actually produce an error (I've submitted a PR against the docs).  sp_getapplock just returns -1, so you have to check the return value.  So like this:

create or alter procedure there_can_be_only_one
as
begin
begin transaction

  declare @rv int
  exec @rv = sp_getapplock 'only_one','exclusive','Transaction',0
  if @rv < 0
   begin
      throw 50001, 'There is already an instance of this procedure running.', 10
   end

  --do stuff
  waitfor delay '00:00:20'

commit transaction
end

I realize the 'real' problem may be more complex.

In case it isn't: if you do your archiving with insert and/or update triggers you can avoid the problem you're trying to solve.

Hope that helps,
   -Chris C.


configure new linked server from SQL Server 2016 to Oracle 12c I'm having difficulty creating a Linked Server in SQL Server 2016 to talk to a remote Oracle database.

I have installed Oracle Database Client 12c. First I installed InstantClient and then later realizing I needed more functionality installed Runtime. During the setup I accepted C:\Oracle\product\12.1.0\client_1 as the Oracle home directory. For the Oracle Home User, I used the Windows Built-in Account NT AUTHORITY\LOCAL SERVICE.

I used Oracle Net Configuration Assistant to create a tnsnames.ora file with alias = MyAlias and service name = MyServiceName. Net Configuration Assistant was able to test the database connection successfully after I provided credentials for the remote database.

The tnsnames.ora file created by Oracle Net Configuration Assistant was in this folder: C:\Oracle\product\12.1.0\client_1\network\admin

From a Powershell prompt, I ran tnsping MyAlias and got OK (200 msec).

I think (hope!) this means I got the Oracle side correct.

On the Microsoft side, I installed .NET Framework 3.5. Then I downloaded and installed Oracle Data Access Components (ODAC) from here. Specifically I downloaded ODAC122010Xcopy_x64.zip and followed the instructions in the readme file included. The command line options I used during install were:

 .\install.bat all c:\oracle odac true true

The parameters of the above commmand were:

component_name = all (install all Oracle Providers)
oracle_home_path = c:\oracle (should it be the Oracle base or the subfolder?)
oracle_home_name = odac (determines registry key path)
install_dependents = true
machine_wide_configuration = true (this parameter was a guess; I want this to work for all users of this machine)

I then added C:\oracle and C:\oracle\bin to the beginning of the Environment variable PATH. In Powershell this was:

 [Environment]::SetEnvironmentVariable("Path","C:\oracle;C:\oracle\bin;" + $env:Path, [System.EnvironmentVariableTarget]::Machine)

Within SQL Server Management Studio, I can see OraOLEDB.Oracle in the list of Linked Server Providers.

Now trying to create the Linked Server. Here's my config:

For security, I'm trying for now with a fixed security context, using the same credentials that worked when testing the connection with Oracle Net Configuration Assistant.

It doesn't work. Here's the generic error:

  The linked server has been created but failed a connection test. Do
  you want to keep the linked server?

  Cannot initialize the data source object of OLE DB provider
  "OraOLEDB.Oracle" for linked server "MYLINKEDSERVER". OLE DB provider
  "OraOLEDB.Oracle" for linked server "MYLINKEDSERVER" returned message
  "ORA-12154: TNS:could not resolve the connect identifier specified".
  (Microsoft SQL Server, Error: 7303)

What am I missing? What diagnostic tests would be valuable?

Try changing your Data Source and Provider String to MYALIAS.  Looking at the three Oracle Linked Servers I have on my server, that is how I have them setup.

at the command prompt (cmd) type

tnsping dbname

The description string you get is the connect information. The script below should create the basic connect information for a linked server to Oracle in SQL Server management studio.

EXEC master.dbo.sp_addlinkedserver @server = N'ORCL12C', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'Orcl12c', @provstr=N'((DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORCL12C)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl12c)));'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORCL12C',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORCL12C',@useself=N'False',@locallogin=N'HOME2\hughsm',@rmtuser=N'NORTHWIND',@rmtpassword=N'password'


SSMS can't connect to Azure database I got a new Windows 10 machine and installed the latest version of SQLEXPRESS and then the latest version of SSMS (17.9.1).

When trying to connect to my Azure database, I'm getting the following error.

  Error connecting to 'mydb.database.windows.net'.

  ------------------------------ ADDITIONAL INFORMATION:

  Failed to retrieve data for this request.
  (Microsoft.SqlServer.Management.Sdk.Sfc)

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  A transport-level error has occurred when receiving results from the
  server. (provider: TCP Provider, error: 0 - An existing connection was
  forcibly closed by the remote host.) (Microsoft SQL Server, Error:
  10054)

  An existing connection was forcibly closed by the remote host

What I've tried so far:

Uninstall/Reinstall - nope. I did this twice
Turn off firewall - didn't work. Besides, I can connect to the database fine through Visual Studio 2017, so it isn't a firewall issue. I also validated with netstat that port was open after I telnet-ed to the server.
Different version - I tried downgrading to SSMS version 17.9, but I get the same error. I'm in the process of bringing up 18.0 (preview 5) just to see if something will work for me. I may update here whether that works for me or not.
Different database - I have two Azure servers, I can't access either. I can connect to my local SQLEXPRESS instance without any issues.

My Google-foo has failed me on this so far. What am I missing? My current theory is that the SQLEXPRESS installation is colliding with SSMS install, but I don't know how to know that for sure - it was just something I read that used to be a problem.

Update

18 Preview 5 didn't help me. Same error :(

Others that appear to have this same issue without a resolution:

https://www.reddit.com/r/SQLServer/comments/9vfos7/issue_with_ssms_cant_connect_to_specific_server/
https://stackoverflow.com/questions/52885575/ssms-connect-to-azure-failure

Update 2

Uninstalling SQLEXPRESS and using IISCrypto to add specific TLS entries into the registry didn't help either.

Update 3 (After the answer below)

I installed SSDT and everything broke again :(. However, I did find that I can connect a single query in SSMS with no problem, but I cannot connect to a Server in the Object Explorer. Go figure.

I'll update here if there is something else I discover.

Update: I found that connecting to AWS failed too which led me to yet another Google search which led me here: https://www.sqlservercentral.com/Forums/1891503/Unable-to-connect-to-SQL-2008-Servers-from-SSMS-2017

Following the instructions from the last post in the referenced site, I downloaded IISCrypto and enabled all of the Ciphers. Bingo.

https://www.nartac.com/Products/IISCrypto/Download

I'll leave my previous answer below in case it brings comfort to anyone who went through sleepless nights and a couple of keyboards.

Old Update - After installing SSDT 15.8.2 the solution below stopped working for me :(

After days of:

MS Azure SQL Support - not helpful (they just referred me to this post. The one I made.)
Uninstalling SQL Server Express/Developer and three different versions of SSMS - all with the same results
Creating a VM on my computer with Win 10 Pro - SSMS worked on it no problem
Wiping the computer and starting from absolute nothing (using the MSI recovery) - still same issue
Inspecting TLS packets to ensure version 1.2 - it was 1.2
Trying to connect with SqlPackage.exe - never worked for me, same error as SSMS
Trying to connect with SQLCMD - it worked!
Trying to connect with SQL Native Client - also worked!

... I did finally get it to work. Good news is that it works. Bad news is that I don't know exactly why.

I currently have a fresh Win 10 Pro install with SSMS 17.9.1. I do not have any SQL Server instance installed. My default Windows Firewall is enabled and Norton AntiVirus is managing it (default with the computer). All Windows Updates have been applied as of this writing. I also downloaded and installed the SQL Server 2012 Native Client https://www.microsoft.com/en-us/download/confirmation.aspx?id=50402

When it started working, I had just used SQLCMD and connected to the Azure SQL Server master database and was querying the sys.event_log table. Immediately before that, I had used SQLCMD to connect to a different database on that same server. When I didn't see anything in the sys.event_log table I went back to SSMS and it worked for all of my servers (we have three).

TL;DR Connect with SQLCMD for a while and see if that helps??

After installing the "Docker Desktop on Windows", I get the same problem. Uninstalling the Docker Desktop on Windows, give me the relief.


ORACLE error while dbstart command I'm trying to start up Oracle DB with startup command.
But error occurred while startup.

dbstart[80]: test: argument expected
Failed to auto-start Oracle Net Listene using /bin/tnslsnr
touch: $ORACLE_HOME/startup.log cannot create
chmod: $ORACLE_HOME/startup.log: No such file or directory
Processing Database instance "$ORACLE_SID": log file $ORACLE_HOME/startup.log
No such file or directory
dbstart[350]: $ORACLE_HOME/startup.log: cannot create

How can I fix it?  Oracle Database 10g Release 10.2.0.1.0 - 64bit Production is running under AIX.

I have faced the similar issue. The reason, why it has happened was the invalid entry in the /etc/oratab.

As dbstart script suggests, to configure, update /etc/oratab with instances that need to be started up. The entries are of the form:

$ORACLE_SID:$ORACLE_HOME:<N|Y|W>:

An example entry:

main:/usr/lib/oracle/emagent_10g:Y

I see you are using a dbstart script to start your database.

In your dbstart script, change the value of ORACLE_HOME_LISTENER:

ORACLE_HOME_LISTNER=$ORACLE_HOME;


What MySQL storage engine should I choose? I have a database for my catalog webpage. Some tables are about 3GB with millions of rows.

What engine should I choose for my database, InnoDB or MyISAM?

There will only be about 1,000 new records a week, and about 1,000 deletes, but I will modify some data in one table quite often (price, quantity, etc.).  All other tables will be "sealed".

I need indexes to enable fast queries. Which storage engine is best for this scenario?

My recommendations are to start as follows:

Go for the latest version of the MySQL Server that you can get, there have been some pretty impressive work done on 5.6 although the production ready version is 5.5
Go for InnoDB - comes as the default for the higher MySQL versions
Configure InnoDB as follows:

file_per_table - creates an data file for each table instead of putting all tables in a single data files
innodb_buffer_pool_size - 80% of available RAM (if a dedicated machine) so that as much of your database is in memory

Table column characteristics:

use the smallest column size needed for the data
use numeric foreign keys
index the columns used in the searches, and setup the indexes to cover the most common searches, for example if you search on columns 4, then 6 alot create a multi-column index with column 4 first followed by 6

Remember these are just starting points you can fine tune specifics as you move along, but before you fine tune, measure tune then measure again

pretty sure that's a no since that would be a pretty expensive op if you have a ton of rows and the optimizer is trying to make things fast..so the fastest info it has would come from the table definition. That might have changed post 5.1 FYI


DB2 ROLLFORWARD option "TO END OF LOGS" - does it read archive logs? I read about ROLLFORWARD option on link http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001978.html?cp=SSEPGG_9.7.0%2F3-6-2-4-106 but I could not find answer to my issue.

I have database in Archive mode.

I am using DISK option in LOGARCHMETH1

I made full ONLINE backup at Monday at 23:59

I have archive logs for Tuesday, Wednesday and Thursday.
Let's say just for example one for each day: S0000001.LOG and S0000002.LOG and S0000003.LOG.

In Friday I have active log S0000004.LOG.

Let's say just for testing purpose I want to do the restore of Online backup made at Monday and all transactions committed during  Tuesday, Wednesday,Thursday and Friday. So I want to restore everything from Monday to Friday

I thought by reading all manuals that I just need to perform after restore ROLLFORWARD TO END OF LOGS

db2 rollforward db maxdb75 to end of logs and complete;

I thought that this command to end of logs it will include Archive Logs(S0000001.LOG + S0000002.LOG + S0000003.LOG.)+Active Logs(S0000004.LOG)

But one good database expert told me that: "When rolling forward to end of logs without specifying overflow log path only active logs will be read"

So I have 2 questions:

What in my example will be restored in case of "to end of logs" command
Is this the command if after the restore I want to rollforward everything from Monday To Friday in my exampe: db2 rollforward db maxdb75 to end of logs and complete overflow log path "(/data/archiveLogsPath)";

Thank you in advance!!!!

Whoever told you "When rolling forward to end of logs without specifying overflow log path only active logs will be read" was wrong.

When you have LOGARCHMETH1 set to DISK, TSM, USEREXIT or VENDOR, the DB2 log manager EDU (db2logmgr) is responsible for moving log files to and from the archive location.  When performing a ROLLFORWARD operation, DB2 will determine which log files it needs, and db2logmgr will retrieve files for processing if they aren't already in the active log path.

Now, if you have LOGARCHMETH1 set to LOGRETAIN, and you are manually moving log files from the active log path to somewhere else, only then will you have to specify OVERFLOW LOG PATH (or move/copy the transaction log files back to the active log path).

So: to end of logs means just what it sounds like:  DB2 will replay all transactions present in log files between the backup time and when the restore took place.

Yes, Ian is correct here. I am sure your issue is resolve now. Also, archive logs can still exist in Active log path if we set LOGARCHMETH1 to LOGRETAIN  correctly . Details at Information Center can give better overview .Hope it helps !


Why can't I view the SQL Server database created by a web application? I have an ASP.NET MVC web application deployed to IIS on a Windows Server 2012 machine.  When the application first starts, it will create the database and tables it needs if necessary through Entity Framework.  This all works fine, and now I want to do some administration to the database through SQL Server Management Studio.  However, when I open up that tool I only see the System Databases.  If I remove "User Instance=true" from the connection string and let the application create the database again, I can the see the database.

Why couldn't I view the database created by the web application when User Instance=true was in the connection string?  If I am viewing the server instance through Management Studio with an account that has all roles, I would expect it to be able to see any and all databases.

Some details that may help:

The application uses the following connection string in Web.config:

<add name="DefaultConnection" connectionString="Data Source=.\SqlExpress;
  Initial Catalog=Foo.Dashboard;  Integrated Security=SSPI;User Instance=true"
  providerName="System.Data.SqlClient" />

In IIS, the application pool is set to run with the identity LocalService and the Load User Profile setting is True.  On SQL Server Express, we added a login for the Local Service account and gave it the dbcreator role.  When the application started up, it was then able to create the database and tables, and everything appears to be functioning.

I then connect to the server with remote desktop and open SQL Server Management Studio. To connect to the database, I enter .\SQLEXPRESS as the server name and authenticate with Windows Authentication. I then connect and only see the system databases. I’ve added all the possible database roles to my login, and I see that I have the VIEW ANY DATABASE permission in the effective tab.

Please stop using the user instance=true setting. Not only has this feature been deprecated, but what is actually happening here is that Management Studio is firing up a different user instance of SQL Server than the one your application is using, so it is not surprising that you can't see the same thing in both cases.

(Usually, the symptom of this is that someone runs an update from their app, then checks the table in Management Studio, thinking there is a bug but not realizing that the update affected a different copy of the database.)

Create the database on your running SQL Server instance (or attach it there). Then connect to that server the same way, without the User Instance setting, from both SSMS and your app. Lots more information here. Don't create the database every time the application starts - that just sounds horrendously wrong.

If you are logged on to the server can you see how many sql services there are in the services windows.
Perhaps there are more than one instance.


How to Get the Second Sequence of Characters using Regular Expressions I have a field with an address in it (ex. 68 TIDAL BREEZE DR) and this regular expression gets everything before the last sequence (ex. 68 TIDAL BREEZE):

substring (address from '(.*) ')

My question is, how do I modify this expression to get everything after the first sequence (ex. 68) and everything before the last sequence (ex. DR) like so: TIDAL BREEZE?

I'm using PostgreSQL 9.5.

postgres=# select substring('68 TIDAL BREEZE DR' from '\s+(.*)\s');
  substring
--------------
 TIDAL BREEZE
(1 row)

Lazy match the first whitespace character to chop the first bit off.

Not using a regex

I highly suggest you don't parse addresses with a regex. If you're looking for a better practice, there is a method of parsing addresses demonstrated and explained in this answer

SELECT * FROM standardize_address(
  'us_lex','us_gaz','us_rules',
  '68 TIDAL BREEZE DR, Citytown Texas, 77346'
);
 building | house_num | predir | qual | pretype |     name     | suftype | sufdir | ruralroute | extra |   city   | state | country | postcode | box | unit
----------+-----------+--------+------+---------+--------------+---------+--------+------------+-------+----------+-------+---------+----------+-----+------
          | 68        |        |      |         | TIDAL BREEZE | DRIVE   |        |            |       | CITYTOWN | TEXAS | USA     | 77346    |     |
(1 row)

Or to get just certain values

SELECT house_num || ' Other Stuff ' || suftype AS whatever
FROM standardize_address('us_lex', 'us_gaz', 'us_rules',
  '68 TIDAL BREEZE DR, Citytown Texas, 77346'
);
       whatever
----------------------
 68 Other Stuff DRIVE
(1 row)


SQL Server Indexed View and TOP I'm struggling to persuade a query plan to behave as I think it should. The addition of a TOP clause when querying an indexed view is causing a sub-optimal plan, and I'm hoping for some help in sorting it.
Environment

SQL Server 2019
StackOverflow2013 database (50GB version), Compat Mode 150 (problem is not specific to this version)

The setup:
Firstly, I've created a view to return everyone with a high reputation:
CREATE VIEW vwHighReputation
WITH SCHEMABINDING
AS
SELECT  [Id],
        [DisplayName],
        [Reputation]
FROM    [dbo].[Users]
WHERE   [Reputation] > 10000

Next, since I'll be searching by display name, I've created a couple of indexes on the view:
CREATE UNIQUE CLUSTERED INDEX IX_Users_Id ON [dbo].[vwHighReputation]([Id])
GO
CREATE NONCLUSTERED INDEX IX_Users_DisplayName ON [dbo].[vwHighReputation]([DisplayName]) INCLUDE (Reputation)
GO

If I query via the view, I can see my nonclustered index is being used:
SELECT  *
FROM    [dbo].[vwHighReputation]
WHERE   [DisplayName] LIKE 'J%'

Plan: (https://www.brentozar.com/pastetheplan/?id=Sy2EoJaiv)
So far so good. I can even use my view as part of a more complex query with an OUTER APPLY, and I still get a seek with only 63 reads against my index (this is obviously a contrived example, but helps illustrate the problem that I'll come to):
SELECT  [U].[Id],
        [A].[Reputation],
        [A].[DisplayName]
FROM    [dbo].[Users] AS [U]
        OUTER APPLY (
                        SELECT  *
                        FROM    [dbo].[vwHighReputation] AS [v]
                         WHERE   [v].[Id] = [U].[Id]
                    ) AS [A]
WHERE   [A].[DisplayName] LIKE 'J%';

Plan: https://www.brentozar.com/pastetheplan/?id=HJaw3y6ov
However, if I add a TOP 1 to my OUTER APPLY:
SELECT  [U].[Id],
        [A].[Reputation],
        [A].[DisplayName]
FROM    [dbo].[Users] AS [U]
        OUTER APPLY (
                        SELECT  TOP 1 *
                        FROM    [dbo].[vwHighReputation] AS [v]
                        WHERE   [v].[Id] = [U].[Id]
                    ) AS [A]
WHERE   [A].[DisplayName] LIKE 'J%';

Then the situation gets bad....very, very bad....
Plan: https://www.brentozar.com/pastetheplan/?id=HyOS6yaiw
My logical read count against that view is now almost 5 million. I can see from the plan that SQL Server is now choosing to perform a seek on the clustered index with the User's ID as the predicate, but doing so around 2.5 million times. It is also scanning the whole of the Users table. It no longer seeks on the view's index.
Obviously the optimiser is deciding that this is the most efficient approach, but I can't understand why! I've think it's probably to do with the way the underlying tables are sorted, but I'm not sure.
Incidentally, re-writing it as a simple SUB QUERY rather than CROSS APPLY yeilds the same outcome.
Any help or advice would be great!

Outer Apply
You're using OUTER APPLY, but with a where clause that would reject NULL values.
It's converted to an inner join without the TOP (1):
SELECT
    U.Id,
    A.Reputation,
    A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
    SELECT
        v.*
    FROM dbo.vwHighReputation AS v
    WHERE v.Id = U.Id
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;

I've formatted your code a little bit, and added an ORDER BY to validate results across queries. No offense.
Outer Apply + TOP (1)
When you use the TOP (1), the join is of the LEFT OUTER variety:
SELECT
    U.Id,
    A.Reputation,
    A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
    SELECT TOP (1)
        v.*
    FROM dbo.vwHighReputation AS v
    WHERE v.Id = U.Id
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;

The TOP (1) inside the OUTER APPLY apparently makes the optimizer unable to apply the same transformation to an inner join, even with a redundant predicate:
SELECT
    U.Id,
    A.Reputation,
    A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
    SELECT TOP (1)
        v.*
    FROM dbo.vwHighReputation AS v
    WHERE v.Id = U.Id
    AND   v.DisplayName LIKE 'J%'
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;

Note the residual predicates to evaluate if the Id and DisplayName columns are NULL.
This isn't just a TOP (1) issue either -- you can substitute any values up to the big int max (9223372036854775807) and see the same plan.
It will also happen if you skip the view entirely.
SELECT
    U.Id,
    A.Reputation,
    A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
    SELECT TOP (1)
        v.Id,
        v.DisplayName,
        v.Reputation
    FROM dbo.Users AS v
    WHERE v.Reputation > 10000
    AND   v.Id = U.Id
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id
OPTION(EXPAND VIEWS);

A Rewrite
One way to get the same effect as TOP (1) without the various optimizer side effects of TOP is to use ROW_NUMBER
SELECT
    U.Id,
    A.Reputation,
    A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
    SELECT
        v.*
    FROM
    (
        SELECT
            v.*,
            ROW_NUMBER() OVER
            (
                PARTITION BY
                    v.Id
                ORDER BY
                    v.Id
            ) AS n
        FROM dbo.vwHighReputation AS v
    ) AS v
    WHERE v.Id = U.Id
    AND   v.n = 1
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;

Which will get you the original plan:

For all intents and purposes, because your indexed view uses the same underlying table that you're re-joining to on the unique clustered index, and because you don't use an ORDER BY with your TOP clause (so your top result is always going to be random and never guaranteed), I believe this query should be logically equivalent to your problematic one:
WITH CTE_TopHighReputation AS
(
    SELECT  TOP 1 *
    FROM    [dbo].[vwHighReputation] AS [v]
    WHERE   DisplayName LIKE 'J%'
)

SELECT  [U].[Id],
        [A].[Reputation],
        [A].[DisplayName]
FROM    [dbo].[Users] AS [U]
LEFT JOIN CTE_TopHighReputation AS [A]
  ON [U].Id = [A].Id
WHERE   [A].[DisplayName] LIKE 'J%';

Without you testing it, I couldn't say for sure if it's better performance wise, but it's possible. (I could be misremembering since I don't use OUTER APPLY much, especially when a LEFT JOIN alternative is possible, but I'm pretty sure this is logically equivalent to your problematic query.)


SQL Server 2012 setup fails immediately (Error result: -2067529723) I am trying to install SQL Server Enterprise Core 2012 w SP1 64bit (as downloaded from the Microsoft Partner Program site) on a 64-bit Windows 2008 R2 SP1 server. When simply right-clicking on SETUP.EXE in the root and selecting Run as Administrator, seemingly nothing happens. When I run it from a Command Prompt (Run as Administrator), I get this:

  An error occurred during the SQL Server 2012 Setup operation.

  Error result: -2067529723
  Result facility code: 1220
  Result error code: 5

  For more information, review SQL Server 2012 Setup logs in your temp
  folder.

No log files were written anywhere. How can I find out what this error is trying to tell me and what I can do to resolve it?

I rebooted the VM, and I was then able to get the Setup to work as expected. I am disappointed that the error message did not provide any indication of what was wrong or what needed to be done to correct the situation, but rebooting seems to have resolved whatever the issue was.

I was facing exactly same error with error code Error result: -2068643839.
It was occurring when I was trying to install SQL Server 2017 Enterprise Edition with mounting ISO file on my Windows 10 machine.
I googled and found some other issues getting resolved when installing by extracting ISO file instead of installing it by mounting the ISO. The error went away.


SQL query that concatenates values from duplicate rows in a single table Let's say I have a table of employees, like this:

Name     Sex     Role

Bob      M       Developer
Joe      M       QA

Now, I have a problem with duplicated rows in this table. I will fix it sometime but it's not the issue. What I currently need is something that queries such a table:

Name     Sex     Role

Bob      M       Developer
Bob      M       Janitor
Joe      M       QA
Joe      M       CEO

And will output the following rows:

Name     Role

Bob      Developer, Janitor
Joe      QA, CEO

It doesn't matter to me if the "Role" will be split to separate columns or be a single column with multiple values.

Using SQL Server 2008 if it matters.

Group_concat as in MySQL is not available in SQL Server up to SQL Server 2016. vNext will introduce STRING_AGG however that provides equivalent functionality.

You have some manual options though:

build a scalar function that takes an employee name/id as parameter and shows concatenated role values and apply this function to each employee
use xml functions
use an already built aggregate (similar as first option)

A solution for your current case (just took Brad's answer from SO and customized for your table) would be:

SELECT name, LEFT(roles , LEN(roles )-1) AS roles
FROM employee AS extern
CROSS APPLY
(
    SELECT role + ','
    FROM employee AS intern
    WHERE extern.name = intern.name
    FOR XML PATH('')
) pre_trimmed (roles)
GROUP BY name, roles;

These details are gathered from different sources:

Simulating group_concat MySQL function in MS SQL Server 2005? - on StackOverflow.com
Emulating MySQL’s GROUP_CONCAT() Function in SQL Server 2005 - blog
GROUP_CONCAT in SQL Server - blog
T-SQL Equivalent For Group_Concat() Function - MSDN

I would try it with the following, but please be aware, that this can run quite a time on very big tables.

select a.name, a.role, b.role from employees a, employees b where a.name=b.name and a.role<>b.role

I called the table "employees" and the columns "name" and "role".


query optimization of a left join I have the following query written into the slow log (mysql-slow.log).

I don't have much idea how to optimize this query. I already have indexes for:

mode,
f.user_id... etc

but doesn't seem to be helping me. Mind you, the table Engine is InnoDb.

# Query_time: 10.830652
    Lock_time: 0.000058
    Rows_sent: 1
    Rows_examined: 18194
    Rows_affected: 0
# Bytes_sent: 75
SET timestamp=1439531119;
SELECT
  COUNT(u.user_id)
FROM (
    users AS u
    LEFT JOIN photos AS f
        ON u.user_id=f.user_id
        AND f.main='Y')
WHERE  u.mode NOT IN('suspended','deleted','hidden')
AND u.activated='Y';

Often times slow queries are caused by a lack of index on a column that is being used in a where or join clause.

Try indexing all of the following:

u.user_id
f.user_id
f.main
u.mode
u.activated

You could also try moving f.main='Y' to the WHERE condition - I believe it will result in the same outcome.


Design of an application log database We are generating logs like following example(this is a table no actual pipes):

2014-06-10 09:00:03.457 | Channel1 | Operation3 | Function15 | 15ms
2014-06-10 09:00:08.245 | Channel2 | Operation5 | Function10 | 22ms
2014-06-10 09:00:22.005 | Channel1 | Operation3 | Function15 | 48ms

Think about this with at least 25-30 columns of time-series application log data. Every row is a transaction. So I aggregate the same ones and get the sum(also average of the durations). After the aggregation, my unique clustured key is all of the columns except the metrics like transaction duration or sum.

In example between 2014-06-10 09:00:00 - 2014-06-10 09:01:00 our example will be:

2014-06-10 09:00:00 | Channel1 | Operation3 | Function15 | 2 | 31,5ms
2014-06-10 09:00:00 | Channel2 | Operation5 | Function10 | 1 | 22ms

Is there a better way to do this? Processing this data also costs me a lot while showing it to users for monitoring and analyzing purposes.

UPDATE-1

I think this question need more clarification. The raw log data is like in the first example. I'm running an ETL agent that gets it as per minute interval and aggregates to an another table like in the second example.

The second table has a primary key of all columns except metric columns(Count,ResponseTime). Because in the end 2014-06-10 09:00:00 | Channel1 | Operation3 | Function15 is the only thing that gives me uniqueness.

When the user wants to analyze it. He/She chooses values from every column which I named "Dimensions". He/She wants to see "Function15 transactions which are on Channel1" or "Operation5 response times on Channel1" and so on. I'm storing the data like this to achieve these requests from the user.

Regards

I did the following

CREATE TABLE L(
Time_Series_TS TIMESTAMP,
Channel VARCHAR(10),
Operation VARCHAR(10),
Function VARCHAR(10),
Duration INT);

Then

INSERT INTO L VALUES('2014-06-10 09:00:03.457', 'Channel1', 'Operation3', 'Function15', 15);
INSERT INTO L VALUES('2014-06-10 09:00:08.245', 'Channel2', 'Operation5', 'Function10', 22);
INSERT INTO L VALUES('2014-06-10 09:00:22.005', 'Channel1', 'Operation3', 'Function15', 48);
INSERT INTO L VALUES('2014-06-10 09:01:03.457', 'Channel2', 'Operation3', 'Function15', 296);
INSERT INTO L VALUES('2014-06-10 09:01:08.245', 'Channel2', 'Operation5', 'Function10', 225);
INSERT INTO L VALUES('2014-06-10 09:01:22.005', 'Channel1', 'Operation3', 'Function15', 7);
INSERT INTO L VALUES('2014-06-10 09:01:16.245', 'Channel2', 'Operation5', 'Function10', 10);
INSERT INTO L VALUES('2014-06-10 09:01:47.005', 'Channel1', 'Operation3', 'Function15', 20);

I added a few records to your sample for checking. Then ran this query

SELECT MINUTE(Time_Series_TS) AS Minute, Channel, Operation, Function,
COUNT(*) AS "Count/min", SUM(Duration) AS Duration
FROM L
GROUP BY Minute, Channel, Operation, Function
ORDER By Minute, Channel, Operation, Function;

Which gave

+--------+----------+------------+------------+-----------+----------+
| Minute | Channel  | Operation  | Function   | Count/min | Duration |
+--------+----------+------------+------------+-----------+----------+
|      0 | Channel1 | Operation3 | Function15 |         2 |       63 |
|      0 | Channel2 | Operation5 | Function10 |         1 |       22 |
|      1 | Channel1 | Operation3 | Function15 |         2 |       27 |
|      1 | Channel2 | Operation3 | Function15 |         1 |      296 |
|      1 | Channel2 | Operation5 | Function10 |         2 |      235 |
+--------+----------+------------+------------+-----------+----------+

Which appears to be the result you want (note 63 as the 1st duration as per my earlier comment). Is this the result you wanted? You can then use HOUR() and DAYOFMONTH() and even YEAR() to aggregate over these also with this query.

For performance, I did create an index

CREATE INDEX L_Index ON L(Channel, Operation, Function) using BTREE;

and explained the query before and after creating it, but there was no difference. This is hardly a surprise, since the optimizer probably said that there's no point in using one for such a small table. Obviously, I can't test
with your data, but there are a couple of points. If you are performing this operation over a large number of records with a large no. of fields, you may run into issues and if you create many indexes, your insert performance will decrease. Is it possible for you to categorise your data in some way to reduce the number of fields - i.e. split your big table into ones with a smaller number of fields? Check out different scenarios, test and see what happens with your data, your queries, your application and your hardware.

[EDIT]

For something more human readable, you might like to try something like

SELECT TIME(FROM_UNIXTIME(UNIX_TIMESTAMP(Time_Series_TS) - MOD(UNIX_TIMESTAMP(Time_Series_TS), 60))) AS Minute,
..
..

for your first field.

[EDIT - Response to UPDATE-1]

OK - so in my schema, you are indexing by (Minute, Channel, Operation, Function)?
See here for the docco on composite indexes in MySQL. If your queries have a predominatly left-right orientation, i.e you [always | usually] query Channel first and then Operation, then Function, you could try an index on Minute + (the usual three). If it's fairly arbitrary, then you could try using 6 indexes, but this will hit insert performance. How much, I can't say, but if this is a DW type app which performs the analysis, you can batch the inserts and only occasionally take the hit for that. You'll have to do a few tests with realistic data and EXPLAIN your queries - with realistic sample data, as I said earlier, the Optimiser with just a few records ignores indexes because the table is too small. Interestingly, on the MySQL man page given above, there's a hashing strategy which looks interesting - take MD5 hashes of CONCAT(Your_Column_List_Here). One other thing that I can suggest is that instead of using the

SELECT TIME(FROM_UNIXTIME(UNIX_TIMESTAMP(Time_Series_TS) - MOD(UNIX_TIMESTAMP(Time_Series_TS), 60))) AS Minute,...

Just remove the TIME() function and then you'll be storing INTs which appears to be better than indexes on DATETIMES - see here for a benchmark. Also as previously mentioned, you should remove your data from Production and perform the  OLAP/DW on another machine. You could also test out the InfiniDB solution that I suggested. It's drop-in compatible with MySQL (no learning curve). Then there are all the NoSQL solutions - we could be here all day :-). Take a look at a few scenarios, evaluate and test and then choose what best fits your budget and requirements. Forgot: Make your OLAP/DW system read only for performing queries - no transactional overhead! Make the OLAP/DW tables MyISAM? This last one is controversial - again, test and see.

Try discretize your data. You can do this by using staging tables. The result will be like this:

time                | channel  | operation  | function   | timecost | count
2014-06-10 09:00:00 | Channel1 | Operation3 | Function15 | >30ms    | 1
2014-06-10 09:00:00 | Channel2 | Operation3 | Function15 | 0-5ms    | 1
2014-06-10 09:00:00 | Channel1 | Operation3 | Function15 | >30ms    | 1


Same time even after deleting the row I have a table which has 1 million record.
When I run

Select count (*) from accounts;

It took around 2 min.
But when I deleted 300 thousands record and run the same query it is still taking same time.
I read in Google many things. Should I do enable row movement and shrink space or
 should I gather the statistics. Which is helpful or both are necessary.

You raise a good and important point. I think it is crucial to understand what happens when data is deleted. I've seen many applications, where this was not thought about until the storage was full or slow or the data protection people came round and complained.

If you DELETE rows in Oracle, the space is marked as free, but left as "holes" in the storage. If you INSERT again afterwards, these holes might be filled if the new data fits in.

Some but not by far not all queries are sped up by indexes. Those that are not faster need then to read all data, including holes (so called full table scan). Reading an 8K block from disk that is full of data takes the same amount of data than reading an completely empty block which contains only "holes". Your time measurement points to this scenario.

Regarding statistics: Oracle updates the optimizer statistics at the nightly maintenance window (usually 10pm/22:00). Personally, I'd certainly gather stats after deleting a third of a large table, especially if this is a regular job, for instance in a procedure.

Regarding row movement: I leave it always on for most tables, but I'd judge it as not so important. It hurts only code that stores rowids in tables or longer time in memory and we don't do that. It is necessary if you do a ALTER TABLE xxx MOVE.

Regarding shrink: I wouldn't be bothered to shrink or reorganize for 300k rows. For 10GB+, it may be worth the effort.

Make sure you have a clustered index on that table: ideally just add an INT identity field:

ALTER TABLE [accounts]
ADD [ID] INT IDENTITY(1,1) PRIMARY KEY

The PRIMARY KEY will automatically created a clustered index, and those counts should come back much faster.


How to change the replication State Transfer SST to IST in Galera Config I would like to migrate 700GB data to galera cluster(2node+1arbitrator). I found that IST is the best method for huge DBs.So how to forecfully change the replication from SST to IST in Galera Config.
Configured sst method:
my.cnf
wsrep_sst_method=rsync

This is not the whole truth. It is not about SST vs. IST it is about SST followed by an IST.

SST is a full sync, IST is an incremental sync between 2 nodes.

Sometimes you have to use an SST. For example, when you sync your second Galera node with the first one the first time. Later on, when both nodes have the full data set and you take out one node and bring it in again, Galera will choose if IST is possible and if not choose SST.

You can force an SST in favour of IST but not the other way around.


Selecting the first purchase item of each customer I have a table like this:

CREATE TABLE SALES
(
CID NUMBER(4),
PDATE DATE,
ITEMCODE NUMBER(4),
ITEMNAME VARCHAR2(20)
);

INSERT INTO SALES VALUES (1111,'15-JAN-2011',4001,'LUX');
INSERT INTO SALES VALUES (1111,'17-JAN-2011',4002,'DRY FRUIT');
INSERT INTO SALES VALUES (1111,'20-JAN-2011',4003,'ARIEL');
INSERT INTO SALES VALUES (1111,'03-JAN-2011',4003,'ARIEL');
INSERT INTO SALES VALUES (1111,'04-JAN-2011',4010,'TELENOR CARD');

INSERT INTO SALES VALUES (2222,'05-FEB-2011',4005,'APPLE');
INSERT INTO SALES VALUES (2222,'06-FEB-2011',4001,'LUX');
INSERT INTO SALES VALUES (2222,'09-FEB-2011',4006,'ORS');

INSERT INTO SALES VALUES (3333,'03-FEB-2009',4007,'CHOCOLATE');
INSERT INTO SALES VALUES (3333,'09-MAR-2011',4001,'LUX');
INSERT INTO SALES VALUES (3333,'15-APR-2011',4008,'MILK');
INSERT INTO SALES VALUES (3333,'03-JAN-2011',4010,'TELENOR CARD');

INSERT INTO SALES VALUES (4444,'11-JAN-2011',4009,'TAPAL MIXTURE');
INSERT INTO SALES VALUES (4444,'19-JAN-2011',4010,'SUGAR');
INSERT INTO SALES VALUES (4444,'21-JAN-2011',4001,'LUX');
INSERT INTO SALES VALUES (4444,'05-JAN-2011',4005,'APPLE');
INSERT INTO SALES VALUES (4444,'15-JAN-2011',4001,'LUX');

INSERT INTO SALES VALUES (5555,'01-JAN-2011',4009,'TAPAL MIXTURE');
INSERT INTO SALES VALUES (5555,'03-JAN-2011',4010,'TELENOR CARD');
INSERT INTO SALES VALUES (5555,'22-JAN-2011',4005,'APPLE');

I need to fetch the first purchased item name by all the id individually.

How can I do this?

select cid,
       pdate,
       itemcode,
       itemname
from (
  select sales.*,
         rank() over (partition by cid order by pdate) as rnk
  from sales
) t
where rnk = 1
order by cid;

Alternatively

select s1.cid,
       s1.pdate,
       s1.itemcode,
       s1.itemname
from sales s1
where pdate = (select min(s2.pdate)
               from sales s2
               where s1.cid = s2.cid)
order by cid;

The following query will do this for you. What it does is create a Rank partitioned by each customer and ordered by Pdate

SELECT CID,PDATE,ITEMCODE,ITEMNAME
FROM sales
ORDER BY CID,PDATE
QUALIFY RANK() OVER (PARTITION BY CID ORDER BY PDATE) = 1


Restrict access to rows by usename In my database i store data of different users (e.g. addresses or invoices) in the corresponding tables. I want to make sure that a logged in user only has access to it's own data in the tables (so that the user cannot read e.g. an address of an other user).

Currently this is done in the application accessing the mysql server.

Because the application will be split into multiple independent parts, written in different languages, I'm looking for a solution that is closer to the database, otherwise i need to make sure that the access rules are equal in every application.

While I also have the alternative of a middleware on my list, I'm looking for a in database solution in the first place.

Currently I already  have a structure running in a test environment (It is a shorted version for illustrating). But I would like to know if there is a better solution to achieve this.

I already know that that a VIEW with the algorithm MERGE is limited in what can be in the query and which JOINs can be done in the VIEW to keep the VIEW in a state that INSERT and UPDATE queries are still available.

Tables

CREATE TABLE IF NOT EXISTS `User` (
  `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mysqluser` varchar(120) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `_Address` (
  `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `owner` int(10) unsigned NOT NULL,
  PRIMARY KEY (`_id`),
  KEY `owner` (`owner`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

CREATE TABLE IF NOT EXISTS `_Invoice` (
  `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `owner` int(10) unsigned NOT NULL,
  `address` int(10) unsigned NOT NULL,
  PRIMARY KEY (`_id`),
  KEY `owner` (`owner`),
  KEY `address` (`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Views

CREATE ALGORITHM=MERGE DEFINER=`cm_root`@`localhost` SQL SECURITY DEFINER VIEW `Address` AS
SELECT `a`.`_id` AS `_id`
     FROM `_Address` `a` JOIN `User` `u` ON (`a`.`owner` = `u`.`_id`)
     WHERE (`u`.`mysqluser` = substring_index(user(),'@',1));

CREATE ALGORITHM=MERGE DEFINER=`cm_root`@`localhost` SQL SECURITY DEFINER VIEW `Invoice` AS
SELECT `a`.`_id` AS `_id`,`a`.`address` AS `address`
    FROM `_Invoice` `a` JOIN `User` `u` ON (`a`.`owner` = `u`.`_id`)
    WHERE (`u`.`mysqluser` = substring_index(user(),'@',1));

Constraints

ALTER TABLE `_Address`
  ADD CONSTRAINT `_owner_address_fk` FOREIGN KEY (`owner`) REFERENCES `User` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `_Invoice`
  ADD CONSTRAINT `_owner_invoice_fk` FOREIGN KEY (`owner`) REFERENCES `User` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `_address_fk` FOREIGN KEY (`address`) REFERENCES `_Address` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE;

MySQL views are a terrible solution to anything because they are VEERRYYY slow and also cause lots of issues during database backup and restores (due to @DEFINER keyword).

You store the id of the current user in the application session session and always use it as a filter for any queries that are executed since its a foreign key in each table.


Oracle RMAN Back-up files in red hat can be read in windows? We have performed a cold back-up of a database using Oracle RMAN. The back-up ran on a Red Hat Server and then the files were copied to a mounted external hard-drive. Can this back-up files then be read in a windows PC or there will be some file system compatibility issue? When restoring the back-up with RMAN, does the hdd needs to be mounted in a linux server or it can be any server?

RMAN can be used for cross-platform tasks but you cannot restore your classic full backup from Linux to Windows

You can read the files with a hex editor, but you can't restore Oracle data files backed up from a Linux server to a Windows server. The endian is different. Depending on how your database is setup you can use data pump with transportable table spaces to migrate an Oracle database from Linux to Windows.

Transporting Tablespaces Between Databases


How to combine UNION and INNER JOIN? I'm fetching the grandparent of all nodes in a tree as follows:

SELECT P.id, P.parent, GP.parent gp_id FROM product_groups P
INNER JOIN product_groups GP ON P.parent = GP.id

There is no entry with id=0 in the table, but some where parent=0, indicating roots of the trees. Those rows are not fetched by this query, as there's no grandparent to join them with. I'd like those rows returned, with 0 (or NULL) for both parent and gp_id.

I was planning to UNION the second table with (0,0), but can't figure out the syntax. How to do this?

Just do a LEFT JOIN instead:

SELECT P.id, P.parent, GP.parent gp_id FROM product_groups P
LEFT JOIN product_groups GP ON P.parent = GP.id

That will return all rows from P and any non-matching rows in GP will have NULL values.

This will return all the lines of the two tables. You can change the aliases so that you get the column name you want.

SELECT t1.col1 as name, t1.col2, t1.col3
FROM t1

UNION ALL

SELECT t2.cola as name, t2.colb, null
FROM t2;


Select unique records from multiple records based on max date I have table like below

+----+----------+-----+-------------+--------+
| ID |   ADDR   | IND | update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
|  1 | PHYSICAL | Y   | 11-Oct      |   1293 |
|  1 | BILL     | Y   | 10-Oct      |   1357 |
|  1 | BILL     | N   | 10-Sep      |  13567 |
|  1 | PHYSICAL | Y   | 10-Oct      |   8403 |
|  1 | BILL     | Y   | 6-Oct       |    135 |
|  2 | PHYSICAL | Y   | 9-Oct       |   5333 |
|  2 | BILL     | N   | 8-Aug       |    657 |
|  2 | BILL     | Y   | 8-Oct       |   3673 |
|  2 | BILL     | Y   | 10-Oct      |   2574 |
|  3 | BILL     | Y   | 11-Oct      |  57923 |
|  3 | BILL     | Y   | 11-Oct      |  29384 |
+----+----------+-----+-------------+--------+

need to check below multiple conditions before populating the data

If record has a combination of duplicate id, addr and Ind, then select the max(update_date) from that record.
If record has a combination of duplicate id, addr, Ind and update_Date, select only one record from the duplicate records (It can be any record).

Below was the expected result.

Can anyone plug me some ideas to achieve this.

here is the sqlfiddle:  http://sqlfiddle.com/#!4/d0098/1

+----+----------+-----+-------------+--------+
| ID |   ADDR   | IND | Update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
|  1 | PHYSICAL | Y   | 11-Oct      |   1293 |
|  1 | BILL     | Y   | 10-Oct      |   1357 |
|  1 | BILL     | N   | 10-Sep      |  13567 |
|  2 | PHYSICAL | Y   | 9-Oct       |   5333 |
|  2 | BILL     | N   | 8-Aug       |    657 |
|  2 | BILL     | Y   | 10-Oct      |   2574 |
|  3 | BILL     | Y   | 11-Oct      |  29384 |
+----+----------+-----+-------------+--------+

For the first condition

count() Over (Partition by id, addr, Ind)
max(update_date) Over (Partition by id, addr, Ind)

For the secontd condition

row_number Over (Partition by id, addr, Ind and update_Date)

I would suggest this:

select ID, ADDR, IND, Update_Date, Sq_NBR
from yourTable as yt
inner join
    (select max(Update_Date) as newestDate, ID, ADDR, IND
     from yourTable
     group by ID, ADDR, IND
    ) as newTable
on yt.ID = newTable.ID
and yt.ADDR = newTable.ADDR
and yt.IND = newTable.IND
and yt.Update_Date = newTable.newestDate
order by ID, ADDR


Why is dbo the only grantor in Microsoft SQL Server? I can't understand why is dbo the only grantor in SQL Server? Or am I wrong?

I mean it as follow:
No matter as which user I am currently loged in in SQL Server Management Studio, when I go to properties of a database under Securables and grant some permissions to a user and save it and come back to Securables: dbo is shown as Grantor for those permissions, which I granted before and not the current loged in user.

Why is it so?

Under which scenario could I see another user than dbo as grantor?

Thanks

Probably because all your logins are sysadmin, and sysadmin always connects as dbo.

Try something like:

create user fred without login

create user joe without login

grant select to fred with grant option

execute as user='fred'
  grant select to joe
revert

select * from sys.database_permissions

I think its simply because dbo literally stands for Database Owner. The properties of a Database and changes should be granted from the dbo role.

Outside of the Database, maybe at the server level, SA becomes the grantor of permissions.


How to switch Quiescing to Normal Mode in Oracle? I have two instance database Oracle, they are ORCL11 and ORCL10,.... I've configured them, it looks like correct. Then, I connect to replication user admin ORCL10

conn repadmin/repadmin@orcl10

And run SQL command in replication admin user ORCL10,...

BEGIN DBMS_REPCAT.RESUME_MASTER_ACTIVITY (gname=>'scott_repg');end;/

Then, i connect to replication admin user ORCL11 to see gname status.

conn repadmin/repadmin@orcl11;
select gname,status from dba_repgroup;

It display gname = SCOTT_REPG and status = NORMAL,.... but if connect to replication admin ORCL10

conn repadmin/repadmin@orcl10;
select gname,status from dba_repgroup;

It display that gname = SCOTT_REPG and STATUS = QUIESCING, I think it does not have any impact, so I test my replication on ORCL10, for example :

conn system/ok@orcl10;
insert into DEPT values (101,'Financial');
select*from DEPT;

It works on table DEPT ORCL10 (1 row created), but when i switch to ORCL11 then run simple sql query :

conn system/ok@orcl11;
select*from DEPT;

it doesn't show anything, i've searched it on Google but there is nothing tutorial how to fix that, I think the problem is about how to switch QUIESCING to NORMAL mode at ORCL10. I've try this way to fix it :

ALTER SYSTEM QUIESCE RESTRICTED;
ALTER SYSTEM UNQUIESCE;

But it still doesn't solve the problem,..... Would you help me to fix this problem ??? thanks !!!

You are mixing up quiescing a replication master group and quiescing a database — these are different concepts:

ALTER SYSTEM QUIESCE

Use the QUIESCE RESTRICTED and UNQUIESCE clauses to put the database in and take it out of the quiesced state. This state enables database administrators to perform administrative operations that cannot be safely performed in the presence of concurrent transactions, queries, or PL/SQL operations.

Master Group Quiesce

At times, you must stop all replication activity for a master group so that you can perform certain administrative tasks on the master group. For example, you must stop all replication activity for a master group to add a new master group object. Stopping all replication activity for a master group is called quiescing the group. When a master group is quiesced, users cannot issue DML statements on any of the objects in the master group. Also, all deferred transactions must be propagated before you can quiesce a master group. Users can continue to query the tables in a quiesced master group.

After quiescing a replication master group, RESUME_MASTER_ACTIVITY resumes normal replication activity.


duplicate rows based on a column value Rather than merging rows I need to 'expand' rows, here's an example to explain:

|Material|count|
|Rock    | 3   |
|Gold    | 2   |
|Silver  | 1   |
|Wood    | 2   |

I need one row per material e.g. translated to this

|Material|count|
|Rock    | 1   |
|Rock    | 1   |
|Rock    | 1   |
|Gold    | 1   |
|Gold    | 1   |
|Silver  | 1   |
|Wood    | 1   |
|Wood    | 1   |

Is there an sql solution for this type of problem?

You need a sufficiently large table to multiply the source table. In your example it is sufficient to do a self join:

select material, 1
from (
    select x.material, x.count
         , row_number() over (partition by x.material) rn
    from t as x
    cross join t as y
) as u
where rn <= count
order by material;

Gold               1
Gold               1
Rock               1
Rock               1
Rock               1
Silver             1
Wood               1
Wood               1

PostgreSQL also have a nifty table function generate_series that can be used:

select material, 1 from (
    select t.material, t.count
         , row_number() over (partition by material) as rn
    from t
    cross join generate_series(1,10)
) as u
where rn <= count
order by material;

instead of 10 you can determine:

select max(count) from t

and use that to avoid creating an unnecessary large table.

While educating myself I realized that generate_series can take a variable as argument:

select material, 1 from (
    select t.material, t.count
         , row_number() over (partition by material) as rn
    from t
    cross join ( SELECT  generate_series(1, cnt)
                 FROM ( SELECT MAX(COUNT) FROM t ) u (cnt)
               ) as v
) as u
where rn <= count
order by material;

Using LATERAL means we can correlate cnt with count in the outmost table, and thereby remove the need for row_number():

select material, 1
from (
    select t.material, t.count
    from t
    cross join LATERAL ( SELECT  generate_series(1, cnt)
                         FROM (
                             SELECT COUNT
                             FROM t as t1
                             WHERE t.material = t1.material
                         ) u (cnt)
                       ) as v
) as u
order by material;

If you made it this far, go read @ypercubeᵀᴹ answer. It does'nt get much simpler or better than that. Please vote for that one.

First create udf function

CREATE FUNCTION [dbo].[udf_ReturnVal]
(
@Input INT
)
RETURNS @Data  TABLE (
val INT
)
AS
Begin
    Declare @IntialVal INT=1,
            @count INT=@Input
    WHILE (@IntialVal <= @count)
    BEGIN
        INSERT INTO @Data
        SELECT 1
        SET @IntialVal=@IntialVal+1
    END
    RETURN
END

Sample Data and Sql query

DECLARE  @TempData AS TABLE (Material Varchar(100),[count] INT)
INSERT INTO @TempData
SELECT 'Rock'  , 3 UNION ALL
SELECT 'Gold'  , 2 UNION ALL
SELECT 'Silver', 1 UNION ALL
SELECT 'Wood'  , 2

SELECT d.Material,
        1 AS [count]
FROM @TempData d
CROSS APPLY [dbo].[udf_ReturnVal](d.[count])

Expected Result

Material    count
-----------------
Rock         1
Rock         1
Rock         1
Gold         1
Gold         1
Silver       1
Wood         1
Wood         1


How to modify authentication plugin in mariadb starting with 10.4.1 to get mysql access for root user without password when migrating mysql to Mariadb At https://kofler.info/root-login-problem-mit-mariadb/ a method is described for modifying the plugin being used for authenticating a user

update mysql.user set plugin='' where user='root';

leading to

ERROR 1348 (HY000): Column 'plugin' is not updatable

https://mariadb.com/kb/en/library/mysqlglobal_priv-table/

explains that there is now a single column global_priv table instead and the original user table is now a view.

select * from mysql.global_priv where User='root';

is possible or

SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) FROM mysql.global_priv where user='root';

for a more readable version.

Unfortunately the documentation says nothing about

How to modify the column under this design decision?
The motivation for the change?

It would be great to get these two questions answered - obviously #1 being the more pressing one.

see: https://mariadb.com/kb/en/library/authentication-plugin-unix-socket/

ALTER USER root@localhost identified via  unix_socket;
flush privileges;

also

mysql

now works for the root user without password.

The statement *"The unix_socket authentication plugin is also installed by default ..."

does have no effect if you migrate from Oracle's mysql to mariadb

ALTER USER 'root'@'localhost' IDENTIFIED WITH <auth_plugin> USING PASSWORD('RootPass');


Distribution database corrupt - What to do? The below error message has been received from one of our SQL Server databases:

  SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x6847f47b; actual: 0x15160a19). It occurred during a read of page (1:934295) in database ID 5 at offset 0x000001c832e000 in file 'C:\XYZ.MDF'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Obviously I have to run DBCC CHECKDB on the corrupt database. Before doing that, I tried to find out what the database name was (using database id) and what object the error was related to, using and the page number that was given in the error message.

So I understood that the database name was distribution and the table name was MSrepl_errors.

I'm not much familiar with SQL Server replication features, so would appreciate if someone can please help me with the below questions:

In order to investigate and/or possibly fix the issue, I have to first execute DBCC CHECKDB; however, CHECKDB cannot be run  without first putting the database in single user mode, is this correct?
Regarding the fact that the distribution database is not used by end users, do I still have to put it in the single user mode? And, if yes, what will be the impact on the replication jobs?

However, CHECKDB cannot be run without first putting the database in single user mode. Is this correct?

It shouldn't need to be in single user in order to run checkdb. You can run DBCC CHECKDB('distribution') WITH NO_INFOMSGS, ALL_ERRORMSGS to check it.

Since we don't know where the current issue is, it's hard to say what/if any impact to the current topology would be. We'd really need to know more, feel free to update the question with the output from CHECKDB.

Please do note that system databases, which the distribution database is considered, are not supported to have REPAIR_ALLOW_DATA_LOSS run on them. In fact, it should only be run in the most devastating of situations and under duress. Choosing to do this to a system database will immediately become an unsupported configuration.

MSrepl_errors  table contains errors which were occurred while running replication and reported by an agent.
Try to run dbcc checked and see error info. Repairing the database would delete data from this table when the page will be repaired and its not major concern. Rest of tables should not be impacted.
I would suggest first check the error info and then run repair command to bring db online.

 DBCC CHECKDB ('distribution', REPAIR_ALLOW_DATA_LOSS);

When the database brings online then start agents and verify errors if any reported by an agent. Try to use replication monitor for more information.


Clustered index creation fail on table creation We've got an error when running the following script;

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Table_Name')
BEGIN
CREATE TABLE Table_Name
(
    Field_Name_1 binary(32) NOT NULL CONSTRAINT PK_Name_Goes_Here PRIMARY KEY NONCLUSTERED
    , Field_Name_2 int NOT NULL
    , Field_Name_3 datetime NOT NULL INDEX IX_Name_Goes_Here CLUSTERED
)
END

In particular it's the clustered index creation throwing the following error:

  Msg 1018, Level 15, State 1, Line 15
  Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

This is odd as it works on all of our servers except for a particular QA server. The fix we've put in place is to create the clustered index outside of the table creation statement but I'd be interested if anybody has come across this problem before?

The syntax for inline index declaration was added in SQL Server 2014, though that is was absolutely unclear in the official CREATE TABLE documentation. After speaking to the documentation owners, that topic now accurately reflects that inline index syntax is only valid starting with SQL Server 2014 (and some variations in 2016):

The other instances, where this syntax is working for you, must be on SQL Server 2014 or newer.

On 2012, regardless of compatibility level, you'll need to create the index separately.

I'd have to run it through SSMS to know for sure, but that syntax looks off to me. While I've defined columns as primary keys inline (like you are doing) I've never attempted to define a clustered index that was not the primary key in that fashion. What you are doing simply isn't possible in the SQL Server version you are using. I think you should define the clustered index using a standard CREATE INDEX after the table has been created via the CREATE TABLE statement.


Cast as INT(11) instead of DECIMAL 11,0 in a view I have a view with a column defined as:

IFNULL(priority.id, 2) priority_id, ...

where priority.id has type INT(11), however the resulting view column has type:

DECIMAL(11,0)

How do I get the resulting column to retain the INT(11) type?

Decimal(n,0) still treated as integer in mysql 5.0.3 and above.

Decimal (n,0) is a fixed point that did not has fractional part.
So it is safe.

The only different int(11) with decimal(11,0) is the maximum range of number. where as the decimal has bigger range than integer

See MySQL numeric type overview


Oracle program "lsnrctl" is missing after the installation of 32bit Oracle client I cleaned up my computer for all old Oracle stuff using Uninstall (OUI) and then later used regedit to  delete all references to Oracle and then deleted all remains of Oracle directories.

I have HP ProBook with Windows 7 (64-bit) and I want to install a 32 bit Oracle client on it. I need to be  connected to an Oracle 11gR2 database on a remote server by OCI, ODBC and OLEDB.

I have done this installation tens of times before, but this time I am stuck.

The command lsnrctl isn't present after the installation, and as expected then, the lsnrctl.exe binary is not present under the Oracle home directory (in BIN) as it should be.

This test shows the frightening situation:

C:\Oracle\product\11.2.0\client_1\BIN>lsnrctl status
'lsnrctl' is not recognized as an internal or external command,
operable program or batch file.

I did the same operations and installation on more time, but with the same result.
Why does this happen and what can I do to straight it out, other than re-installing the whole Windows 7?

There doesn't appear to be a problem.

The listener is a process that generally runs on the database server, not on the client machine.  Doing a client-only install will not install a listener.  If you want to have a listener on your machine, you'd need to do a database install (though you can do a software-only install rather than actually creating a database).

If you're just trying to connect to a remote database, however, you'd use the listener that is (presumably) running on the database server.  It's theoretically possible to install a listener on one machine for a database running on another machine but that isn't particularly common.  It would be extremely, extremely unusual to install a listener on the client machine that listens for connections to a database on a remote server.

I'm pretty inexperienced and still learning but I think the issue has to do with a dedicated server installation.