How do I merge similar records with different validity dates? The table I am working on has three components:

An ID column (primary key in another table)
Some data columns
Date valid from/to columns.

Values:

ID   Data From        To
1    a    2015-01-01  2015-01-05
1    a    2015-01-06  2015-01-10
1    b    2015-01-11  2015-01-15
1    a    2015-01-16  2015-01-20
2    c    2015-01-01  2015-01-05
2    c    2015-01-06  2015-01-10

The table is updated by taking "snapshots" of another data source at some intervals and assigning validity dates to records. The problem is that these snapshots create duplicate entries for records (with different validity dates) that were not changed at all during that interval.

I want to reduce the size of the table by looking for rows with consecutive dates and by merging them and assigning them a single validity period. For example:

ID   Data From        To
1    a    2015-01-01  2015-01-10
1    b    2015-01-11  2015-01-15
1    a    2015-01-16  2015-01-20
2    c    2015-01-01  2015-01-10

The logic I currently have is:

Select and sort all rows by ID, data fields, and 'valid from' fields (so they are in groups of consecutive rows).
Use a cursor to compare adjacent rows for similarity.
If they are same, then merge rows and change validity period to include both rows.

I understand that cursors are very inefficient (I have a large dataset), so I am looking for other approaches.

If this is a table of back-to-back ranges only, your case can be treated as a classic "gaps and islands" problem, where you just need to isolate islands of consecutive ranges and then "condense" them by taking the minimum [from] and the maximum [to] per island.

There is an established method of solving this using two ROW_NUMBER calls:

WITH islands AS
(
  SELECT
    id,
    data,
    [from],
    [to],
    island = ROW_NUMBER() OVER (PARTITION BY id       ORDER BY [from])
           - ROW_NUMBER() OVER (PARTITION BY id, data ORDER BY [from])
  FROM
    #mergeTest
)
SELECT
  id,
  data,
  [from] = MIN([from]),
  [to]   = MAX([to])
FROM
  islands
GROUP BY
  id,
  data,
  island
;

This query will work in as low version as SQL Server 2005.

I wrote a query that seems to work. It uses Common Table Expressions, MERGE statements and Analytic functions. However it is only compatible with SQL server 2012+. You can find the gist here: MergeRecordsByValidityDate.sql

/*  NOTE: Only works w/ SQL Server 2012+
    Merging identical records with different validity dates.
*/
USE [master]

IF OBJECT_ID('mergeTest') IS NOT NULL
    DROP TABLE mergeTest

CREATE TABLE mergeTest          -- Create table with test data
(
    [id] int NOT NULL,
    [data] char(1) NOT NULL,
    [from] date NOT NULL,
    [to] date NOT NULL
);

INSERT INTO mergeTest ([id],[data],[from],[to]) VALUES      -- Insert records w/ different validity dates
    (1,'a','2015-01-01','2015-01-05'),  --1
    (1,'a','2015-01-05','2015-01-10'),  --2
    (1,'a','2015-01-10','2015-01-14'),  --3
    (1,'b','2015-01-14','2015-01-15'),  --4
    (1,'a','2015-01-15','2015-01-20'),  --5
    (1,'a','2015-01-20','2015-01-25'),  --6
    (1,'a','2015-01-25','2015-01-30'),  --7
    (1,'a','2015-01-30','2015-02-04'),  --8
    (2,'c','2015-01-01','2015-01-05'),  --9
    (2,'c','2015-01-05','2015-01-10')   --10

SELECT * FROM mergeTest

/*  This SELECT function uses a Common Table Expression along with Analytic functions over a partition.
    The data set is partitioned on similar primary key and data columns and ordered by 'from' dates.
    A 'last' and 'next' column is added with 'to' date of prev row and 'from' date of next row.
    For each partition, rows are selected (for each partition) that represent the first and last records
    of identical data. For e.g. rows 5,6,7,8 are reduced to 5,8.
*/

;WITH partitionedData AS (
    SELECT *,   LAG([to],1,NULL) OVER(PARTITION BY [id],[data] ORDER BY [from]) AS [last],
                LEAD([from],1,NULL) OVER(PARTITION BY [id],[data] ORDER BY [from]) AS [next]
    FROM mergeTest)
SELECT [id],[data],[from],[to],[last],[next] INTO #temp
    FROM partitionedData
    WHERE [last] IS NULL OR [next] IS NULL OR [last]<>[from] OR [next]<>[to]
;

SELECT * FROM #temp

/*  Now all redundant 'sandwiched' records have been filtered out, only the extreme records are left.
    This MERGE function matches rows on primary key and data, and If the 'to' date of said record matches
    'from' date of another similar record, then the said record is extended to encapsulate the other record's
    'to' date. For example row 5's 'to' date is extended to equal row 8's 'to' date.
*/

MERGE INTO #temp as m1
    USING #temp as m2
    ON m1.id=m2.id AND m1.data=m2.data
WHEN MATCHED
    AND (m1.[to]=m2.[from])
    THEN
    UPDATE SET  m1.[to]=m2.[to]
;

SELECT * FROM #temp

/*  The MERGE function has done its job of extending records. However there are still 2 records with
    identical data. For e.g. rows 9,10 exist even though row 9 now has all the required information. This
    block modifies such redundant rows so their 'last' and 'from' columns become asynchronous.
*/

;WITH repartitionedData AS (
    SELECT [id],[data],[from],[to], LAG([to],1,NULL) OVER(PARTITION BY [id],[data] ORDER BY [from]) AS [last],
                LEAD([from],1,NULL) OVER(PARTITION BY [id],[data] ORDER BY [from]) AS [next]
    FROM #temp)
SELECT [id],[data],[from],[to],[last],[next] INTO #temptemp
    FROM repartitionedData
    WHERE [last] IS NULL OR [next] IS NULL OR [last]<>[from] OR [next]<>[to]
;

SELECT * FROM #temptemp

/* Asynchronous rows are deleted
*/

DELETE FROM #temptemp
    WHERE [from]<[last]

SELECT * FROM #temptemp

/*  However, blocks of data with >2 rows (like rows 5 through 8) could not be merged because of the filtered out
    rows (i.e. rows 6,7). Applying MERGE again on the updated data set.
*/

MERGE INTO #temptemp as m1
    USING #temptemp as m2
    ON m1.id=m2.id AND m1.data=m2.data
WHEN MATCHED
    AND (m1.[from]=m2.[next])
    THEN
    UPDATE SET  m1.[from]=m2.[from],
                m1.[last]=CASE WHEN ((m2.[last] IS NULL) OR (m2.[next] IS NULL)) THEN NULL ELSE m1.[last] END   --if row absorbing from is extreme, then current row is also extreme
;

SELECT * FROM #temptemp

TRUNCATE TABLE mergeTest        -- resetting original table

/* The MERGE corrected all rows with the correct 'from' and 'to' dates. And the only rows we are interested in are
    the extreme rows i.e. with 'last' or 'next' == NULL. SELECTing on that criterion and INSERTing into original table.
*/

INSERT INTO mergeTest           -- inserting processed records into table + some last minute filtering
    SELECT [id],[data],[from],MAX([to])
    FROM #temptemp
        WHERE [next] IS NULL OR [last] IS NULL
    GROUP BY [id],[data],[from]

SELECT * FROM mergeTest

DROP TABLE #temp
DROP TABLE #temptemp


Multiple Similar Tables vs One Master Table I'm a web app developer and less experienced in database design, so would appreciate anyone's input.

When I first built this web app, the app managed one type of object (table) with a handful of properties (columns). As the app grew, there were additional types of very similar objects, so I created additional tables for each object type.  (You could say that all the object types inherit from the same class/prototype.) I'm finding that my tables for each object type are all nearly identical because each object type shares ~ 90% of the same property types.  For example, the tables may have nearly the exact same column names (& data types), but one table may have 1-2 extra columns to refer to properties specific to that object type.

Since all the object types are so similar (in the same way that Dog, Cat, and Mouse all inherit from the Animal class), would it be wise to put these all in the same table and simply allow some columns to contain NULL values when the columns are not relevant to that type?  Or is there some other alternative I'm not considering?

For reference, here are some similar posts I've already reviewed:

1) What are the design alternatives when having multiple "type" tables

2) https://stackoverflow.com/questions/9021838/database-design-multiple-tables-vs-a-single-table

3) Database design: Dividing multiple identical tables, good or bad?

Create the Animal table which has information that all animals share. Add another column to the Animal table called type or something similar. In this, specify whether the animal is a cat, dog, mouse, or whatever.

Then, create Cat, Dog, and Mouse tables that have the Animal ID (The primary key) as a foreign key. Each of those tables has columns specific for Cats, Dogs, and Mice specifically. The Mouse table might have a column Favorite_cheese or some such, which the normal animals table would not. To get the full mouse record, you can join the two tables together.

This allows you to have a relatively lean Animal table while still supporting subclassing functionality.

The idea of single common master table instead of multiple is good but the integrity of Foreign Key constraint fails.


How can I replicate MySQL data from many servers to one? All over the internet I can find manuals on how to replicate databases from master(s) to slaves.

What I need is to continuously keep a copy of table from 5 different servers in sync.

We have one application running at 5 locations. We need to have a copy of the system_log table from every one of those 5 servers always in sync on one new server at our 'headquarters'.

What is the best way to achieve this without using custom scripts?

Sorry.  If you are trying to have many Masters and one Slave -- that is not supported.  (Some third party addons may have such.)  It is better to have the 5 servers all write to the 'headquarters' Master.  Network latencies are probably not a serious problem.

Maybe, you can write in master server the data and replicate to the 5 slaves.
In central node you write 'system_log' and mysql reply your data to slaves servers.


How to properly increment an int identity in SQL Server if the Identity Increment isn't set? When I use SQL Server with an int based identity column, I always use the auto increment.  But I'm needing to insert into an existing table that doesn't have the property set, and I'm wondering what is the best practices way of doing this.

The naive approach would be to query the data, increment it, and then use that for an insert.

For example:

INSERT INTO myTable
(
  ID,
  Data
)
Values
(
  (SELECT MAX(ID) FROM myTable) + 1,
  "My actual data."
)

But I'm unsure if this is actually the best approach.  Namely, since this is all one statement, is there a risk for a heavily utilized system to have another row inserted between selecting the MAX(ID) and the insert.

Edit:
Sql Server version 2012.

Edit 2: To specify, I'm looking for a DML solution as I do not have DDL rights to modify the table.

The best option is to use the SEQUENCE object, introduced in 2012. Since it is an independent object, you don't run the risk of querying it at the same time and retrieving the same value - it'll always provide the next in the chain.

Set the object with a specific start and increment value, then call it to get the next value desired. One of the biggest benefits here is that you can also specify a number of values to be cached for faster retrieval if you're working with rapid inserts.

