Can I run a CLR Stored Procedure on a different server than the database instance? I want to add a trigger whenever a row is inserted into a certain table. The trigger will fire off a CLR stored procedure which will do things other than database work, such as write a JSON string to a txt file and update a system log of a different type of database.

My hands are tied to using this technology. Originally we were going to write a windows service that will periodically scan the table and then run the program if new records were inserted.

My question is:

Can I host the DLL on a different server so it will get run there, or when I deploy a CLR Stored Procedure dll, will it always run within the SQL Server DB instance?

The reason I ask is that we don't want this code slowing down the database server, which it likely would by performing these other tasks within the code.

Can I host the DLL on a different server so it will get run there?

How would this work? If you want to run code on a remote server, you would still need code on the local server to be able to make that remote call.

Although, you could set up an instance of Express Edition to host the SQLCLR code (it would need to be a Stored Procedure(s) and/or Function(s) instead of a Trigger), then create a Linked Server to that Express instance, and then have a T-SQL Trigger on the local table execute the SQLCLR Stored Procedure(s) and/or Functions over the Linked Server. In this manner, the local code calling the remote code is pure T-SQL. You wouldn't even need to enable CLR Integration on the local instance.

  The reason I ask is that we don't want this code slowing down the database server, which it likely would by performing these other tasks within the code.

Whatever work you inside of a Trigger, be it a T-SQL or SQLCLR Trigger, will hold up the completion of that DML statement. Using SQLCLR does not really change anything there.

What are the exact requirements of the Trigger with regards to:

  do things other than database work, such as write a JSON string to a txt file and update a system log of a different type of database.

Do those steps / actions need to be bound by the transaction, such that if the INSERT operation fails, the txt file does not exist and the log entry is not there?

Finding a way to do this asynchronously would be ideal (as also mentioned by Jonathan). However, making a Web Service call removes the transactional aspect so you might get files and log entries for rows that do not actually exist.

