https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out?view=sql-server-ver15

You could try looking at waits:

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

As the direction of travel with the App is Oracle, and SQL 2012 goes out of extended support next year, I would also try...

You can also use Bulkcopy from Powershell but I would expect this to run in a similar manner to SSIS.

eg

https://devblogs.microsoft.com/scripting/use-powershell-to-copy-a-table-between-two-sql-server-instances/

https://www.mssqltips.com/sqlservertip/6009/how-to-copy-a-table-in-sql-server-to-another-database/

These links may be of help:

https://www.sqlshack.com/new-features-in-sql-server-2016-always-encrypted/

https://www.sqlshack.com/an-overview-of-the-column-level-sql-server-encryption/

That is strange.

I expect OCC not to have any lock hints for the initial SELECT especially with RCSI. You could try OCC in a test environment to see if any...

A follow on of what I have found. There are a lot of SELECT....with (UPDLOCK) commands. Since the database is already set for Read Committed Snapshot Isolation (to reduce...

In general we try to apply updates to dev machines within days of release with a view to them going onto production boxes 3-6 weeks later. Assuming no internal problems...

This word document is worth reading:

https://docs.microsoft.com/en-us/previous-versions/dn673537(v=msdn.10)?redirectedfrom=MSDN

Glad to be of help.

The problem with the windowed function approach is it being more difficult to anticipate data combinations which could break the rules. A brut force approach of...

• This reply was modified 4 years, 8 months ago by Ken McKelvey.

The idea with the above is eyeballing the following and seeing what you need to get.

SELECT id, SubType, effdate, termdate
FROM #DateRanges
UNION ALL
SELECT id, 'Gap', effdate, termdate
FROM #Gaps
ORDER...

If you can get the logic right windowed functions should be more efficient.

The following works with your test data:

WITH AllRanges
AS
(
SELECT id, SubType, effdate, termdate
FROM #DateRanges
UNION ALL
SELECT id,...

That rather depends on the amount of memory your machine has and what else is running.

Incidentally SQL 2008 R2 is out of extended support so you may want to upgrade...

Base64 is also available within SQL Server. eg

https://medium.com/falafel-software/t-sql-easy-base64-encoding-and-decoding-872ad184bd7e

https://docs.microsoft.com/en-us/sql/relational-databases/xml/use-the-binary-base64-option?view=sql-server-ver15

1. If by timestamp you mean rowversion I see no point in exporting them. If you mean date types cast them to strings first.

2. CSV files contain text not binary...

I find XML a pain.

Normally I start with nodes and once I get that right I then translate it to OPENXML as it seems faster even though you have to...

• This reply was modified 4 years, 8 months ago by Ken McKelvey.