Need script for new login creation in sybase ase

There are many, many ways you could handle adding a list of logins/users to Sybase ASE, all of which include a mix of (Unix/Linux) shell scripting and T-SQL coding.

One simple example:

set passs=`cat /cis1/prod/dba/input/. ----`

cat addthese.txt | while read newlogin
do
isql -Usa -SPDSQTS1 <<EO_SQL > /tmp/error.out 2>&1
$passs
use master
go
if suser_id("${newlogin}") is NULL
    exec sp_addlogin ${newlogin},Sybase123
go
----------------------
-- repeat following block for each db of interest (eg, qts_db, engcomdb)
use <db>
go
if user_id("${newlogin}") is NULL
    exec sp_adduser ${newlogin}, ${newlogin}, eng
go
---------------------
-- rest of your coding
go
EO_SQL

... check for errors in /tmp/error.out ...

done

You could place all of the above into a shell script, make sure addthese.txt is correct/up-to-date, and then run the shell script.

Variations on the above could include:

putting the T-SQL code in a stored proc and then calling the stored proc for each new login
placing the above T-SQL code in a template.sql file with NEWLOGIN used as place holder, a shell script would then run sed 's/NEWLOGIN/${newlogin}/g' template.sql > runme.sql followed by isql -i runme.sql > error.out 2>&1

There are many variations based on which shell you're using and your coding style/preferences.


postgresql: how to know when a function was modified

Sure, you can use EVENT TRIGGERS and you can create them on any of these DDL events listed here

CREATE OR REPLACE FUNCTION abort_any_command()
  RETURNS event_trigger
 LANGUAGE plpgsql
  AS $$
BEGIN
  RAISE EXCEPTION '[%] user % attempt command % which disabled',
    current_timestamp,
    current_user,
    tg_tag;
END;
$$;

CREATE EVENT TRIGGER abort_ddl ON ddl_command_start
  WHEN TAG IN ('DROP FUNCTION', 'CREATE FUNCTION')
  EXECUTE PROCEDURE abort_any_command();

You can create the EVENT TRIGGER on ddl_command_end if you want an event to trigger ONLY after the operation has completed successfully. After you create an EVENT TRIGGER, you can try to drop the function and you'll see.

DROP FUNCTION abort_any_command();
ERROR:  [2017-01-09 20:27:33.843529-06] user ecarroll attempt command DROP FUNCTION which disabled

If you just want it in the logs, rather than to raise the exception then use a different level than RAISE EXCEPTION. You should also easily be able to make this INSERT into a table if you'd like.


How to copy a schema with another name but with same data?

It's a quite old database. Your SQL command creates new user.

You can use Export in order to copy contents of one schema to another.

exp FILE=$ORACLE_HOME/scott_export.dmp LOG= $ORACLE_HOME/scott_export.log OWNER=scott

Create new user as you have shown in your post and grant required privileges.
Then import it to new schema.

imp FILE=$ORACLE_HOME/ scott_export.dmp LOG= $ORACLE_HOME/scott_import.log FROMUSER=scott TOUSER=scott2

Details are here and here


Restore a Database into a Current Database and merge

If you have Visual Studio Ultimate or Premium, you can use "Data Compare" feature in Data menu. It can compare data between two tables and have ability to directly update your database or generate update script.


Fill in a table name using a variable from another query

Normally I'd advise against dynamic SQL as it can't be cached by the engine, but I don't see any other alternative that doesn't add a hurdle every time you wish to add a new one of these table_x's.

With that in mind, the following function will help you out:

create or replace function sum_table(
    tid integer
) returns numeric as $$
declare
    _out numeric;
begin
    execute 'select sum(honoraria_amount) from table_' || tid::text || ';' into _out;
    return _out;
end;
$$ language plpgsql;

And then to use it in a query:

with
    __tables as(
        select unnest(array[1, 39, 42]) as table_id
    )
select
    table_id,
    sum_table(table_id)
from
    __tables


How to check when statistics was last executed?

For your edit - You need to enclose table name into single quotes:

EXEC sp_autostats 'tablename'

and for the original question

First - find the statistics you want to check:

Second - see its properties, and there you will see the last updated timestamp:

Or you may want to execute the following query:

SELECT t.name TableName, s.[name] StatName, STATS_DATE(t.object_id,s.[stats_id]) LastUpdated
FROM sys.[stats] AS s
JOIN sys.[tables] AS t
    ON [s].[object_id] = [t].[object_id]
WHERE t.type = 'u'


Why do we have negative length for character maximum length for geography and varchar(max) data types?

The value -1 is a special value that means "maximum currently supported". The maximum length of e.g. max, xml, and clr types could change in a future version of SQL Server.

In system views like INFORMATION_SCHEMA.COLUMNS the CHARACTER_MAXIMUM_LENGTH column already uses NULL for types where a character length is not applicable, for example the numeric types.

If you are currently using INFORMATION_SCHEMA views, you should read this and then consider using SQL Server's own metadata views like sys.columns instead.

As a side note, the underlying type of the max_length column in sys.columns is smallint NOT NULL. It is the maximum length (in bytes, not characters) of the column, or -1 meaning 'maximum supported` as mentioned before.


problem is you backed up your databases using mydumper with -m or --no-schemas option, so the database create commands were not backed up.

So on restore you are not able to re-create the database tables prior to myloader reimporting data.


Accessing ORA_ROWSCN in the triggers body

It doesn't seem to be possible (even if Oracle itself populates the value before launching BEFORE UPDATE trigger) - referring to :NEW.ora_rowscn in the trigger body generates error, select ora_rowscn from table1 in the trigger causes mutating trigger error.


Prevent compilation of a stored procedure to a linked server

This can't be done. Deferred Name Resolution only works for table objects. All other objects must exist at the time of creation/modification. See this answer and this older documentation for further info.

As a workaround, you could do one of the following

Restore a copy of the database from the linked server to DEV and create a loopback linked server (the same name as PROD but points to DEV).
Script out the database objects from the linked server database, deploy this 'empty' database to DEV and create a loopback linked server. Alternatively, export a DACPAC (schema-only export) of the DB.
Remove references to the linked server and linked server DBs, essentially remove the 1st and 2nd parts of the four part names. This will only work if no columns in those tables are actually in use by the procedure.
If there is a DEV copy of the linked server, create a linked server in DEV to point to it.


Aborted restoring from back up Sql Server 2008

No there is no rollback.  You will need to restore from the most recent backup that you have.


How to transfer a few databases from MariaDB master to another master

The only thing that you can do is this: ServerB must become a slave to ServerA

Let's assume the following

ServerA1 has an IP of 10.20.30.40
ServerA1 has databases db1, db2, db3
ServerB1 has databases db4, db5, db6
ServerA2 is a Slave of ServerA1
ServerA3 is a Slave of ServerA1
ServerB2 is a Slave of ServerB1
ServerB3 is a Slave of ServerB1

Here are the Steps

STEP 01

Make sure ServerA and ServerB and their slave has unique server_id values

STEP 02

OPTION #1 : Dump from ServerA1

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT schema_name FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN "
SQL="${SQL} ('information_schema','performance_schema','mysql','test','sys')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL} 2>/dev/null"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --master-data=2"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} | gzip > DataFromServerA.sql.gz

OPTION #2 : Dump From a Slave of ServerA1

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT schema_name FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN "
SQL="${SQL} ('information_schema','performance_schema','mysql','test','sys')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL} 2>/dev/null"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --dump-slave=2"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} | gzip > DataFromServerA.sql.gz

Note: In option #2, you use --dump-slave. This tells a slave to publish its master's binary log file/pos to the output of mysqldump instead of its own.

STEP 03 : Get the Replication Log File and Log Position

less DataFromServerA.sql.gz | head -23 | tail -2

This will echo the CHANGE MASTER TO with the log file and position of the Master

For the sake of this example, say the output is

-- CHANGE MASTER TO MASTER_LOG_FILE='bin-log.001236', MASTER_LOG_POS=102938;

STEP 04 : Transfer Dump into ServerB1

STEP 05 : Load Dump into ServerB1

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
gzip -d < DataFromServerA.sql.gz | mysql ${MYSQL_CONN}

STEP 06 : Create Replication User for the Cutover

On ServerA1, run

GRANT REPLICATION SLAVE,REPLICATION CLIENT
ON *.* TO 'cutover'@'%'
IDENTIFIED BY 'cutover';

STEP 07

On ServerB1, run

CHANGE MASTER TO
master_host='10.20.30.40',
master_port=3306,
master_user='cutover',
master_password='cutover',
master_log_file='bin-log.001236',
master_log_pos=102938;
START SLAVE;
SLAVE SLAVE STATUS\G

NOTE: Please make sure there are no firewall issues

STEP 08 : Wait for Replication to Catch up

Keep running on ServerB1 SLAVE SLAVE STATUS\G until Seconds_Behind_Master is 0

STEP 09 : Stop your writes to ServerA1

After disabling all apps that write to ServerA1, run this on ServerA

SET GLOBAL read_only = 1;
FLUSH TABLES;
FLUSH LOGS;

STEP 10 : Point all the apps at ServerB1

STEP 11 : Disable MySQL Replication from ServerA1

On ServerB1, run

STOP SLAVE;
RESET SLAVE ALL;

STEP 12 : Decommission ServerA1, ServerA2, ServerA3

CAVEAT : Please try all this out on Staging Servers Before Doing So on Production

GIVE IT A TRY !!!


Introduce delay between master slave setup in MariaDB

The MASTER_DELAY option is currently scheduled to be in MariaDB 10.2.  There is an issue for the feature.

Until that is added to MariaDB, your best option is pt-slave-delay from Percona.  I've used it before and it is pretty easy to get going but since it isn't part of the mysql server, it's not as reliable.


Slow query that includes a WHERE clause with SUBSTRING

You could create two computed columns for this:

ALTER TABLE [dbo].[hd_acompanhamento]
ADD IsServiceRequest AS
    CASE WHEN SUBSTRING(dsacompanhamento,45,36) = 'requisição de serviço para incidente' THEN 1
    ELSE 0 END;

ALTER TABLE [dbo].[hd_acompanhamento]
ADD IsIncident AS
    CASE WHEN SUBSTRING(dsacompanhamento,45,36) = 'incidente para requisição de serviço' THEN 1
    ELSE 0 END;

And then index them:

CREATE INDEX IX_IsServiceRequest
ON [dbo].[hd_acompanhamento] (IsServiceRequest);

CREATE INDEX IX_IsIncident
ON [dbo].[hd_acompanhamento] (IsIncident);

Then you should be able to rewrite your query like this:

with cte as
(
    -- De Incidente para Requisição
    select
           cdchamado                       AS [Chamado]
          ,'De Incidente para Requisição'  AS [Tipo de Alteração]
          ,dtacompanhamento                AS [Alterado Em]
    from
        [dbo].[hd_acompanhamento]
    where
        IsIncident = 1
    union
    -- De Requisição para Incidente
    select
           cdchamado                       AS [Chamado]
          ,'De Requisição para Incidente'  AS [Tipo de Alteração]
          ,dtacompanhamento                AS [Alterado Em]
    from
        [dbo].[hd_acompanhamento]
    where
        IsServiceRequest = 1
)
select * from cte