Using an NT service would be the least impacting on the INSERT operation, and unless you are using WITH (NOLOCK) (which you shouldn't do), it will only see the results of successful INSERT operations. But, the service is disconnected and will have more latency than the Trigger model, and you will need to find a means of tracking which entries are "new" (the NT service will need to keep track of either a DATETIME or maybe could use a ROWVERSION value and compare to @@DBTS each time). If your table already has a DATETIME or DATETIME2 column being set upon INSERT, then you can leverage that existing column, else you will need to add a DATETIME / DATETIME2 / ROWVERSION column. OR, if you have a PK based upon either an IDENTITY or Sequence, then you can use that value instead of adding a column, and the NT service would just need to keep track of the most recently used ID. Where would you store that value? Text file? Registry? The "Cloud"? (just kidding about that last one ;-)

On the other hand, you could probably use Service Broker to match the two pieces together. When the Trigger fires, it can add a message to the Service Broker queue, including any necessary info from the INSERTED table. This allows it to be asynchronous. Then, on the other end, the queue reader can use a SQLCLR stored procedure as the "activation procedure" to take that info and take the appropriate actions (creating the JSON file, logging the entry on the other system, etc).

This model might not require Service Broker. You can set up your own queue table, have a T-SQL Trigger dump entries into it, and then create a SQL Server Agent job that reads from the queue table and calls the SQLCLR Stored Procedure(s) and/or Function(s) asynchronously (whether they are located on the local server or across a Linked Server), and then removes the records when it has processed them successfully.


Duration how long a door is open, but same status records can be present more than once I want to calculate the difference between a status change, however the same state can have multiple records, see example below.

create table #DeviceMessages
(
    DoorName nvarchar(50),
    [StatusDoor] nvarchar(50),
    EventEndDate DatetIme
)

INSERT INTO #DeviceMessages
  (DoorName, [StatusDoor], EventEndDate)
VALUES
('Door 001', 'OPEN', '2017-03-23 16:50:42.143'),
('Door 001', 'CLOSED','2017-03-23 16:54:15.813'),
('Door 001', 'CLOSED','2017-03-23 17:53:12.757'),
('Door 001', 'OPEN', '2017-03-23 18:22:37.560'),
('Door 001', 'CLOSED','2017-03-23 18:28:01.197'),
('Door 001', 'CLOSED','2017-03-23 18:28:01.250'),
('Door 001', 'OPEN', '2017-03-23 18:29:40.593'),
('Door 001', 'OPEN', '2017-03-23 18:29:40.600'),
('Door 001', 'CLOSED','2017-03-23 18:34:53.827'),
('Door 001', 'CLOSED','2017-03-23 18:34:53.843'),
('Door 001', 'OPEN', '2017-03-23 18:42:56.463'),
('Door 001', 'OPEN', '2017-03-23 18:42:56.470'),
('Door 001', 'CLOSED','2017-03-23 18:46:32.550');
GO

It goes well when there are multiple CLOSED records, but it doesn't work when there are multiple OPEN records. At that moment I want the earliest OPEN record, compared to the nearest CLOSED record. I use the following statement:

    WITH DoorActivityWPairedTimes
AS
(SELECT [StatusDoor]
, EventEndDate
, DeviceDoorOpen = DoorName
, DeviceDoorClosed = LEAD(DoorName, 1) OVER(ORDER BY DoorName, EventEndDate)
, DoorOpen = EventEndDate
, DoorClosed = LEAD(EventEndDate, 1) OVER(ORDER BY DoorName, EventEndDate)
, DoorOpenActivityDate = CAST(EventEndDate AS DATE)
, DoorDichtActivityDate = CAST(LEAD(EventEndDate, 1) OVER(ORDER BY DoorName, EventEndDate) AS DATE)
FROM #DeviceMessages)

SELECT DeviceDoorOpen Device
, EventEndDate
, DoorOpen
, DoorClosed
, DATEDIFF(second, DoorOpen, DoorClosed) DurationOpenSec
FROM DoorActivityWPairedTimes
WHERE [StatusDoor] = 'OPEN'
  AND DoorOpen Is Not Null
  AND DeviceDoorOpen = DeviceDoorClosed
  AND DoorDichtActivityDate = DoorOpenActivityDate
  order by EventEndDate asc

The result of the query above:

+----------+-------------------------+-------------------------+-------------+
|  Device  |        DoorOpen         |        DoorClosed       | Dur.OpenSec |
+----------+-------------------------+-------------------------+-------------+
| Door 001 | 2017-03-23 16:50:42.143 | 2017-03-23 16:54:15.813 |         213 |
| Door 001 | 2017-03-23 18:22:37.560 | 2017-03-23 18:28:01.197 |         324 |
| Door 001 | 2017-03-23 18:29:40.593 | 2017-03-23 18:29:40.600 |           0 |
| Door 001 | 2017-03-23 18:29:40.600 | 2017-03-23 18:34:53.827 |         313 |
| Door 001 | 2017-03-23 18:42:56.463 | 2017-03-23 18:42:56.470 |           0 |
| Door 001 | 2017-03-23 18:42:56.470 | 2017-03-23 18:46:32.550 |         216 |
+----------+-------------------------+-------------------------+-------------+

WANTED RESULT

+----------+-------------------------+-------------------------+-------------+
|  Device  |        DoorOpen         |        DoorClosed       | Dur.OpenSec |
+----------+-------------------------+-------------------------+-------------+
| Door 001 | 2017-03-23 16:50:42.143 | 2017-03-23 16:54:15.813 |         213 |
| Door 001 | 2017-03-23 18:22:37.560 | 2017-03-23 18:28:01.197 |         324 |
| Door 001 | 2017-03-23 18:29:40.593 | 2017-03-23 18:34:53.827 |         313 |
| Door 001 | 2017-03-23 18:42:56.463 | 2017-03-23 18:46:32.550 |         216 |
+----------+-------------------------+-------------------------+-------------+

Can some helpme to make a query that makes the wanted result?

IMHO this question can be solved using a classic GROUPING AND WINDOWS solution.

Reset point

According to your question:

  I want the earliest OPEN record, compared to the nearest CLOSED record.

Two or more contiguous CLOSED records, require two different groups.

I've a set a reset point when:

Last StatusDoor IS NULL (First record)
Last StatusDoor is CLOSED and current StatusDoor is CLOSED. (Nearest CLOSED record).
Current StatusDoor is OPEN and last StatusDoor is CLOSED (New Open/Closed event).

In addition, I've added a MinMaxOrder just to ensure OPEN events can be fetched before CLOSED events.

WITH ResetPoint AS
(
    SELECT DoorName, [StatusDoor], EventEndDate,
           CASE WHEN ([StatusDoor] = 'CLOSED'
                     AND  COALESCE(LAG(StatusDoor) OVER (PARTITION BY DoorName ORDER BY EventEndDate), 'CLOSED') = 'CLOSED')
                     OR
                     ([StatusDoor] = 'OPEN'
                     AND  COALESCE(LAG(StatusDoor) OVER (PARTITION BY DoorName ORDER BY EventEndDate), 'CLOSED') = 'CLOSED')
                THEN 1 END IsReset
    FROM   #DeviceMessages
)
   , SetGroup AS
   (
        SELECT DoorName, [StatusDoor], EventEndDate,
               CASE WHEN StatusDoor = 'OPEN' THEN 0 ELSE 1 END MinMaxOrder,
               COUNT(IsReset) OVER (PARTITION BY DoorName ORDER BY [EventEndDate]) grp
        FROM   ResetPoint
    )
SELECT * FROM SetGroup;
GO

DoorName | StatusDoor | EventEndDate        | MinMaxOrder | grp
:------- | :--------- | :------------------ | ----------: | --:
Door 001 | OPEN       | 23/03/2017 16:50:42 |           0 |   1
Door 001 | CLOSED     | 23/03/2017 16:54:15 |           1 |   1
Door 001 | CLOSED     | 23/03/2017 17:53:12 |           1 |   2
Door 001 | OPEN       | 23/03/2017 18:22:37 |           0 |   3
Door 001 | CLOSED     | 23/03/2017 18:28:01 |           1 |   3
Door 001 | CLOSED     | 23/03/2017 18:28:01 |           1 |   4
Door 001 | OPEN       | 23/03/2017 18:29:40 |           0 |   5
Door 001 | OPEN       | 23/03/2017 18:29:40 |           0 |   5
Door 001 | CLOSED     | 23/03/2017 18:34:53 |           1 |   5
Door 001 | CLOSED     | 23/03/2017 18:34:53 |           1 |   6
Door 001 | OPEN       | 23/03/2017 18:42:56 |           0 |   7
Door 001 | OPEN       | 23/03/2017 18:42:56 |           0 |   7
Door 001 | CLOSED     | 23/03/2017 18:46:32 |           1 |   7

As far as each CLOSED event produces a new group, I can get MIN and MAX event of each group, and return only those groups with more than one record.

--= Set a reset point each time the door opens and last event is a closed event
--
WITH ResetPoint AS
(
    SELECT DoorName, [StatusDoor], EventEndDate,
           CASE WHEN ([StatusDoor] = 'CLOSED'
                     AND  COALESCE(LAG(StatusDoor) OVER (PARTITION BY DoorName ORDER BY EventEndDate), 'CLOSED') = 'CLOSED')
                     OR
                     ([StatusDoor] = 'OPEN'
                     AND  COALESCE(LAG(StatusDoor) OVER (PARTITION BY DoorName ORDER BY EventEndDate), 'CLOSED') = 'CLOSED')
                THEN 1 END IsReset
    FROM   #DeviceMessages
)
   --= Set a group for each reset point, and establish specific order as 0='OPEN', 1='CLOSED'
   --
   , SetGroup AS
   (
        SELECT DoorName, [StatusDoor], EventEndDate,
               CASE WHEN StatusDoor = 'OPEN' THEN 0 ELSE 1 END MinMaxOrder,
               COUNT(IsReset) OVER (PARTITION BY DoorName ORDER BY [EventEndDate]) grp
        FROM   ResetPoint
    )
    --= Get max and min event date avoiding single CLOSED events.
    --
    SELECT   DoorName,
             MIN(EventEndDate) DoorOpen,
             MAX(EventEndDate) DoorClosed,
             DATEDIFF(SECOND, MIN(EventEndDate), MAX(EventEndDate)) DurationOpenSec
    FROM     SetGroup
    GROUP BY DoorName, grp
    HAVING   COUNT(*) > 1
GO

DoorName | DoorOpen            | DoorClosed          | DurationOpenSec
:------- | :------------------ | :------------------ | --------------:
Door 001 | 23/03/2017 16:50:42 | 23/03/2017 16:54:15 |             213
Door 001 | 23/03/2017 18:22:37 | 23/03/2017 18:28:01 |             324
Door 001 | 23/03/2017 18:29:40 | 23/03/2017 18:34:53 |             313
Door 001 | 23/03/2017 18:42:56 | 23/03/2017 18:46:32 |             216

dbfiddle here


Is there any way to increase the mysql reserved pool for super users beyond 1? http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

States that there is 1 connection beyond your maximum number of connections for users with the super privilege.

My question is can you increase that number?  Eventually I want to put limits in for all users that leave additional connections open for administrative jobs, but I'm not in a position to do that right now.

I want to increase that number to 10 so I can have replication jobs and monitoring jobs not affected by application connections.

Is there some sort of sane upper limit any one user should never exceed?  Maybe your busiest application  has at most 20 open connections during peak normal operation.  Let's assume you have 20 non root users.

set max_user_connections = 30 (to give a bit of a safe buffer)

set max_connections = max_user_connections * # users + extra buffer,
   or 30 * 20 + 50 = 650 max connections.

This way your global max connections is safe from every getting reached and there should always be room for you to get in even if one particularly application or user is getting maxed out.


Default schema is ignored on SELECT According to my understanding, a default schema should apply when
issuing SQL statements on tables using unqualified table names.

My problem is that the following statement fails, because the table name
cannot be resolved.

-- Table name : [mydb].[myschema].[mytable]
USE mydb
SELECT * FROM mytable

Msg 208, Level 16, State 1, Line 2
Invalid object name 'mytable'.

I have a database user defined based on a Windows login user mydomain\myuser with the following properties:

The login user is not a member of the sysadmin group, but only of serveradmin and public. According to the documentation, these memberships should not be a problem.
The mapped database user (also called mydomain\myuser) has the default schema myschema assigned.
The database user is member of db_owner.

When connecting with MS SQL Management Studio as mydomain\myuser, calling SCHEMA_NAME() returns dbo. This indicates that for some reason, the default schema myschema is ignored. This is consistent with the failed SELECT statement.

But why is the default schema ignored?

This article gave me the hint:

  It is important note that if the user is authenticated by SQL Server as a member of a group in the Windows operating system, no default schema will be associated with the user.

My user myuser was a member of the Windows Administrators group and
there was an SQL Server login defined for the Administrators group.

After removing the user from this group, everything works fine.

I've read this documentation before, but skipped the crucial paragraph.


Best practices for SQL Server block size while formatting disks for data and log files What is the current best practice or general guidance on formatting the filesystem for SQL server database files.
Currently we have SQL Server file system, NTFS formatted with 64KB block size based on these Microsoft guidelines.
I am not sure how much is this applicable right now and how do we find the best fit as with the latest Windows  servers we can go with formatting up to 1 or 2 MB.
Therefore I am looking for advice how to format the disk for modern SQL Server say 2017 and up, with the latest Windows, as the MSDN documentation seems out of update. Would a different block size, like 512 KB or 128 KB be better to format the drives where mdf, ndf and ldf files reside.

Nothing has changed since that article. You should still use 64KB as your unit size.
That is how the default storage in SQL Server VMs in Azure are configured:


Can Oracle have too much RAM? Our database is slow in some very important use cases. I have traced it to a problem with some queries performing full text searches and single row accesses on a lot of records (db file sequential read) afterwards. Once the data is in memory, those queries become very fast.

The solution from us developers is now to put the hottest table into memory.

We currently have a SGA_MAX of 20 GB, SGA_TARGET is 14GB. ADDM and AWR recommend 25GB of SGA space.

Our infrastructure team will now upgrade our database to 40GB, however, the database admin sais that that much ram can kill oracle performance.

Is that true? I cannot possibly imagine that 20GB more RAM will make the database go slower.

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Yes and No. With improper configuration, too much RAM can cause performance degradation.

For example on Linux platform, if HugePages is not configured, with large SGA and many database sessions, the pagetable will hold a significant amount of memory. Once at one our clients, the database server had 512 GB memory installed. They had about 4000-5000 database sessions with a 150 GB SGA, and this resulted in a 360GB pagetable size - so the server ran out of memory, and this caused hangs and reboots. After enabling HugePages, the size of the pagetable decreased to 1,5 GB (from 360 GB!!), saving and allowing to use more than 350 GB extra memory.

Another case, where the customer used Solaris x86-64 11.1, is the reverse of the above case. The database used 1 GB pages with 300 GB SGA, but because of a Solaris bug, 99.8% of the time spent handling large pages at OS level was overhead, caused the CPU to run in kernel mode, causing huge load on the server (300+ with 32 cores). The database was running fine with 24 GB SGA, but could not handle the load with 300 GB SGA. Disabling large pages in Solaris resolved this issue (there is a Solaris patch for it, but the local admins did not install it...).

These are however not Oracle limitations, but configuration issues. Oracle can handle large amount of memory. One of our customers has been using 500 GB+ SGA for a long time without any problems, and they are planning to increase it further.


MySQL: unique combination of two columns This is my friends table

userid(int) | friendid(int) | confirmed(tiny int)
--------------------
1 | 2 | 1
2 | 1 | 1
1 | 3 | 1
3 | 1 | 1
1 | 5 | 0
2 | 4 | 1
4 | 2 | 1
4 | 1 | 1
1 | 4 | 1

I have about 200k rows like this.

I want to delete duplicates. For example if userid=1 and friendid=2 then I don't want the row userid=2, friendid=1.

I want unique combinations across the two columns. This means if (1, 2) is there then (2, 1) should not be there and should be deleted.

Then I can make a friend list query like this

select * from friends where (userid=1 or friendid=1) and confirmed=1

My question is how to delete these duplicate rows?

Please also help me understand how to find the mutual friends of 2 users (userid of them may reside in either userid column or friendid in friends table).

You can use a non-standard MySQL construction and delete from a self-join:

delete f1.*
from friends f1
join friends f2
    on f1.userid = f2.friendid
   and f1.friendid = f2.userid
   and f1.userid > f2.userid;

when you have cleaned up the table, next step is to prevent this from happening again. The obvious choice would be a CHECK constraint guaranteeing that userid < friendid:

alter table friends add constraint ... check(userid < friendid)

and a unique constraint on these two columns:

alter table friends add constraint ... unique (userid, friendid);

Unfortunately CHECK constraint is not (AFAIK) supported by MySQL. You can however mimic this with generated columns:

alter table friends
    add leastid int as (least(userid, friendid))
    persistent after friendid;
alter table friends
    add greatestid int as (greatest(userid, friendid))
    persistent after friendid;

Now we can add a unique constraint on the generated columns:

alter table friends add constraint ak1_friends
    unique (leastid, greatestid);

insert into friends (userid, friendid) values (1,2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'ak1_friends'
insert into friends (userid, friendid) values (2,1);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'ak1_friends'

To find common friends among for example 3 and 5 you can use a query like:

select f1.userid, f1.friendid, f2.userid, f2.friendid
from friends f1
join friends f2
    on f1.userid in (f2.userid, f2.friendid)
    or f1.friendid in (f2.userid, f2.friendid)
where 5 in (f1.userid, f1.friendid)
  and 3 in (f2.userid, f2.friendid);


Table data transfer using SSIS and triggers on destination table I'm a novoice in db operations. SQL Server 2008 R2 is the database I'm using.

I need to move selected rows from a staging database table to production database table. I'm using SSIS package to do that. I've few triggers on destination table. I'm now confused whether these triggers will be fired or not when I transfer the data from source table to destination table.

If these triggers won't be fired while transferring data from source to destination table, is there anything can be done to fire these triggers during this data transfer?

Giving me some information on the above will be greatly appreciated.

Update:
Selected rows will be added to the destination table. Triggers will update data in some other table in the destination server based on newly inserted data. For example:

    CREATE TRIGGER [MyTableChange]
       ON  [dbo].[DestinationTable]
       AFTER INSERT,UPDATE
    AS
    BEGIN

    declare @newid as int
    select @newid=newid  FROM INSERTED

     Declare @total as int
     declare @lookatme as int
    select @total=total, @lookatme from DestinationTable where newid=@newid
    update anothertable set total=total+@total where lookwhere=@lookatme
    END

GO

Above trigger is kind of example not exactly same logic which I've in original trigger. But it gives info of what exactly I'm trying to do. I'll be accessing newly inserted row details to update another table value. Please do not take this as correct trigger in terms of syntax and way it is written. I just tried to give essence of original trigger.

If these triggers won't be fired while transferring data from source to destination table, is there anything can be done to fire these triggers during this data transfer?

In the OLEDB connection manager there are various options to load data. If

table or view using fast-load options is selected, then you have to explicitly specify FIRE_TRIGGERS in the advance editor as shown below :

If table or view data access mode is chosen then your triggers should fire normally because the insert is done row-by-row.


Cannot create directory with sysoper My user has the two roles sysdba and sysoper. When I connect as sysdba I can sucessfully perform in sql plus:

@> connect myuser/mypassword@myservice as sysdba;
Connected.
SYS@service> CREATE OR REPLACE DIRECTORY DP_TMP_5714 AS '/path/to/dir';

Directory created.

However, when I open session as sysoper It performs as follows:

@> connect myuser/mypassword@myservice as sysoper;
Connected.
SYS@service> CREATE OR REPLACE DIRECTORY DP_TMP_5714 AS '/path/to/dir';
CREATE OR REPLACE DIRECTORY DP_TMP_5714 AS '/path/to/dir';
*
ERROR at line 1:
ORA-01031: insufficient privileges

I already run the grant command:

GRANT CREATE ANY DIRECTORY TO myuser

Great! Your database is working precisely as it should!

SYSDBA has the power to do just about anything to the database, including creating objects, dropping objects, etc, blah.

SYSOPER has a reduced set of privileges which allow such users to perform basic operational tasks but not to look at user data.

Share and enjoy.


First metric in each time "bucket" for multiple IDs I have the following table in PostgreSQL 12:
CREATE TABLE vehicle_fuel (
   vehicle_id     int NOT NULL
 , submitted_at   timestamp NOT NULL
 , fuel           float NOT NULL);

Currently there are approximately 1000 vehicle IDs each with an entry every 15 minutes or so, just over 100 million rows in total.
I'd like to be able to plot the fuel usage on a line chart for multiple chosen vehicles for any chosen time interval. I'd like to plot only n=200 points spread relatively evenly over the time interval for each vessel.
My current solution uses generate_series() to generate evenly spread "buckets" and then choosing the first timestamp within the "bucket" for each vehicle. E.g. for 10 vehicles, 200 points over the past 6 months
SELECT vehicle_id, submitted_at, fuel
FROM (VALUES (1), (3), (4), (34), (44), (56), (76), (79), (81), (83)) vehicle_ids(v)
         CROSS JOIN (SELECT generate_series('2020-05-17T00:00:00'::timestamp, '2020-11-17T00:00:00'::timestamp,
                                            '79488 seconds') AS bucket
                     LIMIT 200) AS buckets
         CROSS JOIN LATERAL ( SELECT vehicle_id,
                                     submitted_at,
                                     fuel
                              FROM vehicle_fuel
                              WHERE vehicle_id = v
                                AND submitted_at <= buckets.bucket
                              ORDER BY submitted_at DESC LIMIT 1) data
ORDER BY vehicle_id, submitted_at DESC;

However, this does not scale well with the number of vehicles requested, ~ 2300ms for the above query, significantly longer when I add more vehicles.
Is there any way I can make this faster? dbfiddle
I'm also using TimescaleDB if there is anything that can be utilised

Thanks to k_rus for suggesting to use the TimescaleDB function time_bucket(). The below query is significantly faster:
SELECT vehicle_id, submitted_at, fuel
FROM (VALUES (1), (3), (4), (34), (44), (56), (76), (79), (81), (83)) vehicle_ids(v)
         CROSS JOIN LATERAL (SELECT DISTINCT ON (vehicle_id, bucket) vehicle_id,
                                                                     time_bucket('79488 seconds',
                                                                                '2020-11-17T00:00:00'::timestamp) AS bucket,
                                                                     submitted_at,
                                                                     fuel

                             FROM vehicle_fuel
                             WHERE vehicle_id = v
                               AND submitted_at BETWEEN '2020-05-17T00:00:00'::timestamp AND '2020-11-17T00:00:00'::timestamp
                             ORDER BY vehicle_id, bucket DESC, submitted_at DESC
                             LIMIT 200) d
ORDER BY vehicle_id, submitted_at DESC;


SQL Server Agent - Running SSIS Package with Proxy I have followed the below article to create credentials/proxy to run a SSIS job from SQL Server Agent.

http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

I get this error:
Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 6).  The step failed.

Does the credential account need to be a Domain user account? I have tried using sa and another SQL Login but still no success.

I know that the post is old, but to make this work be sure that the user you set as Run As in the Job, has access to the proxy and is in the list of the proxy Principals. If the user is systemadmin in sql it will not get saved in the list and you will get the error.


Batch commit did not work I run the following code to purge old data. Exact code was running as is  as SQL Agent job step pointing to a user database.   Expecting to commit with a batch of 10,000 rows.  2 days ago it did not do that, after terminating the session (reason of termination is not related to this question) rolled back all the delete and not just the last batch.

Anybody can point me to a reason why?

SET NOCOUNT ON;

DECLARE @rowCount INT;

SET @rowCount= 1;

WHILE @rowCount > 0
BEGIN
  BEGIN TRANSACTION;

  DELETE TOP (10000)
    dbo.TableName
    WHERE RecordedAt<DATEADD(month, -3, GETDATE() )
   SET @rowCount = @@ROWCOUNT;

  COMMIT TRANSACTION;

END

Difficult to diagnose definitively based on the information provided but the likely cause is the batch was inadvertently invoked with a transaction already open. I suggest one or more the following to prevent the problem going forward:

Add SET XACT_ABORT ON; to the batch to help ensure explicit
transactions are automatically rolled back.
Remove the superfluous BEGIN TRAN and COMMIT since the automatic
single-statement transaction will fully rollback a delete batch if an error
occurs.
Add error handling


Approaches to prevent database slowdown when another process is hitting it hard I'm a developer not a dba, but this task was given to me...
One of our databases is hit hard by a process which reads and modifies hundreds of thousands records onece or twice a day, during the time this process is running our other services who rely on this database get very slow.
What are some "common" approaches to solve this kind of problems?
Things I have thought that could work:

Using "NOLOCK" in the read queries
Using a cache service in front of the database, like Redis
Using database redundancy

My questions are: Could any of these 3 options works? and if so, what kind of results can I expect. What other options do I have?

As others have suggested, the problem you've outlined is very open-ended.  However, I can offer one general solution which you may not have considered: break up the intensive operation into smaller batches.  This will keep locks brief, reducing the risk of deadlocks, may reduce the amount of memory and temp space used, and may give the server enough headroom to cope while you do root cause analysis.
For example, if your overnight operation deletes a large number of records, you can instead run DELETE TOP (10000) FROM X WHERE [blah blah] in a loop while @@NUMRECORDS > 0.  You could throttle this by inserting a delay between executions.  Note that the total CPU cycles used will probably be greater with this approach; possibly dramatically greater, depending on the details of the queries involved.  If your server is short on memory, this approach could lead to thrashing your buffer pool (the cache recently-read or -modified records, held in memory when possible).  Test carefully before promoting any change to production, and be ready to halt and revert if it negatively impacts performance.


Understanding One to One Relationships As far as I understand, a one-to-one relationship between two tables means that each row in one table matches exactly one row in the other.

There a a number of reasons why this is useful, such as virtually adding columns to a table without actually altering it.

The above description would suggest that the relationship is symmetrical: a row in either table is matched by a row in the other.

This is readily done if the primary key is also a foreign key to the other:

CREATE TABLE stuff (
    id INT PRIMARY KEY REFERENCES more(id),
    data VARCHAR(255)
);

CREATE TABLE more (
    id INT PRIMARY KEY REFERENCES stuff(id),
    data VARCHAR(255)
);

I can see a logistic problem here: how can you add a row to one table when there isn’t a row in the other table to match?

A more relaxed version is a one-to-zero-or-one relationship, which I prefer to call a one-to-maybe relationship. This is easily implemented if one table references the other, but not the other way round:

CREATE TABLE stuff (
    id INT PRIMARY KEY, --  does not reference the more table
    data VARCHAR(255)
);

The relationship is still between primary keys, but only in one direction. This is logistically easier, and can be used to implement optional columns without getting into a fight about the use of NULLs.

The question is:

Does a one-to-one relationship really refer to the first case, where two tables match in both directions?
How would you go about adding a row in that case?

1 to 1 (or possibly 0) relationships are not cross referenced, they are implemented as your 2nd example, with one of both entities related to the other one (and not the other way around).

Linking both ways would be redundant for one of the relationships, as you just need 1 to know the nature of the link. Also, in databases that don't support deferrables foreign keys or multiple inserts in one statement (thanks to  @ypercubeᵀᴹ for this note), you won't be able to insert the rows without disabling the foreign key first.

If the existance of stuff depends on more and more depends on stuff then there is a problem of the chicken and the egg. Which one really represents the entity you are trying to store? Do you really need 2 different tables for them? Does your entity exist only when both records are created?

1 to 1 relationships always raise an eyebrow when designing. Maybe if you had a particular case scenario to share we could delve more in detail. The only few exceptions I've implemented 1 to 1 relationships are when we inherited previous databases with tables that had 100+ columns, and we split them up for performance and storage issues, but not for pure designing reasons.


import mysql schema data folder instead of from a dump current state

I have prod and dev mySQL databases.

To create the dev database, I used the various schema backups generated from production and imported them like so:

mysql -u my_user -p target_db < target_db_backup.sql

This was an extremely time-consuming process (over 24 hours for some schemas). I'd like to speed it up.

ideal solution

I'd prefer to just be able to:

stop mysql
rsync production schema data folder (/var/lib/mysql/my_schema/) to the same location on dev.
start mysql
everything just works!

the issue

When I attempt to do something simple like select data, the following errors are generated:

2016-01-11 21:47:14 7f293d454780  InnoDB: Error: table 'my_schema/my_table'
InnoDB: in InnoDB data dictionary has tablespace id 1450,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name my_schema/my_table and id 2451, though. Have
InnoDB: you deleted or moved .ibd files?

So I assume there's some innoDB finagling that needs to happen to get this to work.

Is it possible to just mess with innoDB settings in mysql to allow the data files in the schema to be read properly without having to import data from a dump?

The file ibdata1 has "tablespace ids" in it that need to correspond to the .ibd files containing the data, etc.  Since these ids are generated by the instance, when you reloaded the dump, they may be different than the ones on the production machine.  Hence, the rsync could be copying wrong ids.

Moral of the story:  Don't mix source dumps (mysqdump, xtrabackup, etc) with file backups (rsync, etc).

Your backup is hosed; you will need to start over.

Some backup techniques (starting with least-invasive):

Zero downtime (after set up):  Master-Slave; the Slave is continually updated as a viable backup.

Near zero downtime:  LVM.  After setting up a snapshot area, you (1) stop mysqld, (2) "snapshot" the filesystem, (3) restart mysqld, and (4) rsync the snapshot to wherever.  Step 4 is the only slow part; it does not impact the running of the db other than chewing up lots of I/O.

incremental backup via binlog

Xtrabackup, with some of the non-trivial features.


Query to calculate start and end dates from a list of only start dates I have data in the following form that needs to be combined such that I create a single row from two rows of the input data where the second column is the value of the next row less one day.

I've struggled trying to come up with a way to do this, but not gotten very far. I hope this illustration explains the problem:

Table structure

CREATE TABLE Process(
    ProcessOrder int NOT NULL IDENTITY (1, 1),
    ProcessDate date NOT NULL
);

Query

SELECT ProcessOrder, ProcessDate From Process ORDER BY ProcessOrder;

Data

ProcessOrder  ProcessDate
------------  -----------
208           2016-01-04
209           2016-01-11
210           2016-01-18
211           2016-01-25
212           2016-02-01
213           2016-02-08

I need to get the above data into the following shape (note that ProcessEnd is one day less than ProcessBegin from the subsequent record):

ProcessOrder  ProcessBegin  ProcessEnd
------------  ------------  ----------
208           2016-01-04    2016-01-10
209           2016-01-11    2016-01-17
210           2016-01-18    2016-01-24
211           2016-01-25    2016-01-31
212           2016-02-01    2016-02-07
213           2016-02-08    *NULL*

EDIT

I am guaranteed that values in the ProcessOrder column are consecutive with no missing values.

Since you're on 2008, you don't have the LEAD function yet (I believe), so you can do it yourself with a CTE:

WITH RankedProcess AS
(
  SELECT ProcessOrder, ProcessDate, ROW_NUMBER() OVER(ORDER BY ProcessDate) AS Seq
  FROM Process
)
SELECT P1.ProcessOrder, P1.ProcessDate AS ProcessBegin, DATEADD(day, -1, P2.ProcessDate) AS ProcessEnd
FROM RankedProcess AS P1
  LEFT OUTER JOIN RankedProcess AS P2
    ON P1.Seq = P2.Seq - 1

In 2012 and later, you can just use LEAD:

SELECT ProcessOrder, ProcessDate AS ProcessBegin, LEAD(DATEADD(day, -1, ProcessDate)) OVER(ORDER BY ProcessDate) AS ProcessEnd
FROM Process

Given the update that ProcessOrder is guaranteed to be consecutive with no missing values (and I'm assuming they are in the same order as the dates), you can get away without the CTE and just use the ProcessOrder column. However, the solution with the CTE is more general, which I think is still useful since I am generally skeptical of data requirements that aren't enforced by the database.


What are practical hurdles in making protein interaction database? Following are some protein interaction databases:

http://string-db.org/

http://dip.doe-mbi.ucla.edu/dip/Main.cgi

As a newbie what are the practical aspects of database that I must understand before attempting any such project on a small scale with a data-set of about 20,000 proteins? What difficulties I must expect during design and implementation of such a project as an administrator?

I started writing an answer earlier on certain aspects of database design and implementation, which I abandoned because I realized that these would (or should) not be the very first steps on the way of developing a database (or, more broadly, a software) for your needs.  This way, this may not be the answer you are waiting for.

As a newbie a few years ago, I was also tempted to make something big and interesting.  (To be honest, I had only a part of the competence needed for that particular project.  I hoped that once I would be ready with the database part (which I knew how to do), everybody would be so convinced that it would going to be superb, that they would have the necessary patience for me making mistakes and being (very) slow.)  As I started to do my thing, I realized that

the project was much more complicated than it looked first (partly because of lacking specification)
I was not that good than I'd thought before (especially speedwise)
nobody was patient no matter what I'd achieved so far (understandably, they needed that piece of software very much).

Now, after this coming-out, back to your project :)

To decide whether you have to develop something new, you have to consider a few things.

Are your data so special that only a new software would fit?
There could be (open source) software which you can use after minor modifications.  Going this way you probably can have more help (preferably from those who wrote that software) and finish faster.  And, in the end, if your additions are working and useful, you can publish it.  It will earn reputation for you for sure.  (Not to mention that your citation index will be better :)

Are you in really big need for it?
Since the development will take considerable time (and probably money), you have to assess the costs and the gains.  On the costs side, there will not be the database alone: you must be able to use your database (feed it with data and get results from it).  Even if kept simple, it still can double the efforts needed.  Overall, if the gains aren't significantly bigger than the costs, I would not start with it.

Do you have the expertise required to reach your goal?
Hopefully yes, but if not, than your system will be inefficient, flawed or completely useless.  As you progress with your work, you will gain experience of course, but debugging and rewriting badly written code can be much harder than writing it once again.

(I may be too careful here, but I hate half-written software.  And, if your main goal is to gain experience, and you have the necessary resources (including the luxury of going down all the dead-ends), than start with it.  And, if your answer is three yeses, than you still should read a good book on database design and development - it's far broader a field than a short answer could cover.)


Cannot find either column "dba" or the user-defined function or aggregate "schema.functionName", or the name is ambiguous I created a function and granted select on [dba].[ufn_track_dailies] to public.  The function shows up under table valued functions, but when I try to use it I get an error:

select [dba].[ufn_track_dailies]('Real Name','', '08-01-2015', '08-31-2015')

If this is for SQL Server (and maybe others as well?), then you cannot SELECT a TVF, you need to SELECT FROM a TVF ;-) And, you need to use those silly parenthesis when calling functions:

SELECT * FROM [dba].ufn_track_dailies();

If it were a scalar function, that would be:

SELECT [dba].ufn_track_dailies();


SQL Server Update Statement joining on index column Using SQL Server 2012, I have a stored procedure that updates data by doing a delete and insert similar to this:

DELETE  FROM MyTable
WHERE   EXISTS (   SELECT   1
                   FROM     StagingMyTable s
                   WHERE    MyTable.ID = s.ID
                            AND MyTable.ID2 = s.ID2)

INSERT INTO MyTable
SELECT  *
FROM    StagingMyTable

which was changed to a Update and Insert process like this:

UPDATE  mt
SET     mt.ID = s.ID
      , mt.ID2 = s.ID2
      , mt.col1 = s.col1
      , mt.col2 = s.col2  /* Rest of the columns here*/
FROM    MyTable        mt
JOIN    StagingMyTable s ON mt.ID = s.ID
        and mt.ID2 = s.ID2

INSERT INTO MyTable
SELECT  *
FROM    StagingMyTable s
WHERE   NOT EXISTS (   SELECT   1
                       FROM     MyTable
                       WHERE    MyTable.ID = s.ID
                                AND MyTable.ID2 = s.ID2)

There is a clustered index on MyTable on the ID and ID2 columns which is why we decided to update the stored procedure to update and insert.  What I want to know is whether or not I should leave out the two key columns on the update statement even though those values aren't being changed i.e.

UPDATE  mt
SET     mt.col1 = s.col1
      , mt.col2 = s.col2  /* Rest of the columns here*/
FROM    MyTable        mt
JOIN    StagingMyTable s ON mt.ID = s.ID
                         and mt.ID2 = s.ID2

If I include the key columns in the update statement, does SQL Server thinks these will be new values and delete and re-insert the rows anyway?

SQL server may choose to perform an in-place update or an insert and a delete depending on many factors and you shouldn’t worry about it.
Since the keys are not being updated I would leave them out of the SET clause to help the optimizer make the best decision.

If you are using SQL Server 2008 or later, look at the MERGE statement which can replace both operations:
https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017

HTH


Recommended Postgresql index type to be used with not equal () where condition I need to query two huge tables (more than milion records). Besides other conditions, there is also a not equal (<>) where condition on a columns pair of varchar type. It has to exclude one in a million.

Values in the column are unique, same in both tables, so I have to exclude from joining rows from two tables which have identical values in that particular column.

Is a standard b-tree type index suitable, or would some other index type be more suitable for a not equal (<>) where condition?

What about pg-strom extension? Could pg-strom (utilizing GPU) enhance speed of a not equal comparison in the where clause?

Indexes are good to reduce many rows to a few rows based on a condition in your query. Unless you have a very unequal distribution of values, a "not equals" condition will most probably not be considered for an index lookup.

So, it all depends on how selective this index would be. Unless it eliminates much more than 50% of rows (usually 90%+ is desirable) it's probably no better than a sequential scan.

See Use The Index, Luke


Unable to access OEM 11g after upgrading to Firefox ver 37.0.1 I upgraded my browser (Firefox) to the latest version 37.0.1. Thereafter, OEM 11g is not accessible. Here are the errors:

**Secure Connection Failed**
The connection to bdporagrid.bdpinternational.com:7799 was interrupted while the page was loading.
The page you are trying to view cannot be shown because the authenticity of the received data could not be verified.
    Please contact the website owners to inform them of this problem.

I did some research and tried many workaround but still unable to get it loaded. I have no problem with Chrome or IE loading the OEM11g webpage.

The URL is:https://xxxxx.yyyy.com:7799/em/console/targets

Workaround attempted:

Deleted the cert8.db in Firefox profile and then relaunch Firefox. --Failed to resolve
Delete or Distrust the OEM11g server certificate under Firefox --> Options --> Certificates tab --> View Certificates.
Relaunch firefox--Failed to resolve
I exported the OEM11g server certificate using IE. Then, I imported it into Firefox. Same error. --Failed to resolve.

Not sure if anyone has a workaround or solution. I am sure ver 37.0.1 has changed the https behavior.

Quick workaround: Set Firefox's about:config setting security.tls.version.fallback-limit to '0'.

The correct way to fix this is to get the site to updated.

Source: http://forums.mozillazine.org/viewtopic.php?f=37&t=2925119&start=15


SQL Server: Mixed mode security: Can a non AD user (a local user, remote client) have a trusted connection to SQL? I have used SQL Server trusted connections for years. It works well when the client app is a domain user.

Imagine this three server config:

dbserver
webserver
domain controller

I now have a situation of a web app that can no longer run on as a domain user (long story). This is a problem because this app stores its db connection string in the registry. If I use SQL auth, the SQL password is in clear.

Is it possible to ?

IOW: Is it possible to add webserver\localuser to SQL server as a trusted user, or can SQL server only trust domain users?

I now have a situation of a web app that can no longer run on as a domain user (long story).

The web server can't run under the account or the application context can't run under the account? For example, the web server is sitting in a DMZ?

This is a problem because this app stores its db connection string in the registry. If I use SQL auth, the SQL password is in clear.

This is a problem with how the password is being stored, not a problem with SQL Server TBH. However, you can overcome this by encrypting the password at rest and decrypting on demand then freeing the memory - for example.
The password is not sent over the wire in plain text, this encryption when connecting to SQL Server through SQL Authentication is completed for you automatically.

Is it possible to ?

It is not possible to connect to SQL Server with a username/password combo and setting the trusted connection setting. This is because trusted connection doesn't mean "encrypted and secure connection" it actually means "use windows tokens instead of a username and password". It doesn't mean the connection isn't trusted it just means use my token which will either end up being protected through ntlm or Kerberos.

Is it possible to add webserver\localuser to SQL server as a trusted user, or can SQL server only trust domain users?

There is no such thing as a "trusted user or login". Local windows accounts are only known to the local server, thus you could not use webserver\localuser unless the database server was also on the webserver. Since it is not, this is not possible. Since the local user is not in active directory, this will also not work. Thus, no, you cannot use that method.
Your best possible scenario for this is to use encryption of the connection string at rest and potentially even SSL connections (though that might be overboard and doesn't make the password kept in the registry and more or less secure, it just encrypts the traffic on the line).


Do I have duplicate key indexes? CREATE TABLE relations (

  object_id BIGINT(20) NOT NULL PRIMARY KEY,
  object_term BIGINT(20) NOT NULL,

  UNIQUE KEY link(object_id, object_term),

  CONSTRAINT fk_term FOREIGN KEY(object_term)
    REFERENCES terms(id) ON DELETE CASCADE ON UPDATE RESTRICT,

  CONSTRAINT fk_object FOREIGN KEY(object_id)
    REFERENCES objects(id) ON DELETE CASCADE ON UPDATE RESTRICT

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I have a feeling that I'm indexing object_id3 times, and object_term two times. Am I right?

If yes, how can I fix that and still keep the constraints and force (object_id, object_term) to be unique ?

ok, second try:

CREATE TABLE relations (

  object_id BIGINT(20) NOT NULL,
  object_term BIGINT(20) NOT NULL,

  PRIMARY KEY link(object_id, object_term),

  CONSTRAINT fk_term FOREIGN KEY(object_term)
    REFERENCES terms(id) ON DELETE CASCADE ON UPDATE RESTRICT,

  CONSTRAINT fk_object FOREIGN KEY(object_id)
    REFERENCES objects(id) ON DELETE CASCADE ON UPDATE RESTRICT

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

is this better?

object_id is indexed three times

PRIMARY KEY
UNIQUE KEY link
FOREIGN KEY fk_object

object_term is actually indexed once

fk_term
In UNIQUE KEY link, object_term is not the lead column.

UPDATE 2011-12-09 19:42 EDT
In the First try:

object_id is unique (PRIMARY KEY)
(object_id,object_term) is unique (this means object_id can only be associated with one object_term at any given moment)

In the Second try:

object_id is not unique
(object_id,object_term) is unique (this means object_id can only be associated with more than object_term)

UPDATE 2011-12-09 19:57 EDT
If you are concerned about diskspace taken by indexes, you have a decision to make
OPTION 1 : Diskspace is no object
This can only be beneficial if your queries effectively use covering indexes and you are willing to live with (and provide for) large diskspace.
OPTION 2 : Diskspace is a precious commodity
It's time to do some forecasting. Both object_id and object_term are BIGINT. You can cut space down some as follows:
SELECT * FROM relations PROCEDURE ANALYSE();

This will not dump all the rows. It will sample the data and come back with a recommedation for the best column definitions given the data.
Why is that important?

TINYINT UNSIGNED is one byte (Range 0 to 255)
SMALLINT UNSIGNED is two bytes (Range 0 to 65535)
MEDIUMINT UNSIGNED is three bytes (Range 0 to 16777215)
INT UNSIGNED is four bytes (Range 0 to 4294967295)
BIGINT UNSIGNED is five bytes (Range 0 to 2^40 - 1)

If you know the number will not surpass certain values, you can change the column definitions to accommodate less diskspace. At the very least, let PROCEDURE ANALYSE() suggest it for you.
OPTION 3 : Diskspace can be a bottleneck
The more house guests, the more housekeeping to accommodate. If a host can take care of 20 house guests, keep them all 20 happy, and the host is happy as well, the host can always care 20 house guests over and over again. Add one more guest, and the host's quality of services goes down hill. The same goes with indexes. If your queries are fast enough to warrant keeping indexes around, you need to select which indexes are really necessary, and eliminate indexes that are redundant (unwanted house guests). The host must make sure the each house guest's request (query) can be satisfied (done with covering indexes)
Nice Links on Covering Indexes

http://peter-zaitsev.livejournal.com/6949.html
http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
http://ronaldbradford.com/blog/tag/covering-index/


SQL Server 2008R2 minimum RAID/Disk config I want to take advantage of the great price/performance offered by the new Intel E5-2620 (v2) machines being offered by some dedicated server webhosts.

Unfortunately most of these E5-2620v2 dedicated server offerings are a 1CU and limited to 4 drive bays. My production SQL 2008 R2 (2GB) database is supporting an auction web application with a very high frequency of small updates by many users.

I currently have an 8 drive configuration on my SQL Server with:
 - RAID 1 array (OS & Program files)
 - RAID 1 array (SQL log files)
 - RAID10 (SQL Data & TempDB)

My question is would I creating a disk performance issue for my OLTP SQL server  if I went with:
 1) A single 4 drive RAID 10 array (600 GB SAS drives with OS and all SQL Files). Would I be better off going with a much slower & more expensive machine that supports 8 drives?

2) I found one that offers 2 internal SSDs plus 4 hostswap drives, what would be the recommended disk layout. Is it still recommend to have SQL Logs on separate disk with high frequency of small db updates?
    2 Drive SDD RAID 1: OS & Temp files & SQL Log file
    4 Drive RAID 10: SQL DATA & System DB files, TempDB

3) Is there still a significant performance difference between SATA IIIs and SAS 15K disks as some wehosts only offer SATAs?

Many thanks in advance for any advice.
arf

For a 4 bay configuration with a high insert/update workload I would go with one of these:

1) Spindle only

One RAID1 for the log. Back this up with a a few hundred MB of write cache
One RAID1 for the data files and tempdb

Rationale: you will mostly be writing to the log. Latency of log is king for OLTP and it doesn't get much better than a RAID1 with a write cache. You can use SATA - which should give you at least 80MB/sec sequential writes at latency less than 1ms.

2) SSD plus spindle

