GP 10 and eConnect and RMTransaction

I have been working on a project that involves eConnect 10 for Microsoft Dynamics GP 10.  The documentation exists, but is very sparse when it comes to specifics about using the interface.

When using the RM transactions and distributions, the first bite that we had was that there is a property in the taRMTransaction called CREATEDIST and by default it is set to 1 and you need to ensure that it is set to 0 if you are creating your distributions yourself, which in this case I am.

More to follow on the process of this so that there is at least some documentation on the web about how to do it entirely.

Read More

Vista SP1 on x64

I have installed SP1 on my Dell Lattitude D820 with Vista Ultimate x64 on it. It is quite a bit more snappy in the internet browsing, and the OS seems to not be so sluggish.

I don’t have any more than that as I just installed it, but thought I would share that the install took about 45 minutes to apply, and all is well so far. I will keep my blog up to date with any things that I notice are different.

Read More

Vista and IIS and ASP

I was doing a project and it involved ASP connecting to SQL Server. Now this was a script that was being snipped, so there was a potential of having script errors. So I went through the following ordeal and found that there was a configuration that would help me to be effective in troubleshooting my errors.

Here’s the scenario. This ASP page was created and I made sure that I enabled ASP in the IIS 7 configuration (Turn Windows Features On or Off in Control Panel, and IIS, and ASP). Then I saved the ASP page to inetpubwwwroot since I did not change the default directory. I then ran the page, and in IE7 it gives me this “Page Cannot be Displayed” error or something like that, and so I then went into the Tools | Options and Advanced tab, and turned off “Show Friendly Http Error messages”. Now I see “An error occurred on the server when processing the URL. Please contact the system administrator”. I could not tell where this was coming from because I did not have that sentence in my code.

So I went hunting. I came across someone who went into the IIS Manager and you see in the Group By Area group in the bottom of the manager, “ASP”. If you double click on that icon, you get the following window (now notice I circled the options, the Debugging Options), and that phrase is in the line “Script Error Message”. So no matter what the message as long as the option “Send Errors to Browser” as false, you always get the above message. So when you set that option to “True”, then you will get the original messages to show up in your browser.

Vista IIS Manager

To recap on how to get ASP to show you the error messages in the browser in IIS 7:

1) Enable ASP in the Turn Windows Features on or off
2) Open IIS 7 Manager and go to the ASP icon and double-click it.
3) Expand the Debugging properties
4) Make the “Send Errors to Browser” = True so that error messages come to the browser.

Hope that helps anyone running into this problem.

Read More

SQL PASS 2007 has concluded

I went to the SQL PASS 2007 Summit this past week, and I have to say that this was one of the best ones I have been to. I went to a pre-conference session with Kalen Delaney on Managing and the Reuse of Query Plans. Everything that I learned, I could use in my current work. That was very important for me as a new DBA in a project that needed this information.

Thanks to all speakers who took the time to put together presentations for us to learn from.

Read More

Error in SQL (Error: 17806, Error: 18452) SSPI Problem

I recently came across this while managing a clustered SQL Server. There were various messages listed out on forums, etc., but for me we found that the error was originating because we have DNS issues and Domain Controllers (DC) was unreachable due to a network switch configuration problem.

So if you see these errors, you may see in the EventLog a NetLogon error relating to not being able to reach a domain controller to login, and you will get the error below because the DC is the one that hands the Kerberos token to the process for use in SSPI related activities.

Logon,Unknown,Error: 17806 Severity: 20 State: 2.
Logon,Unknown,Login failed for user ”. The user is not associated with a trusted SQL Server connection. [CLIENT: IPAddress] Logon,Unknown,Error: 18452 Severity: 14 State: 1.
Logon,Unknown,SSPI handshake failed with error code 0x80090311 while establishing a connection with integrated security; the connection has been closed.

Good luck and have a great SQL day.

Read More

Working with SSIS in SQL Server 2005

I thought I would document my exploits in SQL Server SSIS 2005 conversion of DTS packages and the scheduling.

I had a DTS package that used the ProtectionLevel “Encrypt Sensitive Data with User Key” that I did not design or create. When I migrated them from SQL 2000 to SQL 2005, I used BI studio to bring them to the file system and then modify them in SSIS and put them back into SQL 2005 into a SSIS Store.

What I did not realize is that when you have the Encrypt with User Key, in 2005 the only option to run an SSIS package is to run it as SQL Server Agent, which in our case is running as Local System. Now the User Key would not be the same as the one that created it, nor would it have Admin rights (per se) to be part of Administrators Group to run this package.

I went about looking for the solution and found a KB article (918760) that indicates that I can put it in a SQL Server storage protection level and then it would use SQL Server roles to get the data out of encryption.

I promptly went into the BI Studio and opened the package and tried to change the protection level to SQL Server Storage and it would not let me save the package because it could not verify that it was secure. But there is no way to put it into SQL Server Storage to use that Protection Level. So I had to change it to Encrypt SensitiveInfo with Password.

Here is the fun part.

I went into BI studio and Added a Package from SSIS store (because the packages were already in there) and then modified the option and then saved it. What I did not know was that it saved it to the file system and did not save it back to the SSIS Store, so the package still failed to run under SQL Agent.

