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 );

MS Access Query Truncation Problem

There’s been a small change to the way MS Access works from the ’97 to the 2010 version that can cause some serious issues.

Consider this query “Select * From Table1” . What happens? In both versions, it will run just fine. However, when importing this command through any database connection, say OLEDB, it won’t work. Instead, what’s fed into 2010 is “able1”.

What’s happening? For database connections, table names are required to be square-bracketed. A simple fix.

Just use “Select * From [Table1]” instead.

Connection problem or invalid MMI code workaround

I recently encountered this. No calls would go through, even though it was working yesterday.

The fix:
– remove the battery
– remove the sim card
– reinsert both
– (re)start your phone.

SQL Sproc Gotcha

It’s easy to mistake the SQL Sproc syntax

ALTER PROCEDURE [dbo].[fooBar]
(
)
AS
-- etc
RETURN  @Success

With this. Note the BEGIN and END statements. It generally works for shorter queries, but for longer ones, you begin to get baffling SQL syntax queries, where sometimes it will execute the ALTER PROCEDURE, but things like changing the whitespace, reverting it, and executing it again will fail.

ALTER PROCEDURE [dbo].[fooBar]
(
)
AS
BEGIN
-- etc
RETURN  @Success
END