One RAID1 for log with same configuration as the above.
One RAID1 for data files and tempdb

Rationale: This config will favour higher tempdb load and would be good if you are skimping on DRAM (as this will cause checkpoint activity to go up and drive read I/O)

3) Pure SSD

Put them in a RAID5 if you are not worried about wearing them out.
If you are using consumer grade SSD - put them in a RAID10.

Rationale: RAID5 with 4 x SSD is fast enough for nearly all purposes.

Everything goes on same LUN

4) 8 bay, spindle only config

Put 2 disks in RAID1 for the log file.
Rest goes into a RAID10 or RAID5 (RAID10 if you are worried about tempdb)

Overall: always prefer a 2 spindle RAID1 with a write cache for logs. It's plenty for 99% of all cases (80MB/sec for SATA and up to 120MB/sec for SAS).


How to retrieve stored procedures from raw data I have all the raw data from a database that I can't access through the mysql program anymore. I was able to get the tables by copying the .frm files over to the new installation of mysql, but I don't know how to get the stored procedures, which are what I really need out of the database.

Is there a way to retrieve them from the raw data files? (no, I did not do a dump of the stored procedures; I did not realize at the time you specify options to do that, and so I only have a dump of the tables).

Stored Procedures are physically stored in the mysql.proc table as a MyISAM table