I figured out that I still needed to import it into the SSIS store and I did so, but it still would not run. I could not figure out why, but then after digging and looking at all the options, I saw this dialog and decided to click on the button circled.
SSIS Import Package

I put the password into the package and then saved it out to the File System, then imported it into SSIS Store. I needed to schedule this package to run and so I went into SQL Agent jobs and created a job with a step that indicated to run an SSIS Package. I knew that I had to specify a password so I looked up the dtexec utility page and found the /DECRYPT option. Now here is what threw me. I thought that in the first place that BI Studio saved the package or that the import of the package saved the option of Encrypt with Password option so I went into the CommandLines tab and edited the option and put in /DECRYPT with the password in the Edit Manually option, and then clicked OK. I ran the package and it worked. Well I found out later that it had not, but another weird part is that when I edited the job step the option in CommandLines was set back to Restore original options and I did not see the DECRYPT option in the window below.

So I battled over this for a while, then tried the import again and clicked on the dialog button to change the protection level and chose the Password option, set the password and then it imported. After doing this I changed the job and an interesting thing happened. It prompted me for the password for the package this time. I went into the Commandlines and it did not show any difference, but I went ahead and left it.

I found out that the DECRYPT option was in the CommandLines window, but there was no password specified, which means that it was hiding it from the UI, which is OK I guess, but I had never seen it in there saved before when I manually edited it.

I also found out that when I right clicked on the job and scripted it as CREATE to a new query window, that the option in Command showed the DECRYPT option with the password I specified when I created the Job Step.

So wild stuff and I have survived and the job actually runs now under SQL Agent so I am happy now. Just more wiser too as I spent a great amount of time working this one out.

Sorry if this does not make complete sense. I wanted to get this out now so that I did not forget it.
Have fun and I will have more SSIS stuff to share after the next couple of days.

Read More

Verified SQL 2005 Cluster bug…

In an earlier post, I mentioned a bug that I think that we found in SQL Server 2005 Clustering. I have confirmed the bug. The bug goes again like this:

You have 2 Windows 2003 machines and one or both have been named with lower at least 1 lower case letter in the name. Now you may think, how can this happen, because when I installed the Servers I saw in the name of the machine all upper case letters in the dialog box. What you don’t know is that when you don’t put CAPS LOCK on or don’t hold the shift key down, it is recording the name in mixed case, even though the dialog shows upper case. This will be important later.

This is my findings (with help from another student in the Clustering Class from Ameriteach in Denver

Now you have 2 servers and you are ready to cluster them together. Now you launch Cluster Administrator in Windows 2003 and it allows you to cluster. Now the interesting thing is that when you see the dialog box to add the server to the Cluster, it will show you a name in the box. This name is retrieved from HKLMSystemCurrentControlSetServicesTcpIpParamtersHostName registry key. The interesting thing is that when you add it to the Cluster, it will show the cluster machines in the list in Cluster Admin, and the name is retrieved for the list from HKLMSystemCurrentControlSetComputerNameActiveComputerName so if they are different (which I have not found a reason that they would be, but this is what our testing revealed) then you would see wierd behavior.

When you get the machines in the cluster, then you will attempt to install SQL Server. If you have any lower case letters in the machine name, it will let you run setup, and then it will get to the part where it wants to install SQL on the clustered machine, and it fails to find the node. My hunch is that it just does a binary compare on the machine name and does not find it since “ben1” is not equal to “BEN1” unless it looks case insensitive. You will get an error that the “Parameter is incorrect” and it will fail and rollback the install.

The other thing to note, is that when SQL Setup rolls back the install, it will not remove the Cluster Resource based on the dll and it will not delete the SQL Resources that it created, you will have to do that manually. Not a very good cleanup routine for a setup.

I will be filing a bug in Connect so that the SQL Team can have a look. I will update this post or create a new one when I have created the bug and it has a URL.

Happy landing and see you out there.

Read More

SQL Server and Clustering

I will be setting up an environment to repro an issue that we found in installing a SQL Server cluster. We had 2 Windows Server 2003 R2 Enterprise servers that we were to cluster and then put SQL 2k5 in a cluster as well.

We go to the point of installing the SQL Server in a cluster and just at the point of completing the install and removing the temporary files, a message came up and indicated failure. No real earth shattering message, but just that it failed because a Parameter was incorrect. That is it.

After we spent some time trying to reinstall and force it through and a support call with Microsoft, we and the support engineers observances were put to the test. The one thing that was noticed first by the support engineer, was that the server names in the Cluster Admin were not the same case (one was UPPER123 and one was lower393). We could not imagine that it could be that, but were willing to try anything. So with a little registry editing, we got the names in the Cluster Admin to be UPPER and LOWER. Then we attempted the install of SQL Server.

To our amazement, the install completed. We completed our install and then for good measure we rebooted the machines. Again to our amazement, the cluster would not start.

To finish a long story, we unclustered, ripped SQL out by hand and took the lower machine and removed it from the domain and renamed it with UPPER case and then rejoined the domain and reclustered and reinstalled SQL Server, and all was well.

I am going to repro that today in a test environment so that I can make sure that it repros, and then I am going to file a bug.

Any ideas from others that have done this, please feel free to comment.

Read More
Menu