EMCA insists that it cannot connect to the service Really new to Oracle and being a DBA in general. I am trying to set up a development environment so that I can play an learn oracle better. Enterprise Mananger failed to configure itself when I first created the database using the Database Configuration Assistant. No biggie. Just need to user emca.exe

I had some issues with the Listener but those might have just been me being impatient in waiting for the service to register or the service not running. Right now my issue is this from the emca log:

CONFIG: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

According to post here and on SO it should just be an issue with the service name. Problem is it looks right to me.

Listener.ora

ADR_BASE_VLISTENER = D:\app\Administrator

VLISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dvp-oracle)(PORT = 1521))
    )
  )

lsnrctl status

.... truncated ....
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dvp-oracle)(PORT=1521)))
Services Summary...
Service "prodbkp" has 1 instance(s).
  Instance "prodbkp", status READY, has 1 handler(s) for this service...
Service "prodbkpXDB" has 1 instance(s).
  Instance "prodbkp", status READY, has 1 handler(s) for this service...
The command completed successfully

That tells me that the database registered itself dynamically correctly. prodbkp is my SID. This might be a case issue since I named the DB "PRODbkp" but everything else seems to be fine since I can connect with sqlplus sys@prodbkp as sysdba just fine. Case should not be an issue with service names as per docs.oracle.com

tsnnames.ora

PRODBKP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dvp-oracle)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prodbkp)
    )
  )

Which also looks OK. Most of the solutions point to SERVICE_NAME mismatches. However I don't appear to have one.

v$parameter output

SQL> select value from v$parameter where name='service_names';

VALUE
--------------------------------------------------------------------------------

prodbkp

That gap you see above prodkbp is in the output as well. Not sure if that matters either. My production server has that anomaly as well so I can't imagine that has anything to do with it.

Basically everything looks right but something is still wrong. I cannot install Enterprise Manager

Environment

Windows Server 2008R2
Oracle 11g

So it would seem the issue was not the service name specifically but that the request was going to the IPv6 address which was not set up in any of the required files. Looking at listener.log ( which for me was located D:\app\Administrator\diag\tnslsnr\dvp-oracle\listener\trace\listener.log) I found these entries associated to my connnection attempts.

28-AUG-2017 08:41:58 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=prodbkp)(CID=(PROGRAM=D:\app\Administrator\product\11.2.0\dbhome_1\bin\sqlplus.exe)(HOST=DVP-ORACLE)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=fe80::7197:6503:9e81:5454%11)(PORT=49246)) * establish * prodbkp * 0

The important portion was HOST=fe80::7197:6503:9e81:5454%11. Since I would never need to support IPv6 I removed it from the network adapter and restarted the server to ensure all services were updated accordingly.

While it might not be the ideal solution I was able to recreate the EM dbcontrol successfully after that change.


differences (if any) between Select count () from table vs select count (*) from table I was looking at script in Sybase when I read something like

    select count() from table;

the result it's exactly the same to

    select count(*) from table;

I'm curious, and I was looking out for  more info in google but I didn't found anything.
I suppose that select count(*) from table it is ANSI and select count() from table not, and its just another way that sybase allow to do do it.

but anyway I would like to know if there is any difference or why are two different ways to do the same.

According to Sybase documentation:

COUNT(*) and COUNT() are semantically equivalent.


Is it possible to do flashback after truncate? Is there any possibility of doing flashback after truncate ? Thank you in advance.

Sadly no. At least not by default and not directly. For this you need to read about Flashback Data Archives . By default they are disabled for all tables, but it can be activated, and will preserve your data even after a truncate. Moreover, even if you have the FDAs, you cannot just flashback the table, because TRUNCATE is a DDL operation, and will throw you an error that the table definition has been changed.

What you can do though, is to select the data to a point from the flashback table, and insert it back in yours:

insert into table1
select * from table1 as of timestamp to_timestamp(<your_time>)

Note! This can be done only after you have flashback data archives for that table.

Hope this helped!


ora-12705 cannot access NLS data files or invalid environment specified I have i'm installing oracle in machine 12c, while installing everything goes smoothly but when it reach to database creation it passes two step.

cleaning up failed stat
copying database file
creating and starting oracle instance

on the last point (creating and starting oracle instance) it gives me following error:

ora-12705 cannot access NLS data files or invalid environment specified

what I have tried is:

set env variable in windows to NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
and also I have go ahead and rename the register edited of NLS

those above point also mentioned in couple of search I did but still when I retry database creation I get same error all the time.

thanks for help

Thanks everyone,

finally after trying many steps on changing registry and also environment variable in windows property, without any luck I ignored database creation during installation and proceed with installation without creating database.

after installation is done I manually created database by launching DBCA and all went well without any error in NLS_LANG.

hope this is helpful to someone else

regards


time difference between two machines makes problem in service broker communication I have two machines and each machine has a SQL Server instance. Between these machines I run service broker. One of my machines has a different time from the other. I mean the machine time of A is 9:00 o'clock and the other one is 11:00 o'clock. The difference between two times means the message can't be received from another machine. When I sync both times with the same time the messages are received.

My question is how can I configure service broker to skip the time difference?

SSL requires that the time on the client and server be reasonably close.  See What role does clock synchronization play in SSL communcation.

At first glance, it would seem that your servers are in different time zones, so configuring them to be in the correct time zone should resolve the issue.


How to install downgrade of SQL Standard 2016 to Standard 2014 This is probably a super basic question, but if I purchase an OEM SQL Server 2016 Standard edition, how would I install it as 2014 to match the other servers? Can I get a 2014 installer and use the 2016 license key during the installation?

If you want a Sql Server 2014 instance, you'll need to install that specific version. Installing Sql Server 2016 will create a Sql Server 2016 instance, but you can control certain database behaviors to be compatible with the specified version of SQL Server by ALTERING the COMPATIBILITY_LEVEL.


Fetch all rows and leave column value empty if no equality match I have a query where I want to fetch all rows where the value in column x is between 400-600.
Including in this I want a matching column value from antoher table.
But I want all 200 rows between 400 and 600 regardless of if the value in the second table column exists for the corresponding column value in the first table.
Oracle SQL:
SELECT h.*, k.customer_id
FROM EVENTS h, CUSTOMERS k
WHERE k.id = h.ident
AND h.eventid BETWEEN 400 AND 600 ORDER BY h.eventid;

This limits the results to where k.id = h.ident is true. But on false I just want an empty value aswell so that I get all 200 rows.
TL;DR - How can I default to a value if Equal to is false (a.b=c.d), instead of filtering out that row?

I want all 200 rows between 400 and 600 regardless of if the value in the second table column exists

So given two joined tables, "left" and "right", you want to include every row from the "left" table and matching rows in the "right" table if they happen to be there?
That's called a Left [Outer] Join.
You'll have to use explicit join syntax, not the "comma" form that you currently have, because that always does an "inner" join, requiring matching values in both tables.
Try something like this:
SELECT
  h.*                   <-- Personally, I'd avoid this and state every column name explicitly
, k.customer_id
FROM events h
LEFT JOIN customers k
     ON h.ident = k.id
WHERE h.eventid BETWEEN 400 and 600
ORDER BY h.eventid
;


Foreign Key referencing multiple tables in a Question Bank scenario I am working on a business domain in which there are QuestionBanks that contain a set of Questions. Multiple Quizzes can refer to a specific QuestionBank (BankId), and there is an entry in QuizQuestionStudent for each Question on the Quiz for each Student.

I have created the following tables to shape my database structure:

CREATE TABLE QuestionBanks (
    BankId UniqueIdentifier --PK
)

CREATE TABLE Questions (
    BankId UniqueIdentifier, --PK
    QuestionNumber Int, --PK
    QuestionText VarChar(1000)
)

CREATE TABLE Quizzes (
    QuizId UniqueIdentifier, --PK
    BankId UniqueIdentifier
)

CREATE TABLE QuizQuestionStudent (
    QuizId UniqueIdentifier, --PK
    QuestionNumber Int, --PK
    StudentId UniqueIdentifier, --PK
    IsCorrect Bit
)

What is the best way to design the schema such that the QuizQuestionStudent also has a Foreign (FK) to Questions (to ensure only valid questions get added). I essentialy want a FK that consists of columns in QuizQuestionStudent (QuestionNumber) as well columns in the Quizzes table (BankId).

One solution is to repeat the BankId for each row in QuizQuestionStudent, but that seems quite wasteful.

Is there any other way to achieve this?

Is QuestionNumber a unique identifier somehow for table Questions? Or is the QuestionBank designed to have, say, 5 different sets of questions with numbers 1 through 25?

If Questions.QuestionNumber is a mere non-unique ordering value for a given BankId, then there's no way to know, when saving a QuizQuestionStudent record, what QuestionBank a particular question belongs to without presuming that the answer is for a question in the proper bank. When QuestionNumber = 5, you'd have no idea how to check its BankId if multiple banks had a fifth question. You could presume it belongs to the same BankId as the Quiz, but then what is left for the database to check?

Now, if QuestionNumber is unique, you have a different problem. You want the Question's BankId to be required to be the same as the Quiz's BankId. Repeating the BankId in the QuizQuestionStudent table wouldn't help, because that value is coming from the app and it's what you're trying to check. You want to make sure data in two other tables conforms not just to QuizQuestionStudent values, but also to each other's values. You can't do that with a foreign key. (A significantly different design for all of the tables might eliminate the issue, but I wasn't able to come up with one.)

However, it can still be strongly enforced at the database layer. You can create an "indexed view" or "materialized view" and then constrain its values. The view can reference multiple tables. It would pull the BankId from two different paths and make sure they are the same.

CREATE VIEW QuizValidityCheck
WITH SCHEMABINDING
AS
(
SELECT
1 AS DupeCheckValue
FROM QuizQuestionStudent qqs
INNER JOIN Quizzes qz ON qqs.QuizId = qz.QuizId
INNER JOIN Questions qu ON qqs.QuestionNumber = qu.QuestionNumber
CROSS JOIN (VALUES (0),(1)) AS RowExploder(ExploderRow) -- this forces there to be 2 rows any time there's a BankId mismatch, quick causes the unique constraint below to fail
WHERE qu.BankId <> qz.BankId
)
GO

CREATE UNIQUE CLUSTERED INDEX IX_CHECK_BankIdMismatch ON QuizValidityCheck(DupeCheckValue)
GO

You can also use a UDF to enforce a custom CHECK constraint on a column, which could go and check the other. But there are some scenarios where that falls apart.


When to periodically RESTART SQL SERVER 2008 R2? So I've read that it's really a bad practice to restart SQL Server on a production environment..

Does restarting SQL Server speed it up?

Automatically restart SQL Server 2008

Why are periodic restarts required to keep my instance performing well?

Then tell me this,

How many of you have maintained an SQL Server on a production environment without having to restart it for at least ONE year or more? If so, can you share some general guidelines on how to do this? namely with regards to the following:

Minimizing/eliminating deadlocks
Cost efficient CPU and Memory usage
Minimizing/eliminating memory leaks
Monitoring the servers' processes to identify increase resource consumption

Please pardon me if anyone would find this question as offensive, I would just like to be more enlightened with the subject matter.

From my experience, I've never needed to restart a production server, except for patches. Just like Marian, I do not understand how a reboot could have a beneficial effect on your 4 points. My simple advice would be to avoid reboots as much as possible, as they are no cure to your problems. Proper configuration, profiling ... will get you much further.


How do I reference a row which has two foreign keys as its primary key? I am currently trying to setup a database managing players and teams for a competition. Players are competing in teams of two but may be shuffled.

As every team combination can only exist once, I decided that the primary key of each team is composed of the two ids of the players in it:

CREATE TABLE teams (
  player1 INTEGER REFERENCES User(id) NOT NULL,
  player2 INTEGER REFERENCES User(id) NOT NULL,
  rating INTEGER NOT NULL,
  PRIMARY KEY (player1, player2)
);

The competition consists of matches, each match being three games.

I want to reference two teams in a match:

CREATE TABLE matches (
  id INTEGER PRIMARY KEY NOT NULL,
  team_1 INTEGER REFERENCES Team(id) NOT NULL,
  team_2 INTEGER REFERENCES Team(id) NOT NULL,
  timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
)

Each game contains the score of the winning and the losing team:

CREATE TABLE games(
  id INTEGER PRIMARY KEY NOT NULL,
  match_id: INTEGER REFERENCES Match(id) NOT NULL,
  score_winner : INTEGER NOT NULL,
  score_loser : INTEGER NOT NULL
)

As I do not know the winner of a game like this, I would like to add the primary key of the team that won the game to be able to to figure out the winner of a match (whoever won the most matches)/

How do I reference the winner of a game in the given table?

What I would do here is to have a table match_winner! It will be a JOINing table (also more formally called an ASSOCIATIVE ENTITY as well as many other names from the page).

I'm not quite sure why you have two tables called matches and games - are they not the same thing? If not, let me know and I'll modify the schema.

Something like this is what you require IMHO. It is a joining table between teams and matches

CREATE TABLE match_winner
(
  match_id INTEGER NOT NULL,
  team_id  INTEGER NOT NULL,
  winning_score INTEGER NOT NULL CHECK (winning_score > 0),

  CONSTRAINT 'match_winner_pk' PRIMARY KEY (match_id, team_id),
  CONSTRAINT 'mw_match_fk'     FOREIGN KEY (match_id) REFERENCES matches (match_id),
  CONSTRAINT 'mw_team_fk'      FOREIGN KEY (team_id)  REFERENCES teams   (team_id)
);

I made a small modification to your schema - I put a id INTEGER SERIAL into the teams table definition - check out the fiddle here. SERIALs are great! I also added a constraint CHECK (player1 != player2).

I then ran this SQL

SELECT t.player1, t.player2, m.id, mw.match_id, mw.team_id, mw.winning_score
FROM teams t
JOIN matches m ON t.player1 = m.team_1 AND t.player2 = m.team_2
JOIN match_winner mw ON m.id = mw.match_id;

You can check the result on the fiddle. Having the joining table avoids the use of NULL and provides added flexibility. Just a couple of recommendations - use upper case for SQL keywords and lower case for your own variables - or the opposite - just choose a standard and stick to it. You should also name your constraints as I did for the match_winner table - it's much easier to figure out error messages that way.

I hope this answers your question - if not, let me know! p.s. welcome to the forum! ;-)

Ooops - I nearly didn't notice the question. I reversed my modification of your teams schema - see the fiddle here, but the SQL is exactly the same. To use a two field PRIMARY KEY you just join on both fields instead of just a simple id one - et voilà! Of course, you can have the id and with a UNIQUE constraint, it's that bit easier sometimes - it's just that I wanted to give you the identites of the players. I've added a further fiddle here showing how you can alias a table twice in the same query to do useful things - here to join the names of player1 and player2 to your results.


Are RANK() and DENSE_RANK() deterministic or non-deterministic? According to official Microsoft BOL DENSE_RANK is nondeterministic (RANK()). But according to Ranking Functions by Itzik Ben-Gan  "... the RANK() and DENSE_RANK() functions are always deterministic". Who is right?

What I have found so far:
Microsoft's Definition "Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database."

So in Set theory tables
Employees

Employee            Salary
Sue Right            1.00
Robin Page           1.00
Phil Factor          1.00

and
Employees2

Employee            Salary
Phil Factor          1.00
Sue Right            1.00
Robin Page           1.00

are the same. But Ranking functions return different values:

    CREATE TABLE [dbo].[Employees](
    --[ID] [int] IDENTITY(1,1) NOT NULL,
    [Employee] [varchar](150) NOT NULL,
    [Salary] [smallmoney] NULL,
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Employees2](
    --[ID] [int] IDENTITY(1,1) NOT NULL,
    [Employee] [varchar](150) NOT NULL,
    [Salary] [smallmoney] NULL,
) ON [PRIMARY]

INSERT INTO [dbo].[Employees]
([Employee] ,[Salary])
VALUES
('Sue Right', 1)
, ('Robin Page', 1)
,('Phil Factor', 1 )
GO
INSERT INTO [dbo].[Employees2]
([Employee] ,[Salary])
VALUES
('Phil Factor', 1 )
,('Sue Right', 1)
,('Robin Page', 1)
GO
SELECT RANK() OVER ( ORDER BY Salary) AS [Rank]
, DENSE_RANK() OVER (ORDER BY Salary ) AS [Dense_rank]
, [Employee]
FROM
dbo.Employees

SELECT RANK() OVER ( ORDER BY Salary) AS [Rank]
, DENSE_RANK() OVER (ORDER BY Salary ) AS [Dense_rank]
, [Employee]
FROM
dbo.Employees2

SELECT NTILE(3) OVER ( ORDER BY SALARY )
, [Employee]
FROM
dbo.Employees

SELECT NTILE(3) OVER ( ORDER BY SALARY )
, [Employee]
FROM
dbo.Employees2

According to official Microsoft BOL DENSE_RANK is nondeterministic (RANK()). But according to Ranking Functions by Itzik Ben-Gan "... the RANK() and DENSE_RANK() functions are always deterministic". Who is right?

They are both right, because they are using different senses of the word "deterministic".

From the SQL Server optimizer's point of view, "deterministic" has a very precise meaning; a meaning that existed before window and ranking functions were added to the product. To the optimizer, the "deterministic" property defines whether a function can be freely duplicated within its internal tree structures during optimization. This is not legal for a non-deterministic function.

Deterministic here means: the exact instance of the function always returns the same output for the same input, no matter how many times it is called. This is never true for windowing functions, by definition, because as a (single-row) scalar function, they do not return the same result within a row or across rows. To state it simply, using ROW_NUMBER as an example:

  The ROW_NUMBER function returns different values for different rows (by definition!), so for optimization purposes it is nondeterministic

This is the sense BOL is using.

Itzik is making a different point about the determinism of the result as a whole. Over an ordered input set (with suitable tie-breaking) the output is a "deterministic" sequence. That is a valid observation, but it is not the "deterministic" quality that is important during query optimization.


How can I import the contents of an Oracle database into Visio to create an Entity Relationship Diagram? I have an Oracle database, and I would like to create an ERD in Visio 2007 Pro.

I have so far looked at DBMS_METADATA.GET_DDL commands to generate DDL. I know that I can import an Access DB into Visio, but it seems that Access understands an SQL dialect which is different from Oracle, so I would need to tweak every Create Table command to change the data types.

Is there a simple way to create an ERD using DDL in Visio Pro 2007?

All you need is configured ODBC connection to Oracle DB

1. Install Oracle Client SW (download from otn.oracle.com). I recommend installing same version as your Oracle DB
2. Define connection in tnsnames.ora
3. Create and test ODBC connection to Oracle DB in Control Panel > Administrative Tools > Data Sources (ODBC) -  use Oracle's driver in your ORA_HOME, use connection defined in tnsnames.ora
4. Importing from defined ODBC connection to Oracle should be same as importing from Access DB


Very high transactions per second Our production server runs on average 4,000 transactions per second.  In the past few days the average has jumped to 175,000 transactions per second.  That is not a typo, it's 175K per sec.
Looking at the DMVs for transactions, we can't link it to user sessions directly, but we do see this:

SELECT NAME,
       COUNT(*)
FROM   sys.dm_tran_active_transactions
GROUP  BY NAME
ORDER  BY 2 DESC

--

+------------------------------+-------+
|             Name             | Count |
+------------------------------+-------+
| WorkFileGroup_fake_worktable |   627 |
| LobStorageProviderSession    |   217 |
| workfile                     |   171 |
+------------------------------+-------+

Can anyone shed light on these types of transactions?  Or am I chasing ghosts here?

Watch for the high activity again; when you see it, start a server side trace or if necessary use Profiler briefly to see what's going on.

Alternately, use a packet sniffer like Wireshark to capture raw wire activity.

Check dm_exec_cached_plans to see if that gives any idea what's going on.

Watch dm_io_virtual_file_stats to see which files in particular, if any, are being hit.


Percona XtraDB Cluster - do I add arbiter IP in gcomm://? I'm configuring Percona XtraDB Cluster 8 on two nodes, and use a 3rd node to act as an arbiter.
I cannot make up out of the docs whether to add the IP of the arbiter in the galera nodes addresses of the arbiter’s configuration.
# A comma-separated list of node addresses (address[:port]) in the cluster
# GALERA_NODES=""

Also, do I add the IP of the arbiter in the wsrep_clsuter_address of the Galera configuration? Again, it's not described in the docs.
wsrep_cluster_address=gcomm://...

While I too could not find a definite answer in the documentation, the following link states:
While Galera Arbitrator does not participate in replication, it does receive the same data as all other nodes.
Which leads me to believe that yes, you would have to add the Arbitrator IPs to all configuration files as if it were another node.
EDIT: This guide seems to indicate the same, although it is not official documentation.


English version of ISO 3166-2 subdivision data I have a MySQL table containing ISO 3166-2 location subdivision data.  The data contains non-English names with accent characters that I would prefer to have as English names/characters.
Examples include:
'Ajmān
Ghōr
Kāpīsā
Abū Z̧aby [Abu Dhabi]

As you can see with the last example, there are instances which contain square brackets with the English name.
Any ideas where I can find such a version with English names and characters/no-accents?

