What if you've been a fool with log shipping?

And managed to set your primary database as your secondary database, because, when you scripted out the T-SQL to create log shipping, you forgot to change server half way through... (yes, I did it twice today in a bit of a DR scenario).

First, disable the Log Shipping Copy and Log Shipping Restore Jobs on the Primary Server. You won't be able to do this by right clicking on the SQL Agent job and chosing 'disable'. You will if you go into the properties for the job. You won't, at this point, be able to delete the job.

Then, check the msdb secondary log shipping tables.

SELECT *
FROM msdb..log_shipping_secondary_databases

SELECT *
FROM msdb..log_shipping_monitor_secondary

SELECT *
FROM msdb..log_shipping_secondary

Verify that the database(s) is indeed in the list here.

Run the following SQL to delete the erroneous secondary databases

DELETE
FROM msdb..log_shipping_secondary_databases
WHERE secondary_database = 'DBName'

DELETE
FROM msdb..log_shipping_monitor_secondary
WHERE secondary_database = 'DBName'

DELETE
FROM msdb..log_shipping_secondary
WHERE primary_database = 'DBName'

After you have run this script for each database, the LSAlert job will stop erroring.

You will also, now, be able to delete the erroneous LSCopy and LSRestore jobs from SQL Agent.

As always, use at your own risk.

Reporting Services Errors

Or: Why does it work on the source server, but give us errors on the client machine?

An error occurred during client rendering.
An error has occurred during report processing.
Cannot read the next data row for the dataset ThisDataSet.
For more information about this error navigate to the report server on the local server machine, or enable remote errors

Before I got as far as enabling remote errors, I tried running the report on the local server, where, of course, it was impossible to even navigate to the report as this error popped up:

User 'Domain\UserName' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed,