This will let your query seek to those specific rows, rather than scanning the whole table running the substring function.

You may also want to add the columns cdchamado and dtacompanhamento as included columns for the index to avoid a key lookup.  I didn't include them by default, because I'm not sure of their data types.  If they are big text values, it might be better to just leave them as key lookups.

Another option if you only want the overhead of one computed column / index, you could combine them like this:

ALTER TABLE [dbo].[hd_acompanhamento]
ADD ServiceRequestType AS
CASE
    WHEN SUBSTRING(dsacompanhamento,45,36) = 'requisição de serviço para incidente' THEN 1
    WHEN SUBSTRING(dsacompanhamento,45,36) = 'incidente para requisição de serviço' THEN 2
    ELSE 0
END;

Then you could index just that one column, and use ServiceType = 1 and ServiceType = 2 in the two queries being union'd together.  Hat tip to Erik Darling for that idea.


SQL Server Latching from Multithreading/Parallel Inserts and Hash Partitioning

Queries that don't specify the partitioning column will need to touch all partitions. This is particularly an issue with a table partitioned using a computed hash column because the hash value isn't commonly specified in queries. Although you can specify the HashId value, it's not natural to do so. Examples:

--touches all 10 partitions
SELECT *
FROM dbo.UserEntries_RegularWithHash
WHERE Id = @Id;

--touches 1 partition
SELECT *
FROM dbo.UserEntries_RegularWithHash
WHERE Id = @Id
AND HashId = CAST(@Id % 9 AS TINYINT);

Latch contention against a non-partitioned table with an incremental key generally occurs only at very high insert rates.

Latch contention will not happen on a healthy machine at 120 inserts per second. David Browne-Microsoft suggested 10,000 inserts per second is the point at which latch contention is a concern. That's probably a good a SWAG as any, as it will depend on factors such as row size and hardware. I think one can assume a rate of several thousand per second will be needed before latch contention is a consideration. At that point you might consider In-Memory OLTP tables, and/or bulk/batch inserts.

I've heard it said that premature optimization is the root of all evil. In this case, I would certainly not introduce partitioning solely to avoid latch contention.


Unable to connect to SQL server 2008 R2 via Instance name?

You selected the default instance name as the name of your instance.

Instance Configuration

"If you specify MSSQLServer for the instance name, a default instance will be created. For SQL Server Express, if you specify SQLExpress for the instance name, a default instance will be created."

To connect to the default instance you don't specify the instance name, only the server name.


MySQL: Moving Database from Windows to Linux

You can try Percona wizard tool to configure MySQL server parameter based on Hardware and application in use using https://tools.percona.com/wizard
also there are other online tools available at https://cloud.percona.com/ all percona tools are free.

Some similar answsers to the questions can be found here:
https://stackoverflow.com/questions/11574401/mysql-monitor-queries

https://stackoverflow.com/questions/12026488/mysql-query-monitor-tool


SQL Server email function invalid syntax

You need to speficy the database where the table is:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBProfile',
@recipients = 'jupiter@love.com',
@subject = ' Search for empty ID result ',
@query = ' select * from [SomeDatabase].[dbo].[TT_INCI] tt where tt.ts_caseid =''(Auto)'' ',
@attach_query_result_as_file = 0

The error message is a bit misleading.


How to recreate Oracle db from .ora and .dbf?

The various data files that comprise the physical storage of the Oracle database instance can vary depending on the operating system, endian format, and pretty sure some of the files may have a hostname hiding in them somewhere.  And if those files were obtained from a database instance that was running, there is no way those files could be used to recreate a usable database instance, even on the exact same server as the files came on.

The *.ctl files are updated every 3 seconds or so with timestamps and pointers (oversimplifying but fairly accurate), so sorry, no way.


Is it possible that the old primary follow the new secondary?

On postgresql12:

systemctl stop postgresql-12.service

/usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf node rejoin --force-rewind --verbose -d 'host= psql02 user=repmgr dbname=repmgr connect_timeout=2' --dry-run


SQL Sever Transactional Replication on DB with deadlocks and blocking issues

Probably not a good idea to add transactional replication to an already struggling db, have you enabled read committed snapshot isolation on the database ? Assuming you have already added appropriate indexes this is normally this is a good place to start with blocking and deadlocking issues (assumes your application uses read committed the default transaction isolation level).


SQL Server has tooling to provide information on the deadlocks and what was happening at the time.  You can use TRACE FLAG 1222 to capture the details of the deadlock in the error logs.

Of course, you can make a better analysis tool by following Jonathan Kehayias's detailed discussion at:

Handling Deadlocks in SQL Server

In my environment I am always checking for deadlocks and other problems. I would recommend using extended events to capture and store the details of the deadlocks. By reading the XML data for the deadlock you can get better insight on the problems you are facing.

I would recommend using extended events to capture and store the details of the deadlocks.  For a GUI method of setting this up see:

Monitor Deadlock Using Extended Events in SQL Serve 2008 and Later

You will never be 100% free from deadlocks, but you can trim down the problems by using the captured data to resolve many of the deadlocks.


Tool to export data with all relational data?

In reality, you could just script it yourself using mysqldump.

Here my posts using the --where option for mysqldump:

Aug 15, 2011 : Is it possible to mysqldump a subset of a database required to reproduce a query?
Mar 14, 2012 : Can I mysqldump an entire database while using where='condition' on individual tables?
Jul 15, 2013 : Export just rows modified in last month (date defined by user)

With a good review of your tables and some SQL elbow grease, you can script the dump for any table and its child records with the corresponding foreign key references.

Give it a Try !!!


First, make sure you have a user in msdb associated with their login.

USE msdb;
GO
CREATE USER username FROM LOGIN loginname;

Next, deny execute on sp_help_job and sp_start_job for that user.

USE msdb;
GO
DENY EXECUTE ON dbo.sp_help_job TO [username];
DENY EXECUTE ON dbo.sp_start_job TO [username];

Next, create two wrapper stored procedures that execute as owner and call the above stored procedures with that hard-coded job_id. Here is the start version, the help version will look amazingly similar.

USE msdb;
GO
CREATE PROCEDURE dbo.start_that_job
WITH EXECUTE AS OWNER
AS
BEGIN
  SET NOCOUNT ON;

  EXEC dbo.sp_start_job @job_id = 'ACE1...';
END
GO
GRANT EXECUTE ON dbo.start_that_job TO [username];
GO

Now, they'll have to call that stored procedure directly, instead of using the UI.

Test it out:

EXECUTE AS USER = N'username';
GO
EXEC dbo.sp_start_job @job_id = 'ACE1...'; -- fails
GO
EXEC dbo.start_that_job; -- succeeds
GO
REVERT; -- this is important


SQL query to check if secondary database is removed from availability group

You could use exists with the sys.dm_hadr_database_replica_states DMV to see if the DB is in a AG.

  Returns a row for each database that is participating in an Always On
  availability group for which the local instance of SQL Server is
  hosting an availability replica. This dynamic management view exposes
  state information on both the primary and secondary replicas. On a
  secondary replica, this view returns a row for every secondary
  database on the server instance. On the primary replica, this view
  returns a row for each primary database and an additional row for the
  corresponding secondary database

Here is the query:

if exists(
    select
        DBName = db_name(s.database_id)
        ,s.is_local
        ,s.synchronization_state_desc
        ,s.synchronization_health_desc
    from sys.dm_hadr_database_replica_states s
    where db_name(s.database_id) = 'your_database'
    )
begin
--do something useful
end
else
begin
--exit, probably without error
end

This would need to query against the secondary if you aren't on 2014+. If you are, then you can add s.is_primary_replica = 0 to the query. Or you can use this method or a similar approach.


You don't need a source or a date in resultset at all. If so, you may use the simplest

SELECT domain, MAX(score) score
FROM (SELECT domain, score FROM tableA /* WHERE date = @date */
      UNION ALL
      SELECT domain, score FROM tableB /* WHERE date = @date */ ) dummy
GROUP BY domain

or (maybe more optimal)

SELECT domain, MAX(score) score
FROM (     SELECT domain, MAX(score) score
           FROM tableA
           /* WHERE date = @date */
           GROUP BY domain
      UNION ALL
           SELECT domain, MAX(score)
           FROM tableB
           /* WHERE date = @date */
           GROUP BY domain
     ) dummy
GROUP BY domain


What Host Name is my application connecting to SQL Server on

Assuming you can install the app on a Windows 7 (or higher) workstation you can perform the following steps to determine exactly what server and IP address the app is using:

Start a cmd.exe prompt "As Administrator"
Run "ipconfig /flushdns"
Run the app, and ensure it connects to the server
From the cmd.exe prompt, run "ipconfig /displaydns"

The output from step 4 will show all host names, along with the IP address(es) for those hosts that have been resolved in step 3.  Try to ensure nothing else is running between step 2 and step 4 to narrow down the results displayed in step 4.


How to get the DDL for Oracle object without extra characters

The following will get rid of the LF and 2 spaces using a function though it would probably be better/easier to do it in your text editor.

select
   REPLACE(dbms_lob.substr(
        dbms_metadata.get_ddl('FUNCTION','REMAP_SCHEMA', 'ETL'),
      37, 1), CHR(10)||CHR(32)||CHR(32)||'CREATE','CREATE') text
   from dual

Obviously the first word that you are interested in keeping would need to be 'CREATE' for this to work. You could also use one of the REGEXP functions but I don't see that being any better/different to this method.
I'm also not sure why you particularly need to get rid of them.


Joining three tables with null values in SQL

Try to use explicit join. It seems that MySQL has some issue referencing the implicit cross join.
select b.id, u.id, rating
  from books as b
   join
       users as u on 1=1
  left join ratings on u.id = userid and b.id = bookid


Adding a total sum below a column of prices, and not in its own column

You can use a GROUP BY GROUPING SETS over the NULL set

SELECT itemid, itemdescription, biddate, totalbids, sum(bidprice)
FROM f
GROUP BY GROUPING SETS ( (itemid,itemdescription,biddate,totalbids), () );
 itemid | itemdescription |  biddate   | totalbids | sum
--------+-----------------+------------+-----------+------
      1 | Frame           | 2016-08-24 |      3624 |   35
      2 | Car             | 2017-10-26 |      3624 | 3000
      3 | Stand Fan       | 2017-10-30 |      3624 |   29
      4 | Wooden chair    | 2016-10-25 |      3624 |   40
      5 | Black Sofa      | 2017-11-11 |      3624 |  400
      6 | Cabinet         | 2017-11-15 |      3624 |  120
        |                 |            |           | 3624
(7 rows)

Verified to work with PostgreSQL. SQL Server 2014 and SQL Server 2016.

