Monday, November 23, 2009
This should work for SQL Server 2005/2008--
ALTER SCHEMA SchemaNameForTarget TRANSFER SchemaNameOfSource.MyTableName
example:
ALTER SCHEMA RiskEvaluation TRANSFER dbo.ConvertibleDebenture
Monday, November 02, 2009
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)
Friday, August 07, 2009
If you want to reformat data, use the function convert as shown:
select convert(varchar, cast(18 as integer)) as 'a number'
Thursday, August 06, 2009
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
Tuesday, June 16, 2009
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());
}
}
}
}
Tuesday, June 09, 2009
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 ! 
Friday, May 29, 2009
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) 
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
)
usually your host file (DNS/ip addresses) can be found under:
c:\windows\system32\drivers\etc
Thursday, May 28, 2009
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.
Thursday, March 26, 2009
Here's an example...
DECLARE @LocID
SELECT @LocID = LocationID
FROM Locations
WHERE LocationName = 'WAREHOUSE X'
Friday, March 13, 2009
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!
Wednesday, March 11, 2009
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
Tuesday, March 03, 2009
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!
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
Sunday, January 25, 2009
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!!
Tuesday, January 20, 2009
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
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
Tuesday, January 13, 2009
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
Thursday, December 18, 2008
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%
Sunday, December 07, 2008
-
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.
Friday, December 05, 2008
Remote Desktop is actually called mstsc.exe and can be found under: %SystemRoot%\system32 (Usually C:\Windows\system32.
Monday, December 01, 2008
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. 
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.
Wednesday, November 26, 2008
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