Jan 19, 2009

iLSP - SharePoint Designer “Execute SQL” Action

With the “Execute SQL” action you can execute SQL statements from within SharePoint Designer workflows.

The action is included in my SharePoint Designer Actions 1.0 release on CodePlex. Source code is available.

Documentation

Configuration in SharePoint Designer

image

  • Provider: The .NET ADO Provider. Default is MS SQL Server (System.Data.SqlClient). You can use other providers like ODBC, Oracle…
  • Connection: The connection string e.g. “Data Source=localhost\SQLExpress;Initial Catalog=test;Integrated Security=SSPI”
  • SQL: The SQL statement you want to execute. You can use Lookups to parameterize the statement. You can also use stored procedures e.g. “EXEC [test_Proc] @var1=N’test’ ”

image 

  • Result: The variable where you want to store the first column of the first row of the result from the SQL statement. Actual the result variable is mandatory. If you don’t need it you have to use dummy variable.

NOTE: The action runs under the user account of the application pool

40 comments:

EnoNomi said...

Awesome, this is just what I needed! Thanks.

Francisco Castillo said...

I Neeed Help :(


Event Type
User ID

Description
Outcome

3/24/2009 3:40 PM Error System Account
Error executing SQL. Login failed for user 'CL\CLVMFC20090319$'. Error

Christian said...

Hi Francisco,
looks like you have trouble with the connection string. Is guess 'CL\CLVMFC20090319$ the application pool user. Either give him rights on the DB or use a SQL-Server login in the connection string like ...user id=sa;pwd=tiger....
Bye, Christian

Francisco Castillo said...

Christian

Thanks i can fix this problem :D

I have a new question for you...
You have more Examples for "Execute SQL Action"?

I need learn more.

Bye bye

Francisco Castillo said...

Hi Christian

hey this statement is not work

INSERT INTO InstanciasWF (Nombre)
VALUES([%Variable: NomInstancia%])

:(

Thanks bye bye

Christian said...

Hi Francisco,

try
INSERT INTO InstanciasWF (Nombre)
VALUES('[%Variable:
NomInstancia%]')

I think your value is type of string/varchar so you have to sourround it with single quotations marks.

Bye, Christian

Tia said...

Hi, i installed "Execute SQL" Action but now when i try to use it within my workflow it gives me the following error

(0, 0) Could not create activity of type 'Microsoft.SharePoint.Workflow.ROOT'. System.NullReferenceException: Object reference not set to an instance of an object.
at iLoveSharePoint.Activities.ExecuteSqlActivity.set_Result(Object value)
at Microsoft.SharePoint.Workflow.ROOT.InitializeComponent()
at Microsoft.SharePoint.Workflow.ROOT..ctor())


is there anything wrong i did .. please help :(

niuniu said...

I installed it sucessfully, I can see the actions in the designer(such as Exec SQL...) in the list, but when I click, nothing happened? Am I missing some configuration. I am strictly follow the Readme file. and change the webconfidg and resetIIS. What have I done wrong?

Any advise is appreciated. This is really something I am long for for long...

Christian said...

Hi Niuniu,
you have to edit the web.config foreach port you use it.
Additionaly the actions are language specific. You will only see the actions in an english or german web. If you want to use another language you have to copy %commonprogramfiles%\Microsoft Shared\web server extensions\12\TEMPLATE\1033\Workflow\iLoveSharePoint_Activities_EN.ACTIONS to the specific language folder (1033=Englisch). Hope this helps.
Bye Christian

Anonymous said...

Error executing SQL. Has expired a waiting time. The waiting time has expired before end of operation or the server does not answer. Error

The inquiry has not time to be executed for 30 seconds. I do not know as do (tried Connect TimeOut - has not helped, as I have understood it is necessary to use CommandTimeOut - but on hand at me only connection string. Please help = (

niuniu said...

Hi, Christian,

Thanks so much for your in time reply. Work like a charm!

Tia,

I think you need to give a return variable to get rid of that error.

Thanks
Niuniu

Dan said...

Have successfully installed and also updated all of the web.config files, but am still getting the following error when attempting to finish the workflow.

(0, 0) Could not create activity of type 'Microsoft.SharePoint.Workflow.ROOT'. System.NullReferenceException: Object reference not set to an instance of an object.
at iLoveSharePoint.Activities.ExecuteSqlActivity.set_Result(Object value)
at Microsoft.SharePoint.Workflow.ROOT.InitializeComponent()
at Microsoft.SharePoint.Workflow.ROOT..ctor())

Christian said...

Hello,
try to assign a result variable.
Bye,
Christian

Dan said...

Christian,
You were right on with this one. Upon further review I noticed your previous post on the topic and it worked perfectly.
Thanks for the help!

Anonymous said...

Hi there
Very nice tool, i would like to ask you if this can be used to connect to a MySQL database, and if so what are the steps.

Thanks
E.

Jeroen Ritmeijer said...

Very nice.

I have taken this concept one step further by allowing c# and vb.net code to be executed directly from a SharePoint Designer Workflow.

This naturally makes database manipulation very simple as well. I have posted an example at http://www.muhimbi.com/blog/2009/12/sharepoint-workflow-power-pack-user_22.html

Christian said...

Hi Jeroen,

Like that dynamic stuff :) I've done something similar with PowerShell, the PowerActivity http://www.codeplex.com/iLoveSharePoint/Release/ProjectReleases.aspx?ReleaseId=14947
You can execute SQL statemens as well. What's about security? Do you have any solution approach?
bye,
christian
http://iLoveSharePoint.com

Jo Finch said...

I am getting an error on the connection. My connection string is as follows: Data Source=\\tol-db2\MSSQL;Initial Catalog=ProposalGoNoGo;user id=john;password=tigers

Christian said...

Hello Jo,
omit the two leading backslashes at the Data Source e.g. Data Source=tol-db2\MSSQL;Initial Catalog=ProposalGoNoGo;user id=john;password=tigers
Bye, christian

Jo Finch said...

Christian, I tried this and I am still getting the same error. I am able to sign onto the database with this user id and password and I know that I can update this database with my infopath form. Any other ideas?

Anonymous said...

Hi Christian,
I'm using the Execute SQL action in a workflow as well. I want to take the avg value of the bids related to a certain project and update the project. For example

Project List:
|Name |City |State |Avg Bid |
---------------------------------
|Proj 1|City 1|State 1|Avg Bid 1|
|Proj 2|City 2|State 2|Avg Bid 2|

Bid List:
|Bid Num|Proj |Amt |
----------------------
|Bid 1 |Proj 1| Amt 1|
|Bid 2 |Proj 1| Amt 2|
|Bid 3 |Proj 1| Amt 3|
|Bid 4 |Proj 1| |
|Bid 5 |Proj 2| Amt 1|

For Proj 1, I'd like to avg the three bids (omit null) from the bid list and update the avg bid on the proj list. I have tried a few different sql statements, but am unsuccessful. Currently the flow updates with the last bid amount not the average. Any help is appreciated. Thank you. Brian

Christian said...

Hello Brian,
my first idea:
-First "Execute SQL Action":
SELECT AVG(Bid.Amt) from Project join Bid on (Project.Name = Bid.Proj) WHERE Project.Name='xyz'

assign the result to a workflow variable =>avg

-Second "Execute SQL Action":
UPDATE Project SET [Avg Bid] = [insert avg variable via Picker] WHERE Project.Name='xyz'

Bye, Christian

morib said...

Wow...! Great and thanks a lot!

Jay said...

Christian, this is a great custom action - i've use it several different ways. One issue though: I'm using it to get the first 25 characters of a long string field and I'm getting an error if the field contains a single quote (classic SQL single quote problem). I tried using REPLACE('[%Column%]', '''', '') but still get the same error "Unclosed quotation mark after the character string". I'm suspicious that this might be caused by the required single quotes around the SharePoint column name?

Bonde said...

Hi Christain
I am using you SQL Action.
Great tool

I want to retrive data from on field in the current LIST using a workflow.

I have set my connection to:
Data Source=SQL-D027\WEB;Initial Catalog=DB_Content;user id=aaa;password=1234

The DB_Content is the content database on for my sharepoint site
The connection string is correct

I am trying to execute this

SELECT [Title] from Projects where ID=[%Projects:ID%]

I did this ones before but i have forgotten the syntax.
My error is that
Invalid Obejct Name 'Projects', igues that the reason is that i am looking inti the wrong DB for this table or the syntax is wrong

Could you please help me on some Syntax Examples when retriving data from the actual sharepoint list, i want to retrive on the first 10 chars of the title using SQL syntax

Thx
Thomas Bonde Ejby

Christian said...

Hello Thomas,
I'm not sure if I understand the scenario. Do you want to acccess a SharePoint list with SQL? If so, all the SharePoint data is stored in the content db in one table called AllUserData http://msdn.microsoft.com/en-us/library/dd358229(PROT.13).aspx
Bye,
Christian

Bonde said...

Hi Christian

The reason i am trying this is because i want to copy data from a Extended Text Column to another Extende Text Column in another library using Workflow.

When i do this i loose the Formatting of the text in the field.
I wanted to try to get the data from the list / field using the SQL in the workflow, i will asume that this select statement will get me the complete HTML of the extended Text column.
When i have saved this text string in a workflow variable i want to create a new list item in another list with antoher extended text using this value.

I believe i would not loose the HTML formatting of the original text string

thx
Thomas

Darren said...

Hi,

I tried installing the "Execute SQL" action. I see the action in my menu, but when I select it nothing happens. I was expecting to see an action that would allow me to input a data connection string (e.g. the screenshot in this blog). Thanks for the help.

Darren

Anonymous said...

hi
this is great!
I am just having one issue: I'm trying to select a maxID from one of my sql tables save it to a variable then add 1 and insert it to another table.
when I try assigning the maxId I selected (int) to a number variable I get this error:
Error executing SQL. Object of type 'System.String' cannot be converted to type 'System.Double'

the only way it works is if I assign it to a string variable but then I can't increment it.
any ideas?
thank you so much!

Christian said...

hi, try to increment the value on sql side (maxid + 1)
bye, christian

Trivena Jeanete Wenur said...

Need help christian...

how to exec stored procedure?

in my case :

"Data Source=svrdev2\svrdev2;Initial Catalog=Permit;Integrated Security=SSPI"


"EXEC [test_Proc] @permitid=[%Current Item:PermitID%], @permittype=N'[%Current Item:PermitTypeCode%]"


pleaseee helpp give me the right syntax for this :(

Jie said...

Will this work on Sharepoint Designer 2013 and Sharepoint Foundation 2013?

digital signature software said...

Congratulations, your blog is appealing and informative. Going through your Information, I found quite a few new ideas to implement ... Thanks

Fake Chanel Bags said...

The Chanel Boy has been in the collection for some time (and its popularity has made it a staple in each bag collection), but this time, the rectangular lock logo is tone- on- tone.

Hug Day said...

happy hug day Images
hug day 2016 Wishes cards greetings
Hug day Gift Ideas For Him/Her
hug day sms for bf

风骚达哥 said...

20160423 junda
air jordans
cheap oakley sunglasses
kate spade outlet
adidas stan smith
ray ban sunglasses
michael kors outlet
nike air max shoes
cheap omega watches
yeezy boost 350
michael kors outlet online
michael kors outlet online
nike free runs
air jordan uk
michael kors outlet online
pandora charms
ray ban outlet
calvin klein outlet
fitflops sale clearance
toms outlet
nike huarache white
sac longchamp
toms outlet
michael kors outlet clearance
bottega veneta outlet
armani watches
gucci handbags
nike blazer
oakley sunglasses
true religion jeans
cheap jordans
ray bans
hollister
bottega veneta handbags
jimmy choo outlet
oakley sunglasses
converse
prada outlet
reebok shoes
true religion
burberry outlet

Yuanyuan Lin said...

7.14lllllyuan"tiffany and co"
"michael kors outlet"
"hollister uk"
"michael kors outlet"
"salomon shoes sale"
"ralph lauren outlet"
"bottega veneta outlet online"
"fred perry polo"
"true religion jeans"
"michael kors outlet"
"camisetas futbol baratas"
"cartier outlet store"
"hollister"
"christian louboutin shoes"
"nike soccer shoes"
"polo ralph lauren"
"polo ralph lauren"
"michael kors outlet"
"longchamp handbags"
"fitflops sale"
"michael kors outlet"
"coach outlet online"
"louis vuitton handbags outlet"
"adidas shoes"
"tory burch shoes"
"chrome hearts eyewear"
"tory burch outlet"
"michael kors outlet"
"replica watches"
"burberry sunglasses on sale"
"louis vuitton bags"
"coach outlet"
"jordan shoes 2015"
"michael kors outlet"
"abercrombie outlet"
7.14

Fangyaya said...

coach factory outlet
kate spade handbags
jordan 11
abercrombie outlet
cheap toms
ray ban sunglasses uk
adidas uk
michael kors canada outlet
lebron 13 shoes
burberry outlet
insanity workout
the north face jackets
kobe 10
louis vuitton purses
adidas factory outlet
timberlands
fitflop sale
lebron 12
fitflop shoes
concords 11
ladies cartier watches
ralph lauren polo shirts
coach outlet store online
toms shoes outlet
adidas superstar shoes
lebron 13
cheap jerseys
oakley sunglasses
instyler
adidas yeezy
louis vuitton outlet online
gucci outlet
tods outlet store
coach factory outlet online
juicy couture
pandora charms
coach outlet
cheap beats headphones
coach outlet
michael kors outlet online
20168.8chenjinyan

dada24 Xu said...

nmd adidas
oakley vault
cheap uggs
adidas nmd
moncler jackets
longchamp
michael kors outlet clearance
polo ralph lauren outlet online
michael kors watches
nike flyknit
2016921caiyan

Home Improvement Westchester County said...

Ajrin Construction Inc. is a privately owned firm and has been providing home improvement and remolding services to clients all across the boroughs of Westchester County NY. Home Improvement Westchester County