Ask Paula!

How do I create an Index that is NOT the primary on my tables?

To create a index on a single column, do this:

CREATE INDEX IDX_FERC ON FERC(FercName)

In some cases, you may have a need to combine columns to hasten known unique combinations. This is knonw as a composite key. An example:

CREATE INDEX IDX_OperatingCompanyFERC ON FERC(OpCompanyID,FercName)

How do I convert an integer to a string in SQL Server?

If you want to reformat data, use the function convert as shown:

select convert(varchar, cast(18 as integer)) as 'a number'

SQL Server: Why do I get "multi-part identifier could not be bound" ?

Let's say you've developed a stored procedure that assigns a count value to a variable which looks like this:

SELECT @HasDefaultShipTo = count(*)
FROM StakeHolderLoc_Assoc
WHERE assoc.StakeHldrID = @StkhldrID

When you execute to compile you will see the error:

Msg 4104, Level 16, State 1, Procedure MetroDevETL_1, Line 80
The multi-part identifier "assoc.StakeHldrID" could not be bound.

The reason this error appears is because you have forgotten to associate/bind the table to "assoc". The corrected SQL will look like this:

SELECT @HasDefaultShipTo = count(*)
FROM StakeHolderLoc_Assoc assoc
WHERE assoc.StakeHldrID = @StkhldrID

How do I cycle through a Collection?

Here are the 2 common patterns in .NET:

GIVEN:

PATTERN #1:

Note: this method will work with collections that are typed, but not necessarily defined in a class which manages enumeration

string itemName;

for (int index = 0; index < OrderList.Count;index++)

{

itemName = OrderList[index].ItemName;

// do stuff here

}

PATTERN #2

using System;
using System.Collections.Generic;
using System.Text;

namespace AskPaulaExamples
{
    public class Whine
    {
       // define AND dub the collection with something
       List<string> OrderList = new List<string>() {"Gloves","Purse","Hat","Skirt","Gown"};

       static void Main()
       {
           Whine wailAbout = new Whine();

           wailAbout.StuffForMe();

       }

       public void StuffForMe()
       {
           IList<string> MyDemands = OrderList.AsReadOnly();
           foreach (string Item in MyDemands)
           {
               Console.WriteLine("What I want for my BIRTHDAY: " + Item.ToString());
           }
       }

}
}

Why Can't I compile my LINQ code?

If you're getting this message:

The type 'System.Data.Linq.DataContext' is defined in an assembly that is not referenced. You must add a reference to assembly 'System.Data.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'. C:\Documents and Settings\pldital\My Documents\Visual Studio 2008\Projects\PatentableServices\Transform\PetabyteCompression.svc.cs

... add... System.Data.Linq as a reference to your project.

I still get bit every once in awhile (read: pasting together a poc) when I include a LINQ project in a solution were I've got a dependency !

A 'Get it Done Now!' Approach for transforming SQL Server Data to XML Data