CREATE TABLE f(
  itemid int,
  itemdescription varchar(255),
  bidprice int,
  biddate date,
  totalbids int
)
INSERT INTO f VALUES
    ( 1, 'Frame       ', 35  , '2016-08-24', 3624 ),
    ( 4, 'Wooden chair', 40  , '2016-10-25', 3624 ),
    ( 2, 'Car         ', 3000, '2017-10-26', 3624 ),
    ( 3, 'Stand Fan   ', 29  , '2017-10-30', 3624 ),
    ( 5, 'Black Sofa  ', 400 , '2017-11-11', 3624 ),
    ( 6, 'Cabinet     ', 120 , '2017-11-15', 3624 );

The docs at PostgreSQL are much better explaining this

Another method of doing this is with a UNION,

WITH t AS (SELECT * FROM f)
SELECT *
FROM f
UNION ALL
  SELECT null, null, (SELECT sum(bidprice) FROM f), null, null;

For your query, the first method would look like this..

SELECT Item.ItemId, Item.ItemDescription, Bid.BidPrice, bid.BidDate, sum(bidPrice)
FROM Bid
INNER JOIN Item ON bid.ItemId=Item.ItemId
WHERE BidDate between '2016-08-24' and '2017-11-15'
GROUP BY GROUPING SETS ( (Item.ItemId, Item.ItemDescription, Bid.BidPrice, bid.BidDate), () )


Workflow for SQL Server + Azure SQL development?

Developers should produce code, and there are obviously many ways to sync code changes (source control). Never touch the database. Ever. If they need any change in the database, modify the code that deploys/upgrades the database. Rails's Migrations are an excellent example of doing it the right way. Visual studio has Database Projects. You can always roll your own custom solution. Post-fact comparison tools I don't like much, but you can say is a personal opinion.


Are you looking to create a view like this:

SELECT  t1.employeeid ,
    t1.managerid ,
    t2.managerid
FROM    emp t1
    INNER JOIN emp t2 ON t1.managerid = t2.employeeid

If you want to get the employee hirarchy you should use recursive CTE. Check this article at SqlAuthority: SQL SERVER – Introduction to Hierarchical Query using a Recursive CTE.


Basically, to split up a huge table into smaller sub tables or partitions

offload data into different files/filegroups (with changes in backup/restore strategy too)
separate "working" from "historic" data (without having 2+ separate tables which complicates queries)
allow compression of older data

We're talking 100s of millions of rows and/or high volumes.

You don't partition for a few million rows.


I think you ran the query from 2 different databases (same server, but different databases) and are getting unexpected results due to the INNER JOIN condition.

My suggestion would be to just focus on one database at a time when analyzing indexes.


In my experience, the fastest way to get this done is to copy the backup up to Azure blob storage, then restore from there. The fastest way to do that move in the testing I've done is through the use of AZCopy. Copy to the drive that's already mapped to your VM and there's nothing you have to do special to then run the restore. At that point, it's just SQL Server doing it's thing.


By default remote access to the MySQL database server is disabled for security reasons. However, some time you need to provide remote access to database server from home or a web server.

Please refer the link


