Oracle row level security setup? How can I configure a table such that a user can modify a row "owned" by that user, but not modify rows "owned" by other users?

You could use a row-level security policy with a statement_type limited to update (or more likely update and delete, and maybe insert too). See the DMBS_RLS.ADD_POLICY docs for details.

Dummy scenario: a list of tasks, only task owners can modify their task.

create table owners(owner_id int primary key
                  , owner_name varchar2(10));
create table tasks(task_id int primary key
                 , owner_id int
                 , description varchar2(20)
                 , completion number);

insert into owners(owner_id, owner_name) values (1, 'Mat');
insert into owners(owner_id, owner_name) values (2, 'Mark');
insert into tasks(task_id, owner_id, description, completion)
       values (100, 1, 'Task for Mat', 0);
insert into tasks(task_id, owner_id, description, completion)
       values (200, 2, 'Task for Mark', 0);
commit;

The policy function:

create or replace
function tasks_update_policy(schema varchar2, tab varchar2)
return varchar2
is
  owner_id  number;
begin
  select owner_id into owner_id
    from owners
    where lower(owner_name) = lower(sys_context('userenv','session_user'));
  return 'owner_id = ' || owner_id;
exception
  when no_data_found then
    return '1=2';  -- deny unregistered users
end;

Implementing the policy:

begin
  dbms_rls.add_policy(object_schema => 'MAT'
        , object_name => 'TASKS'
        , policy_name => 'Tasks_update_policy'
        , policy_function => 'tasks_update_policy'
        , statement_types => 'update,delete,insert'   -- policy restriction
        , update_check => true);
end;
/

When logged in as myself:

SQL> select * from mat.tasks;

   TASK_ID   OWNER_ID DESCRIPTION      COMPLETION
---------- ---------- -------------------- ----------
       100      1 Task for Mat          0
       200      2 Task for Mark         0

SQL> update mat.tasks set completion = 20 where task_id = 100 ;

1 row updated.

SQL> update mat.tasks set completion = 20 where task_id = 200 ;

0 rows updated.

When connected as 'Mark':

SQL> insert into mat.tasks values (101, 1, 'More work for Mat', 0);
insert into mat.tasks values (101, 1, 'More work for Mat', 0)
                *
ERROR at line 1:
ORA-28115: policy with check option violation


Querying mirrored database I need to create a snapshot of one of our mirror databases.

I have gone through a couple of links and it looks like querying can be achieved here by creating a snapshot at a mirror server.

However, I would need your help in understanding this for the DB in my case, where it is over 16 TBs and is used 365*7. Things like when you should create snap files and how much extra storage would be required in setting this snapshot database.

As far as I can understand, it should not be another 16 TB, apart from the  reserved 16 TB of the mirrored database. Also how would one ensure that a snapshot is in sync with principal with acceptable refresh or lag period of 30 minutes?

Please provide some advices.

Ref:

https://technet.microsoft.com/en-us/library/ms187054(v=sql.105).aspx
https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server

  how much extra storage would be required in setting this snapshot
  database.

It will depend on the rate of changes and the age of your snapshot.

Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. Subsequent updates to records in a modified page do not affect the contents of the snapshot. The same process is repeated for every page that is being modified for the first time. In this way, the snapshot preserves the original pages for all data records that have ever been modified since the snapshot was taken.

If your source database is fairly large and you are concerned about disk space usage, at some point you should replace an old snapshot with a new snapshot. The ideal lifespan of a snapshot depends on its growth rate and the disk space that is available to its sparse files. The disk space required by a snapshot depends on how many different pages in the source database are updated during the life of the snapshot. Therefore, if updates are mostly to a small subset of pages that are updated repeatedly, the growth rate will slow over time and the snapshot space requirements will remain relatively small. In contrast, when all of the original pages are eventually updated at least once, the snapshot will grow to the size of the source database. If the disk begins to fill up, the snapshots compete with each other for disk space. If the disk drive fills up, write operations to all the snapshots will fail.

  Also how would one ensure that a snapshot is in sync with principal
  with acceptable refresh or lag period of 30 minutes?

Snapshot is a point in time state of your whole database. There is no sync with the source.  You have to create a new snapshot every 30 mins if your acceptable lag is 30 minutes.

A database snapshot is a read-only, static view of a SQL Server database (the source database). The database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation.

For your case I would highly recommend you look at Always On Availability Groups where you can have read-only copy of your primary database and sync continuously. Details here.


Way to ignore pages in memory for 1 query Is there some way to ignore both cached query plans and any data pages in memory for a single query or batch?  Something like a table hint or other option which can be turned on just for the current connection?

I want to force this query to hit the disk, because I'm trying to tune it and the execution times are all over the place.  Trying to reduce variables in play.

Using the OPTION (RECOMPILE) query hint will be your friend with regards to forcing a new plan each time you execute the query.

Preventing data being read from the cache is impossible.  However, you can clear the cache, assuming you're not using the production machine for these tests.  Clearing the cache will force data to be read from disk; do this prior to running the query each time.  DO NOT DO THIS IN PRODUCTION since you will cause a very quick and noticeable drop in performance for all queries running against the server.

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.  You may want to run the CHECKPOINT command first to ensure all dirty pages are written to disk first.

Clearly, you're going to want to look at the actual execution plans; do that by clicking "Query -> Include Actual Execution Plan" from the menu in SSMS (or press CTRL - M prior to running the query)

You may also want to turn STATISTICS IO, TIME ON at the start of your session to get better diagnostic info from the server.  Turning on "Client Statistics" is also a great tool to see the impact of SSMS on your results.  Hit the "Query -> Include Client Statistics" menu, or press Shift - Alt - s


Size limit of MySQL database table? I'm running a remote script that collects data from the web nightly.  Each morning I import the latest data to my database on my Mac using a command line import to phpMyAdmin, adding to the number of rows from the night before. I then access the data locally using a Python script.  Is there a limit to the size of a table that I can have either in terms of being able to store the data or access it via a query in Python?  And, if so, is there a way to check and see how close I am to the limit?

Please note that I'm not concerned with the import size since I'm just importing daily snapshots and inserting those newest rows.

MySQL has huge limits -- many terabytes.

You may run out of disk space.

Your app code may run out of RAM.  But only if you foolishly fetch all the rows at once.  Instead, use SQL to filter/summarize the data you fetch.

But otherwise, don't worry.


Do all entities in Chen notation have to be diagrammatically linked? Is it good design practice to visually link each entity when using Chen's notation?

Yes, Chen though so and explicitly calls for it his 1976, and 2002 papers. From his 1976 paper, The Entity-Relationship Model-Toward a
Unified View of Data.

From his paper, Entity-Relationship Modeling: Historical Events, Future Trends, and Lessons Learned 2002

  ER Model has Explicit Linkage between Entities. As can be seen in Figures 2 and 4, the linkage between
  entities is explicit in the ER model while in the relational model is implicit. In addition, the cardinality
  information is explicit in the ER model, and some of the cardinality information is not captured in the
  relational model.

Here is Figure 2,

His contemporaries that cite him also seem to be using explicit links  Hartmann, Sven. "Reasoning about participation constraints and Chen's constraints".  Proceedings of the 14th Australasian database conference-Volume 17. Australian Computer Society, Inc., 2003.

Without the links, you have no relationship. It's a dangling entity.