CREATE SEQUENCE seq_obj
    START WITH 1050
    INCREMENT BY 1
    CACHE 1000
GO

Then to get the next ID value, use:

SELECT NEXT VALUE FOR seq_obj

Which will provide 1050, then 1051, and so on and so forth.

MS Docs for full information on other properties.

Certainly, it will work:

INSERT INTO myTable
(
  ID,
  Data
)
SELECT MAX(ID)+1, "My actual data" FROM myTable;


Cannot connect to my local instance When I connect to my local server, I get this message

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

This is my login information:

Server type :       Database Engine
Server name :       .
Authentication:     SQL Server Authentication
Login:              sa
Password:           123

How can I fix it?

Go to SQL Server Configuration Manager

Start->all program->SQL Server 2008 R2->Configuration Tools->SQL Server Configuration Manager

Explore "SQL Server Network Configuration" and then select "Protocols For MSSQLSERVER".

Check your TCP/IP Protocols in right side pane. If it is disabled then enabled it and Restart the "Sql Server(MSSQLSERVER) service" that available in  "SQL Server 2008 R2 services pane".

You can follow the same steps to make sure for other protocols and services.

Click on Start-> Search for "services.msc" hit Enter.
Now you will see all the services which are running on your Computer.

Start the service :
     1. SQL Server (MSSQLSERVER)

Then connect to local by providing server name : (local)


convert csv to insert sql statements I have a CSV file and I want to convert it into a SQL INSERT statements.But I could only find online converters but I would like to know can I create it manually ?

You could open it in Excel then write a concatenate function that builds an insert statement, then copy it down the rows.

But if you really just want to get the data into a table, you could load it using the 'SQL Server Import and Export Wizard', or build an SSIS job to do it.

It is better to import the csv file into a table than the insert query
I think background also takes same insert query preparation while importing csv file


What's the difference between a CTE and a Temp Table? What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other?

CTE

WITH cte (Column1, Column2, Column3)
AS
(
    SELECT Column1, Column2, Column3
    FROM SomeTable
)

SELECT * FROM cte

Temp Table

SELECT Column1, Column2, Column3
INTO #tmpTable
FROM SomeTable

SELECT * FROM #tmpTable

This is pretty broad, but I'll give you as general an answer as I can.

CTEs...

Are unindexable (but can use existing indexes on referenced objects)
Cannot have constraints
Are essentially disposable VIEWs
Persist only until the next query is run
Can be recursive
Do not have dedicated stats (rely on stats on the underlying objects)

#Temp Tables...

Are real materialized tables that exist in tempdb
Can be indexed
Can have constraints
Persist for the life of the current CONNECTION
Can be referenced by other queries or subprocedures
Have dedicated stats generated by the engine

As far as when to use each, they have very different use cases.  If you will have a very large result set, or need to refer to it more than once, put it in a #temp table.  If it needs to be recursive, is disposable, or is just to simplify something logically, a CTE is preferred.

Also, a CTE should never be used for performance.  You will almost never speed things up by using a CTE, because, again, it's just a disposable view.  You can do some neat things with them but speeding up a query isn't really one of them.

There seems to be a bit of negativity here towards CTE's.

My understanding of a CTE is that it's basically a kind of adhoc view. SQL is both a declarative and a set based language. CTE's are a great way of declaring a set! Not being able to index a CTE is actually a good thing because you don't need to! It's really a kind of syntactic sugar to make the query easier to read/write. Any decent optimizer will work out the best access plan using indexes on the underlying tables. This means you could effectively speed up your CTE query by following the index advice on the underlying tables.

Also, just because you defined a set as a CTE, it doesn't mean that all rows in the set must be processed. Dependent on the query the optimizer might process "just enough" rows to satisfy the query. Maybe you only needed the first 20 or so for your screen. If you built a temp table then you really do need to read/write all those rows!

Based on this I would say that CTE's are a great feature of SQL and can be used anywhere they make the query easier to read. I would only think about a temp table for a batch process that would really need to process every single record. Even then afaik it's not really recommended because on a temp table it's far harder for the database to help you with caching and indexes. It might be better to have a permanent table with a PK field unique to your transaction.

I have to admit that my experience is mainly with DB2 so I'm assuming that CTE's work in a similar way in both products. I will happily stand corrected if CTE's are somehow inferior in SQL server. ;)


Select MIN value from multiple columns I have a very basic database for keeping track of point scores for 6 different events. Each event has 4 events within it.

My Current SQL query is very basic, and looks very similar to what is below;

Select Name,
       SUM(ISNULL(p.e1_e1_points, 0)+ISNULL(p.e1_e2_points, 0)+ISNULL(p.e1_e3_points, 0)+ISNULL(p.e1_e4_points, 0)) AS Event1,
       SUM(ISNULL(p.e2_e1_points, 0)+ISNULL(p.e2_e2_points, 0)+ISNULL(p.e2_e3_points, 0)+ISNULL(p.e2_e4_points, 0)) AS Event2,
       SUM(ISNULL(p.e3_e1_points, 0)+ISNULL(p.e3_e2_points, 0)+ISNULL(p.e3_e3_points, 0)+ISNULL(p.e3_e4_points, 0)) AS Event3,
       SUM(ISNULL(p.e4_e1_points, 0)+ISNULL(p.e4_e2_points, 0)+ISNULL(p.e4_e3_points, 0)+ISNULL(p.e4_e4_points, 0)) AS Event4,
       SUM(ISNULL(p.e5_e1_points, 0)+ISNULL(p.e5_e2_points, 0)+ISNULL(p.e5_e3_points, 0)+ISNULL(p.e5_e4_points, 0)) AS Event5,
       SUM(ISNULL(p.e6_e1_points, 0)+ISNULL(p.e6_e2_points, 0)+ISNULL(p.e6_e3_points, 0)+ISNULL(p.e6_e4_points, 0)) AS Event6
       from points

I need to be able to add an additional column, which is the lowest value from columns (being Event1, Event2, Event3, etc).

Basically, this is about finding a row-wise minimum.

There is an elegant inline solution in the most upvoted answer to this Stack Overflow question:

SQL MAX of multiple columns?

According to that solution, if you have a table T of this kind:

C1  C2  C3
--  --  --
…   …   …
…   …   …

you can find a row-wise minimum like this:

SELECT
  C1,
  C2,
  C3,
  (
    SELECT MIN(C)
    FROM (VALUES (C1), (C2), (C3) AS v (C)
  ) AS MinC
FROM
  T
;

Basically you are arranging the values of C1, C2, C3 as a column and are applying a normal (column-wise) aggregate function to it to find the minimum.

Now in your case the C1, C2 etc. are expressions. Usually that is fine, you can use the VALUES row constructor with expressions. But in this case each expression already contains an aggregate function (SUM()), which prevents us from applying the method directly (VALUES expressions must not use aggregate functions).

However, that issue is easily resolved. You can use your current query as a derived table or a CTE and apply the method at the outer level, where the expressions will be just references, like this:

SELECT
  Name,
  Event1,
  Event2,
  Event3,
  Event4,
  Event5,
  Event6,
  (
    SELECT MIN(Event)
    FROM (VALUES (Event1), (Event2), (Event3), (Event4), (Event5), (Event6)) AS v (Event)
  ) AS MinEvent
FROM
  (
    SELECT
      ... /* your current query */
  ) AS derived
;

And here is an identical solution but using a CTE:

WITH cte AS
  (
    SELECT
      ... /* your current query */
  )
SELECT
  Name,
  Event1,
  Event2,
  Event3,
  Event4,
  Event5,
  Event6,
  (
    SELECT MIN(Event)
    FROM (VALUES (Event1), (Event2), (Event3), (Event4), (Event5), (Event6)) AS v (Event)
  ) AS MinEvent
FROM
  cte
;

I'm late, but doesn't the procedure "least" suits what you're trying to achieve ?
https://www.w3schools.com/sql/func_mysql_least.asp


Database connectivity issue in SSIS I have created a Connection manager in my SSIS project for database connectivity.I choose SQLClient Data Provider and select SQL server authentication mode for login.Test connection succeeded.
In Script Task I'm using following to get the connectionstring:

string strConnection = Dts.Connections["Target_DB"].ConnectionString;

While debug I found following only:

Data Source=ServerName;User ID=myid;Initial Catalog=product;Persist Security Info=True;

As you can see password is missing and that's why I'm not able to connect to database server Even Test connection succeeded.

Does this means it's not saving password if so then how can I connect to database.

---------------UPDATE---------------------

I added App.config file in ScriptTask and added ConnectionString node in it.Then I tried to access connection string in following manner but couldn't succeeded and it's showing some other connection string not the one which I have written:

string strConnection = ConfigurationManager.ConnectionStrings["CRMConnectionString"].ConnectionString;

Thanks

You can't access the password in the connection string. It's a security measure, regardless of your package protection level.

If you need to use an existing connection, then you need to follow the examples laid out in Todd McDermid's post Use Connections Properly in an SSIS Script Task or in Matt Masson's Accessing OLEDB Connection Managers in a Script

Personal note, I never managed to get Matt's version working but since he was the PM on the SSIS team, I'll assume it's my failed implementation and not his.

An alternative for accessing the password, since this is tagged with 2008, is to use Configuration. Whether it be XML or SQL Server, store the connection string in an external repository and assign the value to an SSIS Variable. Then, apply an Expression to the Connection Manager's ConnectionString property. Within your script task, you'll be able to access the complete connection string which contains the password.

This happens because of the protection level set in the package properties. When you create a connection manager and test it, it doesn't save the username and password for runtime in the way you think it might.

To fix, set the pacakage protection level to EncryptSensitiveWithPassword or EncryptSensitiveWithUserKey (recommended for dev projects) and this should work.

At runtime, the connection credentials are encrypted, so if your protection level is something like DontSaveSensitive, you'll run into issues like this. EncryptSensitiveWithUserKey or EncryptSensitiveWithPassword will decrypt the credentials at runtime.

To change the package protection level, follow the instructions in this link


detect what columns to select depending on a column value of the same table I am trying to build a query which returns different columns of the table depending on a column of the same table. Say, a column represents a type of item.

This is my temporary solution, in which I just combine the results of multiple queries.

As you can see, I use "AS" keyword to rename the default column name.

SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", [domain_name] AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 1
union all
SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", CAST([ip_address] AS nvarchar(15)) AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 2
union all
SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", [domain_name] AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 3
union all
SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", [mail_domain]  AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 4
union all
SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", CAST([ip_address] AS nvarchar(15)) AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 5
union all
SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", [domain_name] AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 6
union all
SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", [service_name] AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 7

Sounds like you need to use CASE WHEN expression, which is well documented. So case when x = y then select this else select that etc.


Single Slave with Multiple Masters. Does this pattern exist in MongoDB? Can we have a single mongoDB remotely (online) which can act as a slave to multiple master mongoDBs (which operate offline)?
For example, I have huge number of transactions which I like to perform on my local MongoDBs parallely on multiple systems (offline).
At the end of the day, I want to push minute part of my offline MongoDB records to my single slave MongoDB which is remote and online.
Does this pattern exist?

I don't think so Single Slave with Multipe Master Possible. As per MongoDB BOL Here Master-slave replication preceded replica sets and made it possible to have a large number of non-master (i.e. slave) nodes, as well as to restrict replicated operations to only a single database; however, master-slave replication provides less redundancy and does not automate failover.

  WARNING
  Deprecated since version 3.6: MongoDB 3.6 deprecates the use of
  master-slave replication.

  Master-slave replication has been deprecated for components of sharded
  clusters since MongoDB 3.2.

Replica sets replace master-slave replication for most use cases. If possible, use replica sets rather than master-slave replication for all new production deployments. This documentation remains to support legacy deployments and for archival purposes only.

For further your ref Master Slave Replication

I don't think so. But you can try mongo-connector to connect to multiple instances and collect and auto populate the data accordingly.


Installation of Microsoft SQL Server Data Tools Hangs I'm attempting to install SQL Server Data Tools, but the installation is hanging.  It had an error the first attempt; and I've restarted the machine (as that seems to help in those scenarios).  Now it just sits there on the tools for Visual Studio not installing (I've left it running over night).  Any suggestions on what to do here?

I experienced exactly the same error, right down to the pixel.  I figured out the solution by continuing the broken installation using the standard VS 2017 installer (it will recognize that the SSDT installer error'd out).  Resuming this installer reported more information via error logs.  The errors were folder/file access permissions being restricted by McAfee Anti-virus

tldr; disable your anti-virus.

I have none of this installed nor running, and i have same issue. Hangs at apx 10%
(and this is 2021, latest version 15.9.7) SO annoying :/


Restore Oracle tablespace to its state from 2 days ago I have multiple test tablespaces in an  Oracle SID.  One of the tablespaces is updated with wrong data. It worked fine 2 days ago. I want to restore the tablespace to its state from 2 days ago.

I tried to restore the tablespace from the Coldbackups, but during startup it gave the error :

ORA-01113: file 10 needs media recovery
ORA-01110: data file 10: '/user/oracle/metadata/lmptest/gkpdbspace.dbf'

And

recover database

or

recover datafile '/user/oracle/metadata/lmptest/gkpdbspace.dbf'

didn't work.

You probably tried to restore and then recover the database. When you recover the database all the files should be in sync before you can open the database for work. The error shown tells you that some of your data files are not in sync with the rest of the database.

What your really need to use to recover a tablespace or a set of tablespaces is Tablespace Point-in-Time Recovery (TSPITR). You can do it manually or with RMAN. In the latter case the command might look like:

recover tablespace app_data
until SCN 1234567
auxiliary destination '/tmp/auxdb';

And you wouldn't need to configure the auxiliary database manually. With user-managed TSPITR, on the other hand, you have to configure the auxiliary database first, and then use Transportable Tablespaces feature (see the section "Introduction to Transportable Tablespaces" in Database Administrator's Guide) to move the data files along with metadata to the target database.

For more details read "RMAN Tablespace Point-in-Time Recovery (TSPITR)" and "Performing User-Managed TSPITR" sections in Database Backup and Recovery Advanced User's Guide, and also "Tablespace Point-in-Time Recovery" section in Concepts.

Usually this error comes up when you restore from a hot backup and any archivelogs needed for consistent recovery is not available.

In your case, since you are restoring from cold backup, this error should not be shown.

The open database statement is not available in your question.

Try:

alter database open resetlogs;


Multiple Set Quoted_identifier within one script takes the last one? I'm using SQL Server 2019 and just found a weird behaviour.
Research has not gotten me anywhere further.
Can someone please explain this behaviour?
SET QUOTED_IDENTIFIER ON;
if ((256 & @@options) = 256) print '1- quoted_identifier is on' else print '1- quoted_identifier is off';
BEGIN TRY
    if ((256 & @@options) = 256) print '2- quoted_identifier is on' else print '2- quoted_identifier is off';
END TRY
BEGIN CATCH
    if ((256 & @@options) = 256) print '3- quoted_identifier is on' else print '3- quoted_identifier is off';
    -- SET QUOTED_IDENTIFIER OFF
    -- if ((256 & @@options) = 256) print '4- quoted_identifier is on' else print '4- quoted_identifier is off';
END CATCH

Returns:
1- quoted_identifier is on
2- quoted_identifier is on

but the following code:
SET QUOTED_IDENTIFIER ON;
if ((256 & @@options) = 256) print '1- quoted_identifier is on' else print '1- quoted_identifier is off';
BEGIN TRY
    if ((256 & @@options) = 256) print '2- quoted_identifier is on' else print '2- quoted_identifier is off';
END TRY
BEGIN CATCH
    if ((256 & @@options) = 256) print '3- quoted_identifier is on' else print '3- quoted_identifier is off';
    SET QUOTED_IDENTIFIER OFF
    if ((256 & @@options) = 256) print '4- quoted_identifier is on' else print '4- quoted_identifier is off';
END CATCH

Returns:
1- quoted_identifier is off
2- quoted_identifier is off

Even though it isn't going into the catch!!!
I must be missing something.
I was even able to simplify the code to the most simple :
SET QUOTED_IDENTIFIER ON;
if ((256 & @@options) = 256) print '1- quoted_identifier is on' else print '1- quoted_identifier is off';
SET QUOTED_IDENTIFIER OFF
if ((256 & @@options) = 256) print '2- quoted_identifier is on' else print '2- quoted_identifier is off';

Results:
1- quoted_identifier is off
2- quoted_identifier is off

I have some code that uses FOR XML, which requires me to set quoted Identifier ON, but I need to put it back to OFF, no matter if the XML portion succeeds or fails.  How would you do that?
My tests show that if I put the SET QUOTED_IDENTIFIER to off in the CATCH, the insert fails to say that my quoted identifier is not set properly although it is set at the beginning of the TRY to ON.

This is by design:

For a top-level ad-hoc batch parsing begins using the session's current setting for QUOTED_IDENTIFIER. As the batch is parsed any occurrence of SET QUOTED_IDENTIFIER will change the parsing behavior from that point on, and save that setting for the session. So after the batch is parsed and executed, the session's QUOTED_IDENTIFER setting will be set according to the last occurrence of SET QUOTED_IDENTIFIER in the batch.

The behaviour of QUOTED_IDENTIFIER is therefore dependent on the parsing of the batch, not the execution. The default at the start of parsing the batch comes from the current connection settings.
@@OPTIONS only shows you the current default, that would be used if a batch was parsed. This is why SSMS always puts SET in a separate batch.
The execution of SET is not relevant to the behaviour of @@OPTIONS, except to change later batches' default.

You can see this in action in a db-fiddle
set QUOTED_IDENTIFIER  on;
create table "select"(i int);
drop table "select";
GO

set QUOTED_IDENTIFIER  off;
create table "select"(i int);
drop table "select";
--  Incorrect syntax near 'select'.
GO

set QUOTED_IDENTIFIER  off;
if (1=0)
begin
    set QUOTED_IDENTIFIER  on;
end
create table "select"(i int);
drop table "select";
GO

set QUOTED_IDENTIFIER  on;
if (1=0)
begin
    set QUOTED_IDENTIFIER  off;
end
create table "select"(i int);
drop table "select";
-- Incorrect syntax near 'select'.
GO

Charlieface's answer seems to have explained appropriately the reason for the behavior you described when using SET QUOTED_IDENTIFIER, so I'll just leave a method you could use if you needed to use it inside a SP:
Suggested Solution
Isolate part of your batch on another procedure and call it from the one where the QUOTED_IDENTIFIER needs to be OFF like this: db<>fiddle - run on your computer on 2 different sessions for I couldn't make it run properly on dbfiddle.


Re-writing a sub-select as a condition on a column generated with 'over (partition by ...)' I am wondering if it would be possible to use over (partition by...) clause in order to avoid using a sub-select in the example below:

declare @t table (
     id         int
    ,code       char(2)
    ,descriptor int
)

insert into @t
      select 1, 'a1', 10
union select 1, 'a1', 20
union select 1, 'a1', 30
union select 2, 'b1', 10
union select 2, 'b1', 20
union select 2, 'b1', 30
union select 2, 'b2', 10
union select 2, 'b2', 20
union select 2, 'b2', 30
union select 3, 'c4', 10
union select 3, 'c4', 20
union select 3, 'c4', 30
union select 3, 'c7', 10
union select 3, 'c7', 20
union select 3, 'c7', 30

select *
from @t as t
where code = (select min(code) from @t where id = t.id)

I want to see only the records with the code equal to a1, b1 and c4. Can over (partition by...) assign 1 to all those and 2 to b2 and c7 codes, etc, so that at the end I could say where row_number = 1 instead of using a sub-query?

Yes but not with row_number(). You can use either the window aggregate min():

select id, code, descriptor
from
  ( select id, code, descriptor,
           min_code = min(code) over (partition by id)
    from @t
  ) as t
where code = min_code ;

Or the rank() window function (or the dense_rank() one, they'll both work the same for the rnk = 1 check):

select id, code, descriptor
from
  ( select id, code, descriptor,
           rnk = rank() over (partition by id order by code)
    from @t
  ) as t
where rnk = 1 ;

I was about to comment that there is no way to do this without subqueries of any kind (correlated, derived tables, ctes) but alas, there is. Not in 2008R2 but in 2012 version using one more window function.

Note: Do not use this. The efficiency will probably be the worse. The following query might be good for an obfuscation contest but not for production.

Instead, check the nice execution plan of the above 2nd query, when there is a suitable index.

Just for fun - and horror of the future maintainer of the code:

select distinct
    id,
    code = min(code) over (partition by id),
    descriptor =
        case when code = min(code) over (partition by id)
            then descriptor
            else first_value(descriptor) over
                    (partition by id order by code, descriptor)
        end
from @t ;

Tested at SQLFiddle

select m.id, min(m.code) as code, m.descriptor
from @t as m
group by m.id, m.descriptor
order by m.id, code

The order by is only there to produce the output table in the same order as your example, but it is entirely unnecessary.

Revision to include the comment I added below - handling duplicates:

select a.id, a.code, b.descriptor
from
(
select id, min(code) as code
from
(
select id, code
from @t
group by id,code
) as a
group by id
) as a
inner join
@t as b
on b.id = a.id and a.code = b.code

No subselects, no SQL 2012 special features.  Just SQL.  It can be done.


How to add time from a Date value to another without a varchar conversion? Let's say I have 2 variables in a Stored Procedure, both are Dates

Foo contains '13/10/2019 00:00:00'

Bar contains '01/10/1914 16:33:11'

I want to add Bar's time to Foo's date, so that I get a Foobar variable containing 13/10/2019 16:33:11

Is there a way to achieve this without converting any of them into a varchar?

Simple addition and subtraction.

Although the code is SQL code, the math works the same in PL/SQL.

This code:

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
with data as (
    select TO_date( '13/10/2019 00:00:00', 'DD/MM/YYYY HH24:MI:SS') as foo
         , to_date( '01/10/1914 16:33:11', 'DD/MM/YYYY HH24:MI:SS') as bar from dual
)
select foo, foo + (bar - trunc(bar)) foo_modified, bar from data

Produces this desired results:

FOO                 FOO_MODIFIED        BAR
------------------- ------------------- -------------------
2019-10-13 00:00:00 2019-10-13 16:33:11 1914-10-01 16:33:11

You can use DATEPART to pull the hour/min/sec from BAR and do the same to pull day/month/year from foo, them use DATEFROMPARTS to reassemble.

Pity SQL Server doesn't support the ANSI INTERVAL type, which would make this super simple.


Oracleasm deletedisk failed Unable to clear disk I have dropped the disk from ASM diskgroup:

> ALTER DISKGROUP DATA01 DROP DISK DISK1;

Oracle did a rebalance operation and then dropped the disk.

Now, I want to delete the disk from ASM library and return the disk to the system:

# oracleasm deletedisk -v DISK1
  Clearing disk header: oracleasm-write-label: Unable to open device "/dev/oracleasm/disks/DISK1":
  Device or resource busy failed
  Unable to clear disk "DISK1"

The system details:
11.2.0.1 2-node RAC

Why does ASM not clearing the disk header? Where to see the detailed log?

------------------------------------Edit1--------------------------------------------

# fuser /dev/oracleasm/disks/DISK1

returns nothing on both nodes

First of all, I checked that there were no extents on DISK1.

$ . oraenv
ORACLE_SID = [+ASM1] ?

SQL> select PXN_KFFXP,  -- physical extent number
           XNUM_KFFXP, -- virtual extent number
           DISK_KFFXP, -- disk number
           AU_KFFXP    -- allocation unit number
    from X$KFFXP
    where DISK_KFFXP=1;

no rows selected

I cleared the disk header by dd:

# dd if=/dev/zero of=/dev/oracleasm/disks/DISK1 bs=1024 count=100
100+0 records in
100+0 records out
102400 bytes (102 kB) copied, 0.000156 seconds, 656 MB/s

Note: My allocation unit is 1MB that is why I am using bs=1024.

Then deleted disk by oracleasm:

# oracleasm deletedisk DISK1
# oracleasm scandisks    <--Do this step on all nodes

To tell the truth it is not a good way to solve the problem, because when I run the following command on the second node:

/sbin/blkid | grep oracleasm

/dev/sdg1: LABEL="DISK6" TYPE="oracleasm"
/dev/sdb1: LABEL="DISK1" TYPE="oracleasm"
/dev/sdh2: LABEL="DISK8" TYPE="oracleasm"
/dev/sdh3: LABEL="DISK9" TYPE="oracleasm"
/dev/sdh4: LABEL="DISK10" TYPE="oracleasm"
/dev/sdh1: LABEL="DISK7" TYPE="oracleasm"
/dev/sdi1: LABEL="DISK11" TYPE="oracleasm"
/dev/sdi2: LABEL="DISK12" TYPE="oracleasm"

It shows me the label DISK1.

So clearing the ASM disk header using dd left some junks.

I solved it by logging off from ASM and changing directory away from /dev/oracleasm/disks. Afterwards it worked ok.


Where to find the OID of a newly created Schema I have created a new Schema in my Postgres db. I want to replace an OID in the pg_class table with the OID of the new Schema. But I don't know where to find the OID of the newly created Schema. Can somebody help me with the right queries?

Do NOT change the system catalogs manually!

If you want to move a table to a different schema, there is no need to know the  oid of the table or the schema:
As documented in the manual you can easily do this through ALTER TABLE
ALTER TABLE old_schema.some_table
    SET SCHEMA new_schema;

without having to check or know any oid.

You can run this query to get OID.

SELECT oid FROM pg_class WHERE relname = 'tbl_name' AND relkind = 'r';


ORA-03113 when starting up an Oracle 11g database I have read "ORA-03113: end-of-file on communication channel" on startup and others from this site to solve my problem but I still have not solved it. All I get is the following:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Windows\system32>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jun 28 17:28:20 2014

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area  209235968 bytes
Fixed Size                  1332188 bytes
Variable Size             125832228 bytes
Database Buffers           75497472 bytes
Redo Buffers                6574080 bytes
Database mounted.
SQL> alter system set db_recovery_file_dest_size=35G scope=both;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  209235968 bytes
Fixed Size                  1332188 bytes
Variable Size             125832228 bytes
Database Buffers           75497472 bytes
Redo Buffers                6574080 bytes
Database mounted.
Database opened.
SQL> select instance_name from v$instace;
select instance_name from v$instace
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 43880
Session ID: 170 Serial number: 5

SQL>

Environment: Windows 7 64 bit, Oracle 11g.

Here are the details from alerts.log:

Sat Jun 28 17:32:26 2014
Flush retried for xcb 0x8a6711a8, pmd 0x8afba968
Doing block recovery for file 3 block 2686
Block recovery from logseq 13, block 68 to scn 132386303
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13 Reading mem 0
  Mem# 0: E:\APP\TRUCNGUYEN\ORADATA\XDLL\REDO01.LOG
Block recovery completed at rba 13.69.16, scn 0.132386305
Errors in file e:\app\trucnguyen\diag\rdbms\xdll\xdll\trace\xdll_pmon_55932.trc  (incident=36103):
ORA-00600: internal error code, arguments: [4194], [22], [28], [], [], [], [], []
Errors in file e:\app\trucnguyen\diag\rdbms\xdll\xdll\trace\xdll_pmon_55932.trc:
ORA-00600: internal error code, arguments: [4194], [22], [28], [], [], [], [], []
PMON (ospid: 55932): terminating the instance due to error 472
Sat Jun 28 17:32:27 2014
Errors in file e:\app\trucnguyen\diag\rdbms\xdll\xdll\trace\xdll_q001_79168.trc:
ORA-00472: PMON  process terminated with error
ORA-1092 : opidrv aborting process Q001 ospid (2836_79168)
Sat Jun 28 17:32:27 2014
ORA-1092 : opidrv aborting process W000 ospid (2836_56844)
Sat Jun 28 17:32:29 2014
ORA-472 : opidrv aborting process S000 ospid (2836_85052)
Instance terminated by PMON, pid = 55932

I'm a newbie in Oracle. I would really appreciate any direction and especially step by step instructions to fix this.

I have run the commands:

recover database;
alter database open;

but it still has the same error when I run:

select instance_name from v$instance;

From
ParnassusData:

  This error indicates that a mismatch has been detected between redo records and rollback (undo) records.

  ARGUMENTS:

  Arg [a] - Maximum Undo record number in Undo block

  Arg [b]  -  Undo record number from Redo block

  Since we are adding a new undo record to our undo block, we would expect that the new record number is equal to the maximum record number in the undo block plus one. Before Oracle can add a new undo record to the undo block it validates that this is correct. If this validation fails, then an ORA-600 [4194] will be triggered.

  Oracle is unable to roll back an uncommitted transaction in its transaction table. You probably have corruption in either the rollback/undo segment or corruption in the object in which the rollback/undo segment is trying to apply the undo record.

  Refer to Note: 39283.1 for more details on the description of this error.

Oracle Note  39283.1 says more or less the same.

Here is a link where somebody solved the problem by creating a new  undo tablespace: SAP, Databases and other IT stuff

Here are the statements:

-- Creation of a new Undo tablespace:
CREATE UNDO TABLESPACE undotbs
DATAFILE 'c:\oracle\10201\dev\undotbs1.dbf'
SIZE 300M AUTOEXTEND ON NEXT 20M
MAXSIZE 800M;

-- Change the default undo tablespace to the new one
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs SCOPE=BOTH;

-- Drop the problematic undo tablespace
DROP TABLESPACE oldundotbs;

To create a tablespace  the database must be open.

I don't know how to reproduce this error so I cannot test how to repair the system. I also think I won't get any feedback from the OP. But nevertheless let's think about, how we can proceed.

The best is to open a service request at Oracle Support.
One possibility is to restore the undo tablespace. But maybe the error will occur again.
Then a point in time restore of the whole database will be an option. But this means loss of data.
We will try to repair the system by creating a new undo tablespace an dropping the old one as described above.

To repair the system we unset the UNDO_TABLESPACE parameter. Then the SYSTEM tablespace becomes the default tablespace and I assume the database can be opened now.

startup nomount
alter system reset undo_tablespace scope=spfile;
shutdown
startup

Now we can proceed as above and create and set a new undo tablespace an drop the problem tablespace as described above.

It does not look good. Such an internal error points to something serious, maybe corruption. Try this:

startup mount;
recover database;
alter database open;

I think you'll have a better chance of finding an answer on metalink (Oracle support), if you are entitled. Otherwise on the oracle community forums (where I found the suggestion about trying the recovery).

Do you need the data? If yes, do you have a backup of it? You could drop the database and then recreate it, so that you will see if the problem is linked to your installation or to a corruption of the database.


SQL geography::point null parameter error when parameter is not null? I was getting this error:

'geography::Point' failed because parameter 1 is not allowed to be null.

On this sql:

SELECT [ID], geography::Point([lat], [long], 4326) AS [loc]
FROM (
  SELECT [ID], CONVERT(float, [lat]) AS [lat], CONVERT(float, [long]) AS [long]
  FROM (
    SELECT [ID], [lat],
    [long], ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [EFFDT] desc) AS [sequence]
    FROM [GEO]
  ) AS temp1
  WHERE [sequence] = 1
  AND [lat] IS NOT NULL
  AND [long] IS NOT NULL
) AS temp2
ORDER BY [ID]

But there where no null values, and I was only getting the error on our production machine (Production 13.0.4422.0) and not on our development machine (Dev 13.0.1728.2). After hours of searching and retrying I found that by reordering some stuff this would work:

SELECT [ID], [loc]
FROM (
  SELECT [ID],
    geography::Point([lat], [long], 4326) AS [loc],
    ,ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [EFFDT] desc) AS [sequence]
  FROM [GEO]
  WHERE [lat] IS NOT NULL
  AND [long] IS NOT NULL
) AS temp
WHERE [sequence] = 1

I would really like to understand what I was doing wrong in the first query and why this worked in the second query?

The first query I tried to get only the rows that have the most recent records based on a sequence number calculated using an effective date column, and then convert lat and long data to a geography type.

In the second query I convert the lat and long data and then filter.

I thought I was being more efficient with the first query.

I have struck similar issues with Geometry and Geography data types before.  I think the issue is to do with where (and how) the optimizer decides to build the geometry in the plan.

I was having an issue with insert statement that was basically

INSERT INTO (ID,GEOM)
SELECT ID, Geometry::Point(ISNULL(X,0),ISNULL(Y,0),0)
FROM HeapTable
WHERE X is not NULL and Y is not NULL

After playing around and getting a little confused found that

INSERT INTO (ID,GEOM)
SELECT ID, Geometry::Point(ISNULL(X,0),ISNULL(Y,0),0)
FROM HeapTable;

INSERT INTO (ID,GEOM)
SELECT ID, Geometry::Point(ISNULL(X,0),ISNULL(Y,0),0)
FROM HeapTable
WHERE ISNULL(X,0) != 0;

both work but the following doesn't

INSERT INTO (ID,GEOM)
SELECT ID, Geometry::Point(X,Y,0)
FROM HeapTable
WHERE ISNULL(X,0) != 0;

For the first query the plan shows that the compute scalar is done before the filter

And the expression being computed has had the ISNULL function stripped from it.

<ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="[Geometry]::Point(CONVERT_IMPLICIT(float(53),[sandbox].[dbo].[TestNullGeomIssueSource].[X],0),CONVERT_IMPLICIT(float(53),[sandbox].[dbo].[TestNullGeomIssueSource].[Y],0),(0))">

I think the Point on the Geometry and Geography is flawed in not allowing NULLs to be input.  Simply return a NULL as a result would be more correct in my opinion.

If you wish I can put up my entire test script for this.

One more thing to keep in mind. It is not a matter that your coordinates are not null. They also need to make sense.

So if you are using lat/long, then latitude cannot exceed 90 or -90. Longitude cannot exceed 180 or -180.

Be sure your lat/long are not transposed.


How to query data in MySQL and be able to save in SQL Server? (Python) Good day I have a doubt, I have a code generated in Python which he does is to query a MySQL table named INFORMATION_SCHEMA.PROCESSLIST as follows:

SELECT *, IF (time> = 10, "Bad", "Good") AS State FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id <> connection_id ();

Once this fulfills my conditions by means of python code I tell you to save those processes and also kill them with the command KILL, but the data that I send to make them in a table that creates in MySQL.

Now what I want is to continue browsing in MySQl, but I want to know if there is any way to save the queried data to a table but to SQL Server, basically query MySQL and save it to SQL Server.

Annex the code:

#! / Usr / bin / python
# - * - coding: UTF-8 - * -
Import os, sys
Import signal, os, sys
Import MySQLdb
connection_mysql = MySQLdb.connect (host = '10 .248.204.43 ', user =' root ', passwd =' sahc ', db =' Library ')

cur = conexion_mysql.cursor ()
query = ('SELECT *, IF (time> = 10,' Bad ',' Good ') AS State FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id <> connection_id ()')
cur.execute (query)
for row in cur.fetchall ():
    result = row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8]
    sql = "INSERT INTO test VALUES (" + str (row [0]) + "," + "'" + (row [1] or' ') + "'" + "," + " [2] or "+", "+", "+", "+ (row [3] or '') +" '"+", "+" ) + "," + "," + "," + ", +", "+", "(Row [6] or '') [7] or '') + "'" + "," + "now ()" + ")"
    cur.execute (sql)
    connection_mysql.commit ()
    print "Successfully Saved Data"
    if row [0] == row [0]:
        kill = "KILL" + str (row [0])
        cur.execute
        connection_mysql.commit ()
        print "Deleted data"