Simply copy

/var/lib/mysql/mysql/proc.frm
/var/lib/mysql/mysql/proc.MYD
/var/lib/mysql/mysql/proc.MYI

If the new installation is the same major version of mysql, copying should do it for you

It would be safer to go back to the old setup, start mysql, and dump the stored procedures.

See my old post Dump only the Stored Procedures in MySQL


Install mysql-shell from apt repository I want to install and configure a high availability cluster for Mysql 8. To automate that, I want to use use apt repository (via ansible).
My servers are debian jessy (8) distribution.

I succesfully installed mysql server with commands :

> sudo apt-key adv --keyserver pgp.mit.edu --recv-keys 5072E1F5
> echo "deb http://repo.mysql.com/apt/debian jessie mysql-8.0" | \ sudo tee /etc/apt/sources.list.d/mysql80.list
> sudo apt update
> sudo apt install mysql-server

It works fine. Now I want to install Mysql shell to manage a InnoDB cluster. It seems that it is available via apt repository as mentioned in this page :
https://dev.mysql.com/downloads/repo/apt/
And installation is explained here :
https://dev.mysql.com/doc/refman/8.0/en/installing-mysql-shell-linux-quick.html

But when I execute a install via reposiory, I get an error :

> sudo apt-get install mysql-shell

