Getting Machine name from where stored procedure was last executed Is there any way to find out machine IP or machine name from where a particular stored procedure is executed in SQL Server 2005?

We have a client server architecture with .NET as a front end and SQL Server 2005 as back end. Someone in production environment has executed one stored procedure & deleted critical data from .NET application. We are unable to deduce who has done this.

We know the exact time when this has happened. Is there any way we can find the all the connection in SQL Server at that time, so that we can find out machine name from logged connection?

If the database is in full recovery mode and you are doing periodic log backups then you can find the login that deleted the data, and the exact moment the data was deleted. Read How to read and interpret the SQL Server log. Also, with a proper backup strategy in place, it will be trivial to recover the deleted data as well.

On the other hand, if you do not have a proper backup strategy in place, then you well deserve to loose this data and more.


MySQL Security - Storing lots of sensitive phone numbers securely? Imagine a table in a database in MySQL that holds millions of telephone and cellphone numbers that relate to itemized billing for VoIP and Cellular carriers.

All of these numbers are in VARCHAR fields and they are clearly visible to anyone who has access to the database. These numbers need to be like this because they are read out to form parts of reports and because there are millions of records, it's quite resource intensive to sift through it all.

I'm in search of ways to store these numbers in a way that would be well... Safer.

Say for instance, the database got compromised and someone else gained access to this information... They would have millions of active numbers and could really make havoc out of that.

So to avoid this situation (it's not physically possible at the moment as we're on a closed network) in the future, what would be the right thing to do with all of these numbers?

I need a way to make then like a hash and then unhash them on request... The problem is the resources involved in this, any one number could have thousands of records so this would end up being quite resource intensive... There must be a more suitable solution for something like this?

I'd like to say that I am really looking for some opinions, feedback, input or guidance from some professionals with relative experience. I'm sure that in the manual there is something somewhere that covers something sort of like this... But I cannot seem to find it so I would like some insight into how to deal with this the correct way!

Thank you.

EDIT: I'd like to note that this question is not about server security, it is about information security... When i look at the database and see raw numbers it just feels wrong... Is it wrong or am i just being paranoid?

If you store encrypted data in the database, the data is not understandable anymore without a layer of code on top. This might seem like a good move, but if one takes a closer look, it complicates things and does not provide more security.

An example which becomes a lot more complicated: "Compute the number of phone numbers with a special area code". How to do this in a normal DB? Well something like SELECT COUNT(*) FROM phonenumbers WHERE phonenumber LIKE "XZY%" will work. How to do this, if the database cannot understand its own data? Transfer everything to the application, decrypt it and compute the result there. That can obviously be really cumbersome with large databases. Basically all advantages one receives through using a DBMS are nullified.

Why this is not more secure? One does not even have to hack your database anymore, it is enough to hack the application. Likely this is easier, since the application uses a lot of custom code. Further the firewall will allow access to the application servers, while it will likely prohibit access to database servers from outside.

What to do instead? Secure the database server and restrict access to sensitive data. You can (should) create a dedicated area for sensitive data, which can only be accessed with dedicated database accounts. This credentials should be used rarely in your application, and should not be handed over easily to e.g. developers. You could even set up an own dedicated isolated database server for such sensitive information.


MySQL trigger: Compare value in another table I have the following trigger (generated using PHP via mysqli)...

DROP TRIGGER IF EXISTS post_queue_insert;
CREATE TRIGGER post_queue_insert AFTER INSERT ON posts
FOR EACH ROW BEGIN
    IF (NEW.post_type != 'revision' AND NEW.post_status != 'auto-draft') THEN
        INSERT INTO event_queue (action_id, action_do, action_key, action_value, action_event, action_timestamp, sync_complete, SITE_ID) VALUES (NEW.ID, 'post', NEW.post_type, NEW.post_status, 'insert', UNIX_TIMESTAMP(now()), 0, 1);
    END IF;
END;

What I can't figure out is how to check if the 'post_type' value exists in another table and if it doesn't then insert it.

?

DROP TRIGGER IF EXISTS post_queue_insert;
CREATE TRIGGER post_queue_insert AFTER INSERT ON posts
FOR EACH ROW BEGIN
    DECLARE found_it INT;
    IF (NEW.post_type != 'revision' AND NEW.post_status != 'auto-draft') THEN
        SELECT COUNT(1) INTO found_it FROM some_other_table
        WHERE post_type = NEW.post_type;
        IF found_it = 0 THEN
            INSERT INTO event_queue (action_id, action_do, action_key, action_value, action_event, action_timestamp, sync_complete, SITE_ID) VALUES (NEW.ID, 'post', NEW.post_type, NEW.post_status, 'insert', UNIX_TIMESTAMP(now()), 0, 1);
        END IF;
    END IF;
END;

If found_it = 0, then the row does not contain a value matching NEW.post_type.

Give it a Try !!!


Storing bitsets in PostgreSQL? I need to calculate reach, grouped by different criterions, and would prefer to keep everything in my database. I'm looking for either a pre-built bitset implementation for PostgreSQL, or pointers on how I may achieve my goal.

What I'm currently doing is storing serialized Java instances of EWAHCompressedBitSet in bytea columns, but this means I have to round-trip to a Java process to continue my calculations.

Ideally, I'd like to be able to do the following:

-- Unique people reached during a time window
SELECT   service_name, last_updated_at, bitset_or(followers_bs)
FROM     followers
GROUP BY service_name, last_updated_at

I have used a commercial solution (Truviso) that had such a feature, and it was based on PostgreSQL as well.

Note that I do have a table that has the data in an uncompressed format, but because the data is so huge (10M rows and counting), JOINing on it simply takes too much time than I want to devote to it.

Can you use the "bit" type instead of "bytea", and use your own user-defined aggregate?

postgres=> CREATE AGGREGATE bitset_or ("bit")
postgres-> ( sfunc    = bitor,
postgres->   stype    = "bit",
postgres->   initcond =   '0'  );

Now bitset_or should work with your query as written (again, assuming bytea -> bit).


SSIS - converting string to HH:MM:SS I am trying to read flat file with some data and I need to convert some columns to time type in the format of HH:MM:SS.
Below is the example how they are stored in the file (on the left) and how I want them to see in the database (on the right).

846 ---> 08:46:00
1323 ---> 13:23:00

I am performing following transformations:

if NA value appears, I am converting it to NULL (with Derived column)

then I am concatenating it to string in format of HH:MM:SS (using Derived Column)
LEN(ArrTime) == 3 ? "0" + SUBSTRING(ArrTime,1,1) + ":" + SUBSTRING(ArrTime,2,2) + ":00" : SUBSTRING(ArrTime,1,2) + ":" + SUBSTRING(ArrTime,3,2) + ":00"

then I am converting it to the type DT_DBTIME2 with scale of 0 (using Data Conversion):

And this last step makes the package falling. Here is error message:

[Data Conversion [20]] Error: Data conversion failed while converting
column "ArrTime" (166) to column "Copy of ArrTime" (27).  The
conversion returned status value 2 and status text "The value could
not be converted because of a potential loss of data.".

I am not very experienced in SSIS, but I really cannot see any reason for such an error.
When I tried to perform it without conversion, everything had gone fine.
Can anybody help me with that?
Greetings,
Chris.

Update:
It was related to specific construction of dataset.
if the time was in range from 00:00:00 to 00:59:00 it was presented as follows (examples):

4 ---> 00:04:00
57 ---> 00:57:00

So the previously posted code did not even take such a case into consideration.
The code solving the problem is following:
LEN(ArrTime) == 1 ? "00:0" + ArrTime + ":00" : (LEN(ArrTime) == 2 ? "00:" + ArrTime + ":00" : (LEN(ArrTime) == 3 ? "0" + SUBSTRING(ArrTime,1,1) + ":" + SUBSTRING(ArrTime,2,2) + ":00" : SUBSTRING(ArrTime,1,2) + ":" + SUBSTRING(ArrTime,3,2) + ":00"))

Now I solved the problem and all works fine.
Maybe someone will need such solution ;)


What is an effective way at handling the issue of Postgres allocating significant memory when it forks a new process? I've seen many articles and videos that say things like the following about Postgres over MySQL.

Postgres allocates a significant amount of memory (about 10MB) when it
forks a new process for each connection. This causes bloated memory
usage and effectively eats away at speed. Thus, it sacrifices speed
for data integrity and standards compliance. For a simple
implementation, then, Postgres would be a poor choice! - Sumo Logic

Every time I read that or hear that somewhere, there's no context about what it really means or if there is a way to handle it. What is are specific way to deal with that type of problem in PostgreSQL? Is this overcome by using connection pools?

Interesting to hear that 10MB is "a significant amount of memory".
A database is not a web server, which is optimized for serving lots of short-lived connections. A PostgreSQL connection loads cached catalog data for efficiency.
That is why you use a connection pool, so that all your short database requests are handled by a small number of persistent database connections.
I doubt that this is specific to PostgreSQL – other databases benefit from connection pools as well, and some even have one built into the server. So I would see the statement you quote as hate speech from a competitor who cannot think of anything better than reiterating the old myth that PostgreSQL is slow and complicated.


Generate INSERT list but only for non-nullable columns I am currently using SSMS 2016 as client and have an Azure SQL database (also 2008 r2).

But I am looking for a way to create a custom template to have an insert statement prepared with only the fields that have 'non-null'

If I right click on a table and generate an insert into statement, I get a really long list of all possible columns. I would like to see the bare minimum of columns which needs to be filled. Is there a way to generate such a sql script for ssms? Or is this only possible with other db tooling?

I have a table with over 30 columns and about 90% is allowed to be null, so I prefer my generated insert statement to only include the 10% of columns which I really need to fill in.

You can't change the way the UI functions, you'll have to write that kind of code yourself, sorry. But in the simple case, this is easy enough to generate on your own with sys.dm_exec_describe_first_result_set (save this code as a template or snippet):

DECLARE @table nvarchar(261) = N'dbo.YourTableName';

;WITH x AS
(
  SELECT name, system_type_name,
    column_ordinal = ROW_NUMBER() OVER (ORDER BY column_ordinal)
  FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM ' + @table, N'', 0)
  WHERE is_nullable = 0
    AND is_identity_column = 0
    AND is_computed_column = 0
)
SELECT N'INSERT ' + @table + N'(', N'--', column_ordinal = 0
UNION ALL
SELECT CHAR(13) + CHAR(10) + CASE column_ordinal
  WHEN 1 THEN N'' ELSE N',' END + QUOTENAME(name),
  N'--', column_ordinal
FROM x
UNION ALL
SELECT N'
) VALUES (', N'--', 1000
UNION ALL
SELECT CHAR(13) + CHAR(10) + CASE column_ordinal
  WHEN 1 THEN N'' ELSE N',' END
  + N'<' + name + N', ' +system_type_name + N',>',
  N'--', 1000 + column_ordinal
FROM x
UNION ALL
SELECT N'
);', N'--', 10000
ORDER BY column_ordinal;