As you consult and save in MySQL, I want to continue browsing in MySQL but save the data in SQL Server.

I would appreciate the help :)

There is an option in MSSQL called linked server, where you can connect to MySQL server by using MSSQL queries. So you can query data from MSSQL linked server (MySQL data) and insert data into MSSQL.

I hope this works for your query. Give a try.


SQL Server equivalent to functionality of Oracle RAC? I did some Googling and couldn't find an answer to this question more recent than a few years ago, so I thought I'd ask. Oracle's RAC feature offers load-balancing for both read and write transactions, as well as scale-out and high-availability without downtime (at least, as I understand it - we're about to deploy our first databases that use RAC, so we'll see how it goes).

Is there any SQL Server feature set (or third party component you could install on top) that delivers equivalent functionality? We've always used Windows clustering, where a failover event causes about 20-30 seconds of SQL downtime - always tolerable, but not ideal. Now, with AlwaysOn in SQL 2012, SQL Server shrinks that to about 15 seconds and adds the concept of read-only-secondary databases, but they still require that write transactions are choked through a single connection point (much improved, since many transactions are just read, but still not really load balancing), and in the case of a node failure or the need to patch, there's still downtime.

I suppose it's just more curiosity - I feel like this is the only area that SQL Server falls behind Oracle (at least among the features I've personally seen used). I wanted to see if there are any options out there to close that gap and possibly improve our own SQL Server deployment while we wait for Microsoft's equivalent feature to be added - maybe in SQL 2014/2015?