http://download.geonames.org/export/dump/
has (or used to have) asciiname names and "alternatenames".  The latter is a list of names, often in a variety of languages.  Here are the alternatenames for Abu Dabi:
A-pu-that-pi,AEbu Saby,AUH,Aboe Dhabi,Abou Dabi,Abu Dabi,Abu Dabis,Abu Daby,Abu Daibi,Abu Dhabi,Abu Dhabi emiraat,Abu Zabi,Abu Zaby,Abu Zabye,Abu Zabyo,Abu Ḍabi,Abu Ḑabi,Abu-Dabi,Abu-Dabi khot,Abu-Dabio,Abu-Dzabi,Abú Dabí,Abú Daibí,Abú Zabí,Abû Daby,Abū Dabī,Abū Z̧aby,Abū Z̧abye,Abū Z̧abyo,Abū Z̧abī,Ampou Ntampi,Ebu Dabi,Ebu Dhabi,Gorad Abu-Dabi,a bu zha bi,abu dhabi,abu-dabi,abudabi,abudhabi,abw zby,abwzby,aputapi,xa bud abi,Â-pu-tha̍t-pí,Äbu Saby,Əbu-Dabi,Άμπου Ντάμπι,Αμπου Νταμπι,Αμπού Ντάμπι,Абу Даби,Абу-Даби,Абу-Даби хот,Абу-Дабі,Горад Абу-Дабі,Әбу-Даби,Աբու Դաբի,אבו דאבי,أبوظبي,ئەبووزەبی,ابو ظبى,ابوظبی,ابوظہبی,अबु धाबी,अबू धाबी,আবুধাবি,ਅਬੂ ਧਾਬੀ,ଆବୁଧାବି,அபுதாபி,ಅಬು ಧಾಬಿ,അബുദാബി,අබුඩාබි,อาบูดาบี,ཨ་པོའུ་དྷ་པེ།,အဘူဒါဘီမြို့,აბუ-დაბი,አቡ ዳቢ,アブダビ,阿布扎比,아부다비
The table is over 1GB when loaded into MySQL.  I think I downloaded it in 2016.  If you need some tips on getting around hiccups in the loading, let me know.
Ghor and Kapisa are in the table but without the macron accents.


ROW_NUMBER() without PARTITION BY still generates Segment iterator I'm writing on an upcoming blog post of mine on ranking and aggregate window functions, specifically the Segment and Sequence Project iterators. The way I understand it is that Segment identifies rows in a stream that constitute the end/beginning of a group, so the following query:

SELECT ROW_NUMBER() OVER (PARTITION BY someGroup ORDER BY someOrder)

Will use Segment to tell when a row belongs to a different group other than the previous row. The Sequence Project iterator then does the actual row number calculation, based on the output of the Segment iterator's output.

But the following query, using that logic, shouldn't have to include a Segment, because there's no partition expression.

SELECT ROW_NUMBER() OVER (ORDER BY someGroup, someOrder)

However, When I try this hypothesis both these queries use a Segment operator. The only difference is that the second query does not need a GroupBy on the Segment. Doesn't that eliminate the need for a Segment in the first place?

Example

CREATE TABLE dbo.someTable (
    someGroup   int NOT NULL,
    someOrder   int NOT NULL,
    someValue   numeric(8, 2) NOT NULL,
    PRIMARY KEY CLUSTERED (someGroup, someOrder)
);

--- Query 1:
SELECT ROW_NUMBER() OVER (PARTITION BY someGroup ORDER BY someOrder)
FROM dbo.someTable;

--- Query 2:
SELECT ROW_NUMBER() OVER (ORDER BY someGroup, someOrder)
FROM dbo.someTable;

I found this 6 year old blog post mentioning the same behavior.

It looks like ROW_NUMBER() always includes a segment operator, whether PARTITION BY is used or not. If I had to guess I would say this is because it makes creating a query plan easier on the engine.

If the segment is needed in most cases, and in the cases where it's not needed it's essentially a zero-cost non-operation, it's a lot simpler to just always include it in the plan when a windowing function is used.


Blocked Process Report -two updates causing a long block I have a .NET application which is getting SQL update timeouts and I believe that some sort of blocking/locking is taking place which is causing this. I have ran a blocking process report on the database and found some potential culprits: 17 seconds worth of blocking.

Below is such log ( there are many similar ):

<blocked-process-report>
 <blocked-process>
  <process id="process308898748" taskpriority="0" logused="0" waitresource="OBJECT: 99:774293818:0 " waittime="17146" ownerId="66317995993" transactionname="UPDATE" lasttranstarted="2015-06-15T12:59:05.817" XDES="0x3bc9cd970" lockMode="IX" schedulerid="7" kpid="11204" status="suspended" spid="161" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-06-15T12:59:05.817" lastbatchcompleted="2015-06-15T12:59:05.817" clientapp=".Net SqlClient Data Provider" hostname="WORKFLOWG10" hostpid="6832" loginname="WorkflowStateUpdaterSP" isolationlevel="read committed (2)" xactid="66317995993" currentdb="99" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame line="1" stmtstart="16" sqlhandle="0x02000000cb1bb914fe051a308bd33cb2b873948749c2a96d"/>
    <frame line="1" sqlhandle="0x0200000075c8f5236facd1f18bca0258f9d4babad99091d6"/>
   </executionStack>
   <inputbuf>
UPDATE [ScheduDB].[dbo].[SP_ScheduleEvent] set DateLastProcessed = GETDATE() where ScheduleEventID = 3111573   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="suspended" waitresource="OBJECT: 99:774293818:0 " waittime="17182" spid="278" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-06-15T12:59:05.780" lastbatchcompleted="2015-06-15T12:59:05.780" clientapp=".Net SqlClient Data Provider" hostname="WORKFLOWG12" hostpid="9944" loginname="WorkflowStateUpdaterSP" isolationlevel="read committed (2)" xactid="66317995589" currentdb="99" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame line="1" stmtstart="16" sqlhandle="0x02000000cb1bb914fe051a308bd33cb2b873948749c2a96d"/>
    <frame line="1" sqlhandle="0x0200000001c6452aee248fd8f9f2be0e27c7038cfa4e334f"/>
   </executionStack>
   <inputbuf>
UPDATE [ScheduDB].[dbo].[SP_ScheduleEvent] set DateLastProcessed = GETDATE() where ScheduleEventID = 3807096   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

As you can see it appears that two updates are blocking each other and I'm struggling to see why.

Here are the database specifics that might help you help me:

TABLE: SP_ScheduleEvent
------
|ScheduleEventID| PK| bigint| not null|
...
|DateLastProcessed| | datetime| not null|
...

INDEX:
ScheduleEventID, "use page locks when accessing the index is set to" FALSE ( row locks is on )

Typically I have many processes running on different machines that would trigger this UPDATE, I run the update from a DBDataContext within a .NET application ( Linq to SQL) like so:

string sqlUpdateString = "UPDATE [ScheduDB].[dbo].[SP_ScheduleEvent] set DateLastProcessed = GETDATE() where ScheduleEventID = " + scheduleEvent.ScheduleEventID;
db.ExecuteCommand(sqlUpdateString);

Thanks

The Blocked process report (which you have) can be a little confusing at first. There are a few important sections

Blocking-Process
This is the process(SP, query etc) that is causing the blocking. The process is using a resource that is required by the Blocked-Process

Blocked-Process
This is the process(SP, query etc) that is blocked and is waiting on a a resource that is in use by the Blocking-Process.

waitresource
This details the resource that the process is waiting on. Taking your example of
waitresource="OBJECT: 99:774293818:0 "

The 99 refers to the Database ID of your data and the 774293818 refers to the table. Using the ID 774293818 and systables you should be able to find the table that is causing the blocking.

I would also set up an Extended Events Trace to capture the parameters of the offending blocking process and review the execution plans.

A good starting point is https://support.microsoft.com/en-us/kb/224453