mysqld_pre_systemd fails when starting MySQL on Fedora I installed MySQL using dnf (I'm on fedora) and from the officials rpm on the website. Both had the same problem : I have an error when I do systemctl start mysqld.service.
So I checked systemctl status mysqld.service and got this :
● mysqld.service - MySQL Server
     Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
     Active: failed (Result: exit-code) since Wed 2021-04-07 17:07:54 CEST; 6s ago
       Docs: man:mysqld(8)
             http://dev.mysql.com/doc/refman/en/using-systemd.html
    Process: 39884 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
    Process: 39913 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
   Main PID: 39913 (code=exited, status=1/FAILURE)
     Status: "Data Dictionary upgrade from MySQL 5.7 in progress"
        CPU: 734ms

avril 07 17:07:53 laptop-de-mathis systemd[1]: Starting MySQL Server...
avril 07 17:07:53 laptop-de-mathis mysqld_pre_systemd[39905]: No such file or directory
avril 07 17:07:53 laptop-de-mathis mysqld_pre_systemd[39906]: No such file or directory
avril 07 17:07:53 laptop-de-mathis python3[39907]: detected unhandled Python exception in '/usr/sbin/semanage'
avril 07 17:07:53 laptop-de-mathis mysqld_pre_systemd[39909]: No such file or directory
avril 07 17:07:53 laptop-de-mathis python3[39910]: detected unhandled Python exception in '/usr/sbin/semanage'
avril 07 17:07:53 laptop-de-mathis mysqld_pre_systemd[39912]: No such file or directory
avril 07 17:07:54 laptop-de-mathis systemd[1]: mysqld.service: Main process exited, code=exited, status=1/FAILURE
avril 07 17:07:54 laptop-de-mathis systemd[1]: mysqld.service: Failed with result 'exit-code'.
avril 07 17:07:54 laptop-de-mathis systemd[1]: Failed to start MySQL Server.

I don't really know what to do with this information. I also checked journalctl, but StackExchange considers it as spam, so I can't post it.
Does anyone have an idea how to get me out of this situation?

As mentioned in the comments, the /usr/sbin/semanage file was not working properly. After using the dnf provides command (dnf provides /usr/sbin/semanage), dnf told me that this file was from the policycoreutils-python-utils-3. 1-4.fc33.noarch, so I reinstalled it with dnf reinstall (dnf reinstall policycoreutils-python-utils-3.1-4.fc33.noarch), rebooted, and my problem was solved.
However, I ran into another problem, again in the same place, which I've listed here.


Improving performance of query with `WHERE  ''` I have a qeuery that checks where two columns are not '' (blank) - [blank and NULL are not the same in this case]

Is there any way to improve the performance of the query

SELECT *
FROM myTable
WHERE Currency <> ''
AND Country <> ''

From what I understand, <> is causing a complete table scan?

Do those columns have indexes on them at all? That is rather vital information for questions like this. If there is no index covering either of those columns at all then there is nothing it can do except table scan to match those filters.

If there is at least one index covering one (or both) that it could use, it may be deciding against it anyway due to the SELECT *. If the selectivity of the index suggests to it that many rows will match then a table scan may actually be more efficient than an index seek/scan followed plus row lookups to get the rest of the data that is not contained within the index.

For instance if I run

SELECT parent_id FROM org WHERE parent_id <> ''

on one of our systems it performs an index seek only. Running

SELECT * FROM org WHERE parent_id <> ''

performs a table scan instead. If I force the index choice with

SELECT * FROM org (WITH(INDEX(ix_org_parent_id) WHERE parent_id <> ''

it performs an index seek to locate the start of the records, then the RID lookups to find all the other column data. According to the query plan output it judges this to be ~6 times as costly as just doing the table scan, the estimated costs of the three queries above being 0.22926, 1.81429, and 11.1612 respectively. In this case the number of rows matching the WHERE clause is ~55% of ~50,000.


deleted the /var/log/mysql folder Due to a mistake i deleted the log folder for mysql. Is there a way to generate the folder and the error logs?
I'm using MariaDB 10.3 on Debian 9 Stretch

mysqld keeps the error log file open. If you delete the file the file handler is still valid and the file is available for the process that opened it (=mysqld).

# lsof  | grep mysqld
...
mysqld    20163 20192           mysql    2w      REG              253,2       4434         15 /var/log/mysql/error.log

# rm /var/log/mysql/error.log
# lsof  | grep mysqld | grep error
...
mysqld    20163 20310           mysql    2w      REG              253,2       4549         15 /var/log/mysql/error.log (deleted)

You can get the content of the error log from the /proc filesystem.

# ls -la /proc/20163/fd/ | grep error
l-wx------ 1 mysql mysql 64 May  2 15:49 1 -> /var/log/mysql/error.log (deleted)
l-wx------ 1 mysql mysql 64 May  2 15:49 2 -> /var/log/mysql/error.log (deleted)

# cat /proc/20163/fd/1
2020-05-02T15:49:46.163721Z 0 [Warning] Could not increase number of max_open_files to more than 5000 (request: 65535)
2020-05-02T15:49:46.355461Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-0ubuntu0.18.04.1-log) starting as process 20163 ...
2020-05-02T15:49:46.371978Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-05-02T15:49:46.372006Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-05-02T15:49:46.372010Z 0 [Note] InnoDB: Uses event mutexes
2020-05-02T15:49:46.372013Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-05-02T15:49:46.372016Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-05-02T15:49:46.372023Z 0 [Note] InnoDB: Using Linux native AIO
2020-05-02T15:49:46.372272Z 0 [Note] InnoDB: Number of pools: 1
2020-05-02T15:49:46.372380Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-05-02T15:49:46.376119Z 0 [Note] InnoDB: Initializing buffer pool, total size = 384M, instances = 1, chunk size = 128M
2020-05-02T15:49:46.398759Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-05-02T15:49:46.404255Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-05-02T15:49:46.421829Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2020-05-02T15:49:48.448175Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-05-02T15:49:48.448255Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-05-02T15:49:48.938748Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-05-02T15:49:48.939384Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2020-05-02T15:49:48.939393Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2020-05-02T15:49:48.939823Z 0 [Note] InnoDB: Waiting for purge to start
2020-05-02T15:49:48.990402Z 0 [Note] InnoDB: 5.7.29 started; log sequence number 3292611
2020-05-02T15:49:48.990563Z 0 [Note] InnoDB: Loading buffer pool(s) from /database/mysql/ib_buffer_pool
2020-05-02T15:49:48.990773Z 0 [Note] Plugin 'FEDERATED' is disabled.
2020-05-02T15:49:49.476557Z 0 [Note] InnoDB: Buffer pool(s) load completed at 200502 15:49:49
2020-05-02T15:49:49.569129Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2020-05-02T15:49:49.569147Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2020-05-02T15:49:49.596450Z 0 [Warning] CA certificate ca.pem is self signed.
2020-05-02T15:49:49.596491Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2020-05-02T15:49:49.616398Z 0 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2020-05-02T15:49:49.616425Z 0 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2020-05-02T15:49:49.616456Z 0 [Note] Server socket created on IP: '0.0.0.0'.
2020-05-02T15:49:49.856411Z 0 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=produ-data-db-0000-05f01-use2-relay-bin' to avoid this problem.
2020-05-02T15:49:49.946618Z 1 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2020-05-02T15:49:49.966480Z 2 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2020-05-02T15:49:49.966566Z 2 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000116' at position 2493, relay log './produ-data-db-0000-05f01-use2-relay-bin.000002' position: 1811
2020-05-02T15:49:49.971887Z 1 [Note] Slave I/O thread for channel '': connected to master 'replication@produ-data-db-0000-0c5f4-use2:3306',replication started in log 'mysql-bin.000116' at position 2493
2020-05-02T15:49:51.066602Z 0 [Note] Event Scheduler: Loaded 0 events
2020-05-02T15:49:51.066831Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.29-0ubuntu0.18.04.1-log'  socket: '/var/lib/mysql-files/mysql.sock'  port: 3306  (Ubuntu)
2020-05-02T15:50:05.681566Z 5 [Warning] IP address '10.0.182.254' could not be resolved: Name or service not known

Same way you can get other files (slow log, general log). However if the files were closed and were deleted - you're pretty much out of luck (not saying it's impossible to recover, but hard).


Removal of blank lines I am creating a SSRS Report to produce some address labels. I have a SELECT statement as follows:

SELECT DISTINCT
                         AddressLink.intAddressID, Addresses.txtLabelSalutation, Addresses.txtAddress1, Addresses.txtAddress2, Addresses.txtAddress3, Addresses.txtTown,
                         Addresses.txtCounty, Addresses.txtCountry, Addresses.txtPostcode, Addresses.txtRelationType, Pupils.intNCYear, Pupils.txtSchoolID, Pupils.txtForename,
                         Pupils.txtSurname
FROM            TblPupilManagementAddresses AS Addresses INNER JOIN
                         TblPupilManagementAddressLink AS AddressLink ON Addresses.TblPupilManagementAddressesID = AddressLink.intAddressID LEFT OUTER JOIN
                         TblPupilManagementPupils AS Pupils ON AddressLink.txtSchoolID = Pupils.txtSchoolID
WHERE        (Pupils.intSystemStatus = 1) AND (Addresses.txtAddress1 <> '') AND (Addresses.txtRelationType <> 'OLD Billing') AND (Addresses.intJustContact <> 1)

And within the report itself I am using the following expression to produce the addresses:

=Fields!txtLabelSalutation.Value & vbCRLF &
Fields!txtAddress1.Value & vbCRLF &
IIF(
       Fields!txtAddress2.Value <> "", Fields!txtAddress2.Value, nothing) & " " &
       IIF (
                     Fields!txtAddress3.Value<> "", Fields!txtAddress3.Value, nothing) & vbCRLF & Fields!txtTown.Value
       & vbCRLF &
       IIF(
              Fields!txtCounty.Value <> "", Fields!txtCounty.Value, nothing) & vbCRLF &
              IIF(
                     Fields!txtCountry.Value <> "United Kingdom", Fields!txtCountry.Value, nothing) & vbCRLF & Fields!txtPostcode.Value

My issue is that this expression is producing blank lines for fields that have no data, so I am getting a format like:

Mr & Mrs Smith
22 Somewhere Street

Somewhere Town

SS48 9TQ

How can I concatenate rows together if there are blanks so the address appears in one clean block? I am sure I need to amend my expression, but I am not sure how. I need it to look like:

Mr & Mrs Smith
22 Somewhere Street
Somewhere Town
SS48 9TQ

I have already tried amending the SQL statement using a CAST, however this then removes the DISTINCT clause and this part is vital. I also tried by grouping with the cast but that also failed.

With a bit of help I have solved this, but thought I would post the answer in case it helps someone else.

I have changed the expression to read:

=Fields!txtLabelSalutation.Value & vbCRLF & Fields!txtAddress1.Value & vbCRLF &
    IIF(
            Fields!txtAddress2.Value <> "", Fields!txtAddress2.Value & vbCRLF, nothing) &
    IIF (
            Fields!txtAddress3.Value <> "", Fields!txtAddress3.Value & vbCRLF, nothing) &
    IIF(
            Fields!txtTown.Value <> "", Fields!txtTown.Value & vbCRLF, nothing) &
    IIF(
            Fields!txtCounty.Value <> "", Fields!txtCounty.Value & vbCRLF, nothing) &
    IIF(
            Fields!txtCountry.Value <> "United Kingdom", Fields!txtCountry.Value & vbCRLF, nothing) &
    IIF(
            Fields!txtPostcode.Value <> "", Fields!txtPostcode.Value & vbCRLF, nothing)

My problem was where I was including the line break within the statement.


Getting current date as part of an Stored Procedure I have the following SP:

SELECT
   moncallAdd.FirstListing,
   DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
         DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart,
   DATEADD(MINUTE, mOnCallAdd.duration,
         DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
                 DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) AS OnCallEnd
FROM
 mdr.dbo.mOnCallAdd
 where DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')= DATEADD(ms , 0 - DATEPART(ms , GETDATE()) ,
         DATEADD(s , 0 - DATEPART(s , GETDATE()) , GETDATE())) and
 mOnCallAdd.SchedName = 'arc im'

UNION
SELECT
 moncallDelete.FirstListing,
 DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
         DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) AS OnCallStart,
 DATEADD(MINUTE, mOnCallDelete.duration,
         DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
                 DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) AS OnCallEnd
FROM
  mdr.dbo.mOnCallDelete
where DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')= DATEADD(ms , 0 - DATEPART(ms , GETDATE()) ,
         DATEADD(s , 0 - DATEPART(s , GETDATE()) , GETDATE())) and
  mOnCallDelete.SchedName = 'arc im'

and what I'm trying to do is as follows:

If I have a schedule that runs from 9 a.m. today to 5 p.m. today, and I run my SP anytime during that time frame, I'd like to see that schedule as part of my dataset. I can make this work for date alone, but can't figure out the time portion. Can anyone offer any assistance on this?

  Addendum (was an answer, merging in -- jcolebrand)

Richard,

First let me thank you for your insight about the design of this table, and I will agree with you that it was poorly designed, but I didn't design it (thank god.) Secondly, I tried your query and I am getting better results, but it's still not eliminating all of the data that shouldn't be returned yet. Here's two results from my dataset that should have not shown up:

HEART HOSPITAL - xxxxx  2011-08-15 19:00:00.000 2011-08-16 07:00:00.000
HEART HOSPITAL - xxxxx  2011-08-15 13:00:00.000 2011-08-15 19:00:00.000

(the query was run at 9:18 CST so these wouldn't have occurred yet.)

When I run this query:

SELECT DATEADD(DAY,38490,'12/31/1899'), CAST(CONVERT(VARCHAR(10), getdate(), 111) AS DATETIME)

(38490 is the int in my first value of the StartOncallDate field)

it returns a value of:

2005-05-19 00:00:00.000 2011-08-15 00:00:00.000

Also here are a couple of lines of data in my table:

     Rec Id  Timestamp        SchedName     StartOnCallDate   StartOncallTime    FirstListing     Duration       AddDate  AddTime
    70550    55426893      BITTAR&ESKEW        38490               1020             YIUM            840           38490    1293
    70551    55427287        ZZOB02            38494               1020         CARTER, KIMBERLY    900           38491     247

1) I'm not going to argue database design, since it seems that it's already in place.

2) Looking at the code, I have to presume that StartOnCallDate, StartOnCallTime, and Duration are Integers.  If you're trying to do that with DATETIME, then there are other issues to deal with.

To answer your question...

First off, the math in your where clause is still including the time (hours and seconds).  So this stuff:

DATEADD(ms , 0 - DATEPART(ms , GETDATE()) ,
            DATEADD(s , 0 - DATEPART(s , GETDATE()) , GETDATE()))

Gives this result:

SELECT DATEADD(ms , 0 - DATEPART(ms , GETDATE()) ,
        DATEADD(s , 0 - DATEPART(s , GETDATE()) , GETDATE()))
-----------------------
2011-08-12 15:35:00.000

So, when you compare DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899') to that mess above, it will almost never be equal.  That's because when you use DATEADD(DAY...) to that hard-coded date, you will only end up with values that are days:

SELECT DATEADD(DAY, 40800, '12/31/1899')
-----------------------
2011-09-15 00:00:00.000

To drop the hours and seconds, you could do a bunch more DATEADD stuff, or you could just do something like this:

(SQL Server 2008):
CONVERT (date, GETDATE())

(SQL Server 2005/2008 options):
CAST(CONVERT(VARCHAR(10), getdate(), 101) AS DATETIME)
CAST(FLOOR(CAST(GETDATE() AS DECIMAL(12, 5))) AS DATETIME)

Those will get you JUST the date.  If you throw one of those into your where clause, it will allow you to filter the data, showing you all values that match the current DAY (anything that is going to occur today):

WHERE DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')=
    CAST(CONVERT(VARCHAR(10), getdate(), 101) AS DATETIME) AND
    mOnCallAdd.SchedName = 'arc im'

To give you an idea of the values you are comparing, pull each of those out (with sample data) into a select statement:

SELECT
    DATEADD(DAY, 40766, '12/31/1899'),
    CAST(CONVERT(VARCHAR(10), getdate(), 101) AS DATETIME)
----------------------- -----------------------
2011-08-12 00:00:00.000 2011-08-12 00:00:00.000

Now, to filter out anything that might have already passed, you want to add a condition to your WHERE clause that checks the ending time to determine if that is earlier that the current time.

 DATEADD(MINUTE, mOnCallAdd.duration,
         DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
         DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) > GetDate()

This makes the entire where clause:

WHERE DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')=
        CAST(CONVERT(VARCHAR(10), getdate(), 111) AS DATETIME) AND
     DATEADD(MINUTE, mOnCallAdd.duration,
             DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
             DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) > GetDate() AND
     mOnCallAdd.SchedName = 'arc im'

EDIT:

If you only want items to show up that are currently happening, you need to qualify on both your end time (as above) and your start time.  To qualify your start time, you need to get your start time and make sure that the current time is greater than or equal to the current time:

DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
        DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) <= GetDate()

This makes the entire WHERE clause:

WHERE DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')=
        CAST(CONVERT(VARCHAR(10), getdate(), 111) AS DATETIME) AND
     DATEADD(MINUTE, mOnCallAdd.duration,
             DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
             DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) > GetDate() AND
     DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
             DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) <= GetDate() AND
     mOnCallAdd.SchedName = 'arc im'


Grabbing data from a query with specific criteria, then the data around it The title may be slightly vague... however, this is the basis of what I want to do:
I have people in a database ranked on a statistic, for example, 'Kills'. I want to order the kills by highest to lowest, which I can already do, but this is where I run into an issue.

I would like to then have said query find a specific bit of data, a row with a specific 'Name' as defined in the query, then get 5 rows that would be above it, and 5 rows that would be below it. It would also be helpful if it does this in a timely fashion. If you need more detail, just ask!

For anyone wondering what research I've done, I've found queries that will do this, however those queries use full table scans, which would not be optimal in this case as there is 123k+ rows of data. Also, this is in mysql.

My current query that does a full table scan and doesn't work extremely well, nor effeciently, in general:

SELECT    allusers.rank, allusers.Name, allusers.Kills
FROM (
 SELECT   AgStats.Name,
        @t:=IF(score<=>@s,@t,@r) AS rank,
        @s:=AgStats.Kills AS Kills,
        @r:=@r+1
 FROM     AgStats, (SELECT @r:=0, @s:=NULL, @t:=NULL) init
 ORDER BY AgStats.Kills DESC
) allusers, (
 SELECT COUNT(*) AS rank
FROM   AgStats
WHERE  Kills > (SELECT Kills FROM AgStats WHERE Name = 'VoidWhisperer')
) user
WHERE    ABS(allusers.rank-user.rank) <= 5
ORDER BY allusers.rank

To give an example of what I'm trying to do: You have 10 rows of data, with the row kills on the end. I want to select a specific row out of those 10 based on name, then get a number of rows above it, in this case, 5 rows above/below, done via the query?

First a query without rank. You may get more than 11 rows in the result, if there people with same number of Kills as your player:

  ( SELECT a.*
    FROM AgStats AS a
      JOIN
      ( SELECT Kills  FROM AgStats
        WHERE Name = 'VoidWhisperer'
          LIMIT 1
      ) AS k
      ON a.Kills > k.Kills
    ORDER BY a.Kills ASC
      LIMIT 5
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a
      JOIN
      ( SELECT Kills  FROM AgStats
        WHERE Name = 'VoidWhisperer'
          LIMIT 1
      ) AS k
      ON a.Kills = k.Kills
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a
      JOIN
      ( SELECT Kills  FROM AgStats
        WHERE Name = 'VoidWhisperer'
          LIMIT 1
      ) AS k
      ON a.Kills < k.Kills
    ORDER BY a.Kills DESC
      LIMIT 5
  )
ORDER BY Kills DESC ;

This will show no more than 11 rows, even if there hundreds of players with same number of Kills, by applying an order on the two columns: Kills, Name:

  ( SELECT a.*
    FROM AgStats AS a
      JOIN
      ( SELECT Kills, Name  FROM AgStats
        WHERE Name = 'VoidWhisperer'
          LIMIT 1
      ) AS k
      ON a.Kills = k.Kills AND a.Name > k.Name
      OR a.Kills > k.Kills
    ORDER BY a.Kills ASC, a.Name ASC
      LIMIT 5
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a
    WHERE Name = 'VoidWhisperer'
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a
      JOIN
      ( SELECT Kills, Name  FROM AgStats
        WHERE Name = 'VoidWhisperer'
          LIMIT 1
      ) AS k
      ON a.Kills = k.Kills AND a.Name < k.Name
      OR a.Kills < k.Kills
    ORDER BY a.Kills DESC, a.Name DESC
      LIMIT 5
  )
ORDER BY Kills DESC, Name DESC ;

It will be good if you have a an index on (Kills, Name) and a (unique) index on (Name).

Tested at SQL-Fiddle


Create column for values between a range sql What I want to achieve is to create a new column based on the num_extents between a range and only print the table name below.

This is what I have [table1]:

This is what I want [table2]:

I used a case statement that allows to get values between a range using the below:

select sum(case when num_of_extents between 0 and 9 then 1 else 0 end) as one,
       sum(case when num_of_extents between 10 and 19 then 1 else 0 end) as two,
       sum(case when num_of_extents between 20 and 29 then 1 else 0 end) as three,
       sum(case when num_of_extents between 30 and 39 then 1 else 0 end) as four,
       sum(case when num_of_extents between 40 and 49 then 1 else 0 end) as five,
       sum(case when num_of_extents >= 50 then 1 else 0 end) as six
from (
select
a.tabname,
count(*) num_of_extents,
sum( pe_size ) total_size_pg,
round(sum( pe_size*4),2) total_size_kb,
round(sum( pe_size*4/1024 ),2) total_size_mb,
round(sum( pe_size *4/1024/1024),2) total_size_gb
from systabnames a, sysptnext b, sysdatabases c
where a.partnum = b.pe_partnum
and a.dbsname = c.name
and a.dbsname = 'system'
-- a.tabname not like ' %'
group by 1
order by 3 desc, 4 desc
) a

Output from above:

one    2
two    1
three  1
four   5
five   0
six    2

Please ignore the naming of the columns used, but instead of doing a sum between the range, I want to print out the names of the tables instead like in [table2]. How can I change my sql case statement to do this in informix?

I figured out the answer to my question. The below works fine

select
case when num_of_extents between 0 and 9 then a.tabname end as one,
case when num_of_extents between 10 and 19 then a.tabname end two,
case when num_of_extents between 20 and 29 then a.tabname end three,
case when num_of_extents between 30 and 39 then a.tabname end four,
case when num_of_extents between 40 and 49 then a.tabname end five,
case when num_of_extents >= 50 then a.tabname end six
from (
select
a.tabname,
count(*) num_of_extents,
sum( pe_size ) total_size_pg,
round(sum( pe_size*4),2) total_size_kb,
round(sum( pe_size*4/1024 ),2) total_size_mb,
round(sum( pe_size *4/1024/1024),2) total_size_gb
from systabnames a, sysptnext b, sysdatabases c
where a.partnum = b.pe_partnum
and a.dbsname = c.name
and a.dbsname = 'system'
-- a.tabname not like ' %'
group by 1
order by 3 desc, 4 desc
) a group by 1,2,3,4,5,6

Output shows like below within dbaccess:

one
two    bc_storage_idx2
three
four
five
six

one
two
three
four   tock_mvmt
five
six

one
two
three  rd_stats
four
five
six

one
two    tock_mvmt_idx1
three
four
five
six
...

Please note that I did not use the same table data as above. The above is just an example of how the information will be presented within dbaccess. Due to the number of columns formatting it will not be able to display as needed, but can be filtered when outputting to spreadsheet when extracting.

The below formatting would work if I mess around with my environment variables within AIX:

       select
       (case when num_of_extents between 0 and 9 then a.tabname end)[2,20] as one,
       case when num_of_extents between 10 and 19 then a.tabname[2,20] end two,
       case when num_of_extents between 20 and 29 then a.tabname[2,20] end three,
       case when num_of_extents between 30 and 39 then a.tabname[2,20] end four,
       case when num_of_extents between 40 and 49 then a.tabname[2,20] end five,
       case when num_of_extents >= 50 then a.tabname[2,20] end six