No, nothing in SQL Server can give you the same thing out of the box.

Currently, the only SQL Server technology that allows simultaneous writes at multiple nodes is Peer-to-Peer replication. Note that I did not to say "write scale-out," because it works in a way such that the entire system has the write capacity of only a single node. The introductory paragraph of that page is worded carefully to include the term "scale-out" without saying "write scale-out." Yay for marketing-speak.

From what I've read, Oracle RAC is the same in that respect. Functionally, the only thing that's missing from SQL Server in this configuration is a load-balancing solution, which is both an advantage (you can implement it any way you want) and a disadvantage (it's not in the box or supported by Microsoft) when comparing against competing products.

Conversely, Oracle RAC does not give you the same thing as SQL Server out of the box either. For example, RAC is recommended to be implemented with nodes within 100km of each other because of realtime network latency, whereas Peer-to-Peer replication has no such restriction. (I'm not saying one solution is better than the other: I'm just pointing out that they are different. The business should choose a solution that best fits their specific needs.)

SQL Server 2012/2014 with AlwaysOn adds a lot of capabilities that get you close to Oracle RAC - and in some cases improve on it. (Recall that with RAC, you need to be using Oracle app server, weblogic, and JDBC - plus operating in a single data center and the app can connect to only one RAC cluster - shared storage means RAC doesn't work in the cloud).

With AlwaysOn you get read-only secondaries (4 in 12, 8 in 14) and auto failover support. A few things are challenging though - AlwaysOn does not support load balancing - unless you write a script, it'll always draw the first server on the list. Failover also impacts the app - it's not transparent so apps may have to be restarted.

Full disclosure - I work for a company that makes software that augments AlwaysOn. Our database load balancing software front ends SQL Server - it does read/write split on behalf of the app, does response-time based load balancing that spans data centers, and queues inbound writes/transactions during failover so that apps don't see errors. See how Microsoft, Dell, Quicken Loans, and other enterprises are using ScaleArc software to augment SQL Server AlwaysOn and get RAC-like capabilities with no app changes.


Querying special characters I work with language with special characters (example: e -> é). I have a query with a LIKE but when I search a word with special char and my query doesn't have the special char, I don't get the results I expect:

select * from a where name like '%e%'

The above returns no results when the database contains "dés". Is there a way to have this query return the desired results.

If the collation of your database is set to accent insensitive, then this should work (for most accented characters).

For example in the collation Latin1_General_CI_AS, the CS means Case Insensitive, Accent Sensitive.  You would want the collation to be Latin1_General_CI_AI which means Case Insensitive, Accent Insensitive

Be warned though, that not all "accented" characters are created equally, but for spanish and french searches I found things work out as expected.

I finally replace special char that I know by a %


Get data from both tables what join should I use I have T1 as table 1

----------------
field1 | field2
---------------
 val1  | val2

I have T2 as table 2

----------------
field1 | field2
---------------
 val11  | val22

I want to write a query which can produce result like this

 field1 | field2 | T1.T2 |
 ------------------------|
 val11  | val22  | T2    |
 val1   | val2   | T1    |

Simply, you can use a CTE or SubQuery then ORDER BY [T1.T2] DESC as

WITH CTE AS
(
  SELECT Col1, Col2, 'T1' [T1.T2]
  FROM T1
  UNION
  SELECT Col1, Col2, 'T2'
  FROM T2
)
SELECT *
FROM CTE
ORDER BY [T1.T2] DESC;

Live Demo

Maybe this query is useful... I used union to join the 2 tables.

mysql> select field1 ,field2 from T2 union select field1, field2 from T1;


How to find SQL Server database is in use or not? What query do I need to write to know if the database is in use or not? I tried following query

SELECT
    name
FROM
    sys.databases
WHERE
    state != 0;

but it is not working.

I want to check there should not have a open connection with that database and should not executing a query against that DB

In order to use a database any session must first obtain a lock on it, usually a shared lock. So simply look for such locks:

select *
from sys.dm_tran_locks
where resource_type = 'DATABASE'
and resource_database_id = <dbid>;

To check how many Connections are open for the given database:

SELECT
  DB_NAME(dbid) as DBName,
  COUNT(dbid) as NumberOfConnections,
  loginame as LoginName
FROM
  sys.sysprocesses
WHERE
  dbid > 0
GROUP BY
  dbid, loginame


Is it possible to define a foreign key with duplication and cascading delete only if no values exist? I have the following scenario:

A survey table with the columns survey_type and survey_version.
(survey_type, survey_version) is a primary key of the survey table.

I also have a request table. It contains requestIDs that are associated with a survey_type. Whenever a user initiates a survey for a certain requestID, they will get the most up to date survey_version.

I wish to enforce the restriction that allows creation of requests only for survey_types that actually exist. I also wish for requests to be deleted if the associated request_type is removed from the system. A FOREIGN KEY with ON DELETE CASCADE is almost a perfect solution, except that I need the cascading to happen only if all records with a certain survey_type are removed. If we are only removing an old and no longer used version of a survey_type, but records of that survey_type still exist in the survey table, then the survey request should also remain in the system.

Is this possible to do with FOREIGN KEY constraints, or should I look for another way?

Is this possible to do with FOREIGN KEY constraints, or should I look for another way?

Look another way. I'm not 100% I understand what's happening from that description but ON DELETE CASCADE quite explicitly means "allow the deletion to cascade". There are no buts about it.

Or, just make sure in the app, you check -- in a transaction -- that there are no remaining survey_type's then you can delete and allow it to cascade naturally.


SQL Server data files in Windows Azure: filegroups and partitioning still applicable? I just created a database using the new functionality "SQL Server 2014 data files on Windows Azure" and this works quite well.

However, I have a table with a billion records and in our local environment we have partitioned this table into 33 filegroups, my question is whether to store as Azure blob and also is it worth partitioning it into filegroups for higher performance?

My understanding is that Azure's storage is different from that in our environment, so, can we apply the same techniques to improve performance?

If you anticipate that your database file(s) will exceed more than 1TB in size you should configure the server to put the data files across multiple VHDs.
How can spread my databases across multiple VHDs?

Create one or more dynamic volumes consisting of multiple VHDs at the operating system level and put your database files on these volumes.
Split your tables and indexes into separate database filegroups and then place those filegroups in different files (MDF + NDF) which are spread across separately attached VHDs. Note that this will not offer any benefit for transaction logs because SQL Server does not stripe across transaction log files but uses them sequentially.

If you are migrated to Azure and want to compare the performance of your databases before and after the migration then take a SQL Server baseline(enter link description here)


MySQL join two large tables is very slow I have two tables, one of them contains a history of downloaded urls, while the other table contains details about each url.

The following query groups the URLs by the number of repetitions during the past hour.

SELECT COUNT(history.url) as total, history.url
FROM history
WHERE history.time > UNIX_TIMESTAMP()-3600
GROUP BY history.url
ORDER BY COUNT(history.url) DESC
LIMIT 30

The query above takes about 800ms to execute, not fast enough, but acceptable,

however, when joining it with the cache table, the new query takes about 25s to execute, which is very slow.

SELECT th.total, th.url, tc.url, tc.json
FROM (SELECT COUNT(history.url) as total, history.url
      FROM history
      WHERE history.time > UNIX_TIMESTAMP()-3600
      GROUP BY history.url
      ORDER BY COUNT(history.url) DESC
      LIMIT 30
) th
INNER JOIN (SELECT cache.url, cache.json FROM cache) tc
    ON th.url = tc.url
GROUP BY th.url
ORDER BY th.total DESC
LIMIT 30

I think that this might be happening because in 'tc', the whole cache table is being loaded, and it contains 1million+ entries.

When I use the first query, and then programmatically iterate the results and then run a SELECT query from cache for each result, it is much faster. Is there anyway to speed up my second query?

P.S. I'm using InnoDB

UPDATE
The output of the second query with EXPLAIN

The structure of 'history' table

The structure of 'cache' table

It is generally a good idea to index columns that participate in JOIN predicates or WHERE clauses. A common mistake is to create several one column indexes instead of fewer multi-column indexes. Here we can benefit from both url and time in history (look at all queries against these tables and you may find that you can add additional columns to these indexes):

CREATE INDEX x01_history_url ON HISTORY (URL, TIME);
CREATE INDEX x01_cache_url ON CACHE (URL);

Second, try to unnest the queries. MySQL has limitations on what kind of query rewrites it is able to do, so the nesting may cause unnecessary overhead.

SELECT COUNT(th.url) as total, tc.url, tc.json
FROM history th
JOIN cache tc
    ON th.url = tc.url
WHERE th.time > UNIX_TIMESTAMP()-3600
GROUP BY tc.url, tc.json
ORDER BY COUNT(th.url) DESC
LIMIT 30

Note that this query is semantically different from your query so you might get a different result. If that is a problem you may want to keep the LIMIT 30 construction in a subquery as before. You may also consider if you can add a similar limit to CACHE, is there an upper bound on how many CACHE rows you must investigate to get 30 rows in total?

INNER JOIN (SELECT cache.url, cache.json
            FROM cache
            ORDER BY ? LIMIT ?) tc

try this:

SELECT * FROM (
  SELECT COUNT(history.url) as total, history.url
  FROM history
  WHERE history.time > UNIX_TIMESTAMP()-3600
  GROUP BY history.url
  ) tmp
ORDER BY total DESC
LIMIT 30


How to systematically export all tables from a DB2 database schema to csv Is there a way using a DB2 database to systematically export all tables from a schema to separate csv files ? What I mean is to export all the tables in a schema without specifying table names. Maybe somehow getting all table names in a schema, then applying the export on those

You can easily create a small script that does this for you, or even do a loop on the command line. Assuming you are on a unix/linux platform something like below should do.

#> db2 connect to mydb

#> for t in $(db2 -x "select rtrim(tabschema) || '.' || rtrim(tabname) from syscat.tables where tabschema = 'DB2INST1'"); do db2 "export to $t.csv of del modified by coldel, select * from $t"; done

You can replace DB2INST1 with whatever schema you are interested in. If you want tables from several schematas you can use in:

#> for t in $(db2 -x "select rtrim(tabschema) || '.' || rtrim(tabname) from syscat.tables where tabschema in ('DB2INST1', ...)"); do db2 "export to $t.csv of del modified by coldel, select * from $t"; done

There are a lot of options that you can use to format the output from export, here I use modified by coldel but there is much more that you can configure. See:

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0008303.html

for more info.

Maybe try something like this:

db2move sample export -aw -l lobs -ts USERSPACE1


How to add a new target in Oracle Enterprise Manager 12 C I just installed Oracle Enterprise Manager 12 C Grid Control from a ready made image that Oracle provides and now I am trying to add targets to it (monitoring targets i mean).

It might be a stupid question but how can such a thing be done ? I don't seem to find any tutorials on this and the Oracle docs are not that clear.

Well this task i am afraid it can not be described here, i mean it could but is to much to write and i will not re-invent the wheel.:)
 So i recommend you follow this article How to add a new target on OEM , i wrote this article with this purpose(help guys like you ! kkkkk ).
 i might get a down vote for my short answer ! but for those intrested  -- fell free to read the article !! And thare a lot's of them on Oracle Enterprise Manager 12 C .


What is the best approach to index varchar and date range? There is an order table which stores customer information and location. I need to filter the customer's last five locations within the last six months based on order status.

Query as follows,

SELECT *
FROM orders
WHERE (    status = 4
       AND receiver_phone = '91xxxxxxx'
       AND created_date BETWEEN '2017-08-06' AND '2017-03-01'
       AND NOT (    receiver_latitude = '10.9315615'
                AND receiver_latitude IS NOT NULL)
      )
ORDER BY created_date DESC
limit 5;

EXPLAIN ANALYZE:

    Limit  (cost=374121.96..374121.97 rows=1 width=449) (actual time=2151.298..2151.298 rows=0 loops=1)
   ->  Sort  (cost=374121.96..374121.97 rows=1 width=449) (actual time=2151.297..2151.297 rows=0 loops=1)
         Sort Key: created_date DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on orders  (cost=0.00..374121.95 rows=1 width=449) (actual time=2151.262..2151.262 rows=0 loops=1)
               Filter: ((created_date >= '2017-08-06 00:00:00+00'::timestamp with time zone) AND (created_date <= '2017-03-01 00:00:00+00'::timestamp with time zone) AND ((receiver_latitude <> 10.9315615) OR (receiver_latitude IS NULL)) AND (status = 4) AND ((receiver_phone)::text = '91xxxxxx'::text))
               Rows Removed by Filter: 4376895
 Planning time: 0.267 ms
 Execution time: 2151.917 ms

what are best approaches to optimize this query?

Seq Scan means sequential Scanning, PostgreSQL enum have to enum all records for filtering result.

You should use Btree index.
https://www.postgresql.org/docs/current/static/indexes-types.html

  B-trees can handle equality and range queries on data that can be sorted into some ordering


Bloated transaction log and .bak backup size confusion I recently inherited a poorly-managed SQL server.

One of the databases is in rough shape. Here are the details:

The .mdf file is a modest 369MB.
The .ldf file is a bloated 29.7GB.
The recovery model is set to FULL.
The data file is backed up nightly without issue, and is 365MB.
The transaction log has NEVER been backed up.

I've restored the nightly backup to the same SQL server and poked around in it to make sure data looks to be intact. When I restore that nightly .bak backup file it recreates the .mdf and .ldf files at 369MB and 29.7GB, respectively.

Being decent with SQL, but not what I would call an expert by any means, I have a few follow up questions on this situation that I am hoping to get feedback on:

How is a 369MB .bak file restoring both a 369MB .mdf file and a 29.7GB .ldf file?
Any suggestions on what to do regarding the recovery model and bloated .ldf file? We are only concerned about doing backups once nightly - we don't need to do differential backups at this time - but it's something I will tackle later.

How is a 365 .bak file restoring both a 369MB .mdf file and a 29.7GB
  .ldf file?

It's not restoring the data from the log file, or backing it up, it's just creating it with the same size as it was when backed up.  Check the free space and it should be 99% free.

  Any suggestions on what to do regarding the recovery model and bloated
  .ldf file? We are only concerned about doing backups once nightly - we
  don't need to do differential backups at this time - but it's
  something I will tackle later.

If you are only worried about doing backups once a night then change the recovery model to simple.  If you leave it in full you will need to perform log backups as well.  Once you switch the recovery model or start performing log backups you could do a one time shrink to get back the space.

1) Backups are typically compressed, they will be less than the size of your data and the size of your log.