Get sum of amount monthly I do not know English, sorry :(

example table:

CREATE TABLE IF NOT EXISTS `__example__` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`amount` decimal(10,2) DEFAULT NULL,
`fromDate` datetime DEFAULT NULL,
`toDate` datetime DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

and rows:

INSERT INTO `__example__` (`ID`, `amount`, `fromDate`, `toDate`)
VALUES (1, 1000.00, '2016-01-16 17:05:40', '2016-01-26 17:05:45');

INSERT INTO `__example__` (`ID`, `amount`, `fromDate`, `toDate`)
VALUES (2, 1000.00, '2016-01-26 17:05:40', '2016-02-05 17:05:45');

INSERT INTO `__example__` (`ID`, `amount`, `fromDate`, `toDate`)
VALUES (3, 1000.00, '2015-12-26 17:05:40', '2016-01-05 17:05:45');

INSERT INTO `__example__` (`ID`, `amount`, `fromDate`, `toDate`)
VALUES (4, 4000.00, '2015-12-26 17:05:40', '2016-02-04 17:05:45');

I want to see:

year | month | sum
2015 |    12 | 1400.00
2016 |    01 | 4700.00
2016 |    02 | 900.00

Example:

2 rows started at 12/2015 and the sum thereof is 5000.00(1000+4000).

But 12/2015 sum column should be 1400.00.

How can I do that with SQL query? Help me, please.

This query should accomplish your goal.

Select YEAR(fromDate) [Year], MONTH(fromDate) [Month], SUM(amount) [Amount]
FROM '__example__'
GROUP BY YEAR(fromDate), MONTH(fromDate)
ORDER BY Year, Month


Is it possible to have change tracking ONLY on a replication server, not original? I am working on a system where we are pulling records from a replication server and need to know when changes have occurred.

We would like to not have to touch the original server and only read from it, including not being allowed to enable change tracking.  Can this be done?

If need be, we will simply pull all Record IDs every night and compare the two but it's certainly not the ideal solution considering we have tens of million of records.

I have seen this question:

Is it possible to use Change Tracking on a Replication target?

The answer to it seems to be suggesting that the way to do this is to enable change tracking on the original server that is then replicated, which in my case is not an option.

if you can consider not only SQL Server options take a look at GoldenGate.
We have a project to deliver content (ongoing changes) + change tracking from Oracle to Greenplum database. GoldenGate supports SQL Server as well, so idea and even parameter files for the processes are the same.
On a high-level it looks like:

capture transactions record info on source system (EXTRACT process)
deliver to the target independent system (it can be SQL Server, or whatever supported by GoldenGate) - (PUMP process)
apply changes not as DML operations but as full records with additional attributes like "operation name", "commit timestamp" and else you can get from trail files generated on source (REPLICAT process).
You can record only PK values or full row values into change tracking. It's just a matter of design.

EXTRACT and PUMP processes are quite simple. The most interesting here is REPLICAT config:

REPLICAT <PROCESS NAME>
INCLUDE ./dirprm/DBLOGIN.inc
INCLUDE ./dirprm/REPORT.inc
MACRO #map_history
PARAMS (#table)
BEGIN
IGNORETRUNCATES
MAP <SRC_SCHEMA>.#table, TARGET <TGT_SCHEMA>.#table,
  INSERTALLRECORDS,
  COLMAP (USEDEFAULTS,
    OPERATION = @GETENV('GGHEADER', 'OPTYPE'),
    CSN = @GETENV('TRANSACTION', 'CSN'),
    RSN = @COMPUTE(@COMPUTE(@NUMSTR(@GETENV('RECORD', 'FILESEQNO')) * 100000000000) + @NUMSTR(@GETENV('RECORD', 'FILERBA'))),
    CSN_TIMESTAMP = @GETENV('GGHEADER', 'COMMITTIMESTAMP')
  );
END;
INCLUDE ./dirprm/DML_REPLICAT.inc
#map_history(<TABLE1>)
#map_history(<TABLE2>)
...
#map_history(<TABLEN>)

This works well for hundreds of millions operations per day (including LOBs).

Hope it helps to get an idea of the one of the potential solution.


Connecting to SQL Server 2008 on windows server 2008 Hell all

I am new to windows server 2008 and we are trying to transfer our website from hosting to virtual server.

I installed SQL Server 2008 Express edition and I did the things like opening port and allowing them through the firewall but I can't log into to the remote server from my local machine.

I am not quite sure which IP address to use to connect to SQL Server from local machine whether is it servers IP address or will SQL give us a different IP address.

Please help.

Thanks

You will be using the servername (DNS) or the IP address of the server.  But if you have a named instance (as opposed to the default instance), your servername will be followed by \instanceName.

So, in other words unless you changed any SQL Server Express install defaults, you should connect to the server like this:

yourServerName\sqlexpress

SQL Server Express has a default named instance name of SQLEXPRESS.  Also, since SQL Server Express is a named instance, you'll need to ensure that your SQL Server Browser service is running.

Another one.  If you are trying to connect to SQL Server from a separate machine, you'll need to ensure that the TCP/IP protocol is enabled for SQL Server.

The above two points can be verified and configured in SQL Server Configuration Manager.


How to create a an insert trigger on How to write an insert trigger for:

"If the client wants to pay by using a credit card, its details need to be entered in the record. If a credit card is not being used it must be ensured that the credit card details are blank."

create table  Management.PaymentMethods
(
    PaymentMethodID int CONSTRAINT PMID PRIMARY KEY identity(1000,24),
    Description varchar(22) constraint chkTitle check
        (Description in ('cash','cheque','credit card'))
)

Create table Management.Payments
(
    PaymentID int CONSTRAINT PKIDD PRIMARY KEY identity(100,45),
    EventID int constraint EVId
        foreign key(EventID) references Management.Events(EventID),
    PaymentDate date not null,
    StartDate date not null,
    CONSTRAINT PDK  CHECK (PaymentDate <= StartDate),
    check(PaymentDate > Getdate()),
    PaymentMethodID int constraint Phg
        foreign key(PaymentMethodID) references Management.PaymentMethods(PaymentMethodID),
    CreditCardDetails varchar(100),
    ExpiryDate date constraint PD check(ExpiryDate > Getdate()),
    ChequeNo int,
    ChargePerPerson int constraint chkk check(ChargePerPerson > 0),
    NoOfPeople int not null,
    PaymentAmount as ChargePerPerson * NoOfPeople
)

I assume SQL Server because of the getdate(). It would be something similar to

CREATE TRIGGER Management.PaymentsCreditCard
               ON Management.Payments
               AFTER INSERT,
                     UPDATE
BEGIN
  UPDATE Management.Payments
         SET CreditCardDetails = NULL
         FROM inserted
              INNER JOIN Management.PaymentMethods
                         ON PaymentMethods.PaymentMethodID = inserted.PaymentMethodID
         WHERE Payments.PaymentID = inserted.PaymentID
               AND PaymentMethods.Description = 'credit card';
END;

. (Just to give you the idea. I haven't tested it, it might need some adaption.)


You can use the  overlaps operator:

select *
from the_table
where (startdate, enddate) overlaps (date '2019-01-01', date '2019-01-30');

The overlaps operator works as the name suggest. It would e.g. include a start/end like 2019-01-20/2019-02-05 (because that range "overlaps" with the one specified)

If your input needs to be contained completely in the range defined by startdate/enddate, a query using a date range and the "contains" operator would be better.

select *
from the_table
where daterange(startdate, enddate, '()') <@ daterange(date '2019-01-01', date '2019-01-30', '()');

Or you can use a simple lower than bigger than/lower than condition:

select *
from the_table
where startdate >= date '2019-01-01'
  and enddate <= date '2019-01-30';

Online example (with more data): https://rextester.com/ALJSJE59375


Why does JSONB show a different date format than Javascript? With Javascript's .toISOString(); and .toJSON() I get

(new Date()).toISOString();
2018-01-30T12:04:13.153Z

Using PostgreSQL when I return a date, I get

# SELECT to_jsonb('2018-01-30T12:04:13.153Z'::timestamp with time zone);
            to_jsonb
---------------------------------
 "2018-01-30T06:04:13.153-06:00"
(1 row)

Why are these two different formats?

With ECMA-262 v6, the Z must be present in the result of .toISOString if a time zone is provided, you do not need millisecond precision. Javascript on the other hand has no date types to begin with. Both of these formats are ISO 8601. PostgreSQL doesn't specify a Z and it gives only the precision provided. Nothing prohibits that.

The Date() constructor will gladly accept both formats, and you should be using Moment.js for all your date time needs in Javascript. You should be immediately constructing date objects with the output of PostgreSQL. This may not be done for you, it also may be something you have to do as JSON doesn't know the difference from a ISO 8601 date/time object, and a string that looks exactly like it. From that point, it's out of PostgreSQL's hand. All that PostgreSQL ensures is that the output is valid JavaScript Date() input, which is above and beyond because the JSON spec doesn't even require that.

ECMA-262 v6 spec,

The ECMA-262 v6 spec says,

  Date.prototype.toISOString() This function returns a String value representing the instance in time corresponding to this time value. The
  format of the String is the Date Time string format defined in 20.3.1.16. All fields are present in the String. The time zone is always UTC, denoted by the suffix Z**. If this time value is not a finite Number or if the year is not a value that can be represented in that format (if necessary using extended year format), a RangeError exception is thrown.

That says this,

  Z is the time zone offset specified as "Z" (for UTC) or either "+" or "-" followed by a time expression HH:mm

  This format includes date-only forms:

YYYY
YYYY-MM
YYYY-MM-DD

  It also includes “date-time” forms that consist of one of the above date-only forms immediately followed by one of the following time forms with an optional time zone offset appended:

THH:mm
THH:mm:ss
THH:mm:ss.sss

JSON Spec

The JSON spec says nothing about date formatting. In fact, there isn't even a date type mentioned. This is probably because Javascript dates are objects not primitives.

PostgreSQL

PostgreSQL outputs only the minimum precision provided to the constructor, and without the z (still in ISO 8601).

SELECT to_jsonb(dt::timestamp with time zone)
FROM ( VALUES
  ('2018-02-01T13:24:36.466849-06:00'),
  ('2018-02-01T13:24:36-06:00');


Delete all rows within transaction: what would happen to other transactions? I currently have a function in PostgreSQL that creates a temp table, fills it, performs some tricky selects and returns results. postgresql's temp tables are local to functions, so this helps me avoid introducing another column to my temp table to mark all rows inserted by a particular execution of the function. If I used a global table that would be required, and due to nature of my queries, it'd kill performance.

I want to port this piece of code to VoltDB, but it does not support creating temp tables from stored procedures. This made me think about other approaches. If a stored procedure would start a transaction and delete all rows in a table (not temp), then what would happen to other executions of the same stored procedure running at that moment?

Or if after the delete, the stored procedure begins inserting rows, and another session invokes the same sp, which will want to delete all rows again, what then?

In short, can I create a local (to sp execution) view of data within a transaction by deleting all rows, inserting some rows, then performing queries on the inserted rows, then deleting all rows again before the sp execution ends (and transaction commits)?

This would let me avoid using a rowset_id column for the temporary rows, avoiding much slower joins (this is an EAV implementation).

The comment by a_horse_with_no_name is actually the answer, but since it was not posted as an answer, I'm not able to mark it so. I've completely forgotten about transaction isolation levels, I've asked the question in VoltDB forums and it appears that as long as make sure that stored procedures are local to nodes (sorry, this is a bit of a VoltDB terminology), that is, they don't process data across partitioned tables, this should work.


Select fields from multiple tables I want to make a query a little complicated, and I'm not able to do it alone.

I have the following tables:

Table color:
id_uo  P
id_cor   PF
id_area   F
text_cor

Table area:
id_area  P
text_area

Table atv:
id_dest PF
id_cor PF
id_uo PF

Table dest:
id_dest P
type

What I intend to have as a result is a table that returns the fields as follows:

text_area | id_cor | text_cor | type

Do not know if atv through the table, I can create a query that helps me access to all parameters. Sorry but I'm newbie in the creation of more complex query. I've already tried that with Join and Union but got nothing that I would get. Can you help me?

select * from area:

id_area | text_area
--------------------
  1     |   info1
  2     |   info2

select * from color:

id_cor |  id_uo | id_area |text_cor
-----------------------------------
  1    |  1222  |    1    |  create
  2    |    1   |    2    |  teste
  2    |  1222  |    1    |  sssss

select * from dest:

id_dest | type
--------------
   1    |   E
   2    |   M

select * from atv:

id_dest | id_cor | id_uo
----------------------------
  1     |   2    |  1222
  2     |   2    |   1
  2     |   1    |  1222

What I want to achieve for each id_uo is the following result (for id_uo: 1222):

text_area | id_cor | text_cor | type
-------------------------------------
 info1    | 1      |  create  |   M
 info1    | 2      |  sssss   |   E

You should be able to get the result that you want by using a LEFT JOIN on each of the tables.

select a."text_area",
  c."id_cor",
  c."text_cor",
  d."type"
from area a
left join color c
  on a."id_area" = c."id_area"
left join atv t
  on c."id_cor" = t."id_cor"
  and c."id_uo" = t."id_uo"
left join dest d
  on t."id_dest" = d."id_dest"
where t."id_uo" = 1222
order by c."id_cor"

See SQL Fiddle with Demo

Result:

| TEXT_AREA | ID_COR | TEXT_COR | TYPE |
----------------------------------------
|     info1 |      1 |   create |    M |
|     info1 |      2 |    sssss |    E |

You will see that when I used the LEFT JOIN on the atv table, I joined it to the color table on both id_cor and id_uo.


Is it safe or possible to change SQL Server AutoGrowth when in a SQL Server AlwaysOn Cluster I am wondering it is possible or advisable to change the AutoGrowth settings of a database while it is part of an AlwaysOn SQL Server 2016 database cluster. I realized I used the default settings when I want to use custom growth rates based on the suggestions by Brent Ozar.

I cant change them in the GUI so I am guessing its not possible but I wanted to check. Will I have to remove the database from the AlwaysOn group, change the growth settings and then add it back in?

You have to have AutoGrow enabled and do it on the primary replica. You can also do it in t-sql using the ALTER DATABASE command.


many 'unauthenticated user', 'connecting host', in show full processlist (Mysql 5.1.5) I have a mysql slave on AWS/EC2 that receives peak CPU randomally.

I run show full processlits and I get many (few 100s) of processes like this:

'1141944', 'unauthenticated user', 'connecting host', NULL, 'Connect', NULL, 'login', NULL

What is it ?

Any idea on how I can detect what causes the server to suddnely climb to 200 load average?

Thanks

Setting skip_name_resolve disables the MySQL server's ability to perform DNS lookups. DNS servers can be unavailable and for a production server it is usually better to specify and ip and not a hostname as the 'host' specifier of a username. However users must then be created as 'user'@’ip’ and not as 'user'@'host' (except for ‘localhost’ what is accepted with this setting ON)

Do not resolve host names when checking client connections. Use only IP addresses. If you use this option, all Host column values in the grant tables must be IP addresses or localhost

http://dev.mysql.com/doc/refman/5.0/en/host-cache.html


AlwaysOn AG and Cross-Database/Server Transactions We're about to move from SQL Server 2008 R2 Standard to SQL Server 2012 Enterprise and are reviewing or HA as part of process. Right now we use DPM and log shipping every 15 mins. Our MS partner is recommending AlwaysOn AG. I've heard of a lot of issues with AG, but the one that gives me the most concern is this one:

According to Microsoft, "Cross-database transactions and distributed transactions are not supported by AlwaysOn Availability Groups or by database mirroring". (http://msdn.microsoft.com/en-us/library/ms366279(v=sql.110).aspx)

We can't move to SQL Server 2014 (and it doesn't seem any better there anyway).

We have a 3rd party vendor app that uses 7 databases (bad design, but nothing we can do about it); many integrations that use multiple databases; and some that use multi-server transactions.

Is there a way to live with this limitation?

What do you guys do who use AG and have cross-database and cross-server transactions?

Regards

Mark

Is there a way to live with this limitation?

As of now, no not in a supported manner (I won't reference the link, as you have already done that in your original question).  There are potential inconsistencies that can arise from this configuration.  With that being said, there will be no hard enforcement on this, so you will not be prevented from the implementation.  But with the possible inconsistent pitfalls that may arise, I wouldn't and couldn't make the recommendation to go forward with AGs and distributed transactions.

  What do you guys do who use AG and have cross-database and cross-server transactions?

Currently, not implement AGs in this type of environment.


mysql apply regexp_replace for whole column/multiple rows I Have column exe_production inside users_acts table with data like `blah blah blah 10.10.2020 (any date) blah blah.
I need to order this column by date.
My steps:

Get date from string by next query:
SELECT REGEXP_REPLACE((SELECT exe_production FROM users_acts LIMIT 1), '[^[0-9]{2}[.]{1}[0-9]{2}[.]{1}[0-9]{4}]', '');

Convert all results strings to date by DATE_FORMAT statement.

Apply ORDER BY statement.

The problem is that I can't apply first query for multiple rows/values, only by LIMIT 1. How I can solve it?


Trouble with Alerts and Notifications on SQL Server 2000 I'm trying to set up alerts and notifications in SQL Server 2000, but they are not working.  I'm testing them for Agent scheduled jobs, and I get a note saying:

  NOTE: Failed to notify '[operatorname]' via email.

Then in the Current Error log, I see an entry like:

  [264] An attempt was made to send an email when no email session has been established

xp_sendmail works fine, and I'm actually using it in these agent jobs.  The notifications are supposed to tell me the procedure completed successfully.  I'm only asking for completion notification to get this working, then I'll change it to failure notifications.

Anyone know why this is happening and how to correct?  I'd really like notifications to work.

For SQL Server 2000, I quite honestly wouldn't bother trying to get operators to work. SQL Mail is a royal PITA and requires Outlook or a similar mail client to be installed on the server. I would rather just setup each job to have a step called "mail on failure", which uses a token to identify the job, and then calls a stored procedure. You would only reach that step in the workflow if you set a step to go to that step on failure. Otherwise the step before it simply quits the job with success.

A couple of alternatives:

(1) I used xp_smtp_sendmail with great success when I was still straddled with SQL Server 2000. The challenge is it used to be available on sqldev.net, but Gert seems to have abandoned that site. So you may have to search around to find a copy of the DLL. It was written by a Microsoft employee but the standard "download from a trusted source only" disclaimer applies.

(2) Use CDO. The same ick factor as SQL Mail but no dependency on Outlook / MAPI. See this KB article for some info, but here is an example that sets up a stored procedure to send job mail failures and then how to include a job token in a job step to call that stored procedure.

First, a generic procedure to send the e-mail (you may want to re-use this for more than just job failures):

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
   @From       VARCHAR(320)   = 'default_admin@yourcompany.com',
   @To         VARCHAR(320)   = 'default_admin@yourcompany.com',
   @Subject    NVARCHAR(4000) = N'',
   @Body       NVARCHAR(4000) = N'',
   @SMTPServer VARCHAR(2000)  = '127.0.0.1'
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @obj INT;

  EXEC sp_OACreate 'CDO.Message', @obj OUTPUT;
  EXEC sp_OASetProperty           @obj,
    'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value',
    '2';
  EXEC sp_OASetProperty           @obj,
    'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',
    @SMTPServer;
  EXEC sp_OAMethod                @obj,
    'Configuration.Fields.Update',
    NULL;
  EXEC sp_OASetProperty           @obj, 'To',       @To;
  EXEC sp_OASetProperty           @obj, 'From',     @From;
  EXEC sp_OASetProperty           @obj, 'Subject',  @Subject;
  EXEC sp_OASetProperty           @obj, 'TextBody', @Body;
  EXEC sp_OAMethod                @obj, 'Send',     NULL;
  EXEC sp_OADestroy               @obj;
END
GO

Next, a procedure to handle the e-mail specifically for job failures:

CREATE PROCEDURE dbo.JobAlertSender
   @To      VARCHAR(320) = 'default_admin@yourcompany.com',
   @job_id  VARCHAR(36)
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @Subject NVARCHAR(4000);
   SELECT @Subject = N'Job failed: ' + name
       FROM msdb.dbo.sysjobs
       WHERE job_id = @job_id;

   DECLARE @Body NVARCHAR(4000);
   SET @Body = CONVERT(CHAR(10), GETDATE(), 120)
       + ' ' + CONVERT(CHAR(5),  GETDATE(), 108);

   EXEC dbo.sp_send_cdosysmail
        @From    = 'default_admin@yourcompany.com',
        @To      = @To,
        @Subject = @Subject,
        @Body    = @Body;
END
GO

Now you can add a step to your job that calls this procedure. You would only hit this step if a previous step failed. The step would have code like this (note that you may want to override the default "To" person depending on the job):

EXEC master.dbo.JobAlertSender
   -- @To = 'override@yourcompany.com',
   @job_id = '(JOBID)';

Here is how the job step would look:

And here is how you would set up previous steps, either using "go to the next step" or picking the failure step explicitly from the drop-down:

If you find xp_smtp_sendmail, you'd just call a slightly different procedure from dbo.JobAlertSender.

If you don't have a valid SMTP server, you'll need to install an SMTP server locally. For help with that you'll want to hit up SuperUser or ServerFault. If you need something to test locally in the meantime, nothing could be simpler than smtp4dev. You won't get the e-mails in your inbox, but you can verify the contents, headers, etc until you do have a proper SMTP server in place.

Google also told me I wrote this article in 2002 which may contain some useful information.


Mapping product codes in CRM and ERP I'm loading data into a data warehouse. Customers/transactions are coming from the ERP and from Salesforce. Product codes do not match up between the systems.

Is there a standard pattern for solving this problem?

I'm trying to be careful to avoid a big opinion-based question/answer. I'm really just looking for the fact of how this problem is normally addressed. Maybe it's as simple as, "Put some cross reference tables in the warehouse or transform the product codes in your ETL jobs." But maybe warehouse experts could share their experience.

Yes: the pattern you need is "synthetic keys."  For each incoming logical product, you'll assign a key, to be used only within the data warehouse.  If two source systems reference the same product, you can give them the same synthetic key (SK).  If you source systems use the same ID for two different products, you can give them each a different key.  Thus, in your warehouse you have a clean list of products.

As an example, the schema for my current SyntheticKeys.Products table looks like this:

CREATE TABLE SyntheticKeys.Products
(
SourceSystem    VARCHAR(30) NOT NULL,
ProductID   VARCHAR(30) NOT NULL,
ProductSK   INT NOT NULL IDENTITY (2, 1),
-- Housekeeping
ETLCreated  INT NOT NULL,
ETLModified INT NOT NULL
)

In this case, products are de-duplicated before they reach this table, so I can use IDENTITY to allocate synthetic keys, but you may prefer to set up a SEQUENCE object and get IDs from it when a record is identified as being a genuinely new product.

A couple of additional benefits from using synthetic keys:

You can use a 32-bit or even 16-bit integer, rather than 144-bit Salesforce IDs or other wide keys.  This keeps your fact tables narrow and fast.
You can reserve space for special records.  My convention is that SK #-1 means "not applicable" and SK #0 means "unknown"; thus, a customer who's never bought a product may have FirstPaidOrderSK of -1, rather than NULL, which is ambiguous.


Connect to SQL Server 2012 from an Windows XP client We have an SQL Server 2012 Standard database, to connect to it we use SQL Server 2008 Management Studio (since 2012 SSMS is not supported on Windows XP).
The problem is that SQL Server 2008 gives all the time an exception (Index outside the bounds of the array) and it's not possible to edit table in visual editor or open table. Client can work now only with database objects through queries.
So the question is - is there any solution on Windows XP to work properly with 2012 database ?

The version of Management Studio you should be using is SQL Server 2012. Sadly - as you've confirmed - this is not supported on Windows XP. I don't think this is a wholly artificial restriction - I think even if you could figure out some way to hack the 2012 tools into installing, you'd have other behavioral issues to deal with.

So, you have a couple of options (and I understand that these may or may not be practical):

Upgrade to a new version of Windows (and if you need XP for compatibility reasons, use XP mode). You won't get a whole lot of sympathy from Microsoft running a 10-year old operating system.
Remote to the SQL Server 2012 machine and use its copy of Management Studio.
Deal with / ignore the exception (though you may be missing other functionality too).


Run lo_export as client through psycopg2 Python 2.7
psycopg2

When I run:
cur.execute("SELECT lo_export(123456, '/tmp/test.jpg')")

Produces error - need to be superuser to do this.

This makes sense as it is running a server side lo_export and the permissions are not set to superuser for the user I want to run the request.

I want to run a client side lo_export as if I was running psql.

Can I do this within an open psycopg2 connection?

OK simple solution. Use:

conn = psycopg2.connect("db=dbname etc etc")
lo = conn.lobject(12345)
lo.export('/tmp/test.jpg')


Query slowness after migration from sql server 2000 to 2014 I'm not a DBA. I have two servers. One of them runs on sql server 2000, the other on sql server 2014. Both have almost identical hardware (2014 has more RAM). My job is to migrate all databases and activity from the first server to the second one.

To achieve this I made database backups on SQL 2000 and restored them on a server running on SQL 2008. Then made another set of backups and restored them on the 2014 server. You can't restore 2000 backups on a 2014 server - you need an intermediary - in my case it was 2008. Anyway, now that I have all the databases on the 2014 server I began testing some stored procedures and queries. Some queries run as good as on the 2000 server. Unfortunately, there are some that take ages - what takes about 20 seconds on the SQL 2000 server, takes a couple of hours on the 2014 server which is not acceptable.
I made several steps to improve the performance:

I rebuilt/reorganized indexes
I ran Update Statistics table_name with FULLSCAN
I ran sp_updatestats
I ran DBCC UPDATEUSAGE('database_name') WITH COUNT_ROWS
I altered between database compatibility levels 2014, 2012 and 2008 (2005 displays an error and there is no option to select 2000)

Nothing worked. Am I missing something? Optimizing the problematic queries is not a real option - there are too many of them - the problem needs to be addressed globally.

Example query which works fine:

select
MKPF.MANDT,
MKPF.MBLNR,
MKPF.MJAHR,
MSEG.BWART,
MSEG.MATNR,
MSEG.LGORT,
MKPF.BUDAT,
MKPF.CPUDT,
MKPF.CPUTM,
MKPF.AEDAT,
MSEG.SHKZG

from PI2.PKT.pkt.MSEG AS MSEG
join PI2.PKT.pkt.MKPF AS MKPF
on MKPF.mandt=MSEG.mandt and MKPF.MJAHR = MSEG.MJAHR and MKPF.MBLNR = MSEG.MBLNR
join PI2.PKT.pkt.MARA MARA
on MARA.MANDT=MSEG.MANDT and MARA.MATNR = MSEG.MATNR
and MARA.spart ='01'

However, when I add a WHERE to the query it takes a couple of hours to finish instead of 20 seconds like on SQL Server 2000:

select
MKPF.MANDT,
MKPF.MBLNR,
MKPF.MJAHR,
MSEG.BWART,
MSEG.MATNR,
MSEG.LGORT,
MKPF.BUDAT,
MKPF.CPUDT,
MKPF.CPUTM,
MKPF.AEDAT,
MSEG.SHKZG

from PI2.PKT.pkt.MSEG AS MSEG
join PI2.PKT.pkt.MKPF AS MKPF
on MKPF.mandt=MSEG.mandt and MKPF.MJAHR = MSEG.MJAHR and MKPF.MBLNR = MSEG.MBLNR
join PI2.PKT.pkt.MARA MARA
on MARA.MANDT=MSEG.MANDT and MARA.MATNR = MSEG.MATNR
and MARA.spart ='01'
WHERE
MSEG.Mandt='500' and MSEG.MATNR like '00000000%' and
MKPF.CPUDT between @datefrom and @dateto
AND MSEG.BWART in ('101','561')

FYI @datefrom and @dateto are defined beforehand (I'm sure they don't affect the preformance). PI2 is a linked server. The linked server has the exact same properties on 2014 as it does on 2000.

Any ideas on what I should do? At this point I'm not sure whether it's an issue with the SQL Server 2014 configuration or maybe there's an issue with the linked server (maybe the linked server had a better network connection with the old sql server 2000? )

What version of SQL Server is the PI2 server in this situation?  When was the last time the underlying statistics were updated on these tables on the Linked Server?  While you updated statistics, it sounds like that was only done on the local database server that you migrated to.  Updating statistics on these underlying remote tables may help the optimizer identify a more optimal plan and allow for the entire query to execute remotely.

Another possibility is permissions (though you say that's no different between the old and new servers), four-part (aka distributed) queries require proper permissions to remote tables in order to get an optimal plan.  From the Guidelines to Using Distributed Queries page:

  To create the best query plans when you are using a table on a linked
  server, the query processor must have data distribution statistics
  from the linked server. Users that have limited permissions on any
  columns of the table might not have sufficient permissions to obtain
  all the useful statistics, and might receive a less efficient query
  plan and experience poor performance. If the linked server is an
  instance of SQL Server, to obtain all available statistics, the user
  must own the table or be a member of the sysadmin fixed server role,
  the db_owner fixed database role, or the db_ddladmin fixed database
  role on the linked server.

Really since all of these tables are on the linked server, I suggest you force the query to execute remotely in it's entirety instead of hoping it will do so with four-part/distributed query syntax.  You do this by reworking the query into an EXECUTE AT statement, such as follows:

EXECUTE('select
        MKPF.MANDT,
        MKPF.MBLNR,
        MKPF.MJAHR,
        MSEG.BWART,
        MSEG.MATNR,
        MSEG.LGORT,
        MKPF.BUDAT,
        MKPF.CPUDT,
        MKPF.CPUTM,
        MKPF.AEDAT,
        MSEG.SHKZG
    from PKT.pkt.MSEG AS MSEG
    join PKT.pkt.MKPF AS MKPF
        on MKPF.mandt=MSEG.mandt and MKPF.MJAHR = MSEG.MJAHR and MKPF.MBLNR = MSEG.MBLNR
    join PKT.pkt.MARA MARA
        on MARA.MANDT=MSEG.MANDT and MARA.MATNR = MSEG.MATNR
        and MARA.spart =''01''
    WHERE
        MSEG.Mandt=''500'' and MSEG.MATNR like ''00000000%'' and
        MKPF.CPUDT between ? and ?
        AND MSEG.BWART in (''101'',''561'')'
, @datefrom, @dateto) AT PI2

I'm suggesting the EXECUTE AT approach instead of using other LinkedServer syntax such as OPENQUERY or OPENROWSET as this will allow you to still generate the date parameters from data hosted on your local server (as your estimated query plan indicates) and pass them as parameters to the query you wish to run remotely in what will likely be a more optimal fashion.


Does shrinking log file affects the indexing in SQL Server? In database log file grows from 32 GB to 70 GB and % of log space used become > 97%. After I took the log backup then %of log space used comes < 1% but size remains 70 GB .

Now I want to shrink the file size , but having following doubts : -

Is shrinking log file is good, to reuse empty VLF as well as reducing size of file .
How to determine that up to what % I need to shrink the file size ...
Does shrinking log file remove the indexing of database?

Any guidance please.

I will answer this specific part

  Does shrinking log file removes the indexing of database.

I believe you meant does shrinking of log file brings index fragmentation, not it does not causes index fragmentation. It definitely does not removes indexes I am not sure what made you asked this but rest assured no indexes would be removed.

  For other parts of your question

Shrinking of log file is as bad as shrinking of data file. The reason is, when you log file grows again after shrinking and autogrowth event is triggered which forces the process to stop until space is made available via autogrowth event. So now think this autogrowth happening frequently on large OLTP databases you can see how many a times a process would have to wait for autogrowth to complete. The best way is to presize the log file. Please refer Autogrowth article from Simple Talk. This will help you in presizing the log file and setting appropriate autogrowth value. Both are equally important.

If space is really a problem and you anyhow need it now and have no option you my shrink the log file but DON'T make it a habit. Find out process which forced it to grow more than expected and size your log file accordingly.

Some blogs for reading

Steps for better transaction log throughput

Transaction Log VLF too many too few


What is the MySQL Mutex called "srv_sys_mutex"? For the purposes of diagnosing performance issues, what kinds of operations might wait their turn for this mutex, and what configuration options and resources might influence contention on it?

It's a system mutex. It's used to protect creating new threads, destroying them.
I guess, thread_cache_size may ease contention on it.


Oracle equivalent of sp_rename in SQL Server I am trying to rename a column of multiple tables in Oracle using a cursor over a table that has all the information of other table.

In SQL Server I was able to do it correctly using sp_rename. I am not able to find an equivalent in Oracle.

I tried with alter table but my table names are in variable so I am not able to do it. I am using:

execute immediate alter table var_table_name rename var_col to var_new_col

Can anyone provide a method that is similar to sp_rename in Oracle or any other method?

Community Wiki answer containing the solution given by Justin Cave in a question comment

execute immediate takes a string.  The snippet you posted has no strings in it.  You'd need to assemble the appropriate string and then pass that to execute immediate.  Something like:

execute immediate 'alter table ' || var_table_name || ' rename ' ||
    var_col || ' to ' || var_new_col`.

Of course, you probably want to put that in a local variable so that if/when it fails you can log the actual SQL statement.


MySQL Help with Desktop Based Administration I am making a MySQL based database for a professor and she would like to have a version uploaded to the web and a version on her desktop for personal use.  The web version would be "official" whereas the one on her desktop would be used for personal research and would have additional categories, etc.  The two databases do not have to be linked but I was unsure if it would be easier to put both databases on the web and make her personal one password protected or if there was a good desktop based program.  She is not very tech-savvy which is why I was leaning away from having her install MySQL and Apache on her own desktop.  Any help would be appreciated.

If you have a very small database, I recommend using XEROUND free database service.

REASON #1 : Database is stored in the Cloud for Free

REASON #2 : Automatic Backups of the Database

REASON #3 : You can load data over the Internet and retrieve it via mysqldump

REASON #4 : It comes with phpmyadmin (no installation required) to administer and query the database

REASON #5 : You can set up your own access to the data via external SQL tools

MySQL Workbench
HeidiSQL
MS Access
etc.

Restrictions

There are only three storage engines

MyISAM
MEMORY
XEROUND (Proprietary Transactional Cloud Storage Engine)
Sorry, no InnoDB

Maximum Database Size for Free Service is 10MB


Migrating Standalone named instance to AlwaysON Scenario:
I have a named instance such as SERVER1\NAME.

I need to migrate this instance to an AlwaysON, but I'd like to use the same instance name in the listener. Is it possible?

I was planning a side-by-side migration:

Create 2 new instances. (SERVER2\NAME and SERVER3\NAME)
Create a Listener called SERVER1_NEW.
Once everything is ready for the cut-over, just rename the SERVER1 to SERVER1_old and then rename SERVER1_NEW to SERVER1.

Is that going to work?
The plan is to avoid changing the connection strings everywhere.

Yes, its very much possible and I completely concur with your idea as starting with new server and do all the required and once all set-up, get a short window of down-time. Take database backup and restore it on the new server. Once sanity and basic testing is completed successfully, switch off the old server or rename it with some other name and allocate that name to new server.

Create 2 new instances. (SERVER2\NAME and SERVER3\NAME)
Create a Listener called SERVER1_NEW.
Once everything is really for the cut-over, just rename the SERVER1
to SERVER1_old and then rename SERVER1_NEW to SERVER1.

You may read wonderful article on how to do set up of windows always on availability group by Priyanka Devre as below:

https://www.sqlshack.com/configuring-a-sql-server-alwayson-high-availability-group/

Hope above helps.


Is it possible to format SQL on output into associative array? We have a table.

customer
+----+------+------+-------+
| id | type | name | group |
+----+------+------+-------+
| 1  | boy  | dave | adult |
| 2  | girl | lisa | child |
| 3  | girl | amy  | adult |
| 4  | boy  | ivan | child |
+------------------+-------+

If we query this table with

SELECT * FROM `customer` WHERE type = 'girl'

Result output

Array
(
    [0] => Array
        (
            [id] => 2
            [type] => girl
            [name] => lisa
            [group] => child
        )
    [1] => Array
        (
            [id] => 3
            [type] => girl
            [name] => amy
            [group] => adult
        )
)

I would like to receive the output like the following.

Array
(
    [girl] => Array
        (
        [child] => Array
            (
                [id] => 2
                [name] => lisa
            )
        [adult] => Array
            (
                [id] => 3
                [name] => amy
            )
        )
)

I could manipulate the result with another programming language, but wondered if this is possible with pure SQL?

Thanks in advance.

As far as I know, no SQL has yet been designed to return associative arrays, but I could be wrong!


SQL Server Always On Node and File Share Majority I am looking for some guidance on architecting our SQL Server 2016 SP1 Always On Availability Groups HADR solution on Windows Server 2012 R2. We have a primary site A with a primary replica and a secondary replica and a disaster recovery (DR) site B with a secondary replica and a file share witness. Our goal is that if the primary replica server 1 at site A is down, the Always On Availability Group (AG) fails over to the secondary replica server 2 at site A, and if both servers at site A are down, the AG fails over to site B.

We are attempting to do a Node and File Share Majority configuration per https://technet.microsoft.com/en-us/library/cc731739(v=ws.11).aspx and this diagram:

This diagram shows that when one node and the "disk" / file share witness are in communication the cluster runs, but in our testing of this situation, the cluster fails due to a loss of quorum of the WSFC. The automatic AG failover works if we test a failure of one server at a time by disabling the NIC in vmWare since SQL Server 2016 supports two automatic failover target replicas. However, it does not work if we fail both servers at site A at the same time to simulate a point-to-point network failure or a site power failure.

The following approach with manual intervention to force the quorum will work but it is not automatic, which is what we want in the ideal situation:

$node = "SQLServerC"
Stop-ClusterNode –Name $node
Start-ClusterNode –Name $node –FixQuorum

ALTER AVAILABILITY GROUP SQLServerAO FORCE_FAILOVER_ALLOW_DATA_LOSS;

$node = "SQLServerC"
Stop-ClusterNode –Name $node
Start-ClusterNode –Name $node

I appreciate any suggestions you can offer and thank you in advance!

Our goal is that if the primary replica server 1 at site A is down, the Always On Availability Group (AG) fails over to the secondary replica server 2 at site A, and if both servers at site A are down, the AG fails over to site B.

Except for a very specific scenario, this will not work. In all other scenarios you'll lose quorum before you'd ever be able to automatically fail over (which requires... quorum).

The best answer would be to have the DR side be manual failover (it could still be synchronous) with the proper documentation on how to force quorum and bring the AG online.

You could also invest more into VMWare and use their technologies but that assumes the infrastructure, licensing, and ability to implement those products for a specific service such as this.


Security concerns on SMTP in SQL Server In my company, while I try to  get the ports opened to connect to my SMTP server from my SQL Server running on Windows Server 2012, I get a reply saying that it is a security concern to open ports from SQL Server hosts to the SMTP server and it cannot be allowed. I'm just trying to understand what could be the security issues in having the Windows server hosting the SQL Server in allowing simple database mail alert notifications?

The security concern with SMTP from a database server is the potential for disclosure of sensitive information.  For example, a user that has (or gains) permissions to execute sp_send_dbmail could send an email with sensitive information.  Disallowing outbound access to the default SMTP port is a security measure security folks sometimes insist upon to limit such exposure.  It is common to lock down overall database server network traffic in an environment with particularly sensitive information such that network connectivity is allowed only to ports/hosts specifically needed, and permitted only after approval by the security department.

You need to work with your security team if your organization does not yet have an infrastructure for database administrative alerts.  They may allow a security exception for SMTP if there are additional measures in place to prevent ad-hoc email access on both the database and SMTP server.


Storing data in multiple rows vs serialized data I am currently designing a database table where I store list of courses chosen by students for a session. My current design has to do with storing each course in a row and a student can have up to 30 courses per session. This means that 300 students in that single department will consume 30*300 = 9000 rows for just a session. There over 50 departments in the School and each department has at least 300 students per level (5 levels) and the minimal courses taken per session is up to 30. The system is designed in such a way that these data stored will not be deleted.

Meaning:

50 (departments) * 300 (possible students) * 5 (levels) * 30 (rows for courses) = 2, 250,000 (possible rows consumed in a session).

Serialized Format

Is it better to store a student courses in a serialized format that use just a row to save it?

50 (departments) * 300 (possible students) * 5 (levels) * 1 (rows for courses) = 75,000 (possible rows consumed in a session).

I calculated this and I think it makes sense but I don't know if it is a faulty design.

Low Point
I recognised that update and deletion query will overwrite/delete everything in the courses column.

Please an insightful help will be appreciated.

Note: I use MySql for my database.

2M rows is "medium-sized".  That should not be your concern; databases are designed to handle millions, even billions, of rows.  However you will need to have suitable indexes, and include the optimal PRIMARY KEY.

Think about what "entities" you have -- departments, students, sessions, courses, etc.  Thin think about the "relationships" -- course in department; student in a session of a course; etc.

1:many is handled by an id in one table for JOINing to another table.  many:many is handled by an extra table with two ids in it.  1:1 is (usually) a no-no.

Work through some tutorials on database design.  Be sure to understand JOINs.


Replace column with dynamic value There's a column in my database which has got this kind of value:

id|       body
-----------------------------------
1 | ...  ...
2 | ...  ...
3 |...  ....

Basically, after the = sign I can have anything. And I would like to change it for nothing.

I know if it was a static value I could use:

SELECT REPLACE(column, $$$$, ' ') FROM table

But I couldn't do it dynamically.

regexp_replace() seems ideally suited

SELECT REXEXP_REPLACE(column, $$<img src=[^>]*>$$, ' ','g') FROM table


Assuming your status is consistent, i.e. always comes in pairs you could do something like:

select dt, employee_id, min_tm, max_tm, sum(delta_tm)
from (
    select dt
         , employee_id
         , min(tm) over (partition by employee_id,dt) as min_tm
         , max(tm) over (partition by employee_id,dt) as max_tm
         , tm - lag(tm) over (partition by employee_id,dt order by tm) delta_tm
         , status
    from t
) as u
where status = 'O'
group by dt, employee_id, min_tm, max_tm;


Replication Monitor Missing Subscriptions in Watch List In Replication Monitor, for one of our publications (transactional replication), none of the subscriptions are showing up in the Subscription Watch List. This is new behavior that just started recently.

The other publications associated with this distributor don't exhibit this behavior, the subscriptions are visible under the Subscription Watch List tab.

I can reproduce similar behavior using the Sp_replmonitorhelpsubscription SP. When executing this using the @publisher that is not returning results in the Replication Monitor, no results are returned. But when using the @publisher that do show subscriptions, results are returned.

I've confirmed in the subscription database for this affected publisher that transactional replication is functioning as expected, commands are being delivered, row counts are in sync.

Is there anything that can be done to get the subscriptions to appear again for the affected publisher? The only thing I can think of doing is deleting the subscription and publication and starting over, since some metadata must be missing, but that requires a downtime and seems unnecessary.

Version: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0

Update for resolution if someone is having similar situation.
I faced similar scenario where I could not get subscription agent list pulled up when launched replication monitor neither I could get the result from Sp_replmonitorhelpsubscription and the query kept on running without result. When checked currently executing query, all the  distribution agent jobs were being blocked by couple of pull subscription distribution agent also causing issue for other distribution agents jobs and couldnot pull replicated commands. I stopped and restarted those 2 head blockers distribution agent jobs that released the rest of distribution agent jobs. This resolved the issue with subscription agents not being showed up on the replication monitor.


New Analyst SQL Optimization - Multiple Spools Unfortunately my ability to Query has outgrown my knowledge of SQL Optimization, so i am hoping someone would help a young analyst by looking at this atrocious execution plan and provide some wisdom as to how i could speed it up.  I've read a few threads about spooling, but they were all mostly a discussion about weather an Eager Table spool is good or bad, and the answer is always "it depends".

My execution plan looks like it's Spooling and Sorting the same #Temp Table multiple  times, and it's eating up a lot of execution cost.

My understanding of a Table Spool is that it is temporary storage to be used later, but if the data is already stored for later use, why would it spool the same data over and over again?  My query doesn't require any ordering so why would it need to sort the same #TempTable/Spool multiple times?

I'm so new to this, i can't figure out how to attach the entire execution plan.... so i attached an image of the bottom half of it...

Help me experienced analysts. You're my only hope.

A Little Context.

I currently have a transaction table that tracks all changes made to a lead in my CRM, and i am attempting to create a new table from this data to speed up reporting.

I am pulling data from this transaction table and flagging the first action, first user, and other firsts of a lead by using Row_Number().  I am then inserting every "first" into a #Temp Table, as i know i am going to utilize this data multiple times.

SELECT
ID,
Action,
ROW_NUMBER() OVER (PARTITION BY ID, Action ORDER BY DATE) AS ActionNum,
ROW_NUMBER() OVER (PARTITION BY ID, Actor  ORDER BY DATE) AS USERNUM
INTO #Temp
FROM Table
;

I am then Left joining this #Temp Table many times (10 times actually).  I have tried multiple other ways of solving this issue but using Row_Number multiple times seems like the best solution.

SELECT
*
FROM #temp T1
     LEFT JOIN #Temp T2
               ON T2.ID = T1.ID AND T2.Action = A2 AND T2.ActionNum = 1
     LEFT JOIN #Temp T3
               On T3.ID = T1.ID AND T3.Action = A3 AND T3.ActionNum = 1
     LEFT JOIN #Temp T4
               ON T4.ID = T1.ID AND T4.UserNum = 1
WHERE
T1.Action = A1
AND
T1.ActionNum = 1

I've looked into creating a clustered index on the #TempTable, but i must not be doing it right because it didn't change anything about my execution.

Thanks in advance for all your help! Any good reading material is also greatly apprecaited!

Best,
Austin

Have you considered created a permanent table in the / a database with clustered index creation (and appropriate NCIs)? You can then simply query that table and allow for statistics to build up - presumably and only with a very limited understanding of your actual context, if the data is transactional, you can build up that historical data and re-use it for different use cases.
B


postgresql function to automatically merge and add two jsonb values from different tables when one is updated and store it in a third Lets say that tableA has column columnA with jsonb data in it like the following:
{
   "A": 5,
   "B": 15,
   "C": 20
}

and tableB has columnA with jsonb data in it like the following:
{
   "B": 10,
   "C": 55,
   "D": 60
}

Is it possible to set it up so that whenever either of the jsonb columns from tableA or tableB change then both jsonB columns will be taken, merged and added to a third table, tableC?
so in this example, if the column in tableB was modified, then what gets stored in tableC will be:
{
   "A": 5,
   "B": 25,
   "C": 75,
   "D": 60
}

The keys and values are retrieved from separate APIs and are unknown ahead of time. I'd like to keep them separate but also have a table that summarizes all the overall values.
Currently using postgresql 12.5 but can move to 13.1 if necessary
thanks in advance

Check out this PostgreSQL function
CREATE OR REPLACE FUNCTION public.test1(in_r1 json, in_r2 json)
 RETURNS json
 LANGUAGE plpgsql
AS $function$
DECLARE
 sum_R jsonb;
 R record;
 x text[];

BEGIN
 sum_R = '{}'::jsonb;
 /* FOR R IN SELECT * FROM json_each_text(in_R1) */
 FOR R IN SELECT * FROM json_each(in_R1)
 LOOP
   raise notice 'key %', R.key;
   raise notice 'value %', R.value;
   x := array_append(array[]::text[],R.key);
   raise notice 'x %', x;
   raise notice 'jsonb value  %', to_jsonb(R.value);
   sum_R := jsonb_insert(sum_R, x, to_jsonb(R.value));
   raise notice 'sum_R %', sum_R;
 END LOOP;
 FOR R IN SELECT * FROM json_each_text(in_R2)
 LOOP
   raise notice 'key %', R.key;
   x := array_append(array[]::text[],R.key);
   raise notice 'x %', x;
   raise notice 'jsonb value  %', to_jsonb(R.value);
   raise notice 'jsonb old value  %', sum_R->>R.key;
   if ((sum_R->>R.key = '') is not false) then
     sum_R := jsonb_insert(sum_R, x, to_jsonb(R.value));
   else
     raise notice 'sum %', (sum_R->>R.key)::int + R.value::int;
     sum_R := jsonb_set(sum_R, x,
                        to_jsonb((sum_R->>R.key)::int + R.value::int));
   end if;
   raise notice 'sum_R %', sum_R;
 END LOOP;
 RETURN to_json(sum_R);
END;
$function$

Call it like
select test1('{"A": 5, "B": 15, "C": 10 }','{"A": 1, "C": 5, "D": 1}');

to get
                test1
--------------------------------------
 {"A": 6, "B": 15, "C": 15, "D": "1"}
(1 row)

apart from a lot of comments.
Using HSTORE for intermediate results:
CREATE OR REPLACE FUNCTION public.test2(in_r1 json, in_r2 json)
 RETURNS json
 LANGUAGE plpgsql
AS $function$
DECLARE
 sum_R hstore;
 R record;
 x integer;

BEGIN
 sum_R = ''::hstore;
 /* FOR R IN SELECT * FROM json_each_text(in_R1) */
 FOR R IN SELECT * FROM json_each(in_R1)
 LOOP
   sum_R := sum_R || hstore(R.key::text, R.value::text);
   raise notice 'sum_R %', sum_R;
 END LOOP;
 FOR R IN SELECT * FROM json_each_text(in_R2)
 LOOP
   x := sum_R->R.key;
   if (((x::text) = '') is not false) then
     sum_R := sum_R || hstore( R.key, R.value::text);
   else
     sum_R := sum_R || hstore( R.key, (x + (R.value::int))::text);
   end if;
   raise notice 'sum_R %', sum_R;
 END LOOP;
 RETURN hstore_to_json(sum_R);
END;
$function$


Get TEXT value of a CLOB OID in Postgresql I have a database table that looks like:

create table answers(
   id int not null,
   question_id int not null,
   answer text null
)

This table was originally build by Hibernate using the @Lob attribute for the "answer" column.  I did not realize it at the time, but when setup that way, Hibernate stores an OID in the column instead of the actual text.  Everything works fine when I use Hibernate to retrieve the values since it automatically converts the OID to the CLOB string, however it is becoming a performance problem and I'd like to get rid of the OID.

select * from answers
ID     QUESTION_ID     ANSWER
===============================
1       123             55123
2       234             51614
3       345             56127
should be
ID     QUESTION_ID     ANSWER
===============================
1       123             Male
2       234             203-555-1212
3       345             555 Main St. New York, NY

My desire is to add an extra column to the table "ANSWER_VALUE TEXT" and do something like below to get the actual value into the table, then change Hibernate around to not use the @Lob designator

update answers set ANSWER_VALUE= getValueFromOID(ANSWER)

Does that "getValueFromOID" function exist?  If not, could I get some pointers on how to create one or at least how to fetch the actual value of an OID?

Thanks

Thanks goes to a_horse_with_no_name.  Solution is:

update answers set answer_value = lo_get(cast(value as bigint))

Note - the lo_get function appears to be present in Postgres 9.4 or higher.  For earlier versions, I don't see a way of doing this directly.  I'm currently running 9.0, but this just accelerated my upgrade plans.


Remove InnoDB files (ibdata1 and iblog files) after migration to MyISAM I changed the only two InnoDB tables on a replication slave to MyISAM, so the slave now only has MyISAM tables.

Can I now remove the ibdata1 file completely?

In case you do not have a single InnoDB table, then yes, you can remove the ibdata file and the ib_logfile[01] files.

However, note:

With MySQL 5.6 there are system InnoDB tables, which means you don't want to remove the InnoDB files ever.
It is best done when the server is stopped.


Duplicate record problem create table #Personel
(
Ad varchar(100)
)

create table #Masa
(
MasaAdi varchar(100)
)

insert into #Personel values('deneme1')
insert into #Personel values('deneme2')
insert into #Personel values('deneme3')

insert into #Masa values('Masa1')
insert into #Masa values('masa2')
insert into #Masa values('masa3')

select Ad,MasaAdi from
(
select ROW_NUMBER() over(partition by MasaAdi order by ID,Ad ) as Sira,ID,Ad,MasaAdi
from (
select newid() as ID,Ad,MasaAdi
from #Personel
cross join #Masa
)t
) t2 where Sira = 1 order by Ad

it's creating the result as shown below.

deneme1 - masa2
deneme2 - masa3
deneme1 - masa1

well,it's to be able to deploy to different tables two times the same personal.

e.g deneme1 - masa2
    deneme1 - masa1

I don't want to do that.I want to do something as shown below.

e.g deneme1-masa2
    deneme2-masa3
    deneme3-masa1

So,I don't want to deploy to different tables two times the same personal.

with
cte1 as
(
    select *, row_number() over(order by Ad) RowNumber
    from #Personel
),
cte2 as
(
    select *, row_number() over(order by newid()) RowNumber
    from #Masa
)
select cte1.Ad, cte2.MasaAdi
from cte1
    join cte2 on
        cte1.RowNumber = cte2.RowNumber


Is it possible to estimate the restore progress in SQL Server 2014 from pages read/written? For context - SQL 2014 EE, we're doing tran log backups from our main production box and restoring to a R/O instance. The log is usually around 50GB.

So I'm familiar with the structure of SQL Server restores and know the percentage complete estimate only refers to the data copy phase. For us, this generally runs pretty quickly but the redo phase is where the time is taken.

From sp_whoisactive I can easily get pages read/written by the log restore. What I'm wondering is if I know the size of the log (which I do), can I estimate the progress of the restore from those numbers?

I intend to give is a quick go, but if someone has tried this already and it's a fools errand I'll skip it!

Look at percent_complete and estimated_completion_time in sys.dm_exec_requests. If it is in fact populated for restoring logs (I have only tested full database restores), it is going to be based on the same math you want to try and calculate manually.

SELECT percent_complete, estimated_completion_time
  FROM sys.dm_exec_requests
  WHERE session_id = <SPID running the backup>;

Obviously neither approach can predict pending concurrency / I/O interruptions, so it is nowhere near any sort of guarantee - just a ballpark guess. It also won't be possible to predict how much I/O will be required for any undo/redo operations, since these are not directly related to the size of the log.


How to get the daily amount of committed transactions on a given MSSQL database? The number of transactions for the current connection can be obtained with @@TRANCOUNT.

How can you get a daily counter of the globally committed transactions on a given database?

1) Below is a great script from Paul's blog  Script: open transactions with text and planswhich should get you going:

SELECT
    [s_tst].[session_id],
    [s_es].[login_name] AS [Login Name],
    DB_NAME (s_tdt.database_id) AS [Database],
    [s_tdt].[database_transaction_begin_time] AS [Begin Time],
    [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
    [s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
    [s_est].text AS [Last T-SQL Text],
    [s_eqp].[query_plan] AS [Last Plan]
FROM
    sys.dm_tran_database_transactions [s_tdt]
JOIN
    sys.dm_tran_session_transactions [s_tst]
ON
    [s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN
    sys.[dm_exec_sessions] [s_es]
ON
    [s_es].[session_id] = [s_tst].[session_id]
JOIN
    sys.dm_exec_connections [s_ec]
ON
    [s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
    sys.dm_exec_requests [s_er]
ON
    [s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY
    sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY
    sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
ORDER BY
    [Begin Time] ASC;
GO

You can schedule it accordingly via job to send you the results as per you're needs:

2) Also, you can refer to Adam Mechanic's WHO_IS_ACTIVE to get the details for same.

To gather the data from who_is_active you can log that to a table for later review as explained here How to Log Activity Using sp_whoisactive in a Loop


DB2 passwords with special characters Our DB2 authentication is integrated with active directory, so the allowed passwords are controlled by it's policy. We have some users that are not being able to connect to the database through Command Editor or the Query screen in Control Center. The error is the following:

SQL0104N  An unexpected token "!" was found following "".
Expected tokens may include:  "NEW".
 SQLSTATE: 42601
 sqlcode: -104

They are able to connect through Command Line Processor as far as they don't include the password in the connect statement.
The problem is affecting all users with some special characters in their passwords. We had them try issuing the connect statement without the password in Command Editor but it didn't work.
The exact version of the database is DB2/AIX64 9.5.4
Is there a way for them to use this tools other that to have them change their passwords?
Thanks

This is a long-standing bug in the GUI tools.

However, as long as your password does not contain a double quote (") character, then you can work around this by putting double quotes around the password to connect.

connect to sample user test using test!
--> SQL0104N

connect to sample user test using "test!"
--> connection succeeded.

This method will also work if the GUI tools prompt you for a password with a dialog box.

If you have passwords with double quotes in them, you may be out of luck.  You could open a ticket with IBM DB2 support and/or just change the password.


What are valid usage scenarios for HEAP tables? I am currently doing some data imports into a legacy system and discovered that this system does not use a single clustered index. A quick Google search introduced me to the concept of HEAP tables and now I am curious in what usage scenarios a HEAP table should be preferred over a clustered table?

As far as I understood a HEAP table would only be useful for audit tables and/or where inserts happen far more often than selects. It would save disk space and disk I/O since there is no clustered index to maintain and the additional fragmentation wouldn’t be a problem because of the very rare reads.

The only valid uses are for

staging tables used in import/export/ETL processes.
ad-hoc, temporary and short term backup of tables using SELECT * INTO..

Staging tables are typically quite flat and truncated before/after use.

Note that a clustered index is typically few small compared to the data size: the data is the lowest level of the index structure.

Heap tables also have problems. At least these:

can not be defragmented to reduce space on disk. This matters because used data pages will be scattered throughout the MDF for example, because data has no "order" from the clustered index
non-clustered index now point to the row, not the clustered index entry. This affects performance: Need for reaching data through clustered index with a non-clustered index

Also see

http://www.sqlbadpractices.com/heap-tables/ (Bad practice)
http://msdn.microsoft.com/en-us/library/hh213609.aspx (MS's recommendations)
http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2930)-fixing-heap-fragmentation.aspx (Paul Randal's DBA myths)


Hash index vs. btree index performance on inserts and updates I have table which is several time bigger than available RAM. Table size is 181 GB, 92 million rows. PostgreSQL 10.

There is field with uuid in the table.
And there is need to insert into the table several hundreds rows, from time to time.

What is better, hash index or b-tree index for fast inserts? The table is practically never read, it just store archive information. The index is necessary only to check what there is no row in the table with the same uuid.

Currently, inserts work really slow. Insert of 1 million rows now works for more than 4 hours, still running. Typically, it works for about 5 hours.

The query looks like this:

insert into TableName (...)
select [list of fields]
from table_data_forinsert;

No where condition, the source table contains only data which needs to be inserted.
And how to insert faster?

I think about create btree index, and sort data by uuid before insert.
May it help to improve performance for btree index? Or may be it is possible to do something else to improve performance? I not want to make the table unlogged, it is necessary to not lose the data.

Is any approach how to make inserts faster for hash index?

And same question for updates. What is faster for updates, b-tree index or hash index, if there is update of several hundreds of thousands of rows in big table? Are any methods how to make updates faster?
Like may be sort data in source table or something else?

A B-tree index will be the best when it comes to the speed of data modifications.

There is no way to significantly increase the speed of inserting a few hundred rows.

If you need the uniqueness guarantee, you cannot do without the index, so there is not much room for improvement.