from (
select
a.tabname,
count(*) num_of_extents,
sum( pe_size ) total_size_pg,
round(sum( pe_size*4),2) total_size_kb,
round(sum( pe_size*4/1024 ),2) total_size_mb,
round(sum( pe_size *4/1024/1024),2) total_size_gb
from systabnames a, sysptnext b, sysdatabases c
where a.partnum = b.pe_partnum
and a.dbsname = c.name
and a.dbsname = 'system'
-- a.tabname not like ' %'
group by 1
order by 3 desc, 4 desc
) a group by 1,2,3,4,5,6

I don't know how to make the section visible in bold on stackexchange, but see [2,20], that will be used for format.


When importing raw files for internal conversion, should I use a secondary database or just isolate them within the database? When importing raw files for internal conversion, should I use a secondary database or just isolate them within the database?

I've got between 1.25 and 2GB of CSV files to be imported (and already have most of the process running smooth as butter) so my question is: does it make sense as a "best practice" to use a "secondary" database for the import or just load them into the database that I'm going to be working in?

Example:

FinalDatabase -- this is the one I'm wanting to do, unless there's a net negative
  core.Users
  core.Addresses
  core.Emails
  core.OtherTables
  staging.UsersImport
  staging.ContactImport

OR

FinalDatabase
  core.Users
  core.Addresses
  core.Emails
  core.OtherTables
StagingDB
  dbo.UsersImport
  dbo.ContactImport

Obviously I'll be migrating from one table to the others via scripts, so it doesn't make much difference what the four-part name is going to be, whether it's just

INSERT INTO core.Users (fieldlist) SELECT fieldlist FROM staging.Users

or

INSERT INTO core.Users (fieldlist) SELECT fieldlist FROM StagingDB.dbo.Users

The cons I've identified with the first style are the following:

Increases backup size
Increases file size (including increasing the file size of the existing database -- autogrowth issues)
Negates the potential use of multiple spindles (in my case not an option :( )

Some of the pros I've identified with the first style:

The original import data stays with the database in case of future questions (until someone truncates it, of course)
It gets backed up with the latter data in case you need to recover something

What would be considered a best practice in this situation and why?

Well, since you have the CSV files, they can serve as a backup to the staging tables, so I see no benefit to keeping them in the primary database (or maintaining the staging tables after the import is done). If those staging tables can be discarded / truncated once the import is finished, then they really should be separate and have as minimal impact on the primary database as possible.

Also, I see no value in putting the staging tables in the primary database in any case, no matter how permanent the loaded data will be. And if you need the data to be permanent, you can always reference the data even if it is in a different database - in fact that is arguably better, because now:

you can handle that data alone independently:

set the recovery model of that database to simple
mark it as read only
heck back it up and drop it, knowing that you can always recover it later
restrict access to it in simpler and higher level ways than you could tables in the primary database (to prevent the "until someone truncates it" fear)

or just abandon it because, as I said above, your CSV files are already acting as a secondary backup.

In addition to the cons you've already identified, well, they're essentially just scratch tables. Even if you're only on a single spindle, why bother disrupting the primary database? There's no penalty in crossing database boundaries on the same instance, e.g. INSERT ... FROM db.dbo.table; compared to INSERT ... FROM dbo.table;.

Finally, when you say "autogrowth" I cringe. If you know your database is going to get bigger than it is now, why not manually expand it to the size you need it to be? Autogrow is a performance nightmare when it happens during peak activity - and you will certainly notice it happening even on a single machine and even as the only one performing any work - basically all of your data transfer will halt when either the data or log file needs to grow. This is particularly true if you've left the autogrow settings at the current, ridiculous, defaults, and/or if you don't have instant file initialization. Don't forget to set your log growth settings appropriately too, as log files are not able to take advantage of IFI.


What is meant by "Stand-Alone Instance of SQL Server" I am cloning a server to perform an in-place upgrade and I will have to change the name of that server - which has a SQL Server with just one instance (default instance) installed on it.

I am reading this article and it keeps referring to "stand alone" instance" of SQL Server. What is meant by stand alone here?

Stand Alone:

  Operating, functioning, or existing without additions or assistance; independent; able to be separate or separated.

And that's precisely what is referring to on that article, to a self-sustained SQL Server instance, not participating (as Aaron commented) on a Failover Cluster or a WSFC. That's why the article refers to some scenarios where renaming there computer is directly forbidden or it could wreak havoc as it's not a "stand alone" but part or some other process.


This is fairly straightforward. You can query the data from your temporal tables using the FOR SYSTEM_TIME clause to return the history of each record, then simply use DATEPART to identify the month and aggregate using GROUP BY. You can see an example of this here, using SECOND instead of MONTH to group on because I can't insert literal datetime values in the temporal date fields.

Suitable code for MONTH would be:

SELECT ID, Year, Month, AVG(Value) AS AvgValue
FROM
(
  SELECT ID, Value, DATEPART(MONTH, BeginDate) AS Month, DATEPART(YEAR, BeginDate) AS Year
  FROM TemporalData
  FOR SYSTEM_TIME ALL
) s
GROUP BY ID, Month, Year


Azure SQL Hyperscale, Very Large Tables, and lack of explicit partitioning Hyperscale scales to 100TB when it comes to storage.  And I understand the page server concept - each server handling 128GB of data, with its own local cache.
What I do not understand though is how this can scale from a performance perspective, without explicit user sharding/partioning.
If I have a single table, with billions of rows and terabytes of data, my indexes will continue to grow proportionately.  Every insert will have to update those as well. Surely any query that does a table scan will crumble eventually, and even those using the indexes will get slower and slower?
Other DB solutions that scale tables infinitely, enforce a partition key, and that key needs to be in any query predicate.  How can Hyperscale get away without that requirement?  Have I missed something?
Thanks!

I spoke directly to MS on this.  Their answer is that there is no promises - the bad situation I described could well happen.  They don't give any SLAs on performance on Hyperscale.  There is no way to influence how it shards the data between page servers.


SSIS import with header record I have fixed length text files that I have to import into SQL Server daily. These files contain header records (01) and Detail records (02). I am trying to build an SSIS package to do this. I have a script component that splits the data out but I cant get it to work. I am new to SSIS ...please help.

Here is sample data:
01CA0022-0002                I0000000002618
02      AJ -000000000000375626 -0000000000003
02      AJ -000000000000372226 -00000000000037
02      AJ -000000000000738726 -00000000000073

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void PreExecute()
    {
        base.PreExecute();
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        if (Row.Line.Substring(0, 2) == "01")
        {
            HeaderBuffer.AddRow();
            HeaderBuffer.NAME_EDC = Row.Line.Substring(231, 60);
            HeaderBuffer.DUNS_EDC = Row.Line.Substring(291, 13);
        }
        else if (Row.Line.Substring(0, 2) == "02")
        {
            DetailBuffer.AddRow();
            DetailBuffer.REC_TYPE_DTEAIL = Row.Line.Substring(1, 2);
            DetailBuffer.PMT_NUMBER = Row.Line.Substring(3, 6);
            DetailBuffer.PMT_ACTION_CD = Row.Line.Substring(9, 2);
            DetailBuffer.PMT_AMOUNT = Row.Line.Substring(11, 18);
            DetailBuffer.ADJUSTMENT_CD = Row.Line.Substring(29, 2);
            DetailBuffer.ADJ_AMOUNT = Row.Line.Substring(31, 18);
            DetailBuffer.ACCT_EDC = Row.Line.Substring(49, 50);
            DetailBuffer.ACCT_OLD_EDC = Row.Line.Substring(99, 50);
            DetailBuffer.NO_ACCT_EGS = Row.Line.Substring(149, 30);
            DetailBuffer.PMT_POST_DATE = Row.Line.Substring(179, 8);
            DetailBuffer.XREF_NUMBER = Row.Line.Substring(187, 30);
            DetailBuffer.UNDISCOUNTED_AMOUNT = Row.Line.Substring(217, 18);
            DetailBuffer.DISCOUNT_ADJUSTMENT_AMOUNT = Row.Line.Substring(235, 18);
            DetailBuffer.INVOICE_ADVICE_NUMBER = Row.Line.Substring(253, 30);
        }
    }

    public override void CreateNewOutputRows()
    {
    }

}

The error I get says:

  Error at Data Flow Task [Script Component [85]]: The binary code for the script is not found. Please open the script in the designer by clicking Edit Script button and make sure it builds successfully. Error at Data Flow Task [SSIS.Pipeline]: "Script Component" failed validation and returned validation status "VS_ISBROKEN". Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation. Error at Data Flow Task: There were errors during task validation

Instead of using a script component, I recommend using a more database-centric approach.

Consider the following approach:

Read the data from the flat file using a flat file source.  Keep your flat file source simple.  It should have one column which contains the EntireRow.
(optional) add a script component or derived column to count rows and generate a InputRowNumber field.  The InputRowNumber is helfpul for debugging.
Write the two columns to a simple staging table called InputFlatFile containing the InputRowNumber int, and EntireRow varchar(max) fields.
Using ExecuteSQL Tasks, execute queries which insert the data from your InputFlatFile staging table into your real database.  Based on your example data, these queries should be fairly straight forward.

INSERT INTO HeaderTable (NAME_EDC, DUNS_EDC)
SELECT SUBSTRING(EntireRow, 231, 60) NAME_EDC
     , SUBSTRING(EntireRow, 291, 13) DUNS_EDC
FROM InputFlatFile
WHERE LEFT(EntireRow,2) = '01'

INSERT INTO DetailTable (columns..)
SELECT SUBSTRING(EntireRow, 1, 2) REC_TYPE_DETAIL
     , SUBSTRING(EntireRow, 3, 6) PMT_NUMBER
     (... more columns)
FROM InputFlatFile
WHERE LEFT(EntireRow,2) = '02'

use an ExecuteSQL task to permanently delete the data in the staging table so it is ready for the next load, or use an OUTPUT clause to move it into an archive table if necessary.

I think the database-centric approach is better for the following reasons:

From this base, you can easily create a queryable data lineage.  Want to know which file created which row in the final database?  Create a FileLog table and insert a row at the start of the control flow with logging information like file name, modified date, etc.  Retain a reference to the InputRowNumber and FileLogID in the destination database and you can trace the data from source file to destination easily.
In my experience, people who work with SSIS tend to have a stronger database background than OO programming background so the Set Theory that I used above comes naturally and is easier to maintain on an ongoing basis (also, there tends to be more helpful ideas for database-centric approaches on this and other forums)


