Having trouble with Execute SQL command

Dec 21, 2010 at 6:09 PM

 

Hi,

 

I'm new with the custom action in SPD. I installed your package on my server and It's work well :) Thanks you

But I need to get help with the "Execute SQL" action. I got a list with those column :

 

Number / Year / Name

  • 1 / 2008 / Andre
  • 2 / 2008 / Jean
  • 1 / 2009 / Michel
  • 2 / 2009 / Andrew
  • 3 / 2009 / Jack
  • 4 / 2009 / Pierre
  • 1 / 2010 / Lewis
  • 3 / 2008 / Steve

I would get the next number for the year that has been inserted. So when I create a new element, the user enters the date (year) and the name of a person, we must assign a new number to each line at its inception. So my problem is, what is the provider and the datasource that I have to put in the action to obtain the information of this list.

And when I do my select, Do I write Select [Column name] from [ListID] where [My condition] ?

 

Thanks you


Alexandre(Sorry, if my english isn't so good)

Coordinator
Dec 22, 2010 at 10:16 AM

Hello,

do you mean that you would like to access the SharePoint List over SQL? This is not recommended.

However, SharePoint stores all the list data in a single table http://msdn.microsoft.com/en-us/library/dd585240(v=office.11).aspx

The title of the columns are stored in another table.

The execute sql action can only return an single value.

Bye,

Christian

 

Dec 22, 2010 at 1:39 PM

Hello christian,

Yes , I would like to access to the Sharepoint List. But why isn't recommenced ?

I just need a single value, So this option does not bother me. Else If I put an excel table or an Access database on my server, I can access to this db with a path and his provider ? And can we do Insert , delete or Update on those table ?

Bye

Alexandre

Coordinator
Dec 23, 2010 at 8:38 AM

Hi Alexandre,

writing to SP Database is prohibited, reading is not recommended ;-) At the TechEd I've worked togheter with the Microsoft Support at the TLC booth. They told me that readind the DB is also not recommended, because reading could also cause an unwanted lock in the SQL DB. 

On the other hand, in the official course SharePoint 2007 course is one example accessing the SharePoint DB with reporting services.

The perfect way in SharePoint 2007 would be a custom SharePoint Designer Action using the Object Model.

The agile way would be scripting the Object Model with my PowerShell Action for SharePoint Designer.

In general the Execute SQL should support all ADO Data Provider, but I never tried this scenarios...but sounds interesting.

In SP2010 this could be a scenario for BCS.

But, actually I'm not sure if I understand your scenario.

Bye, Christian

 

 

 

Dec 23, 2010 at 2:05 PM

Hi Christian,

Thanks again for this fast anwser.

Yes, I know my scenario is a bit Difficult. But each time I had a new item, I would like to give a value to a column. This column is like an Id but each row of each year start at 1, so if I have year 2009 : with 1-2-3 and year 2010 with : 1-2-3-4 . If I had a new item with the year 2009, the next Id will be 8 but I want to put number 4 in a column (Name : Numéro AR). I have search if this column can be filled by a calculated fields. But I founded nothing.

But I'll try to look how work your PowerShell action and to understand it. I didn't work with this action at the moment. I'll learn more because I really dont know what you think to do with this and the Object Model.

You're really good man ! I like to install your custom action. VB isn't certified on my job so we cannot use it and When i found your action, it was so nice ! :)

Bye, Alexandre

Coordinator
Dec 28, 2010 at 8:04 AM

Hello,

sounds like you need kind of a trigger (Workflow could also be used). In SharePoint triggers are called event receivers. You can develop them with Visual Studio or use my PowerShell Event Receiver. The Object Model is the SharePoint API - easy to use ;)

Bye, Christian