Microsoft Word 2010 and Blogs March 24, 2010
Posted by williamhatter in Uncategorized.1 comment so far
Today, I decided to remove Microsoft Office 2007, and try the Beta of Office 2010 on my work computer. The only bad part was that I had to remove all the 2007 products I was using due to incompatibilities with 2007 being 32-bit, and 2010 being 64-bit enabled. I, of course, run Windows 7 Ultimate x64, so I wanted to leverage the power of the new Office 2010 x64 Suite.
I typically use Word to write my blog posts before “copy and paste” into the wordpress editor. Usually works well, but I have to do some editing before being able to post it, which of course can be very frustrating.
This time however, when I went to open a new document, I saw a “Blog Page” option. (see screen shot below)
I was able to successfully setup my account to log into wordpress and WHAMO! I was able to add the title, add categories, make all the textual changes I want, add pictures inline by just pasting them in, and do any other editing that I normally would have done with a Word Document.
I apologize if my posts appear “off” while I’m attempting this, but it has me pretty excited and makes writing my blog posts just a little bit easier.
If you don’t have it, I recommend you give it a shot.
Exchange 2007 Active Directory Nightmare March 24, 2010
Posted by williamhatter in Microsoft Exchange.add a comment
Today we decided to reinstall our test Exchange Server 2007 since we needed to use the machine it was originally slated to be on for something else. Unfortunately, we couldn’t just “reinstall” exchange in the domain due to Active Directory Nightmares.
I spent nearly 2 hours trying to solve this problem before finally hitting on the solution.
The problem occured because we had deleted the Security OU from the Active Directory (we had numerous orphaned Exchange 2007 objects and duplicates from failed installs). We did this because we wanted a fresh start for the install in the AD. We started receiving the following errors however during the install:
The well-known object entry B:32:A7D2016C83F003458132789EEB127B84:CN=Exchange Servers1ADEL:e6db790a-2bdf-4cf6-948e-e10fa2fd74da,CN=Deleted Objects,DC=bryan,DC=local of the otherWellKnownObjects attribute on container object CN=Configuration,DC=bryan,DC=local points to an invalid DN or a deleted object.
Let the nightmare begin.
As Falstaff said: “The better part of valor is discretion” (Henry IV, Part I, Act V, Scene IV)
However, we’re gung-ho IT pros, so discretion isn’t in our dictionaries or vocabularies. Due to an oversight by deleting an entire OU without knowing all the full ramifications. Thankfully, even though we are in an enterprise where we have both Exchange 2003 and Exchange 2007, this OU didn’t affect our mail ability.
The fix took me two hours of searching, piecing together, and experimenting to finally find the appropriate solution. Of course, the example on Microsoft’s site really fit the bill, and would have saved me two hours of searching had Google not failed me.
You can read the complete article here: http://technet.microsoft.com/en-us/library/bb288907(EXCHG.80).aspx
But I’ll save you some time by pinpointing the exact solution to the problem.
- If you don’t have it, get Ldp.exe as it is one of the MOST invaluable AD Tools you can have.
- Run Ldp.exe and connect to your domain
- Expand Configuration >> Services and double click on Microsoft Exchange.
- Right click on Microsfot Exchange, and select Modify.
- Type otherWellKnownObjects into the Edit Entry Attribute field.
- Select the Delete option
-
In the ldp.exe results pane (the right hand side) find otherWellKnownObjects.
-
Copy the entry and paste into notepad
- Organize to taste
- Select an entire entry (leave out the trailing semi-colon)
-
Paste the entry into the Values textbox of the Modify dialog box, then press the Insert button.
- Repeat for each entry.
-
- Once you have all the Entries in the box, hit the Run button.
Voila!
You have cleared the issue, and should now be able to rerun Exchange 2007′s setup /p and have it complete successfully.
If you have any other further questions or problems, feel free to ask, and I’ll help you out how I can.
Exchange 2007 Server Installation Hangups December 24, 2009
Posted by williamhatter in Uncategorized.add a comment
This of course is not a complete error list, but rather common errors that I’ve seen floating around the net.
While setting up our new Exchange 2007 server, I ran across the following errors:
“The Exchange organization is not in native mode…”
and
“Microsoft® Exchange Server 2007 setup cannot continue because one or more Active Directory Connectors have been found…”
Of course, I am going to encourage you to run the Exchange Best Practices Analyzer and the Exchange Prerequisites Check before starting your install. I’m impatient, and since I usually do typical installs, I run through the setups, and fix the bugs I encounter. And it usually provides me with a nice new blog entry.
The story:
We recently upgraded out fax server to Esker 5. Since this version supported x64 architecture, we decided to put it on Server 2003 R2 x64. Being the adventurous souls that we are, we also decided to implement a new Exchange server, going with Exchange 2007.
For those of you who don’t know this fact (I didn’t earlier this year), Exchange 2007 is ONLY available in x64 architecture. While I applaud Microsoft for this forward move, a little more information would have been wonderful.
The problem/fix:
After finishing our DeliveryWare setup, I started working on the Exchange install. Since it was a clean install, I didn’t expect the hang ups that I did. I hope that someone out there can find this information useful, and perhaps save themselves the hours of searching that I went through to solve this.
Before starting the setup, you must ensure that the account you are using to install has Domain, Schema and Enterprise Admin permissions. Otherwise your install will fail almost immediately. That of course, is the easiest task of the process.
If you are installing the Exchange 2007 on a new server (which of course you’ll have to do if you have an existing 2003 server on an x86 Server platform), you must ensure that your Exchange 2003 server is running in Native Mode.
If you’ve done a previous upgrade from Exchange 5 or 5.5 to Exchange 2003, the server is defaulted in Mixed Mode for mailbox migrations and connections. I went ahead and upgraded our forest and domain levels to Native 2003 as well just to be on the safe side. I’m not 100% positive this move has to be made, but unless you’ve still got NT4 or Windows 2000 DCs in your domain, it’s a safe move.
Our Exchange 2003 server had been setup years ago by a consulting company that apparently didn’t know its arse from a hole in the ground. And our previous IT guy(s) were nothing less than morons. Why would you leave legacy components in your domain when you have no legacy systems to connect to? All they did was create more headache and hassle for us (though it did provide a helpful blog for you).
When I went to upgrade our Exchange 2003 to Native Mode (open Exchange System Manager, right click on your organization, and select “Change Mode”) the option was greyed out. Frustrated with the lack of help from Microsoft as to why this was happening, I had to dig a lot more to find out why. If you’re like me, you care less about the technical reasons “why” and more about how to fix it. Let’s get to it.
The first thing you need to do is check to see if you have the Active Directory Connector installed on your Exchange 2003 server. If so, there’s your problem. As long as you no longer have any Exchange 5.5 servers in your domain, and you’re not replicating any data with Exchange 5.5, it’s safe to get rid of this.
However, we can’t just go into Add/Remove Programs and delete it, we need to remove the replication rules first. We need to start up Active Directory Connector (should be found in your Exchange Folder), and remove the replication rules. One thing you do NOT want to remove is anything that starts with Config_CA. This will get remove automatically when we remove the ADC.
Give the server(s) time to replicate their changes throughout the domain/forest, then proceed to the next step.
Next we need to open Exchange System Manager, and expand Tools. Right Click on Site Replication Services and delete them.
After we’ve removed all the replication services, we need to go into services, and disable the Active Directory Connector and the Site Replication Service services. Set both to disable.
After this has been done, we need to load our Exchange 2000/2003 disc, and navigate to \ADC\i386 and run the Setup.exe program. This will allow you to remove the Active Directory Connector.
Once you have completed these steps, restart the Exchange 2007 installation, and you will no longer receive the above errors.
Happy Exchanging!
Merry Christmas December 24, 2009
Posted by williamhatter in Uncategorized.add a comment
To all my readers out there, I wish you a Merry Christmas and a Happy New Year.
In the coming year, one of my resolutions is to be more dutiful with providing good IT insights, tips and tricks. Programming snafu’s as well as SQL and Exchange information.
I am currently working on installing Exchange 2007 in our network, and hope to share with you some of the hangups I found while trying to attempt the install side-by-side with Exchange 2003 in our domain. I hope it will save someone the hours it took for me to get this stuff straightened out.
Until then, be safe and smart.
Windows Mobile ActiveSync and New Exchange Certificates December 14, 2009
Posted by williamhatter in Microsoft Exchange.Tags: ActiveSync, Certificates, Exchange, Microsoft, OWA, SSL
add a comment
Last week our company ran into a problem with upgrading our Exchange VeriSign security certificate and the Windows Mobile phones from Verizon that some of our users were carrying. Unfortunately, after many calls to VeriSign, Verizon and Samsung, we were still unable to get ActiveSync to replicate any changes from Exchange to the user’s phones without disabling SSL.
As any security concious administrator knows, you NEVER want to disable SSL, especially when sending/receiving delicate company information.
The problem was that the phone’s root certificates didn’t include VeriSign(really Samsung…that’s a plainly major oversight), and the phone’s wouldn’t download the new certificate properly without having a trusted root authority. Thankfully, the solution is petty straightforward, and relatively simple. The following instructions will work for both third-party certificates, or your own certifcates.
If you’re a small business and want to avoid the not insignificant cost of purchasing a certificate every year, you should start by looking SSL-enabling OWA 2003 using your own certificate authority at MSExchange.org.
Before we’re able to export the required certificates, we need to make sure that the one(s) we are looking for are actually installed. We do this by browsing to the site that has the certificate we need, and make sure to install certificate when prompted.
After we have the appropriate certificate installed on our computer, we have to export the current third-party certificate installed locally. If you are running XP, Vista, or Win7, open Start->Run->MMC
After the Management Console opens, we need to add the Certificates Snap-in by going to File->Add/Remove Snap-in where we will select the Certificates snap-in and add it to the console. We want to select “Computer Account” for the management style. Once the console has been added in, we need to expand the Trusted Root Certificates branch, and then select Certificates.
You should now be looking at a list of all the installed root certificates that are on your computer. We need to select our certificate that we wish to export, and right click on it, and select All Tasks->Export. When given the export options, we need to make sure to select Base-64 style for the export. I usually create a C:\CABS location where I’m going to be doing all my work. Save the file with whatever name you desire (I usuall put it as cert.cer) making sure to use a .CER extension.
Once the file is saved, we need to create the following within Notepad (I use Programmer’s Notepad for all my text work):
<wap-provisioningdoc>
<characteristic type=”CertificateStore”>
<characteristic type=”ROOT” >
<characteristic type=”CERTIFICATE THUMBPRINT“>
<parm name=”EncodedCertificate” value=”CERTIFICATE TEXT“/>
</characteristic>
</characteristic>
</characteristic>
</wap-provisioningdoc>
After we have the basic XML created, we want to save it as _setup.xml in our C:\CABS directory.
Before we close our MMC Console, we want to double-click on the certificate we want to export. Next, click on the Details tab, and scroll down until you find the Thumbprint.
Click on Thumbprint, and copy the algorithim into your text editor. Remove all spaces from the thumbprint that you copied, and add it to _setup.xml as demonstrated below.
<wap-provisioningdoc>
<characteristic type=”CertificateStore”>
<characteristic type=”ROOT” >
<characteristic type=”85371ca6e550143dce2803471bde3a09e8fxxxxx”>
<parm name=”EncodedCertificate” value=”CERTIFICATE TEXT“/>
</characteristic>
</characteristic>
</characteristic>
</wap-provisioningdoc>
Now,we need to open the CER file that we exported earler. The text will look something like this:
—–BEGIN CERTIFICATE—–
MIIDAjCCAmsCEH3Z/gfPqB63EHln+6eJNMYwDQYJKoZIhvcNAQEFBQAwgcExCzAJ
BgNVBAYTAlVTMRcwFQYDVQQKEw5WZXJpU2lnbiwgSW5jLjE8MDoGA1UECxMzQ2xh
c3MgMyBQdWJsaWMgUHJpbWFyeSBDZXJ0aWZpY2F0aW9uIEF1dGhvcml0eSAtIEcy
MTowOAYDVQQLEzEoYykgMTk5OCBWZXJpU2lnbiwgSW5jLiAtIEZvciBhdXRob3Jp
emVkIHVzZSBvbmx5MR8wHQYDVQQLExZWZXJpU2lnbiBUcnVzdCBOZXR3b3JrMB4X
DTk4MDUxODAwMDAwMFoXDTI4MDgwMTIzNTk1OVowgcExCzAJBgNVBAYTAlVTMRcw
FQYDVQQKEw5WZXJpU2lnbiwgSW5jLjE8MDoGA1UECxMzQ2xhc3MgMyBQdWJsaWMg
UHJpbWFyeSBDZXJ0aWZpY2F0aW9uIEF1dGhvcml0eSAtIEcyMTowOAYDVQQLEzEo
YykgMTk5OCBWZXJpU2lnbiwgSW5jLiAtIEZvciBhdXRob3JpemVkIHVzZSBvbmx5
MR8wHQYDVQQLExZWZXJpU2lnbiBUcnVzdCBOZXR3b3JrMIGfMA0GCSqGSIb3DQEB
AQUAA4GNADCBiQKBgQDMXtERXVxp0KvTuWpMmR9ZmDCOFoUgRm1HP9SFIIThbbP4
pO0M8RcPO/mn+SXXwc+EY/J8Y8+iR/LGWzOOZEAEaMGAuWQcRXfH2G71lSk8UOg0
13gfqLptQ5GVj0VXXn7F+8qkBOvqlzdUMG+7AUcyM83cV5tkaWH4mx0ciU9cZwID
AQABMA0GCSqGSIb3DQEBBQUAA4GBAFFNzb5cy5gZnBWyATl4Lk0PZ3BwmcYQWpSk
U01UbSuvDV1Ai2TT1+7eVmGSX6bEHRBhNtMsJzzoKQm5EWR0zLVznxxIqbxhAe7i
F6YM40AoiuIIO897UYrxaZLvcRTDOaxxp5EJb+RxBrO6WVcmeQD2+A2iMzAo1KpY
oJ2daZH9
—–END CERTIFICATE—–
We need to remove the BEGIN CERTIFICATE and END CERTIFICATE portions of this text. After we have done that, we should remove all line breaks, making a single continuous stream of data. After we have done this, we need to put the single string of text into the appropriate location in our _setup.xml file. Your finished file should look similar to the following:
<wap-provisioningdoc>
<characteristic type=”CertificateStore”>
<characteristic type=”ROOT” >
<characteristic type=”85371ca6e550143dce2803471bde3a09e8fxxxxx”>
<parm name=”EncodedCertificate” value=”MIIDAjCCAmsCEH3Z/gfPqB63EHln+6eJNMYwDQYJKoZIhvcNAQEFBQAwgcExCzAJBgNVBAYTAlVTMRcwFQYDVQQKEw5WZXJpU2lnbiwgSW5jLjE8MDoGA1UECxMzQ2xhc3MgMyBQdWJsaWMgUHJpbWFyeSBDZXJ0aWZpY2F0aW9uIEF1dGhvcml0eSAtIEcyMTowOAYDVQQLEzEoYykgMTk5OCBWZXJpU2lnbiwgSW5jLiAtIEZvciBhdXRob3JpemVkIHVzZSBvbmx5MR8wHQYDVQQLExZWZXJpU2lnbiBUcnVzdCBOZXR3b3JrMB4XDTk4MDUxODAwMDAwMFoXDTI4MDgwMTIzNTk1OVowgcExCzAJBgNVBAYTAlVTMRcwFQYDVQQKEw5WZXJpU2lnbiwgSW5jLjE8MDoGA1UECxMzQ2xhc3MgMyBQdWJsaWMgUHJpbWFyeSBDZXJ0aWZpY2F0aW9uIEF1dGhvcml0eSAtIEcyMTowOAYDVQQLEzEoYykgMTk5OCBWZXJpU2lnbiwgSW5jLiAtIEZvciBhdXRob3JpemVkIHVzZSBvbmx5MR8wHQYDVQQLExZWZXJpU2lnbiBUcnVzdCBOZXR3b3JrMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDMXtERXVxp0KvTuWpMmR9ZmDCOFoUgRm1HP9SFIIThbbP4pO0M8RcPO/mn+SXXwc+EY/J8Y8+iR/LGWzOOZEAEaMGAuWQcRXfH2G71lSk8UOg013gfqLptQ5GVj0VXXn7F+8qkBOvqlzdUMG+7AUcyM83cV5tkaWH4mx0ciU9cZwIDAQABMA0GCSqGSIb3DQEBBQUAA4GBAFFNzb5cy5gZnBWyATl4Lk0PZ3BwmcYQWpSkU01UbSuvDV1Ai2TT1+7eVmGSX6bEHRBhNtMsJzzoKQm5EWR0zLVznxxIqbxhAe7iF6YM40AoiuIIO897UYrxaZLvcRTDOaxxp5EJb+RxBrO6WVcmeQD2+A2iMzAo1KpYoJ2daZH9“/>
</characteristic>
</characteristic>
</characteristic>
</wap-provisioningdoc>
Simple so far isn’ it? It’s a little bit of tediousness to gather the data we need, but trust me, the end efforts will make it all worthwile.
After we’ve saved our finished _setup.xml file, we need to open a command prompt, and navigate to our C:\CABS directory. Once there simply type in: MAKECAB _setup.xml <yourcertnamehere>.cab
Now that we have a CAB file, simply transfer it to the offending phone, and open it up. You should be prompted to add the certificate, which of course you want to do.
After you have installed all necessary certificates (I had to install 2 for VeriSign), just open your ActiveSync configuration, make sure SSL encryption is enabled, save the configuration, and then attempt to synchronize.
If you followed the steps above properly, and imported the appropriate certificates, you should now be able to synchronize with your Exchange server without any problems.
SQL SSMS 2008 on x64 Operating System December 11, 2009
Posted by williamhatter in SQL.Tags: SQL SSMS 2008 DTS, SQL SSMS 2008 x64, SQL SSMS x64, x64 DTS
add a comment
I know it’s been a while since my last post, and I apologize for that. Lots been going on in my life, and I’ve been slipping lately. I’m hoping to finally be able to post information that I wanted to do when I started. I’m hoping that from now on, I’ll be able to get some good quality information out there on a daily to bi-daily basis.
With that said, on with the show:
Have you ever used SQL Server Management Studio on an x64 system and try to manage legacy DTS jobs? If so, then unless you’ve done the voodoo uneedtoo, you’ll run into the headaches and heartaches of trying to solve this capracious little problem without pullling out all your hair (I’m bald now…trust me).
So I’ve decided to make one succinct location. In this walk-through we’ll be installing the DTS Run-Time components and setting them up to work properly on your x64 operating system. I recommend having done a slip-stream install of SQL Server 2008.
Obviously by now you have instaled SSMS 2008 on your computer. If you did not install “Backwards Compatibility” as well as “Integration Services”. The Integration Services install the ActiveX Script Task and the DTS Package Migration Wizard. You can download and install the current Backwards Compatibility Installer from SQLServer2005_BC_x64. Or, you can install it from the setup for SQL Server 2008.
Once you have installed this file, you’ll need to get the SQL Server 2000 DTS Design Components. These can be downloaded from here: SQLServer2005_DTS but understand, this file is ONLY available for x86 platforms. But it will install just fine on your new x64 box, and we want the 32-bit components anyway.
Once this file has been installed, we need to copy some of the .dlls from the x86 portion to the x64 portion.
You need to copy: SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL from %ProgramFiles (x86)%\Microsoft SQL Server\80\Tools\Binn to %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE
You’ll also need to copy: SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL from %ProgramFiles (x86)%\Microsoft SQL Server\80\Tools\Binn\Resources to %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id% %lang_id% is the language id of your installed locale. For English user this is 1033.
Here are some quick commands to do the above tasks for you automatically:
COPY “C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\SEMSFC.DLL” “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE”
COPY “C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\SQLGUI.DLL” “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE”
COPY “C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\SQLSVC.DLL” “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE”
COPY “C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\Resources\1033\SEMSFC.RLL” “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033″
COPY “C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\Resources\1033\SQLGUI.RLL” “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033″
COPY “C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\Resources\1033\SQLSVC.RLL” “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033″
Once you restart SSMS 2k8, you should be abe to open, execute, and modify legacy DTS packages.
I wish I could take credit for this helpful article, but much of the material, and links are from the MSDN site located here: How to: Install Support for Data Transformation Services Packages
I only recently found this article on MSDN helpful, as much of the instructions on there were gibberish until the past 3 months, and the locations they provided originally were incorrect for the locations of the dll and rll files.
Enjoy and happy SQL’ing!
Webroot provides tips on Staying Safe using Social Network Sites September 3, 2009
Posted by williamhatter in Security Ramblings.add a comment
|
Social Media & Security: How to stay safe while you network online. |
|
|
In the News: Facebook & Twitter attacks, Koobface threat
Facebook, Twitter and LiveJournal recently reported they were the targets of “denial-of-service” attacks. Hackers launch these attacks by infecting hundreds or thousands of so-called “zombie” computers with malicious software. Once the computers are infected, hackers remotely instruct them to simultaneously attack a targeted Web site, flooding it with so much traffic that it becomes inaccessible.
Koobface is another pervasive threat. It began on Facebook, but now targets a host of other social networks. This worm sends fake messages and links to friends, usually encouraging them to watch a video. Depending on the site, the worm can also post infected links on walls and changes the account user’s “Status” by modifying the text and adding a link. Read more on our blog: “Koobface: Not Just for Facebook Anymore” |
|
Stay safe while you network online
|
Facebook to allow 3rd party advertisers to use your pictures FOR FREE! July 24, 2009
Posted by williamhatter in Security Ramblings.2 comments
Facebook has decided, in their infinite wisdom, to allow 3rd Party advertisers to use your profile pictures without any notification to us.
From kosertech’s blog earlier today:
“Facebook has chosen to allow 3rd party advertisers to use of your uploaded pictures without permission as a default. Basically this means that your face/avatar may show up in a singles ad on your friends pages. It would have been nice if Facebook set the default to “no one” but they have chosen to enable this use of your photos by 3rd parties by default.
Managing your privacy on Facebook is a confusing difficult process and something you would do well to take a look at. There is a good write up at mashable on this subject.
You can disable the advertisers use of your photos by logging into facebook and selecting Settings -> Privacy, then choose News feed and Wall. There are two tabs there select the Facebook Ads tab and select “no one” and click save. Your photos will now not be shown in 3rd party ads.”
Thank you Vince Koser for posting this wonderful info.
This is a horrible indictment of Facebook’s policies if you ask my opinion. For them to allow our Profile Pictures, and who knows what else, to be used by 3rd party advertisers is a complete disregard of their user’s privacy, and desires.
I encourage everyone to contact Facebook to have them remove this horrible policy. After setting up your security first of course!
Temporary Tables and SELECT INTOs July 9, 2009
Posted by williamhatter in SQL.add a comment
I was working on moving a DTS job into Stored Procedures this morning, and ran across an issue that had me banging my head against the wall for about 30 minutes. We were moving from the DTS job because we no longer needed to produce output files from the job, and all the Tasks in the DTS were Execute SQL Tasks anyway.
The problem I ran into was that the previous designers had used OPENROWSET() functions to pull data from other servers (all of which were running SQL 2000 except the calling server, which was SQL 7 on an NT4.0 box ***SHUDDER***).
While trying to remove the SELECT * INTO #TmpTbl FROM OPENROWSET(*blah*) and make into more readable structure, I kept running into problems with my FROM statement. The problem was that the FROM statements contained UNIONS and AGGREGATES that weren’t resolving properly. Double Quotes, and mismatched Parenthesis were also an issue.
Here’s an example what what I was dealing with:
Example 1:
SELECT A.*
INTO #Customers
FROM OPENROWQUERY(‘server’, ‘userid’, ‘password’, ‘
SELECT A.clmn1, A.clmn2, A.clmn3, B.clmn1, B.clmn2
FROM [DB].dbo.table A
INNER JOIN ( SELECT B.clmn1, B.clmn2, B.clmn3 FROM [DB].db.table B)
ON B.clmn1 = A.clmn1
WHERE (a.clmn1 = customerid)
‘) A
And some had GROUP BYs and ORDER BYs on the OPENROWQUERY SELECT statements, that of course, as good little dba’s we know is a no-no.
Example 2:
SELECT A.*
INTO #Customers
FROM OPENROWQUERY(‘server’, ‘userid’, ‘password’, ‘
SELECT A.clmn1, A.clmn2, A.clmn3, B.clmn1, B.clmn2
FROM [DB].dbo.table A
INNER JOIN ( SELECT B.clmn1, B.clmn2, B.clmn3 FROM [DB].db.table B)
ON B.clmn1 = A.clmn1
WHERE (a.clmn1 = customerid)
ORDER BY A.clmn3
‘) A
The solution to this seems pretty straightforward. Remove the OPENROWQUERY junk, and go on:
Example 1-a:
SELECT A.*
INTO #Customers
FROM (
SELECT A.clmn1, A.clmn2, A.clmn3, B.clmn1, B.clmn2
FROM [DB].dbo.table A
INNER JOIN ( SELECT B.clmn1, B.clmn2, B.clmn3 FROM [DB].db.table B)
ON B.clmn1 = A.clmn1
WHERE (a.clmn1 = customerid)
) A
Example 2-a:
SELECT A.*
INTO #Customers
FROM (
SELECT A.clmn1, A.clmn2, A.clmn3, B.clmn1, B.clmn2
FROM [DB].dbo.table A
INNER JOIN ( SELECT B.clmn1, B.clmn2, B.clmn3 FROM [DB].db.table B)
ON B.clmn1 = A.clmn1
WHERE (a.clmn1 = customerid)
ORDER BY A.clmn3
) A
Unfortunately, once you do this, you’re up a creek.
The reason is that even though you have valid SubQueries within your FROM statement, SQL Server doesn’t know what that A at the end is supposed to be.
We all know we can assign a FROM statement to a more useable format such as:
SELECT A.*
FROM [DB]..table A
And since we’re using SubQueries as in our FROM, you would expect this syntax to work.
However, SQL can’t figure it out.
The proper formatting of the above would look like this:
Example 1-b:
SELECT A.*
INTO #Customers
FROM (
SELECT A.clmn1, A.clmn2, A.clmn3, B.clmn1, B.clmn2
FROM [DB].dbo.table A
INNER JOIN ( SELECT B.clmn1, B.clmn2, B.clmn3 FROM [DB].db.table B)
ON B.clmn1 = A.clmn1
WHERE (a.clmn1 = customerid)
) AS A
By assiging the AS clause to the end of the FROM, we’re able to tell SQL what data to insert properly into the temporary table.
But wait! What about Example 2???? If you were to try the above code with Example 2, you’d recieve the nice little following error:
Server: Msg 1033, Level 15, State 1, Line 6
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
This is because SQL can’t guarantee ordered output within an inline fuction, derived table or subquery. Why? I don’t know honestly. We could look at
the Query Plan to see what’s happening, but that’s a bit beyond the scope of this article.
So how do we fix it? Simple, we move the Order By clause outside of the Inner Select statment, and put it into the Outer Select which is doing our Insert.
SQL will recognize that you’re doing the INSERT, and put the data in the way you originally wanted.
Example 2-b:
SELECT A.*
INTO #Customers
FROM (
SELECT A.clmn1, A.clmn2, A.clmn3, B.clmn1, B.clmn2
FROM [DB].dbo.table A
INNER JOIN ( SELECT B.clmn1, B.clmn2, B.clmn3 FROM [DB].db.table B)
ON B.clmn1 = A.clmn1
WHERE (a.clmn1 = customerid)
) AS A
ORDER BY A.clmn3
I hope this helps save someone else from having to do a lot of research into SELECT INTO statements, and FROM (SUBQUERY) nightmares.
Why Virtual Environments are the Schiznitz! June 26, 2009
Posted by williamhatter in Daily Rambling.add a comment
For the past day, I’ve been struggling to get our ERP system functional in our test environment. Our entire test environment is running on a VMWare ESX Server connected to a 15 TB SAN. Pretty sweet.
Our Test and Production environments are on separate network segments connected to the same switch. This allows us to connect to our Test environment from our workstations just by giving ourselves IP Aliases also on our Test Network Segment.
The problem I ran into (as I wrote yesterday about hardcoding things into applications) was one of our services was hardcoded with an IP address for a production environment. I needed to be able to change this to point to our test environment. The caveat to the whole scenario however was the fact that to be able to change the hardcoded setting, you had to attach to the service that used the setting, and the service wouldn’t start up properly because it didn’t have the proper IP Address assigned.
Following me so far? Took some time to understand the design of the system.
I looked for everything from config files to .class files that used this connection string.
What did I find? NADA!
So after fighting with Tech Support for an entire day on their product (and being told I need to pay for a $200 an hour consultant because we’re “non-hosted”) I finally hit upon a solution that worked.
And now as to why Virtual Environments are the “Cat’s Meow”:
Since both servers run on VMWare’s ESX Servers on different boxes, and both systems are connected to the same Switch, just with A Class Address differences, I was able to disable the production environment’s network connection and enable the test environment’s network connection (after reconfiguring it for the production environment before hand) within a matter of seconds.
Since everything was on the same switch, the connections from our production webserver rolled over to the test box with no problems, and the test box hit the proper database with no problems. Connectivity was affected for only a matter of seconds.
I was able to do the reconfiguration work I needed, and perform the operation in reverse, and then reconfigure the network connection in our test environment back to it’s necessary settings. After a quick reboot….VOILA! The service came up, and I was able to do the upgrades I needed properly.
Originally both boxes had been identical (by performing a P2V operation on the production into the test environment) but now I had what I needed to test our upgrades in the test environment.
Test and Production Environments are so much more easily managed when they’re within virtual environments. Whether it be VMware or Hyper-V (Haven’t had a chance to play with it yet.) the uses are endless!