This leaves out the identity column and any computed columns, but the view doesn't tell you about other things like not null columns with defaults or rowguidcol columns, which should also stay out of your statement.

The following solution is probably more appropriate in general since you are still using SQL Server 2008 R2 (that view was introduced in SQL Server 2012). It handles rowguidcol and default values but is a lot more verbose because of the work required to generate friendly-looking types.

DECLARE @table nvarchar(261) = N'dbo.YourTableName';

;WITH x AS
(
  SELECT [column] = c.name, [type] = t.name, t.system_type_id,
    t.user_type_id, c.max_length, c.precision, c.scale,
    column_ordinal = ROW_NUMBER() OVER (ORDER BY c.column_id)
  FROM sys.columns AS c
  INNER JOIN sys.types AS t
    ON c.system_type_id = t.system_type_id
   AND t.user_type_id = CASE -- filter out false sysname
     WHEN c.system_type_id = 231 THEN CASE
       WHEN c.max_length = 256 THEN c.user_type_id
       ELSE c.system_type_id END
     ELSE c.user_type_id END
  WHERE [object_id] = OBJECT_ID(@table)
    AND c.is_nullable       = 0
    AND c.is_identity       = 0
    AND c.is_computed       = 0
    AND c.is_rowguidcol     = 0
    AND c.default_object_id = 0
)
SELECT N'INSERT ' + @table + N'(', N'--', column_ordinal = 0
UNION ALL
SELECT CASE column_ordinal WHEN 1 THEN N'' ELSE N',' END
    + QUOTENAME([column]), N'--', column_ordinal
  FROM x
UNION ALL SELECT N') VALUES(', N'--', 1000
UNION ALL
SELECT CASE column_ordinal WHEN 1 THEN N'' ELSE N',' END
    + N'<' + [column] + N',' + [type] + CASE
    WHEN system_type_id IN (106,108)
    THEN N'(' + CONVERT(varchar(11), precision)
       + N',' + CONVERT(varchar(11), scale) + N')'
    WHEN system_type_id IN (59,62)
    THEN N'(' + CONVERT(varchar(11), precision) + N')'
    WHEN system_type_id IN (41,42,43)
    THEN N'(' + CONVERT(varchar(11), scale) + N')'
    WHEN system_type_id IN (167,175,239,231,165,173) AND user_type_id <> 256
    THEN N'(' + CASE max_length WHEN -1 THEN N'max' ELSE
      CONVERT(varchar(11), max_length / CASE
        WHEN system_type_id IN (231,239) THEN 2 ELSE 1 END) END + ')'
    ELSE N''
   END
    + N',>', N'--', 1000 + column_ordinal
  FROM x
UNION ALL SELECT N');', N'--', 10000
ORDER BY column_ordinal;

Note that in both cases I tried to match the output of SSMS exactly with the exception of every single tab and carriage return. I don't think their output makes sense in all aspects, but you can tailor it to your liking.


Why seq-scan can be much faster than index-scan and index-only-scan in this simple query? I am using PostgreSQL 9.4.4. I have a query like this:

 SELECT COUNT(*) FROM A,B WHERE A.a = B.b

a and b are the Primary Keys of tables A and  B, so there are B-indexes on a & b

By default, PostgreSQL will use seq-scan on AB and use hash join, I force it to do the index-scan and index-only-scan.

The result showed that, seq scan is much faster than the other two, it takes more time to do the full scan on a,b for index-scan and index-only-scan.

EXPLAIN ANALYZE SELECT COUNT(*) FROM journal,paper WHERE journal.paper_id = paper.paper_id;

Can someone explain it?

Thank you so much!

This is quite a common query (pardon the pun! :-) ) from people running queries which perform full table scans (FTS), when the poster feels that the system should make use of the index(es).

Basically, it boils down to the explanation given here. If the tables are so small, the optimiser will say that "it's not worth the bother of going to the index, doing a lookup and then fetching the data, instead, I'll just slurp in all the data and pick out what I need", i.e. perform an FTS.

[EDIT in answer to @txsing's comment]

For an MVCC (multi-version concurrency control) database, you have to traverse every record for a count at a given moment - that's why, for example, a COUNT(*) is much more expensive for MySQL's InnoDB rather than MyISAM.

An excellent explantion (for PostgreSQL) is available here. The guy who wrote this post is a "major contributor" to PostgreSQL (thanks to @dezso for leading me to that post).


Table indexes for autocomplete in MySQL I need to implement an "autocomplete-like" query in MySQL, one on a fairly short textual field varchar(11) and one on a potentially long field varchar(140).

As they are autocomplete query, they will be LIKE-query. What sort of indexes I need to configure?

not really big choice - it will be normal B-Tree index

with autocomplete You can not use FullText because it work only with full words

at the same time queries like:

SELECT text FROM tag_table WHERE text LIKE 'part_text%';

will use indexes

add:

in case of big column -

You of course could try to realise auto-complete by whole words - there You can create full text index, but not sure - how good it will work, because with fulltext always a lot of variants at the begin of search phrase, and variants how suggested text will be relevant to expected result


Assessing the normal form for a relation with a multi-valued attribute Assume that you have designed the following schema of relations:

COURSES (CID, CNAME)
STUDENTS(SID, SNAME, FAV_CIDS)

However, you can not have a relation with set valued attributes (Did not really understand what was meant by this) and you
need to convert it to a normal form. What is the normal form and the resulting schema?

We are studying SQL at school. Could not really make out which normal form this was in and functional dependencies.

You first need to know what's the relationship between those two entities: Courses and Students. But, you also need to know the abstraction of those entities into your database schema; we all know that a Student could be enrolled in many Courses, and a Course could contain many Students. The relationship between them are many-to-many.

The way to enforce a many-to-many relationship is by creating a table apart that will store as Foreign Keys the Students, and Courses Primary Keys. Both foreign keys will compound the table's primary key. This will gives to you the flexibility to store many courses, and many students.

However, check below how the tables should look like:

Courses(CourseId (PK), CourseName)
Students(StudentId (PK), StudentName)
StudentsCourses(StudentId (FK)(PK), CourseId (FK)(PK))


How to connect to sql server with dynamic IP Address I have installed sql server express edition 2014 on my machine. And I am connecting to it with CRM using my local machine's IP address.

The problem here is my IP address allocated to my machine is dynamic and changes every day when I log in, which makes it impossible for me to connect to CRM (as the IP address changes obviously).

What to do in that case? Any help will be appreciated.

Instead of connecting via IP address, connect to your machine's name. If you're connecting locally, you can use LOCALHOST as the name, or just a period ("."), which connects you to your local default instance.


Make Xtrabackup ignore myisam tables & backup only innoDB tables? I'm using mysql 5.7 (or rather percona 5.7)

Is it possible to backup only the innodb tables (easily, not to specify each table one by one because I have a lot of tables) with xtrabackup? and not take into account myisam tables that are read-only?

If I remember right, if you have myisam tables it will lock the whole DB thus breaking the application during the backup procedure.

Xtrabackup website says something different:

  Percona XtraBackup can back up the following storage engines by briefly pausing writes at the end of the backup

This information used to be in the documentation, probably better detailed, but nowadays only this enigmatic claim remains. Probably you are worrying too much, but I will answer the rest of the question anyway.

There doesn't seem to be any way to easily exclude non-InnoDB tables from the backup. What you can do is to manually exclude those tables with --table-exclude If you have entire databases dedicated to non-InnoDB tables, --databases-exclude should be easier to use.

You can dynamically compose the list of those tables with this query:

SELECT GROUP_CONCAT(CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) SEPARATOR ',')
    FROM information_schema.TABLES
    WHERE
        ENGINE <> 'InnoDB'
        AND ENGINE IS NOT NULL
        AND TABLE_SCHEMA NOT IN (
            'mysql',
            'performance_schema',
            'information_schema',
            'sys'
        );


Oracle Audit on Types I found that auditing can be done on oracle schema objects like procedures and views. Is there any possibility to do the auditing on Types?

Types can be altered, granted/revoked privileges on, so why not?

Example:

SQL> select object_type from dba_objects where object_name = 'T1';

no rows selected

SQL> create type t1 is table of varchar2(20);
  2  /

Type created.

SQL> audit all on t1;

Audit succeeded.

SQL> select * from dba_obj_audit_opts where object_name = 'T1';

OWNER                OBJECT_NAME          OBJECT_TYPE             ALT       AUD       COM       DEL       GRA       IND       INS       LOC       REN       SEL       UPD       REF EXE       CRE       REA       WRI       FBK
-------------------- -------------------- ----------------------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --- --------- --------- --------- --------- ---------
BP                   T1                   TYPE                    A/A       A/A       -/-       -/-       A/A       -/-       -/-       -/-       -/-       -/-       -/-       -/- A/A       A/A       -/-       -/-       -/-

See DBA_OBJ_AUDIT_OPTS and AUDIT for details.


SQL Server 2008 R2 Dirty reads - how non-atomic? I am wondering "how dirty" dirty reads can get under a read-uncommited isolation level. I understand that rows that have been updated but not yet committed are visible, but:

Can a row appear as partially updated - that is, some of the columns are updated and some are not?
Can a single column appear partially updated. For example, if you have a varchar(4000) column that was in the process of being completely updated and assuming it actually contains 4000 chars. Can you read say 2k chars from the previous state and 2k chars from its new state?
What about varchar(max) with length >8k?

Update: After some debating, the minimal consensus is that if the column size is >8KB, dirty reads, even within the column itself, are possible.

EDITED after reading the MSDN forum link from the comment, very interesting.

Regardless of isolation level, two users cannot update a single page simultaneously, nor can any user read a partially updated page.  Just imagine how SQL Server would deal with a page where the header says Col3 starts at byte 17.  But it really starts at byte 25, because that part of the row hasn't been updated yet.  There's no way a database could handle that.

But for rows larger than 8k, multiple pages are used, and that makes a half-updated column possible.  Copied from the MSDN link (in case the link breaks), start this query in one window:

if object_id('TestTable') is not null
    drop table TestTable
create table TestTable (txt nvarchar(max) not null)
go
insert into TestTable select replicate(convert(varchar(max),
    char(65+abs(checksum(newid()))%26)),100000)
go 10
update TestTable set txt=replicate(convert(varchar(max),
    char(65+abs(checksum(newid()))%26)),100000)
go 100000

This creates a table and then updates it with a string of 100.000x the same character. While the first query is running, start this query in another window:

while 1=1 begin
 if exists (select * from TestTable (nolock) where left(Txt,1) <> right(Txt,1))
    break
end

The second query stops when it reads a column that is half updated.  That is, when the first character is different from the last.  It will finish quickly, proving that it is possible to read half-updated columns.  If you remove the nolock hint, the second query will never finish.