with the message :

  Reading package lists... Done Building dependency tree Reading state
  information... Done E: Unable to locate package mysql-shell

I have same error with server running on debian stretch (9).

I solved the problem by adding the apt repository mysql-tools :
deb http://repo.mysql.com/apt/debian/ stretch mysql-tools

It works for Debian 9 (stretch). Not tested for Debian 8 (jessie).


name of subquery not accessible by where clause SELECT
  NAME,
  (SELECT COUNT(1) FROM MOVIERENTED WHERE MOVIERENTED.MOVIEID=MOVIE.MOVIEID) AS RENTCOUNT
FROM MOVIE
ORDER BY RENTCOUNT;

Some movies in this database have never been rented, how can I select only the movies that have been rented outside of the subquery?

I have tried adding WHERE RENTCOUNT > 0 after the FROM MOVIE statement.

  ORA-00904: "RENTCOUNT": invalid identifier

Simiply use an Inner Join (your current query using a Scalar Subquery is similar to an Outer Join):

SELECT
  MOVIE.NAME,
  COUNT(*) AS RENTCOUNT
FROM MOVIE
JOIN MOVIERENTED
ON MOVIERENTED.MOVIEID=MOVIE.MOVIEID
GROUP BY MOVIE.NAME, MOVIE.MOVIEID
ORDER BY RENTCOUNT;


Error 4108: Windowed functions can only appear in the SELECT or ORDER BY clauses The error in the title is produced when add the second condition in my where clause. I understand the error, have tried using subqueries as seen in other posts to fix this but no luck. Does anyone know how I can filter row_ID = 1 successfully?

Here is my query:

SELECT
    a.database_name ,
    a.collection_time ,
    ROW_NUMBER() OVER (
        PARTITION BY
            CONVERT(DATE, SWITCHOFFSET(
                CAST (a.collection_time AS DATETIMEOFFSET), '+00:00'))
        ORDER BY a.collection_time DESC) AS row_ID
FROM    MDWDB.snapshots.disk_usage a
WHERE
    a.database_name = 'MDWDB'
    AND ROW_NUMBER() OVER (
            PARTITION BY
                CONVERT(DATE, SWITCHOFFSET(
                    CAST (a.collection_time AS DATETIMEOFFSET), '+00:00'))
            ORDER BY a.collection_time DESC) = 1

One method is with a common table expression like the example below.  Note that the preceding statement must be terminated with a semicolon.

WITH cte AS (
SELECT  a.database_name ,
        a.collection_time ,
        ROW_NUMBER() OVER ( PARTITION BY CONVERT (DATE, SWITCHOFFSET(CAST (a.collection_time AS DATETIMEOFFSET),
                                                              '+00:00')) ORDER BY a.collection_time DESC ) AS row_ID
FROM    MDWDB.snapshots.disk_usage a
WHERE   a.database_name = 'MDWDB'
    )
SELECT database_name
    ,collection_time
    ,row_ID
FROM cte
WHERE Row_ID = 1;


What's a good indexing scheme for a type-2 SCD table with a very broad natural key? I have a table which links several fairly long text fields (~100 chars) to a single text field: { A, B, C, D } => E.  The dependent value can change, so I query data daily and record A, B, C, D, EffectiveDate, E.  My design right now has a clustered PK on { A, B, C, D, EffectiveDate }, in that order.  I had thought that I could then query the most recent value easily:

SELECT
    X.A, X.B, X.C, X.D, X.E
FROM
    Tbl AS X
WHERE
    NOT EXISTS (SELECT * FROM Tbl AS X2 WHERE X.A = X2.A AND X.B = X2.B AND X.C = X2.C AND X.D = X2.D AND X2.EffectiveDate > X.EffectiveDate)

Since the self-join is on the first fields in the clustered key, I expected good performance, with a merge join.  However, it's taking about two minutes with less than a million rows in the table.  This is part of a near-real-time update, so I really need it to take 30 seconds, tops.

Is there a better indexing strategy for this scenario?  I have a couple ideas which I have not tried, because of some combination of (A) I'm lazy and (B) I'd rather get the right answer from the wise people on Stack Exchange, so I'll know better in the future.  I realize that the "best" solution will depend on exactly the data I have, but I suspect that there's a good general solution I'm missing, I just need to adjust my clustering somehow.

I could replace the wide PK with a synthetic integer key but keep the wide key for clustering, but my understanding is that this would only reduce the weight of any additional indices, of which this table has none.
I could should stop recording data every day, and instead only record changes.  Values for E change every week or two on average, so I'm definitely bloating the data around tenfold.
I could create a synthetic key to collapse these text values to integers, or maybe use a hash to join first and then resolve collisions with the exact values.
I could shift to a type 4 SCD and do all the heavy lifting during overnight ETL.

Look at the order of the column indexes and ensure the most selective are first

SELECT
 COUNT(DISTINCT A),
 COUNT(DISTINCT B),
 COUNT(DISTINCT C),
 COUNT(DISTINCT D)
FROM
 X