2) If you run a database in full recovery model you need to back up your transaction logs or they will grow indefinitely. If you don't care about transaction log backups you should switch back to simple recovery.


Scheduled backup task doesn't always backup all databases despite always saying job successful I have a job in SQL 2008 that runs a stored proc to backup all databases. This runs daily via sql server agent job.

It quits with success every day but some days it quits with success only after backing up a few of databases. It can be different number of databases each time. Most days it successfully backups all databases but sometimes 2 backup successfully, sometimes 5, etc.

I don't see any errors in job history, event viewer or sql server log.

Backups are taking place to a local disk, although the folder is a "junction" to a folder on an expandable storage volume.

OS is Windows 2003 64bit running Sql Server 2008 web edition 64 bit as a virtual machine running on Vmware ESXi 5 host.

Stored Procedure:

ALTER PROCEDURE [dbo].[backup_all_databases]
@path VARCHAR(255)='c:\backups\'

AS

DECLARE @name VARCHAR(50) -- database name
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @dbIsReadOnly sql_variant -- is database read_only?
DECLARE @dbIsOffline sql_variant -- is database offline?

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('tempdb')
AND version > 0 AND version IS NOT NULL

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '.bak'

SET @dbIsReadOnly = (SELECT DATABASEPROPERTY(@name, 'IsReadOnly')) -- 1 = Read Only
SET @dbIsOffline = (SELECT DATABASEPROPERTY(@name, 'IsOffline')) -- 1 = Offline

IF (@dbIsReadOnly = 0 OR @dbIsReadOnly IS NULL) AND @dbIsOffline =0
BEGIN
    BACKUP DATABASE @name TO DISK = @fileName  WITH INIT
    WAITFOR DELAY '00:00:20'
END

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Any suggestions please?

I would add TRY/CATCH blocks to handle errors and log them. The DB could be in single user, being restored or whatever.

