https://blog.sqlauthority.com/2016/08/25/sql-server-fix-error-cannot-use-backup-file-originally-formatted-sector-size-4096-now-device-sector-size-512/

https://stackoverflow.com/questions/22448818/backup-a-database-on-a-hdd-with-a-different-sector-size

Also, if you are taking over a server run the following:

SELECT * FROM msdb.dbo.suspect_pages;

Hopefully nothing will be returned.

Microsoft is depreciating replication to Oracle but Oracle is not.

Goldengate allows for replication to a number of database types. I have no idea about the cost.

 

https://dataedo.com/kb/query/sql-server/find-the-time-when-table-was-last-accessed

One of today's articles has a script to show columns created with ANSI_PADDING OFF

https://www.red-gate.com/hub/product-learning/sql-prompt/never-create-columns-with-ansi_padding-set-to-off

 

The following suggests it is the ANSI_PADDING setting with CREATE TABLE.

This is interesting. In future I think I am going to alter the first line of my CREATE TABLE scripts...

The behaviour of DATALENGTH seems to depend on ANSI_PADDING when the table was created.

All my connections have ANSI_PADDING ON so I have never noticed the difference.

SET ANSI_PADDING...

I would be inclined to generate the SELECTS. Something like:

SELECT 'SELECT * FROM ' + TABLE_SCHEMA + '.' + TABLE_NAME +
' WHERE' +
CASE WHEN CREATEDON IS NOT...

For varchar, LEN(Area) will give the trimmed length while DATALENGTH(Area) will give the actual length.

For nvarchar, LEN(Area) will give the trimmed length while DATALENGTH(Area) will give the actual length times...

• This reply was modified 5 years, 7 months ago by Ken McKelvey.

-- *** Consumable Test Data ***
CREATE TABLE #t
(
KeyID int NOT NULL
PRIMARY KEY
,UserID varchar(10) NOT NULL
,Amount money NOT NULL
);
INSERT INTO #t
VALUES (1, '12', 1.00)
,(2, '09', 1.00)
,(3, '12', 5.00)
,(4,...

Try Powershell. Something like:

https://social.technet.microsoft.com/wiki/contents/articles/890.export-sql-server-blob-data-with-powershell.aspx

 

I have not tried it but Oracle's Goldengate will probably work. I suspect this would be the expensive option.

SELECT * FROM OPENQUERY([AS400], 'SELECT * FROM AS400.S78359E0.QS36F.INDETLWO')

I do not know much about AS400/DB2 but I think three part naming would be more likely to work with OPENQUERY

            
March 13, 2020 at 2:44 pm
            
#3734072

            
            
                                                
        

You can generate SUBSTRINGs with something like:

WITH StringOffSets
AS
(
SELECT
COALESCE
(
SUM(CHARACTER_MAXIMUM_LENGTH) OVER(ORDER BY ORDINAL_POSITION ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
,0
) + 1 AS StartPos
,CHARACTER_MAXIMUM_LENGTH AS ColLen
,COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA =...

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

You could create a job and then have a trigger run sp_start_job.

I would not reload the production table but only INSERT/UPDATE/DELETE the differences.

I don't know much about sqlite but I doubt if joins in updates are allowed.

You could try something simple like:

UPDATE wbridge_history
SET yearclass =
(
SELECT D.payclass
FROM
(
SELECT grower, block, section,...

• This reply was modified 5 years, 11 months ago by Ken McKelvey.