7

I am trying to figure out how to get the following code to return the row that it just inserted - a co-worker pointed out and suggested running ALTER FULLTEXT CATALOG uiris_production REBUILD but that cannot be run within a user transaction.

The reason this has to be in a transaction is that this is coming from a test framework where the test is wrapped in a transaction and then rolled back.

declare @search varchar(64)
set @login_test = 'foobar'
set @search = '"' + @login_test + '*"'

begin transaction
   insert into users(login) values (@login_test)
   select login from users where contains(login, @search)
commit
Lee Taylor
  • 7,761
  • 16
  • 33
  • 49
Ransom Briggs
  • 3,025
  • 3
  • 32
  • 46

2 Answers2

4

First, make sure your full text index is set up for automatic change tracking. change_tracking_state should be 'A' for this query. 

select t.name, fi.change_tracking_state 
    from sys.fulltext_indexes fi
        inner join sys.tables t
            on fi.object_id = t.object_id
    where t.name = 'users'

But, even with automatic change tracking, there is a certain degree of latency in updating fulltext. You may need to build a WAITFOR into your unit test to accommodate this.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • Even with automatic change tracking enabled it looks as though fulltext indexes are only updated when the transaction is committed so waitfor will have no affect. This doesn't seem possible to do... :( – ajbeaven May 26 '15 at 05:47
-2
SELECT SCOPE_IDENTITY()

would tell you the id of the record you just created.

JeffO
  • 7,957
  • 3
  • 44
  • 53
  • The op is asking how to get the whole row; not only the id. Perhaps you can also suggest to them that they fetch the id from the inserted row, then do a SELECT lookup on that id? – Jon Adams Nov 13 '12 at 12:52