Fun with sysobjects

Here is a handy script that will give you all of the tabls and views that a particular field resides in:

SELECT

   CASE sysobjects.xtype
      WHEN 'V' THEN 'VIEW'
      WHEN 'U' THEN 'USER TABLE'
      WHEN 'S' THEN 'SYSTEM TABLE'
      ELSE sysobjects.xtype END AS OBJ_TYPE,
   sysobjects.name OBJ_NAME
FROM syscolumns
   LEFT JOIN sysobjects ON sysobjects.id = syscolumns.id
WHERE syscolumns.name = 'yourcolumnname'
ORDER BY 1,2

Happy scripting!


Did this help you?  If so, please leave a comment!

Installing Visual Studio 2010 on an x64 system

When trying to install Visual Studio 2010 RC1 on my Windows 7 x64 system, I kept getting an error that the license key was not valid.

Now, even more confusing than the fact that this install did not require a license key was the glaring problem that it didn't actually ASK for a license key.

After much digging for an answer to this issue, I wrote it off to bad error handling and made the assumption that this version of studio was not compantible with the x64 system.

After a little more downloading and testing, I found that Visual Studio 2010 Premium installed just fine on x64.

I hope this helps others looking to upgrade.


Did this help you?  If so, please leave a comment!

Adventures in Replicating a Dynamics GP 9 install on SQL2008

I had a client yesterday that took a backup of their GP9 system and was trying to create a test system.

They installed SQL 2008 and restored DYNAMICS and a company database to the new server.  For simplicity, I will refer to the old SQL instance as SQL1 and the new sql instance as SQL2.

They already had the GP9 client installed on their local machine (pointing to SQL1), but there was no GP9 client on the server, since it was Win Server 2008 64bit and the GP9 client was not compatible.

They followed the steps in KB:878449

They ran the Capture_logins.sql script on SQL1 and then ran the generated script on SQL2. 
They ran sp_changdbowner 'DYNSA'
They ran dex_req.sql and Grant.sql

When they tried to set up an ODBC connection to SQL2 on their client, they could not connect as sa.

We did the following to correct this:
1. Reset the sa password on SQL2
2. Started the SQL Browser service on SQL2
3. Started the DTS service on SQL2
4. Set the firewall on SQL2 to allow DTS
5. Rebooted SQL2

Now we could connect with the ODBC connection, but we got errors when logging into GP as sa.

We re-ran the scripts on SQL2 and I noticed that Grant.sql was throwing an error that it could not find a view.  The view was a custom object in the database with a name exceeding 64 chars.  Once we shortened the name, Grant.sql ran successfully.

Now we could log into GP as sa.

The point of all this was to migrate the users from SQL1 to SQL2 along with their encrypted passwords.  But we could not log in as any user other than sa.

The answer to this is found in a Note on Step 11 of KB:878449
"If the old server was running Microsoft Dynamics GP and does not have the same name as the new server, the passwords for the users will no longer be valid."

We that sucks with a capital "S".  The whole point here was to be able to migrate the users with their encrypted passwords.  So why?  It seems the answer is that the encryption algorithm includes the server name.  Since there is no way GP is handing out the encryption algorithm to the likes of us,  once again, security trumps common sense, but I digress.

So we are relegated to logging into GP as sa, opening security, pulling up the users one at a time, and changing their password to something simple.  We then check the box to force the user to change the password on next login and save. 

Now when we try to log in as the user, GP prompts us to change the password.  But when we try to we get the following error:
"The password change failed for an unknown reason. Enter a different password or contact your system administrator."

Google pops up lots of hits that address the Group Policies, like this one.
Basically, it looks like most of the time this error pops up because the Minimum password age is set to some amount other than zero.  Most admins have enough headaches getting their users to change their passwords before they expires.  Why in the world would anyone want to restrict them from changing it too often?  But I digress again.

In our case, this is not the issue.  Once again, we will be saved by a post from Dave Musgrave.  The issue is the ODBC connection itself.

When they set up the ODBC connection on then client, they chose SQL Server for the driver.  However, the user password change function will only work using the SQL Native Client driver.
(When in doubt, always try the newest driver first)

So we changed the ODBC to use SNAC and ....Presto!  The user password change now works.

Hope this saves someone else some reading.


Did this help you?  If so, please leave a comment!

Insufficient data error in SSIS package

When multiple developers are working on a group of SSIS packages and one of them tries to run a package created by another user, they could receive an error saying there is insufficient data to connect to the data source.


After a little googling and a lot of trial and errors, I think I have narrowed down the issue and how to fix it.

As far as I can tell, the error has nothing to do with the driver or ODBC, it has to do with how SSIS packages handles sensitive data such as passwords.

In the package properties, there is a parameter called ProtectionLevel. By default this is set to EncryptSensitiveWithUserKey. That means that the login info on the data sources will be encrypted in the package file with a key generated from the user who was logged in when they were created. So later, when I log in and try to run it, the decryption returns the wrong value based on my user key and the connection bombs.

You can change this value to any of the other choices, however, you can't use DontSaveSensitive or ServerStorage if you are working with local package files that are not stored in SQL. Leaving the value alone will also work, you just need to add data sources to the project (especially ODBC) and make sure that all of the tasks and data flows reference those data sources.

