Using SQL inside Workflow

Thanks to iLove SharePoint for creating the “Execute SQL” action.

With it I was able to create a workflow against the add/change event in my document library to create a link to my equipment page based on if there is any information to link to.

I first store my select statement in a variable so I can log it to the history file (this was helpful with debugging the workflow.)

SELECT COUNT(DWG_NAME_) FROM AccessDatabase…[AccessTable] where DWG_NAME_ = ‘[%DocumentLibrary:FileName%].DWG’

Then using the Execute SQL action, I can query the Access Database to see if there is anything I want to link to and store the results in a variable. I can then also show how many items there are in the hyperlink text. (The connection string I used was: Data Source= OFFICESERVER;Initial Catalog=WSS_Content;Integrated Security=SSPI. This particular server uses Studio Express on the same server as SharePoint.)

So if there is something to link to, I create the URL text: Store Helpful text-[%Variable: SQLResult%] in Variable: URLText.

Then I build the URL string:

And store it in the hyperlink column of my document library. The user will know that only the drawings with links have something in the Access Database to look at:

Copy List Items between Sites

Steps will need to be run by SharePoint Administrator:

1. Download Useful Sharepoint Designer Custom Workflow Activities
2. Extract all files in the archive to a singe location.
3. Run setup.exe
4. Go to Central Administration -> Application Management -> Manage Web Application Features and activate the feature for desired web applications (usually it’s Sharepoint – 80 or Sharepoint – 443).

To create workflow:

Open up Sharepoint Designer, connect to your site and create a new workflow: 

Action: Copy List Item Extended 

Choose List Item “Current Item”

To list at this URL

Leave option to “Overwrite existing items”

Use variable (rename it to be clearer) to store ID.

I don’t know where that can be used yet since there isn’t an Update List Item Extended. Maybe that’s for a future enhancement or for those that can extend the project code.

Click Finish.

Open Calendar Item and select Workflows:

Start the workflow:

Go back into the workflows on the calendar entry to see the workflow history:

Once you’ve verified that the workflow works manually, you can change it to run automatically on item add or change.