SRSS Troubleshooting

Data fields won’t refresh or error.

Try to refresh the data by right clicking on the data sources and click refresh data. If it still doesn’t work, delete the data cache file in your solution for the file.

Error about a field not existing

You’re probably using the field in an expression somewhere

Takes forever to view the design tab, without even setting any input parameters

It’s trying to be smart with the variables and design different executions. Assign middle-man variables at the very beginning to use.

@RealParameter = @sprocInputParameter

Recovering sysadmin on your local instance

Ran across the issue where my SQLServer was installed without my knowledge, and needed to recover sysadmin access to it.

Step 1 : Check if you can log into sa

Sometimes the sa account still has the default password that comes with the instance. Each version is different, check your version’s default password online. In my case, because the instance was installed vanilla (right out of the box), the sa account is disabled, and with my permissions, I can’t enable it.

If you’re really lucky, you can just log in with the sa account and fix all your stuff. But it’s most likely that you’re not…

Step 2 : Force yourself in with single-user SQLCMD

Stack Overflow has a great guide, but I had SQLExpress, so my steps were slightly different.

First, figure out exactly what your SQL login (most likely windows auth login) is. If you can log into the server, you can expand the security > Logins to find your exact login, complete with domain. If you can’t log in at all, you won’t have a login, and we can fix that too.

Start > SQL Server > Configuration Tools > SQL Configuration Manager

Right-click on the appropriate instance of ‘SQL Server’ and Stop the service. If you can’t stop, make sure you’ve exited out of SMSS.

Start > run > cmd.exe
sqlserver.exe -sSQLExpress -m"SQLCMD"

This starts a new instance of SQLCMD as single-user mode. My instance is SQLExpress.

Start > run > cmd.exe
SQLCMD -S localhost\SQLExpress

Your first cmd window is keeping the SQLCMD connection open, you’ll need a new window (this one) to do SQL. My instance is SQLExpress.

SQLCMD -S localhost\SQLExpress

CREATE LOGIN [testAdmin] WITH PASSWORD=N'test@1234', DEFAULT_DATABASE=[master]
go

EXEC sys.sp_addsrvrolemember @loginame = N'<<DOMAIN\USERNAME>>', @rolename = N'sysadmin'
go

If you can log into the instance, you already have the login and don’t need to perform the first two lines that creates your account. Notice the ‘go’s after each command. If you don’t have them, SQL will not do anything ; neither with the go’s or without, it will not tell you one way or another if the action was performed or is successful – only if there was an error.

After you’ve done this, CTRL-C in your SQL CMD window (current window), and CTRL-C in your other (first) window. Start up the instance again in SQL Configuration Manager and see how you did.

Select all tables, and number of rows and columns

SELECT 
 t.NAME AS TableName,
 p.[Rows],
 count(c.name) as Columns
FROM 
 sys.tables t
INNER JOIN 
 sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
 sys.columns c on t.OBJECT_ID=c.OBJECT_ID
WHERE 
 t.NAME NOT LIKE 'dt%' AND
 i.OBJECT_ID > 255 AND 
 i.index_id <= 1
GROUP BY 
 t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY 
 object_name(i.object_id) 

MSSQL ms_foreachdb

When you have many databases and want to iterate a command on them quickly, use the sp_MSforeachdb command. For example:

DECLARE @command varchar(1000) 
SELECT @command = 'SELECT * FROM information_schema.tables' 
EXEC sp_MSforeachdb @command

Using SQL’s BCP and CMDShell to Encrypt and FTP

I inherited a project where I needed to create a physical file of data, encrypt it, and FTP it daily. First, I created my script, and when it was completed, added it to the SQL Agent jobs. Code snippets follow:

SQL Updates with Destination Table Alias

UPDATE T
SET IS_ACTIVE= ( select IsActive FROM Staff S WHERE S.EmployeeNum=T.EmployeeNum   )
FROM MyTable T WHERE T.EmployeeNum IS NOT NULL

SQL Merge and Multiple Cases

The Merge feature is new as of SQL 2008, and it’s great for doing exactly that. However, you can only have one when  matched clause, and only one when not matched .

MERGE Mammals AS TARGET
USING(
	SELECT C.LatinName, C.CommonName, C.Lifespan
	FROM Camels C
) AS SOURCE (LatinName, CommonName, Lifespan)
ON TARGET.LatinName=SOURCE.LatinName
WHEN MATCHED THEN
	-- update conditionally. Cases are : normal update, update when newly inactive
	UPDATE SET
	CommonName=
		CASE WHEN SOURCE.LatinName='ABC' THEN 'Brown Camel'
		     WHEN SOURCE.IsActive='DEF' THEN 'Red Camel'
		END
	, Lifespan=SOURCE.Lifespan
WHEN NOT MATCHED THEN
	-- does not exist, new record, insert it
	INSERT (LatinName, CommonName, Lifespan)
	VALUES (SOURCE.LatinName, SOURCE.CommonName, SOURCE.Lifespan );

Iterating stored procedures in SQL

You can iterate the procedures and functions in your database with a query like this:

SELECT * 
FROM  INFORMATION_SCHEMA.ROUTINES

Or, if you only want to see the stored procedures and functions that you have created yourself:

SELECT specific_name, routine_type, created, last_altered 
FROM INFORMATION_SCHEMA.ROUTINES
where
substring(specific_name, 1,3) != 'sp_'
and substring(specific_name, 1,4) != 'sys_'
and substring(specific_name, 1,7) != 'aspnet_'

Debugging your AJAX AutoCompleteExtender

The AJAX AutoCompleteExtender is a useful utility that looks very easy to implement, however, can be a time consuming whirlwind of confusion in the darkness.

Here’s how to trouble shoot:

1. Make sure it compiles and runs
2. Make sure that your AJAX method fires – get yourself a copy of Fiddler (only seems to work with IE) and watch the HTTP requests for your method
3. If it doesn’t fire, check to make sure that your MinimumPrefixLength (the number of characters required of the user to type before AutoComplete kicks in), and CompletionInterval (the number of milliseconds after the appropriate number of characters are typed before AutoComplete helps out) have the right values
4. Also make sure that the namespaces and attributes are correct.
5. If it is actually firing, check the response, is there an error?
6. If it’s actually firing and you have no errors, check that data is actually being returned

In addition, I’d like to throw my two cents in – the majority of AutoCompleteExtender examples and tutorials are incorrect if you’re on ASP.NET 4.0 . They all say that your GetCompletionList for your AJAX method (or similiarly named method) takes a third arguement, a string called contextKey. This is not true and will produce a HTTP error. The actual method signature is :

[System.Web.Services.WebMethodAttribute(), System.Web.Script.Services.ScriptMethodAttribute()]
public static string[] GetCompletionList(string prefixText, int count)
{
// do things and return the string array
string[] movies = { "Star Wars", "Star Trek", "Superman"};
return movies;
}

MSSQL Drop Database Errors & WorkArounds

‘sp_delete_database_backuphistory’ Errors

If you’re trying to delete a MS SQL database and you get errors like this :
Delete backup history failed for server ‘MYSERVER’
The EXECUTE permission was denied on the object ‘sp_delete_database_backuphistory’

The workaround is very simple. Open up your query editor and drop it manually, like so:

DROP DATABASE 'Northwind'

‘Database In Use’ Error

If you get an error like :
Cannot drop database “NorthWind” because it is currently in use.

You can fix it by using this instead:

ALTER DATABASE [NorthWind]
SET SINGLE_USER --or RESTRICTED_USER
WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE [NorthWind];
GO