SSMA - - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

SSMA -

Author

Topic

neerajdba
Starting Member

2 Posts

Posted - 2011-11-17 : 06:03:42

Hi,
I have use this code to declare collection index

DECLARE
@CollectionIndexInt$TYPE varchar(max) = ' TABLE OF ( RECORD ( UCID DECIMAL , C_TIME DATETIME , KEY DECIMAL , KEY_SET DECIMAL , VALUE DECIMAL ) )',
@CollectionIndexInt$TYPE$2 varchar(max) = ' TABLE OF ( RECORD ( KEY_SET DECIMAL , VALUE DECIMAL ) )'

DECLARE
@main_sets_item dbo.CollectionIndexInt = dbo.CollectionIndexInt ::[Null].SetType(@CollectionIndexInt$TYPE),
@main_item dbo.CollectionIndexInt = dbo.CollectionIndexInt ::[Null].SetType(@CollectionIndexInt$TYPE$3),

Now I want to use this like

FETCH @c_sysref
INTO
@ucid,
@c_time,
@key,
@key_set,
@value

IF @@FETCH_STATUS = -1
BREAK

SET @main_sets_item = @main_sets_item.ExtendAndSetRecord(@collection_cur_key, @main_sets_item.GetOrCreateRecord(@collection_cur_key).SetDecimal(N'VALUE', @value))

SET @main_sets_item = @main_sets_item.ExtendAndSetRecord(@collection_cur_key, @main_sets_item.GetOrCreateRecord(@collection_cur_key).SetDecimal(N'KEY_SET', @key_set))

SET @main_sets_item = @main_sets_item.ExtendAndSetRecord(@collection_cur_key, @main_sets_item.GetOrCreateRecord(@collection_cur_key).SetDecimal(N'KEY', @key))

SET @main_sets_item = @main_sets_item.ExtendAndSetRecord(@collection_cur_key, @main_sets_item.GetOrCreateRecord(@collection_cur_key).SetDatetime(N'C_TIME', @c_time))

SET @main_sets_item = @main_sets_item.ExtendAndSetRecord(@collection_cur_key, @main_sets_item.GetOrCreateRecord(@collection_cur_key).SetDecimal(N'UCID', @ucid))

DECLARE
@key_set$2 float(53)

SELECT @key_set$2 = sysdb.ssma_oracle.db_get_next_sequence_value(N'Tific', N'DEV_LOG', N'LOG_SAC_SETS_SEQ')

SET @main_sets_item = @main_sets_item.SetRecord(@i, @main_sets_item.GetOrCreateRecord(@i).SetDecimal(N'KEY_SET', @key_set$2))

SET @main_item = @main_item.Extend()

SET @main_item = @main_item.SetRecord(@main_item.Last(), @main_item.GetOrCreateRecord(@main_item.Last()).SetDecimal(N'UCID', @main_sets_item.GetRecord(@i).GetDecimal(N'UCID')))

SET @main_item = @main_item.SetRecord(@main_item.Last(), @main_item.GetOrCreateRecord(@main_item.Last()).SetDatetime(N'C_TIME', @main_sets_item.GetRecord(@i).GetDatetime(N'C_TIME')))

SET @main_item = @main_item.SetRecord(@main_item.Last(), @main_item.GetOrCreateRecord(@main_item.Last()).SetDecimal(N'KEY', @main_sets_item.GetRecord(@i).GetDecimal(N'KEY')))

SET @main_item = @main_item.SetRecord(@main_item.Last(), @main_item.GetOrCreateRecord(@main_item.Last()).SetDecimal(N'KEY_SET', @main_sets_item.GetRecord(@i).GetDecimal(N'KEY_SET')))

But when i use this like

WHILE @ii <= @main_item.COUNT
INSERT INTO log_sac_main VALUES @main_item.GetRecord(@ii)

This gives Error,

Please help me on this
- Neeraj

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-17 : 12:21:40

I've never used SSMA, so I don't have the exact answer, but...

What's the error?

Saw this article?

neerajdba
Starting Member

2 Posts

Posted - 2011-11-18 : 01:22:26

Thanks russell to prompt reply. Ok forget SSMA, if i use Collections via CLR UDT then i am not able to use this.

actually i am not able to use @main_item var to insert statement. when i use like this

INSERT INTO log_sac_main VALUES @main_item.GetRecord(@ii)

this gives error 'incorrect syntax error'. My problem is how i use @main_item var for insert record.

Thanks

johntech
Yak Posting Veteran

51 Posts

Posted - 2011-11-30 : 09:37:51

First about SSMA look to below link:
http://www.google.com.eg/url?sa=t&rct=j&q=sql%20server%20ssma&source=web&cd=2&ved=0CCsQFjAB&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2Ff%2F2%2F8%2Ff283e6f5-d5b2-4fba-b09e-1024ee19bb2c%2Fsqlserver2000migrationassistant_final.pdf&ei=Uz_WTo7gIoXltQa49pyRAQ&usg=AFQjCNEocTIB8a2JwvwpkjxulxNiNR9OlQ&sig2=Yxt_WLW2g_12r1PY6lNg4Q&cad=rja

second:
Do you know what the difference is between using SET and SELECT when assigning varaibles in T-SQL? Well, there are some differences. I came across a great article by Narayana Vyas Kondreddi from the UK that describes the difference between the two.

The article is well worth the read, but here are the main points:

SET is the ANSI standard for variable assignment, SELECT is not.
SET can only assign one variable at a time, SELECT can make multiple assignments at once.
If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources