Wednesday, October 29, 2008

Find a Specific Value Anywhere in a SQL Server Database

I can't tell you how often in the past I could have used this script from David Yardy:
I can find a string value anywhere it exists in a field in my database. As he says, it can easily be modified to search for values that are not strings.

Thursday, October 09, 2008

ASP.Net Enabling the Session Object

Today I was attempting to use the Session object to exchange information between two ASP .Net pages. I received the following exception when I tried to assign something to it:

System.Web.HttpException was unhandled by user code
Message="Session state can only be used when enableSessionState is set to true, either in a configuration file or in the Page directive. Please also make sure that System.Web.SessionStateModule or a custom session state module is included in the \\\\ section in the application configuration."
at System.Web.UI.Page.get_Session()
at AddToCart.SaveCustomFields() in c:\Inetpub\wwwroot\StarterSite\AddToCart.aspx.cs:line 485
at AddToCart.AddItemToCart() in c:\Inetpub\wwwroot\StarterSite\AddToCart.aspx.cs:line 490
at AddToCart.btnAddToCart_Click(Object sender, EventArgs e) in c:\Inetpub\wwwroot\StarterSite\AddToCart.aspx.cs:line 300
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Based on information I found on the web, I first tried to set EnableSessionState="True" on every page that needed to use the session, and that didn't cure the issue.

Next, I tried setting <pages enableSessionState="true"> in the Web.config file under configuration/system.web. That didn't fix it either.

Finally, what worked was to put this into the Web.config:
<sessionState mode="InProc"></sessionState>
(also under configuration/system.web).

Thursday, September 04, 2008

SQL Server 2000 - Finding a Field in a Database

Today I was looking for a field in a SQL Server 2000 database which had lots of tables. I knew the name of the field, but I didn't know the table name. So I modified the code contained in master.dbo.sp_columns to find it. It turns out that in my case, I was looking for a field that didn't exist. Hopefully you'll have better luck.

To use this, replace the words your field here in the next-to-last line.

TABLE_QUALIFIER = convert(sysname,DB_NAME()),
TABLE_OWNER = convert(sysname,USER_NAME(o.uid)),
TABLE_NAME = convert(sysname,,
COLUMN_NAME = convert(sysname,,
convert (sysname,case
when t.xusertype > 255 then
else d.TYPE_NAME collate database_default
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */
else OdbcPrec(c.xtype,c.length,c.xprec)
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */
isnull(d.length, c.length)
end) LENGTH,
SCALE = convert(smallint, OdbcScale(c.xtype,c.xscale)),
NULLABLE = convert(smallint, ColumnProperty (,, 'AllowsNull')),
REMARKS = convert(varchar(254),null), /* Remarks are NULL */
COLUMN_DEF = text,
CHAR_OCTET_LENGTH = isnull(d.length, c.length)+d.charbin,
ORDINAL_POSITION = convert(int,
select count(*)
from syscolumns sc
where =
AND sc.number = c.number
AND sc.colid <= c.colid
IS_NULLABLE = convert(varchar(254),
substring('NO YES',(ColumnProperty (,, 'AllowsNull')*3)+1,3)),
SS_DATA_TYPE = c.type
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on c.cdefault =
AND m.colid = 1
-- = @table_id =
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0))
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (,, 'IsIdentity'),0)
AND c.xusertype = t.xusertype
AND like '%your field here%'

Wednesday, February 13, 2008

Deploy Problem From Within Visual Studio

Yesterday I was trying to deploy a BizTalk solution from within Visual Studio and I saw all kinds of strange errors. I started trimming down the app, deleting schemas, pipelines, and I still saw errors. Then I deleted the entire application in the BizTalk Console, and when I tried to deploy I saw this error in Visual Studio:

Error 25 Failed to add resource(s). Change requests failed for some
resources. BizTalkAssemblyResourceManager failed to complete end type change
request. Failed to update binding information. Party 'PartyName' enlisted
under role
has not bound all the operations of role link port types.

The last thing I tried was to go through each project and to set Redeploy= false, and then delete the project. That worked.

Wednesday, February 06, 2008

I was trying to get a send port working in a BizTalk app the other day, but it kept failing with the following message:

Event Type: Error
Event Source: BizTalk Server 2006
Event Category: BizTalk Server 2006
Event ID: 5754
Date: 2/5/2008
Time: 6:06:56 PM
User: N/A
Computer: ACME31
A message sent to adapter "FILE" on send port "ACME.B2B.ResaleReport.Port.Snd.Http.OneWay.Test.eBI.Steve" with URI "C:\Dev\ACME.B2B.ResaleReport\OneWayPorts\DB2\%MessageID%.xml" is suspended.
Error details: The system cannot find the file specified. (Exception from HRESULT: 0x80070002)
MessageId: {4E99E4FF-5E09-441E-8AB2-2A83FB49B3A6}
InstanceID: {0BFBB93E-3352-4354-A1C7-A153FE90A42C}

For more information, see Help and Support Center at

When I removed the map from the send port, I was able to get a message out with no errors.

I was stumped until I tried to run the map inside of Visual Studio, and then I saw this message:
Invoking component...
C:\Dev\ACME.B2B.ResaleReport\ACME.B2B.ResaleReport.Transforms.Premier\ResaleReport_To_EDI867.btm: error btm1067: The external assembly with the fully qualified name "ACME.B2B.ResaleReport.Utilities, Version=, Culture=neutral, PublicKeyToken=ffee3e80c6a877dd" cannot be invoked. Make sure this assembly is in GAC.
Test Map used the following file: as input to the map.
Test Map success for map file C:\Dev\ACME.B2B.ResaleReport\ACME.B2B.ResaleReport.Transforms.Premier\ResaleReport_To_EDI867.btm. The output is stored in the following file:
Component invocation succeeded.

From this, it was easy to see that there were some custom functoids in the map that were not deployed to the GAC. I deployed the DLL containing the functoids, and the error was fixed.