Workflow Soluiton

OK, i've got the integration library down for read\write\update of a record for 1 table. But what if I need to join two tables. Example: I need to associate the serial numberof a PC from vASSET with the same PC's computer name from vCOMPUTER  Any ideas?-Thanks

I'd use the SQL script generator
i.e.

SQL will look like

UPDATE blahAsset SET SerialNumber = @serno WHERE id = @assetid
UPDATE blahComputer SET SerialNumber = @serno WHERE id  =  @compid

So the generated comp will have 3 parms, the serial number, and 2 ids where you can isolate the rows to update in the 2 tables.

Not sure if this helps, but in simple terms you can get the serial number from Vasset and computer name from Vcomputer as follows:

select
va.[Serial Number],
vc.[Name]

from vasset va

join vcomputer vc on va.[_resourceguid] = vc.[guid]


You could add a where clause at the end if you know the specific computer name:

where vc.[Name] = 'computername'

Thanks AltAdmin, but you can only pull from one table/view per SQL table generator.

You can use any of the suggested SQL queries by using them in the SQL script generator.  It will convert those @names into input parameters on the generated component.

If you will be performing the query many times,  consider creating a stored procedure and then using the stored procedure generator to execute it.  Stored procedures are much more efficient for the SQL server to execute repetively and also provide some basic security protection from injection attacks. 

I think I can already see that the script generator is going to be the way to go. Thanks guys. I'll let you know how it turns out.

Example:

UPDATE vAsset
SET [Status]=Pulled
WHERE [serial number]='SERIAL_NUMBER'

In this example, 'SERIAL_NUMBER' (the name of a variable in my project) is a serial number that was returned from a SELECT statement earlier in the project.

Absolutely... just use the @blah, and the script generator will identify 'blah' as a variable. Then you specify what type to except, etc.

I vastly prefer the script generator out of all the SQL generators because it gives you much tighter control of the SQL being executed.

consider this as well, i'm using my script generators as thus:

select A1.*, B1.*
from @tableA1 as A1
join @tableB1 as B1
on A1.@columnA = B1.@columnB
where @conditionA = @criteria

This way you don't have to create separate integration components for different result sets, just manually set the criteria per all-purpose component.  Of course if you're using the same component over and over, it doesn't help much.  Try it out!

That works as far as using the variable in my script generator. Now when my project hits that component i get the following error. From what I've seen in researching it is that there is an issue with the view i'm trying to update.  Any recommendations?
 

Error Details

Error Message: Update or insert of view or function 'vAsset' failed because it contains a derived or constant field.

Message Stack: at ChangeStatusToPulled.SqlQuery.ChangeStatusToPulled.Run(IData data) at LogicBase.Core.ExecutionEngine.SinglePathProcessComponentExecutionDelegate.Execute(IData data, IOrchestrationComponent comp, String& outputPath, IExecutionEngine engine, TLExecutionContext context) at LogicBase.Core.ExecutionEngine.AbstractExecutionEngine.DoRunComp(IOrchestrationComponent comp, IData data, TLExecutionContext context)

Ok, you can't insert/update a view in SQL Server unless they're updatable (no computerd fields, mostly)... what I'd do is script out the view definition in SQL Server. Then update the appropriate tables that define the view.