First, for all of the MS SQL Server database purists out there that will complain vehemently against this method of extraction/formatting because of the special characters (SEE: http://www.webmonkey.com/reference/Special_Characters) that may (will likely) surface and need to be escaped, or that there's a better way to do this such as utilizing the XML Schema Collection/XML Explicit features available with SQL Server, etc. -- I recognize your concern, however, you'll have to admit, this is still (low-tech/mid-90's as it is) one of the fastest and most easily understood ways of simply selecting data out of existing tables and into an XML file with commonly available tools!

This method is intended for a one-time only conversion process. Anything else will require further examination of XML Explicit/XML Schema collection conversion processing.

..For that one-time conversion....here's what to do....

Let's say this is the XSD schema you have for storing Sales Agent data in XML:

<?xml version="1.0" encoding="utf-16"?>
<xsd:schema id="NewDataSet" xmlns="" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xsd:element name="SalesRep">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="First" type="xsd:string" minOccurs="0" />
        <xsd:element name="Last" type="xsd:string" minOccurs="0" />
        <xsd:element name="Email" type="xsd:string" minOccurs="0" />
        <xsd:element name="Mobile" type="xsd:string" minOccurs="0" />
        <xsd:element name="Phone" type="xsd:string" minOccurs="0" />
        <xsd:element name="Fax" type="xsd:string" minOccurs="0" />
        <xsd:element name="Address1" type="xsd:string" minOccurs="0" />
        <xsd:element name="Address2" type="xsd:string" minOccurs="0" />
        <xsd:element name="City" type="xsd:string" minOccurs="0" />
        <xsd:element name="State" type="xsd:string" minOccurs="0" />
        <xsd:element name="Country" type="xsd:string" minOccurs="0" />
        <xsd:element name="CountryName" type="xsd:string" minOccurs="0" />
        <xsd:element name="Postal" type="xsd:string" minOccurs="0" />
        <xsd:element name="Territory" type="xsd:string" minOccurs="0" />
        <xsd:element name="TerritoryState" type="xsd:string" minOccurs="0" />
      </xsd:sequence>
    </xsd:complexType>
</xsd:element>
<xsd:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
    <xsd:complexType>
      <xsd:choice minOccurs="0" maxOccurs="unbounded">
        <xsd:element ref="SalesRep" />
      </xsd:choice>
    </xsd:complexType>
</xsd:element>
</xsd:schema>

.... take a close look at the elements, then frame the elements for each row in a SELECT statement...

(Here's how you would write that statement)

SELECT
'<SalesRep>' +
'<First>' + first + '</First>' +
'<Last>' + last + '</Last>' +
'<Email>' + email + '</Email>' +
case
    when Mobile is null then '<Mobile></Mobile>'
    when Mobile = '' then '<Mobile></Mobile>'
    else '<Mobile>' + PhoneCode + '-' + Mobile + '</Mobile>'
   end
+
case
    when Phone is null then '<Phone></Phone>'
    when Phone = '' then '<Phone></Phone>'
    else '<Phone>' + PhoneCode + '-' + Phone + '</Phone>'
   end
+
case
    when Fax is null then '<Fax></Fax>'
    when Fax = '' then '<Fax></Fax>'
    else '<Fax>' + PhoneCode + '-' + Fax + '</Fax>'
   end
+
'<Address1>' + Address1 + '</Address1>' +
   case
    when Address2 is null then '<Address2></Address2>'
    when Address2 = '' then '<Address2></Address2>'
    else '<Address2>' + Address2 + '</Address2>'
   end
+
   case
    when City is null then '<City></City>'
    when City = '' then '<City></City>'
    else '<City>' + City + '</City>'
   end
+
   case
    when State is null then '<State></State>'
    when State = '' then '<State></State>'
    else '<State>' + State + '</State>'
   end
+
'<Country>' + Country + '</Country>' +
'<CountryName>' + CountryName + '</CountryName>' +
'<Postal>' + Postal + '</Postal>' +
'<Territory>' + Territory + '</Territory>' +
   case
    when TerritoryState is null then '<TerritoryState></TerritoryState>'
    when TerritoryState = '' then '<TerritoryState></TerritoryState>'
    else '<TerritoryState>' + TerritoryState + '</TerritoryState>'
   end
+
'</SalesRep>'
FROM Metro_SalesForce

This statement yields rows that will look like this:

<SalesRep><First>Jack</First><Last>Sprat</Last><Email>Jack.Sprat@metro-design-dev.com</Email><Mobile>1-678-999-9999</Mobile><Phone>1-770-999-8888</Phone><Fax></Fax><Address1>1313 Mockingbird Lane</Address1><Address2></Address2><City>Conyers</City><State>GA</State><Country>USA</Country><CountryName>United States</CountryName><Postal>30013</Postal><Territory>Georgia</Territory><TerritoryState>GA</TerritoryState></SalesRep>

....Once you've executed the statement in SQL Server here's what's next....

1. Right click on the results grid to save the file in .csv format.
2. Open the file in Microsoft EXCEL. (Do NOT double click on the file to open Microsoft EXCEL. Instead, open MS EXCEL first--then follow the dialog box with the format prompting to assure that you are using comma delimited, rather than fixed-length or tab formatting.)
3. In EXCEL, do a Find and replace all -- searching for any special characters such as '&', etc.
4. Save the file.
5. Copy the file -- but use the file type .xml instead of .csv
6. Wrap the XML you have with the appropriate utf header AND node to manage the recursion of the rows (e.g. <SalesReps> . In this example that would be:

<?xml version="1.0" encoding="utf-16"?>
<SalesReps>

7. End the management node at the tail end of the file. In this case that would be:

</SalesReps>

8. Save the file-- which would look like this:

<?xml version="1.0" encoding="utf-16"?>
<SalesReps>
<SalesRep><First>Jack</First><Last>Sprat</Last><Email>Jack.Sprat@metro-design-dev.com</Email><Mobile>1-678-999-9999</Mobile><Phone>1-770-999-8888</Phone><Fax></Fax><Address1>1313 Mockingbird Lane</Address1><Address2></Address2><City>Conyers</City><State>GA</State><Country>USA</Country><CountryName>United States</CountryName><Postal>30013</Postal><Territory>Georgia</Territory><TerritoryState>GA</TerritoryState></SalesRep>
</SalesReps>

9. Double click on the file to open it. If the file comes up in your default browser or IDE bench you're DONE! If not, check your XML header/trailer first. If it still won't come up check for other possible escape characters using EXCEL.

That's all there is to it! (Really)

SQL Update Statement - using SubSelect

This is a simple, but helpful example of changing a boolean or bit flag in a table to 'True' when the condition in the subSelect is met. In this context, the SQL statement finds all the cases where a sales agent's email exists in the Metro_SalesForce table and exists in the related table Metro_SalesForceMult. The Metro_SalesForceMult table contains the USA state assignments only for sales agents representing more than a single state.

UPDATE Metro_SalesForce
SET multipleStates = 'True'
WHERE Metro_SalesForce.email in
(
     Select x.email
       From Metro_SalesForce x, Metro_SalesForceMult y
        Where x.email = y.email
)

Where is my Host file on Windows?

usually your host file (DNS/ip addresses) can be found under:

c:\windows\system32\drivers\etc

The Wrong Program is Active when I double Click on a File. How Do I Change it Back? (AKA: How do I change My File Type Association?)

This can be a really irritating problem when suddenly the EXCEL file on your desktop you used to double-click on no longer gets viewed via Microsoft EXCEL, but instead gets viewed through the Microsoft Excel READER program instead! In essence, either a new program you've installed has reset the file association, or you are working on a new computer that may have different software--overall, this area has to do with File Management/File Extension types.

Here's what to do:

In Windows XP:
1. Look for the 'My Computer' icon on your desktop (usually to the far left, near the top of your monitor)
2. Double-click on the icon. A window will appear. Go to 'Tools'. Click on Folder Options...
3. Look to make sure the 'Hide Extensions for known file types' box is unchecked. Click 'Ok',
4. Go to the File Types tab.
5. Look for the File extension (e.g. XLS) you want to change.
6. Once found, highlight it and click on the Change button.
7. Under recommended programs, the one you want is probably there. Be sure the 'Always use the selected program to open this kind of file' is checked. Click OK. (Note: If you can't find your program under recommended programs, continue to scroll until you find the one you want.)

In Vista:
1. open Control Panel. Go to Programs. Click on Make a file type always open in a specific program. ( if you are in classic view, open Default Programs. Click Associate a file type with a program.
2. Locate/highlight a file type in the Set Associations folder.
3. Click on the Change Program button.
4. The Set Associations window will show--pick your program--using the same advice in Step #7 above.
5. When you've selected the program, Click OK.

That should patch you right up!

With that all that said:

If you do not find the program you are looking for, do not try to change the existing association for these files. First determine why the program you think is there no longer appears to be there.

How do I select a value from a table into a variable in SQL Server?

Here's an example...

DECLARE @LocID

SELECT @LocID = LocationID
FROM Locations
WHERE LocationName = 'WAREHOUSE X'

Why do I get the error: "General failure. The URL was: [http text]. The system cannot find file specified" When I click a link from an Outlook Message?

In a nutshell, this is a FireFox last release issue. If Internet Explorer is not your default browser, go into the internet options page (under the gear icon in IE 7) , then go to the programs tab. Click on the 'Make Default' button, then 'Apply'. Try clicking on the link from your email message again. The message should now be gone.

Raymond @ www.raymond.cc has a GREAT detailed blog entry on this issue explaining all of the reasons and conditions.

http://www.raymond.cc/blog/archives/2008/03/25/fix-general-failure-when-open-link-or-url-from-outlook-and-windows-mail/

He offers a different, more high tech solution--however, for those of you not interested in techie-stuff per se -- try the 'Easy Button(s)" approach first!

Does SQL server have an encryption function?

Yes. The functions are PWENCRYPT and PWCOMPARE. This basically allows the ability to encrypt a value on an insert/update and offer a comparison of the value on a select. There isn't a decryption function available.

Here are a few T-SQL statements to illustrate how pwencrypt/pwcompare work:

create table #MetroTest
(
UserLogIn varchar(10),
UserPass nvarchar(256)
)

insert #MetroTest
(UserLogIn,UserPass) values ( 'MaryMary', PWDENCRYPT('QuiteContrary'))

select UserLogIn, password = 'QuiteContrary', PWDCOMPARE('QuiteContrary', UserPass, 0) from #MetroTest
select UserLogIn, password = 'QuietContrary', PWDCOMPARE('QuietContrary', UserPass, 0) from #MetroTest

It is possible to write user defined functions which will encrypt/decrypt and compare. An excellent example of this is Peter Larsson's RC4 hash:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76258

Why does my BlackBerry Storm keep shutting off?

The most likely answer if you are in the USA is that you have your network setting set to "Global". Set it to "1XEV" -- pretty much most metro areas in the USA can default to that setting without the same struggle. To do this, follow these steps:

Go to options (wrench icon)
Go to Mobile Network
There are 3 settings; mobile network, network selection mode and network technology. If the button for Network Technology says Global or GSM/UMTS -- select 1XEV.
Recycle the device.

This should clear up the maddening recycling issue!

How can I stop calls going into "Mute" or "Speaker" mode when I'm on a BlackBerry Storm?

If you are an animated person when speaking on the phone, you've no doubt inadvertantly tapped the touch pad with your cheek or chin. There is a SUPER 3rd party utility called: TalkLock from Cellavant. I installed this on my device and have been grateful ever since!

http://www.cellavant.com/TalkLock

Why am I getting a binding error on an *.msi file for a re-deployment of a BizTalk application that doesn't contain any bindings?

When this dreaded error crops up, its usually right in the midst of a fast and furious development effort. That's always been the case for me! To solve it in a hurry, I just dub the app with a higher version number, recompile and move on--at least that is what I did when I was a bts developer. Now that I'm a bts admin, I've delved into the topic more. Although the re-versioning scenario works, it is possible to re-deploy an *.msi package with the same version number using these few steps:

Go to \Documents and Settings\[deployment acct username]\Application Data\Microsoft\BizTalk Server\Deployment\BindingFiles. (READ: you should see 2 on a reploy, one with a tilda [~]; 1 without)
Since a new app with a new version # creates a BindingInfo xml file on an initial deployment, rename the one you find there to something else. Get rid of the new one (file without the ~) that failed.
Import/re-deploy the *.msi file again.

Why do these steps work? It appears that new app deployments create the Binding.Info.xml file based on the initial assembly reflection which has the orchestrations, etc. -- however -- when you redeploy an existing app, the binding file gets generated based on the details stored in the bts management database (BizTalkMgmt) for that existing assembly. The binding file that gets generated from that process is the one without the ~.

When you get rid of the base/initial bindings file, bts does not try to apply the bindings it expects to find (even if there aren't any) like it does the first time it recognizes the file.

These steps should solve the "ghost" bindings issue, but if not for some reason, there's always the tried and true re-versioning of the app!!

Why am I getting the error: CLSID_BizTalkPropertyBagFactory error in the BTS Admin console

Perhaps you've experienced the scenario where you hit the F5 key on the Group Overview in the BizTalk Admin console and instead of seeing your message queues, you see instead the very ugly () message:

Failed to create a CLSID_BizTalkPropertyBagFactory COM component installed
with a BizTalk server. A dynamic link library (DLL) initialization routine
failed. (WinMgmt)

Go to the event viewer and look for any COM/COM+ errors under applications. Chances are you will see a conflict with WMI services--perhaps a threading error. Since the SSO, BizTalk Admin Console, SCOM...(even event viewer) use WMI for the MMC console, one of the DLLs for WMI may have become unavailable. If WMI is not available, or the WMI service has crashed, the console for the BizTalk Admin can fail with this error message as its swan song ().

Aaron Tiensivu's blog offers a way to isolate WMI issues on svchost:

http://blog.tiensivu.com/aaron/archives/294-Suspect-that-WMI-is-crashing-your-SVCHOST-Run-it-standalone..html

There are 2 solutions that I know of, 1 radical and 1 less so...

restart the server [waaay radical, but effective]
restart the WMI windows service

Why am I getting the error: SQL Network Interfaces: The Local Security Authority cannot be contacted.[SQL Native Client]Cannot generate SSPI context

The full error message looks like this:

[SNAC] “[SQL Native Client]SQL Network Interfaces: The Local Security Authority cannot be contacted.[SQL Native Client]Cannot generate SSPI context”
[MDAC] “Cannot generate SSPI context”;
[.Net1.0/2.0]” Failed System.Data.SqlClient.SqlException: Cannot generate SSPI context”

When this message occurs--especially when the same access 20 minutes ago worked, chances are you've logged off of your primary network. An example would be that at work you were using an ODBC connection to reverse engineer a database into a Visio diagram on a local MSSQL Express instance, then when you went to work on it after hours at home offline the connection fails with this error message.

In essence, your desktop/laptop with the local db instance is disconnected from its domain. The immediate solution in this case would be to VPN back into the network and keep moving forward!

There are other causes, and there are workarounds to the disconnected network roadblock that are explained exceptionally well in this MSDN blog:

http://blogs.msdn.com/sql_protocols/archive/2005/10/19/482782.aspx

Can I insert multiple rows with a single insert statement?

Yes! .....(Now, we're not talking about bulk inserts--which is a completely different task... we're just talking about a small number of rows that need to get inserted into a relatively static table)..

Typically folks do something like this to quickly add rows into a reference table:

INSERT INTO ReasonType
(DisplayName,Description)
VALUES
('Delay','Reason for a project delay')
GO
INSERT into ReasonType
(DisplayName,Description)
VALUES
('Cancellation','Reaon for a project cancellation')
GO

...but it is possible to do this instead...

INSERT INTO ReasonType (DisplayName, Description)
    SELECT 'Delay' ,'Reason for project delay'
    UNION ALL
SELECT 'Cancellation' ,'Reason for project cancellation'
    UNION ALL

How do I view/see the PATH in a windows environment?

For those of you working with LINUX/UNIX and you've typed:

c:> echo $PATH

..in a command line expecting to see everything in your path on startup and saw only 'PATH', don't fret!

type this instead:

c:> echo %path%

Why am I getting the "operation has been canceled" message in Outllook?

If you are getting the message...

This operation has been canceled due to restrictions in effect on this computer

...whenever you try to click on a hyperlink in a mail message when you are in Outlook or in a Word document, it is primarily because your default web browser has a 3rd-party setting that doesn't work and play well with Office products. For example if you are using the FireFox web browser, try changing the default web application to be Internet Explorer.

To do this, follow these steps:

Start>Control Panel>Add Remove Programs>Set Program Access and Defaults
Once you're in Program Access and Defaults, go to Custom
Click on Internet Explorer; click on the Enable access to this program checkbox.

Try the hyperlink again. If the problem still occurs after this, that means that there is a setting still active under Internet Explorer which is now your default. To bring back Internet Explorer to where it was before any 3rd-party tools (e.g. Google Toolbar, etc.) may have been installed, do the following:

In Internet Explorer, click on Options (if IE 7, go to Tools, Options)
Go to Advanced.
Click on Reset button.

Note: Once you click on the reset button all your browser add ons will be disabled.

Once you complete this, follow steps 1-3. This should eliminate this message from reappearing.

Where Can I Find Remote Desktop?

Remote Desktop is actually called mstsc.exe and can be found under: %SystemRoot%\system32 (Usually C:\Windows\system32.

Why does IE crash with this error "Microsoft Visual C++ Runtime Library Runtime error"/"R6025 pure virtual function call" ?

There are several possibilities, however one of the most common reasons is the combination of Internet Explorer version 7 and the Google Desktop/Toolbar. For some reason, the plugin does not seem to be compatible. Try removing the Google Desktop and/or Toolar.

Why do I get the error "eznwupd.exe has stopped working" on bootup?

The eznwupd.exe program is an update program for the greater spyware program called eznorun.exe. To get rid of this menace, do the following:

Go to c:\Program Files\ezn. Delete the directory. Empty the trash to actually remove it.
Go to run at the start menu and type: regedit. Search for and remove all keys, values, etc. to eznorun.exe
Go to to run at the start menu and type: msconfig. Go to the Startup tab. Delete the eznorum.exe entry.

That should do it! You should no longer see this message appearing on your computer.

Why won't my old x86 (32b) ASP .NET Applications work on my New x64 (64bit) Server?

I can't answer that question in detail, but I can say that on Windows 2003 servers under x64 you must explicitly set up IIS to Allow Web Service Extensions for ASP xxxxx (32) applications. In a console window, type:

cscript %SYSTEMDRIVE%\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 1

Then proceed with the following steps:

Open IIS; Web Service Extensions
Click on Allow All Web Service extensions for a specific application
Choose ASP Net vX.Xxxxx (32)
Click OK
In a command window type IISRESET

This should help get your application back online!

Other references include:

http://msdn.microsoft.com/en-us/library/k6h9cz8h(VS.80).aspx

http://www.iisadmin.co.uk/?p=15#more-15

Why Won't My Dell Latitude D820 Show Anything on My External Monitor?

On your keyboard you'll see the function 8 key (F8). This is the key that controls flip-flopping between lcd/crt. You'll also see another key near the control key [Ctrl] called FN. FN is the function key.

Try this key sequence to invoke the second/external monior:

FN + F8 (click these at the same time).

This should wake up the monitor!