Surprising result!  A half-updated XML column might break a (nolock) report, because the XML would be malformed.


ALTER COLUMN NOT NULL, not enough space in the log file I am trying to run the following DDL
ALTER TABLE [dbo].[table_name] ALTER COLUMN [Value] [float] NOT NULL

This generates a large amount of log file data, and there is not enough space for it on the server.
Is it possible to somehow reduce the information being logged?
I'm using Microsoft SQL Server 2016 (SP1-CU15) (KB4495257)

you might find it faster to create a new table, truncate the old, and then re-insert:
select t.*
into temp_my_table
from [dbo].[table_name] t;

truncate table [dbo].[table_name];  -- back it up first!

alter table [dbo].[table_name] alter column [Value] [float] NOT NULL

insert into [dbo].[table_name]
select t.*
from temp_my_table t;

You may have to pay attention if you have insert triggers or identity columns.


pgAdmin4 v4: TypeError: string indices must be integers When I open pgAdmin4 v4 on Windows 10, the dashboard displays
An error occured whilst rendering the graph
on all charts. The link (1) told me that the logs on windows are at
Windows log location: C:\Users\YOUR_USERNAME\AppData\Roaming\pgAdmin\pgadmin4.log
The logs contained numerous times the same error

2019-05-10 10:01:32,139: ERROR  flask.app:  string indices must be integers
Traceback (most recent call last):
  File "C:/Program Files (x86)/pgAdmin 4/v4/venv/Lib/site-packages\flask\app.py", line 1813, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:/Program Files (x86)/pgAdmin 4/v4/venv/Lib/site-packages\flask\app.py", line 1799, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "C:/Program Files (x86)/pgAdmin 4/v4/venv/Lib/site-packages\flask_login\utils.py", line 261, in decorated_view
    return func(*args, **kwargs)
  File "C:\Program Files (x86)\pgAdmin 4\v4\web\pgadmin\dashboard\__init__.py", line 251, in wrap
    return f(*args, **kwargs)
  File "C:\Program Files (x86)\pgAdmin 4\v4\web\pgadmin\dashboard\__init__.py", line 371, in dashboard_stats
    for chart_row in res['rows']:
TypeError: string indices must be integers