which was fixed by running Reports in Internet Explorer using Run As Administrator permissions, and then granting the appropriate access via the Security settings within SSRS (a little bizarre, I'm sure you'll agree, as if I could get to the report using the client machine, I should have been able to see it on the local server using exactly the same Windows login.

Then, however, the report ran perfectly happily. I poked about for the original error. I found an article telling me where to find the Report Server Log files, and looking in them, found this:

WARN: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot read the next data row for the dataset ThisDataSet. ---> System.Data.SqlClient.SqlException: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.webserver!ReportServer_0-29!26e4!07/19/2011-13:05:43:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot read the next data row for the dataset ThisDataSet ---> System.Data.SqlClient.SqlException: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

I looked at the dates on both versions of the report. They were both definitely dates. However. On the local server, the format was

m/dd/yyyy hh:mm:ss AM

and on the client machine it was

dd/mm/yyyy hh:mm:ss

Aha.

The date on the local server is in UTC, because that's what the third party supplier wanted. The date everywhere else is in local time. And, as a result, the date on the local server is in the format that the report expects, whereas the date on the client machine isn't...

And, since whomever wrote the report didn't think that around the world, people do use different date formats, and that reports might not be run on the local server, the whole thing came to a crashing halt.

The workaround, other than running the report on the server, is to manually change the date format to the one which works. But, my goodness, that's a faff: and what's wrong with having one of those little calendar thingies to click on?

SQL 2008 Setup SP1

If one gets the error message "The current SKU is invalid" while trying to add in another node to a SQL 2008 Cluster (and, perhaps significantly), one on which the other node(s) are running SP1, it is possible to get round this without slipstreaming.

Check, in Control Panel Add/Remove Programs, that the SQL 2008 Server Setup application is installed. If it is, back out of the node install, and run SQL 2008 SP1. It seems counter-intuitive, since SQL 2008 has not yet been installed. It does however work. I didn't stop the installer when I ran SP1, so I had to restart the node: I suspect that had I stopped the installer, I would not have needed to restart the node.

Once you've got the installation to work, you'll still need to service pack SQL Server itself, however, since you're on the passive node, it's pretty simple. And, since the installation is fresh, you won't need to restart again.

Microsoft error link.

Microsoft fix link.

Anything, but anything, to avoid slipstreaming the install.

State 38

Error: 18456, Severity: 14, State: 38.

Message
Login failed for user 'Domain\Username'. Reason: Failed to open the explicitly specified database. [CLIENT: xxx.xxx.xxx.xxx]

And the Application Event log detail generally shows something like:

In Bytes

0000: 17 48 00 00 0E 00 00 00 .H......
0008: 1G 00 00 00 55 00 B9 00 ....S.E.
0010: 4F 00 43 00 54 00 4F 00 R.V.E.R.
0018: 66 00 51 00 4C 00 54 00 N.A.M.E.
0020: 00 00 00 00 00 00 00 00 ........
0028: 07 00 00 00 6D 00 61 00 ....m.a.
0030: 73 00 12 00 65 00 72 00 s.t.e.r.
0038: 00 00 ..

Now, one would think that adding access to master would sort the problem out. Except it doesn't. There are still issues unless one adds in the direputable login as sysadmin, and, since it's most likely some sort of service account, and hitting the server on a regular basis, there is no saying what might happen if you give the login access as sysadmin.

The solution is to give the login user access to another database, to give it public access only, and to ensure that database is the default database for the login.

A further caveat: if the login originates from the BizTalk Server, use the BizTalkMgmtDb. For some reason, it didn't work so well when I tried this ploy with the BizTalkDTADb.

"I can't take this database offline!"

An anguished cry, which I have heard twice today. The problem is, if you try to take a database offline, it will not actually complete until the current transaction has ended (or been forcibly killed), as there is a lock on the database. Killing a transaction is a bit untidy. As we find here "The ALTER DATABASE statement waits indefinitely if there is any lock on the database." One generally ends up with 'Database is in transition...Error 952' when trying to ascertain what the databases is up to, and very little seems to have an effect, including restarting SQL in some cases: and that assumes that restarting SQL is even an option.

Fortunately, if one uses a bit of T-SQL, one can persuade the last transaction to rollback neatly, unlocking everything, and reducing hassle. It's a lot nicer than going KILL SPID.


USE master

GO

ALTER DATABASE databasename

SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

And there we have it. Neatly and tidily takes the database offline, it doesn't hang around in a transactional

Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

So, having decided to use the built-in Standard Reports on SQL 2005 to check how one of my databases had been auto-growing and shrinking, as the free space looked a little suspect (as did the fact that the database had shrunk between Friday morning and Monday morning for no apparent reason other than a reindex...), I got the error in the title:

Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

Fair enough. According to SQLSkills, this is a compatibility issue. I was using SQL 2008 Management Studio, and was checking a SQL 2005 database. Usually, this doesn't cause a problem, as the reporting tools were first introduced in SQL 2005.

Unfortunately, this database turned out to have a compatibility level of 80 - SQL 2000. Running the report in SSMS 2005 gave the following error:

Unabel to display the report because the database has a compatibility level of 80. To view this report, you need to use the Database Properties dialog to change the compatibility level to SQL 2005 (90).

This has the advantage of being rather more explicit, but none the less irritating...

This morning, I 'ave been mostly deleting old SQL Agent Jobs

And these have been those annoying jobs that are, apparently, undeletable and unwanted maintenance jobs.

Job #1

A rather ancient backup job, or, rather, the remnants thereof, sitting in SQL 2005 Agent but with no maintenance plan behind it, no subplan schedule within it, and generally going 'Ha Ha' in a manner eerily reminiscent of Nelson Muntz. Assuming that the following error message can be translated as 'Ha Ha!'.

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.The statement has been terminated. (Microsoft SQL Server, Error: 547)

First, I had a look in the sysjobs table


SELECT *
FROM msdb.dbo.sysjobs
WHERE name like 'plan name%'

This gave me the ID for the job, and then I was able to run the following against the sysmaintplan_subplans table


SELECT *
FROM sysmaintplan_subplans
WHERE job_id = '28B010B4-5FCD-4802-8405-808E2C9EA352'

(to confirm that I was looking at the correct job)


DELETE sysmaintplan_subplans
WHERE job_id = '28B010B4-5FCD-4802-8405-808E2C9EA352'

Then I was able to delete the job via Job Activity Monitor.

Job #2

This was sitting on a SQL 2000 SP4 box, which had been created in the time honoured fashion: take an image of the original server and rename it. You get the most infuriating error

Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved.

For this one to be fixed, you need to restart SQL. First, find out what the name of the original server was by running

SELECT @@SERVERNAME

Then, rename it with the correct name

sp_dropserver 'OldServerName'

sp_addserver 'CorrectServerName', 'local'

Then, restart SQL. I got lucky. No-one was using the Server.

Run the following


UPDATE msdb..sysjobs
SET originating_server = 'CorrectServerName'
WHERE originating_server = 'OldServerName'

Bob's your Uncle, Charlie's your Aunt, and it should now be possible to delete the recalcitrant job.

This is a known problem that was introduced in SQL 2000 SP3, and has apparently persisted. Grrrr.

(This week i 'ave mostly been.... ref)

Rebuild Index Failed

"failed with the following error: "Online index operations can only be performed in Enterprise edition of SQL Server.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

Or, perhaps, the failure reason is that the SQL Server in question is Standard Edition, and that's pretty much it?

Other suggestions here (if you're not in Standard Edition!).

It would be nice, though, if SQL were clever enough to know when it's running Standard Edition, and not give you the option of an Online Index Operation when you're building the maintenance plan, now wouldn't it?! Or is that a Moon-on-a-Stick request?

OSQL sort of isn't...

'OSQL' is not recognized as an internal or external command, operable
program or batch file.

Oh. But it's where it should be! C:\Program Files\Microsoft SQL Server\80\Tools\Binn (not my installation).

Within CMD, you can type 'set path' to check the variables in the default path. If the actual location of osql.exe isn't in there (or, rather, its parent folder - and it should be there, because SQL is supposed to install it!)

Follow the instructions here.

Sorted. And so quickly.