MS-SQL 2012 LINKED SERVER TO MS-SQL 2000

Having trouble creating a linked server from MS-SQL 2012 to MS-SQL 2000?  Need to access data on your older SQL 2000 database system?  Here is a handy script to use as a template for creating the link.  But first go to the control panel and create a 64 bit data source called “MYDSN” using the older provider (SQL Client 10).  Then when that is completed, run the following database script on the MS-SQL 2012 machine to create the link.  But first edit the script as follows:

  • replace ‘MyLink’ with any name you want to use for your linked server.
  • replace ‘mylocaluser’ with the database user name on the SQL 2012 machine.
  • replace ‘myremoteuser’ with the database user name on the SQL 2000 machine.
  • replace ‘myremotepassword’ with the database password on the SQL 2000 machine.
  • Note:  the first line of the script deletes the link, which will fail if the link doesn’t exist yet.

EXEC master.dbo.sp_dropserver @server=N’MyLink’, @droplogins=‘droplogins’
EXEC master.dbo.sp_addlinkedserver @server = N’MyLink’,
    @srvproduct=N’MSDASQL’, @provider=N’MSDASQL’, @datasrc=N’MYDSN’, @location=N’System’
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname=N’MyLink’,
    @useself=N’True’, @locallogin=NULL, @rmtuser=NULL, @rmtpassword=NULL

EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname=N’MyLink’,
    @useself=N’False’, @locallogin=N’mylocaluser’,
    @rmtuser=N’myremoteuser’, @rmtpassword=‘myremotepassword’
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname=N’MyLink’, @useself=N’False’,
    @locallogin=N’sa’, @rmtuser=N’myremoteuser’, @rmtpassword=‘myremotepassword’
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname=N’MyLink’, @useself=N’False’,
    @locallogin=N’NT Service\SQLSERVERAGENT’,
    @rmtuser=N’myremoteuser’, @rmtpassword=‘myremotepassword’
EXEC master.dbo.sp_serveroption
    @server=N’MyLink’, @optname=N’collation compatible’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’MyLink’, @optname=N’data access’, @optvalue=N’true’
EXEC master.dbo.sp_serveroption @server=N’MyLink’, @optname=N’dist’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’MyLink’, @optname=N’pub’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’MyLink’, @optname=N’rpc’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’MyLink’, @optname=N’rpc out’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption
    @server=N’MyLink’, @optname=N’sub’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption
    @server=N’MyLink’, @optname=N’connect timeout’, @optvalue=N’0′
EXEC master.dbo.sp_serveroption
    @server=N’MyLink’, @optname=N’collation name’, @optvalue=null
EXEC master.dbo.sp_serveroption
    @server=N’MyLink’, @optname=N’lazy schema validation’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption
    @server=N’MyLink’, @optname=N’query timeout’, @optvalue=N’0′
EXEC master.dbo.sp_serveroption
    @server=N’MyLink’, @optname=N’use remote collation’, @optvalue=N’true’
EXEC master.dbo.sp_serveroption
    @server=N’MyLink’, @optname=N’remote proc transaction promotion’, @optvalue=N’true’
 
Advertisements

Can’t sign in to Windows 10

As of yesterday, suddenly my laptop has an extra layer of security.  🙂  Unless I hold down shift-and-enter during boot, I can’t sign in.  Everything boots normally, and the pin/password boxes appear, but I can’t enter anything into them. I can press all I want on the keyboard with the focus on the pin and/or password boxes but nothing appears in it. Using the touch screen keyboard to key the pin and/or password doesn’t work either, same result, nothing appears in the pin/password entry box. Booting into safe mode doesn’t work either because there’s a password to get in, and you can’t enter anything into the password entry area. So using an external USB keyboard has the same result. Can’t type anything in. But if I hold down shift-enter during boot I can sign in with a pin or password no problem. What does holding shift-enter during boot do? It doesn’t look any different, just now I can sign in. I guess I have to do that from now on. Arrrg!

Solution that worked for me:

  • Turn off your Windows 10 computer
  • Press and hold the Shift key and the Enter key at the same time
  • Either the left or the right shift key will do, it doesn’t matter
  • Turn on the power of your Windows 10 PC or laptop while still holding down those two keys (Shift and Enter).
  • Keep holding shift and enter keys until the sign in screen appears
  • Now the pin / password entry boxes no longer ignore your entries

 

Query MS-SQL database backup history

How to query your database backup history:

SELECT TOP 10
    d.name,
    backup_start_date,
    m.[user_name], m.database_name,
    m.server_name, m.machine_name, f.physical_device_name
FROM
    msdb.sys.databases d
    INNER JOIN msdb.sys.backupset b ON (b.database_name = d.name)
    LEFT JOIN msdb.sys.backupmediaset m ON b.media_set_id = m.media_set_id
    LEFT JOIN msdb.sys.backupmediafamily f ON f.media_set_id = m.media_set_id
WHERE
    d.name = N'MyDatabaseName'
    AND m.[type] = 'D' -- D or L
ORDER BY
    backup_start_date DESC,
    m.backup_set_id,
    f.physical_device_name