Hi, I'm seeing weird behaviour when querying an SQL (MS SQL2005) source using an ExecuteSQL function.
First, I create a record using the ExecuteSQL script step. All goes well, I can see the entry that's created in the destination table.
Then, I try and query the SQL source for a UUID associated with the entry I just created:
sku = SQLQuote ( Items::SKU ) ;
"SELECT " & SQLfield ( Items | dbo.StockItem_ALL_TEST::pkStockItemID )
" FROM " & SQLTable ( Items | dbo.StockItem_ALL_TEST::pkStockItemID )
" WHERE " & SQLfield ( Items | dbo.StockItem_ALL_TEST::ItemNumber ) & " = " & sku
" AND " & SQLfield ( Items | dbo.StockItem_ALL_TEST::bLogicalDelete ) & " = 0"
ExecuteSQL ( query ; "" ; "" )
And I don't get any results, which I know is odd, because there IS a record which matches the above. So then I try:
Commit Records (Skip/No Dialog)
Refresh Window (Flush/Flush)
Flush Cache To Disk
Still no joy.
Then, I go into Manage Database, double check things, and when In exit, hey presto, the calculation returns the result I was expecting. This happens everytime, It's as though something is being refreshed, just by opening and closing the Manage Database window, which isn't refreshed by any of the script steps above. Am I missing something here? Is there some sort of refresh external data sources script step I'm missing?...