Now the bad news. Since you are working with local files, Visual Studio does not expect a different user to access them while local. So it over-protects them to the point that they are almost un-usable.

In order for a different user to run the package, they need to

1. Open each data source
1. Edit the connection string
2. Enter the user and password data (it will be missing)
3. Test the connection
2. Open each task that accesses a connection
1. Choose the connection property
2. Choose new connection and map to the data source defined in the project
3. Parse the query to make sure the connection is good
4. Click OK
3. Open each data flow object
1. Click New and map to the existing data source
2. You will probably need to entirely rebuild each data flow object, so pay attention to the other properties before you change the data source.

The package should now run.

There is one saving grace. Once both users have followed this process, their individual data source will show up in the drop downs above (rather than having to use new).

Update:
However, I just couldn;t let this one go.  On a hunch I started exploring configuration files in SSIS.  Success!

This is by far the best solution to this problem.  It streamlines moving packages from one server to another, and almost totally eliminates the multi-developer issue above.

Basically, if you enable configuration files on a package, the package will override any properties listed in the config file with the values from the config file. 

This link gives a great alkthrough on how to use configuration packages with SSIS:
http://decipherinfosys.wordpress.com/2008/08/15/ssis-creating-package-configurations/

Follow those instructions, then just choose the connection object property for each object that has one, and choose the username and password for the connection manager connections.

Then add the config file to the project and open it.  Edit the password entries and type in the password and save.  Now when a another developers opens the package, they may get a couple of load warnings and errors related to the encryption.  Ignore them and load anyway.  Once you run the package, the errors will go away, since the object properties will get overwritten by the config properties.



I hope this keeps someone else from chasing their tail.




Did this help you?  If so, please leave a comment!

WennSoft Products - Product Indicator Drop Down is empty

If the Product Indicator field is empty, try this:
Open Security for Alternate windows, select all products and windows
Select Purchasing
Check each purchasing window and make sure that the WennSoft Products version is selected.
Apparently, the product indicator field will only be populated if ALL the purchasing alternates for WennSoft are enabled.

I ran into this issue after installing another 3rd party, which installed some of the same alternates as WennSoft and automatically set security to the 3rd party window.


Did this help you?  If so, please leave a comment!

Visual Studio Tools for Dynamics Templates not showing up in IDE

Again, we are back with a work-around to a Windows 7 / Visual Studio issue.

In the original version of Visual Studio Tools for Dynamics GP 10, the installer only includes Visual Studio 2005 templates. It flatly refuses to install these templates on Visual Studio 2008, so you have to uncheck it to install.

Now what is the point of installing the Visual Studio Tools if you must choose NOT to install the main part of the program (and only interface) to continue??????

In order to get them to show up, I went into the control panel, selected "Visual Studio Tools for Dynamics GP 10.0 SDK" and clicked Change. I then chose Add/Remove Components and checked the Visual Studio 2008 Templates. After the install completed, I had the appropriate objects in Visual Studio.

After some poking around, I discovered that SP2 and later of the toolkit includes the templates for Visual Studio 2008. When you run the SP installs, there is a minimal install interface (Just continue and done). So you would expect the templates to be there after the patch, right? Wrong! Since you had to uncheck the templates during the initial install, they remain unchecked after the patch.

To recap, the steps are:
1. Install the Visual Studio Toolkit with the 2005 Templates unchecked
2. Install Visual Studio Toolkit SP2 or greater.
3. Use Add/Remove programs to add the 2008 templates.


There may be a better way to do this, and I can't find any documentation on what Microsoft actually intends for you to do about it, but this seems to work fine.


Happy Friday!


Did this help you?  If so, please leave a comment!

Getting SharePoint to use multi-line text fields for calculated fields

Requirement: Trim, truncate, or otherwise modify the text from one SharePoint list field to make it appear in another.
Solution: Make the new field a calculated field and enter the formula, referencing the original field in [brackets].
Obstacle: Text functions in SharePoint list columns do not work properly on multiple line text columns.

The work-around here is to trick the new SharePoint list field into thinking it is operating on a single-line text field. While it is true that the text functions don’t work perfectly for multiple line text fields, they are adequate for simple tasks such as truncation. The issue is that SharePoint validates the calculated field when saving and throws an error if the function refers to a multiple line text field, preventing the save operation.

So how do you trick it?
1. Create Field1, single line text
2. Create Field2, calculated field, formula=RIGHT([Field1],LEN([Field1])-8)
    (this gets rid of the html tag that will appear at the beginning as an artifact of operating on a multiple line text field)
3. Create Field3, calculated field, formula=LEFT([Field2],50) & “…”
    (this will display the first 50 characters of the original field follows by the ellipse)
4. To prevent validation, do this from the site settings window
     a. Delete Field1
     b. Create Field1, multi-line text
5. Create a custom view and hide Field1 and Field2 and set it as the default view
     (they will still appear in the item view, but not in the edit window since they are calculated fields)

There you have it. Steps for tricking SharePoint into doing something it was designed to do, but prevented from by poor validation logic.  Now how to we trick those MS Devs into removing that erroneous validation logic?

Enjoy.


Did this help you?  If so, please leave a comment!