MDX Crossjoin filtering on different hierarchies from the same dimension is very slow I have an MDX query that was written by an MDX novice that is excessively slow (the query, that is, not the MDX novice). And I am also an MDX novice. Here is the query:

SELECT
NON EMPTY
(
    [Measures].[Status]
)
ON COLUMNS,
NON EMPTY
(
    Filter
    (
        Crossjoin
        (
            {
                [Value1].[Value1].[Value1A],
                [Value1].[Value1].[Value1B]
            },
            [Value2].[Value2A].[Value2A],
            [Value3].[Value3].[Value3].ALLMEMBERS,
            [Value4].[Value4].[Value4].ALLMEMBERS,
            [Value5].[Value5].[Value5],
            [Value2].[Value2B].[Value2C],
            [Value6].[Value6].[Value6],
            [Value7].[Value7A].[Value7A]
        ),
        (
            [Measures].[Status]
        ) > 0
    )
)
ON ROWS
FROM [Cube]
WHERE
(
    [Value7].[Value7].[Value7A],
    [Value8].[Value8].[Value8A],
    [Value9].[Value9].[Value9A]
)
CELL PROPERTIES VALUE

I have very little knowledge of MDX, but through some trial-and-error, I have found that removing the two [Value1].[Value1] entries from the Crossjoin makes the query return quickly. Or, removing both the [Value2].[Value2A] and [Value5] entries also makes it return quickly. But, obviously, this is changing the query, so is not the solution, but perhaps it provides clues as to where I should look in terms of indexes or suchlike if there is anything like this in SQL Analysis Services (I'm more familiar with SQL Server databases)?

One thing I did try, is to put & before [Value1A] and [Value1B]. This caused the query to return very quickly with no results. Unfortunately, I don't know if this is correct because the query without this change takes too long to be able to see if there are any results. I don't know what difference & is supposed to make, but is this the obvious answer or does it change the query to be different? It is equally possible that the original writer of this query should have used & anyway, but never got to test the query with a real data set.

Any help would be greatly appreciated.

I found an answer here that has helped my situation and now the query returns very quickly. It appears the HAVING clause is a better solution than using the Filter function as long as the dataset is reasonably small. However, I noticed that removing NON EMPTY from the second axis (ON ROWS) causes it to still be slow even using the HAVING clause. I may still need to resolve that problem, too. But, at least the first problem is solved.


Single query to get only newest entry of grouped redundant entries A table files looks like:

id, file_name, vendor_id, created
1,  z.txt,     2,         2011
2,  z.txt,     2,         2011
3,  a.txt,     1,         2016
4,  a.txt,     1,         2016
5,  a.txt,     1,         2016
6,  b.txt,     1,         2015
7,  y.txt,     2,         2015
8,  x.txt,     2,         2014

There is an index on: file_name, vendor_id and created.

I want a list of the latest files (newest_file) of each vendor_id in a single query.

SELECT vendor_id, SUBSTRING_INDEX(GROUP_CONCAT(file_name ORDER BY created DESC), ',', 1) AS newest_file
FROM files GROUP BY vendor_id

This query works perfect, but is very slow. Is there a faster alternative in a single query?

CREATE TABLE `files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` datetime DEFAULT NULL,
  `file_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `vendor_id` int(11) NOT NULL DEFAULT '0',
  `description` mediumtext CHARACTER SET utf8,
  `color` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `vendor_id` (`vendor_id`),
  KEY `file_name` (`file_name`),
  KEY `created` (`created`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

The best index for this query would be on (vendor_id, created, file_name), with this specific order on the indexed columns.

There are various other methods to rewrite this type of queries, without using GROUP_CONCAT(). There is even a tag, at SO and this site: greatest-n-per-group.

One way to rewrite (needs the same index as above). It assumes that (vendor_id, created) is UNIQUE (ie. there are no two rows with same vendor and created) or it isn't unique and you want all the duplicate/tied results:

SELECT
    v.vendor_id,
    f.file_name AS newest_file,
    f.created                    -- you can have this in the results as well
FROM
    ( SELECT vendor_id           -- you can replace the "v" subquery with
      FROM files                 -- a single "vendors" table,
      GROUP BY vendor_id         -- if there is one
    ) AS v
  JOIN files AS f
  ON  f.vendor_id = v.vendor_id
  AND f.created =
      ( SELECT fi.created
        FROM files AS fi
        WHERE fi.vendor_id = v.vendor_id
        ORDER BY fi.created DESC
        LIMIT 1
      ) ;

If (vendor_id, created) is not UNIQUE and/or you want just one row of the duplicates, change the joining condition to:

--  the query as it is
--
  JOIN files AS f
  ON  f.id =
      ( SELECT fi.id
        FROM files AS fi
--
--  the query as it is


What does [FROM x, y] mean in Postgres? I'm just getting started with Postgres. Reading this document I came across this query:

SELECT title, ts_rank_cd(textsearch, query) AS rank
FROM apod, to_tsquery('neutrino|(dark & matter)') query
WHERE query @@ textsearch
ORDER BY rank DESC
LIMIT 10;

I can understand everything in this query, except for this: FROM apod, ....

What does this , mean? I'm used to joins but not to multiple FROM statements separated by a comma.

I searched the net for no avail. After looking at it and thinking, it seems to me that it's declaring a variable called query so it can use it multiple times. But if this is true, what does it have to do with FROM?

The manual has detailed explanation for the comma in the FROM list in the chapter Table Expressions:

The FROM Clause derives a table from one or more other tables given in
a comma-separated table reference list.
FROM table_reference [, table_reference [, ...]]
A table reference can be a table name (possibly schema-qualified), or
a derived table such as a subquery, a JOIN construct, or complex
combinations of these. If more than one table reference is listed in
the FROM clause, the tables are cross-joined (that is, the Cartesian
product of their rows is formed; see below).

The fact that comma-separated table references have been defined in an earlier version of the SQL standard than explicit JOIN syntax does not make the comma wrong or outdated. Use explicit join syntax, where it's technically necessary (see below) or where it makes the query text clearer.
The manual again:

FROM T1 CROSS JOIN T2 is equivalent to FROM T1 INNER JOIN T2 ON TRUE
(see below). It is also equivalent to FROM T1, T2.

There are corner cases where both are not completely equivalent. The manual:

Note
This latter equivalence does not hold exactly when more than two
tables appear, because JOIN binds more tightly than comma. For example
FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition is not the same as
FROM T1, T2 INNER JOIN T3 ON condition because the condition can
reference T1 in the first case but not the second.

This related question demonstrates the relevance of the difference:

“invalid reference to FROM-clause entry for table” in Postgres query

Basically, your observation is exactly right:

it seems to me that it's declaring a variable called query so it can use it multiple times.

Any function can be used as "table function" in the FROM list.
And function parameters can reference columns from all tables to the left of the function, because the notation:
FROM apod, to_tsquery('neutrino|(dark & matter)') query

... is really equivalent to:
FROM apod CROSS JOIN LATERAL to_tsquery('neutrino|(dark & matter)') AS query

The manual on LATERAL queries:

Table functions appearing in FROM can also be preceded by the key word
LATERAL, but for functions the key word is optional; the function's
arguments can contain references to columns provided by preceding FROM
items in any case.

Bold emphasis mine.
The keyword AS is completely optional noise before table aliases (as opposed to column aliases, where it's recommended not to omit AS to avoid possible ambiguities). See:

Date column arithmetic in PostgreSQL query


Allow another user to access my Oracle table I would simply like to allow a colleague to view and edit the Database I've created.

I've tried:

GRANT ALL on FISHTABLE to JDOE;

and it returned Grant succeeded but nothing changed for him and he still wasn't able to view my table.

I also tried

GRANT SELECT smahala.fishtable to jdoe@example.org;

but that failed with SQL Error: ORA_00990: missing or invalid privilege.

Is my issue that I don't have the administrative authority to allow someone else to view my Oracle table? Any advice is appreciated, thanks.

Has he tried "SELECT COUNT(*) FROM smahala.fishtable;"? If you grant select on a table in one schema to another schema that user should be able to access it by including the schema name in the query. However, if NOAA is using virtual private databases, then there may be other obstacles that prevent you from sharing data. If there isn't much data, you can try to export to a flat file and have the other person import the flat file. but this should probably get resolved by your internal DBA's. Since they know how the database is configured. You might also want to verify that you are using the same Oracle database.


Postgres 9.6 (pgAdmin 4) - How to query daily statistics Is there a way I can query through pgAdmin in a way I will get statistics for daily query usage?

I need to find what queries are run against my api as I get huge network traffic. I'm looking for a way I can get top queries for example (top 100). I've tried using various pg statistics, but didn't find anything that provides this feature.

The extension pg_stat_statements is what you are looking for.  It is not provided by PgAdmin4, but you can use PgAdmin4 to read from the view it creates.  It provides cumulative stats, so you either need to reset them every night, or just save daily snapshots and compute the differences.  You might want to use PgAgent to automate some of this.  (I have no experience with PgAgent, I just know that it exists.  I use crontab to schedule things)


Oracle 10g Listener issues after IT changed computer domain name I need some help here.

We have Oracle 10.2.0.5.0 installed on a windows 2008R2 machine.

The issue is that our IT department changed the domain for that machine.
I tried editing our tnsnames.ora and listner.ora files and restarted the listener service.

I can connect to the database remotely using sqldeveloper, but if I try to connect through one our legacy apps that uses BDE I'm getting ORA-03114: not connected to ORACLE.

In addition to this when I try to do a imp command or try to sign in using the sqlplus command on the box I get the following error.

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

So I believe I'm missing something a step here.  I've tried this as well
http://www.oralytics.com/2011/03/changing-domain-of-oracle-database.html
But I received a warning trying to drop the repo, and received an error trying to create it.

lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.5.0 - Production on 24-JAN-2018 10:48
:20

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.5.0 - Produ
ction
Start Date                24-JAN-2018 10:39:51
Uptime                    0 days 0 hr. 8 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oracle\product\10.2.0\db_1\network\admin\listener.o
ra
Listener Log File         C:\oracle\product\10.2.0\db_1\network\log\listener.log

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MYCOMPUTERNAME.myNewDomain)(POR
T=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl.myNewDomain" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.myNewDomain" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT.myNewDomain" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

listner.ORA file

# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = MYCOMPUTERNAME.myNewDomain)(PORT = 1521))
    )
  )

TNSNAMES.ora

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MYCOMPUTERNAME.MYNEWDOMAIN)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

Update:  Added my SQLNET.ORA file
    # sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
    # Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

NAMES.DEFAULT_DOMAIN = MYCOMPUTERNAME.MYCOMPUTERDOMAIN

I appreciate any help you can give me.

What is the entry in SQLNET.ora file of following parameter?

names.default_domain = world

If it doesn't contain your myNewDomain domain name then kindly modify it. I hope it will resolve your domain name misconfiguration. Or ask your system admin to check /etc/hosts file for domain name properly enter or not.


Specify NLS-Settings during Oracle installation under Windows Is there an easy way to specify NLS parameters for the database during the installation?

We want to install a new database (environment: Windows). For testing purpose we need NLS settings for nls_language and nls_territory that differ from the default values (German instead of American). To avoid the process of dropping an recreating the database with changed init.ora I would like to specify those setting during a fresh installation on a VM.

I found no settings in the setup dialogue. Is there any way to achieve this?

environment: Oracle Database Enterprise Edition, available versions 11.2 or 12.1.

From the Oracle forums:  https://community.oracle.com/thread/777001

It appears that NLS_DATABASE_PARAMETERS is essentially a permanent copy of the values in NLS_INSTANCE_PARAMETERS at the time of database creation and cannot be changed later.  Reasoning given in link above is that NLS_DATABASE_PARAMETERS is used by the data dictionary to validate CHECK constraints and changing these parameters could violate those constraints.

NLS_LANGUAGE and NLS_TERRITORY need to be set to the desired values in your parameter file at the time the CREATE DATABASE statement is issued for the values to be stored in NLS_DATABASE_PARAMETERS.


What exactly is the 'online' in OLAP and OLTP? I'm a bit confused because I'm questioning the definition of 'online' in OLTP and OLAP. I used to think that 'online' here means that we want our answer in bounded time and based on the data available at an instance in time.

But OLAP queries can take a some hours to compute - isn't this offline?

And a quick search indicates offline OLAP which sounds rather confusing (offline online analytical processing)...?

What exactly is 'online'?

It is simply a remnant of olden times, when it was used in contrast to batch processing. "Online" here means "interactive", that is, requests to the database are processed as they come and responses are given more or less immediately, or at least as soon as they are available. Batch processing would collect requests into, well, batches, and execute them on schedule; responses would be given after the entire batch execution (e.g. next morning).

Abbreviations OLAP and OLTP hint at another historical artifact:  "on-line" used to be the more common spelling until mid-1980s.


What's called 'Universe Data Source' in Crystal Reports? There are several options to choose which data source we want to connect in Crystal Reports including SAP BEx, Relational Connection, and Universe. I have difficulties in understanding what exactly do they mean my saying a Universe data source. How are Universe data sources created and where are they used for? What is its difference with relational connection?
Thanks.

Universe Data Connection is for connecting Crystal Reports to a Universe type file(.unv), which are created by a Universe Design Tool by SAP. This file is an abstract layer between the db schema and the user. It gives an opportunity to do queries without knowing the underlying schema of the db, hence can be used by the staff who does not know SQL.
In contrast Relational Connecting directs to a specific relational database. Both types can have only one connection.


not able to attach the .mdf file I have attached x_Data.mdf and x_Log.ldf file successfully with SQL Server, to replace the same file I deleted and tried to attach with new file having same name. But it is throwing below error statement.

Can anybody please shed some light on this?

  Msg 5173, Level 16, State 2, Line 1
  One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.

You should check the real database name + files it consists of by executing dbcc checkprimaryfile on your mdf file

as described here: SQL SERVER – FIX – Error: One or more files do not match the primary file of the database


Improve performance of converting a large xml file (~300 MB) to relational table in SQL Server So this is what I have so far:

--Read xml content into a XML data type variable
DECLARE @FileData XML
SELECT @FileData = CONVERT(XML, BulkColumn)
FROM OPENROWSET(BULK '\\file_path\test.xml', SINGLE_BLOB) AS x
--Read from the XML variable to create Entity-Attribute-Value table
SELECT N1.Id.value('@Id', 'varchar(50)') as Id
    , N1.Id.value('@Name', 'varchar(100)') as Name
    , N2.AttributeLongName.value('@AttributeName', 'varchar(100)') as AttributeName
    , N3.AttributeValue.value('.', 'varchar(MAX)') as AttributeValue
FROM @FileData.nodes('/Data/Entities/Entity') as N1(Id) ---1st lvl Node contains the Entity
cross apply Id.nodes('Attributes/Attribute') as N2(AttributeName) --2nd lvl Node contains AttributeName
cross apply AttributeName.nodes('Values/Value') as N3(AttributeValue) --3rd lvl Node contains AttributeValue

This code performs decently when run against an 8 MB XML file (~15s). However, when I try to run it against a 300 MB XML file of the same structure, it takes hours.

Does this mean that the XQuery nodes() method does not scale linearly or scales worst?

Is there any other way I can improve the performance on this?

I had another look at this and can reproduce your issue.  Try adding OPTION ( MAXDOP 1 ) to your query.  In my test rig with a 300MB file this ran in 1 min 42 seconds.  The unhinted version ran for 30 minutes at 100% CPU before I killed it.

You could also have a look at OPENXML.  People often say it's faster with large XML files and it appears to be in this case.  However you should be aware of the known issues with OPENXML ( eg can take 1/8th of buffer pool, is an old-fashioned COM .dll, you must call sp_xml_removedocument etc ).  Once you've researched the pros and cons of OPENXML, you could try something like this:

DECLARE @FileData XML

SELECT @FileData = BulkColumn
FROM OPENROWSET(BULK 'd:\temp\temp.xml', SINGLE_BLOB) AS x

DECLARE @hDoc int

EXEC sp_xml_preparedocument @hDoc OUTPUT, @FileData

SELECT *
INTO #tmp
FROM OPENXML( @hDoc, '/Data/Entities/Entity/Attributes/Attribute/Values/Value', 1 )
WITH
    (
    Id VARCHAR(50) '../../../../@Id',
    Name VARCHAR(100) '../../../../@Name',
    AttributeName VARCHAR(100)  '../../@AttributeName',
    AttributeValue VARCHAR(MAX) '.'
    )

EXEC sp_xml_removedocument @hDoc

Honestly I try and avoid it these days due to those issues; what's the point of one query going faster when you've just lopped 1/8th of your buffer pool?

Finally, the quickest and most scalable method (IMHO) is SSIS.  This version ran in about 10 seconds for the same file as the above methods in my rig.

SSIS XML Import Method

Create a package, add a Data Flow task, add an XML Source and then each table. I created a 300MB file with the same structure as yours and it loaded in about 10 seconds, eg

Obviously you'll need extra time to query the tables you have imported but I think this is a safer method.  You could even scale it out with multiple packages.  Post back if you need more help with the package.


Oracle PL/SQL Trigger to update a outstanding field on one table when another table is updated with date So I have example 2 tables:

movies:

mid - pk varchar2
MovieName - varchar2
outstanding - num

rental:

sid - num pk
mid - fk varchar2
rent_out - date
rent_in - date

Need to have trigger that would decrement value in movies.outstanding when rental.rent_in is populated.

I tried to write something like this in trigger

outstanding = outstanding - 1
when mid = old.mid

but when trigger is fired it increase number instead to decrease. I used for each row and after update. Need help with this please.

So my first trigger to update rental table is:

create or replace
  trigger rentals_rent_trg
  after insert on rentals
  for each row
begin
  update Movies
  set outstanding = outstanding + 1
  where mid = :new.mid;
end;

Please I need help with 2nd trigger.
This is my 2nd trigger, it is not correct, I have tried few things but is not working:

create or replace
  trigger rentals_return_trg
  after update of return_date on rentals
  for each row
begin
update Movies
  set outstanding = outstanding - 1
  where :old.mid = :old.mid;
end;

I have tried to write 2nd trigger like this and then I am receiving ORA-04091....RENTALS is mutating, trigger/function may not see it

create or replace
trigger rentals_return_trg
after update of return_date on rentals
for each row
declare
    return_date_temp date;
begin
    select return_date
    into return_date_temp
    from rentals
    where return_date = :new.return_date
    and return_date is not null;

if return_date_temp is not null
then
      update Movies
     set outstanding = outstanding - 1
     where mid = :new.mid ;
/*mid = :old.mid;*/
end if;
end;

Since I was getting that error I have created compund trigger, however now I managed to start decreasing number however instead to be based on rows it is based on run I belive since it lowers only for one digit down if :old.outstanding = 5 then :new.outstanding = 4. I really need HELP please hahaha.

create or replace trigger rentals_return_trg
FOR update of return_date on rentals
COMPOUND TRIGGER
      cursor return_date_cur is
      select R.rid, R.mid, R.return_date, M.outstanding
       from rentals R JOIN MOVIES M
       ON R.mid = M.mid;
       type return_typ is table of return_date_cur%ROWTYPE
       index by binary_integer;
      return_tbl return_typ;
      INT number(4) := 0;
BEFORE STATEMENT IS
begin
for rec in return_date_cur loop
      int := int +1;
      return_tbl(int).rid := rec.rid;
      return_tbl(int).mid := rec.mid;
      return_tbl(int).return_date := rec.return_date;
       return_tbl(int).outstanding := rec.outstanding;
     end loop;
END BEFORE STATEMENT;
AFTER EACH ROW IS
    LV_RETURN_DT DATE;
    LV_OUTSTANDING_NUM NUMBER;
BEGIN
   for i IN 1..return_tbl.count loop
        if return_tbl(i).return_date = :NEW.return_date THEN
        LV_OUTSTANDING_NUM :=  return_tbl(i).outstanding - i;
       exit;
       end if;
  end loop;
  if :new.return_date is not null then
  update movies
  set outstanding = LV_OUTSTANDING_NUM;
  end if;
end after each row;
end;

Using Oracle 12c, I've tried to re-trace your steps.  Your second trigger needs some modifications ...

Test setup:

create table movies (
  mid varchar2(64) primary key
, MovieName varchar2(64)
, outstanding number
);

create table rentals(
  sid number primary key
, mid varchar2(64) references movies(mid)
, rent_out date
, rent_in date
) ;

-- original trigger -> okay, works for INSERTs
create or replace
  trigger rentals_rent_trg
  after insert on rentals
  for each row
begin
  update Movies
  set outstanding = outstanding + 1
  where mid = :new.mid;
end;
/

Test data (inserts):

insert into movies (mid, moviename, outstanding)
values (1, 'Snatch', 0);
insert into movies (mid, moviename, outstanding)
values (2, 'Aliens', 0);
insert into movies (mid, moviename, outstanding)
values (3, 'Mars', 0);

-- check
select * from movies;

SQL> select * from movies;
MID  MOVIENAME  OUTSTANDING
1    Snatch     0
2    Aliens     0
3    Mars       0

-- table RENTALS: insert 42 rows for movies 1 and 2
begin
  for i in 1 .. 42
  loop
    insert into rentals (sid, mid, rent_out, rent_in)
    values (i+100,1,'19-AUG-2017',null);
    insert into rentals (sid, mid, rent_out, rent_in)
    values (i+200,2,'20-AUG-2017',null);
  end loop;
end;
/

-- checks
SQL> select count(*) from rentals;
COUNT(*)
84

SQL> select * from movies;
MID  MOVIENAME  OUTSTANDING
1    Snatch     42
2    Aliens     42
3    Mars       0

Let's use your second trigger, with minor modifications:

create or replace
  trigger rentals_return_trg
  after update of rent_in on rentals
  for each row
declare
  new_ number default 0 ;
begin
  new_ := :new.mid ;

  update Movies
  set outstanding = outstanding - 1
  where mid = new_;

end;
/

Trigger RENTALS_RETURN_TRG compiled

Now, if we UPDATE some of the rows in the "rentals" table, and have a look at "movies" and "rentals", respectively, everything seems to be working as you wanted it to work (- only the affected rows are SELECTed from rentals in this example).

-- "Aliens" and "Mars" not affected
begin
  for i in 120 .. 125
  loop
    update rentals
    set rent_in = '22-AUG-2017'
    where sid = i ;
  end loop;
end;
/

SQL> select * from rentals where rent_in is not null;
SID  MID  RENT_OUT   RENT_IN
120  1    19-AUG-17  22-AUG-17
121  1    19-AUG-17  22-AUG-17
122  1    19-AUG-17  22-AUG-17
123  1    19-AUG-17  22-AUG-17
124  1    19-AUG-17  22-AUG-17
125  1    19-AUG-17  22-AUG-17

SQL> select * from movies;
MID  MOVIENAME  OUTSTANDING
1    Snatch     36
2    Aliens     42
3    Mars       0


SQL Server agent job dependency We have a 4h datawarehouse job that runs every 4h on 4h schedule. We want to create a new 'daily' schedule and have some processes run out of hours.

However, if when the daily job comes to run and the 4h job is still running, I would like it to wait for the 4h job to complete, then run (or have a specified duration). I haven't decided which is best yet. How is this possible? Thanks!

Please do not suggest 3rd party options as I have no control over the infrastructure.

You can right click on the job in SSMS, go to properties, then to the 'schedule' option.  From the schedule add your daily schedule to the 4h schedule.  Default behavior of SQL Server jobs are that if a job is already running and the same job comes up again, it'll wait for the prior execution to finish before starting a new one.  What you're describing is the default behavior in SQL Server, you just need to give the job 2 schedules.


Using AMMM or Huge Pages for Oracle 11g R2 Database I'm running an Oracle production 11gR2 database which has around 65Gb of data, running with a 4Gb SGA & 40Mb PGA using ASMM.

Now, I have a memory increase up to 32Gb. I'm planning to increase the SGA and PGA for the database but I don't know which one I should use - AMMM or HugePages? I'm using Linux 6 (RHEL?).

This is what Oracle says about it:

About HugePages

  If you have a system with more than 16GB of memory running Oracle
  databases with a total System Global Area (SGA) larger than 8GB, you
  should enable the HugePages feature to improve database performance.


One query two MySQL servers I have two MySQL servers on different server computers. Is it possible to execute single query like this:

INSERT INTO database1.xxxxx SELECT table1.field1, table2.field1 FROM database2.xxxxx INNER JOIN ....

EDIT: I will make cron job witch will run this once a day. I don't need synchronization.

First of all in this scenario you have two remote MySQL server you need to have two connections.
MySQL does not support such a query wherein it connects to other server and get details, in my understanding no other RDBMS does this kind of thing.

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Conclusion:
NO. You can't write such query on Two Mysql servers.


SQL Server access issue I am trying to connect 3 SQL servers using SSMS from a different landing server. The issue is I am able to connect 1 server but unable to connect other 2. The error I get is below:

  A connection was successfully established with the server, but
  then an error occurred during the login process. (provider: SSL
  Provider, error: 0 - An existing connection was forcibly closed by the
  remote host.)

I am unable to figure out what is wrong with other 2 servers and need your help.

Landing server details:

Windows server 2008 R2 Ent Edition
SSMS 2012 SP1

SQL server details:

SQL Server 2012 -- Able to connect
SQL Server 2012 -- Unable to connect
SQL Server 2005 -- Unable to connect

I am able to ping and telnet all 3 servers from my landing server. Also, tried SQL authentication for 2 servers which I am unable to connect but it didn't work. Disabled TCP chimney, didn't work. The same 3 connections are working fine from my other landing server. So not sure what is wrong with this one. Please help me out to fix the issue.

You need to check your SSL and TLS settings.

Check out this KB 3135244. The settings between the client and server should be somewhat consistent (i.e. if the server only allows 1.2 and the client only supports 1.0 I think you might run into issues). Also read through the known issues on that page

Updating to a build that supports TLS 1.2 might resolve your issue.


Using output value of column from function for another column in SQL*Loader I have asked the same question on StackOveflow. Do not know, if this site is linked to that or not and thus whether that question will be seen by professionals here, so I am adding it here.  Please let me know, if this is inappropriate to do so.

==============================

I am trying to define a SQL*Loader control file where col1 is determined by using a user function which takes a couple of parameters with values from the input file. This part is working fine.

I want to use the value returned by above function as a parameter for another user defined function and that does not work. The value sent to the second function is not the calculated value of the first column.

Is this possble? Can someone give me a sample?

Here is my control file

( col1  BOUNDFILLER         POSITION(1:8),
  col2  INTEGER EXTERNAL    "schema_own.get_col2_val(:col1, :col6)",
  col3              POSITION(29:33) CHAR,
  col4              "sysdate",
  col5              constant " ",
  col6              POSITION(9:11)  CHAR,
  col7              "sysdate",
  col8              POSITION(12:18) CHAR,
  col9              POSITION(19:28) CHAR,
  col10             POSITION(29:33) CHAR,
  col11             POSITION(52:63) CHAR,
  col12             "(col2)", --constant    "TEST",
  col13             "schema_own.get_col13_value(:col1, :col2)",
  LAST_UPDATE_TS            "sysdate",
  LAST_UPDATE_USER_I        constant "SQLLDR"
)

What seems to be happening is that the the second parameter in get_col13_value seems to be containing the FIRST character of the col6 instead of the number that col2 should have from the call to get_col2_val.

I tried col12 with both (col2) and (:col2) - same outcome.

Am I missing something?

Can someone also help me understand when to use col2 vs. :col2 in sql loader?

In don't think you can use the resullt of a function. The documentation for the loader can be found in the Utilities Manual.
Applying SQL Operators to Fields says

  The execution of SQL strings is not considered to be part of field setting. Rather, when the SQL string is executed it uses the result of any field setting (...)

Field setting means that the field names are assigned to the substrings of the current data of the record of the  file that is loaded. How this works is described in Specifying the Position of a Data Field: If you have a POSITION(x:y) specification for a column named colthen the substring from position  x to position y is the field value of col and it can be references as :col in an sql-string. If there is no POSITION specifiaction found as in the specification of your fields col2, col4,  col5, col7, col12, col13, LAST_UPDATE_T and LAST_UPDATE_USER_I, then  POSITION is defined some way depending on the previous field and the data type of the column. So because the col2-Field (that is referenced by :col2 in an sql-string) has no explicit field specification it is the  field of INTEGER EXTERNAL  type  (of default lenght 1) following col1. So it actually is POSITION(9:9) and therefore the first character of:col6

If you want to set the column values of a table depending on other column values you can use a trigger on the table.


Drop the user from Read only Secondary database Always ON server How to drop a user from the secondary database in an AG when it is in read only mode and synchronizing in SQL Server 2012?

The login and user have been deleted from the primary database, and we are trying to recreate the login on primary.  But when we are trying to create the login on primary, it is throwing an error saying it already exists.

We suspect due to the user being present on the secondary it is not allowing it to create on the primary. Hence we need a way to create the login by deleting the user in secondary.

Any ideas on how do we do that?

You can't do this to the secondary only.  Dropping a user modifies the database, which is not possible because the secondary AG database is in read only mode.

You would have to drop it on the primary - then the change will be replicated to the secondary.

From the updated question, it sounds like you're trying to recreate both the login and the user after removing the user from the primary.

It's hard to tell without seeing the actual T-SQL being executed, but you may have better luck with removing the login from the secondary first, as that is a server level object and thus wasn't removed when you dropped the user on the primary.

  The login and user have been deleted from the primary database, and we are trying to recreate the login on primary. But when we are trying to create the login on primary, it is throwing an error saying it already exists.

I think it's important to clarify something real quick.  A login exists at the instance level, not in any specific database.  You can just connect to each instance and create the login.

A user exists at the database level, and is linked to a login.  To create the user, you'll need to connect to the primary database (whichever instance that happens to be on), and create the user there.  It will automatically be created on the secondary.


Optimization: Moving variable declarations to the top of your procedure While working on optimizing some stored procedures, I sat down with the DBA and went through some stored procedures with high blocking and/or high read/write activity.

One thing the DBA mentioned was I should declare all variables (especially TABLE ones) at the top of the stored procedure to avoid recompiles.

This is the first I have heard of this and was looking for some confirmation before revisiting all the different stored procedures we have. He was calling it "late viewing of the code", and the recompile was locking the schema which would account for the blocking.

Does moving all variable declarations to the top of your stored procedure reduce recompiles?

No.
This either used to be true a long time ago (and no longer is, at least since SQL Server 2000), or it never was true and your DBA just confused his recommendation with the following one:

It is important to group together all DDL statements (like creating indexes) for temporary tables at the start of a stored procedure. By placing these DDL statements together unnecessary compilations due to schema change can be avoided.

You can find another explanation of the reasoning behind this recommendation on this page.
If we take a look at this Microsoft KB, we see that the cause of a stored procedure recompile can be one of the following (SQL Server 2005+):

Schema changed.
Statistics changed.
Recompile DNR.
Set option changed.
Temp table changed.
Remote rowset changed.
For browse perms changed.
Query notification environment changed.
MPI view changed.
Cursor options changed.
With recompile option.

Declaring a variable -- even a table variable (i.e. @table_variable) -- cannot trigger any of these events, obviously, because declaring a variable doesn't count as DDL. A variable (even a table variable) is a temporary object used exclusively for your T-SQL programming. That's why table variables get no statistics and are not bound by transactions.  Declaring a variable (table or not) cannot trigger a proc recompile.
Creating a temp table (i.e. #temp_table) or an index, however, is DDL that affects the physical definition of the database. Temp tables and indexes are "real" objects with statistics and transactional control, therefore creating them could fire any of events 1, 2, or 5 in the list above and thus trigger a proc recompile.


SQL Server Database recovery from corrupt database Last week I backed up my SQL Server by using Backup Exec 2012. I named the file "SQL Server BAK" which contained copies of my SQL Server databases. A few days ago I lost some part of my data due to accidental deletion. I backed it up, so I tried to restore the database from the .bkf file. The problem comes here, when I try to to restore my .bkf file, it becomes inaccessible.

Does anyone know what causes this? I'm suspecting corruption here (it's just a suspicion as I'm not sure). Please help me. The copies of my databases are very crucial.

Many thanks in advance.

I think you're running the restore with the NO RECOVERY option. This will restore your database, but will leave it in the RECOVERY state.

You can easily check this by taking a look at it in the SSMS. It should say something like: yourDatabaseName (Recovering...)

If you haven't the option to take a look in your SSMS, you can simply querying your master database (if your restore doesn't aim at your master database at all. :-D).

SELECT name, state_desc
FROM sys.databases

You can filter it by name for example. Take a look at the column state_desc it should say something like RECOVERING.

If this is the case and your restore is successfully. You can simply run this:

RESTORE DATABASE yourDatabase WITH RECOVERY

This will immediately remove the RECOVERING state from your database and set it back to ONLINE.

Behind the scenes:

RECOVERING is just a transition state from (for example) RESTORING to ONLINE. In most cases a restore using WITH RECOVERY is good enough to set the database back online after the successful restore. But in some cases (e.g. Mirroring) you need to restore a copy of a database on a different machine and leave it in the RECOVERING state to define it as the mirroring partner for your PRINCIPAL database.


SELECT rows based on indefinite number of filters How do I create a function which takes indefinite parameters? And then finds all game_ids in a table where each parameter matches a different row (but same game_id)?
Example
Table:
create table tags (
    tag_id    serial primary key,
    game_id   int, -- references games(game_id),
    tag_name  text,
    tag_value text
)

Sample data:
 tag_id | game_id | tag_name  |      tag_value
--------+---------+-----------+----------------------
     55 |       6 | Event     | EUR-ASIA Rapid Match
     58 |       6 | Round     | 5
    400 |      38 | Event     | EUR-ASIA Rapid Match
    403 |      38 | Round     | 4

Example request: Let's say I want all game_ids where
Event (a tag_name) = 'EUR-ASIA Rapid Match' (a tag_value)
AND
Round (a tag_name) = '5' (a tag_value)

A hardcoded solution for that exact scenario only might look like this:
with m1 as (
  select game_id from tags
    where tag_name = 'Event'
    and tag_value = 'EUR-ASIA Rapid Match'
), m2 as (
  select game_id from tags
    where tag_name = 'Round'
    and tag_value = '5'
) select * from m1 intersect select * from m2;

Except I want an indefinite number of tag matches. Can I create a function that takes an arbitrary number of tag names/values and returns a set of game_id matching all? The call might look this (pseudo-code):
select * from get_games_by_tags('{Event,EUR-ASIA Rapid Match}', ...)

This is a special case of relational-division. Here is an arsenal of query techniques:

How to filter SQL results in a has-many-through relation

The special difficulty of your case is to filter on the combination of two attributes, but the principle is the same.
You can make this fully dynamic with plain SQL, without string concatenation and dynamic SQL:

Using same column multiple times in WHERE clause

But performance won't come close to the following solution with dynamic SQL.
For best performance, have this (UNIQUE) multicolumn index:
CREATE [UNIQUE] INDEX ON tags (tag_name, tag_value, game_id);

Maybe your PRIMARY KEY on tags already spans these columns. For best performance you need index columns in the demonstrated order. Create an additional index if the PK does not match or change the column order of the PK unless you need columns in a different order (too). Related:

Is a composite index also good for queries on the first field?
PostgreSQL composite primary key

The basic query technique I chose uses the pattern:
SELECT game_id
FROM   tags t
WHERE (tag_name, tag_value) = ('Event', 'EUR-ASIA Rapid Match')
AND    EXISTS (SELECT FROM tags WHERE game_id = t.game_id AND (tag_name, tag_value) = ('Round', '5'))
AND    EXISTS (SELECT FROM tags WHERE game_id = t.game_id AND (tag_name, tag_value) = ('some_tag', 'some value'))
AND ...

This query is already optimized for performance.
Function
Using a user-defined row type as input like you have in your answer (optional, but convenient for the function design). I chose the name game_tag because tag felt too generic:
CREATE TYPE game_tag AS(
   tag_name  text
 , tag_value text
);

Note the subtle differences in syntax for these two row values:
'(Event,"EUR-ASIA Rapid Match")'::game_tag
('Event', 'EUR-ASIA Rapid Match')

The first one is a string literal for the registered row type game_tag, the second is a ROW constructor on two string literals building an anonymous row, short for:
ROW('Event', 'EUR-ASIA Rapid Match')

Either works for our purpose and gets index support. Just don't confuse the different syntax requirements. Related:

Invalid input syntax for type numeric: “(0.0000000000000000,8)”

The shortcuts for just 1 or 2 parameters are optional but should further improve performance.
CREATE OR REPLACE FUNCTION f_games_by_tags(VARIADIC _filters game_tag[])
  RETURNS table (game_id int) AS
$func$
BEGIN
   CASE cardinality(_filters)
-- WHEN 0 THEN  -- impossible
   WHEN 1 THEN
      RETURN QUERY
      SELECT t.game_id
      FROM   tags t
      WHERE (tag_name, tag_value) = _filters[1];

   WHEN 2 THEN
      RETURN QUERY
      SELECT t.game_id
      FROM   tags t
      WHERE (tag_name, tag_value) = _filters[1]
      AND    EXISTS (
         SELECT FROM tags t1
         WHERE  t1.game_id = t.game_id
         AND   (tag_name, tag_value) = _filters[2]
         );

   ELSE
      RETURN QUERY EXECUTE
        (SELECT 'SELECT game_id FROM tags t WHERE (tag_name, tag_value) = $1[1] AND '
              || string_agg('EXISTS (SELECT FROM tags WHERE game_id = t.game_id AND (tag_name, tag_value) = $1[' || g || '])', ' AND ')
         FROM   generate_series (2, cardinality(_filters)) g)
      USING _filters;
   END CASE;
END
$func$  LANGUAGE plpgsql;

db<>fiddle here
Should be faster by orders of magnitude than what you have in your answer.
Call:
SELECT * FROM f_games_by_tags('(Event,"EUR-ASIA Rapid Match")');
SELECT * FROM f_games_by_tags('(Round,5)', '(Event,"EUR-ASIA Rapid Match")', '(some_tag,"some value")');

You can also pass an actual array to a VARIADIC function. Related:

How to use an array as argument to a VARIADIC function in PostgreSQL?
Use array of composite type as function parameter and access it


Running SQL Server on VMWare along with many other VM's Excuse the long question, this is my first on the board.

I have just joined an organisation that is running a mission critical SQL server 2012 on a VMWare host along with another 9 (at least) VM's (web servers and batch processing servers mainly) on the same physical box.

The Physical box has 6 CPU's with 64GB of Memory at the moment. The disks are tiered so that tier 1 is pure SSD, tier 2 is SAS with SSD cache, and tier 3 something slower. I believe failover is provided at the VM level. It is not set up to reserve any CPU's or memory for the SQL server.

Our main bespoke application system suite runs the company and is a Tier 1 application which has 250GB data spread across multiple databases.

We do not have a massive IT team, and no one who is experienced at the VM and SQL level. Therefore we have hit a bit of an impasse.

Myself (with 25 years development experience) and our Experienced Contract DBA believe that a much better solution which would be simpler and easier to manage would be to avoid the VM route for the database boxes, or to use HyperV and allocate specific resources to the SQL server.

The SQL server (and it's clustered counterpart when we implement it) should live on separate physical boxes, or properly configured HyperV instances. One of the reasons is that the DBA wants to horizontally stripe some of the larger tables, and our infrastructure team have said separate drives per NDB file are not possible with the current setup. We could put the current years data on tier 1 and older data on tier 2.

From reading VMWare best practices, the host should really only contain instances of SQL server anyway, however we have all sorts of VM's installed there. You should also assign specific CPU's to HyperV CPU's for sole SQL use.

As you can imagine, when a slowdown occurs, at the moment, it's very difficult to identify the exact problem as VMware is spreading usage where it wants to.

Our Infrastructure team maintains that this is a valid configuration, but to be honest, myself and our DBA in our 40 odd years of experience have never seen anything like it. The solution we recommend needs to last for at least 3 years before we start looking at the configuration again.

My questions are (and treat this as if cost is not a major issue)

Is the current configuration good design/best practice?
Are there any benefits to the current configuration?
Would we not be better having two physical clustered SQL boxes at different sites and perhaps a mirrored VM host with web servers and possibly some service machines on them?
Any help or suggestions would be seriously appreciated.

There is nothing inherently wrong with the current configuration however you have not included info that would be relavent, such as whether the host is overcommitted on RAM/CPUs. What problem are you trying to solve - performance and/or HA/DR? Assuming you are not overcommitted:

Make sure the SQL Server instance(s) have sensible max memory settings. Ensure resource is allocated to accomodate that. If money is no object, use resource pools.
VMware (in my opinion) is a better choice than HyperV, but it isn't a big deal which you choose.
If you have enough memory to hold all the databases on the instance(s) which are virtualized, your main concern after that is CPU pressure, which is easy to measure. If you don't (which seems to be the case from your description), you're relying on storage for IO much more, so using the SSDs is obviously a good approach.
I'd suggest the contractor DBA you are working with is coming up with ideas designed to keep him/her in a job as opposed to solving a problem. Either that or they have no experience of VMware. Or both. You can still allocate resources in VMware in much the same way as with HyperV. Striping tables is not necessarily a performance improvement, depending on the nature of your storage array and the IO requirements of the other VMs.
If your SQL instance is Enterprise edition, you ought to use a physical box.
WHen you say "6 CPUs" are you talking about an 8 socket box with unpopulated sockets or a single socket populated with 6 cores? If it is the latter, your performance issues will almost certainly be caused by processor availability, as VMware will only use all cores allocated to a VM once they all become available.

Bottom line - it seems that the host is under resourced for memory and CPU cores, which won't go away with HyperV. If you have need for more than 128GB of memory (based on amount of data in all databases) a physical box for the SQL server would be a good choice.

I wouldn't bother with anything fancy like table partitioning until you have sorted your resource issues.