Workarounds « 3VL – SQL Server Development

Archive for the ‘Workarounds’ Category

Friday, May 28th, 2010

I recieved the following error when trying to restore from a .bak file:

error 5
RESTORE HEADERONLY

The error while very frustrating was easy to work around. I simply changed my logon to ‘Local System’ and was able to restore the backup.

To take you through the process step by step I:

1) Navigate to SQL Server Confiuration Manager

2) Right click on the instance you are using

3) Click on properties

4) Select thethe Logon Tab

5) Click on built in account

6) select ‘Local System’ from the drop down

7) Apply and ok your way out of the menus.

Thursday, April 15th, 2010

Using OpenQueries you can execute a pass-through query to a linked server within SQL Server. The Syntax for an open query is relatively simple:

Select * from openquery({SERVER_NAME}, ‘SELECT {FIELD} FROM {TABLE}’)

This can be useful however OpenQueries dont accept parameters. The way round this is to put the parameters outside the string. For example:

SELECT QUERY.* FROM OPENQUERY({SERVER_NAME}, ‘SELECT {TABLE}.{ROW} AS {R1} FROM {TABLE}’) AS QUERY<br />
WHERE QUERY.{R1} = {@Variable}

Note: Its best practice to use aliases for the openquery.

Thursday, April 15th, 2010

Unfortunately it is impossible to use the ALTER INDEX command with variables as Parameters.

It will always give a syntax error.

Luckily there is the following workaround:

declare @Alter varchar(200);�
set @Alter = ‘ALTER INDEX ‘ + @Index_Name + ‘ ON ‘ + @Table_Name + ‘ SET (ALLOW_PAGE_LOCKS = ON)’;

To execute just use the following code:

exec(@Alter);