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
- 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’ ”
- 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
Posts
15 comments:
Awesome, this is just what I needed! Thanks.
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
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
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
Hi Christian
hey this statement is not work
INSERT INTO InstanciasWF (Nombre)
VALUES([%Variable: NomInstancia%])
:(
Thanks bye bye
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
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 :(
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...
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
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 = (
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
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())
Hello,
try to assign a result variable.
Bye,
Christian
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!
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.
Kommentar veröffentlichen