Microsoft Data & AI

Known bug when trying to edit DTS packages:

described here, but the two links in the article – Microsoft SQL Server 2008 Feature Pack page and Feature Pack for Microsoft SQL Server 2005 page.

Microsoft SQL Server 2005 Backward Compatibility Components available on both links, but only on the second one it will work with 2008R2 (Despite the fact that the date of Feature Pack release is earlier).
In addition, here, in the first comment are correct pathes to copy *. dll and *. rll files.
In SSMS 2012 DTS package management is discontinued.

When migrating a database from SQL Server 2000 to SQL Server 2005, it became clear that the line

RAISERROR ('test error %S', 16, 1, @test)

due to wrong written %S instead of %s SQL Server 2000 simply works wrong (returns only the first character of the parameter), but SQL Server 2005 does not create a procedure:
Msg 2787, Level 16, State 1, Line 6
Invalid format specification: ‘%S’.

Because such writing could occur in any procedure, we can find them all::

SELECT name FROM sys.procedures WHERE CHARINDEX ( '%S', OBJECT_DEFINITION(object_id) COLLATE Latin1_General_CS_AS) >0

In one server SQL Server 2005 when I tried to open the properties of the database using the GUI I saw:

Indeed, we cannot determine the database owner:

select owner_sid, suser_sname(owner_sid) from sys.databases where name='model'

It was probably the domain user that no longer exists in AD.
Change the owner with procedure sp_changedbowner in the database model is prohibited::

use model; EXEC sp_changedbowner 'sa'

Msg 15109, Level 16, State 1, Line 1
Cannot change the owner of the master, model, tempdb or distribution database.

We can change the sid directly in the system table, as described here, but it is, really, is not recommended by MS way.

So we use the fact that owner of any attached database become the current user, and running SQL Server with the key /T3608, execute under the user’s session sa:

EXEC master.dbo.sp_detach_db @dbname = N'model'
GO
CREATE DATABASE [model] ON
( FILENAME = N'E:\DATA\model.mdf' ),
( FILENAME = N'E:\DATA\modellog.ldf' )
FOR ATTACH
GO

Restart the SQL Server, we see:

select owner_sid, suser_sname(owner_sid) 'owner name' from sys.databases where name='model'

If it possible, you can open the port for the SQL Server Browser, listed here.
If not, you need to create an alias or just connect by specifying port in the connection string:

where a named instance INSTANCENAME running on port 1453.

OS version: Windows Server 2008R2 EE x64
SQL Server version: SQL Server 2008R2 x64 SP1

Due to unspecified “Oracle provider for OLE DB” error twice restart SQL Server service.
An entry in the Application log:

01:46:12 PM Information SERVERNAME.domain.a 1001 Windows Error Reporting N/A N/A Fault bucket , type 0 Event Name: APPCRASH Response: Not available Cab Id: 0 Problem signature: P1: sqlservr.exe P2: 2009.100.2500.0 P3: 4dfb6221 P4: StackHash_6d63 P5: 6.1.7601.17725 P6: 4ec4aa8e P7: c0000374 P8: 00000000000c40f2 P9: P10: Attached files: C:\Users\username\AppData\Local\Temp\WERC049.tmp.appcompat.txt C:\Users\username\AppData\Local\Temp\WERCB13.tmp.WERInternalMetadata.xml C:\Users\username\AppData\Local\Temp\WERDDCA.tmp.mdmp C:\Users\username\AppData\Local\Temp\WERE22E.tmp.WERDataCollectionFailure.txt These files may be available here: C:\ProgramData\Microsoft\Windows\WER\ReportQueue\AppCrash_sqlservr.exe_acc33c1fca791edc578ecac2be501126d4755535_cab_64a1e249 Analysis symbol: Rechecking for solution: 0 Report Id: 15cbe2fd-ae2a-11e1-a618-3c4a927b0a88 Report Status: 0
01:46:12 PM Error SERVERNAME.domain.a 19019 MSSQL$SQL1 Server N/A The MSSQL$SQL1 service terminated unexpectedly.

Reason restart SQL Server is an access violation – heap corruption:

STACK_TEXT:
00000000`00000000 00000000`00000000 oraoledbutl10!Unknown+0x0
FAILURE_BUCKET_ID: ACTIONABLE_HEAP_CORRUPTION_heap_failure_block_not_busy_AFTER_CALL_c0000374_OraOLEDButl10.dll

To prevent this situation in the future, remove the property “allow in process” Oracle provider, to the provider for a linked Oracle server work out of the process SQL Server:

Further, according to this article, customize access to MSDAINITIALIZE.

Now provider error “Oracle provider for OLE DB” will not lead to restart the SQL Server.