Fast and dirty:

 INSERT INTO dm.billables_links (billable_id,mobiuser_id) VALUES (
    (SELECT billable_id FROM dm.billables WHERE account_id = 32152
    AND billable_id IN (select billable_id from junk.billable_id),
    (SELECT id FROM public.ja_mobiusers WHERE name_first LIKE 'Alisha%'));

or a JOIN, if an relation between "billable_id"(junk.billable_id) and "id"(public.ja_mobiusers) exists.


Why is my SQL Server giving out of memory errors when there should be plenty?

Identifying large memory objects associated with MEMORYCLERK_SQLGENERAL may make this situation on November 18 more clear.
-- memory objects in MEMORYCLERK_SQLGENERAL
SELECT omo.type, omo.pages_in_bytes, omo.partition_type_desc, omo.memory_node_id
FROM sys.dm_os_memory_objects omo
JOIN sys.dm_os_memory_clerks omc
ON omo.page_allocator_address = omc.page_allocator_address
WHERE omc.type = 'MEMORYCLERK_SQLGENERAL'
ORDER BY pages_in_bytes DESC;

Recently I saw a case where automatic seeding of an availability group replica was generating errors on the remote side due to insufficient disk space. In that case, memory object MEMOBJ_DBCOPY grew to be quite large over time (MEMOBJ_DBCOPY is in MEMORYCLERK_SQLGENERAL).
Editing this to add ~~
Obviously, if MEMORYCLERK_SQLGENERAL just keeps growing it'll consume memory that could instead be used for other activities and performance will degrade over time.  At some point, OOMs will occur - for example maybe there won't be enough memory in the pool and SQL Server will inform of that with an error.
And there's one more threshold/behavior to be aware of.  When SQL Server "total server memory" is no longer in its initial growth after startup, and database cache declines to 2% of "target server memory" on any one SQLOS memory node (in this case looks like there is only one memory node), most if not all of the user worker threads will likely try to correct the situation and a large amount of CPU will be expended performing "free list stalls".
Microsoft mentions this briefly in this kb article (although the kb article doesn't shed any light on what might be causing MEMORYCLERK_SQLGENERAL to be growing so large in this case).
KB4536005 - Improvement: Fix incorrect memory page accounting that causes out-of-memory errors in SQL Server
https://support.microsoft.com/en-us/help/4536005/kb4536005-improvement-fix-incorrect-memory-page-accounting-that-causes
In some cases where SQL Server memory gets into internal trouble, using DBCC DROPCLEANBUFFERS, or DBCC FREEPROCCACHE, or DBCC FREESYSTEMCACHE ('ALL') can rescue the instance for the time being.  Unfortunately, not the case here.  Have to actually identify the memory objects involved in order to bring MEMORYCLERK_SQLGENERAL back under control.
Edited to discuss November 22 SQL Server log...
~~
In the November 22 memorystatus output, memoryclerk_sqlgeneral doesn’t seem to be of immediate concern: immediately after each of the many errors memoryclerk_sqlgeneral seems to be at or about the same size of 22040 kb. Other extremely interesting details: [system physical memory high] is 1(hinting SQL Server memory could grow) and [current committed] is less than [target committed] (also hinting SQL Server memory should be able to grow).
The full SQL Server log shows the increase of RAM in the instance.
11/18/2020 10:15:11,Server,Unknown,Detected 32409 MB of RAM. This is an informational message; no user action is required.

11/18/2020 15:26:01,Server,Unknown,Detected 64723 MB of RAM. This is an informational message; no user action is required.

We also see the modifications to [Max Server Memory].
11/18/2020 10:33:03,spid96,Unknown,Configuration option 'max server memory (MB)' changed from 300000 to 280000. Run the RECONFIGURE statement to install.

11/19/2020 11:34:10,spid94,Unknown,Configuration option 'max server memory (MB)' changed from 280000 to 60000. Run the RECONFIGURE statement to install.

Both 300000 mb and 280000 mb were too large for SQL Server to attain on a VM with 32409 mb of memory.  So SQL Server chose lower values of [target server memory] on the VM that it could attain, and managed memory based on those values.
What is likely not as intuitive is on the new VM, for reasons currently unknown, SQL Server also chose a lower target than the new [Max Server Memory] value of 60000 mb.  That can be seen in memorystatus sections like the following.
11/22/2020 09:06:59,spid69,Unknown,Memory Manager               KB
---------------------------------------- ----------
VM Reserved                               101157820
VM Committed                                 636280
Locked Pages Allocated                     51513180
Large Pages Allocated                             0
Emergency Memory                               1024
Emergency Memory In Use                          16
Target Committed                           58468304
Current Committed                          52149464
Pages Allocated                            50371824
Pages Reserved                               273864
Pages Free                                    35352
Pages In Use                                7855040
Page Alloc Potential                       50512960
NUMA Growth Phase                                 0
Last OOM Factor                                   3
Last OS Error                                     0

The [Target Committed] value here of 58468304 kb corresponds to what appears in perfmon as [Target Server Memory].  Its approximately 57098 mb.  Many of the errors have this value.  The other value I see in there for [Target Committed] is 57882440 kb, or approximately 56526 mb.
If the non-SQL Server workload on this VM is as expected (eg backup agent if there is one, other SQL Server related stuff like SSIS, SSAS, SSRS and any other apps including SSMS or ADS) I recommend setting [Max Server Memory] no higher than 56500 mb for this instance.  That may result in better memory management from SQL Server.
However, there's still the question: If memorystatus results show [system physical memory high] why hasn't SQL Server grown [Current Committed] closer to [Target Committed]?  Especially rather than triggering OOM type errors.  I don't have an answer for that, yet.
~~
aha! missed this before.
parsing MEMORYSTATUS results from the error log can be tricky.  the newest lines of the error log are at the top. But... the memorystatus results within each error log line separated with  should not be reversed like the error log lines.  First reverse full lines from the error log, then replace  with newlines.
Correct order is: clerk, VM Reserved, VM Committed, Locked Pages Allocated, SM Reserved, SM Committed, Pages Allocated.
11/22/2020 09:06:59,spid69,Unknown,MEMORYCLERK_SQLBUFFERPOOL (node 0)               KB
---------------------------------------- ----------
VM Reserved                                25686200
VM Committed                                 141312
Locked Pages Allocated                      1072140
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                            44537424
11/22/2020 09:06:59,spid69,Unknown,MEMORYCLERK_SQLGENERAL (node 0)                  KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                               22040

The easiest way to confirm this ordering is by using sqlcmd against the instance.
This should give the details for each clerk in the correct order.
sqlcmd -Q "dbcc memorystatus"
So scrolling all the way back to November 18 in the logs on Google Drive we find:
11/18/2020 15:10:48,spid51,Unknown,MEMORYCLERK_SQLBUFFERPOOL (node 0)               KB
---------------------------------------- ----------
VM Reserved                                12867644
VM Committed                                  75776
Locked Pages Allocated                       546740
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                            22821672
11/18/2020 15:10:48,spid51,Unknown,MEMORYCLERK_SQLGENERAL (node 0)                  KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                               22416

During the events on November 18, it wasn't MEMORYCLERK_SQLGENERAL that was over 21.75 gb in size.  That was MEMORYCLERK_SQLBUFFERPOOL, the database cache.  MEMORYCLERK_SQLGENERAL was a much more reasonable 22416 kb in size on November 18 during the OOMs.
There's some kind-of-good news in this: although this is something that will still take Microsoft's involvement to fix (because SQL Server really shouldn't throw OOMs when there's room to grow into [Max Server Memory]), if the problem persists this is the type of conditions in which clearing caches and dropping clean buffers can prevent needing to restart the service or having to reboot the VM.  I saw something similar - very similar actually - on SQL Server 2017 CU18.  Once-a-day we cleared caches and dropped clean buffers whie working with Microsoft to try to understand what was going on.  That's not ideal but its better than crashing.  Unfortunately, the problem magically went away before we could diagnose what was really going wrong.
This is pretty drastic for a band-aid but if the system becomes desparate...
CHECKPOINT
DBCC DROPCLEANBUFFERS -- drop all clean buffers from database cache
DBCC FREEPROCCACHE -- clear plan/procedure cache
DBCC FREESYSTEMCACHE('ALL') -- clear any other system caches

It's problably redundant to FREEPROCCACHE in addition to FREESYSTEMCACHE('ALL') - I never checked whether that's the case or if the FREEPROCCACHE lends some benefit of its own in that scenario.  Not something you'd want to do anymore than necessary since plans would have to be re-compiled and its kicking a lot lof stuff out of the database cache.  But if you get desparate while waiting to resolve the condition...


Change column datatype without rewrite when data remains unchanged

Like the documentation says:

  [...] changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception, when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt.

So the key is that the type conversion is binary coercible, in other words, you need a type cast between these types that is defined WITHOUT FUNCTION.

This is not the case for the data types you mention in your question, but if you are a superuser, you can create such a cast and then use it with ALTER TABLE:

CREATE CAST (date AS int4) WITHOUT FUNCTION;
ALTER TABLE mytable ALTER datecol TYPE int4 USING (datecol::int4);
DROP CAST (date AS int4);

Of course you have to understand the internal representation of date for that to make sense.

It is a good idea to remove the cast after you are done, because adding type casts to PostgreSQL can easily lead to ambiguities during type resolution, which will cause errors.


SQLCLR only allows for managed code / pure-MSIL Assemblies. So .NET Visual C++ might work, and I think I have seen one example of it.

You can load / call unmanaged C++ DLLs in an UNSAFE C# / VB.NET Assembly, but that can be riskier than using "unsafe" .NET functionality.

In theory, any .NET language should work IF it produces a pure-MSIL Assembly, but I have yet to see working examples of this. I have seen an example of Visual C++, and I have seen an example of someone coding actual MSIL (on Stack Overflow), though I'm still doubtful as to the supposed benefit of that.

Folks have tried F#, but can't doing that as SAFE since it requires another library for F#:

F# with sqlclr in a reasonably safe way and scripted assembly

As to the updated question, which now also asks about the Extended Stored Procedure (xp) API, that should be deprecated and frowned-upon.


I have a (partial) solution - what I did was the following (see the fiddle available here):
The algorithm I used for interpolating was

if there's a sequence of 1 NULL, take the average of the value above and the value below.

A sequence of 2 NULLs, the top assigned value is the average of the two records above it and the bottom assigned one is the average of the two records below.

In order to do this, I did the following:
Create a table:
CREATE TABLE data
(
  s SERIAL PRIMARY KEY,
  t TIMESTAMP,
  lat NUMERIC
);

Populate it with some sample data:
INSERT INTO data (t, lat)
VALUES
('2019-01-01 00:00:00', 5.07),
('2019-01-01 01:00:00', 4.60),
('2019-01-01 02:00:00', NULL),
('2019-01-01 03:00:00', NULL),
('2019-01-01 04:00:00', 4.7),
('2019-01-01 05:00:00', 4.20),
('2019-01-01 06:00:00', NULL),
('2019-01-01 07:00:00', 4.98),
('2019-01-01 08:00:00', 4.50);

Note that records 3 & 4 and 7 are NULL.
And then I ran my first query:
WITH cte1 AS
(
  SELECT d1.s,
    d1.t AS t1, d1.lat AS l1,
    LAG(d1.lat, 2)  OVER (ORDER BY t ASC) AS lag_t1_2,
    LAG(d1.lat, 1)  OVER (ORDER BY t ASC) AS lag_t1,
    LEAD(d1.lat, 1) OVER (ORDER BY t ASC) AS lead_t1,
    LEAD(d1.lat, 2) OVER (ORDER BY t ASC) AS lead_t1_2
  FROM data d1
),
cte2 AS
(
  SELECT
    d2.t AS t2, d2.lat AS l2,
    LAG(d2.lat, 1) OVER(ORDER BY t DESC) AS lag_t2,
    LEAD(d2.lat, 1) OVER(ORDER BY t DESC) AS lead_t2
  FROM data d2
),
cte3 AS
(
  SELECT t1.s,
    t1.t1,  t1.lag_t1_2, t1.lag_t1, t2.lag_t2, t1.l1, t2.l2,
    t1.lead_t1, t2.lead_t2, t1.lead_t1_2
  FROM cte1 t1
  JOIN cte2 t2
  ON t1.t1 = t2.t2
)
SELECT * FROM cte3;

Result (spaces mean NULL - it's much clearer on the fiddle):
s   t1  lag_t1_2    lag_t1  lag_t2  l1  l2  lead_t1     lead_t2     lead_t1_2
1   2019-01-01 00:00:00             4.60    5.07    5.07    4.60
2   2019-01-01 01:00:00         5.07        4.60    4.60        5.07
3   2019-01-01 02:00:00     5.07    4.60                    4.60    4.7
4   2019-01-01 03:00:00     4.60        4.7             4.7         4.20
5   2019-01-01 04:00:00             4.20    4.7     4.7     4.20
6   2019-01-01 05:00:00         4.7         4.20    4.20        4.7     4.98
7   2019-01-01 06:00:00     4.7     4.20    4.98            4.98    4.20    4.50
8   2019-01-01 07:00:00     4.20        4.50    4.98    4.98    4.50
9   2019-01-01 08:00:00         4.98        4.50    4.50        4.98

Note the use of the LAG() and LEAD() Window functions (documentation). I've used them on the same table, but sorted differently.
This and using the OFFSET option means that from my original single lat column, I now have 6 extra columns of "generated" data which are very useful for assigning values to the missing NULL values. The last (partial) piece of the puzzle is shown below (full SQL query is at the bottom of this post and also in the fiddle).
cte4 AS
(
  SELECT t1.s,
  t1.l1 AS lat,
    CASE
      WHEN (t1.l1 IS NOT NULL) THEN t1.l1
      WHEN (t1.l1 IS NULL) AND (t1.l2) IS NULL AND (t1.lag_t1 IS NOT NULL)
        AND (t1.lag_t2 IS NOT NULL) THEN ROUND((t1.lag_t1 + t1.lag_t2)/2, 2)
      WHEN (t1.lag_t2 IS NULL) AND (t1.l1 IS NULL) AND (t1.l2 IS NULL)
        AND (t1.lead_t1 IS NULL) THEN ROUND((t1.lag_t1 + t1.lag_t1_2)/2, 2)
      WHEN (t1.l1 IS NULL) AND (t1.l2 IS NULL)  AND (t1.lag_t1 IS NULL)
        AND (t1.lead_t2 IS NULL) THEN ROUND((t1.lead_t1 + t1.lead_t1_2)/2, 2)
      ELSE 0
    END AS final_val
  FROM cte3 t1
)
SELECT s, lat, final_val FROM cte4;

Final result:
s    lat    final_val
1   5.07         5.07
2   4.60         4.60
3   NULL         4.84
4   NULL         4.45
5   4.7           4.7
6   4.20         4.20
7   NULL         4.59
8   4.98         4.98
9   4.50         4.50

So, you can see that the calculated value for record 7 is the average of records 6 & 8 and record 3 is the average of records 1 & 2 and the assigned value for record 4 is the average of 5 & 6. This was enabled by the use of the OFFSET option for the LAG() and LEAD() functions. If you get  sequences of 3 NULLs, then you'll have to use an OFFSET of 3 and so on.
I'm not really happy with this solution - it involves hard-coding for the number of NULLs and those CASE statements will become even more complex and horrible. Ideally some sort of RECURSIVE CTE solution is required, but I  HTH!
===============================  Full Query ========================
WITH cte1 AS
(
  SELECT d1.s,
    d1.t AS t1, d1.lat AS l1,
    LAG(d1.lat, 2)  OVER (ORDER BY t ASC) AS lag_t1_2,
    LAG(d1.lat, 1)  OVER (ORDER BY t ASC) AS lag_t1,
    LEAD(d1.lat, 1) OVER (ORDER BY t ASC) AS lead_t1,
    LEAD(d1.lat, 2) OVER (ORDER BY t ASC) AS lead_t1_2
  FROM data d1
),
cte2 AS
(
  SELECT
    d2.t AS t2, d2.lat AS l2,
    LAG(d2.lat, 1) OVER(ORDER BY t DESC) AS lag_t2,
    LEAD(d2.lat, 1) OVER(ORDER BY t DESC) AS lead_t2
  FROM data d2
),
cte3 AS
(
  SELECT t1.s,
    t1.t1,  t1.lag_t1_2, t1.lag_t1, t2.lag_t2, t1.l1, t2.l2,
    t1.lead_t1, t2.lead_t2, t1.lead_t1_2
  FROM cte1 t1
  JOIN cte2 t2
  ON t1.t1 = t2.t2
),
cte4 AS
(
  SELECT t1.s,
  t1.l1 AS lat,
    CASE
      WHEN (t1.l1 IS NOT NULL) THEN t1.l1
      WHEN (t1.l1 IS NULL) AND (t1.l2) IS NULL AND (t1.lag_t1 IS NOT NULL)
        AND (t1.lag_t2 IS NOT NULL) THEN ROUND((t1.lag_t1 + t1.lag_t2)/2, 2)
      WHEN (t1.lag_t2 IS NULL) AND (t1.l1 IS NULL) AND (t1.l2 IS NULL)
        AND (t1.lead_t1 IS NULL) THEN ROUND((t1.lag_t1 + t1.lag_t1_2)/2, 2)
      WHEN (t1.l1 IS NULL) AND (t1.l2 IS NULL)  AND (t1.lag_t1 IS NULL)
        AND (t1.lead_t2 IS NULL) THEN ROUND((t1.lead_t1 + t1.lead_t1_2)/2, 2)
      ELSE 0
    END AS final_val,
    t1.lead_t1_2
  FROM cte3 t1
)
SELECT s, lat, final_val, lead_t1_2 FROM cte4;


Restore data from remote server using ".sql" file

You should gzip the file.

gzip DataFile.sql

Then, download it to the local machine.

Once it is on the local machine, run the following:

gzip -d < DataFile.sql.gz | mysql -uroot -p... ...

If you would rather import through the tunnel, try this on the remote server:

mysql -h<IP of your machine> -uroot -p... < DataFile.sql

If you local Linux machine has a firewall, run this

service iptables stop

then run the import. Finally, run this on your local box and put up the firewall

service iptables start


Is there a more satisfactory query to get the rows of 'A' ordered by the number of rows of 'B' associated with that row of A in SQL

You can either use the suggestion suggested by @Thomas Cleberg. Count([attribute]) will only count the rows where [attribute] is not null, whereas count(*) will count all rows

Another option is to do the count in a sub select:

select id, name, age, cnt
from (
    select A.ID,
           A.Name,
           A.Age,
           ( select Count(*) from B where B.AID=A.ID ) AS Cnt
    FROM A
) as T
Order By cnt Desc

I've added a nested level for convenience with the order by


TLDR version: the availability group listener points to the instance where the database sits, not the database itself.

Right now that that's out the way.
If I'm reading this right you have multiple databases set up with Always On High Availability(AOHA) across two (or more)instances

There are a few settings to consider here. Firstly the unchangeable, you never connect to a database directly, you connect to an instance and then to the database within that instance, if you want to be restricted to only access the one database you need to do that with user/group level permissions and have different accounts to access each database separately.

The setting you have control over is what happens with the non primary instance, there are a few options:
Connections in Primary Role (Allow all, Allow read/write)
Readable Secondary (Yes, Read Intent Only, No)

If you turn readable secondary to No, then you'll only ever be able to use the database if it is the primary node (note this will save on licensing as a server with all of the databases in this mode does not require to be licensed(few conditions))
If this is set to 'yes' or 'read intent only' then users connected to that instance will be able to see and read data from that database (obviously read intent only will only allow read intent connections).
The connections in primary role is used to re-direct read intent connections to the secondary node without having to know about the read-replica

On the note of separate listeners that depends on how you are using the databases, if they're completely separate and can exist within different instances (or their operations can be done with linked server connections) then being able to move the primary node to separate servers may allow you to unburden a server under heavy load. If the databases are dependant on being active on the same box and instance then there isn't much point in having multiple listeners as they will all always point to the same place which is ultimately the instance itself, it would be better to use different users (if possible) to lock off different databases (unless you wanted to do some really really complicated stuff with login triggers granting / removing permissions on the fly any time someone connects (which I would heavily not recommend and don't know if its actually possible)

Hope that helps


MS SQL Server 2012 Data Quality Services (DQS) installation failure

The solution was starting one of the installer scripts manually and starting installer again afterwards.
I've searched for recreate_schema.bat script on my PC, opened cmd, changed directory to the script location and started the script the way installer starts it...

cd C:\Users{my user name}\AppData\Local\Temp\j1bnpjct.k3b
recreate_schema.bat RS-1OT-45OAR200\MSSQL2012 DQS SQL_Latin1_General_CP1_CI_AS

It finished without errors.
After that I started installer again and it worked.
I hope this will help someone.


All manual settings get wiped out when saved in My.cnf, why?

I solved the problem with :

chattr -i /etc/xxxx/x/x/my.cnf


WITH ... AS statement not working as expected

The CTE (common table expression) is like a view and you need to treat it like any other view (or table). You need a complete SELECT statement as the second part of the EXCEPT:

with expensive_service as (
    select s1.*
    from service s1, service s2
    where s1.price > s2.price
)
select *
from service
except
select *
from expensive_service;

Note that parentheses around the individual parts of a UNION, EXCEPT or INTERSECT are not needed (unless you need an ORDER BY for only that query, e.g. because you are using a LIMIT clause).

So your original query could (should in my opinion) also be written as

select *
from service
except
select s1.*
from service s1, service s2
where s1.price > s2.price


Are there any outstanding critical security issues for Sql Server 2000?

Yes, there are no patches available for newly discovered issues like meltdown/spectre

Microsoft only releases security patches for SQL versions that are still supported. Also notice that if you are running a supported SQL version with an unsupported service pack, you MUST upgrade to a supported service pack to have an available security patch.

4057122 Description of the security update for SQL Server 2017 GDR: January 3, 2018
4058562 Description of the security update for SQL Server 2017 RTM CU3: January 3, 2018
4058561 Description of the security update for SQL Server 2016 SP1 CU7: January 3, 2018
4057118 Description of the security update for SQL Server 2016 GDR SP1: January 3, 2018
4058559 Description of the security update for SQL Server 2016 CU: January 6, 2018
4058560 Description of the security update for SQL Server 2016 GDR: January 6, 2018
4057117 Description of the security update for SQL Server 2014 SP2 CU10: January 16, 2018
4057120 Description of the security update for SQL Server 2014 SP2 GDR: January 16, 2018
4057116 Description of the security update for SQL Server 2012 SP4 GDR: January 12, 2018
4057115 Description of the security update for SQL Server 2012 SP3 GDR: January, 2018
4057121 Description of the security update for SQL Server 2012 SP3 CU: January, 2018
4057114 Description of the security update for SQL Server 2008 SP4 GDR: January 6, 2018
4057113 Description of the security update for SQL Server 2008 R2 SP3 GDR: January 6, 2018

Protect SQL Server from attacks on Spectre and Meltdown side-channel vulnerabilities


Since you are running out of space for the journal files, here is what the docs says:
journal

journal Default: (on 64-bit systems) true
Default: (on 32-bit systems) false
Set to true to enable operation journaling to ensure write durability
and data consistency.
Set to false to prevent the overhead of journaling in situations where
durability is not required. To reduce the impact of the journaling on
disk usage, you can leave journal enabled, and set smallfiles to true
to reduce the size of the data and journal files.
Note You must use nojournal to disable journaling on 64-bit systems.

journalCommitInterval

Default: 100 or 30
Set this value to specify the maximum amount of time for mongod to
allow between journal operations. Lower values increase the durability
of the journal, at the possible expense of disk performance.
The default journal commit interval is 100 milliseconds if a single
block device (e.g. physical volume, RAID device, or LVM volume)
contains both the journal and the data files.
If different block devices provide the journal and data files the
default journal commit interval is 30 milliseconds.
This option accepts values between 2 and 300 milliseconds.
To force mongod to commit to the journal more frequently, you can
specify j:true. When a write operation with j:true is pending, mongod
will reduce journalCommitInterval to a third of the set value.

For more clarification, please read Recover MongoDB Data following Unexpected Shutdown. You may need to run validate() on your data collections to verify integrity.
You should also read about Journaling
SUGGESTIONS

Looking back at this phrase If different block devices provide the journal and data files the default journal commit interval is 30 milliseconds.
You should make store the MongoDB journals on a different disk from the Data.
You may also want to shrink the journaling files if you haven't already done so.
You should see if the journal files are RAM-based. If they are and if you ran out of memory, that may explain your crash.
See Documentation on --smallfiles


I believe that the cast to XML is killing performance for you and what is happening is described by Paul White Compute Scalars, Expressions and Execution Plan Performance

The cast to XML is deferred to where you actually use the XML column so in your query the cast happens three times for each row returned.


Service Broker Error - The conversation handle is not found

You might want to try Microsoft's built-in ssbdiagnose utility.

The ssbdiagnose utility reports issues in Service Broker conversations or the configuration of Service Broker services. Configuration checks can be made for either two services or a single service. Issues are reported either in the command prompt window as human-readable text, or as formatted XML that can be redirected to a file or another program.

And possibly specifically this part:

To correctly analyze the configuration used by a conversation, run a ssbdiagnose configuration report that uses the same options that are used by the conversation. If you specify a lower level of options for ssbdiagnose than are used by the conversation, ssbdiagnose might not report conditions that are required by the conversation. If you specify a higher level of options for ssbdiagnose, it might report items that are not required by the conversation. For example, a conversation between two services in the same database can be run with ENCPRYPTION OFF. If you run ssbdiagnose to validate the configuration between the two services, but use the default ENCRYPTION ON setting, ssbdiagnose reports that the database is missing a master key. A master key is not required for the conversation.

Alternatively you might want to consider contacting Microsoft and opening a case with them.
Reference: ssbdiagnose Utility (Service Broker) (Microsoft | SQL Docs)


Time stored using now()::timestamp function stores wrong value

tl;dr

Instead of the data type TIMESTAMP WITHOUT TIME ZONE, define your column for the data type TIMESTAMP WITH TIME ZONE.
Set the default time zone of your session within PgAdmin to UTC.
To adjust the retrieved UTC value into a particular time zone in SQL, call AT TIME ZONE 'Asia/Kolkata'. (But better to leave that zone adjustment to your application code rather than your SQL.)

TIMESTAMP WITHOUT TIME ZONE

  to store date and time without time zone.

You are using the wrong data type when defining your column.

The TIMESTAMP WITHOUT TIME ZONE data type cannot represent a moment.

This type purposely lacks the context of a time zone or offset-from-UTC. So you have a date and a time-of-day, such as noon on the 23rd of next January. But we have no way to know if you meant noon in Tokyo Japan, noon in Toulouse France, or noon in Toledo Ohio US, all very different moments several hours apart.

Any time zone or offset information passed along with an input is ignored when reaching a column of type  TIMESTAMP WITHOUT TIME ZONE.

TIMESTAMP WITH TIME ZONE

The TIMESTAMP WITH TIME ZONE type can track a moment, a specific point on the timeline.

Any time zone or offset information passed along with the date and time-of-day is used to adjust into UTC. The zone/offset is then discarded. So if you care about the original zone/offset, you need to explicitly store that in an extra column.

When retrieving a TIMESTAMP WITH TIME ZONE value, the value will always be in UTC (an offset of zero hours-minutes-seconds). However, beware of tools, middleware, or drivers carrying the anti-feature of injecting some default time zone or offset onto the retrieved value. This creates the illusion of that zone/offset having been stored when in fact Postgres always stores in UTC values of the data type TIMESTAMP WITH TIME ZONE.

PgAdmin is one of the tools with this unfortunate anti-feature. I suggest always setting the default zone of the session to UTC to see your retrieved values as they were stored.

Adjusting to time zone

I suggest you learn to think and work as a programmer in UTC. Most of your business logic in programming should be in UTC. Adjust into a time zone only for presentation to the user or where business logic demands. So this means that in most cases you should adjust to time zone only in your app code rather than in your SQL and database admin tools.

AT TIME ZONE function

But if you insist on adjusting to time zone with your SQL, use the AT TIME ZONE function.

You mentioned the offset of five and a half hours ahead of UTC, +05:30. I assume you mean time in India. Always prefer a real time zone name rather than a particular offset. Politicians frequently change the offset used by their jurisdictions. So hard-coding an offset could lead to faulty results.

Keep the tzdata file in your Postgres installation up-to-date, and then let Postgres determine the offset appropriate to the named time zone for that particular moment.

… AT TIME ZONE 'Asia/Kolkata' …

Various date-time data types

This chart I made for Java programmers may also be of use here, to better understand the various data types defined by the SQL standard.


What is the max length of oracle user passwords?

Quote from the manual

  Passwords can be at most 30 bytes long

(Emphasis mine)

So the answer to the question "Can I change that" is: no


Deleting duplicate based on latest datestamp entry

You can use a CTE to add a row number that starts at 1 from the newest datestamp for each e-mail address, then delete all the rest.

;WITH x AS
(
  SELECT email, origin_date, new_opt_in_date, datestamp,
    rn = ROW_NUMBER() OVER (PARTITION BY email ORDER BY datestamp DESC)
  FROM dbo.tablename
)
DELETE x WHERE rn > 1;


Not sure I understand specifically what your question is or what exactly you're trying to do, but take a look at this BOL reference on int, bigint, smallint, and tinyint.

int

Size: 4 bytes
Min: -2,147,483,648
Max: 2,147,483,647

bigint

Size: 8 bytes
Min: -9,223,372,036,854,775,808
Max: 9,223,372,036,854,775,807

If you're asking about what are the upper limits of a sequential number (for instance, an IDENTITY column) then the max rows will be the Max values shown above, provided an initial value of 1.


Who is "focker" and why does he own my schema?

Unfortunately, I can only answer the question in the title.

Here's an excerpt from an article on Compose.io that might shed some light on your question:

  As LXC {Linux containers} doesn't have a nice command line API, Compose has developed its own internal tool. As Docker started as a nice command line for LXC, Kurt noted that Compose's tools were now named, tongue firmly in cheek, Focker. Focker is where all the tools are bundled up and, unlike Docker which focuses on packaging, it concentrates on the resource allocation and managment. The container instances are then connected via Open vSwitch which allows each customer to have their own private VLAN for their database.

Bottom line - "focker" represents the command line API "user" of Compose.io.

In yet another article, we can see the following:

[snapshot] more README

This snapshot is meant to be run with the same minor version
of Postgresql, which is postgres (PostgreSQL) 9.4.5.

To startup a Postgresql environment with this snapshot, run:
`postgres -D conf`

You can then connect to the db by running: `psql postgres -U focker`

  ...

My best bet would be trying: psql postgres -U focker


Changing SQL Server service account passwords enterprise-wide

Option 1

I would probably take advantage of using a Central Management Server to run the password change script across all the different servers at once. This should provide an easy method for verifying the output from each server while keeping it easy to manage by executing once for all target servers.

Option 2

If I was doing this in a tighter change management system, then I might consider scripting it as a batch file and having a sqlcmd execution output a separate log file for each server. This would verify the changes and keep record of them more reliably than doing it in SQL Management Studio.

I don't think there is necessarily a "right" method here, just depends on what meets your requirements best.


Sql Server 2008r2 Replication Snapshot Agent Hang

I've not set up Merge Replication before but I do have a fair amount of experience working with Transactional and Snapshot Replication.

This gets me every time I set up a new Subscription or Publication.  Make sure your passwords are correct.  I will typically get this same type error every single time, then I have to go and test to make sure they are correct, and guess what, even though I thought I entered them correctly, I didn't.

A good test is to try to connect to the SQL server using the account/password you are using in the replication environment.

Once you've made the change, if any, attempt to restart the desired replication agent.  I've occasionally had to restart the SQL Server Agent as well, if it was royally screwed up.

Hopefully this is a good starting place.  Best of luck in getting this resolved.

Regards,
Tom


How do I get PostgreSQL to keep inserting after unique index violation?

My first suggestion would be to try avoiding duplicates in the first place: Pentaho has "Unique Rows" and "Unique Rows (hashset)" steps to help with that.
First option would work fast but is only good for pre-sorted input.

The table output step with error handling should be capable of proceeding correctly past the error. Could you check if you have any steps past the output? (E.g., "abort on error" would kill the transform). Posting a log would also help a lot to identify the issue.

UPDATE:
In short, due to PostgreSQL internals, the default behaviour on error within transaction is different to some other DBs. It requires a formal rollback or commit before any other commands could be issued. This behaviour made it's way into the JDBC driver used by Pentaho -- as expected.

SOLUTION:
Update the JDBC driver to the later version (at least 9.4.1210, but I would recommend version 42+). Then in the connection properties in Pentaho go to Advanced and create property "autosave" with value "always". This will effectively ROLLBACK any failing statements by automatically creating a savepoint after every successful statement. For more details see: https://jdbc.postgresql.org/documentation/head/connect.html

This comes at a minor performance hit, but likely not huge. You still have to keep the Commit size at "1" otherwise the whole batch will happily fail and you'll miss data.


What is the difference between sys and system accounts in Oracle databases?

SYS

Automatically created when Oracle database is installed
Automatically granted the DBA role
Has a default password: CHANGE_ON_INSTALL (make sure you change it)
Owns the base tables and views for the database data dictionary
The default schema when you connect as SYSDBA

Tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS. Database users should not connect to the Oracle database using the SYS account.

SYSTEM

Automatically created when Oracle database is installed
Automatically granted the DBA role
Has a default password: MANAGER (make sure you change it)
Used to create additional tables and views that display administrative information
Used to create internal tables and views used by various Oracle database options and tools

Never use the SYSTEM schema to store tables of interest to non-administrative users.

/via


SQL - Select everything after a certain row

Perhaps something like this:
SELECT
  ...,
  (
    SELECT value
    FROM
      (SELECT MAX(mtsub.ID) maxid FROM mytable mtsub WHERE mtsub.type_ = 'STATUS' AND mtsub.id < my.ID) maxids
      INNER JOIN my_table mtsub2 ON mtsub2.id = maxes.maxid
  ) recent_status_value
FROM
  my_table mt ...

This should result in a column that has the most recently encountered status row's value - you can then where it for being equal to 40 in an outer query
Not a huge fan of "selects in the select list" coordination but if you don't have window functions available to help spread the most recent value of status over some rows..
Where'ing the mtsub queries to restrict their rows may help performance too; I left it out for clarity of code


Can spatial index be a part of a multiple columns index?

No, you can't.

An index, in MySQL, will either be

a BTREE single or multi-column index.
a HASH index (available only for MEMORY tables), that can span several columns.
a SPATIAL index (available only for MyISAM tables).
a FULLTEXT index (available for MyISAM and 5.6.4+ InnoDB tables), that can span several columns of CHAR, VARCHAR or TEXT type.

You can't combine these types.


PostgreSQL function definition "syntax error at or near +"

A LANGUAGE sql function must be a complete SQL statement (or more than one).

Yours is just an expression.

Just prepend SELECT, like you would when running it stand-alone.

$BODY$
SELECT (SELECT ...

The reason it complains at the + is that, surprisingly, it's legal to parenthesise a top-level query. This, for example, is a valid query:

regress=> (SELECT 1);
 ?column?
----------
        1
(1 row)

(I had no idea about that until your question, either, so thanks. I'm not even sure it's intentional...)

Note that you'd have got the same error if you ran your original query as-written, so I assume you'd prepended SELECT to it.

regress=> (SELECT 1) + (SELECT 2);
ERROR:  syntax error at or near "+"
LINE 1: (SELECT 1) + (SELECT 2);


Does trigger "see" all changes done in the transaction it was fired from?

Regular trigger  will see changes made in other tables within the same transaction. However, if you create the trigger (or other programmable object) with PRAGMA AUTONOMOUS_TRANSACTION, it will be executed within the scope of new transaction and won't be able to see "parent" transaction changes.


Restoring a MySQL database to a failed master

Slow ALTER in Master-Master

In Master-Master, each server is acting both as a Master and a Slave to the other server.

STOP SLAVE -- When you later do START SLAVE, the stuff that was performed on the Master will be applied to the Slave.  If you did, say, a big ALTER on a big table, that ALTER will run, taking a long time.  This is probably the "downtime" you are worried about?

The alternative is to do the lengthy process twice.

Stop client usage of B.
On B, turn off writing to the binlog
On B, do the ALTER(s), etc.
On B, turn on writing to the binlog
Wait for replication to catch up.  (It was probably blocked by the ALTER.)
Flip -- that is stop talking to A and start talking to B.
On A, (steps 2,3,4,5) binlog off, ALTER, binlog on, catch up

Alternatives

In Galera, this is "RSU" (as opposed to "TOI").

See also pt-online-schema-change -- this tool (percona.com) allows for doing time consuming maintenance on a table with essentially no downtime.

5.7 can now do many ALTERs with ALGORITHM=INPLACE, hence little or no downtime.

Putting the burden on the Code

Keep in mind that some code changes involve schema changes.  Sometimes they can be done in a "backward compatible" manner, thereby obviating stuff like this.

In the extreme, you might need to double up the code.  That is, have it capable of handing the old schema and the new schema at the same time.  This complicates the code, but eliminates coordination problems between code and schema.  (The code should eventually be cleaned up to throw out the 'old' variant.)

That last way becomes especially important when you have Sharding, multiple clients, or other cases of "lots of servers to update".


Why is this integer range selection so slow?

First thing stop using CTEs.

with structure as (
    SELECT s.id, s.terr_id
    FROM structures s
    WHERE s.id=55
)
SELECT r.id
FROM territories t
INNER JOIN structure ON (t.id = structure.terr_id)
LEFT JOIN resources r ON (
    t.rownum > r.rownum - 2
    AND t.rownum < r.rownum + 2
    AND t.colnum > r.colnum - 2
    AND t.colnum < r.colnum + 2
)

Get's rewritten as..

SELECT r.id
FROM structures AS s
JOIN territories t
  ON (t.id = s.terr_id)
LEFT JOIN resources r
  ON (
    t.rownum BETWEEN (r.rownum - 1) AND (r.rownum + 1)
    AND t.colnum BETWEEN (r.colnum - 1) AND (r.colnum + 1)
  )
WHERE s.id=55;

And equijoins are always going to be faster

They never fan and they're easy to optimize.
They're direct index lookups.


Error updating zoned field from SQL Server stored procedure linked server to iSeries

IBMi DB2 Numeric Data Types:
RPGLE - DESCRIPTION - DB2 SQL
P - Packed decimal - DECIMAL - DEC
S - Zoned decimal - NUMERIC - NUM


What are the compelling resons to update SQL Server name to a real one after its changing?

If you aren't using SQL Server Replication or remote jobs (MSX Jobs) then it really doesn't matter all that much.  It's a bit annoying when the server names don't match when you run select @@servername, but it's really not that big a deal for the most part.


That is weird. Two things I would check that can cause problems (in my experience):

1> make sure iptables is not running

2> make sure selinux is disabled (or permissive)

...at least in the beginning.

You can also try watching the network to see if things are going as suggested although I've never done this for mysql cluster specifically:

tcpdump -s0 -A -i any port 63132

Finally,

I noticed you forced a port number in the ndb mgmt config but not in the node configs. Is that going to be required info on the nodes? Try commenting that out in the mgmt node.


Is there any way to trace optimizer's work in DB2?

I don't know of a way to trace all the plans the optimizer considers. Obviously the EXPLAIN statement gives you the plan the optimizer eventually selects (and the re-written statement), but that doesn't tell you anything about the ones it doesn't select.

One way to get a little more info might be to set CURRENT QUERY OPTIMIZATION to different values (the default is 5), run EXPLAIN on the statement, then set it back to the default (5) and run EXPLAIN again.

If you get different plans with the other optimization levels, this may provide a clue as to the paths DB2 is taking in optimizing your statement (of course, if you get the same plan, it doesn't tell you anything!). I would suspect that the simpler the query the more likely you are to find no difference (so conversely this technique would work better with more complex statements).


Clustering vs. transactional replication vs. availability groups

The way I always like to visualize high availability solutions is the following:

SQL Server Failover Cluster Instance (FCI)

What is highly available?  The entire instance.  That includes all server-objects (logins, SQL Server Agent jobs, etc.).  This also includes databases and their containing entities.  It's a great solution for highly available SQL Server instances, because that is going to be the level of containment with this given solution.

What about reporting?  None, NULL, nonexistent.  A failover cluster instance has an active node delivering the cluster group containing the instance, VNN, etc. and all other nodes are passive, sitting idle (as far as the current cluster group is concerned) and waiting for a failover.

What happens when there is failover?  The downtime for an FCI is going to be determined by the amount of time that the passive node takes to grab the cluster resource and bring the SQL Server instance in a running state.  This is typically minimal in time.

Any client abstraction?  Yes, this is going to be innately built in with the virtual network name for the failover cluster instance.  This will always point to the active node that is currently delivering the SQL Server cluster resource.

AlwaysOn Availability Groups

What is highly available?  An availability group is going to be the logical containment of high availability here, whereas an availability group consists of a number of databases and a virtual network name (the listener, an optional cluster resource).  It is worth noting that server objects such as logins and SQL Server Agent jobs will not be part of the HA solution, and special consideration needs to be taken to ensure that these are properly implemented with an availability group.  Not an overly burdening requirement, but needs to be cared for.

What about reporting? This is a great solution for reporting, although I probably wouldn't use a synchronous replica as my reporting instance.  There are two commit relationships, synchronous and asynchronous.  In my opinion and from what I've seen in practice, is that your synchronous secondary replica is there waiting for a disaster.  Think of it as that replica that's ready to take a no-data-loss failover in the event of an issue.  Then there are asynchronous replicas that can handle that reporting workload.  You aren't using this replica as the aforementioned solution, but moreso for things like reporting.  Reporting workloads can be pointed to this replica (either directly, or indirectly through read-only routing via the listener).

What happens when there is failover?  For a synchronous commit secondary replica that is paired with automatic failover, this will be the replica role state change from SECONDARY_NORMAL to PRIMARY_NORMAL.  In order for there to be automatic failover, you need to have a synchronous secondary replica that is currently synchronized, and what's implemented is the Flexible Failover Policy to determine when in fact this failover should occur.  That policy is indeed configurable.

Any client abstraction?  Yes, you could optionally configure an AlwaysOn Availability Group listener.  This is basically just a virtual network name (can be seen through WSFC as a cluster resource in the AG's cluster group) that points to the current primary replica.  This is a key part of shifting your reporting workload around, as well as setting up a read-only routing list on any servers that you want to redirect ReadOnly traffic (this is set through the connection string, with the .NET Framework Provider for SQL Server, this will be the Application Intent parameter, set to ReadOnly).  You would also need to set a read-only routing URL for each replica that you want to receive this reporting workload while in the secondary replica role.

Transactional Replication

What is highly available?  This is arguable, but I'm going to say nothing.  I don't see replication as a high availability solution whatsoever.  Yes, data modifications are being pushed to subscribers but we're talking at the publication/article level.  This is going to be a subset of the data (could include all the data, but that won't be enforced.  I.e. you create a new table in the publisher database, and that will not automatically be pushed to the subscribers).  As far as HA goes, this is bottom-of-the-barrel and I will not group it in there with a rock-solid HA solution.

What about reporting?  A great solution for reporting on a subset of data, no question about that.  If you have a 1 TB database that is highly transactional and you want to keep that reporting workload off the OLTP database, transactional replication is a great way to push a subset of data to a subscriber (or subscribers) for the reporting workload.  What happens if out of that 1 TB of data your reporting workload is only about 50 GB?  This is a smart solution, and relatively configurable to meet your business needs.

Summary

What it boils down to are a handful of questions that need to be answered (partly by the business):

What needs to be highly available?
What does the SLA dictate for HA/DR?
What kind of reporting will be taking place and what latencies are acceptable?
What do we need to handle with geographically dispersed HA? (storage replication is expensive, but a must with an FCI.  AGs don't require shared storage from standalone instances, and you could use a file share witness for quorum potentially eliminating the need for shared storage)


Firstly I want to introduce three tools for use CDC in MySQL.

They are about three popular CDC for MySQL/MariaDB.

Two of them are Free and one of them is commercial.

MariaDB Maxscale is commercial tools for Routing, Qeury Cache, Load balancing and also has CDC for use in MariaDB (I don't know is it work on MySQL or not)

Another Tools are Maxwell and Debezium. They are Free and Open source.

Debezium developed by RedHat Team and It has MySQL/Oracle/PostgreSQL and MongoDB Connector But its performance on the MariaDB database is not clear (Until now). Look at this link: https://issues.jboss.org/browse/DBZ-357

Usually CDC is used for Push changes data (producer) to Message Broker like Kafka and Pull from Another Tools such as Apache Solr orElastic and etc to use in other tasks.

for Maxwell, It has --producer switch To specify the place where the changes are made. You can set this to stdout (usually for test), Kafka and RabbitMQ

For Prepare MySQL to use for Maxwell

you need to Enable Binary Log and set to Row format.
Then Create a user which have grant SELECT, REPLICATION CLIENT and REPLICATION SLAVE
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE on . to 'maxwell'@'%';
Clone maxwell from github https://github.com/zendesk/maxwell
Start Maxwell and your producer bin/maxwell --user='maxwell' --password='XXXXXX' --host='127.0.0.1' --producer=stdout

You can config maxwell to Produce Specific Table or Database to specific Topic (kafka) or Channel (RabbitMQ).

You can start multiply Maxwell to Produce Multiply Tables to different Topic.

Pay attention Currently Maxwell doesn't have any Failover Solution and you should to handle this.

More Information:

http://maxwells-daemon.io/quickstart/

https://www.youtube.com/watch?v=eMCZAlGsNSA


Query does nothing except context switches

I'd like to understand what could have been happening during the 30 seconds when it seems to have been doing nothing.

If a query isn't using CPU, it's in a wait, which are tracked as the system-level in sys.dm_os_wait_stats.

SQL Server 2016 introduces a DMV for session-level wait stats with sys.dm_exec_session_wait_stats.

SQL Server 2017 introduces wait tracking at the query level in Query Store with sys.query_store_wait_stats.

Earlier versions can use Extended Events to see the waits for a particular session.  See eg: https://blogs.msdn.microsoft.com/sqlsakthi/2011/02/20/troubleshooting-slow-running-query-using-extended-events-wait-info-event/


What ports does SQL Server use on Linux?

Assuming the defaults, there are two ports SQL Server listens on which can be verified with netstat -ln,

ms-sql-s TCP 1433 for the server
ms-sql-m TCP 1434 for the Dedicated Administrator Connection which by default is enabled for local connections on 127.0.0.1 only. For more information see "remote admin connections Server Configuration Option"

While my install was only listening for TCP connections, /etc/services shows entries for UDP too,

ms-sql-s        1433/tcp                        # Microsoft SQL Server
ms-sql-s        1433/udp
ms-sql-m        1434/tcp                        # Microsoft SQL Monitor
ms-sql-m        1434/udp

The "Microsoft SQL Monitor" is the DAC connection.

The log also shows similar information,

spid19s     Server is listening on [ 'any' <ipv6> 1433].
spid19s     Server is listening on [ 'any' <ipv4> 1433].
Server      Server is listening on [ ::1 <ipv6> 1434].
Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].


The error says what to do, that sql_variant type does not accept to operate the divide function.

I would suggest to use CONVERT/CAST to a specific data type, depending on your usage.
Below I will use float:

DROP FUNCTION IF EXISTS dbo.SafeDivide
GO
CREATE FUNCTION dbo.SafeDivide
(
    @a sql_variant,
    @b sql_variant
)
RETURNS sql_variant
AS
BEGIN
     RETURN CASE
               WHEN @b = 0
                   THEN 0.00
               ELSE Convert(float, @a) / Convert(float, @b)
           END;
END
GO
/*
select dbo.SafeDivide(4,4.4622434)
*/


Backup Structured Data and Unstructured Data seperately in SQL Server - Is it Possible?

No, there's no such direct feature. You would work at the filegroup level, and at that level SQL Server doesn't care whether it is a filestream or something else you have in that filegroup.

And when you restore a part of the database, you need to have the primary file group (since this contains all metadata) and whatever other filegroups you want. The non-restored filegroups will be "defunct", and will forever have those "ghost" filegroups hanging around (there's no way to delete/remove a defunct filegroup).


All your stored procedure is doing is to put “select” in front of whatever is passed in. It doesn’t run anything.
But presumably you’re going to then ask something else to run whatever comes back, and that could be unsafe. If you’re wanting a very dynamic querying environment, then read what Erland Sommarskog has to say on the matter at http://www.sommarskog.se/dynamic_sql.html


You don't state the steps you take in each case so it is difficult to give specific analysis but three generic explanations for some of the difference spring to mind:

Rebuilding is an offline operation unless you are using Enterprise Edition, so it takes a table lock and does all the work before letting any other processes touch it. This means the process can optimise for itself and not care about concurrency or otherwise dealing with changes that happen during the process.
Reorganise is an online operation so it needs to be concerned with possible concurrent actions: optimising for concurrency often means doing more work than you otherwise would and if there is any concurrent activity it will contend with the process for IO bandwidth & throughput.
Rebuilding just throws away the existing index and rebuilds from the table data, reorganise is much more "clever" and moves & compacts existing pages which depending on the state of the page distribution, your I/O subsystem, how much of the index's pages are in RAM right now, and how much of the cluster/heap is in RAM, how fragmented the cluster/heap is on disk, if another index can be used to aid the rebuild, and a number of other factors, could make reorganise slower.
If you are not checking first (and just rebuilding) then you are skipping a chunk of the work that the first job does: you just rebuild instead of scan then rebuild or reorganise.

https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/ has a few other notes that you might find useful in understanding the difference more.

For an answer more specific to your particular case you'll need to update your question with more details of the maintenance plans that you are comparing.


Alter table modify column on a huge table containing more than 25 million records

You should only need to drop and recreate the affected NC index.

Saying that, on a test server, you can see the differences in doing this compared to your strategy of dropping all indexes above.

I reckon dropping/creating the single index would be quicker overall because the data will be shifted around twice otherwise: once char to varchar, another to build the clustered index. Then you have the NC creation overhead.


Not sure where you copied this code from, but it looks like it came from a web site that translated minus signs (-) to long dashes (–). You can see this in Management Studio if you turn IntelliSense on:

Here is the easy way to compare the same range as the year before:

DECLARE @startdate date = '20170101',
        @enddate   date = '20170212';

SELECT @startdate, DATEADD(YEAR, -1, @startdate),
       @enddate,   DATEADD(YEAR, -1, @enddate);

This compares the same period as last year, but I'm not sure that's what you're after since you are also using the zero date and datediff - not clear why. It does not account for leap year; you'll need to define what should happen if the range involves 2/29 either this year or last year.

When you use those dates in a query, don't do this subtracting 3 milliseconds nonsense - it only leads to problems. If you're trying to get all the data for February 12th even if it has time, then use < DATEADD(DAY, 1, @enddate). Also, don't use YY, just type out YEAR. It is also far easier to get the full range of last year:

-- for SQL Server 2012+
WHERE col >= DATEFROMPARTS(YEAR(@startdate)-1, 1, 1)
  AND col <  DATEFROMPARTS(YEAR(@startdate), 1, 1);

-- for older, unsupported versions:
WHERE col >= DATEADD(YEAR, YEAR(@startdate)-1901, '19000101'),
  AND col <  DATEADD(YEAR, YEAR(@startdate)-1900, '19000101');

To sum up:

DECLARE @startdate date = '20170101',
        @enddate   date = '20170212';

-- sales from that period this year:

SELECT SUM(some_column) FROM dbo.SomeTable
  WHERE some_datetime_column >= @startdate
    AND some_datetime_column <  DATEADD(DAY, 1, @enddate);

-- sales from same period previous year (again, not accounting for leap special case):

SELECT SUM(some_column) FROM dbo.SomeTable
  WHERE some_datetime_column >= DATEADD(YEAR, -1, @startdate)
    AND some_datetime_column <  DATEADD(YEAR, -1, DATEADD(DAY, 1, @enddate));

-- sales from all of previous year:

SELECT SUM(some_column) FROM dbo.SomeTable
  WHERE some_datetime_column >= DATEADD(YEAR, YEAR(@startdate)-1901, '19000101'),
    AND some_datetime_column <  DATEADD(YEAR, YEAR(@startdate)-1900, '19000101');

Suggested reading:

What do BETWEEN and the devil have in common?
Bad Habits to Kick : Using shorthand with date/time operations
#BackToBasics : Dating Responsibly


Is there a benefit/drawback to adding LIMIT 0,1 when performing SELECT on unique key field?

If MyKey is NOT NULL (see Jannes answer) and defined as a UNIQUE index in the table DDL, the only benefit of adding LIMIT 0,1 is potential clarity to viewers of the query that this is only going to return a single result. Without them having to look at the DDL to know that it is unique. However, other Database designers will be looking at the DDL anyway.

If there is any overhead of adding LIMIT 0,1 it would be very, very minuscule.

Either way, adding it would be up to you. Do you need the clarity (how often do users view your queries)?


How do you change the location of the data files in SQL Server 2000?

If you can plan some downtime, I would unattach the database from the server, move the physical files and then reattach the database.

Right click on the database --> all tasks --> detach database.
Move files
Right click on 'Databases' --> all tasks --> attach database.

The other option you have is to add another data file on the b: drive (right click on database --> properties -- > data files). Once you do that, you can move some of your tables over there. Easiest way to do that is to do 'select into' on the second drive, then do a rename of the first and second table.


Proper storage size estimation technique for PostgreSQL

For point 1), you need to read the Storage Page Layout chapter of the documentation and in particular consider the HeapTupleHeaderData Layout table for the metadata at the row level.

The 4-bytes per-row OID is obsolete for user tables. PostgreSQL no longer have them by default since 8.1. This is now controlled by the default_with_oids config parameter or the WITH(OIDS) clause of CREATE TABLE.

When a significant part of the data is live (gets frequently updated), estimating the disk size is harder because UPDATEs are equivalent to INSERT new version followed by DELETE old version, followed hopefully by reuse of the unused space whenever possible. There is also the fillfactor storage option for tables that come into play here (see the storage parameters section in CREATE TABLE)

And there is bloat at the index level, too. For write-heavy tables, it's not uncommon for an index to be several times bigger than its optimal size.

You may need to learn a bit about VACUUM to figure out how much you may be affected by table and index bloat in your specific usage.

The size of the transaction logs entirely depends on the amount of writes to the database.


We have created gMSAs for SQL Agent, Database Engine, Analysis Services and Integration Services.  I recommend the following:

MICROSOFT SQL SERVER 2016 INSTALLATION USING GMSA (GROUP MANAGED SERVICE ACCOUNTS) – PART I

Also, make sure that follow:

Using a gMSA with SQL Server

This will help you escape the pitfall of restarting the server.


PostgreSQL CAST() giving unexpected results

Based on your last comment,

JavaScript parseFloat("34.55555555555595149").toPrecision(15)

the cast should be to the numeric datatype

CAST ('input' AS numeric(17,15))

https://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-NUMERIC-DECIMAL


Stored Procedure null parameter within where clause

SELECT *
FROM Table1
WHERE Table1.URL LIKE '%' + @Parameter1 + '%' AND Table1.ID = @Parameter2
AND
(
    @Parameter3 is null
    or Table1.ID2 = @Parameter3
);

Take a look at the above example.  If you change your AND clause to a nested OR clause specifying your initial expression as well as @Parameter3 is null.  That will then demand that the nested expression is true if @Parameter3 is NULL.


A Graph Table in SQL Server should suit this purpose. You can use a graph table to create complex many-to-many relationships using Nodes and Edges then perform complex association queries with relative ease. Below is a rough example of a 'Netflix-style' recommendation based on previously liked movies:

Setup:

USE master
GO
IF EXISTS (SELECT * FROM sys.databases WHERE [name] = 'RatingsDB')
    DROP DATABASE RatingsDB
GO
CREATE DATABASE RatingsDB
GO
USE RatingsDB
GO
CREATE TABLE Users (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    UserName NVARCHAR(255)
) AS NODE
GO

INSERT INTO Users (Username)
VALUES ('Bob'), ('Carol'), ('Frank'), ('Beth'), ('Jan'), ('Marco')
GO

CREATE TABLE Movies (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    MovieTitle NVARCHAR(255)
) AS NODE
GO

INSERT INTO Movies (MovieTitle)
VALUES ('Red Dawn'), ('Titanic'), ('Jurassic Park'), ('Clerks'), ('Four Weddings & A Funeral'), ('Saving Private Ryan'), ('Mallrats')
GO

CREATE TABLE Likes AS EDGE
GO

CREATE TABLE SimilarMovies AS EDGE
GO

INSERT INTO SimilarMovies
VALUES (
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Red Dawn'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Saving Private Ryan')
), (
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Four Weddings & A Funeral'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Titanic')
), (
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Clerks'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Mallrats')
)

INSERT INTO Likes
VALUES (
    (SELECT $node_id FROM Users WHERE UserName = 'Bob'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Red Dawn')
), (
    (SELECT $node_id FROM Users WHERE UserName = 'Carol'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Titanic')
), (
    (SELECT $node_id FROM Users WHERE UserName = 'Frank'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Mallrats')
), (
    (SELECT $node_id FROM Users WHERE UserName = 'Beth'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Jurassic Park')
), (
    (SELECT $node_id FROM Users WHERE UserName = 'Jan'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Four Weddings & A Funeral')
), (
    (SELECT $node_id FROM Users WHERE UserName = 'Marco'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Clerks')
)

Query Example:

SELECT Users.UserName,
    RecommendedMovie.MovieTitle AS Recommendation,
    WatchedMovie.MovieTitle AS BasedOn
FROM Movies WatchedMovie, Users, Likes, Movies RecommendedMovie, SimilarMovies
WHERE MATCH(Users-(Likes)->WatchedMovie -- Find movies that a user likes
    AND WatchedMovie-(SimilarMovies)->RecommendedMovie) -- Find similar movies to the one the user liked
UNION
SELECT Users.UserName,
    RecommendedMovie.MovieTitle AS Recommendation,
    WatchedMovie.MovieTitle AS BasedOn
FROM Movies WatchedMovie, Users, Likes, Movies RecommendedMovie, SimilarMovies
WHERE MATCH(Users-(Likes)->WatchedMovie -- Find movies that a user likes
    AND WatchedMovie<-(SimilarMovies)-RecommendedMovie) -- Find similar movies to the one the user liked

Results:

UserName    Recommendation              BasedOn
-----------------------------------------------------------------
Bob         Saving Private Ryan         Red Dawn
Carol       Four Weddings & A Funeral   Titanic
Frank       Clerks                      Mallrats
Jan         Titanic                     Four Weddings & A Funeral
Marco       Mallrats                    Clerks


I fixed it using a response file, after all.

C:\PDQStaging\Oracle\64bit\client\setup.exe -silent -nowelcome -nowait -responseFile C:\PDQStaging\Oracle\64bit\client_install.rsp


Fast database for a small microcontroller

A ported version of SQL Lite is probably your best bet. See this thread.


Using `SELECT` as `RETURN` Argument in Function?

Save the result of your query into a variable and return the variable. For example:

CREATE OR REPLACE FUNCTION test_f(v1 number) RETURN number IS
v2 number;
 BEGIN
  IF TRUE THEN
     SELECT 42 into v2 from dual;
     RETURN v2;
   ELSE
     RETURN v1;
   END IF;
 END;

Be sure that your query always generates exactly one row. Otherwise you could get an error if no rows returned or if more than one row is returned.


Why does the same query run faster the second time I run it?

What you are experiencing is called caching. The database doesn't have to go to disk the 2nd time because it can either get the data from its own buffer cache, or the operating system/disk array can also provide the data faster from its own cache.

In order to see whether Oracle fetched the data from disk, or used its cache you can enable autotrace in SQL Developer.

You'll get something like the following:

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      3  consistent gets
      0  physical reads
      0  redo size
    538  bytes sent via SQL*Net to client
    543  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

Physical reads are pulled from disk.

Another factor that makes the initial query slower is the fact that Oracle has to hard parse a query the first time it sees it - this involves finding the optimum (as far as the Oracle optimiser is concerned) execution plan for the query & is quite computationally (CPU) expensive, thus takes time.


vertica/postgres copy text to binary column

As you asked for it: here is a Postgres solution if you can upload all the files to the Postgres server (or at least a directory accessible from there).

do
$$
declare
  l_rec record;
  l_data bytea;
  -- change this to the server directory where all XML files are stored
  l_dir text := '/data/import';
  l_fname text;
begin
  for l_rec in select * from pg_ls_dir(l_dir) as t(name) loop
    l_fname := l_dir ||'/'||l_rec.name;
    l_data := pg_read_binary_file(l_fname);

    insert into the_table (the_xml_column)
    values (l_data);
  end loop;
end;
$$

This must be run as the superuser (postgres)  because the functions pg_ls_dir() and pg_read_binary_file() have restricted access (for security reasons)


Getting 'specified network password is not correct' when trying to change password

I have resolved it. Below is what I did and what fixed it.

Ran "net user /domain " and made sure the account was still active and not locked or expired. (The account was active and healthy)
Restarted the workstation. (The only thing it proved was the password was correct and I managed to log on successfully)
Waited for couple of hours. (Sometimes you just have to be patient.)

After doing all of the above, the issue still remained. In the end, it was the action below that resolved it:

I went to the Services mmc snap-in under Control Panel -> Administrative Tools instead of SQL Server Configuration Manager. Changed the password there and started the SQL Server service successfully. Repeated the steps for SQL Agent.

It worked but I am still puzzled why it worked in Services but not in SQL Server Configuration Manager. Active Directory vs WMI? Anyone?