Posts Tagged ‘SQL’

Smarter EPDM Drop Lists Using SQL

Tuesday, May 7th, 2013

Out of the box SolidWorks Enterprise PDM gives you the ability to populate your drop lists based on SQL query results. I use this functionality all the time to give my users a list of qualified suppliers from a purchasing database, lists of users in a specified group, choices of product type, etc.

The limitation is that these queries are all based off of constant values. These queries are run by the EPDM server on a predefined interval, and the results are stored in the EPDM database. This method is fast, but it eliminates the ability to populate the lists based on properties of the file itself.

Granted with EPDM’s “Controlled by variable” option,

ControlVariable

you can change the query based on one other variable, but if you want to use more than one variable or the file’s physical properties, PigeonHole makes the process very easy.

This video gives a quick demonstration showing how.

YouTube Preview Image

Jeff Sweeney

CSWE Engineering Data Specialist 3DVision Technologies

Group Members for your Datacard

Monday, June 11th, 2012

You’ve designed the perfect folder datacard for your SolidWorks Enterprise PDM setup. It is a great little folder card listing project due date, description and even have a nice little droplist for your users to pick the proper project manager.

But wait! You’ve noticed there is no way to get a list of just your project managers! What’s a girl to do?!

List Properties

The solution is easy with Engineering Data Specialist Man! Create a new list, make it a SQL query and enter in this SQL statement:

 

SELECT Users.Username
FROM GroupMembers INNER JOIN
 Groups ON GroupMembers.GroupID = Groups.GroupID INNER JOIN
 Users ON GroupMembers.UserID = Users.UserID
WHERE (Groups.Groupname = 'Engineers') AND (Users.Enabled = 1)

Now, assuming you have assigned your project managers on the root level, you have a list for your card that will update as your project managers come and go.

Jeff Sweeney

CSWE Engineering Data Specialist 3DVision Technologies

SQL – Convert to Hex

Thursday, January 19th, 2012

Often times when getting really down and dirty with SolidWorks Enterprise PDM, I need to know a document’s ID value in hex format. Back in the old days [yesterday] I would do a SQL query on the document’s table and then manually calculate the hexadecimal value.

However I found a SQL function that will do the conversion for me:
CONVERT(varbinary,<value>)

Cool eh? Thus a SQL statement like this:

SELECT DocumentID, CONVERT(varbinary,DocumentID) as IDHex, Filename FROM Documents where Filename like 'Big Tuna%'

Returns:

hex

Yeah, SQL rocks.

Jeff Sweeney

CSWE Engineering Data Specialist 3DVision Technologies

Should you upgrade to SQL 2008 R2?

Tuesday, April 26th, 2011

You may have noticed in your SolidWorks Enterprise PDM 2011 DVDs, Microsoft SQL 2008 Standard Edition R2 was in the box. If you originally installed EPDM 2010 or earlier, you likely have SQL 2008 Standard Edition installed. Should you use your new DVD to upgrade to 2008 R2?

SolidWorks says you do not have to. EPDM will run fine on SQL 2008, it even runs fine on SQL 2005.

The following capabilities are new to R2 Standard:

  • Backup Compression to reduce data backups by up to 60% and help reduce time spent on backups
  • Can be managed instance for Application and Multi-Server Management capabilities
  • High-scale complex event processing with SQL Server StreamInsight™

In your EPDM environment, the first bullet above is all you care about…a 60% smaller backup file is pretty impressive.

I had to upgrade because a customer sent me a backup of their database and as you can imagine my old SQL could not read this new backup format. The upgrade took about 20 minutes on my M6500 and was very easy. Drop the DVD in the drive and follow the prompts.
Upgrade
Though the smaller backups are nice, I wouldn’t recommend upgrading yet unless your backups are taking too long or you are running into storage issues.

Remember to backup before you upgrade.

Jeff Sweeney

CSWE Engineering Data Specialist 3DVision Technologies

Babysitting SQL

Friday, June 25th, 2010

In the world of SolidWorks Enterprise PDM administration, maintaining the Enterprise systems is really pretty easy. You need to add/remove users (assuming the HR department bothers to tell you when they hire someone) and ensure you have good reliable backups.

But don’t forget Enterprise’s playmate – SQL.