Then, read this around memory allocations for varchar columns in queries: SQL Server VARCHAR Column Width (follow Martim Smith's link)

Finally, you could use HASHBYTES to collapse the columns into a hashed computed column. You can add this to the index too as the first column so this is highly selective because it's almost unique
A surrogate key may be useful


Service broker not getting messages when enabled Due to lack of my knowledge in service broker i am finding hard time in troubleshooting Service broker in one of the databases.

Initially the Service broker somehow got disabled on this database running ssis packages using conversations to process messages. Still wandering how it got disabled and not getting enough info to find. please suggest where to look
when i enabled it using ALTER DATABASE DBSERB set NEW_BROKER WITH ROLLBACK IMMEDIATE, it enabled SB but seems to have clear of the messages i see in sys.conversation_endpoints. But the issue is i dont see those messages coming or starting. so i am back to my original issue of service broker not working. How can i troubleshoot and get this working

Adding more info while troubleshooting

select * from sys.conversation_endpoints shows 0 rows while for similar db in diff env i get almost 5-6 rows. Not sure what does that mean

To answer your questions :

Check default trace to see when it got disabled
when you altered the db with NEW_BROKER, it did more damage than you have thought of

  If you created any route with service name and service instance then it will become invalid as the route service instance value no longer reflect the destination service_broker_guid. It goes w/o saying that doing NEW_BROKER on a database that does have running conversations that span isntances (or even DBs in the same conversation), these conversations will have one half 'nuked' and the other half will be left stranded, unable to make any progress (will have to be cleaned up with END ... WITH CLEANUP or the other database will also have to be NEW_BROKER-ed).

Unfortunately, your best bet is to re-setup service broker from scratch.


sp_Blitz error: "File '' either does not exist.." SQL Server 2008 v10.0.5538.0
sp_Blitz v5.5

I'm trying to run sp_Blitz for the first time and getting this error message:

sp_Blitz @CheckUserDatabaseObjects = 0

  Warning: Null value is eliminated by an aggregate or other SET operation.
  Msg 567, Level 16, State 5, Procedure sp_Blitz, Line 3863 [Batch Start Line 0]
  File '' either does not exist or is not a recognizable trace file. Or there was an error opening the file.

What may be causing the error?

There's a weird bug that only pops up on some instances of SQL Server when the Trace File path has problems. I can't diagnose those, but we took some steps to avoid that bug which are in the latest version of sp_Blitz.

Please head over to firstresponderkit.org to grab the newest version, and file an issue at the GitHub repo if you run into anything else.

Thanks!


Can we restrict Maximum Job history rows for particular job/jobs in SQL Server 2012? I am aware that there are 2 universal settings for Job history retention rows in SQL Agent. First one is default 1000 rows and per job max history row count is 100.  Second one is deleting history older than given weeks.

Apart from these 2 options, do we have any possibility left to set up our desired max row count for a single job/few jobs? Like individual max row count for each job/specific jobs.

For example, I have a job which runs for every hour whose run time changes according to other concurrent jobs resource blocking.I want to see at which hour schedule, the Job took long time. As I have multiple jobs running on server, only 10 job history entries are available for this job. Also, I don't require more history entries for few other jobs. So, Could anybody please let me know if there is any way to setup individual max history row count for specific jobs?

Not through the UI, but since the sysjobhistory table is not a system table, you could manage this manually with a simple scheduled job that cleans up. Assuming you always want to keep fewer rows for the exceptions than the rest.

First, let's assume you have some query against sysjobs that will return the jobs you want to be more restrictive:

SELECT job_id FROM dbo.sysjobs WHERE <...>;

Next, for simplicity, let's say that for all the jobs in that list, you only want to keep the 10 most recent rows.

DECLARE @rows_to_keep int = 10;

;WITH history AS
(
  SELECT rn = ROW_NUMBER() OVER
    (
      PARTITION BY job_id
      ORDER BY run_date DESC, run_time DESC
    ),
    job_id, run_date, run_time
    FROM msdb.dbo.sysjobhistory
    WHERE job_id IN
    (
      -- query from above
      SELECT job_id FROM dbo.sysjobs WHERE <...>
    )
)
DELETE history
WHERE rn > @rows_to_keep;

It gets a little more complex depending on the answers to these comments:

  If you have two jobs where you want to keep only the last 10 rows, and one job has 2 steps and the other has 5 steps,  are you keeping 10 rows each (so 5 executions of the first job, and 2 executions of the second), or 10 total executions each (so 20 rows for the first job, and 50 rows for the second)? Well, disregarding step 0 for simplicity.

Also if you want different rules for different jobs, you can just create your own table:

CREATE TABLE dbo.CustomJobRetentions
(
  job_id uniqueidentifier,
  rows_to_keep int
);

Then you can incorporate that into your query:

;WITH history AS
(
  SELECT rn = ROW_NUMBER() OVER
    (
      PARTITION BY h.job_id
      ORDER BY h.run_date DESC, h.run_time DESC
    ),
    h.job_id, h.run_date, run_time, c.rows_to_keep
    FROM msdb.dbo.sysjobhistory AS h
    INNER JOIN msdb.dbo.CustomJobRetentions AS c
    ON c.job_id = h.job_id
)
DELETE history
WHERE rn > rows_to_keep;

Please try this out on a test system first.


ORA-01652 : unable to extend temp segment by 128 in tablespace HFDORA_TEMP I have increased temp tablespace temp02.dbf by the following command

alter tablespace HFDORA_TEMP add TEMPFILE '/orac/u01/oradata/hfd2/system/hfd2/temp02.dbf' size 2000M;

But after increasing it same error is coming. Kindly suggest.

  ORA-01652 : unable to extend temp segment by 128 in tablespace
  HFDORA_TEMP

Error message ORA-01652 means: you don't have enough free temp space (enough free memory inside temp tablespace) or local temp segment not being able to extent space even though there is space in other instances.

To troubleshoot this issue: execute the following commands:

select TEMPORARY_TABLESPACE from dba_users where USERNAME = 'myuser'

then take the result from first SQL command to help you get free space inside that tablespace

select sum(free_blocks)
from gv$sort_segment
where tablespace_name = 'result'

If you get the result 0 thats mean the temp tablespace doesn't have enough free space, otherwise you need to check segment (mostly happened for RAC) by executing the following command:

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks
from gv$sort_segment;

check total_blocks and used block and adjust the segment as needed.


Grouping on any one of multiple columns in Postgres Is it possible to create some sort of chain of grouping in Postgres? Let's say I have the following chart:

CREATE TABLE foo AS
SELECT row_number() OVER () AS id, *
FROM ( VALUES
  ( 'X', 'D', 'G', 'P' ),
  ( 'F', 'D', 'L', 'M' ),
  ( 'X', 'N', 'R', 'S' ),
  ( 'Y', 'I', 'W', NULL ),
  ( 'U', 'Z', 'E', NULL )
) AS f(a,b,c,d);

id | a | b | c | d
------------------
 1 | X | D | G | P
 2 | F | D | L | M
 3 | X | N | R | S
 4 | Y | I | W |
 5 | U | Z | E |

I want to somehow craft a GROUP BY that yields three groups:

1, 2 and 3 together

1 and 2 because of a common D in the b column
1 and 3 because of a common X in the a column

4 alone (no common values in any of the columns; nulls shouldn't match)
5 alone (no common values in any of the columns; nulls shouldn't match)

I'm currently using Postgres 9.5, but we'll upgrade eventually to 9.6, so if there's anything in there that'll help me, I'm open to hearing it.

In other words, I'm looking for something like (let's say I used array_agg(DISTINCT a), etc. to keep display simpler):

   ids    |     as     |     bs     |       cs        |      ds
-----------------------------------------------------------------------
{1, 2, 3} | {'X', 'F'} | {'D', 'N'} | {'G', 'L', 'R'} | {'P', 'M', 'S'}
{4}       | {'Y'}      | {'I'}      | {'W'}           | {NULL}
{5}       | {'U'}      | {'Z'}      | {'E'}           | {NULL}

(I'm not exactly sure how the nulls would display so don't get too hung up on that; the important point is that they shouldn't match each other.)

When I use GROUP BY CUBE (a, b, c, d), I get way more than three results...ditto GROUP BY ROLLUP and GROUP BY GROUPING SETS.

Is there an elegant way in Postgres? I can imagine how you would do it in Ruby via Active Record (loop through every record, group it with previous grouped sets that match), but I'd like to keep this in Postgres if possible.

Another recursive solution that:

first creates the adjacency list of the connected graph of ids,
then finds its transitive closure (this is the recursive part)
and then groups by (once) to find the connected components that each node belongs to
and joins to the table again and group by (again) to gather the values from all nodes of each connected component.

Initial data (copied from Jack Douglas' solution):

begin;
create schema stack;
set search_path=stack;

create table foo as
select *
from (values (1,'X','D','G','P')
           , (2,'F','D','L','M')
           , (3,'X','N','R','S')
           , (4,'Y','I','W',null)
           , (5,'U','Z','E',null) ) AS f(id,a,b,c,d);

The query:

with recursive
  al (tail, head) as                     -- adjacency list
  ( select f.id, g.id
    from foo as f join foo as g
      on (f.a = g.a or f.b = g.b or f.c = g.c or f.d = g.d)
  ),
  tc (tail, head) as                     -- transitive closure
  ( select * from al
    union distinct
    select f.tail, g.head
    from al as f join tc as g on f.head = g.tail
  ) ,
  cc (head, ids) as                      -- group once
  ( select head, array_agg(distinct tail order by tail) as ids
    from tc
    group by head
  )
select                                   -- group twice
    ids,
    array_agg(distinct a order by a) as a,
    array_agg(distinct b order by b) as b,
    array_agg(distinct c order by c) as c,
    array_agg(distinct d order by d) as d
from
  cc join foo on cc.head = foo.id
group by ids ;

┌─────────┬───────┬───────┬─────────┬─────────┐
│   ids   │   a   │   b   │    c    │    d    │
├─────────┼───────┼───────┼─────────┼─────────┤
│ {1,2,3} │ {F,X} │ {D,N} │ {G,L,R} │ {M,P,S} │
│ {4}     │ {Y}   │ {I}   │ {W}     │ {NULL}  │
│ {5}     │ {U}   │ {Z}   │ {E}     │ {NULL}  │
└─────────┴───────┴───────┴─────────┴─────────┘

Cleanup:

rollback;


Should I Avoid Repeated References to Aggregate Columns in a View? So I'm wanting to summarize the balance of the accounts in my business application.  My (simplified for our purposes) data model looks like:

--Main account table that holds the single accounts
create table account (
id int primary key,
descr varchar(50))

--This table holds the budgeted amount for the account.  This table also holds the
--'actual' adopted amount.  The difference is that budgeted is what they thought it
--should be, while adopted is what the board accepted.  They can be different
create table account_amount (
id int primary key,
acct_id int foreign key references account (id),
amount money,
type tinyint)

--Each monitary action against an account is contained here.  This is the transactional
--table
create table account_journal (
id int primary key,
acct_id int foreign key references account (id),
amount money)

I want to create a view to summarize the balance for me, so I've got something like this:

create view account_balance as
select a.id, isnull(aaBudgeted.amount,0) as budgeted, isnull(aaAlloted.amount,0) as alloted
, sum(isnull(aj.amount,0)) as journaled_activity,
, isnull(aaAlloted.amount,0) + sum(isnull(aj.amount,0)) as balance
from account a left join acct_amount aaBudgeted on a.id = aaBudgeted.acct_id
and aaBudgeted.type = 2
left join acct_amount aaAlloted on a.id = aaAlloted.acct_id
and aaAlloted.type = 1 and aaAlloted.is_current = 1
left join acct_journal aj on aj.acct_id = a.id
where a.fiscal_year = 2012
group by a.id, aaBudgeted.amount, aaAlloted.amount

My question has to with the:

isnull(aaAlloted.amount,0) + sum(isnull(aj.amount,0)) as balance

line above.  Do I want to be referencing an aggregate column twice like this?  Is SQL Server 2k (don't get me started) smart enough to NOT sum the values twice?  What are my options?

The aggregate will only be calculated once, so you're ok as you are. That said, I tend to use a subquery (as per @Aaron's comment) but purely for readability.


MySQL: How do I limit or prevent caching for specific tables? I'm currently working on a database schema for tables to hold user information on a website with integrated forums.

Right now my idea is to have all the data in two separate tables: one table with "essential" information stored with the InnoDB engine (for transactions and crash-recovery) while "nonessential" information is stored in a regular MyISAM table:

CREATE TABLE users_data (
    user_id        int unsigned NOT NULL AUTO_INCREMENT,
    username       varchar(24) UNIQUE NOT NULL,
    password       char(32) NOT NULL,
    name           varchar(64) NOT NULL DEFAULT '',
    gender         enum('M','F','U') NOT NULL DEFAULT 'U',
    birth_date     date NOT NULL,
    email          varchar(256) NOT NULL,
    member_group   enum('Banned','Member','Moderator','Admin'),
    join_date      date NOT NULL,
    avatar         varchar(40) NOT NULL DEFAULT '',
    views          int NOT NULL DEFAULT '0',
    posts          int NOT NULL DEFAULT '0',
    ip             int unsigned NOT NULL DEFAULT '0',
    last_seen      timestamp NOT NULL,
    PRIMARY KEY (user_id),
    UNIQUE KEY email (email),
    KEY     join_date_key (join_date, username),
    KEY     post_count_key (posts, username),
    KEY     last_seen (last_seen)
) ENGINE=InnoDB

CREATE TABLE users_profile (
    user_id        int unsigned NOT NULL,
    location       varchar(64),
    interests      varchar(500),
    website        varchar(128),
    msn            varchar(256),
    aim            varchar(256),
    facebook       varchar(50),
    signature      varchar(500) NOT NULL DEFAULT '',
    PRIMARY KEY (user_id)
)

With the tables separated like this, I get high concurrency on the InnoDB table, which is where information is most frequently going to be selected and inserted, while most of the arbitrary text that a user can store for their profile is in a separate MyISAM table. Also, because the MyISAM table does not require a row for every user, there is less wasted space from the allocation of space for the large varchar fields.

One of the factors that influenced this organization is that the data in users_profile is not accessed very frequently. My instinct is that it would then be helpful to disable the cache for this table so that more memory may be allocated for other purposes such as indices on MyISAM tables and other caches. If my conclusion is correct, how would I go about doing this?

InnoDB and MyISAM already use different buffer/cache structures, so if you use different engines for the two tables it is very easy to configure different cache sizes.

For InnoDB you set up innodb_buffer_pool_size which caches data and indexes.
For MyISAM you configure key_buffer_size for buffering indexes. MySQL leaves MyISAM data caching to the OS to manage. You could also create custom key buffers for each MyISAM table and decide how big the cache is.


Business Intelligence for developers I'm a developer with apps backed with SQL databases. I want to give my CEO access through BI self-serving tools such as PowerBI or Tableau.

My issue is that the affordable tools all seem to require for someone to define the model in an interactive application leaving no means of automization.

In practice, that means that every time I add some column somewhere I need to fire up some sluggish desktop application, manually add the column in the model there too and then trigger some deployment process. I'm a developer, I need to automate such things in order to not be overwhelmed with complexity.

I've looked at PowerBI's file format, but it's binary. I've looked at both theses product's REST APIs, but all they allow is upload that binary and undocumented file format.

I'm looking for

a way to create a model programmatically with these tools or
affordable alternatives that allow that.

I'm otherwise satisfied with both these tools.

I also had a look at Looker, but that already costs thousands of dollars per month.

Power BI uses Power Query (M) to extract and transform the data that is loaded into the data model.  A Power Query statement is broken into individual steps that are reevaluated each time the report dataset is refreshed.  This fluidity can be leveraged to return dynamically structured datasets.  Power Query can consume unstructured data, such as JSON or XML, and programmatically expand each element into separate columns as part of its sequence of applied steps.

Once the Power BI report is published, each time it is refreshed, it will retrieve the current unstructured base data from the source JSON column.  Then, it will parse & update the column structure in the resulting dataset automatically, without needing to manually refresh the data & republish using Power BI Desktop.

If the source data is not already in an unstructured format, you will need to prepare the raw tabular data for Power Query ingestion.  The desired columns will need to be rendered into key/pairs in a valid JSON string in the source table that you will reference in Power Query Editor.  Make sure you put these columns into a single JSON object for each row, as opposed to putting the key/pair values for every row into nested objects within a single element.  SQL Server 2016 and above have some JSON formatting tools that can assist with this transformation.

The required Power Query functions are as follows:

Table.TransformColumns to return the contents of a valid JSON string into a Json.Document object.  This applied step can be easily inserted by selecting "Transform -> JSON" from the context menu of the column header in Power Query Editor.

JSONTableName = Table.TransformColumns(sourceTableName,{{JSONStringColumnName, Json.Document}})

Table.ExpandRecordColumn to parse the JSON object key into columns.  In order to have the columns dynamically rendered you will also need to use a combination of Record.FieldNames, Record.Combine, & Table.Column.

DynamicTableName = Table.ExpandRecordColumn(JSONTableName, JSONStringColumnName, Record.FieldNames(Record.Combine(Table.Column(JSONTableName, JSONStringColumnName))))

One consideration for this dynamic approach, if you remove a key/value pair from the JSON object, it will, by design, remove the column from the refreshed dataset.  Even though any visualizations that are using that particular column will then show an error, it is not a critical issue and can automatically be fixed by clicking the "Fix This" button.  However, to avoid this altogether, it is best to avoid removing columns from the structure.


"Remote" connection to a local MYSQL database on LAN I have a mySQL (5.5.38) server running on my desktop (windows 7), I can connect just fine into any of the servers Users, and skip-networking is off when checked via show variables. I am trying to give another family member access on their laptop, but get error 10060 each time (could not connect)

I have tried giving them an account from host %, and another one from their local IP, though neither is working. I have tried having them connect to each of the following with both accounts - my actual IP, my local IP, and the hostname variable for the server. each connection returns error 10060.

Issue solved - Windows firewall had to be turned ON and then have the ports forwarded. Windows firewall being off was filtering the ports.


When is Seconds_Behind_Master too big? My mysql_slave got stuck on an error and built up to being 200,000+ Seconds_Behind_Master. Should I rebuild it and start from scratch, or let it catch up by itself?

IMHO

Let it catch up.

The Seconds_Behind_Master reading is not what I rely on to see if catching up is possible or worth it. Don't be afraid at the number reported.

You have to look at the server's setting for expire_logs_days. If your slave is so far behind that the binary log file has been deleted, then you need to re-image.


Want to create an SQL function that removes table row duplicates I'd be following the procedure outlined here (unless of course someone has a better way to do it), and I'm wondering if I could just have some help being pointed in the right direction on how to start.

Basically I need help first on HOW to create functions, and general tips on making it adjustable for varying number of columns etc. This may be a very complicated task, as I have no previous experience making SQL functions, so please let me know if this is a difficult task for an SQL noobie working with MS SQL 2005.

That's a big first step for a self-identified newbie. If I read your question correctly, you're looking to create reusable code that will delete duplicates from an arbitrary table. I'm not going to show you how to do that because it's a) dangerous and b) non-trivial. But here's how I'd do it for one table:

with cte as (
   select row_number() over (partition by col_a, col_b order by col_a) as [rn]
   from tbl
)
delete cte
where [rn] <> 1

This code assumes that you have a table called tbl and you're using *col_a* and *col_b* together to determine uniqueness. What's going on here is that you're essentially enumerating all of the rows within a given set that would be considered duplicate and deleting all but the first.


Using Parameters in OPENQUERY I have linked server connection named MMSHO. There is a parameterized function in Oracle server where I want to send store codes by OPENQUERY then filter in where condition. :

        DECLARE
       @OPENQUERY   NVARCHAR (MAX),
       @STOREID     VARCHAR (2),
       @ARTICLEID   VARCHAR (10),
       @SQL         NVARCHAR (MAX)
    SET @STOREID = 10;
    SET @ARTICLEID = 245511;
    SET @OPENQUERY =
             'SELECT *
              FROM OPENQUERY (
                      MMSHO,
                      ''SELECT MS000'
           + @STOREID
           + '77TRP.MW070K01.MW070P01_REKE@MS000'
           + @STOREID
           + '77TRP(ART_NR, VAR, GEBI_NR) AS ARTICLE_NNBP
                                    FROM MS000'
           + @STOREID
           + '77TRP.MW_MA_VAR_GEBI_SNAP@MS000'
           + @STOREID
           + '77TRP
                                    WHERE SUBSYS_ART_NR = '
           + @ARTICLEID
           + '''';

    SET @SQL =
             'SELECT STORE_NO,
           SUBSYS_ART_NO,
           ARTICLE_NO,
           ARTICLE_DESC,
           PUAR,
           SUPPLIER_NO,
           SORTEN_TEXT AS ARTICLE_VARIANT,
           GEBI_NR,
           BLOCK_CD,
           PACKAGE_CONTENT AS CONTENT,
           PACKAGE_TYPE AS CONTENT_TYPE,
           VAT AS ARTICLE_VAT,'
           + CHAR (13)
           + '('
           + @OPENQUERY
           + ') AS ARTICLE_NNBP,
           ARTICLE_NNSP,
           STOCK_QTY AS ARTICLE_STOCK,
           CASE WHEN DEPT_NO IS NULL THEN 0 ELSE DEPT_NO END DEPARTMENT
           FROM ARTICLE
           WHERE STORE_NO = 10 AND SUBSYS_ART_NO = 245511'

    PRINT (@SQL)

When I run the above query with EXEC (@SQL), SQL Server throw an error message which is :

Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'CASE'.
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near 'DEPARTMENT'.

I have tried to remove the lines between OPENQUERY and FROM keyword but no success. What am I doing wrong? Could you please help me?

PRINTED QUERY

SELECT STORE_NO,
       SUBSYS_ART_NO,
       ARTICLE_NO,
       ARTICLE_DESC,
       PUAR,
       SUPPLIER_NO,
       SORTEN_TEXT AS ARTICLE_VARIANT,
       GEBI_NR,
       BLOCK_CD,
       PACKAGE_CONTENT AS CONTENT,
       PACKAGE_TYPE AS CONTENT_TYPE,
       VAT AS ARTICLE_VAT,
       (SELECT *
          FROM OPENQUERY (
                  MMSHO,
                  'SELECT MS0001077TRP.MW070K01.MW070P01_REKE@MS0001077TRP(ART_NR, VAR, GEBI_NR) AS ARTICLE_NNBP
                                FROM MS0001077TRP.MW_MA_VAR_GEBI_SNAP@MS0001077TRP
                                WHERE SUBSYS_ART_NR = 245511') AS ARTICLE_NNBP,
       ARTICLE_NNSP,
       STOCK_QTY AS ARTICLE_STOCK,
       CASE WHEN DEPT_NO IS NULL THEN 0 ELSE DEPT_NO END DEPARTMENT
       FROM ARTICLE
       WHERE STORE_NO = 10 AND SUBSYS_ART_NO = 245511

I think:

this code:

       (SELECT *
          FROM OPENQUERY (
                  MMSHO,
                  'SELECT MS0001077TRP.MW070K01.MW070P01_REKE@MS0001077TRP(
ART_NR, VAR, GEBI_NR) AS ARTICLE_NNBP
FROM MS0001077TRP.MW_MA_VAR_GEBI_SNAP@MS0001077TRP
WHERE SUBSYS_ART_NR = 245511') AS ARTICLE_NNBP,

should be:

       (SELECT *
          FROM OPENQUERY (
                  MMSHO,
                  'SELECT MS0001077TRP.MW070K01.MW070P01_REKE@MS0001077TRP(
ART_NR, VAR, GEBI_NR) AS ARTICLE_NNBP
FROM MS0001077TRP.MW_MA_VAR_GEBI_SNAP@MS0001077TRP
WHERE SUBSYS_ART_NR = 245511') AS ARTICLE_NNBP),

there is a ")" missing at the end


MDF, LDF, TempDb Location on same Hybrid SAN I have seen this question asked before regarding SSDs or SANs, but never a Hybrid SAN. Also, most of the questions are several years old, so I figured I'd get a fall 2016 take on the answer.

We currently have a physical server with ~150 SQL 2012 databases all located on a RAID10 volume. The TempDb is located on the OS volume and is RAID1. We do mostly reading, with occasional, massive batch inserts.

We are migrating to a virtual server housed on a Hybrid SAN (24 drives, 1/3 SSD 2/3 10K HDD).

The general consensus is to keep MDF, LDF, and TempDb on different volumes, but I am getting conflicting opinions from my SQL guru vs. my SAN master.

One of the benefits to the Hybrid SAN is that I can create volumes of all SSD, all HDD, or a combination of both (magically migrated based on usage) with a different RAID method for each volume.

Given all that, what would be the suggested best practice for MDF, LDF, and TempDb placement? All on same volume of SSD? Split off into different volumes, some SSD some HDD? What RAID method? How about formatted block size and partition alignment (if that applies to SAN/SSD)?

Thanks in advance!

The basic principle do not change regardless of what SAN, DAS or USB sticks you use to hold the data and transaction log files. TempDB is just a "special" database where its data and transaction log files have different workload behavior considerations since it can serve 1 - '000s of databases.

You design your database storage to handle the IOPS, throughput (MB/s), latency and reliability/availability. Modern SAN solutions largely removed the need to have custom designed physical drives and file placements. There's no magic and it's not a free ride. The SAN has to be setup to deliver on the numbers and ensuring it has the right level of protection against failure, including perf impact during partial failures. It's really boils down to letting the vendors/admins configure their physical layout optimized for their SAN and delivering on the key requirements listed above.

If you can correctly measure what you need and the SAN admins signup to deliver on the SLA, I won't try to get into the physical layout. For starters, it's technically interesting but you're signing up for long term maintenance. Also, with most SANs, you really shouldn't or even cannot pick some arbitrary physical disk configuration. Critical point here is having the right numbers for the 4 areas above and the SAN admins signing off and being accountable to deliver.


How to print empty string as a result from xml node creation using oracle in a procedure i want to print one of the node value as Author ="" empty string in xml node element as below:
This syntax is in oracle:

CREATE OR REPLACE FUNCTION GetRtData
(
    pf_wkstn_oid_sh number,
    pf_wkstn_oid_lng number
)
RETURN varchar2
IS RtData varchar2(10000);
BEGIN
    SELECT REPLACE(XMLElement("TableData",
                         XMLAGG(XMLElement("rt",XMLAttributes(field_data_type as field_data_type,'' as Author)))
                       ).getStringVal(),'></rt>','/>')
    INTO RtData
    FROM pmry_c_rt_flng
    where wkstn_oid_sh = pf_wkstn_oid_sh
    AND wkstn_oid_lng = pf_wkstn_oid_lng;
    return RtData;
END;

it is returning as below.

SQL> select GetRtData(100,4223) from dual;
GETRTDATA(100,4223)
----------------------------------------------------------------------------
<TableData><rt FIELD_DATA_TYPE="N"/></TableData>

even Author has removed in oracle while creating the node using as ( '' as
Author) in the above result.

i want the result as below format:

<TableData><rt FIELD_DATA_TYPE="N" Author=""/></TableData>

once i am using as

' ' as Author
<TableData><rt FIELD_DATA_TYPE="N" Author=" "/></TableData> one extra space which is not empty string.

so can any one help me how to handle this one to make as Author = "" only in oracle.

You use REPLACE in your code, with 1 more REPLACE:

SELECT REPLACE(XMLElement("TableData",
          XMLAGG(XMLElement("rt",XMLAttributes(1 as file_data_type, '' as Author)))
       ).getStringVal(),'></rt>','/>')
  as xml
FROM dual;

XML
--------------------------------------------------------------------------------
<TableData><rt FILE_DATA_TYPE="1"/></TableData>

SELECT REPLACE(XMLElement("TableData",
          XMLAGG(XMLElement("rt",XMLAttributes(1 as file_data_type, 'dummy' as Author)))
       ).getStringVal(),'></rt>','/>')
  as xml
FROM dual;

XML
--------------------------------------------------------------------------------
<TableData><rt FILE_DATA_TYPE="1" AUTHOR="dummy"/></TableData>

SELECT REPLACE(REPLACE(XMLElement("TableData",
          XMLAGG(XMLElement("rt",XMLAttributes(1 as file_data_type, 'dummy' as Author)))
        ).getStringVal(),'></rt>','/>'), 'dummy')
  as xml
FROM dual;

XML
--------------------------------------------------------------------------------
<TableData><rt FILE_DATA_TYPE="1" AUTHOR=""/></TableData>


How to get a boolean value into a SELECT operation based on another table? I have two tables: Message and Attachments (which represent entity types that have a one-to-many relationship) as follows:

CREATE TABLE message (
    messageId ...,
    [from]...,
    [to]...,
    subject...,
    body...
);

CREATE TABLE attachments (
    messageId...,
    filename...
);

I want to SELECT the basic message data and include a bit column to denote whether the message has attachments or not. I am trying to use EXISTS in the operation shown below:

SELECT
M.*,
hasAttachments = EXISTS(SELECT AttachmentId
                          FROM Attachment
                         WHERE messageId = M.messageId),
FROM Message M

But this doesn't work - What's the way to do what I'm looking for?

I'd do it with a CASE statement:

select
    m.*,
    hasAttachments = CASE WHEN EXISTS(select * from Attachment where messageId = M.messageId) then 1 else 0 end
from Message M

or

select distinct
    m.*,
    hasAttachments = CASE WHEN a.AttachmentId is not null then 1 else 0 end
from Message m
left join Attachment a on a.MessageId = m.MessageId


Will SQL Server check FK restrictions between row deletes in a single DELETE statement? I have a table that is self-referencing:
CREATE TABLE [dbo].[TestTable] (
    [id] [bigint] IDENTITY(100000,1) NOT NULL,
    [referenced_id] [bigint] NULL,
    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED (
        [id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestTable] WITH NOCHECK ADD CONSTRAINT [FK_ReferencedId] FOREIGN KEY ([referenced_id])
    REFERENCES [dbo].[TestTable] ([id])
GO

ALTER TABLE [dbo].[TestTable] CHECK CONSTRAINT [FK_ReferencedId]
GO

Let's say I have several rows that reference each other:
id     | referenced_id
-------|--------------
100023 | 100024
100024 | 100023
100025 | 100026
100026 | 100023

If I try and DELETE the row WHERE [id] = 100023, the FK will be violated because 100024 and 100026 reference that row and the DELETE will fail.  However if I just DELETE FROM [dbo].[TestTable], it seems to work and successfully delete all rows.  Therefore, SQL Server only seems to be checking the FK constraint after all rows to be deleted in a single DELETE statement have been deleted, rather than in between each row deletion.
Can I rely on this behaviour, or might such a DELETE sometimes fail?

SQL Server only seems to be checking the FK constraint after all rows to be deleted in a single DELETE statement have been deleted, rather than in between each row deletion. Can I rely on this behaviour?

Yes.  Constraints are checked for the statement, not for each row, and not at the end of a transaction.
Here's the query plan for a DELETE from that table.

The delete occurs on the top branch, but the IDs of the deleted rows are spooled, and after the clustered index delete step, the spooled IDs are used to look up any rows that reference the deleted rows.  If any are found the DELETE will be rolled back (DML statements always run in a nested transaction internally), and a FK error raised.


SQL Server SSL connection Is it possible if I wanted to set up SQL Server 2008 R2 instance so that it only accepts SSL Connection?

I am getting different answers and based on my testing, it will accept non-SSL connection even though I have force encryption as instructed on http://msdn.microsoft.com/en-us/library/ms191192%28v=sql.105%29.aspx

The last comment from Edward Dorland on this thread (SQL Server SSL connection - What am I missing?) pointed out
"SQL server uses ssl for encryption, that doesnt need a cert at the client side."

Could anyone explain in detail?

Thanks.

If you enable the force encryption option and restart the SQL Server instance then all connections between the instance and the SQL Server will be encrypted via SSL. There is no need for a certificate or change to the client. The server tells the driver that it needs to switch to an encrypted session. Thus is kind of like when you connect to a website using http:// and the Web server tells your browser to switch and reconnect using https://. It all happens without the user needing to do anything.

There's no need for an appliance to do this. SQL Server has been doing this internally just fine since SQL 2000.

What SQL does isn't to block unencrypted sessions, it converts unencrypted sessions to encrypted then allows them to continue.


'LDF' is not a valid database file header. The PageAudit property is incorrect I 'am trying to import database into sqlserver 2008 using this query

EXEC sp_attach_db @dbname = 'LIQ_DEV_V9',
@filename1='/home/elsayedawd/DATA/databasedumbs/database9/LIQ_DEV_V9.mdf',
@filename2='/home/elsayedawd/DATA/databasedumbs/database9/LIQ_DEV_V9_log.LDF';

but it failed with

  Error: The header for file
  '/home/elsayedawd/DATA/data-base-dumbs/database-9/LIQ_DEV_V9_log.LDF'
  is not a valid database file header. The PageAudit property is
  incorrect. SQLState:  S00015 ErrorCode: 5172

Ok , is it possible to import without log file?

Yes, but stop using deprecated procedures like sp_attach_db (see the big warning at the top).

Next, you need to supply paths to SQL Server that make sense. Can you get to /home/ in Windows Explorer? Can you do that when logged in as the SQL Server service account?

CREATE DATABASE LIQ_DEV_V9
  ON
  (
    name = LIQ_DEV_V9,
    filename = 'C:\...\LIQ_DEV_V9.mdf'
  )
  FOR ATTACH_REBUILD_LOG;

This might fail if SQL Server attempts to create a new log file in a location where that file already exists; so if the damaged LDF file is in that same folder, you may want to rename it, move it, or delete it.

More importantly, you really shouldn't be relying on mdf/ldf files as backups; please read this post:

Bad Habits : Using MDF/LDF Files as "Backups"