Archive for February, 2011

Calling the VBScript InStr function returns error

I am forced to work in classic ASP for some project. I don’t want to, it’s not my idea, but there we have it – the life of a consultant is fraught with peril.

While working on some VBScript code, I wrote some code to check permissions using the InStr function, and ran into this error:

Invalid procedure call or argument: 'InStr'

My call to InStr looked like this:

InStr(0, AllowablePermissions,UserPermissions)

According to w3schools.com , the first parameter, which was optional, defined the starting location. On a whim, I removed the first parameter, so my function call then looked liked this:

InStr(AllowablePermissions,UserPermissions)

And it worked. Go figure.

1 Comment

Fix for the Visual Studio 2010 Find and Replace window bug

Okay, it was not a big deal, but I always found it annoying that the Find and Replace window in Visual Studio 2010 kept changing. “Why do you keep doing that!” I would boom, to the consternation of my co-workers and other passers-by.

Well, my days of complaining (about that issue) are over. I found a link on the Visual Studio Blog that points to a patch that solves that little issue.

Not earth shaking, I know, but admit it, you were annoyed, too.

1 Comment

SQL Reporting Services – “Cannot find the object” error

I was trying to get a Matrix working on SQL Reporting Services when I ran across this error:

Cannot find the object ‘dbo.HooperSnooper’ because it does not exist or you do have permissions

Well, it did exist and I did have permissions to it. At least, I thought I did.

The “solution” was to drop the procedure, recreate it, and grant the db_executor account permissions to the stored procedure:

GRANT EXECUTE ON [dbo].[HooperSnooper] TO db_executor
GO

The first time I tried, it didn’t work. That was because I forgot to put the GO command after I created the SP. The correct syntax is (drum roll)…

CREATE PROCEDURE [dbo].[HooperSnooper]
 @Hooper int,
 @Snooper int
AS
BEGIN
 SELECT *
 FROM HooperSnooper
 WHERE Hooper = @Hooper
 AND Snooper = @Snooper
END
GO
GRANT EXECUTE ON [dbo].[HooperSnooper] TO db_executor
GO

I must have missed a GO statement somewhere along the line. Those GOs are important, or so I’ve heard.

Leave a comment

Dealing with SecurityException in a medium trust environment

I recently deployed a C# / Silverlight / ASP.NET site to a hosting provider. While most of the site worked just fine, I ran into this rather ugly error upon hitting one part of my application:

Security Exception Description: The application attempted to perform an operation not allowed by the security policy.  To grant this application the required permission please contact your system administrator or change the application’s trust level in the configuration file.

Exception Details: System.Security.SecurityException: Request for the permission of type ‘System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed.

FileIOPermission? What were they talking about? The part of my application that was throwing the exception didn’t access any files. Or so I thought.

The problem was with my calls to System.Web.Configuration.WebConfigurationManager to access the appSettings tags in the web.config file. After I discovered a post on the forums of asp.net, I discovered my error:

Most of the new configuration APIs in System.Configuration require full-trust.

Try debugging the problem in your development server by changing the Web.config so that it matches the trust level in your provider. For example,

<trust level=”Medium” />

Yep, that was it – after I added the trust tag to the web.config file in my development  environment, I was able to reproduce the error locally and successfully diagnose the problem. I had to rewrite how I accessed the configuration settings, using WebConfigurationManager.AppSettings[“ConnString”] instead of the old way, but that was a relatively painless step. And now I know to run my applications that will be hosted by a provider in a medium trust environment.

Leave a comment

Defining a SQL Server function that returns a table

Who doesn’t like returning tables from user defined function in SQL? Nobody, that’s who! And fellas, ladies love guys that can do this. So without further ado: 

CREATE FUNCTION [dbo].[udfGetCounts]
(
@Id int
)
RETURNS TABLE
AS 
RETURN
(
SELECT COUNT(*) As MyCount, Building
FROM tblHistory
WHERE FacilityNumber = (SELECT Code FROM tblEntities (NOLOCK) WHERE ID = @Id)
GROUP BY Building
)

 Notice that the function returns a TABLE data type.

 To get the results from this function, use the following syntax:

 select * from dbo.udfGetCounts(16051)

 If you try to call it like a normal function:

 select  dbo.udfGetCounts(16051)

Then you will get this message which will cause much weeping and gnashing of teeth:

 Msg 4121, Level 16, State 1, Line 1
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.udfGetCounts", or the name is ambiguous.

You can see more information on MSDN.

Leave a comment

Formatting a phone number in T-SQL

I needed to format a phone number in SQL (stored in out database as a varchar(10), just numbers, no formatting) as an honest to goodness, nice looking phone number, like (517) 555-1212. And I found an appropriately named site called “Programming Tutorials” that explained exactly how to do it. I’ve added a wee bit of extra code for demonstration purposes:

 DECLARE @Phone AS VARCHAR(15)
 SET @Phone = ‘5175551212’

SET @Phone = CASE
  WHEN LEN(ltrim(rtrim(@Phone)))=’10’ THEN ‘(‘+SUBSTRING(@Phone,1,3)+’)’+’ ‘+SUBSTRING(@Phone,4,3)+’-‘+SUBSTRING(@Phone,7,4)
  ELSE ‘ ‘ end

SELECT @Phone

Remember to make the variable that will hold the formatted phone number large  enough to accomodate the newly inserted characters. When I first wrote the code, my @Phone variable was a varchar(10), enough to contain the original value, but not large enough for the formatted final phone number.

Leave a comment

Reporting Services error: Failed to enable constraints

I was creating a report using Microsoft Reporting Services when I ran across this error:

Failed to enable constraints. One or more rows contain values 
violating non-null, unique, or foreign-key constraints.

That’s not too terribly helpful, is it? So I went in search of something to make sense of this error. A Bing search took me to “Castner IT Blog” and there it was, bullet point number 1, the solution to my problem:

Check if you have changed the length of any fields in the underlying database table after creating the dataset. If you have, you may need to manually change them in the dataset as well. If you are using Visual Studio 2008, open the xsd file and click on the relevant field name. Ensure the maxlength property matches your new field length.

I had, in fact, updated the stored procedure used as a data source for that report, and that update had changed the maximum size of the data being returned.

Leave a comment