SQL is that one kid who was hard to babysit for. Sure he was good when you were watching him, but if you left him alone for too long you would catch him chewing on the furniture, burning the hair on your sister’s dolls, or using a shovel in your mom’s flower bed.

He’s a good kid, he’ll serve you well, but every once in a while you need to pat him on the head so he knows you are watching him.

Like any Microsoft product, SQL requires some occasional maintenance. Tim Kwong, SolidWorks Sr. Technical Support Engineer, recommends this schedule:

Daily:

  • Preform backups of the SQL database (and log file if using Full Recovery Mode)
  • If using the SQL backup feature, offload the backup from the SQL server

Weekly or Monthly:

  • Rebuild the SQL indexes using SQL Maintenance Plan Wizard
  • Review the MDF and LDF files to make sure they haven’t exceeded the initial sizes
  • Defrag the SQL drives

Quarterly:

  • Check the integrity of the SQL backup by restoring and testing a recent database backup on a test setup

Jeff Sweeney

CSWE Engineering Data Specialist 3DVision Technologies

There’s an object for that

Thursday, March 4th, 2010

When writing custom code for your SolidWorks Enterprise PDM system, have you been tempted to connect directly to the Enterprise database tables? You certainly could. The database isn’t encrypted and when you first start out it is easy to get the information you need with a simple SQL query.

Don’t. There’s an object for that. Everything you need from the database: BOMs, history, users, variables, searches,  … has an associated object in the API.

It may take a little while to learn these new objects, but your code will be more robust and since you never know when SolidWorks may change the database schema, your code will be much more “upward” compatible in the future.

Heart

Check out the EdmUtility constants list in the API help file to give you a taste of some of the objects available to satisfy your little SQL writin’ heart.

Jeff Sweeney

CSWE Engineering Data Specialist 3DVision Technologies

HOWTO: Kill SQL

Friday, December 4th, 2009

Looking for a quick way to kill your SQL engine and bring your SolidWorks Enterprise PDM system to a screeching halt?

I just got a support call from an Enterprise customer who was complaining they could not connect to the database. Tried restarting SQL, the service stopped – good…but then I could not start back up again – bad. Rebooted the server, the service still laid there dead. -worser than bad!

killSQL

Next, tried to do a SQL repair install – the repair install failed, telling me “the database engine was bad”. Well no kidding! Had to do a clean uninstall/reinstall of SQL before we could get the service started again.

What happened? Symantec AntiVirus. Symantec started scanning the databases and less than a minute later there was nothing left but the crying. Luckily it didn’t seem to hurt the actual databases – they mounted right back up after the reinstall.

Jeff Sweeney

CSWE Engineering Data Specialist 3DVision Technologies

SQL 2008 PreRelease

Thursday, December 3rd, 2009

Had a little scare yesterday and since misery loves company I thought I would share it with you.

Yesterday I received an email with this image:

SQLVersion

These are the file properties of the Microsoft SQL 2008 install that comes with SolidWorks Enterprise PDM. Look closely, it says that it is a prerelease! Yikes! Using a prerelease for production data?

I’ve done some checking and this SQL build (also known as 10.0.1600.22) is a a RTM build. [Reference] I have no idea why it says prerelease in the version information.  If you are concerned, there is a service pack can be downloaded from the Microsoft web site.

Heart attack averted. As you were.

Jeff Sweeney

CSWE Engineering Data Specialist 3DVision Technologies

Are you sure you are backed up?

Wednesday, September 30th, 2009

I just got off of the phone with a SW Enteprise PDM administrator. I was getting ready to change some database settings and decided to make a backup of their database first. (Better safe than sorry, especially with someone else’s data!) I while making the backup I noticed there were no other backups in existence. Turns out for the past two years this company has not made a single database backup!! The IT guys assumed the Engineering department was making them, and vice versa.

This is the second company in two weeks I have found in this situation.

Backing up your data files on the archive server is NOT the same thing as backing up your database. Please put your mouse down, call your IT guys right now and confirm your company is backing up your Enterprise database.

Stop dancing on the mine field.

Jeff Sweeney

CSWE Engineering Data Specialist 3DVision Technologies

Follow
Get every new post delivered to your inbox
Join other followers
Powered By WPFruits.com
Bear