Hi!

Understand now.

Id id not tried this solution before, but probably @@IDENTITY value could be stored from inside of trigger somewhere else and get from there later.

Or just have a separate table with integers to generate PK for this table...



>Yes - TableUpdate use a stored procedure on SQL - "sp_executesql" - which is fine except that, as a stored procedure, it runs in it's own scope and precludes SCOPE_IDENTITY from being able to get the identity (primary key) that was inserted. I'm using SCOPE_IDENTITY instead of @@IDENTITY because I have cascading insert triggers, and @@IDENTITY returns the last inserted @@identity value instead of the intital one.
>
>>Hi!
>>
>>I do nto know why VFP converts SQLthat way. Try to play with SQLSetProp() properties.
>>
>>Also, as about way of inserting, I do not see difference. When you insert record using tableupdate(), it is inserted the same way as with SPT INSERT command, include behavior related to identity fields. I just do not understand your reasons to use SPT INSERT instead of tableupdate(), sorry. Can you explain in more details?
>>
>>>Yes - "MyLocalTable" is already an updatable cursor - but I'm using SPT for Inserts the aforementioned reasons. Actually, a TableUpdate does nothing but SPT anyway - not sure why it differs from "manual" SPT.
>>>
>>>My fallback is to simply insert a dummy value to get my identity stuff need, then update it with the normal tablupdate method - at least I know that works. However, I'd sure like to know why the isnert statement is getting translated that way.
>>>
>>>Thanks,
>>>Ken
>>>
>>>>Hi!
>>>>
>>>>The T-SQL code for SQL Server command is certainly generated by deep internals in VFP. That is why I attempt to find/propose anotehr solution :-)
>>>>
>>>>You can get cursor by SPT and make it updatable like remote view. See FAQ#8153. The only issue here is that SPT cannot map image field into memo. However, despite you will have General field type there, you can always copy MyLocalTable.MyGeneralField into that field and then make update.
>>>>
>>>>HTH.
>>>>
>>>>>Hi Vlad - thanks for responding.
>>>>>
>>>>>Yes - definitely an image type - note that I CAN succesfully store some binary files there.
>>>>>I'm still trying to find out why that is, but it may be realted to file size.
>>>>>Note also that if I insert something small there first, then update with the big file,
>>>>>it goes in fine.
>>>>>
>>>>>I haven't tried the tableupdate on insert, but I'm guessing it would work. I'm not doing
>>>>>that because of a whole other reason to do with identity fields and session scopes - not
>>>>>relevant here, but that's why I'm doing SPT.
>>>>>
>>>>>Now - here's a clue. Keep in mind I'm using a simple SPT insert statement from VFP like
>>>>>

Insert into Mytable (ORIGINALFILEPATH, FILENAME, DESCRIPTION, FILEBIN)
>>>>>values ("val1", "val2", "val3", ?MyLocalTable.MyGeneralField)

>>>>>Watching in SQL Profiler, here is what I see. When I insert the small file, Profiler shows
>>>>>that the entire insert statement is coming in as expected - with hex data for the image field.
>>>>>However, when I attempt to insert the larger file, here is what comes into SQL Server
>>>>>

begin tran declare @h int exec @h=sp_createorphan 'GFile.FILEBIN' WRITETEXT BULK GFile.FILEBIN @h WITH LOG
>>>>>Insert into GFile (ORIGINALFILEPATH, FILENAME, DESCRIPTION, FILEBIN) values ('val1', 'val2', 'val3', {fn getimage(1)} )  exec sp_droporphans commit tran

>>>>>Now - either SQL Server or VFP is doing this, because I'm certainly not.
>>>>>
>>>>>Any ideas?
>>>>>
>>>>>TIA,
>>>>>Ken
>>>>>
>>>>>>Hi!
>>>>>>
>>>>>>Are you sure that field is image field type? I just searched MSDN and read the INSERT command manual. Text pointers are mentioned there. Try to look there.
>>>>>>
>>>>>>Also, did you try to insert record using append blank/populate fields/tableupdate()? Would it work for you?
>>>>>>
>>>>>>>I am succesfully using an offshoot of Vlad Grynchyshyn's FAQ#7995 technique for moving binary data back and forth from SQL 2K to Desktop via SPT. It works great for word, excel, and all other files I've tried until now.
>>>>>>>
>>>>>>>The problem is, that when trying to upload an .exe (a VFP exe actually), I get a SQL error saying that:
>>>>>>>
>>>>>>>"Only text pointer are allowed in work tables, never text, ntext, or image columns. The query processor produced a query plan that required a text, ntext, or image column in a work table."
>>>>>>>
>>>>>>>I should note that this happens ONLY on INSERT where I'm using SPT with a

(myimage field) values (?mygenfield)

clause. An UPDATE statement from the cursor using tableupdate() works fine.
>>>>>>>
>>>>>>>Any ideas anyone?
>>>>>>>TIA,