I can also not open any databases(pressing the databases button doesn't unfold the list of databases) via pgAdmin4 v4.

Since I was working with the permissions of my users on certain databases, this has probably to do with it. The commands I recall running are

REVOKE ALL ON DATABASE Database_Name FROM Database_User;
REVOKE ALL ON DATABASE Database_Name FROM PUBLIC;

To verify that I did not completly corrupt my databases and the error is just weird I logged in with the admin user via psql. Running \du and \l did not show anything was off.

What Should I do to fix this?

(1) https://stackoverflow.com/questions/41766323/pgadmin4-dashboard-does-not-display-sessions

-- edit --
DataGrip doesn't seem to have the problem either.

I have the same problem in Ubuntu 18.04 with pgAdmin 4.11 and postgres 11.

What works for me is to go to the process list and kill the pgadmin4 process and reload it. You may do it from Task Manager on windows. Good luck.

BTW Datagrip always work fine for me too.


Do Sleeping Threads Hold Memory We are having memory pressure issues (RESOURCE_SEMAPHORE waits) on one of our servers but there are very few active connections going on at one time. Logically, it wouldn't seem like sleeping connections would hold on to memory but I'm wondering if they actually do?  IderaDM shows several sleeping connections with high Memory Usage.

This is a SQL 2008 SP3 server with 27 Gb of memory.  There's typically only two to 10 active calls going on at any one moment and a good portion of them are having resource_semaphore waits.  This server holds our third party DBs so we can't change most of the calls/procs.

So, is it possible that sleeping connections are holding onto memory or should I be looking elsewhere?

If you're trying to track down memory usage and what queries are the biggest offenders, run through the following steps:

Identify what DB on the instance is consuming the most memory

-- DB using most cache
DECLARE @total_buffer INT
SELECT @total_buffer = cntr_value
   FROM sys.dm_os_performance_counters
   WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
   AND counter_name IN ('Total Pages', 'Database Pages');

WITH src AS
(
   SELECT
       database_id, db_buffer_pages = COUNT_BIG(*)
       FROM sys.dm_os_buffer_descriptors
       --WHERE database_id BETWEEN 5 AND 32766
       GROUP BY database_id
)
SELECT
   [db_name] = CASE [database_id] WHEN 32767
       THEN 'Resource DB'
       ELSE DB_NAME([database_id]) END,
   db_buffer_pages,
   db_buffer_MB = db_buffer_pages / 128,
   db_buffer_percent = CONVERT(DECIMAL(6,3),
       db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

Dig into what objects within said db are consuming the most memory

--objects in DB using most space
USE DatabaseName
GO
WITH src AS
(
    SELECT
        [Object] = o.name,
        [Type] = o.type_desc,
        [Index] = COALESCE(i.name, ''),
        [Index_Type] = i.type_desc,
        p.[object_id],
        p.index_id,
        au.allocation_unit_id
    FROM
        sys.partitions AS p
    INNER JOIN
        sys.allocation_units AS au
        ON p.hobt_id = au.container_id
    INNER JOIN
        sys.objects AS o
        ON p.[object_id] = o.[object_id]
    INNER JOIN
        sys.indexes AS i
        ON o.[object_id] = i.[object_id]
        AND p.index_id = i.index_id
    WHERE
        au.[type] IN (1,2,3)
        AND o.is_ms_shipped = 0
)
SELECT
    src.[Object],
    src.[Type],
    src.[Index],
    src.Index_Type,
    buffer_pages = COUNT_BIG(b.page_id),
    buffer_mb = COUNT_BIG(b.page_id) / 128
FROM
    src
INNER JOIN
    sys.dm_os_buffer_descriptors AS b
    ON src.allocation_unit_id = b.allocation_unit_id
WHERE
    b.database_id = DB_ID()
GROUP BY
    src.[Object],
    src.[Type],
    src.[Index],
    src.Index_Type
ORDER BY
    buffer_pages DESC;

Review what queries are hitting said objects in memory

USE DatabaseName
GO

SELECT DISTINCT TOP 100
        s.total_logical_reads / s.execution_count
    , SUBSTRING(t.TEXT, (s.statement_start_offset / 2) + 1, (
            (
                CASE s.statement_end_offset
                    WHEN - 1
                        THEN DATALENGTH(t.TEXT)
                    ELSE s.statement_end_offset
                    END - s.statement_start_offset
                ) / 2
            ) + 1) AS statement_text
    , s.execution_count AS ExecutionCount
    , s.max_elapsed_time AS MaxElapsedTime
    , ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime
    , s.creation_time AS LogCreatedOn
    , *
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
WHERE TEXT LIKE '%ObjectName_From_QueryAbove%'
    AND TEXT NOT LIKE '%sys.dm_exec_query_stats s%'
ORDER BY s.total_logical_reads / s.execution_count DESC

Hopefully this gives you some troublesome queries.  You may find that you just don't have the proper amount of memory in the instance for the workload it's trying to accommodate.  Alternatively you may find a query written by a user missing something as simply infuriating as a predicate (e.g. the WHERE clause).

If you want to dig into the execution plan, you can take the plan_handle and send it into this query:

SELECT query_plan
FROM sys.dm_exec_query_plan (PLAN_HANDLE_FROM_Query_3)

and review the execution plan.  If you are not able to tune said query, this probably isn't as useful to you.

Once you figure out what's using the memory on the instance, you can come up with a better idea on how to get the system back to a healthy state.


Table Design - User Management I have designed a database design for a simple User Management System, this system consist of the following things,

User - A user may be a Vendor / Employee / Buyer.
User - A user may have more than one Address - /delivery/ Billing / Profile / Company Address.
User - A user can have more than one role- Sales Executive/Manager/CEO.
User - A user can access all his roles / needs by using a single login credential (Username / Email address).

keeping in this, I have designed this system. This is my first application, I need to know whether the designed application will meet all requirements and is this good to go in this design?

Any suggestions and ideas regarding this and some tips for best practise. Thanks in advance.

The "Data Model Resource Book", Volume 1, has complete and validated structures for all that. I suggest you grab a coffee - it also discusses these details in great length.

You have a problem - User and UserRole - a User may be in multiple roles and roles should be time limited so they can change over time without invalidating your audit trail. What you do if a Vendor turns into a Buyer? You fall into the typical trap of assuming a User (entity / table) contains a role - it should not. A user is an ID - much like you passport does not contain your job.

Roles are attached to users (UserRole) table, but a user does not contain a role.

Same with AddressType - what if I want to use the same address for both invoicing and shipping? Types should be tags added. Or in the Role you have a pointer to a specific address (ShippingAddressId etc.).

You code the address very detailed - that is nice, but also totally bad if you ever run into addresses that are non-standard. There is a good sense to have fields like postal code, city, country - but even state is something irrelevant (never give it on an address) for shipping but what is worse are all the address fields. Street? What about countries not using this? Not having proper house  numbers? Where your shipping address is 15 lines long? Those DO exist. Read http://online.wsj.com/news/articles/SB10001424052702304870304577489094121477570 for a reference. There is a good point to have a free text field for stuff like that - as well as additional routing details you are not aware of at the moment.

Phone numbers- same thing. Please. ContactItem is a good table - can be email, phone, etc.- Allows people to have multiple phone numbers.

PinCode? You want to store a readable pin code in there? Programming for beginners: NEVER EVER STORE A PASSWORD OR PIN CODE IN A DATABASE. Store a Hash, salted, by standard security approach. Something a hacker can not easily use to pretend to be the user. Storing a pin code was legally gross neglect 20 years ago. It still is today.

So, looks like a non-enterprise model made by someone now aware of the complexities of the real world. I can really only recommend a copy of the Data Model Resource Book for a good description of all those cases.


Trying to use MySQL Workbench with TCP/IP over SSH - failed to connect I can't connect using TCP/IP over SSH connection in MySQL Workbench from a PC. What's going on?

I created a MySQL 5.1 database on an Ubuntu server mysql.myhost.com. I can access it locally. MySQL Workbench (PC) offers to make a connection via TCP over ssh. It runs on port 3306 on the remote server where command-line mysql works fine.

I used the following session details:

Connection Method: TCP/IP over SSH.
SSH Hostname: mysql.myhost.com:3306
SSH username: my linux login
SSH public key file: my local public key file
MySQL hostname: 127.0.0.1 MySQL
Server Port: 3306
Username: root

I get an error message when I try to connect:
"Failed to connect to MySQL at 127.0.0.1:3306 through SSH tunnel at mysql.myhost.com with user root"

"Can't connect to MySQL server on '127.0.0.1' (10061)"

As another test - I set up a SSH tunnel with port 3306 using Putty and I can connect OK using MySQL Workbench through that tunnel which forwards connections to my local 3306 to the remote server as described above. But I can't get "TCP/IP over SSH" working in Workbench.

Secondary question: when Workbench asks for "Path to SSH public key file" doesn't it really need my private key file?

I stumbled upon this question when I myself had encountered this error. I could finally figure out the configuration.

I didn't touch anything in /etc/mysql/my.cnf which already has bind_address = 127.0.0.1. So only localhost can connect.
I use OpenSSH server. So in its config file /etc/ssh/sshd_config I changed from no to yes the param responsible for TCP forwarding, thus AllowTcpForwarding yes.
Finally I have the following entered in MySQL WorkBench.

SSH Hostname: 192.168.0.8:22 (my SSH server listens to port 22)
SSH Username: sshuser
SSH Key File: *C:\Users\windowsuser\.ssh\id_rsa* (should be private key, even though it says public)
MySQL Hostname: 127.0.0.1 (this should not be changed, since MySQL server by default is bound to localhost only which I didn't change)
MySQL Server Port: 3306 (also default)
Username: root

The only remaining thing for you is to configure correctly your SSH server to work with keys rather than passwords. Hope this will help someone.


Can I stop a SQL database backup after it has already started? I am looking to do a full database backup of a SQL database in SQL Server 2008.

My questions are:

Can I stop, pause, or cancel the backup after it has started if it is taking too long or slowing things down too much? Is a backup just a job, that I would just stop the job?
When doing a backup, are you able to view the progress, and see an estimated completion time of the backup? If so, how do you view its progress?

Straight to answer your questions :

  Can I stop, pause, or cancel the backup after it has started if it is taking too long or slowing things down too much?

You cannot pause a backup - either running using Tsql or using sql agent job. If your db is in mirroring, then when you failover the backup gets killed.

Since you are on sql server 2008, recently I ran into a weird situation that caused an outage of one of our critical app is that if you are not on the latest SP (2008 SP4) - we were running sp1 on on node that had a bug and was fixed in SP1+CU4 - It was resolved in CU4 for SP1:https://support.microsoft.com/en-US/help/973602.  Search, “970133” for: FIX: When you create a compressed backup for a database in SQL Server 2008, you cannot stop the backup operation if the backup operation stops responding.

You can stop it or kill it using the kill spid command.

you have to be cautious as if you kill a job that is already in progress, it has to ROLLBACK which will take some or more time.

  Is a backup just a job, that I would just stop the job?

Depends on how you are running the backup. If you run it using SQL Agent job, then its a job which you can STOP or if you are running it directly from SSMS or SQLCMD, then you  can CANCEL or KILL the command.

  When doing a backup, are you able to view the progress, and see an estimated completion time of the backup? If so, how do you view its progress?

You can use DMVs to monitor the backup (or restore) progress (if you are not using WITH STATS = number):

----- find out the ETA time for restore and backup progress

SELECT command,
            s.text,
            start_time,
            percent_complete,
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

Some food for thoughts :

Start checking what is causing "slow performance" - analyze Wait stats, locking and blocking problems, run sp_whoisactive, etc
If you really found out that doing backups is causing the slowness then schedule backups when there is minimal activity and follow Aaron's advise and make sure to read some common backup myths - from Paul Randal.
There are Options to Improve SQL Server Backup Performance as well.


Troubleshooting oracle database errors from cloud application I am trying to troubleshoot an issue that presents as different database errors such as, ORA-01000: maximum open cursors exceeded or Unable to create DB Connection.  I have reviewed the PLSQL to determine if cursors were left open and all are closed even if there is an error.

The java application and background are as follows:
The original application was a 3-tiered system:

  GUI app. -> server app -> 11g Oracle database

The enhancement was to add an API service in a Pivotal Cloud Foundry (PCF) environment.
So this architecture was like this:

  Close Function: GUI app -> Server App -> API service -> Database.

  All other Functions: GUI app -> Server App -> Database.

This was put into production and run for a week without any database issues described above.
Then another enhancement was added in which the API Service communicates with several other services all in PCF in which 2 communicate with the same oracle database.  Now during heavy volume we are getting these database errors.

It seems to me that the Oracle database cannot keep up with the requests from these additional services.  But how can I demonstrate that.  We have AppD configured for the servers but not the database.  Are there queries that I can run in the prod env. that shows that these PCF applications are causing the issue?  Or should I look in another area?

Thanks,

UPDATE
I created a query that shows the total open cursor by sessionID
This is the query:

select b.sid, b.username, b.osuser, b.status, sum(a.value) total_opened_current_cursors
from sys.v_$statname c,
     sys.v_$sesstat a,
     sys.v_$session b
 where a.statistic#=c.statistic# and
      b.sid=a.sid and
      c.name in ('opened cursors current') and
      (b.username is not null or b.username <> '' )
group by b.sid, b.username, b.osuser
order by total_opened_current_cursors desc

Now, I need to link the sessionID with the application that has this session.
The osuser for the top ones is NULL.
Also, most of the sessions' status are INACTIVE
How to identify the application to the session?
Secondly, is the session is inactive, which I thought meant that no query is happending so why are there open cursors?

UPDATE
SORRY I ADDED THIS UPDATE TO THE ANSWER...SHOULD HAVE ADDED IT HERE
So, I wrote a query that returns the top 10 sessions with the highest open cursors

select *
FROM
(
select b.sid, b.username, b.osuser, b.status, sum(a.value) total_opened_current_cursors
from sys.v_$statname c,
     sys.v_$sesstat a,
     sys.v_$session b
 where a.statistic#=c.statistic# and
      b.sid=a.sid and
      c.name in ('opened cursors current') and
      (b.username is not null or b.username <> '' )
group by b.sid, b.username, b.osuser,  b.status
order by total_opened_current_cursors desc
)
WHERE ROWNUM <= 10;

I found the SQL_TEXT that accounts for most of the open cursors...by far! (87%)
So, how do I find the query that calls this SQL?
There are at least 5 services that hit the database. Some of the services call PLSQL stored procedures some call raw SQL text.  The query that accounts for the open cursors is listed as a SELECT statement.  Does that mean it is NOT a stored procedure?  Or can this SELECT be called within the stored procedures.
How do I find the connection that uses this session?

ORA-01000: maximum open cursors exceeded is almost always an application error.  I'll put a large wager down that some part of the Java middle tier is failing to close a ResultSet in every case.  That causes it to leak cursors and will eventually cause this error.  You can increase the max_open_cursors setting in Oracle so that the cursor leak hits the limit more slowly.  You may want to combine that with more aggressive restarts of the middle tier and/or recycling of the connection pool as a temporary measure.  But the right answer would be to figure out where the application is leaking cursors and plug the leak.

Do you have any additional logging around the unable to create a connection error?  It is possible that you're hitting the maximum number of connections specified by your sessions setting (and the processes setting on which it depends).  If you really need to support more connections, you can always bump that up or move some of the connections over to shared server connections if they're using dedicated server connections today.  But I'd be concerned given the cursor error that the middle tier is also leaking connections.


64 bit hexadecimal AS Date in SQL I am loading a Qlikview CalData PGO XML file into a SQL table.
Dates in this XML are stored as a 64 bit HEX string and I need to convert it to a date.
How do I do that?
Example
40e58f2c4153d0f9 should be converted to 18/11/2020 09:11:29
I have seen some sample code within qlikview, which is
daystart(date($(HEX64CONVERT(ToBeDeleted))))

This value is encrypted by QlikView to prevent you from tampering with the value.

Some fields in the generated XML file i.e. "LastUsed", are encrypted
for security reasons, thus cannot be converted into readable dates
using date() conversion. The generated XML files should be used for
troubleshooting only.

https://community.qlik.com/t5/Support-Knowledge-Base/Reading-PGO-contents-and-CAL-assignments-PGO-to-XML/ta-p/1710604?_ga=2.160240829.1496857553.1606312645-1955769053.1606312645
Presumably this is part of how they track and enforce their sofware licensing agreements.


Delete records from single Azure DB table that has no linking - reference to database and/or server name .. not supported Environment

Azure SQL database (migrated from SQL Server)
Old database originally (15 years+)
Dealing with a single table (trying to delete records)
Using an admin user
Using SSMS 2014
There are some triggers on the table (disabling them yields no change in error)
Name of database is databasename in our example here
There is an azure blob storage where files are stored and are "referenced" by an app via the records in said tblImages table -- but I cannot see any hard link between the two (is that even possible?)

I can select, insert and update records in this table, but cannot delete.

Failing Script

delete from
[databasename].[dbo].[tblImages]
where companyid = 123

Error:

  Msg 40515, Level 15, State 1, Line 91 Reference to database and/or
  server name in 'Azure.dbo.tblImages' is not supported in this version
  of SQL Server.

But who said anything about "Azure.dbo.tblImages' ?!?

Things I've tried

Have tried fully qualifying the table name, and not - no change.
Have tried deleting records using the "edit top 200" feature, same error
tried disabling all triggers on the table, no change
tried script "create to new window" but nothing about linked databases/servers or other table constraints

Here's a simple test that succeeds with entirely same environment:

CREATE TABLE _tmpPersons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

INSERT INTO _tmpPersons ( PersonID , LastName , FirstName , Address , City )
VALUES (123, 'Tore', 'Tess', '123 Main St.', 'Nowheresville');

select * from _tmpPersons;

delete from _tmpPersons where PersonId = 123;

select * from _tmpPersons;

I have already tried fully qualified [database].[schema].[table] and different combinations thereof.

It is definitely a table, and there ARE triggers, but disabling them changes nothing.

Zero results found from:

SELECT OBJECT_NAME(referencing_id), referenced_database_name
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL;

Change your query from

delete from
[databasename].[dbo].[tblImages]
where companyid = 123

to

delete from
[dbo].[tblImages]
where companyid = 123


MySQL "bad handshake" after entering password I'm having problem to connect to the remote DB from the terminal as follow

mysql -u db_user -p -h 10.100.1.100
Enter password:

afer entering password I'm getting that error:

ERROR 1043 (08S01): Bad handshake

and I can connect to the DB from a PC using MySQL Workbench.

Running MySQL  Ver 14.14 Distrib 5.1.73 for CentOS6

There is old Bug Report about MySQL Workbench connecting to older databases.

Bug #91828 : Bad handshake since 8.0.12

Others have come across this

Can't connect to MySQL server on MySQLCC ERROR 1043 Bad Handshake
SOLVED: Bad Handshake – MySQL Workbench Failed To Connect To SQL
Devart Forums : Bad Handshake

When you google the subject of "mysql bad handshake", you will see two basic issues:

Older Versions of MySQL
Older Versions of ODBC/JDBC Drivers

The second post I referred to actually recommends "dummying down" and using an older product (MySQL Administrator for you Old School DBAs like myself).
Main thing to do is to make sure your drivers match the version of Workbench you are using. BTW Don't feel bad about using MySQL 5.1. There are still a lot of die-hards out there that are using (I hope you sitting down), MySQL 3.23.58.
WHAT TO DO
Use Older Versions of MySQL Workbench, Navicat, SquirrelSQL.
Hey, that worked in the second post (Click Here for Those Tools)
Please remember that MySQL 5.1 made allowances for MySQL 4.1 password authentication. Newer ODBC/JDBC drivers may not understand MySQL 5.1's way of authenticating and other network-related things, colloquially called "BAD HANDSHAKE".


Is there Such Thing As The Opposite of Unique Constraint I want to be constrain my database with what is essentially the opposite of a UNIQUE constraint. I want to only be able to add a new row if some combination of fields has been added before.

Note: I am working within postgres.

Take the following datasets for example. I want a "NON UNIQUE" constraint on the following:

1, a, 1

1, a, 1

1, b, 1

I should not have been able to add the row (1, b, 1) since it has not appeared before. There are also other fields I don't want to constrain so it's not like all the entries are identical.

This may be a separate question, but I would also like to add the constraint that all rows with the same value in one column (i.e. a in the above example) have the same value in another.

So I want this to be impossible

1, a, 1

1, a, 1

0, a, 1

But this is ok

1, a, 1

1, a, 1

0, b, 1

I have looked around pretty heavily and the opposite of this is possible
with unique constraints, but I can't find anything that works for this.
Check constraints will only look at the row in question, and won't take into account the entire table's entries like a unique constraint would.

Is this even possible? Or is this now out of the realms of Postgres functionality, and must be done in-application.

Subqueries are not allowed in a CHECK constraint, or in the WHEN clause of a trigger, but you can use them inside a trigger function, something like this:

CREATE FUNCTION my_little_trigger_function()
RETURNS TRIGGER
AS $$
DECLARE
   row_count int;
BEGIN
   row_count := (SELECT COUNT(*)
                 FROM ...
                 WHERE ... = NEW.x ...);
   IF (row_count = 0) THEN
       RAISE EXCEPTION 'I''m sorry, Dave. I''m afraid I can''t do that.';
   END IF;
   RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_little_trigger
BEFORE INSERT ON my_little_table
FOR EACH ROW
EXECUTE PROCEDURE my_little_trigger_function();


Is this application creating stored procedures on the fly? We are running a transaction program that uses MS SQL Server. Lately as our number of users and locations have grown, speed/response seems to be an issue.

When looking at *sys.dm_exec_sql_text*, I see a lot of queries starting with "CREATE PROCEDURE", of which some are over 1000 lines of code. Is the application creating stored procedures on the fly or is it merely executing these stored procedures? I thought executing a stored procedure is started using "EXEC"?

If the application is constantly (re)creating the procedures, is it likely that this causes performance issues?

sys.dm_exec_sql_text presents the full text of the procedure including it's CREATE PROCEDURE command.

To get the actual query that was executed, you'll need to join to sys.dm_exec_query_stats, where the start/end offset values of the procedure's SQL are stored.

SELECT
    deqs.execution_count,
    deqs.last_execution_time,
    SUBSTRING(dest.text, (deqs.statement_start_offset/2)+1,
                        ((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
                              ELSE deqs.statement_end_offset
                            END - deqs.statement_start_offset)/2)+1) AS statementtext
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest


Create a View from Multiple Databases Database X - tableCust - Column-CustNumber
Database Y - tableCust - Column-Custnumber
Datbase Z - tableCust - Column-Custnumber

I'm trying to create a view in database H from those three databases like below.

Select Custnumber,Orginal_DB_Name() as DatabaseName
from X.dbo.tablecust
Union
Select Custnumber,Orginal_DB_Name() as DatabaseName
from Y.dbo.tablecust
;
;
;
etc

But the database name is showing me Master instead of their DB_Name.

It needs to show output like below:

Database_Name CustNumber
X               221
X               1223
Y                122
Y               233
"
"

Specify each database name explicitly as a string:

SELECT
  Custnumber,
  'X' AS DatabaseName
FROM
  X.dbo.tablecust

UNION

SELECT
  Custnumber,
  'Y' AS DatabaseName
FROM
  Y.dbo.tablecust

UNION

...

There is no other way. After all, you have to explicitly specify the database name in the table references too (X.dbo.tablecust, Y.dbo.tablecust etc.).


How can I most closely monitor this table in production to find why rows are disappearing? Scenario:
We write online banking software and for the time being (due to legacy design decisions) are using Quartz 2.2 right now as a queue system to perform a handful of operatoins long term. [Let's sidebar the discussion on if it's the right tool, it's worked well for many years until we updated to 2.2]
Part of that quartz.net has the following tables (related) for schema: https://gist.github.com/jcolebrand/8695603
So the process is we're inserting records in this table, to the tune of say 80k records. (I have three replications of quartz.net serving three different configurations, one has 80k records, one 50k, one 280k, so it varies). We insert them from some other table that is the table of record, so rebuilding this table isn't a terrible loss. The issue is described below.
I have a tool which can bulk schedule these tasks (most of which are one-time and will happen anywhere from two weeks to twenty years in the future, again, this is about the db and not the architecture choices) and I can debug step through, see the rows get inserted into the table with no concern. I can monitor the table and see that they get inserted. I then come back 30 minutes later (well, this part varies. It's not deterministic on when they disappear) and check and the records are now missing.
Out of 80k on the one instance, I'm missing approximately 2700 records. And when I run my tool, I see them all synced up, and after some matter of time, they have evaporated again.
Here's things I've tried:

checking the all transactions report in SSMS
checking the all blocking transactions report in SSMS
leaving my scheduling application open for long duration (in case, by some freak magic, there was an open, uncommitted transaction)
restarting the Quartz.net windows service application maintaining the database
inserting records with the quartz.net windows service disabled

Things I haven't tried:

restarting SQL Server
my queries don't use "with(nolock)" [I've heard that's bad juju for day-to-day]

Things I think I should do and don't know how:

run SQL Profiler against the instance and monitor for "delete" statements
magic to determine if there is an uncommitted transaction
sacrifice unicorns
telephone a friend
daily double

@@version:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
   Jun 28 2012 08:36:30
   Copyright (c) Microsoft Corporation
   Standard Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

EDIT 2014-01-30
It is imperative for internal reasons that I can demonstrate precisely why the records are disappearing, including but not limited to:

deletion by a user
uncommitted transaction
deletion by Quartz itself
some other unexplained phenomena
butterfly effect
trigger effect
unicorns

Where possible, I need the specific statement involved in the delete.
TL;DR
So, basically, the TL;DR is: records are inserted. For some duration of time up to at least 30 minutes record definitively show in queries against the table. After some duration, records no longer show in queries against the table.
What gives? What am I overlooking? How would you profile this IN PRODUCTION to see what's happening here?

If you don't expect any rows to be deleted from this table, then the simplest, least impact way to catch anyone running deletes would be a delete trigger. You can use an INSTEAD OF trigger if you want to log and prevent, or an after trigger if you just want to log. First, a logging table:

CREATE TABLE dbo.CatchTheDoNoGooders
(
  [JOB_NAME] [nvarchar](200),
  [JOB_GROUP] [nvarchar](200),
  [SCHED_NAME] [varchar](120),
  UserName SYSNAME,
  HostName SYSNAME,
  Program NVARCHAR(4000),
  IP VARCHAR(15),
  EventDate DATETIME
);

Now, an INSTEAD OF INSERT trigger, could log the DELETEs but not carry through:

CREATE TRIGGER dbo.InsteadDelete_QRTZ_JOB_DETAILS
  ON dbo.QRTZ_JOB_DETAILS
  INSTEAD OF DELETE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @ip VARCHAR(15), @hn SYSNAME, @prog NVARCHAR(4000);

  SELECT TOP (1) @ip = client_net_address
    FROM sys.dm_exec_connections
    WHERE session_id = @@SPID;

  SELECT TOP (1) @hn = [host_name], @prog = LEFT([program_name],4000)
    FROM sys.dm_exec_sessions
    WHERE session_id = @@SPID;

  INSERT dbo.CatchTheDoNoGooders
  SELECT JOB_NAME, JOB_GROUP, SCHED_NAME,
    SUSER_SNAME(), @hn, @prog, @ip, CURRENT_TIMESTAMP
    FROM deleted;
END
GO

Change INSTEAD OF to FOR and now you have an AFTER trigger that will still allow the delete to happen, but will log them all anyway. This can be better if you do have some legal deletes going on but want to determine who is deleting the wrong things.

The beauty is that in both cases this only has any impact whatsoever if and when a delete is actually issued against the table.


"Cannot open database version 706. Upgrade the database to the latest version" - when trying to use availability database for read-only access I have Availability Group with below config:

replica-01: SQL Server 2012 (primary)
replica-02: SQL Server 2012 (secondary synchronous)
replica-03: SQL Server 2016 (secondary asynchronous)

replica-01 and 02 are in on-premise data center
replica-03 is on Azure VM
(03 is synchronizing, no problems here)

When I try to use replica-03 as reporting read-only server, running "use MyDB" statement shows the following

Cannot open database 'TestDB' version 706. Upgrade the database to the latest version.

I tried to run ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 130
But it shows error message:

ALTER DATABASE failed because a lock could not be placed on database

Tried to exclude MyDB from availability group, set compatibility level to 130, and include back to AG
But since database in NORECOVERY mode, it does not allow me to set compatibility level

If I restore database with recovery, I can set compatibility level to 130, but can't return database in norecovery mode, and can't add back to AG

What do you suggest on above ?

I need to keep primary SQL server replica as 2012 for now, and I need to make async replica (SQL server 2016) to be available for read-only access

What do you suggest on above ?
I need to keep primary SQL server replica as 2012 for now, and I need to make >async replica (SQL server 2016) to be available for read-only access

There is nothing you can do, this is a known item:

During a version upgrade, readable secondaries cannot be read after an
upgrade of the readable secondary and before either the primary
replica is failed over to an upgraded secondary or the primary replica
is upgraded.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/upgrading-always-on-availability-group-replica-instances


how to get collection based on number of duplicate foreign keys? I have a batch table, and an item table, the relationship is the batch has many items (item has a batch_id foreign key).
How would I make a query that will find the batches which have more than 100 items?

SELECT b.* FROM
    batch b LEFT JOIN
    item i ON i.batch_id = b.batch_id
GROUP BY b.batch_id HAVING COUNT (i.batch_id) > 100

To get the count by batch_id
SELECT b.batch_id, COUNT(i.batch_id)
FROM
    batch b LEFT JOIN
    item i ON i.batch_id = b.batch_id
GROUP BY b.batch_id HAVING COUNT (i.batch_id) > 100


MS SQL - Convert string mm/dd to date I am having a bit of a hardtime in converting string to date in MS SQL. Hope you can guide me.

Here is my scenario:

I have a column in my table called ExpDt. It is the expiration date of the product. the format is mm/dd.

Now I want to know what products would expire 5 days before. So I am going to do a datediff. But the problem is the ExpDt column is not a valid date. How can I convert it to a valid date?

So the output would be something like: 2018-01-20 which is a valid date. Not a string.

Here's what I have tried.

-- Outputs 2018-01-31
-- select  @expDate = cast(datepart(year, getdate()) as nvarchar(5))
-- + '-' + substring(ExpDt, 1, 2)
-- + '-' + substring(ExpDt, 4, 5)

-- from BigEMerchandiser.dbo.tbl_Abis_D

It returns a string but I can't convert it to date using the convert function. I will attach the screenshot of the table so you can see the data of the ExpDt column. Hope I explained myself clearly. Any help would be much appreciated. Thankyou.

The best thing that you can do is fix your table so that you store a date instead of a string. With that said, the code that you posted in the question is on the right track. The problem is that you leave it as a string and don't explicitly cast it to a DATETIME. By the rules of data type precedence the data that you filter against also gets converted to a string which isn't what you want.

If you want to keep the SUBSTRING approach then you can try the following:

CREATE TABLE #T (NOT_A_DATE NVARCHAR(5));

INSERT INTO #T VALUES (N'01/30'), (N'01/31');

SELECT
    DATEFROMPARTS(
      datepart(year, getdate())
    , substring(NOT_A_DATE, 1, 2)
    , substring(NOT_A_DATE, 4, 5)
)
FROM #t;

The key is the use of the DATEFROMPARTS function to convert the string to a date that's safe in all locales.


Why would an UPDATE SET REPLACE() statement match rows, but change none and give no warnings? I'm seeking a certain string in a field and want to replace it with a new string. Specifically, I want all references to one URL to be changed to another URL. I've crafted this SQL statement and am running it at a mysql> prompt on CentOS 5.5 using MySQL Community Server 5.1.54.
update [table] set [field] = REPLACE([field],'%domain.com%','%domain.org%');

The response is:
Query OK, 0 rows affected (0.02 sec)
Rows matched: 618  Changed: 0  Warnings: 0

How can I track down why no changes are being made?
EDIT 1:
Thanks to Aaron Bertrand, I discovered that REPLACE() cannot handle wildcards and I was using it completely wrong (think: missing WHERE clause). Here's is my reformed statement:
UPDATE [table]
SET [column] =
REPLACE (
    [column],
    'companydomain.com',
    'companydomain.org' )
WHERE
    [column]
LIKE
    '%companydomain.com%';

To which I receive the old, familiar:
Query OK, 0 rows affected (0.02 sec)
Rows matched: 167  Changed: 0  Warnings: 0

What could I be doing wrong?
EDIT 2:
I'll tell you what I was doing wrong!! I was not questioning assumptions. My assumption was that the string that I was replacing was in all lower case. The WHERE clause was returning all things that looked LIKE %companydomain.com%. That includes all capitalization permutations such as CompanyDomain.com, CoMpAnYdOmAiN.com and etc.
It passed that on to REPLACE() which then was looking strictly for companydomain.com to then replace it with companydomain.org.
REPLACE (
    [column],
    'companydomain.com',
    'companydomain.org' )

So of course my records were being returned, but nothing was being replaced. Once I changed REPLACE() to take the capitalization into account, all records were updated and it appears that all is well. The correct REPLACE() syntax for my scenario was thus:
REPLACE (
    [column],
    'CompanyDomain.com',
    'companydomain.org' )

REPLACE does not play with wildcards that way. I think you meant:

UPDATE [table]
  SET [column] = REPLACE([column],'TLD.com','TLD.org')
  WHERE [column] LIKE '%TLD.com%';

You have no WHERE clause, so it tried to update 618 rows, but it did not find any instances of %TLD.com% in that column. To see which rows should be affected, run a SELECT instead:

SELECT [column], REPLACE([column], 'TLD.com', 'TLD.org') AS new_value
  FROM [table]
  WHERE [column] LIKE '%TLD.com%';


Concatenation Physical Operation: Does it guarantee order of execution? In standard SQL, the result of a union all is not guaranteed to be in any order.  So, something like:

select 'A' as c union all select 'B'

Could return two rows in any order (although, in practice on any database I know of, 'A' will come before 'B').

In SQL Server, this turns into an execution plan using a "concatenation" physical operation.

I could easily imagine that the concatenation operation would scan its inputs, returning whatever input has records available.  However, I found the following statement on the web (here):

  The Query Processor will execute this plan in the order that the
  operators appear in the plan, the first is the top one and the last is
  the end one.

Question:  Is this true in practice?  Is this guaranteed to be true?

I haven't found any reference in Microsoft documentation that the inputs are scanned in order, from the first to the last.  On the other hand, whenever I try running it, the results suggest that the inputs are, indeed, processed in order.

Is there a way to have the engine process more than one input at a time?  My tests (using much more complicated expressions than constants) are on a parallel-enabled 8-core machine, and most queries do take advantage of the parallelism.

No, there is no documentation from Microsoft guaranteeing the behavior, therefore it is not guaranteed.

Additionally, assuming that the Simple Talk article is correct, and that the Concatenation physical operator always processes inputs in the order shown in the plan (very likely to be true), then without a guarantee that SQL Server will always generate plans that keep the same the order between the query text and the query plan, you're only slightly better off.

We can investigate this further though.  If the query optimizer was able to reorder the Concatenation operator input, there should exist rows in the undocumented DMV, sys.dm_exec_query_transformation_stats corresponding to that optimization.

SELECT * FROM sys.dm_exec_query_transformation_stats
    WHERE name LIKE '%CON%' OR name LIKE '%UNIA%'

On SQL Server 2012 Enterprise Edition, this produces 24 rows. Ignoring the false matches for transformations related to constants, there is one transformation related to the Concatenation Physical Operator UNIAtoCON (Union All to Concatenation).  So, at the physical operator level, it appears that once a concatenation operator is selected, it will be processed in the order of the logical Union All operator it was derived from.

In fact that is not quite true. Post-optimization rewrites exist that can reorder the inputs to a physical Concatenation operator after cost-based optimization has completed. One example occurs when the Concatenation is subject to a row goal (so it may be important to read from the cheaper input first). See UNION ALL Optimization by Paul White for more details.

That late physical rewrite was functional up to and including SQL Server 2008 R2, but a regression meant it no longer applied to SQL Server 2012 and later. A fix has been issued that reinstates this rewrite for SQL Server 2014 and later (not 2012) with query optimizer hotfixes enabled (e.g. trace flag 4199).

But about the Logical Union All operator (UNIA)?  There is a UNIAReorderInputs transformation, which can reorder the inputs.  There are also two physical operators that can be used to implement a logical Union All, UNIAtoCON and UNIAtoMERGE (Union All to Merge Union).

Therefore it appears that the query optimizer can reorder the inputs for a UNION ALL; however, it doesn't appear to be a common transformation (zero uses of UNIAReorderInputs on the SQL Servers I have readily accessible. We don't know the circumstances that would make the optimizer use UNIAReorderInputs; though it is certainly used when a plan guide or use plan hint is used to force a plan generated using the row goal physical reordered inputs mentioned above.

  Is there a way to have the engine process more than one input at a time?

The Concatenation physical operator can exist within a parallel section of a plan.  With some difficulty, I was able to produce a plan with parallel concatenations using the following query:

SELECT userid, regdate  FROM (  --Users table is around 3mil rows
    SELECT  userid, RegDate FROM users WHERE userid > 1000000
    UNION
    SELECT  userid, RegDate FROM users WHERE userid < 1000000
    UNION all
    SELECT userid, RegDate FROM users WHERE userid < 2000000
    ) d ORDER BY RegDate OPTION (RECOMPILE)

So, in the strictest sense, the physical Concatenation operator does seem to always process inputs in a consistent fashion (top one first, bottom second); however, the optimizer could switch the order of the inputs before choosing the physical operator, or use a Merge union instead of a Concatenation.


Create Sqlite3 database I am simply trying to create a database.

In my account's home directory, I type in

  sqlite3 test.db

Then once I am in sqlite, I type

  .quit

That should create the database file in the current directory.
But there is no such file!
I tried it in other directories (chmodded to 777) to no avail.

What am I doing wrong?
OS is Ubuntu 14.04 LTS. Sqlite version 3.8.2.

It's because you haven't actually done anything.

Doing nothing:

phil@ironforge:~$ sqlite3 test.db
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .quit

File not created:

phil@ironforge:~$ ls -al test.db
ls: cannot access test.db: No such file or directory

Do something:

phil@ironforge:~$ sqlite3 test.db
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a (b number);
sqlite> .quit

File has been created:

phil@ironforge:~$ ls -al test.db
-rw-r--r-- 1 phil phil 2048 Oct 21 09:31 test.db
phil@ironforge:~$


Enforcing "one-to-one-or-more" vs. "one-to-zero-or-more" Suppose that I have a mini-world consisting of three types of entities, A, B, and C, subject to the following constraints:

each A is associated with 0 or more B's, and each B must be associated with exactly one A;
each A is associated with 1 or more C, and each C must be associated with exactly one A.

It is easy to come up with a schema that will automatically enforce constraint 1, namely, include in the definition for the B table a foreign key referring to the A table.

The same idea does not quite work for constraint 2, because it does not ensure that at least 1 C is associated with each A.

Is there a standard/canonical way to enforce constraint 2?  Or does it depend on the particular DB vendor?  If the latter is the case, what would it be for Oracle?

EDIT: Sorry, my original wording of the constraints was incomplete.  I fixed this by specifying how many A's must be associated with each B and each C, respectively.

It seems to me that the second half of rule #2 is readily enforced with a foreign-key constraint, as you describe for rule #1.

But the first half of rule #2 seems hard to enforce, in part due to a chicken-vs-egg conflict:  Are you going to make 'em add a C before there's an A, so that when you add the A, it will have its (one) C?

But you can't:  that new C needs a valid pointer to an A, which doesn't exist yet, and so can't be added.


Finding differences between a master and slave when pt-table-sync fails I have a table that's showing some differences on a master and slave according to pt-table-checksum (PTC).  I've found some verifying differences beyond that.

Certain tables in the mix have composite primary keys which, while work well enough for PTC do not so much with pt-table-sync (PTS) to try and find the differences.  PTS does not seem to honor the newer options of PTC to restrict the depth of composite key searches.  End result is the a 5.5Mrow table is left spinning for hours w/ PTS.  I'm somewhat convinced there's an infinite loop bug in PTS as the output has just started spitting out the a correction for the same exact record (and nothing else at this point) over and over.

So, really this post is more about alternatives than trying to get this tool to run right.

The most straight forward alternative that comes to mind is flush table with read lock on the master; select * into outfile on master and slave; unlock and diff.  However this table is fairly active and I cannot afford it being locked out on the master that long.

I was hoping to do something like start a transaction in repeatable read isolation level and select out from the master there.  However I can't find a way to have the slave be stopped specifically at the point in the transaction history as on the master.  Things like show master status continue to update after starting a transaction so I can't simply start slave until then.

There's no guarantees about atomicity to "quickly" do "show master status; begin;"

The only other solution of a non blocking method is to write a custom script that might be able to more efficiently do a PTS type thing but with application specific domain knowledge about how the columns of the table in question are generated.

While it might come to that I was hoping to find a more generalized solution for the future when prebuild wheels like PTS turn out not as well rounded as I'd like.

Here's a method that does not interrupt service on the master, though it does lock the slave temporarily.

Stop the slave.

mysql> STOP SLAVE;

Dump the offending table on the master with mysqldump in a repeatable-read transaction. Include the binary log coordinates in the output (--master-data). Output one INSERT per row (--skip-extended-insert).

$ mysqldump --single-transaction --master-data=2 --skip-extended-insert
  mydatabase myoffendingtable > master-dump.sql

Note the binlog coordinates in the dump file, and start slave until the coordinate shown in the master's dump file.

mysql> START SLAVE UNTIL MASTER_LOG_FILE='xxxx' MASTER_LOG_POS=yyyy;

Wait until the slave thread catches up to that position and stops itself. This shouldn't take long.

mysql> SELECT MASTER_POS_WAIT('xxxx', yyyy);

Dump the table on the slave, also with one row per INSERT.

$ mysqldump --skip-extended-insert mydatabase myoffendingtable > slave-dump.sql

Resume replication.

mysql> START SLAVE;

Now you can diff the dump files, and be assured they represent exactly the same logical point in the stream of changes. This allows you to find data discrepancies.


How to generate a reference key between rows I would like to group rows under a reference number.

My current approach is this:

CREATE TABLE `txn` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `reference` BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

SET @ref := (SELECT FLOOR(RAND() * 9999999999) AS ref FROM txn WHERE "ref" NOT IN (SELECT reference FROM txn) LIMIT 1);

-- They belong together
INSERT INTO txn (reference) VALUES (@ref), (@ref);

This seems to work fine. The only concern is that I have to dump CSV files with around 50 to 100 thousand entries and I do worry about the performance. On every new reference it needs to SELECT to check if the reference already exists.

I would really appreciate other approches or hints how to improve the query.

EDIT: If this can be solved with a sequence, this would be also quite nice.

As I had some time to think about it, I came up with this solution. I would appriciate a comment on this.

SET @ref = LAST_INSERT_ID() + 1;

INSERT INTO txn (reference) VALUES (@ref), (@ref);

I'm a bit worried about concurrent connections, which could result in identical reference numbers for unrelated transactions.

EDIT:

The upper solution will NOT work, since LAST_INSERT_ID() returns the last inserted ID of the current connection, which in this case will always be 0.

So another idea would be this:

-- This is the parent transaction
INSERT INTO txn (reference) VALUES (0);
-- Get ID of parent
SET @ref = LAST_INSERT_ID();
-- Insert related/child transactions
INSERT INTO txn (reference) VALUES (@ref), (@ref);
-- Update parent with its own ID
UPDATE txn SET reference = @ref WHERE id = @ref;


Application/MS SQL Server Express Installer We're developing a small(ish) application that collects and stores data. It's low-throughput, low-volume, but needs to be able to archive and retrieve data from (almost) arbitrarily earlier times, hence the decision to store the data in a MS SQL Server Express database. To the user, the database is invisible. The setup is:

Application - Visual Studio 10 (yes, 10) C#, .NET Framework 4.0
Database schema (tables, views, queries ...)
MS SQL Server Express 2008 SP2
MS Visual Studio-generated Installer (not the Installshield one), which ...

installs the application
installs .NET 4.0 (if needed)
installs MS SQL Server Express 2008 SP2
does not create the actual database

At this point, we can create the database using MS SQL Management Studio or Visual Studio.  But for production deployment, the creation of the database must be automated, either by the installer or by a start-up functionality of the application: Starting out from a fresh Windows 7 or Windows 10 installation (the PC will not be availlable to us beforehand), running the installer and then starting the application (e. g. by our service personnel) must be enough. Also, the application should not depend on which user is logged onto the PC.

Question: How do I make the database, err ... appear ... on the target PC?

Options I've considered so far:

Create the DB, make the DB files part of the installer, copy them into the right SQL Server directory, hope it recognizes it (will it?)
Same as above, but install the file into the CommonAppDataFolder, attach it to SQL Server.
Let the application test for the existence of the DB, and, if it does not yet exist, run a SQL script (generated by SSMS, modified) to create the database (currently implemented).

Which option would you recommend?

Create the DB, make the DB files part of the installer, copy them into the right SQL Server directory, hope it recognizes it (will it?)

It will not, you will have to attach the database to your server afterwards. You could add a sql script that will attach the files to your server though.

  Let the application test for the existence of the DB, and, if it does not yet exist, run a SQL script (generated by SSMS, modified) to create the database (currently implemented).

This could be a good option. Perhaps even better if you could run the .sql file in your installation, that way your application doesn't have any delay when the users first run it.

I also think it depends on the size of the database files. If it's big you could go for the first option, if not i'd use the second option.


Is there a way to start a script on the publisher when synchronization starts? I am using merge replication with pull subscriptions and Web Sync.

What is the easiest way to start a SQL script on the publication database when a subscriber starts the synchronization process?

There is this, sp_addscriptexec, but that specifies a script to run at the subscription database which isn't what I am after.

There isn't a built-in or easy way to do this with Web Synchronization.  You're best bet is to programmatically monitor Merge Agent sessions at the Publisher/Distributor using sp_replmonitorhelpmergesession and sp_replmonitorhelpmergesessiondetail.  You can script this out and poll on a schedule.


How do I close log files that are open in OracleServiceORCL after inputting data into Oracle? I inserted data into a table called cell_local_stage1. With this procedure:
CREATE TABLE cell_local_stage1
    (
      construct_id NUMBER(10),
      n_term VARCHAR2 (50),
      enz_name VARCHAR2 (50),
      c_term VARCHAR2 (50),
      cpp VARCHAR2 (50),
      mutations VARCHAR2 (50),
      g_batch VARCHAR2 (50),
      p_batch VARCHAR2 (50),
      c_batch VARCHAR2 (50),

      emptycol VARCHAR2(50),
      ind_var_number NUMBER,
      study_id VARCHAR2 (50),
      char_id NUMBER,
      concentration NUMBER (6, 2),
      valid NUMBER (1) DEFAULT 1,
      endpoint1 NUMBER (1),
      method1 VARCHAR2 (50),
      methodv1 VARCHAR2 (50))

    ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_cell_local_stage1_from_cell_local.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('CELL_LOCAL.CSV')
    ) REJECT LIMIT UNLIMITED;

I got a log file that has this information:

 LOG file opened at 08/17/20 22:00:47

Total Number of Files=1

Data File: CELL_LOCAL.CSV

Log File: CELL_LOCAL_STAGE1_4908_22980.log

And when I try to delete that file this messages says that the file is still open:

I was able to delete the CSV files, but not the log files. I didn't know I could even open these log files in Oracle.
Sorry for such a basic question.

Well, its Oracle on Windows, such issues are not uncommon there.
Similar, but not exactly your issue:
Bug 30191227 - HANDLE LEAK ON EXTERNAL TABLE LOG FILE IN WINDOWS PLATFORM
No fix exists yet for it, not even in 19c. Suggested workaround:
WORKAROUND:
-----------
Manually release the file handle using Windows process explorer.

Of course database restart works as well (Oracle on Windows runs as a single process).
Process Explorer


How to generate Entity Relationship (ER) Diagram of a on-premises database using Azure Data Studio (macOS)? I found a similar question for SQL Server Management Studio. But I use latest Azure Data Studio on macOS. We use on-premises SQL Server (not Azure cloud).

How can I use Azure Data Studio to generate an ERD from an existing database?

Azure Data Studio doesn't have database diagramming built in.

This means you're asking a shopping question, and here's how and where to ask a shopping question.


SUM n rows of each group with ORDER BY and LIMIT where the LIMIT is based off of another table I have a requirement to build a leaderboard of users of a fantasy football game.
The simplified database of the game is as follows:

users have squad_players in squads for each matches (related by transfer_period)
players have match_points for each matches
squad_players have position, priority. The priority is the order of substitutions if a squad_players does not appear in a matches
squads have formations which determine the maximum number of players to select from of each position ordered by priority

The database is MySQL 5.6 and the maximum number of users is 10K.

I am able to inner join (in order) squad_players, squads, matches, match_points to get the point of each squad_players who played (players who didn't play have no match_points).

I am struggling to SUM the points of x squad_players of each squads where x is in formations and determined by position of the squad_players.

I have tried to modify versions of Top n-rows with group by with correlated subqueries but without success.

What should my approach be here?

My Expected Output is something like this

|---------|-----------|------------|----------|
| User ID |  SQUAD ID | POSITION   |  POINTS  |
|---------|-----------|------------|----------|
|   12    |    34     | defender   |    12    |
|---------|-----------|------------|----------|
|   12    |    34     | forward    |    2     |
|---------|-----------|------------|----------|
|  117    |    31     | midfielder |    5     |
|---------|-----------|------------|----------|

Notes:

I must add the outputs may not reflect the problem statement.
If you look into the Sample Dataset Link, you will find a view
named normalized_squad_player_points that is the denormalized table I want to work with to create different stats.

Final Output for the leaderboard will be something like this

|---------------------|------------------|
|      User ID        |       Points     |
|---------------------|------------------|
|          12         |         34       |
|---------------------|------------------|
|          56         |         32       |
|---------------------|------------------|
|          117        |         31       |
|---------------------|------------------|

Additional information

SQL Fiddle link to schema.
Link to exported Schema and Sample Dataset.
I'm open to modifying table definitions if necessary.
Pastebin link to squads and match_points inserts.

The solution I ended up implementing was to break up the query into simpler ones based on the different permutations of the positions and formations. I also needed to add several tables to store intermediate results. I ran all the queries (around 42) in a transaction.

This worked out pretty well and completed in reasonable time without overloading the server resources.

I guess trying to do it in one giant query was a bad decision from the beginning. Breaking it up into smaller queries allowed the process to be maintainable and flexible.


Need to configure Availability Groups between three different SQL failover instance We have total 6 Nodes and 3 failover cluster (Each with two node).

Cluster1.TestAD.com(Node1,Node2) - SQL Failover Instance (SQL1) - 1
TB Shared storage
Cluster2.TestAD.com(Node3,Node4) - SQL Failover
Instance (SQL2)- 1 TB Shared storage
Cluster3.TestAD.com(Node5,Node6) - SQL Failover Instance (SQL3)- 1 TB Shared storage

Now we want to create availability group setup between above three SQL instance.

We plan to create new cluster (cluster4.TestAD.com ) with all six node and create Availability groups with all three instance.

SQL Edition : SQL Server 2012 R2 Enterprise

Is it possible? Any other recommendations ?

One of the requirements for AGs is that all instances are participating nodes in the same WSFC.  I believe, based on what youa re telling us, is that the 3 instances are, in fact, in 3 separate clusters.

I don't believe that what you are trying to accomplish is possible.


How to make a copy from a Emergency Database? I've a database on a very old Computer (Windows xp, SQL server 8.0). Yesterday, the database has been marked as suspected, I've put it to the emergency state. So now the database is Read-Only. The only way to make it functional again is make a copy of its to another database. Previously, I use Backup to save the database to a File and restore to other database but now, the database is in Emergency mode so that this way is not working. I've tried to import/Export to other database but there is one problem, some key in some the table of the old Database was mark as Identity Specification and it will automatically generate. But now the new database is not mark as Identity Specification and the Null error is generate.

So why the import/export data doesn't make a exact copy of the old database to the new one? And how to make a exact copy to the database?

The import/export wizard doesn't copy a lot of stuff, the identity property on columns is just one of the things that it doesn't copy.

If you don't have a functional backup you'll want to manually script out all the objects from the database first, then copy the data using the import/export wizard.

Once your new database is up and running you'll want to look at setting up regular database backups so that if this happens again the database can just be restored from the backups.

You should also keep in mind that SQL Server 2000 has been end of lived and all support for the product is gone.  Upgrading to a newer version of SQL Server would be a good idea.


postgresql dump and load -- need to dump "postgres" db also? If I pg_dump a db from an 8.4 cluster, and then load it into a different 9.1 cluster, do I need to pg_dump and restore the 'postgres' db as well? (I'm not familiar with what is contained in the 'postgres' db)

I want to upgrade a db from 8.4 to 9.1, so I tried pg_upgradecluster but got a lot of errors, so now I'm going to try doing a dump from 8.4 and load the dump into 9.1.

OS: Ubuntu 11.04 Natty

The postgres database is just the default database that is created during initdb. It has no special meaning.

If you created a different one where all your data is kept and you never created tables in the postgres database, then there is no need to dump it.

I don't know what pg_upgradecluster is. The default tool to upgrade the data directory (apart from dump/restore) is pg_upgrade.

If you want help with the upgrade, "got a lot of error" won't get you much help. You need to be a lot more detailed.


Refactoring database after migrating table I'm creating a new staging database, into which I intend to migrate several tables from the old database. These tables are referenced in functions and stored procedures. If I were doing things manually, I would just migrate the tables, and then modify the functions by replacing the old table name with the new table name. I'm thinking there must be a more automated way of doing this though.

Has anyone done something similar; what tools have you used to help refactor?

Replace the tables in the old database with synonyms that point to the location in the new database.

This not only minimizes the code change, it gives you a single place to manage the references to objects in other databases.


Why can't I use variables in T-SQL like I imagine I can? Forgive me, I am a developer who has moved over to the world of SQL. I thought I could improve some SQL by adding variables but it did not function like I expected. Can someone tell me why this does not work? I don't want a work around, I want to know the reasons why this doesn't work like I imagine it should as I am sure there is a good reason, but currently it doesn't jump out at me.

DECLARE @DatabaseName varchar(150)
SET @DatabaseName = 'MyAmazingDatabaseName'

CREATE DATABASE @DatabaseName
GO

USE @DatabaseName
GO

Per the Books online page for variables

  Variables can be used only in expressions, not in place of object
  names or keywords. To construct dynamic SQL statements, use EXECUTE.

It would work the way you were expecting if, for example, you used your variable in a where clause. As for why, I would think it has something to do with the parser not able to evaluate the variable and thus check for existence. When executing, the query is parsed first for syntax and objects and then, if parsing successful, the query executes at which point the variable would be set.

DECLARE @name varchar(20);
SET @name = 'test';

CREATE TABLE [#tmp]([val] varchar(10));

insert into #tmp
values('test')

SELECT *
FROM [#tmp]
WHERE [val] = @name;


Why is context_info sometimes blank in sys.dm_exec_sessions? I am trying to get information from the context_info column using Cast(context_info as varchar(128)) from sys.dm_exec_sessions as given in the article below:

Finding User Sessions from SPID in Dynamics AX 2012 by Amit R. Kulkarni

However, for some rows, the context_info column is empty. Does anyone know why this is?

The database is a Dynamics AX database and the query I'm using is:

select cast(context_info as varchar(128)) as ci,*
from sys.dm_exec_sessions
where program_name like '%Dynamics%'

Here is the Output, I have had to remove a couple of columns but this is pretty much how it looks. If you see, The Context_info is blank for session 63 and 87.

CONTEXT_INFO needs to be set by the application and Dynamics AX sets the value for the connections included in the session. If CONTEXT_INFO is blank it really is because the application didn't set the property.

When AX uses some connections for a specific session one can see the setting of the CONTEXT_INFO in ETW traces like this:

I did several tests and I couldn't create a situation where the CONTEXT_INFO is not set correctly, even when constructing my own ODBCconnection or using the Connection() class from within AX.

Since you have a situation where for some sessions CONTEXT_INFO is empty it really looks like you have an Object Server somewhere that isn't configured to set the context information for it's sessions.

If you look closely at your results, you will see that both empty rows have the same host process id, and you don't have any rows where that process id does return a context_info.

According to the documentation on sys.dm_exec_sessions the host_process_id is the process id of the client application.

  Process ID of the client program that initiated the session. The value
  is NULL for internal sessions. Is nullable.

What I think is happening is that you have either multiple AX environments on this server, or you have multiple AOS instances where one of them isn't configured according to the article you linked.

For example I have a set-up with a couple of instances on one machine, connecting to a SQL Server and when querying the SQL Server sys.dm_exec_sessions the CONTEXT_INFO is blank for the instances where I didn't configure it, and never blank for the instance I did configure correctly.

These processes:

Show up in the sessions on SQL Server:

However, since I only configured one of the instances if I look at the context info query I get this result (with a bunch of blanks)

My conclusion is that you likely have an instance somewhere where this registry configuration is not properly activated.


Backup status report from multiple server in sql server 2005 Per daily routine activities of DBA, as we know backups are important to be monitored and to make sure they are corrupt free, i am trying to fetch a report to get such status.

Moreover, we have SQL safe installed on three servers which do a compress backup of larger databases.

I tried a lot to look for a report from SQL safe, to fetch the backup status but could not.

I used a script and put it in a job to fetch the backup status on single server, but for multiple instances where native as well as SQL safe like a third party backup is running, how can i get the backup status on daily basis.

Kindly suggest.

Thanks-- Kapil

It is redundant, but i have put this on every server and had it send me an email.  Most third party backup tools put a value in "Description", or you can tell based on
the name or user name.

select
    bs.name backup_name,
    backup_finish_date,
    bs.database_name,
    bs.backup_size ,
    cast(bs.backup_size/1024/1024 as int) 'Backup Size MB',
    cast(bs.backup_size/1024/1024/1024 as int) 'backup size gb',
    bs.compatibility_level,
    bs.Recovery_Model,
    BackupType =
        CASE
            WHEN bs.type = 'L' THEN 'Log'
            WHEN bs.type = 'D' THEN 'Full'
            WHEN bs.type = 'I' THEN 'Differential'
            WHEN bs.type = 'G' THEN 'File Differential'
            WHEN bs.type = 'P' THEN 'Partial'
            WHEN bs.type = 'Q' THEN 'Partial Differential'
        END,
        [user_name],
        [description],
        cast(bs.compressed_backup_size/1024/1024 as int) 'Compressed Backup Size MB',
    cast(bs.compressed_backup_size/1024/1024/1024 as int) 'Compressed Backup Size gb'

from
backupset bs

where bs.backup_finish_date > '12/01/2014'
--and

--bs.database_name in('')
--either put a list of databases you care about, or do a select from
--sys.databases...can change the logic to reflect dbs what haven't been backed up

order by bs.backup_finish_date desc


Find rows where any column matches a search pattern I have a table "Test" with 15 different columns of different data types.

I'm looking for rows having characters like "ABC". But "ABC" can be present in any column and I want to find whole records having "ABC".

I cannot restrict the row using a WHERE clause, because I really do not have any idea in which column the ABC resides? Is there a way to do this?

Well, there is the plain and simple "brute force" way:

SELECT *
FROM   "Test"
WHERE (col1 LIKE '%ABC%' OR
       col2 LIKE '%ABC%' OR
       col3 LIKE '%ABC%' OR
       ...
       col15 LIKE '%ABC%');

Parentheses won't be necessary if you don't have additional WHERE expressions.
Probably faster (and more robust in any case) than concatenating all columns for a single LIKE test.


MySQL - how to use having in group by (SQL query) Have a query like this I use to find the number of products that match a number of words in a list of words:

SELECT GROUP_CONCAT(DISTINCT w.word) AS words, COUNT(DISTINCT w.word) AS wordcount,
COUNT(*) AS productcount
FROM productwords pw JOIN words w ON pw.wordid = w.id
WHERE w.word IN ('lenovo', 'laptop', 'computer')
GROUP BY w.word HAVING COUNT(DISTINCT w.word) >= 1

The query works like it should when matching 1 word, but for some reason it doesn't return matches of 2+ words.

i.e. the output is like this even though there are lots of products that matches 2+ words:

lenovo;1;320
laptop;1;7900
computer;1;8200

Any ideas for fixing this, so it will also return the 2+ word matches?

i.e. so it returns something like this:

lenovo;1;320
laptop;1;7900
computer;1;8200
lenovo laptop;2;278
lenovo computer;2;302
laptop computer;2;3200
etc.

Tables are like this:

products (id, name)
[1..*]
productwords (id, productid, wordid)
[*..1]
words (id, word)

The query has GROUP BY w.word so the COUNT(DISTINCT w.word) is - has to be - 1 for every group, so for every row in the results.

To get the results you want, you need to first GROUP BY product and then GROUP BY words:

SELECT g.words,
       g.wordcount,
       COUNT(*) AS productcount
FROM
    ( SELECT GROUP_CONCAT(DISTINCT w.word) AS words,
             COUNT(DISTINCT w.word) AS wordcount
      FROM productwords pw JOIN words w ON pw.wordid = w.id
      WHERE w.word IN ('lenovo', 'laptop', 'computer')
      GROUP BY pw.productid
      -- HAVING COUNT(DISTINCT w.word) >= 2
    ) AS g
GROUP BY g.words, g.wordcount ;