Viewing legacy DTS packages in SSMS

I installed SQL Server Management Studio  , to view some a SQL Server instance 2000.
I attempted to access the packages via the : "Management | Legacy | Data Tramsformation Services"
and got the following message:

SELECT permission denied on table sysdtspackages, database 'master', owner 'dbo'

I was perplexed , because when I attempted to view the DTS packages via SQL Server 2000 Enterprise Manager - I was to view tehm.

I got around the problem , via creating a new role called "DTS_user" and assigned
EXECUTE on sp_enum_dtspackages and sp_get_dtspackage in msdb.

I then went into the user and assigned that role.

DTS jobs

SQL Server Surface Area Configuration command line interface

The SQL Server Surface Area Configuration command-line interface, sac.exe, allows you to import and export settings.
In a recent server consolidation exercise I exported the setting and used it as a form of backup , in case of any errors
during the consolidation process.
You can import and export settings on a per-instance basis and also on a per-service basis by using the command line parameters.

Using this command line utility requires sysadmin privileges

The following code is an example of exporting all settings from the default instance of SQL Server on server1 and importing them into server2:

sac out MYSERVER1.out –S MYSERVER2.out –U admin –I MSSQLSERVER
sac in MYSERVER1.out –S MYSERVER2

 

ole automation configuring for SQL Server 2005

There was a requirement to allow Ole Automation Procedures within SQL Server 2005 ,
this code will enable the feature.

-------------------CODE START------------------------
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

-------------------CODE FINISH--------------------------

ole jobs

Powershell and Batch files

Executing batch files throgh Powershell - is not doable by design. The cmd.exe is the runtime environment for .bat files , and it is
possible to issue a .bat call via the Powershell prompt

PS> cmd /c mybatchfile.bat

System administrator jobs

Powershell : SQLCMD and invoke-expression

There was a requirement to add a SQL Server login to a group of SQL Servers.
The SQL Server login was to be uniform across all the servers. I tend to use SQLCMD for most
DBA tasks - and as my account is Windows based and with high level of privileges , I didn't
have to worry about supplying a different username / password for every SQL Serve instance.

This powershell script is very simple , iterating through a list of SQL Server instances which are
stored in a text file . For every instance a SQLCMD is run . This is done through the "invoke-expression" call.
This allows the DBA to maintain their sql scripts separately , which can be executed against any number of instances

-----------------CODE STARTS---------------------------

foreach ($svr in get-content "C:\MyInstances.txt"){
    $svr
    invoke-expression "SQLCMD -E -S $svr -i createMyuser.sql"
   
}

-------------CODE FINISHES----------------

sqlcmd jobs

SQL Server restore problem - Error 3154: The backup set holds a backup of a database other than the existing database

On SQLServer 2005 Standard Edition Service Pack 2  - we have a routine that backs up databases onto a dedicated drive.
It is a full backup to disk. This routine does not drop the database

 BACKUP DATABASE MY_DB TO  DISK = 'I:\backups\jvTest20081127.BAK' WITH NOFORMAT,COPY_ONLY, INIT,  NAME = MY_DB  , SKIP, NOREWIND,

NOUNLOAD,  STATS = 10;

A couple of weeks later , I needed one of the databases and attempted a standard RESTORE DATABASE
but without the WITH REPLACE parameter.

   RESTORE DATABASE MY_DB FROM DISK =  'I:\backups\jvTest20081127.BAK'

An error was encountered : Error 3154: The backup set holds a backup of a database other than the existing database
The error occurs because I was  trying to restore the database on another existing active database.

The solution is :

RESTORE DATABASE
   FROM DISK = N'I:\backups\jvTest20081127.BAK'
WITH REPLACE

Storage Jobs

mssqlsystemresource moving error

Trying to run the following :

ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME = data, FILENAME = 'D:\MSSQLSERVER\MY_SQL_SERVER_INSTANCE\Data\mssqlsystemresource.mdf')
GO
ALTER DATABASE  mssqlsystemresource
MODIFY FILE (NAME = log, FILENAME = 'D:\MSSQLSERVER\MY_SQL_SERVER_INSTANCE\Data\mssqlsystemresource.ldf')
GO

error is :
Could not locate entry in sysdatabases for database 'mssqlsystemresource'

Tried the following  and it solved the problem:

NET START MY_SQL_SERVER_INSTANCE /f /T3608

The "/f" switch - This flag starts SQL Server with minimal configuration and allows updates to system tables. Sometimes a DBA makes config

changes , which won't allow the SQL Server instance to start.The switch is useful , as sometimes it is the only way to correct

configuration mistakes

The "/T3608" flag skips automatic recovery for all databases except the master database at the startup

London DBA jobs  

Speed and Duplex settings

Based on some problems I've been working on recently, I've set up a maintenance policy  to maintain the appropriate speeds on the Network Adapters. I've only found this problem on non-fibre networks.
There are a few machines network adapter settings with an "Auto" setting. The machines I've noticed are the 100Mb speed.
By changing the Speed & Duplex setting to 100 Mb full - the problem is solved.
1)There is the immediate issue - which I solved by manually resetting.The danger of manually resetting , is that I might miss
a machine , which is part of the chain.
2)We need to script out some way of comparing the network adapter speed with the switch ports speeds. This becomes more complex
when you realise that developing a catch-all for every type of network adapter is not straightforward.There are utilities available
for Intech based adapters , but if you are using other manufactures network adapters - then some work needs to be done .

In most cases I've noticed this is happening because the standard install is from an unprocessed install image. Therefore in a narrow implementation window it is easy to overlook , as it's a configuration setting once the install has occured

The impact of not having the right speed - is: e.g a 1 day test is taking about 3 days.
I've implemented a few tests across Desktops and Servers - switching back and forth from the different settings. With standardised scripts acceptance test takes about 30 minutes , but setting the inappropriate setting will take about 3 hrs.

The problem is that the network card is auto-sensing incorrectly, therefore a less than optimum speed , which is hurting the network performance . It is not obvious in every application but for very network intense applications .

Network jobs

Interview with Jim Gray

Maybe a lot of you have read this interview  with Jim Gray . I hadn't ever read it before but was blown away. Storage, transactional pocessing,theory , he covers a lot of topics with very concise but penetrating responses.

Account Locked out

One of the application owners sent me a message the other day: Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it.

The following code will solve the issue

--------------------CODE START----------------------------------

use master

ALTER LOGIN sa WITH PASSWORD = 'password' UNLOCK

-----------CODE END-------------------------

SQL Security vacancies