Without this, errors can abort in a way that no errors are logged (statement, batch, scope, connection etc)

With TRY/CATCH then everything except for compile or connection aborting errors are logged? but I doubt this is the case.

I'd also use sys.databases which replaces sysdatabases and read more flags:

-- declares etc

BEGIN TRY

    DECLARE db_cursor CURSOR FOR
    SELECT name, state, user_access
    FROM sys.databases
    WHERE name NOT IN ('tempdb')

    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @name, @state, @user_access

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @fileName = @path + @name + '.bak'
        IF @state = 0 AND user_access = 0
        BEGIN
            BEGIN TRY
                BACKUP DATABASE @name TO DISK = @fileName  WITH INIT
            END TRY
            BEGIN CATCH
                -- log but do not rethrow so loop continues
            END CATCH
            WAITFOR DELAY '00:00:20'
        END
        ELSE
           --log user and/or state issues

        FETCH NEXT FROM db_cursor INTO @name
    END

    CLOSE db_cursor
    DEALLOCATE db_cursor

END TRY
BEGIN CATCH
  -- some useful stuff here
END CATCH

I have tried to figure out this problem and it seems many times users posted the solution that if you make the cursor declaration insensitive then it starts working. So I tested it and yes it does just make sure you cursor declare is static and it starts working.

The fact for which it fails is -
Check your server level cursor threshold setting - if it is configured as -1 then it means all cursors are getting populated synchronously in other words while trying to read cursor key set data is synchronous and all is tried to read at same time. If we change this value to 0 that tells SQL server to do a asynchronous population in simple words cursor can fetch the records while key set will still be populating and you will see that after doing this change at server level you will never miss any database using cursors.

Solutions:
Either declare Cursor Static
Or Change Server level setting "Cursor Threshold" to 0 from -1.

Thanks,
Gaurav Mishra | Senior DBA


How could I connect to MySQL database running on WAMP server from other computers in same LAN? I'm new in web development. I'm requesting help from all of you. My problem is:

I installed WAMP server in my desktop computer. My desktop computer has a static LAN IP (192.168.0.101).

I'm able to access WAMP server homepage from my other computers connected to the same network by using the LAN IP (192.168.0.101).
But while I was trying to access phpMyAdmin from other computer it says:

  Forbidden. You don't have permission to access/phpmyadmin/ on this server. Apache/2.4.9(Win64) PHP/5.5.12 Server at 192.168.0.101 Port 80.

I also tried to connect to the MySQL database running on my desktop computer using cmd in other computer by the following code, mysql -h 192.168.0.101 -u root -p (there is no password for root user).

Please help me how could I be able to access phpMyAdmin and MySQL database from other computer connected to the same network.

WAMPServer is primarily designed for use on a single machine i.e. a developer writing and testing code locally on that PC.

So to protect the beginner from his/her self the Apache & MySQL configuration by default is setup so no access can be made to the Apache or MySQL server form anywhere but the PC running Apache and MySQL.

When changing this configuration great care should be taken to only allow access that you intend and not global access. So be VERY CAREFUL with any help/tutorial that suggest adding Allow from all or Require all granted as they either dont really know what they are doing or the help is based on giving access to the site to the whole internet.

To allow access to phpMyAdmin you need to change the Alias configuration for phpMyAdmin, so edit wamp\alias\phpmyadmin.conf and add an instruction to allow access from any ip address on your internal network like so :-

Alias /phpmyadmin "d:/wamp/apps/phpmyadmin4.1.14/"

<Directory "d:/wamp/apps/phpmyadmin4.1.14/">
   Options Indexes FollowSymLinks MultiViews
   AllowOverride all
  <IfDefine APACHE24>
    Require local
-->     Require ip 192.168.0
  </IfDefine>
  <IfDefine !APACHE24>
    Order Deny,Allow
      Deny from all
      Allow from localhost ::1 127.0.0.1
-->     Allow from 192.168.0
    </IfDefine>
  php_admin_value upload_max_filesize 128M
  php_admin_value post_max_size 128M
  php_admin_value max_execution_time 360
  php_admin_value max_input_time 360
</Directory>

If you actually want to access MySQL via the mysql.exe processor and other tools like it from another IP address you have basically the same problem.

MySQL user account are made up of 2 parts, a UserId and a Host. The Host part tells MySQL where this userid is allowed to connect from. If you look using phpMyAdmin at the user accounts that are pre-created in MySQL you will see that the Host part of all the account is either 127.0.0.1 or localhost or ::1 all of which equate to The PC running MySQL. Again for the security of the beginner no access allowed from outside the PC running MySQL.

So to use mysql.exe etc, I suggest you create a new MySQL User Account, lets say MAKhan with a Host of 192.168.0 AND OF COURSE A PASSWORD. That account will them be allowed to login to MySQL from any ip on your local network. You will of course have to give rights to this new account so that it can access some or all of the databases on the MySQL server.

AS above mentioned Change in this file
wamp\alias\phpmyadmin.conf

"Require local" Changed to => "Require all granted"
 "Deny from all" Changed to => "Deny from none"
 "Allow from localhost ::1 127.0.0.1" Changed to => "Allow from all"

Restart The Server Don't Change other things
Other Pc will Surely able to access the phpmyadmin Page

If you are not able to login then check from the mysql server root user host value in mysql-DB user table


lsnrctl: cannot execute binary file: Exec format error When I type lsnrctl from my command prompt the below error message is displayed:

oracle@Gaurav:~$ lsnrctl
bash: /u01/app/oracle/product/12.1.0/dbhome_1/bin/lsnrctl:
cannot execute binary file: Exec format error

Can anyone please provide me lsnrctl for oracle 12c, so that I could replace it?

First, don't worry about the comments. Your question or problem is total valid.

Second, there is a comment about "ubuntu isn't supported so your question should be deleted" which isn't right. If ubuntu isn't "supported", that means you will have probably more problems if you try to install it, and Oracle won't help you. But Oracle never can be really easy installed (I am thinking some like so easy as typing as an apt-get install oracle-11.2-server or like, which is easy. Sucking with a monstrous and buggy java installer, which Oracle DB has, isn't easy), not for the so-named "supported" distributions/systems and not for the "unsupported" ones. Second, your problem is still a valid database administration problem, even if you use ubuntu or not.

About your problem: it is really clear. lsnrctl had to be runnable. This error message has the very clear meaning: your lsnrctl has an invalid binary format in your architecture. For example, you are trying to run a 64-bit binary on a 32-bit system. Or you try to run an i386 binary on a ia64 system. Or your lsnrctl binary is somehow damaged.

The command file -szL /u01/app/oracle/product/12.1.0/dbhome_1/bin/lsnrctl could make clear, which is the real problem, thus what is happened to your lsnrctl. But it is very probable, that simply you have a bad distribution installed, and so you need a reinstall, give a try to this command before.


postgres user cannot drop database Im trying to drop some databases on a postgres 9.6 server, but when I try Im told I am unable to.

I was under the impression that the postgres user was like a superuser.

su postgres
bash-4.2$ psql
could not change directory to "/root": Permission denied
psql (9.6.8)
Type "help" for help.

postgres=> DROP DATABASE d;
ERROR:  must be owner of database d
postgres=>

The problem with su postgres command, you changed the user to posgress but bash (to be more specific environment) owner still root or the original user, in-order to fix this issue use su - postgres instead of su postgres


Database Implementations of ORDER BY in a Subquery I am using an application (MapServer - http://mapserver.org/) that wraps SQL statements, so that the ORDER BY statement is in the inner query. E.g.

SELECT * FROM (
        SELECT ID, GEOM, Name
        FROM t
        ORDER BY Name
        ) as tbl

The application has many different database drivers. I mainly use the MS SQL Server driver, and SQL Server 2008. This throws an error if an ORDER BY is found in a subquery.

From the MS Docs (although this is for SQL Server 2000 it still seems to apply):

  When you use an ORDER BY clause in a view, an inline function, a
  derived table, or a subquery, it does not guarantee ordered output.
  Instead, the ORDER BY clause is only used to guarantee that the result
  set that is generated by the Top operator has a consistent makeup. The
  ORDER BY clause only guarantees an ordered result set when it is
  specified in the outermost SELECT statement.

However the same type of query when run in Postgres (9) and Oracle return results - with the order as defined in the subquery. In Postgres the query plan shows the results are sorted and the Postgres release notes include the item which implies subquery orders are used:

  Avoid sort when subquery ORDER BY matches upper query

http://en.wikipedia.org/wiki/Order_by states:

  Although some database systems allow the specification of an ORDER BY
  clause in subselects or view definitions, the presence there has no
  effect.

However from my own checking of query plans:

SQL Server 2008 does not support ORDER BY in a subquery
Postgres 9 does support ORDER BY in a subquery
Oracle 10g supports ORDER BY in a subquery

So my question are there any links that can officially confirm or deny that Postgres and Oracle do not allow sorting in a subquery?

You're going to have to make your application not put the ORDER BY inside the subquery (maybe it has an option to not use a needless subquery in the first place). As you've already discovered, this syntax is not supported in SQL Server without TOP. And with TOP, unless you want to leave some rows out, using TOP 100 PERCENT is going to render the ORDER BY optimized away anyway.

And in Oracle and PostGres, just because the syntax is supported, does not mean it is obeyed. And just because you observe it as being obeyed in some scenario, does not mean that it will continue to be obeyed as new versions come out or with subtle changes to your data, statistics, the query itself, or the environment.

I can assure you that, without a doubt, if you want a guarantee about order, you need to put the ORDER BY on the outermost query. This should be a doctrine you hold close no matter what platform you're using.

You are asking for a link that officially states that something is not supported. This is like looking in your car owner's manual for an official statement that your car cannot fly.

I admit this is sleazy but if you're in a pinch try returning the top number of rows in the subquery. Returning the top 100 percent doesn't work but if you want to go through the trouble you can query the number of rows and pass that to TOP as a variable. I tested this on a database set to compatability level 80 so I think it should work with SQL 2000.

SELECT * FROM (
        SELECT TOP (100000) ID, GEOM, Name
        FROM t
        ORDER BY Name
        ) as tbl


Advice on diagnosing a "sometimes" slow query I have a stored procedure which returns results from an indexed view via a covering index. Usually, it runs fast (~10ms), sometimes it can run up to 8 seconds.

Here's an example random execution (note: this isn't a slow one, but the query text is the same apart from the value passed through):

declare @p2 dbo.IdentityType
insert into @p2 values(5710955)
insert into @p2 values(5710896)
insert into @p2 values(5710678)
insert into @p2 values(5710871)
insert into @p2 values(5711103)
insert into @p2 values(6215197)
insert into @p2 values(5710780)

exec ListingSearch_ByLocationAndStatus @statusType=1,@locationIds=@p2

