| Author |
Topic |
|
karuna
Aged Yak Warrior
582 Posts |
 Posted - 2005-04-15 : 18:43:26
|
| I have a DTS package in which I import some data from a excel file to a temp table and then populate other tables from this temp table.After this I need to update one specific column of a table and I need to use an activex function to update this column.Whats the best way to do? As of now I created one more connection for the database server and use a transform task between them in which I call that activex script and map the equivalent columns, call the activex function for the specific column.I dont want to append data..Just update ...Is this assumption right?Karunakaran___________NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER... |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-15 : 19:28:32
|
| What does the ActiveX function do? |
 |
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-04-15 : 20:17:46
|
| ActiveX script has a function which generates an hashed value for that column.Karunakaran___________NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-15 : 20:31:34
|
| Any possibility of writing that as a UDF in T-SQL? The reason I ask is that doing ActiveX transformations can slow down the process quite a bit, vs. doing a straight DTS and then updating afterwards.If not, you'll have to modify the Transformations for each column and create the proper ActiveX transform for the affected column(s). I've never used ActiveX transformations, so I can't give an example, but Books Online should have something, as well as this site:http://www.sqldts.com/ |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-04-16 : 01:05:33
|
| I tried searching but so far no luck.Once I import to the temp table then its all plain T-Sql used to populate the other tables. This particular tables say TblA has one column which should have a hashed or some kind of encrypted value to identify that row in a unique manner. We use the data of this column as a parameter in our web page for query string.Any pointers towards UDF where I can generate a unique value everytime something like this "f2089818e894187e674593d7626dbbbc8f1605704eb9...." based on a input parameter.Karunakaran___________NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-16 : 09:57:44
|
| Converting a type to binary or varbinary will give you a hexadecimal representation of it, though it will not be an actual string. You may want to look at CHECKSUM() and BINARY_CHECKSUM() in Books Online.If you only need a unique binary value to identify a row, and it does not have to depend on the actual data, use NEWID() to create a new uniqueidentifier. |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-04-17 : 03:12:06
|
| Varbinary and Binary did not work out. The output of hexadecimal can be easily converted back to original text.I dont want people doing that. Actually its dependent on other three columns, while one column is an identity row so its always unique and other two columns can have duplicate values. The identity column dependency is to ensure that the value generated out of this combination is always unique. If NEWID() can give always a unique value for any number of records then I can go with NEWID().Considering that the table has million records, I'll happily add one more column as long as NEWID() can give unique values.I'll try out NEWID() and see how it works.Thx for all the suggestions Rob.Karunakaran___________NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER... |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-04-17 : 03:51:53
|
Worked with some sample data with NEWID(). Looks like its all I need... Thanks Rob.In a scemario where I insert data from other table using a select. So the query will be something like this:I also have some records in TblA already.Insert into TblA (FirstName,LastName,CustomerID,UniqueId)Select (FirstName,LastName,CustomerId,NEWID()) from TblB What will happend if a NEWID() generated from the select is already there in TblA?.Will I get an error in my insert or the NEWID() will be regenerated during insert? considering this scenario I have made that column Is RowGuid as true, So that I dont need to have a NEWID() in my select list. Is this a right way to do? Or Am I messing it up too much...Karunakaran___________NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-17 : 08:05:33
|
| NEWID() is random enough that you'll never get a duplicate. The algorithm used to be based on network MAC address (more or less unique in itself) along with datetime and some other random generator functions. You might be able to find the exact algorithm online, but it really won't matter much. Unless you anticipate inserting multiple billions of trillions of rows of data, you won't come close to generating a dupe. |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-04-17 : 08:30:01
|
| Thanks for all the inputs Rob.Karunakaran___________NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER... |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-04-17 : 11:46:33
|
| Rob,Whether the unique id will always be in this format? Or it varies based any other issues?XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXXI need to confirm on this format to process it during a web request.Karunakaran___________NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-17 : 23:22:11
|
| A GUID is a binary(16) value that is displayed as a character string in that format, and SQL Server will recognize that format as a GUID. |
|
|
|
|
update specific column using activex script