Archive for February, 2011
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:
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:
And it worked. Go figure.
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.
Not earth shaking, I know, but admit it, you were annoyed, too.
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.
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=18.104.22.168, 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.
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:
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.
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
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.
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.