Here's the SPROC:

ALTER PROCEDURE [dbo].[ListingSearch_ByLocationAndStatus]
    @LocationIds IdentityType READONLY,
    @StatusType TINYINT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT      -- lots of fields
    FROM        [dbo].[ListingSearchView][a] WITH (NOEXPAND)
    INNER JOIN  @LocationIds [b] ON [a].[LocationId] = [b].[Id]
    WHERE       [a].[StatusType] = @statusType
    OPTION (RECOMPILE);

(note: i added the OPTION (RECOMPILE) hint recently after some advice, but it hasn't helped.

Here's the covering index (note: the view also has a clustered index on ListingId, which is unique)

CREATE NONCLUSTERED INDEX [IX_ListingSearchView_ForAPI] ON [dbo].[ListingSearchView]
(
    [LocationId] ASC,
    [StatusType] ASC
)
INCLUDE ( -- all the fields in the query) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

I put a profiler trace on, with showplan XML statistics.

Here's a slow one (6 seconds), and the relevant plan:

Looks exactly as i'd expect, and is the same plan when the query is fast.

Here's the zoom in on the costly part of the plan, if that helps:

Here is the full schema of the view/backing tables, if that helps: https://pastebin.com/wh1sRcbQ

Notes:

Indexes have been defrag'd, statistics up to date.
Originally query was inline against the view, but i moved to SPROC to try and help stabilize. Hasn't helped.
Adding WITH OPTION (RECOMPILE); hint (didn't work, so can't be parameter sniffing?)
Other queries in the system also sometimes run slow, and they also have no obvious issues in their plan.
Could be locking? Not sure how to confirm.

Any ideas on what i could try next?

Thanks

I really don't think using the OPTION (RECOMPILE) is an effective way to eliminate the possibility of parameter sniffing.

Parameter sniffing happens when SQL is confused about a particular query and thinks its new because it sees new parameters.  It's slow because it's taking extra time to generate a new execution plan.

All that option does is force SQL to produce a new plan every time which is pretty much the same thing. Instead, you might want to consider adding default parameters using this hint:

OPTION(OPTIMIZE FOR(@LocationIds='xx',@StatusType='xx'))

When choosing parameters for the default make sure to use a statistically representative set.
That will force the same plan to be used every time and eliminate the possibility of parameter sniffing.  Once you do that, and determine it didn't help, then its probably safe to dismiss parameter sniffing as a possibility.

If you think the problem is in blocking I will suggest you to use optimistic transaction isolation level Read Committed Snapshot (take in mind that this will put overhead on your tempDB).

REFERENCE: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

It the problem is not in the read/write blocking, you can to try add indexes on your view (the best choice of indexes depends on selectivity of your data)

CREATE NONCLUSTERED INDEX IX_ListingSearchView (LocationID, StatusType) INCLUDE (other columns...)


Audit user activity in SQL Server Please advice how can I audit all actions of specific user in SQL Server 2008 EE? If it can be traced and written to log file? If the audit has the levels?

With regards!

If i understand correctly, you want to see all activity from a single user.

The quickest would be a profiler trace filtered to that user, start a trace and in the Event Selection and Column Filters add the user under "Like" in the LoginName filter, or NTUserName if appropriate.

Beware of overhead when using profiler in production it can be resource hungry.

If you want something more long term, you can look into auditing. Here is a useful tutorial for Select Statements but there is much more you can do with it.

Auditing Select Statements 2008

As sqlblog of jonathan_kehayias Here you can trace like who and from what machine a server side trace was created.

First execute the this TSQL query.

select
    trace_event_id,
    name
from sys.trace_events
where name like '%trace%';
Go

You shall get the output like here

The run the next TSQL query like

DECLARE @FileName VARCHAR(MAX)

SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1;

SELECT
    LoginName,
    HostName,
    ApplicationName,
    StartTime,
    TextData
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt
WHERE EventClass = 175;
Go

This information is tracked in the Default Trace and can be found by querying for EventClass 175 which is the Audit Server Alter Trace Event trace_event_id from sys.trace_events.


mysql view with user variables I have a query that calculate distance from point a to point b using gps coordinates, and would like to make it view.

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

Here's query.

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

check out this one: https://stackoverflow.com/questions/2281890/can-i-create-view-with-parameter-in-mysql

ot you can crate sp for this select
 http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html


How do you tune MySQL for a heavy InnoDB workload? Assuming a production OLTP system with predominantly InnoDB tables

What are the common symptoms of a mistuned/misconfigured system?
What configuration parameters do you most commonly change from their defaults?
How do you spot potential bottlenecks before there is a problem?
How do you recognize and troubleshoot active problems?

Any anecdotes detailing specific status variables and diagnostics would be appreciated.

Interestingly, in MySQL 5.5, you can now have multiple innodb buffer pools.
The parameters you care about are

innodb_buffer_pool_instances (default is 1)
innodb_buffer_pool_size (default is 1G)

In about a month, I am slated to implement 112 innodb buffer pools for a client. I'll let you know how it went.
UPDATE 2011-02-27 21:57 EDT
I found out the max value for innodb_buffer_pool_instances is 64
I decided to configure 144 GB , so I set innodb_buffer_pool_instances to 18 and innodb_buffer_pool_size to 8. I am currently loading the server with 450GB
UPDATE 2011-04-28 13:44 EDT
I tried out multiple InnoDB Buffer Pools. There was too much threading locking and contention. I changed over to a single 162GB Buffer Pool + setting read_io_threads and write_io_threads to 64 (maximum value). This worked way better.
UPDATE 2012-07-03 17:27 EDT
I learned something amazing about MySQL. If you allocate a single monolithic InnoDB Buffer Pool that is bigger that Total Installed Divided By Number of Physical CPUs, your will incite the OS to regular intervals memory swapping due to a full InnoDB Buffer Pool. MySQL 5.5's option known as innodb_buffer_pool_instances can be used to split up the buffer pool. Yesterday, I properly implemented this for the client I mentioned in my answer last year. I still have 162GB for the client's Buffer Pool. I have set the server's innodb_buffer_pool_instances option to 2 because each DB Server is dual hexacore. I was thinking of setting it to 12 but then a colleague showed me a blog from Jeremy Cole on MySQL and Swappiness. After reading it, I put it into practice immediately for my client. I ran this command
numactl --hardware

I saw a mapping of 192GB of server RAM as 96GB to each physical core. Therefore, I set the innodb_buffer_pool_instances to 2. Things are looking good right now. I will update my answer to see how this affects memory swapping for the next 2 montns.

What configuration parameters do you
  most commonly change from their
  defaults?

memory configuration


Percona Toolkit Installation Error [root@localhost ~]# rpm -ivh percona-toolkit-2.2.7-1.noarch.rpm
  error: Failed dependencies:   perl(DBD::mysql) >= 1.0 is needed by
  percona-toolkit-2.2.7-1.noarch    perl(Time::HiRes) is needed by
  percona-toolkit-2.2.7-1.noarch    perl(IO::Socket::SSL) is needed by
  percona-toolkit-2.2.7-1.noarch

How to solve above dependencies?

I got this same error on an Amazon Linux box (AWS EC2). Here's what I did to get Percona toolkit installed.

# Installs Percona's Perl deps...
yum install perl-Time-HiRes
yum install perl-DBD-MySQL
yum install perl-IO-Socket-SSL     # I actually didn't need this one,
                                   # but based on your error you probably will.

# Install Percona toolkit
rpm -Uhv http://www.percona.com/redir/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.7-1.noarch.rpm

I'd suggest getting the URL for the Percona toolkit RPM from their website under the "Download Latest" link. (Latest for me at the time of this post was 2.2.7.) The website offers RPM and deb formats; just choose RPM, and then use the link it shows.

I know this doesn't address the OP's problem as described, but it does address his root problem (can't get Percona toolkit installed). I agree with Peter's answer in that a package manager like yum or apt should be used... if for no other reason than handling nasty dependency hell, but I don't know Percona's yum or apt repo... or if they even still have one.

Edit: Also, those are just the Percona toolkit dependencies that I had missing on my box. You may have more or less. Another EC2 instance of mine already had all of the Perl dependencies installed, so all I needed to do was run the rpm command. A Percona repo sure would be great to have!

You need to install this with its dependencies.

yum install percona-toolkit

Tracking back the dependencies, checking the conflicts/replacements, and downloading the needed packages from the net isn't a trivial or low-level task. Thus not the rpm does this, but yum.


How to solve error "ORA:01-006 :bind variable does not exist" when working with bind variables? I have a table with below structure and data :
create table TEST_TABLE
(
  item_number NUMBER,
  item_name   VARCHAR2(50),
  item_col    VARCHAR2(50),
  item_pol    VARCHAR2(50)
)

Sample data:
item_number |  item_name|  item_col |  item_pol
------------------------------------------------
     1      |   blue    |     b     |   c
     2      |   red     |     d     |   a
     3      |   black   |     e     |   a
     4      |   yellow  |     d     |   b

This is my sample procedure with in which I'm trying to use bind variables .
create or replace procedure test_bind_variable(res         out sys_refcursor,
                                               item_number varchar2,
                                               item_name   varchar2,
                                               item_col    varchar2,
                                               item_pol    varchar2) is

  qry varchar2(8000);

begin

  qry := 'select * from test_table where 1=1 ';

  if (item_number is not null) then
    qry := qry || ' and item_number = :1  ';
  end if;

  if (item_name is not null) then
    qry := qry || ' and item_name = :2 ';
  end if;

  if (item_col is not null) then
    qry := qry || ' and item_col= :3 ';
  end if;

  if (item_pol is not null) then
    qry := qry || ' and item_pol = :4 ';
  end if;

  dbms_output.put_line(qry);

  open res for  qry
    using item_number, item_name, item_col, item_pol;

end;

The problem is that when all Input parameters have a values , the procedure works properly , without any error , but when only one or two parameters have value , I receive this error : ORA-01006: bind variable does not exist,. How can I solve this problem? Some parameters might have value and some may not .
Thanks in advance

You can completely avoid dynamic SQL (you also should never name PL/SQL variables the same as table columns):
create or replace procedure test_bind_variable(res         out sys_refcursor,
                                               p_item_number varchar2,
                                               p_item_name   varchar2,
                                               p_item_col    varchar2,
                                               p_item_pol    varchar2) is

  qry varchar2(8000);

begin

  open res for select * from test_table where
    (p_item_number is null or p_item_number = item_number) and
    (p_item_name is null or p_item_name = item_name) and
    (p_item_col is null or p_item_col = item_col) and
    (p_item_pol is null or p_item_pol = item_pol)

end;

PS. Make sure you take the operator precedence into account, unlike me.

You shouldn't be able to call that procedure without supplying all the parameters as you have no defaults.