MS SQL Server using SSL Connection over the Web? I'm looking to have an external SQL Server 2008R2 Express instance and be able to connect to this via SSL using a 'sql.my-domain.com' address. So far, I've looked at a few Microsoft links on the subject and also a blog found on here (links towards the bottom), but I'm not having a great amount of luck with this.

My main issue appears to be getting the actual certificate to be recognised by the server. I have installed this into the MMC > Certificates location, applied permissions for the account which is running the SQL Express instance and I am still unable to even select the account when I am in SQL Configuration Manager and trying to apply the certificate to the instance.

I'm running SQL Express as this is needed as a proof-of-concept that we can get it working, but we'll be moving onto SQL Standard for the live environment.

Does anyone have an explanation in everyday terms, any knowledge of the pitfalls or any other web resources I can check out to try and get this service running?

EDIT - I need more than 10 rep to post all the links. Here's two I've used.

http://technet.microsoft.com/en-us/library/ms189067%28v=sql.105%29.aspx

http://thedataspecialist.wordpress.com/2013/03/12/using-a-self-signed-ssl-certificate-with-sql-server/

According to Books Online, the certificate used for encrypting connections must meet the following requirements:

The certificate must be issued for Server Authentication
The name (common name or CN) on the certificate must match the full-qualified domain name used by clients to connect.

If those two conditions are not met, the certificate will not show in the drop-down listing. Additionally, the service account for SQL Server must have read access to the private key of the certificate. Without that right, you will be able to select the certificate, but the service will not start.

Since this is a test system you intend to use as proof-of-concept, a self-signed certificate should be adequate. I am not sure what language you are using for your client application, but this link provides options available for connection strings in .Net. You should review at least the following two options.

Encrypt - Tells the client application to force encryption.
TrustServerCertificate - Tells the client application to skip validating the certificate chain. This is important with a self-signed certificate as the chain likely will not be trusted.


How to make psql restore of a database ignore the owner information? My database is associated with different usernames on the server and on my local machine. Whenever I try to restore it on my local machine from the backup downloaded from the server using the command
psql -d dbname -f backupfile.sql

I get the error output saying that
ERROR: role "myusername" does not exist

Despite the error message, the actual restore goes through. But it is annoying to see multiple error messages saying the same thing (once for each table). Is there a way I can suppress this error message. I searched quite a bit but could not find anything. There is no-owner option for pgrestore, but I could not find anything for psql.
Thank you for your help.

That is easy. Use the --no-owner and --no-privileges options of pg_dump.
You might be more happy if you used the custom format of pg_dump (-F c). Then you'd restore with pg_restore and could specify the options at restore time.


Cannot INSERT Into Newly Created Column I have a simple test table like this:

CREATE TABLE MyTable (x INT);

Within a transaction, I try to add a column and then insert into the newly created column:

BEGIN TRANSACTION;
    PRINT 'Adding column, ''SupplementalDividends'', to MyTable table.';

    ALTER TABLE MyTable
        ADD SupplementalDividends DECIMAL(18,6);

    PRINT 'Column added successfully....';

    PRINT 'Ready to INSERT into MyTable ...';

    INSERT INTO MyTable (x, SupplementalDividends)
        VALUES (1, 3.2);

    PRINT '**** CHANGES COMPLETE -- COMMITTING.';
COMMIT TRANSACTION;

The Problem is an error message when I run the above code:

Invalid column name 'SupplementalDividends'.

Why is this causing an error? If I add the column in a different batch, outside the transaction, it'll work. My problem is that I want to add the column within the transaction. Why the error?

It's a binding issue. The code is bound to the metadata of the table at compile time and it is not late bound.
Try using EXEC and dynamic SQL to overcome this limitation:

BEGIN TRANSACTION;
    PRINT 'Adding column, ''SupplementalDividends'', to MyTable table.';

    ALTER TABLE MyTable
        ADD SupplementalDividends DECIMAL(18,6);

    PRINT 'Column added successfully....';

    PRINT 'Ready to INSERT into MyTable ...';
    EXEC('
    INSERT INTO MyTable (x, SupplementalDividends)
        VALUES (1, 3.2);
    ')

    PRINT '**** CHANGES COMPLETE -- COMMITTING.';
COMMIT TRANSACTION;

Another option is using a stored procedure to insert the data: late binding applies to stored procedure, but not to ad-hoc queries. Again, you would have to use dynamic SQL to create the procedure, but it could make it easier for you to pass parameters:

BEGIN TRANSACTION;
    PRINT 'Adding column, ''SupplementalDividends'', to MyTable table.';

    ALTER TABLE MyTable
        ADD SupplementalDividends DECIMAL(18,6);

    PRINT 'Column added successfully....';

    PRINT 'Ready to INSERT into MyTable ...';

    EXEC('
    CREATE PROCEDURE insData @p1 int, @p2 DECIMAL(18,6)
    AS
    BEGIN
        INSERT INTO MyTable (x, SupplementalDividends)
        VALUES (@p1, @p2);
    END')

    EXEC InsData 1, 3.2;

    PRINT '**** CHANGES COMPLETE -- COMMITTING.';
COMMIT TRANSACTION;

A temporary stored procedure would work as well:

BEGIN TRANSACTION;
    PRINT 'Adding column, ''SupplementalDividends'', to MyTable table.';

    ALTER TABLE MyTable
        ADD SupplementalDividends DECIMAL(18,6);

    PRINT 'Column added successfully....';

    PRINT 'Ready to INSERT into MyTable ...';

    EXEC('
    CREATE PROCEDURE #insData @p1 int, @p2 DECIMAL(18,6)
    AS
    BEGIN
        INSERT INTO MyTable (x, SupplementalDividends)
        VALUES (@p1, @p2);
    END')

    EXEC #InsData 1, 3.2;

    PRINT '**** CHANGES COMPLETE -- COMMITTING.';
COMMIT TRANSACTION;


Cannot Start mySQL Daemon with Error "mysqld from pid file ... ended" I cannot get MySQL to run. It was working just fine hours ago, but upon starting, repeatedly ends without telling me very much. Here are my logs

[myusername@location ~/mysql]$ ./bin/mysqld_safe --log-error=my-errors &
[2] 14747
[1]    Done                          ./bin/mysqld_safe
[myusername@location ~/mysql]$ 150708 20:55:25 mysqld_safe Logging to '/rc00/d84/myusername/mysql/data/my-errors.err'.
150708 20:55:25 mysqld_safe Starting mysqld daemon with databases from /rc00/d84/myusername/mysql/data
150708 20:57:06 mysqld_safe mysqld from pid file /rc00/d84/myusername/mysql/data/ovid03.u.washington.edu.pid ended

Attempting to start the regular way leads to the following:

[myusername@location bin]$ mysqld
mysqld: Command not found.
[myusername@location bin]$ cd ..
[myusername@location mysqlver]$ ./bin/mysqld
150708 21:04:09 [Note] Plugin 'FEDERATED' is disabled.
150708 21:04:09 InnoDB: The InnoDB memory heap is disabled
150708 21:04:09 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150708 21:04:09 InnoDB: Compressed tables use zlib 1.2.3
150708 21:04:09 InnoDB: Initializing buffer pool, size = 128.0M
150708 21:04:09 InnoDB: Completed initialization of buffer pool
InnoDB: Unable to lock ./ibdata1, error: 11

The last few lines repeat over and over again, as it tries to start.

I was reading around that there could be problems with ownership of files and folders, could this be it?

[myusername@location ~]$ ls -l mysql
total 0
lrwxrwxrwx 1 myusername nousers 12 Jul  1  2013 bin -> mysqlver/bin
lrwxrwxrwx 1 myusername nousers 29 Jul  1  2013 data -> /rc00/d84/myusername/mysql-data
lrwxrwxrwx 1 myusername nousers 13 Jul  1  2013 docs -> mysqlver/docs
lrwxrwxrwx 1 myusername nousers 16 Jul  1  2013 include -> mysqlver/include
lrwxrwxrwx 1 myusername nousers 12 Jul  1  2013 lib -> mysqlver/lib
lrwxrwxrwx 1 myusername nousers 12 Jul  1  2013 man -> mysqlver/man
lrwxrwxrwx 1 myusername users   40 Jul  1  2013 mysqlver -> /usr/local/mysql-5.5.18-linux2.6-x86_64/
lrwxrwxrwx 1 myusername nousers 16 Jul  1  2013 scripts -> mysqlver/scripts
lrwxrwxrwx 1 myusername nousers 14 Jul  1  2013 share -> mysqlver/share
lrwxrwxrwx 1 myusername nousers 18 Jul  1  2013 sql-bench -> mysqlver/sql-bench
lrwxrwxrwx 1 myusername nousers 22 Jul  1  2013 support-files -> mysqlver/support-files
lrwxrwxrwx 1 myusername nousers 14 Jul  1  2013 tests -> mysqlver/tests
lrwxrwxrwx 1 myusername nousers 12 Jul  1  2013 usr -> mysqlver/usr

I feel really lost right now and thank you in advance for your help. I do have a backup of the sql folder from a week ago, but am not yet ready to try putting that on to the server just yet.

InnoDB: Unable to lock ./ibdata1, error: 11

Perhaps another copy of mysqld is still running?

Only run it as a service.


Combine 4 rows based on common ID and Date I have a query that returns 4 lines per ID. I would like to be able to merge these into one single row in a new table, based on the ID and Date.
Can anyone help? I've researched and seem can't find any examples.
Simplified version of how the data looks at the moment:

There's no more fields to identify if it's in or out. This is a log type database usually 4 entries a day per user id.

try this :

select t.user_id,t.time_log,substring_index(t.tstring,'#',1) am_in,substring_index(substring_index(t.tstring,'#',2),'#',-1) am_out,
substring_index(substring_index(t.tstring,'#',-2),'#',1) pm_in,substring_index(t.tstring,'#',-1) pm_out
from
  (select h.user_id,h.time_log,group_concat(h.stime order by h.stime separator '#') tstring
  from user_hardware h
  group by h.user_id,h.time_log
  ) t;

But ensure that same stime will not occur twice for same user_id and time_log i.e. user_id,time_log,stime should be unique key.


Trigger calls and application code When a trigger is set on a table does the application code wait for the original statement and the trigger to execute before resuming or just the original statement? Consider application code to be PHP or Ruby.Here by original statement I mean the sql query called by the application code.

Triggers in MySQL are synchronous. That means that the query submitter has to wait for the trigger to finish before it can resume its work.


For an Update to the existing table, one solution would be:

UPDATE t
SET t.New_Column = CASE WHEN types.mintype = 'F' and types.maxtype = 'F' THEN 'FTW'
                        WHEN types.mintype = 'N' and types.maxtype = 'N' THEN 'Non-FTW'
                        WHEN types.mintype = 'F' AND types.maxtype = 'N' THEN 'Mixed' END
FROM tbl t
INNER JOIN
(SELECT ORDR,
       MIN(TYPE) mintype,
       MAX(TYPE) maxtype
FROM tbl
GROUP BY ORDR) types
    ON t.ORDR = types.ORDR

Though, I would also, as Chris Aldrich said in the comments, suggest using a view. That view could be:

CREATE VIEW v_tbl
AS
SELECT t.ORDR,
       t.TYPE,
       CASE WHEN types.mintype = 'F' and types.maxtype = 'F' THEN 'FTW'
            WHEN types.mintype = 'N' and types.maxtype = 'N' THEN 'Non-FTW'
            WHEN types.mintype = 'F' AND types.maxtype = 'N' THEN 'Mixed' END New_Column
FROM tbl t
INNER JOIN
(SELECT ORDR,
       MIN(TYPE) mintype,
       MAX(TYPE) maxtype
FROM tbl
GROUP BY ORDR) types
    ON t.ORDR = types.ORDR


SQL Server heap tables fragmentation monitoring As a SQL Server DBA I have maintenance jobs to regularly rebuild/reorganize indexes. Recently, I ask myself about table fragmentation in SQL Server. Then I have read article regarding heap tables and their fragmentation. We constantly check the status of SQL servers in various way, and I am wondering if adding a check on table fragmentation will be useful.

I’m thinking to develop something like: "Is their a cluster index ?" yes -> do nothing, no -> so check :

percentage of fragmentation > x%

numbers of forward pointing > x

numbers of rows > x

if x > some values then raise an alert.

What do you think of setting up that kind of check, useful or not?

Are Defragmentation of heap tables going to reduce physical size of database's files on disk ?

EDIT :

I finally use this piece of code to detect heap tables which need to be rebuild :

SELECT Database_id, Object_name([object_id]) as TableName, Index_type_desc,
Avg_fragmentation_in_percent, rowmodctr, forwarded_record_count
From sys.dm_db_index_physical_stats(db_id(),object_id(''),null,null,'detailed') AS SDDIPS
Inner join sys.sysindexes AS SI on SDDIPS.[object_id] = SI.id
AND SDDIPS.index_id = SI.indid
--Inner join sysobjects AS SO on  SI.id = SO.id
Where index_level = 0 AND index_type_desc = 'HEAP' AND Avg_fragmentation_in_percent > 40
AND rowmodctr > 100 AND forwarded_record_count > 1000

However it runs for a long time period and performance like PLE collapse

I would suggest that, rather than 'roll-your-own', you save yourself a whole bunch of time and effort and implement Ola Hallengren's Index Maintenance script.

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

And, check out Brent Ozar's tweaks for Ola's script

https://www.brentozar.com/archive/2014/12/tweaking-defaults-ola-hallengrens-maintenance-scripts/


Creating row number for multiple columns? I have the below data, and I want to only take one Location per Order ID ordered by the distance. This data set takes customer zips and compares to store zips and then returns the distance. I want to only choose the store they're closest to.
I have this so far:
SELECT
*
FROM (
    SELECT
    t.*,
    row_number() over(PARTITION BY orderid ORDER BY dts) rn
    FROM (
        SELECT
        location,
        orderid,
        group1,
        group2,
        group3,
        group4,
        group5,
        custid,
        dts,
        sum(qty) AS units,
        sum(bsk) AS demand
        FROM osfdist
        GROUP BY 1,2,3,4,5,6,7,8,9
    ) t
) a

But this just starts the increment at 1 for the first row ordered by Distane and then increments to the total number of rows. I want it to say:
Downtown    1
Downtown    1
Downtown    1
Downtown    1
Coastal     2
Coastal     2
Coastal     2
Coastal     2

etc, so I can select where row_number=1 and only select the Downtown records.

Location
OrderID
Group 1
Group 2
Group 3
Group 4
Group 5
Customer ID
Distance
Qty
Sales

Downtown
1
FOOTWEAR
SHOES
SHOES (LOW)
M
RUN
abc123
8.724497523
1
90

Downtown
1
APPAREL
PANTS
PANTS (1/1)
F
FOO
abc123
8.724497523
1
22.5

Downtown
1
FOOTWEAR
SHOES
SHOES (LOW)
U
ORI
abc123
8.724497523
1
55

Downtown
1
APPAREL
SHORTS
SHORTS
M
TRA
abc123
8.724497523
3
50

Downtown
1
APPAREL
PANTS
TRACK PANT
F
ORI
abc123
8.724497523
1
35

Downtown
1
APPAREL
PANTS
TRACK PANT
M
ORI
abc123
8.724497523
1
35

Downtown
1
FOOTWEAR
SHOES
SHOES (LOW)
M
ORI
abc123
8.724497523
1
65

Downtown
1
APPAREL
JACKETS
LIGHT JACKET
F
OUT
abc123
8.724497523
2
100

Downtown
1
APPAREL
PANTS
PANTS (1/1)
M
TRA
abc123
8.724497523
1
27.5

Downtown
1
FOOTWEAR
SANDALS/SLIPPERS
SLIDES
M
RUN
abc123
8.724497523
1
17.5

Downtown
1
APPAREL
TIGHTS
TIGHT LONG
F
TRA
abc123
8.724497523
2
35

Coastal
1
APPAREL
PANTS
TRACK PANT
F
ORI
abc123
8.888442956
1
35

Coastal
1
FOOTWEAR
SHOES
SHOES (LOW)
M
RUN
abc123
8.888442956
1
90

Coastal
1
FOOTWEAR
SANDALS/SLIPPERS
SLIDES
M
RUN
abc123
8.888442956
1
17.5

Coastal
1
FOOTWEAR
SHOES
SHOES (LOW)
U
ORI
abc123
8.888442956
1
55

Coastal
1
APPAREL
PANTS
TRACK PANT
M
ORI
abc123
8.888442956
1
35

Coastal
1
APPAREL
PANTS
PANTS (1/1)
M
TRA
abc123
8.888442956
1
27.5

Coastal
1
FOOTWEAR
SHOES
SHOES (LOW)
M
ORI
abc123
8.888442956
1
65

Coastal
1
APPAREL
JACKETS
LIGHT JACKET
F
OUT
abc123
8.888442956
2
100

Coastal
1
APPAREL
SHORTS
SHORTS
M
TRA
abc123
8.888442956
3
50

Coastal
1
APPAREL
PANTS
PANTS (1/1)
F
FOO
abc123
8.888442956
1
22.5

Coastal
1
APPAREL
TIGHTS
TIGHT LONG
F
TRA
abc123
8.888442956
2
35

What is sor_hdr?
I think you need to drop the Partition by and replace row_number by rank


What's difference between standard view and indexed view in SQL Server I learn that the indexed view holds the data from base tables while standard view does not holds data.Is it means that the indexed view need memory and standard view don't?

Indexed view needs to be bind with schema object.I am also confused about the point that '*' is not allow  in the schema-bind object.For instance:

create view viewname as select * from tablename

The statement above can not work. why?

An indexed view is physically stored ("materialised") on disk = requires memory
A standard view is simply an expandable macro: there is no persistence of the data and the base tables are always used

Other notes:

Both will return the correct data from the base tables
SQL Server will consider whether to use the indexed view or just expand it like a macro (subject to edition and NOEXPAND hint)

SELECT * is bad practice anyway, but * means you can't qualify all columns as required for indexed views


sql server disk I/o question I have a question on disks.
How much I/O can a normal disk and a SAN disk can handle?
I have heard about SAN disks should be able to handle upto 200-300 mb/sec, how much does a normal disk can handle or do we need to decide this based on baseline values?

I wanted to know this because while troubleshooting sql server I/O issues , I want to isolate if SQL Server is posting too much of that a Disk is not able to handle that much load or a disk (I/O path) itself is bad so that I an engage storage team.

Thanks in advance.

How much I/O can a normal disk and a SAN disk can handle?

You have to read the manual to find out or you can do a stress test using SQLIO or SQLIOSim. Brent has a good article on : SQLIO Tutorial: How to Test Disk Performance

To see if underlying storage is a problem, you can use sys.dm_io_virtual_file_stats. It will show you where the hot-spots are and you can ask your SAN admin to move them away.

 ---Ref: http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/

SELECT
    --virtual file latency
    [ReadLatency] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
    [Latency] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
    --avg bytes per IOP
    [AvgBPerRead] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
    [AvgBPerWrite] =
        CASE WHEN [io_stall_write_ms] = 0
            THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
    [AvgBPerTransfer] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE
                (([num_of_bytes_read] + [num_of_bytes_written]) /
                ([num_of_reads] + [num_of_writes])) END,
    LEFT ([mf].[physical_name], 2) AS [Drive],
    DB_NAME ([vfs].[database_id]) AS [DB],
    --[vfs].*,
    [mf].[physical_name]
FROM
    sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
    ON [vfs].[database_id] = [mf].[database_id]
    AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO


How can clone database in mariadb prompt character? mysqlword="wwww"
old="xxxx"
new="yyyy"
mysqldump $old  -u root -p$mysqlword   | mysql $new  -u root -p$mysqlword

Now new database was cloned as like old database.
Can i use some command to clone database when entering mariadb terminal?

mysql -u root -pwwww
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.56-MariaDB MariaDB Server
MariaDB [(none)]>

How can clone database in mariadb prompt character instead of inputing mysqldump $old  -u root -p$mysqlword   | mysql $new  -u root -p$mysqlword in bash terminal?

General answer - No!

First of all - mysqldump it is not a simple sql command, this is
    complicated logic behind it, such as:
 - show definition of all objects - tables, routings, foreign keys
 - manage export import in proper order, like disable constraints and etc

Second (in addition to first) - mysql console (and SQL as language) do not support database level commands, like select all - You would need do this for each tables, objects, routines.

Federated engine (FederatedX) also not resolve "problem"


How to Resolve deadlock on concurrent MERGE on two tables Recently I have been encountering deadlocks occasionally when I try to insert data concurrently on two tables.

Following is the table structure

Create Table TableA
(
  Id Bigint not null primary key ,
  FieldA1 nvarchar(50)
)
go

Create Table TableB
(
  Id Bigint not null primary key,
  TableAId Bigint not null constraint FK_TableA Foreign Key (TableAId) References TableB(Id),
  FieldB1 nvarchar(50)
)
go

CREATE type TableBParam as table
(
    Id Bigint,
    TableAId Bigint not null,
    FieldB1 nvarchar(50)
)

--Deadlock was observed on the save query
go
CREATE PROC SaveTableB
(
 @val [dbo].[TableBParam] READONLY
)
AS
BEGIN
SET NOCOUNT ON;

MERGE [dbo].[TableB] AS T
USING (SELECT * FROM @val) AS S
  ON ( T.Id = S.Id)
WHEN MATCHED THEN
    update set FieldB1 = S.FieldB1
WHEN NOT MATCHED THEN
    insert(TableAId, FieldB1) Values(S.TableAId, S.FieldB1);

END
go

On a single transaction I am trying to complete data insertion on tableA and Table B by calling respective stored procedure (SaveTableA and SaveTableB).
SaveTableA is same as SaveTableB.

.

Database has read committed snapshot isolation enabled.
wondering how deadlock occurs on insertion? From the above graph I infer that the SaveTableB from one thread is victim while other instance of same procedure on different thread was holding lock on primary key of TableA.

From this reference I understand that the lock here is on the Btree of the Index based on the reference https://technet.microsoft.com/en-us/library/ms189849(v=sql.105).aspx and the deadlock graph above. While the locked resource is on the clustered index BTree node itself,

From the above inference I am curious to understand following

Does Insertion operation always holds lock at BTree level as seen the graph?
Trying to understand why there is a lock on the clustered index of TableA when the statements involved in the deadlock are SaveTableB procedure from two different transactions?
Since I am using the Merge statement here, is it the assumption that the btree lock on insert is hold on till the transaction ends or till the Merge statement ends?

Curious to understand that if it is possible to get deadlocks in this kind of scenario mentioned, trying to understand on how to approach it so that I will prepare myself to solve it. Thanks for helping me learn about this.

Concurrent MERGE statements will deadlock or produce PK violations by default, as the "scan" phase of the MERGE is performed without a restrictive lock.  You need to add a lock hint for this to work.  See https://blogs.msdn.microsoft.com/dbrowne/2013/02/25/why-is-tsql-merge-failing-with-a-primary-key-violation-isnt-it-atomic/ for MERGE locking details.

Or just fix it like this:

MERGE [dbo].[TableB] with (serializable) AS T
USING (SELECT * FROM @val) AS S
  ON ( T.Id = S.Id)
WHEN MATCHED THEN
    update set FieldB1 = S.FieldB1
WHEN NOT MATCHED THEN
    insert(TableAId, FieldB1) Values(S.TableAId, S.FieldB1);


join on just the next closest date? I want to get a table of

[file], [folder], [DateAdded], [DateRemoved]

I have a log table of changes that has

[file], [ChangeDate], [OldFolder], [NewFolder]

Files have moved in and out of folders multiple time so for the records for a file and folder I need to join the lowest date to the next highest date. I am not exactly sure how to write the SQL Query to do this.

Figured it out

SELECT temp1.[file], temp1.[ChangeDate] AS [AddedDate]
,temp1.[OldFolder]
,temp1.[NewFolder]
,(SELECT MIN(temp2.[ChangeDate])
FROM [Changes] AS temp2
WHERE temp2.[OldFolder] = temp1.[NewFolder]
AND temp2.[ChangeDate] > temp1.[ChangeDate]
AND temp2.[file] = temp1.[file]
) AS [RemovedDate]
FROM [Changes] AS temp1


What's the difference between a cursor and a trigger? I'm triying to understand why use a Cursosr or really how they work (I'm a newbie).

I don't really find a clear purpose for using it.

How does it compare to a trigger?

Generally, a cursor is used to iterate through a result set that was returned by a SELECT statement.

Cursors can be used in stored procedures, stored functions, and triggers.

A trigger is a stored program executed automatically to respond to a specific event like an insert, update, or delete.

As you can see, these are very different concepts and have very different purposes.

I suggest you review these links for the basics on both of these concepts:
Triggers, Cursors


Dynamic length obfuscation method in T-SQL I would like to obfuscate (scramble) sensitive data from a SQL Server database, but in the way which will provide:

irreversibility (the plaintext can't be derived from the obfuscated data),
obfuscated data length needs to be the same as a length of data before obfuscation.
obfuscated value does not need to be unique for repeated obfuscations of the same input value. To be honest, I rather like getting the same value for the same input which can used (e.g. some matching data in different tables, probably useful in test cases).

Example:

Abc -> zyx (lenght: 3)
StackOverflow -> a65vr4doqjd (lenght: 11)

Usually I avoid "home made" algorithms, so are you aware of some MS builtin solution which could provide this kind of obfuscation?

I hope I expressed my problem clearly, otherwise let me know and I'll try to add as much info as needed.

No, I am not aware of any built-in function that does exactly this. But, you can still accomplish this without doing anything too complicated.

You could use the built-in CRYPT_GEN_RANDOM function (introduced in SQL Server 2008 R2) which generates random values based on a supplied length. The output is in hex/binary values so each byte returned is represented as two alphanumeric characters (hence the / 2 + 1 part below).

DECLARE @InputString NVARCHAR(4000) = 'hello';
SELECT SUBSTRING(CONVERT(VARCHAR(8000),
                         CRYPT_GEN_RANDOM((LEN(@InputString) / 2) + 1),
                         2),
                 1,
                 LEN(@InputString)) AS [Obfuscated];

SET @InputString = 'test';
SELECT SUBSTRING(CONVERT(VARCHAR(8000),
                         CRYPT_GEN_RANDOM((LEN(@InputString) / 2) + 1),
                         2),
                 1,
                 LEN(@InputString)) AS [Obfuscated];

Returns something along the lines of:

8C108

9A7A

The only real downside here is that this needs to be done inline as CRYPT_GEN_RANDOM cannot be used in a User-Defined Function (UDF: Scalar or Table-Valued). However, it can still be applied in a set-based approach using a CTE as shown here (just set @MaxLength to the max length of the column being obfuscated):

DECLARE @MaxLength INT = 10;

;WITH cte AS
(
    SELECT CONVERT(VARCHAR(8000),
                   CRYPT_GEN_RANDOM((@MaxLength / 2) + 1),
                2) AS [Random]

)
SELECT tmp.[String],
       cte.[Random],
       SUBSTRING(cte.[Random], 1, LEN(tmp.[String])) AS [Obfuscated]
FROM   (VALUES (N'test'), (N'Hello')) tmp(String)
CROSS JOIN  cte;

Returns something along the lines of:

String    Random          Obfuscated
------    ------------    ----------
test      F99B3888F993    F99B
Hello     D3250E74F0A3    D3250

As you can see, CRYPT_GEN_RANDOM returns a different value for each row.

Also, not sure if this is acceptable or not, but the only alpha characters returned are A - F.

OR, if you want the obfuscation to be repeatable for the same input value, or at least don't mind it being repeatable and prefer that this code be in a function so that it is easier to apply to multiple columns, you can use the HASHBYTES function which, like CRYPT_GEN_RANDOM, returns hex/binary bytes. Unlike CRYPT_GEN_RANDOM, the output length is fixed (in this case at 64 characters since I am using SHA2_256), so I used REPLICATE to repeat the hashed valued if the length of the input string is more than 64 characters. Also unlike CRYPT_GEN_RANDOM, HASHBYTES can be used in a User-Defined Function (UDF) :-).

CREATE FUNCTION dbo.Obfuscate(@InputString NVARCHAR(4000))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
  SELECT SUBSTRING(REPLICATE(CONVERT(VARCHAR(8000),
                                     HASHBYTES('SHA2_256', @InputString),
                                     2),
                             (LEN(@InputString) / 64) + 1),
                   1,
                   LEN(@InputString)) AS [Obfuscated];
GO

And that can be used as follows:

SELECT tmp.[String],
       LEN(tmp.[String]) AS [InputLength],
       ob.[Obfuscated],
      LEN(ob.[Obfuscated]) AS [OutputLength]
FROM   (VALUES (N'test'), (N'Hello'), (REPLICATE(N'A', 63)),
               (REPLICATE(N'B', 64)), (REPLICATE(N'C', 65)),
               (REPLICATE(N'D', 4000))) tmp(String)
CROSS APPLY dbo.Obfuscate(tmp.[String]) ob;

Returns something along the lines of:

String                       InputLength    Obfuscated                    OutputLength
------                       -----------    ----------                    ------------
test                               4        FE52                                 4
Hello                              5        A07E4                                5
AAAAAAAAAAAAAAAAAAAAAA...         63        4B589C85DE74E76487730F3...          63
BBBBBBBBBBBBBBBBBBBBBB...         64        79813FB6480F354F1C6017A...          64
CCCCCCCCCCCCCCCCCCCCCC...         65        FB4B38FBA41ECC24B5B0F68...          65
DDDDDDDDDDDDDDDDDDDDDD...       4000        5D01CC6508C164E652B5C77...        4000

PLEASE NOTE: If you need alpha characters beyond A - F and/or need to have distinct obfuscated values for distinct input values (i.e. reduce chances of collisions), then either method above can be adapted easily enough to do that.


Upgrading Cassandra from 1.1.0 to latest I have a single node cassandra database running since 2 years now... never had any issues with it, but I would like to upgrade it to the latest cassandra (2.1.2 at the time I write those lines), of course without having to shutdown the service.

I have multiple servers at my disposal, that can be brought up to add nodes into a cluster if needed, what would be the procedure to update from my 1.1.0 version to the latest version without having any downtime ?

I know that it will be hard work, since I can't upgrading from 1.1.0 to 2.1.2 straight, I'll have to go through steps because of un-interoperable versions...

what would be the procedure to update from my 1.1.0 version to the
  latest version without having any downtime ?

You're going to need to do 3 upgrades in-total:

upgrade 1.1 to 1.2
upgrade 1.2 to 2.0
upgrade 2.0 to 2.1

And you'll want to follow these steps for each upgrade:

Stop the node.
Back up your configuration files. Depending on how you install the
product, these files may be overwritten with default values during
the installation.
Install the new version of Cassandra.
Configure the new product. Using the backups you made of your
configuration files, merge any modifications you have previously
made into the new configuration files for the new version.
Configuration options change often, so be sure to double check the
version restrictions for additional steps and changes regarding
configuration.
Start the node.
If you are upgrading from a major version (for example, from 1.2 to
2.0) or a major point release (for example, from Cassandra 2.0 to 2.1), upgrade the sstables on each node.

$ nodetool upgradesstables
Check the logs for warnings, errors and exceptions. Repeat on each
node in the cluster.

For questions on those steps, refer to the DataStax Upgrade Guide.


mongod.service: Failed with result 'exit-code' I'm trying to run mongodb server :
sudo systemctl start mongod

this gives me no error, but when I try to check the status, I get error saying :
mua@mua  ~  sudo systemctl status mongod               ✔  2916  14:07:16

● mongod.service - MongoDB Database Server
   Loaded: loaded (/lib/systemd/system/mongod.service; enabled; vendor preset: e
   Active: failed (Result: exit-code) since Tue 2021-04-27 14:07:16 +06; 5s ago
     Docs: https://docs.mongodb.org/manual
  Process: 13625 ExecStart=/usr/bin/mongod --config /etc/mongod.conf (code=exite
 Main PID: 13625 (code=exited, status=1/FAILURE)

এপ্রিল 27 14:07:16 mua systemd[1]: Started MongoDB Database Server.
এপ্রিল 27 14:07:16 mua mongod[13625]: {"t":{"$date":"2021-04-27T08:07:16.650Z"},"
এপ্রিল 27 14:07:16 mua systemd[1]: mongod.service: Main process exited, code=exit
এপ্রিল 27 14:07:16 mua systemd[1]: mongod.service: Failed with result 'exit-code'
lines 1-11/11 (END)...skipping...
● mongod.service - MongoDB Database Server
   Loaded: loaded (/lib/systemd/system/mongod.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Tue 2021-04-27 14:07:16 +06; 5s ago
     Docs: https://docs.mongodb.org/manual
  Process: 13625 ExecStart=/usr/bin/mongod --config /etc/mongod.conf (code=exited, status=1/FAILURE)
 Main PID: 13625 (code=exited, status=1/FAILURE)

এপ্রিল 27 14:07:16 mua systemd[1]: Started MongoDB Database Server.
এপ্রিল 27 14:07:16 mua mongod[13625]: {"t":{"$date":"2021-04-27T08:07:16.650Z"},"s":"F",  "c":"CONTROL",  "id":20574,   "ctx":"main","msg":"Err
এপ্রিল 27 14:07:16 mua systemd[1]: mongod.service: Main process exited, code=exited, status=1/FAILURE
এপ্রিল 27 14:07:16 mua systemd[1]: mongod.service: Failed with result 'exit-code'.
~
~

Here is some details :
MongoDB shell version v4.4.5
Build Info: {
    "version": "4.4.5",
    "gitVersion": "ff5cb77101b052fa02da43b8538093486cf9b3f7",
    "openSSLVersion": "OpenSSL 1.1.1  11 Sep 2018",
    "modules": [],
    "allocator": "tcmalloc",
    "environment": {
        "distmod": "ubuntu1804",
        "distarch": "x86_64",
        "target_arch": "x86_64"
    }
}

Can anyone help me solving this problem ?
Here is the service
 mua@mua  /lib/systemd/system  cat mongod.service   127 ↵  3002  13:28:11
[Unit]
Description=MongoDB Database Server
Documentation=https://docs.mongodb.org/manual
After=network-online.target
Wants=network-online.target

[Service]
User=mongodb
Group=mongodb
EnvironmentFile=-/etc/default/mongod
ExecStart=/usr/bin/mongod --config /etc/mongod.conf
PIDFile=/var/run/mongodb/mongod.pid
# file size
LimitFSIZE=infinity
# cpu time
LimitCPU=infinity
# virtual memory size
LimitAS=infinity
# open files
LimitNOFILE=64000
# processes/threads
LimitNPROC=64000
# locked memory
LimitMEMLOCK=infinity
# total threads (user+kernel)
TasksMax=infinity
TasksAccounting=false

# Recommended limits for mongod as specified in
# https://docs.mongodb.com/manual/reference/ulimit/#recommended-ulimit-settings

[Install]
WantedBy=multi-user.target

Looks familiar... If you check your mongod.service file (/lib/systemd/system/mongod.service), there should be line what tells where mongod.pid file is located (PIDFile=/tmp/mongod.pid). That same file (with path) should be found from your /etc/mongod.conf file.
processManagement:
  pidFilePath: /tmp/mongod.pid

Problem is that, after 5 second when you have said 'sudo systemctl start mongod.service', system will check "is there mongod.pid" and because it cannot find it, it will KILL that running mongod-process.


Power BI report server: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' when trying to view a report We have SQL Server 2019 and Power BI Report Server installed on the same host. we're using a virtual service account for Power BI Report Server. We get the following error when trying to view a report:

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSource1'.
(rsErrorOpeningConnection)    Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'.

We are trying to migrate and upgrade to SQL Server 2019 and Power BI Report Server on Windows Server 2019 from SQL Server 2014 and SSRS on Windows 2012. SQL Server 2019 has been set up on the new host and database schemas have been migrated over and Power BI Report Server has been installed (same host as the new SQL Server). We can access the database using SSMS without any issues. We are able to log in to the reports URL (https://hostname/reports) and can see that there are some reports that have been created. When we try to view a report, we get the error I mentioned above.
We are using a virtual service account for Power BI Report Server. As mentioned, Power BI Report Server and SQL Server are on the same host.
For Data source properties > Credentials > Log into the data source
If we set it to "As the user viewing the report" and click the "Test connection" button, it says "Connected successfully"
Also tried setting it to "By prompting the user viewing the report for credentials" with "Type of credentials" set to "Windows username and password", but it does not prompt for credentials when trying to view a report.
In both cases we get the "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' " error.
web.config has
    <authentication mode="Windows" />
    <identity impersonate="true" />

rsreportserver.config has
        <AuthenticationTypes>
            <RSWindowsNTLM/>
        </AuthenticationTypes>

We have also tried RSWindowsBasic and RSWindowsNegotiate, but they did not work.
We have 2 other servers where SQL Server and SSRS (not Power BI Report server) are installed and working. web.config and rsreportserver.config have the same values as above. I can't find any difference in the configs.
Some posts and documentation mention setting up SPNs, but as I understand it, SPNs are only needed if you use a domain account for the service account and not needed if you use the virtual service account. Maybe I misunderstood? Also read the posts about it possibly being a double hop issue, but the solution there seems to be to set up SPNs, but I'm not sure how to do that for the virtual service account.
Two of the docs I referred to:
Configure Windows Authentication on the Report Server
Specify Credential and Connection Information for Report Data Sources

Not familiar with virtual service accounts, but this is often related to Kerberos authentication.
Try using Microsoft® Kerberos Configuration Manager for SQL Server® to see if it can generate the SPNs for you.
It's a rather easy to use tool from Microsoft which will detect your SPN settings, and create scripts you can execute to fix your issues.


Show all privileges given to a schema or user in postgres I want to know what are the privileges a given schema or user has in Postgres. Like whether it can create a table, view, function, sequences or not.
I found one query but it showed privileges on table only. Please suggest any appropriate query.

Let me guess: you are an Oracle user, right? Because you mix up “user” and “schema”.
In PostgreSQL, the right to create tables, views, functions, operators, data types and the like is not properties of the user (or “role” in PostgreSQL).
You manage this with privileges on schemas: if there is a schema where the user has the CREATE privilege, the user can create any object he or she wishes in that schema. If you want to keep a PostgreSQL user from creating objects, you don't give them CREATE on any schema. Since the public schema by default gives CREATE to everybody, you'd have to
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

which is a good idea anyway for security reasons.
There are only few privileges that are tied to the user, because they pertain to global objects or no objects at all:

CREATEDB: the right to run CREATE DATABASE
CREATEROLES: the right to run CREATE ROLE
REPLICATION: the right to establish a replication connection; that is for example needed for pg_basebackup


Get a list of SQL scripts that ran on my server The schema change history report tells me when the schema of a DB was last changed.

I would have run an x.sql file on that same DB for those changes to have happened. Does SQL Server log that I ran x.sql on 3/01/2016 at 3:15?

No it doesn't.  SQL Server doesn't really have any concept of script files, it just processes commands that are run against it.

You could potentially set up some kind of automated process and/or tracing to track this, maybe setting the application name to the name of the script and tracing that but there's nothing in the box that I'm aware of which will do this automatically.


Downsides to using SQL 2012 Upgrade as opposed to a fresh install? I currently have SQL Server 2008 R2 which I would like to upgrade to SQL 2012. I can either un-install SQL 2008 and install a new copy of SQL Server or run the Upgrade. The upgrade is less work but I am wondering if there is a downside.

I have already tried the upgrade once. Ran into problems and had a difficult time removing both SQL 2008 & SQL 2012 so I could begin again. Upon successful upgrade, I saw that in Add/Remove programs both SQL 2008 R2 & SQL 2012 are still there afterwards. Which may make future upgrades/uninstalls complicated.

Aside from that, is there any other downsides to using the upgrade versus a fresh install?

Even if you install SQL Server 2012 clean, you get menu items in the start menu under SQL Server 2008 (e.g. Configuration Tools > Installation Center). In Add/Remove Programs do you see anything other than 2008 R2 Management Objects and 2008 Setup Support Files? I think these get installed by 2012 setup for backward compatibility reasons.

To be on the safe side, you could remove all the SQL Server 2008 components (except the database engine or other services you're using - e.g. remove client tools etc.) before installing SQL Server 2012, if cleanliness is important. The system does allow some components from both version, such as 2008 Management Studio and 2012 Management Studio, to co-exist, so for some parts of the ecosystem, upgrading is an addition rather than a replacement. It might be beneficial for other reasons to also remove peripheral things - for example I don't think BIDS 2008 plays very well with the 2012 client tools (not that I have tried it, just overheard rumblings about it).

When upgrading I would certainly make sure to do the following things, in addition to reading this very thorough upgrade guide:

Update the compatibility level of each database
Update all statistics

When upgrading from 2000 it has always been recommended to run DBCC ... WITH DATA_PURITY but I don't believe that step is necessary anymore (besides, you can't upgrade to 2012 directly from 2000 anyway).


What is better? Many articles in one publication or many publications with one article each one? I have a large database with several transactional replication publications.  Each publication has 15-20 articles.  Some subscriptions are push, so each time a new article is added to the publication, a new large snapshot must be created.  In order to avoid this, I'm thinking to create a single publication per article (not adding the arcticle to an existing one), thus reducing the need for a large snapshot.  I'm wondering if this approach has any downsize.

My last position we had dozens of publications. We would group the articles by size where the largest tables with millions or tens of millions of rows would have their own publication and others would be grouped together. In some cases publications were grouped by tables that were needed for a single application. Some critical tables were also in their own publications so that if we needed to reinitialize it during the day, it wouldn't be because of some minor table.

But this also depends on your hardware, applications, etc. Some publications we would reinitialize during the day and no one complained. Others could only be done after hours.

We probably had over 1000 tables that we published so one table per article would be a no go. There is no hard answer on this.


Switch publisher - subscriber side I have 2 server using merge replication. One acts as a Publisher and one acts as a Subscriber.
One day I want to switch the side, publisher become subscriber and subscriber become publisher. In my current way, I need to stop the replication, delete all publishing & subscription settings I was made and recreate it again. Is there any easier/faster way that I don't know ?

You can not "switch" a server's role in replication between subscribers and publisher.

Unless you are doing it repeatedly, I would highly recommend the method you describe, as it is the only supported method, and the most reliable.

It is possible to update system tables directly to change the name of a server, as I describe in this post. This method is unsupported, and is not easier--it simply shifts the type of work, depending on your priorities.

Note that in my post, I'm describing only the publisher, and only for transactional replication, not merge replication. You would need to identify different tables for merge replication, and do additional changes if changing the subscriber as well. However that article shows the general method. You would need to test heavily to ensure everything is working properly.


Difficulty with nested query order & grouping I'm trying to identify the count of objects a user updated during a given day, but only where the user was the final update.

Each object can have multiple updates in a day from any user, but the final update is the one that should be counted.

Each object only has 1 status though--always as a result of the most recent update.

Object_Update_Table

|UpdateID| UserID |timestamp| ObjectID |
| 1      |   3    | today   |    1     |
| 2      |   3    | today   |    2     |
| 3      |   1    | today   |    3     |
| 4      |   1    | today   |    3     |
| 5      |   1    | today   |    1     |

Object_Table

|ObjectID| Status  |
| 1      | failed  |
| 2      | failed  |
| 3      | progress|

Final desired output of query

|User |count "failed"|
| 1   |  1           |
| 2   |  0           |
| 3   |  1           |

This sort of thing can be done fairly elegantly using Window Functions.  While I was researching this answer, I found that MySQL doesn't support them.  The code that follows is will likely have some performance problems since it uses nested GROUP BY select statements.  Proceed with caution.

SELECT UserID
     -- potentially add a date column here
     , sum(Counter) Updates
FROM (
     SELECT UserID
          , ObjectID
          --- potentially add a date column here
          , 1 Counter
     FROM Object_Update_Table OUT inner join
          (
          SELECT ObjectID
               -- potentially add a date column here
               , max(timestamp)
          FROM Object_Update_Table OUT
          WHERE timestamp >= today 00:00:00
                AND timestamp < tomorrow
          -- if you add a date column, you'll need to add a GROUP BY clause with the date column here
          ) last_update_day ON OUT.ObjectID = last_update_day.ObjectID
                           AND OUT.timestamp = last_update_day.timestamp
     ) Join_Back_User
GROUP BY Join_Back_User.UserID
     --, potentially add a date column here

The case above only works for one day due to the WHERE clause in line 12 and 13.  If you need to do this for more than one day at a time, you should add a function which returns the date to the inner-most sub-query and then carry that date in the Join_Back_User subquery to the outermost query...


mongoaudit: Security through obscurity and how to limit connections from a specific subnet? I run mongoaudit on a server. Those are the failed results:

I don't wish to hide my version number. In my opinion this is Security through obscurity

  In security engineering, security through obscurity (or security by
  obscurity) is the reliance on the secrecy of the design or
  implementation as the main method of providing security for a system
  or component of a system. A system or component relying on obscurity
  may have theoretical or actual security vulnerabilities, but its
  owners or designers believe that if the flaws are not known, that will
  be sufficient to prevent a successful attack. Security experts have
  rejected this view as far back as 1851, and advise that obscurity
  should never be the only security mechanism.

what other MongoDB experts do?

Howto whitelist networks? I wish to do that, but only know about net.bindIp. This only say on which virtual NIC mongod listens. Are there options to restrict a subnet?

I don't wish to hide my version number. In my opinion this is Security through obscurity

Hiding version details is not security through obscurity unless that is the main security measure you rely on. Exposed version details can allow an attacker to easily identify known security issues rather than having to probe for them. Forcing an attacker to test for known exploits may be useful for an Intrusion Detection System to detect and handle obvious malicious attempt signatures.

I would consider this a low risk if you are following other best practices (eg. limiting network exposure, actively applying security updates, monitoring for intrusion attempts). Putting a reverse proxy in front of your MongoDB deployment may also add unwelcome operational complexity for server discovery and failover.

  Howto whitelist networks? I wish to do that, but only know about net.bindIp. This only say on which virtual NIC mongod listens. Are there options to restrict a subnet?

As you've noted, MongoDB's bindIP configuration only determines which NIC IP addresses the server listens to (not the client IPs that are allowed to connect). To limit network exposure you need configure solutions at the operating system and/or network infrastructure level (eg. firewalls or VPNs).

The current version of mongoaudit includes a link to some suggested resources depending on where your MongoDB deployment is hosted: How to Configure Allowed Hosts, Firewall, Whitelisting and Blacklisting in MongoDB.

For more information, also see Hardening Network Infrastructure and the Security Checklist in the MongoDB documentation.


Are triggers bad for created/timestamp columns? We are in need of some better auditing on some of our MySQL tables.  Specifically, we'd like to store when the row was inserted and when it was last updated.

Originally, I thought DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP would be perfect, but unfortunately our current MySQL is below version 5.65, so it only supports doing this on one column and thus doesn't meet our needs.

Thus, my next thought was that triggers would be a good solution.  However, when I ran this by our DBA, he said that triggers are normally a bad idea and that I should just update the values in the code every place there is an update or insert.  This works, but I don't like the fact that it leaves an extra thing for everyone who creates/updates records to worry about, and if they forget then the values will be incorrect.  Thus, to me this seems like exactly the case where triggers should be used.

Is this a bad use of a trigger and why?

As far as I know, this is the reason Triggers were made.
To handle an event related to a change running in the DB.
I don't see any reason why you shouldn't be using a Trigger.
P.S. please ask your DBA of why he doesn't like Triggers, and report back. Thank you.


sp_executesql processes uniqueidentifier-param with EntityCommandExecutionException but still acts like expected I use a Azure DB(MS-Sql) and administrate the DB with Management Studio for SQL Server 2016. My Application is a simple C#/WPF-Application.

So here we go, this is my SProc:

    ALTER PROCEDURE [dbo].[StartChangeTrackingProcedure]
(
@UID uniqueidentifier
)
AS
BEGIN
SET NOCOUNT ON
declare @sql nvarchar(max);

SET @sql = N'INSERT INTO TBL_ChangeTrackerPerLoggedUser
             VALUES (0,0,0,0,0, @UID)';
             PRINT @sql;
EXECUTE sp_executesql @sql, '@UID uniqueidentifier', @UID = @UID
END

The TBL_ChangeTrackerPerLoggedUser's name is a bit confusing as it has nothing to do with the ordinary ChangeTracking-Mechanism, it is just a simple table where you can look up wether there were made any changes to DB or not (because Azure has no Query Notifications, right?). It looks like this:

CREATE TABLE [dbo].[TBL_ChangeTrackerPerLoggedUser](
[ChangedTemplates] [bit] NOT NULL,
[ChangedInstances] [bit] NOT NULL,
[ChangedWF] [bit] NOT NULL,
[ChangedUser] [bit] NOT NULL,
[ChangedDoc] [bit] NOT NULL,
[UserDependency] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_TBL_ChangeTrackerPerLoggedUser] PRIMARY KEY CLUSTERED
(
[UserDependency] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

Next you'll see the call from Entitiy Framework, where LoggedUser.Instance.UsrID is a GUID:

 using (NCDBEntities DBContext = new NCDBEntities())
 {
     var uid = new SqlParameter("@UID", LoggedUser.Instance.UsrID);
     var res = DBContext.Database
               .SqlQuery<TBL_ChangeTrackerPerLoggedUser>
               ("StartChangeTrackingProcedure @UID", uid).ToList();
 }

Now, when i process this code I get an EntityCommandExecutionException telling me:

  Der Datenleser ist mit dem angegebenen Wert für 'NCDBModel.TBL_ChangeTrackerPerLoggedUser' nicht kompatibel. Ein Element vom Typ ('ChangedTemplates') weist keine entsprechende Spalte im gleichnamigen Datenleser auf.

Translated this means something like:

  Datareader is not compatible with the given value for 'NCDBModel.TBL_ChangeTrackerPerLoggedUser'. One element of type ('ChangedTemplates') has no actual column in equal named Datareader.

But when i take a look at DB at this point, SProc has been executed successfully:

So...is there any advice or explanation you can provide to understand this kind of magic? Should i simply catch this Exception and act like it never happened? Sideeffects etc.? Thank you!

You use an API that expects a result set (SqlQuery<...>(...)) but your stored procedure produces no result set. Hence the error message. The error occurs on the client side, after your procedure has executed, to explain the 'magic' you see.

You have several alternatives:

Use a non-query API, like ExecuteSqlCommand
have your stored procedure produce a result set, using the OUTPUT clause of INSERT.
Map the stored procedure in EF


How to append some letters or numbers to already existing data I would like to append some data to already existing data, something like
720967537 to 255720967537. All the data in my column is in that format. Is there any way I can do that to all of the records that are in my MySQL database?

You can use the CONCAT() function:

select concat(255, 720967537), concat('my ', 'test')

Results:

255720967537, my test

SQL Fiddle with Demo

If you want to UPDATE the values in your table, you could do the following:

update t
set col1 = concat(255, col1)
  , col2 = concat('my ', col2);

See SQL Fiddle with Demo


Table design for changing forms I'm a programmer, I work on an application where our members (about 3,000) enter in data into a form on a quarterly basis.  The form itself has about 120 fields, but every quarter some new fields are added and some are removed.

Is it a better for performance to have a data table with four columns (member_id, quarter_id, field_id, value) where each member adds 120 or so rows every quarter, or to have a flat table with 120+ columns, where new columns are added for new fields, and each member only adds one row per quarter?

Currently we're doing it like the latter, and we have about 67,000 records in it.  I feel like the former is cleaner and simpler from a programming perspective, but it'd mean expanding that to around 8 million rows, and increasing by 350k+ every quarter.

I'm with you: better to have a more flexible system than having to change your table structure every year. 8M rows is not excessive, just make sure you index it properly based on how you search/retrieve the information, and you'll be fine.

There's another way to go that would probably fit your use case better: document store databases. Switch to MongoDB or RethinkDB and flexibility issues disappear.


Query to compute unspecified values in "Other" category The problem that we are trying to solve is explained herein using a simple (and deliberately sub-normalized) table.
The table is defined as follows:
create table expense_profile
(
  id  INT UNSIGNED AUTO_INCREMENT,
  name  VARCHAR(10),
  category  VARCHAR(16),
  percentage DECIMAL(15,2),

  PRIMARY KEY(id)
);

The table is populated as follows:
INSERT INTO expense_profile(name, category, percentage)
VALUES('Alice', 'Housing', 15), ('Alice', 'Transportation', 5),
('Alice', 'Food', 25), ('Alice', 'Utilities', 5),
('Alice', 'Healthcare', 5), ('Alice', 'Personal', 3),
('Bob', 'Transportation', 7), ('Bob', 'Food', 18),
('Bob', 'Healthcare', 8), ('Bob', 'Personal', 20);

In this table we have accounted for 58% of Alice's expenses and 53% of Bob's expenses.  Implied therein is that 42% of Alice's expenses and 47% of Bob's expenses are in the "Other" category.

Q) Is there a query that would allow us to list, for the specified user, all the categories, including the "Other" category?

For example, we would like to have the following output for Bob:
+------+----------------+------------+
| name | category       | percentage |
+------+----------------+------------+
| Bob  | Transportation |       7.00 |
| Bob  | Food           |      18.00 |
| Bob  | Healthcare     |       8.00 |
| Bob  | Personal       |      20.00 |
| Bob  | Other          |      47.00 |
+------+----------------+------------+

The following query would accomplish what you're looking for:
SELECT name, category, percentage
FROM expense_profile
WHERE name = 'Bob'

UNION ALL

SELECT name, 'Other' AS category, 100.0 - SUM(percentage) AS percentage
FROM expense_profile
WHERE name = 'Bob'
GROUP BY name


Two relations to same table in SQL Server I've got two tables. When I delete a row in Table1, I want to set the ID of that row in all the rows of Table2 to NULL. I've got two relationships on Table1.

The problem is: I can only set one delete rule: Set NULL. If I try to set the other it fails. But when I try to delete ID 1 from Table1, this now also fails.

My table data looks like the following:

Table1:

ID  Text
1   Hoi
2   Hoi

Table2:

ID  ForeignID1  ForeignID2
1   1   2
2   1   1

How do I need to set my constraints, so it sets the Foreign IDs to NULL on Table2 when I delete the row in Table1?

It is not possible with a constraint. Have a look at Multiple Cascading Actions.

  No table can appear more than one time in the list of all cascading
  referential actions that result from the DELETE or UPDATE.

You can use an instead of trigger instead where you do the update in Table2 before the delete in Table1.

The trigger could look something like this

create trigger Table1_Delete on Table1 instead of delete
as

update Table2
set Table1ID1 = null
where Table2.Table1ID1 in (select D.Table1ID from deleted as D);

update Table2
set Table1ID2 = null
where Table2.Table1ID2 in (select D.Table1ID from deleted as D);

delete from Table1
where Table1.Table1ID in (select D.Table1ID from deleted as D);


mysql replica ignoring server-id I've setup a mysql replica slave a couple months ago. After a couple hickups lately, I've decide to restore it from a clean master export.

I'm trying to start my mysql instance with this command :

mysqld_safe --defaults-file=/etc/mysql/my_secondary.cnf --server-id=2 --log-bin --skip-slave-start --skip-name-resolve&

I've declared the server-id in my cnf file as the following :

server-id = 2

But when I connect with the command line client, and I check the variable, I get :

mysql> show variables like "version";
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| server-id     | 0                       |
+---------------+-------------------------+
1 row in set (0.00 sec)

I'm also getting the feeling that my instance is ignoring my "CHANGE MASTER" commands since it's complaining each time i run "start slave", but I would like to settle this server-id issue first.

I'm running two instance of Mysql 5.1.61-0ubuntu0.10.04.1 (one on port 3306, and this one on 4306)

As we suspected, you are making the changes on 4306 and connecting on default port which is the server running on 3306. use these :

mysql -u user -p --port 4306


Delete SQL Server logins on replicated server We have a production database which is replicated on another server at a remote location. The local database users (not logins) are also replicated in this database.  However, the SQL logins are not replicated from production to the remote server.

What is the best way to replicate "logins" to the remote server with passwords and all?

Here's my approach and I need to know if I'm going the right direction.

Need help with a script that will drop all logins on the replicated server.
EXEC [production server instance].[master].[dbo].[sp_help_revlogin] from the replicated server to get an accurate production list of logins.
Find a way to actually execute the results from sp_help_revlogin that will script out the logins on the replicated server with hash passwords, SIDs etc.

Any major problems with my approach?  If not, I could use some help actually writing the scripts or outline the exact process for 1-3 (made some attempts but have had problems getting the scripts to work).

I dont see a need for 1.

You can use below script to move logins from one server to another. It uses SQLCMD and xp_cmdshell.

You can also look for a PowerShell option if you dont want to use xp_cmdshell.

set nocount on
--  Author  :: Kin
--  Desc    :: Move Logins from one server to another
-- Version  :: 1.0 for dba.stackexchange.com
--  Date    :: 05/24/2013
-- Change your paths for the output from D:\logs\ to whatever suits your need

declare     @dbid tinyint,
            @SQLText varchar(8000),
            @destserver varchar(255),

set @destserver ='' --Insert your Destination Server NAme in here.
--1.Transfer Logins
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@@servername+' -E -Q"execute master.dbo.sp_help_revlogin" -oD:\logs\revloginout.sql'''
print @sqltext
--exec (@sqltext)
-- Create on Destination Server.
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@destserver+' -E -iD:\logs\revloginout.sql'''
print @sqltext
--exec (@sqltext)


Is Oracle 18c XE good enough to start learning to be a DBA? I don't know if the esteemed community here will find this question silly, but at the same time I thought that I would get a good answer to my question here.

As of now, I am into writing SQL queries/procedures, etc(dev work).
Additionally, in future I also plan to gain knowledge on DBA tasks(at least the basic ones).

I know that each DB is greatly different from another, but I would like to know which one is the easiest DB to learn DBA tasks.
By easiness I mean, I can install it on my PC and learn them without having to go to a school for training(As I lack time to do so).

Oracle is said to be the most widely used relation DB, but will Oracle 18c XE enable me to learn and perform basic DBA tasks(for DBs 12c and above)?

Another DB that I have on my radar is MySQL, if so, will installing the community edition help me learn things, Or do I have to go for the enterprise one?

The "First Commandment" of any DBA is: Thou Shall Not Lose Data!

To accomplish this, you'll want to teardown, build, and restore/recover the database multiple times.  To that end, I'd recommend you use some sort of virtualization (eg Oracle VirtualBox).

You might be able to find both Oracle EE and MySQL pre-made virtual guests for VirtualBox.

I believe RMAN on XE is limited.  So, for backup/recovery practicing, I'd use EE.

Beyond that, any edition (of any RDBMS) would do.


Purposefully fail select with no where We have an evil query that reads every row from a table, but should not.  To help the devs find the source of that query, I want to make SQL Server error out when any select against that table has no where clause.

An app is doing an evil select with no where.  The select reads every row of a table having about 6,500 rows.  The select is fast in SSMS (< 1 second), but when run by the app it's slow (about 1/2 hour).  We think that's because the app is slowly iterating over the result set.  It's an active table for updates and inserts, so other queries may be blocked while this read takes place.
The developers can think of no reason that any app should read every row of that table.  They suspect an oversight by a past developer.
The evil query-with-no-where is my top-runner, by far, for having the longest total duration in Query Store.
I know which app is issuing the evil query by using SQL Profiler to identify the app name, host name, and DB user name.  The app's developers have been unable to locate the source of the evil query.  I want to help.
The app is in C# and uses LINQ.  The evil query is exactly the same every time, with the same columns in the same order.  Simple equality could be used to determine whether the query being issued is the evil one.  The query is issued in its own batch without any other queries.
I want to cause any select against that table that has no WHERE clause (i.e. selects every row) to fail immediately.  Our hope is that the app will log the errors, or possibly an end user that triggered the activity will report a 500 error to us, and this will give us another clue to find what code is issuing the evil query-with-no-where.
Our other selects against that table have specific where clauses and only return a subset of the table's rows.  Those selects must be allowed to continue.
The query
SELECT
  [a].[Id],
  [a].[AccountingIntegrationActionTypeId],
  [a].[CreateDate],
  [a].[CreatedBy],
  [a].[FranchiseId],
  [a].[Message],
  [a].[ModelObject],
  [a].[ProcessId],
  [a].[RequestedBy],
  [a].[RequestedFor],
  [a].[Resolved],
  [a].[UpdateBy],
  [a].[UpdateDate]
FROM
  [AccountingIntegrationQueue] AS [a]

Given the evil query is long-running you don't have to stress out about trapping it synchronously with its execution. It would be good if the approach was relatively lightweight and easy to remove.
I'd go with an agent job, executed on a schedule likely to overlap with the evil query. Once every few minutes should suffice given the evil query runs for thirty minutes or so.
This job examines all currently-running work by querying sys.dm_exec_requests. If start_time is more than a few minutes ago you likely have your culprit. Find the actual SQL text for each piece of work.
declare @query   nvarchar(max);
declare @victim  smallint;

select
    @query  = s.text,
    @victim = r.session_id
from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as s;

A little bit of SUBSTRING(), CHARINDEX() and LIKE will confirm you're dealing with a "SELECT" without a "WHERE". If the evil query is submitted in a batch with other queries you'll have to be more careful when parsing the SQL text.
It can then be ended with KILL(@victim). There is a risk that, between being identified and killed, the evil query finishes and its session id is re-allocated to a "good" query. If evil's execution time is consistently long you can use start_time to reduce this risk. You know your environment and what's acceptable.


Is there a setting to force an error when asigning a varchar longer than a variable? I would like to get an error when assigning a longer varchar to a variable and no silent truncation. Are there any settings?

This truncates silently:

declare @code varchar(10)
set @code = 'This is too long'
select @code

Inserting a varchar into a smaller Table column

create table test (col1 varchar(10));
insert into test values ('This is too long');

yields an error:

Meldung 8152, Ebene 16, Status 14, Zeile 2
String or binary data would be truncated.
The statement has been terminated.

This should work for this problem:

declare @code varchar(10)
declare @input VARCHAR(200)
set @input = 'Set your input code here'

IF(LEN(@input) <= 10) -- make sure this number is same as length of 'code' variable
    SET @code = @input

ELSE
BEGIN
    SELECT 'Inputted string is too long for the variable.'
    RETURN
END

select @code


Which one is more efficient: select from linked server or insert into linked server? Suppose I have to export data from one server to another (through linked servers). Which statement will be more efficient?

Executing in source server:

INSERT INTO [DestinationLinkedServer].[DestinationDB].[dbo].[Table]
SELECT a, b, c, ... FROM [dbo].Udf_GetExportData()

Or executing in target server:

INSERT INTO [dbo].[Table]
SELECT a, b, c, ...
FROM OPENQUERY([OriginLinkedServer],
    'SELECT a, b, c, ... FROM [OriginDB].[dbo].Udf_GetExportData()')

Which one will be faster and consume fewer resourcers in total (both source and target server)? Both servers are SQL Server 2005.

Suppose I have to export data from one server to another.

Best is to use

IF you want all data use Backup / Restore; BCP OUT & BCP IN or SSIS
IF you want subset of data (some tables only) use SSIS or BCP OUT & BCP IN

TO move data, depending on the amount/size of data and n/w bandwidth, Linked server will kill the performance.

  Executing in source server Or executing in target server - Which one will be faster and consume fewer resourcers in total (both source and target server)?

-- Executing in source server:

INSERT INTO [DestinationLinkedServer].[DestinationDB].[dbo].[Table]
SELECT a, b, c, ... FROM [dbo].Udf_GetExportData()

This is called PUSHING Data as you are executing the query on source server and pushing the data into destination server. This will be expensive operation.

--- executing in target server

INSERT INTO [dbo].[Table]
SELECT a, b, c, ...
FROM OPENQUERY([OriginLinkedServer],
    'SELECT a, b, c, ... FROM [OriginDB].[dbo].Udf_GetExportData()')

This is called PULLING Data as you are executing the query on destination server and pulling data from source server. This will be a lot faster and less resource intensive compared to the former one (depending on how much data is being pulled).

In the case of the pull method, using SQL Profiler you will see that a single SQL statement is executed across the linked server (source server), and the resultset is pulled over from source server  to destination server which is a huge performance gain over PUSH method.

Another point to be noted is :

Between Linked server (4 part naming convention used servername.databasename.schema.tablename a.k.a Distributed Queries) and OPENQUERY, generally OPENQUERY will be fast. Why ?

For Linked Server - Query optimizer creates an execution plan by looking at the query nomenclature and breaks it into remote and local queries. Local queries are executed locally and data for remote queries are collected from the remote servers, scrubbed locally, combined together and presented to end user as single record set.

For OPENQUERY - Executes the specified pass-through query on the specified linked server. SQL Server sends pass-through queries as un-interpreted query strings to an OLE DB data source . Hence, SQL won’t apply any kind of logic on the query and won’t try to estimate what that query would do, it would simply pass the specified query as it is to the target linked server. Open queries are useful when you are not referencing multiple servers in one query. It’s generally fast as SQL does not  break it into multiple operations and does not perform any local action on the output received.

Excellent reading references :

Linked servers and performance impact: Direction matters!
Distributed Queries
OPENQUERY


SQL Server Standard Edition - Limitation The below SQLServer License Edition comparison URL does NOT mention any limitation about the number of databases supported within an AlwaysOn Failover Cluster Instance - for standard edition. Is it safe to conclude that there is no enforced limits on number of databases supported within a single AlwaysOn FCI for Standard Edition License?
I get that for standard Edition there is limitation of 2 nodes within a single SQLServer FCI. I also understand for Standard Edition's Basic Availability groups there is limitation of ONE Database per Availability group.
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-ver15
Just to give a little background info, Our application & database design is such that, we have one database per client. we put multiple small clients' DBs (say 10 of them) into a single SQL Server instance.
Our present goal is just HA and there is no need for DR scenario and there is no need for ReadOnly secondary copy of database which AGs provide.
I certainly do not want to manage, say 10 different Basic availability groups (for 10 databases) within the same SQL Server instance for achieving HA goal. Hence avoiding the popular AGs and exploring SQL Server FailOver Cluster instance.

This is second question...
Additionally, what if we bring Software Assurance into the mix. The SQLServer 2019 Licensing guide
says, with Software Assurance we get HA, DR (and an Azure) copy for free but there is no mentioning of Ent or Std edition license in this context.
Does the Standard edition's - 2 nodes cluster limitation still applies for AlwaysOn Failover Cluster Instance, even with Software Assurance? Can we have a 3 node SQL FCI cluster with Std edition license if we also buy Software Assurance? If so, that would be icing on the cake (Primary, HA & DR nodes within same SQL FCI !)
Any help here is greatly appreciated. Thanks!

As has been exhaustively commented, licensing questions are out of scope, however for technical clarification...
AlwaysOn is built on top of Failover Clusters, but is distinct from Failover Cluster Instances (FCI).  The difference mostly comes up with whether they share storage or not.
This link https://straightpathsql.com/archives/2019/02/basic-availability-groups-myths-truths/ does a good job of dispelling / clarifying basic availability groups.
YES, basic AG only allow a single database.  HOWEVER, you can have many, many availability groups with a database each.  This does complicate things a bit as it means that the databases won't necessarily move in sync with each other.  But you get what you get.
Given your scenario, I don't think managing one AG per database would be terribly problematic.  You are going to have to script your failover commands but checking AG health through TSQL should let you keep an eye on the herd (flock?  gaggle?) of AG's.  I wouldn't stress about it.
As for using Failover Cluster Instances instead of AG's, go for it.  They have no limitation and if you just want HA then it's easier to manage as you don't need to sync logins, jobs, etc.  It does mean that you must rely on your storage layer's HA rather than potentially having completely separate HA, but using FCI will allow you to do rolling upgrades and survive compute (CPU, memory, O/S) failure.


MySQL Exec_Master_Log_Pos value greater than Read_Master_Log_Pos I am trying to repair one MySQL replication setup. One of the database's table at slave side is deleted and the updates from master side are received but because of missing table these updates are not written to the database. This setup is not repaired since many months and the slave has much to catch up. I observed that Exec_Master_Log_Pos value is greater than Read_Master_Log_Pos which should not the case. Is this some kind of bug? I am guessing that it will be back to actual normal when the slave catches up with the master. Anybody familiar with this situation?

This is a classical scenario I have seen dozens of times in my DBA career. Before I explain this, I want to share something with you. I learned something a long time ago about positions within a binary log. It actually represents the number of bytes up to a specific place when recording a SQL transaction.
EXAMPLE
When you first startup a mysql instance with binary logging enabled, you get an empty binary log. Each version of MySQL has a different size for an empty binary log (See my Feb 04, 2011 post MySQL master binlog corruption) Let's use MySQL 5.5 for this example. Here is the mysqlbinlog output of a empty binary log for MySQL 5.5 running on my Windows Desktop:
C:\MySQL_5.5.12\data>dir mysql-bin.000001
 Volume in drive C has no label.
 Volume Serial Number is 2C92-485B

 Directory of C:\MySQL_5.5.12\data

08/16/2012  02:29 PM               107 mysql-bin.000001
               1 File(s)            107 bytes
               0 Dir(s)  191,225,950,208 bytes free

C:\MySQL_5.5.12\data>mysqlbinlog mysql-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#120816 14:29:55 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.12-log created 120816 14:29:55 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
IzwtUA8BAAAAZwAAAGsAAAABAAQANS41LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAjPC1QEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

The file is exactly 107 bytes and there is header info within it that marks end_log_pos 107. If you were to setup a slave that is newly installed, you would run
CHANGE MASTER TO
MASTER_HOST='IP_of_Master',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;

Then, run START SLAVE;. That's it. MySQL Replication takes off from there.
NOW, YOUR SITUATION ...
The only reason I have ever encountered your situation (MySQL Exec_Master_Log_Pos value greater than Read_Master_Log_Pos) is this (Still Writing)...
MySQL Replication functions with two threads

IO Thread : Responsible for Maintaining a Connection Back to the Master. It is used to collect SQL entries recorded in the Master's Binary Logs and storing those entries in the Slave's Relay Logs.
SQL Thread : Responsible for Reading the Entries from the Slave's Local Relay Logs, Executing the SQL, and Rotating the Fully Used Relay Logs.

Your problem has to do with the IO Thread. If there was any intermittency experienced by the IO Thread, it was most likely figuring out the size of the incoming SQL statement from the Master but never had the chance to actually read the SQL statement. I can say with absolute certainty because in the example posted above, the end_log_pos is read in the header of the SQL statement before the statement itself is actually read. This usually happens under these circumstances:

mysqld was restarted on the Master while replicating
Master DB Server crashes while replicating
possible dropped packets during the session between Master and Slave

REMEDY
The simplest remedy when this happens is to skip to the next binary log on the Master.
Suppose you have the following

running MySQL 5.5 on the Master
Read_Master_Log_File in SHOW SLAVE STATUS\G is mysql-bin.012345

The remedy is to run this (skip to the next binary log mysql-bin.012346)
STOP SLAVE;
CHANGE MASTER TO
master_log_file='mysql-bin.012346',
master_log_pos=107;
START SLAVE;

This will pick up from where the Slave last left off. This should be your solution in a low-write environment and if the Master has all the binary logs the Slave needs.
BUT WAIT...
you said it had been months since the Slave was broken. The next binary log on the Master may no longer exist. If you run CHANGE MASTER TO command right now, it will erase any binary logs you have now. What can you do ???
Unfortunately, you must rebuilt the Slave from scratch.
You would have to do the following on the Slave
echo "STOP SLAVE;" > MySQLData.sql
MYSQLDUMP_OPTIONS="-hIP_of_Master -u... -p..."
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases --triggers --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --master-data=1 --single-transaction"
mysqldump ${MYSQLDUMP_OPTIONS} >> MySQLData.sql
echo "START SLAVE;" >> MySQLData.sql
mysqldump -uroot -prootpass < MySQLData.sql

This will stop the slave, reload the data, and start the slave. The option master-data=1 will write the CHANGE MASTER TO command at line 23 of MySQLData.sql.
Give it a Try !!!


Mysql read only engine type and indexes I have a mysql table. This table has only three columns. Two are varchar (5),and one is varchar (32).
There will only be one query run against table.

Select (column 1),(column 2) from mytable where column 3= xxxxx

Table will be dropped once a month and recreated with fresh data. Other than that, this table will be read only, and only queried with the above query.

The column that is used in the query is not unique or null.
I assume that I would add a regular index to that column. Would there be any benefit to adding indexes to the other 2 columns?

The max number of rows would be around 5,000

Also, which table engine would be best?

If you solve it by adding a secondary index then imho MyISAM or InnoDB won't matter much, as both use B-Tree indexes.

Append both additional columns to the index: (col3, col1, col2) or (col3, col2, col1) should behave the same for your query.

The reason to add all the columns is that such index covers your query - all data needed to resolve the query are contained inside the index, so it is not needed to read the actual rows from the table. That means the data you want to fetch are grouped together on disk (the index is ordered/clustered by col3) and no random reads to the "main" table are needed.

If you had index on only (col3) then each row may be stored in a different part of the table (depending on your primary key or order of inserts) and it would randomly jump all over the place, incurring IO penalty.

If you have no primary key, then MyISAM would be probably better, as InnoDB would create internal primary key and the table would take more space than needed.

But there is a possibility to solve it without using any secondary key. Use InnoDB table like

create table t (
   col1 varchar(5),
   col2 varchar(5),
   col3 varchar(32) not null,
   col4 smallint unsigned not null auto_increment,
   primary key(col3, col4),
   key(col4)
) engine = innodb;

That way the table will use InnoDB feature of clustering by primary key - effectively forcing the optimal order and grouping of rows described previously. And it will take just a small bit more space for the additional column (2 bytes only, if you are positive there will always be less than 60k rows) and much smaller secondary key to accomodate the auto_increment behavior instead of effectively having 2 copies of the table as with the other version.

Example: http://sqlfiddle.com/#!9/c36cfc/2


In Oracle SQL Developer, how do I create or update a procedure and have the schema name be a variable: In Oracle SQL Developer, how do I create or update a procedure and have the schema name be a variable:

    define my_schema_name = 'schema_1';

    CREATE OR REPLACE PROCEDURE my_schema_name.updateDBUSER(
         p_userid IN DBUSER.USER_ID%TYPE,
         p_username IN DBUSER.USERNAME%TYPE)
    IS
    BEGIN

      UPDATE DBUSER SET USERNAME = p_username where USER_ID = p_userid;

      COMMIT;

    END;

define my_schema_name = 'SCHEMA_1';

CREATE OR REPLACE PROCEDURE "&my_schema_name".updateDBUSER(
     p_userid IN DBUSER.USER_ID%TYPE,
     p_username IN DBUSER.USERNAME%TYPE)
IS
BEGIN

  UPDATE DBUSER SET USERNAME = p_username where USER_ID = p_userid;

  COMMIT;

END;


sp_sqlagent_get_perf_counters SQL Server 2005 running every 20 seconds A little background info:

We are running SQL Server 2005 SP2 on a server with Microsoft Windows Server 2003 SP2.

When I run profiler I notice that EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters is running every 20 seconds.

Here is what the profiler is running every 20 seconds:

SELECT N'Testing Connection...'
go
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
go

What I have done so far:

I have deleted all the demo alerts on the system.

Possible answers:

I was wondering if a service pack 3 or 4 on 2005 might fix this issue?
or is there something I can set in the registry or in sql itself?
or is there a patch for Windows Server 2003 that might help?

Does it matter, unless it is sucking resources? As I understand it (from some searches), it will run anyway.

There is an MS Connect item for SQL Server 2008 for CPU usage, but it looks unresolved


Dumping and restoring tables in PostgreSQL - ERROR: extra data after last expected column I am trying to dump and restore multiple tables from a database on one server, to a different database on another server. Both are running Postgres 9.4 on linux.

The command for the backup was:

$ pg_dump -t table1 -t table2 -t table3 -t table4 dbname > backup.sql

Restore command:

$ psql new_dbname --set ON_ERROR_STOP=1 < backup.sql

The restore would start, but then I get the following error:

ERROR: extra data after last expected column

Tables are created, but there is no data.

After a bit of searching the error normally seems to pop up when trying to import CSV or other text files, but this is obviously not the case here.

Just to add some confusion, the restore works perfectly if I split the backup into 2 separate SQL files. One of the tables is much larger, so I did that one on it's own and the other 3 into a single SQL file.

Further info:

From Daniel's suggestion I have taken a look at the line where the error is occurring. There are 2 rows where the data looks bad. The first row is completely missing data in 3 fields. The other row has one field where the data does not appear as it should (almost as if the data has partly been taken from a different field).
Going back to the original database, these problems are not there, the data is fine.

What could be causing this problem in the SQL file?

Turned out to be a minor corruption of the SQL backup file.
After re-running the entire process again, the restore worked fine with no errors.

You would think that Postgres would have in-built checking to ensure integrity of exported data!


Primary key which is foreign key in an relationship is specified NOT NULL. Why? I have question about a foreign key.

Primary key which is foreign key in an relationship is specified NOT NULL in some book's example. Why?

I was thinking that primary key couldn't take on value "null" in an relation. Hence if it is a foreign key in a relationship it couldn't take on value "null" de facto. Otherwise such modification violates key constraints since foreign key has to be one of the values contained in the table it points to in attribute which is primary key.
Thank you very much.

A foreign key null means that there may not be an entry on the other side. In a master-detail relationship it means a detail can exist without a master.


sp_whoisactive - Do stats include activity for snapshot versioning? On a system that has read committed snapshot isolation enabled, do the reads, writes, CPU, etc. stats returned by sp_whoisactive include the activity associated with maintaining versions in tempdb?

I don't believe so, since none of the common DMVs used to track version store usage are present in the proc code.


Temporal Table that doesn't use system time We want to implement temporal tables in SQL Server 2016. We are creating a Datawarehouse and developing Type 2 Slowly changing Dimension Tables.

For the BeginDate, we want it to be dependent on transaction date and not the current getdate time. We are reprocessing a history of transactions. Example below, customer has gym or bank status, and goes from inactive, to active, or pending, dependent on transaction date.

We currently have this.

CREATE TABLE dbo.Department
(
    CustomerId int primary key,
    MembershipStatus int,
    TransactionDate datetime
 );

We we would like to create a table like this.

CREATE TABLE dbo.DepartmentHistory
(
    CustomerId int primary key,
    MembershipStatus int,
    TransactionDate datetime,
    BeginDatetime datetime,
    EndDatettime datetime
 );

Example usage would be:

first Customer Transaction on 3/5/2018 as Pending P

    +------------+--------+-----------+---------+
    | CustomerId | Status | BeginDate | EndDate |
    +------------+--------+-----------+---------+
    | 1          | P      | 3/5/2018  | NULL    |
    +------------+--------+-----------+---------+

second transaction is 4/21/2018 is Active A

    +------------+--------+-----------+-----------+
    | CustomerId | Status | BeginDate | EndDate   |
    +------------+--------+-----------+-----------+
    | 1          | P      | 3/5/2018  | 4/21/2018 |
    | 1          | A      | 4/21/2018 | NULL      |
    +------------+--------+-----------+-----------+

Temporal tables are system-versioned1, so the only way to "manually set" the timestamp of a given history row is to alter the OS time at the moment the row is modified which... you probably don't want to do.

If you want to "manually set" time bounds on a history table so that it supports temporal query syntax, you can do so by manually applying a temporal table on top of your existing data. This is a bit tricky and requires the underlying data to conform to the temporal history versioning rules. Google is peppered with various blog posts on this from back when Temporal Tables were introduced; since I haven't played with that exact use case in a while now I'll arbitrarily link off to this one example that looks promising.

Quickie Demo

Remember that a system-versioned temporal table is actually two tables - a "now" table and a "history" table squished together...

create table dbo.b_now (
     i int not null primary key
    ,info varchar(10)
    ,start_dt datetime2 generated always as row start
    ,end_dt   datetime2 generated always as row end
    ,period for system_time (start_dt, end_dt)
) with (system_versioning = on (history_table = dbo.b_history));
go

insert b_now (i,info)
values
 (1,'AAA')
,(2,'BBB');
go
update b_now set info = 'XXX' where i = 1
go
select * from b_history
select * from b_now
go

Note that while b_now appears to be the "base table" and supports temporal syntax - it is just a separate object. Removing the system versioning binding between the objects reinforces this.

Using this information to "manually version"

Keeping this in mind - and remembering that we can switch SYSTEM_VERSIONING from ON to OFF and back ON again, we can tell SQL Server to arbitrarily apply (and arbitrarily disregard) system versioning rules to an arbitrary pair of objects so long as...

the schemas match
the data conforms exactly as it would if it had been system versioned

  But I want example code!

Yea, I thought you might - so try this...

drop table if exists a_now ,a_history;
go
create table a_now (
     i    int not null primary key
    ,info varchar(10)
    ,start_dt datetime2 not null
    ,end_dt   datetime2 not null
        -- you'll want this CHECK later...
        check (end_dt = convert(datetime2,'9999-12-31 23:59:59.9999999'))
);
go
create table a_history (
     i        int not null
    ,info     varchar(10)
    ,start_dt datetime2 not null
    ,end_dt   datetime2 not null
);
go

declare @end_of_time datetime2 = '9999-12-31 23:59:59.9999999';

insert a_now values
 (1,'XXX','2017-01-01',@end_of_time)
,(2,'BBB','2017-01-01',@end_of_time)

insert a_history values
 (1,'AAA','2016-01-01','2017-01-01');
go
select * from a_now
select * from a_history

...Looks a lot like b_now and b_history, don't it? Too bad it's not a proper system table...

alter table a_now
    add period for system_time (start_dt, end_dt);
go
alter table a_now
    set (system_versioning = on (history_table = dbo.a_history));
go

Wait wut...? You can even plop a view on top of the "now" table to hide your abuse of the naming conventions.

create or alter view a
as
select * from a_now
go

select *
from a
for system_time as of '2016-06-01'

  Okay yea... but how do I add new rows Mr. Smarty Pants?

Very carefully... that's how.

alter table a_now
    set (system_versioning = off);
alter table a_now
    drop period for system_time;
go

declare @end_of_time datetime2 = '9999-12-31 23:59:59.9999999';

insert a_now values
 (3,'CCC','2018-01-01',@end_of_time);

update a_now set
     start_dt = '2018-01-01'
    ,info = 'YYYY'
where i = 1;

insert a_history
values
  (1,'XXX','2017-01-01','2018-01-01')

alter table a_now
    add period for system_time (start_dt, end_dt);
go
alter table a_now
    set (system_versioning = on (history_table = dbo.a_history));
go

select * from a for system_time as of '2017-06-01'

I leave it to the reader (you) to wrap up the INSERT/UPDATE/DELETE logic and relevant DROP/re-SET system versioning into a re-useable module.

1. N.B. this is hinted at with the syntax the period for SYSTEM_time in the required DDL for creating a temporal table


How to set the Tablix outer border width In SSRS 2008 R2, a Tablix has got a "Border Width" property. I assumed that by assigning a value to this property, I can define the outer border's width. But it does nothing.

I'd like to have the outer border of the Tablix thinner than the internal grids. There are many Tablixes on our reports with many cells and it is painful to select each cell on the top, bottom, left and right borders and set the outer border weight width to 3pt.

In your first screenshot, the BorderStyle value is None. In order for the BorderWidth setting to have an effect, the BorderStyle setting must be changed also (e.g. to Solid).

The borders in your second screenshot are cell/row borders. The weight of these borders are controlled via the BorderWidth setting of the cell or row.

The Tablix border is in addition to the cells borders, so if you want the Tablix to have a thinner border than the cells you will need to either use different colours (e.g. LightGrey for the cells and Black for the Tablix), or change the top/bottom/left/right border settings for each outer cell.


Oracle With Statement and Delete Together I am trying to use WITH statement and DELETE together. But I get ORA-00928 error.
Basically my code is like following

WITH TO_DELETE
AS
(
SELECT TableKey
  FROM SOME_TABLE
  WHERE SomeConditions LIKE '%'
)
DELETE FROM TO_DELETE

-- SELECT * FROM TO_DELETE

This code works in SQL Server 2005+ but gives error in Oracle. Any idea how can I solve this problem?

Following workaround exists.

use temp table and drop it.
use following inner select construct.

DELETE FROM
(
SELECT TableKey
FROM SOME_TABLE
WHERE SomeConditions LIKE '%'
)

What I wish to know is there any way using WITH statement?

The Oracle syntax is DELETE FROM (query). The query can be a SELECT with subquery factoring:

delete from (
   with to_delete as (
      select * from a
   )
   select * from to_delete
);

The above query works in 11gR2 but fails with ORA-01732 (non-updateable view) on an old 9iR2.