Create procedure or trigger to auto generate string ID

Dear everyone,

I would like to create auto-generated "string" ID for any new record inserted in SQL Server 2000.

I have found some SQL Server 2000 book. But it does not cover how to create procedure or trigger to generate auto ID in the string format.

Could anyone know how to do that?? Thanks!!

From,

Royi dont think there is any "autogenerated string" you can only have a numeric column...you need to write your own script to get the next value if you decide to use strings ...

hth|||Do not use a string in your DB, rather cast it to a string when you need to use it.|||Thanks you for reply!!

I know there is no autogenerate string. But I need to use string value as primary key. When new record inserted, e.g. string 'A0001' should be generated automatically.

U said that script can help. Could u briefly tell me some steps by how to write??

Thanks for help!!!|||i got some code in my pc at work place...it will take a value and give you the next number/string..

xample:
if you pass 1 to it, it will return 2..
if you pass A1 it will return A2 and return Abcde45 if you pass Abcde44...
the only drawback is it wont work if you try to use a1a...as long as its string followed by number...it works...
i can share it here on monday if you are interested...

hth|||You can write a trigger on that table to fire on INSERT, and that will fill in the next number in your table.

Look up Triggers in Books Online|||Thanks you for reply!!

I could not find books for writing this procedure.

Could u share the code for what u said??

Thanks a lot!!!!!|||Look for SQL Server Books Online - it's a free reference. Do a Google search, I don't know the URL.

Create NamedCalculation in DSV based on other NamedCalculation

Dear Friends,

How can I create a namedcalculation based on another named calculation?

The first named calculatoion has in expression textarea:

CASE
WHEN [D_INST_TIPO_ID]=1 THEN
CASE
WHEN [Price/NPV] = 0 THEN [Avg Flash]
ELSE [Price/NPV]
END
END

And I need to create a second named calculation based on the first!!

If it's not possible, can I set the expression of first named calculation to a variable? To use only one named calculation? example:

xx=(CASE
WHEN [D_INST_TIPO_ID]=1 THEN
CASE
WHEN [Price/NPV] = 0 THEN [Avg Flash]
ELSE [Price/NPV]
END
END)

xx/(Field3)+(Field4)*(Field5)

?

Thanks!!

I don't believe this is possible. You may need to repeat the first calculation in the context of the second one.|||Someone knows any solution?|||

Hi,

you can't use a named calculation inside another.

If you have formulae that are going to be used one inside another,

then you can use 'calculated measures'.

These are created from the 'calculation' tab of the BI studio.

and they are evaluated at run time.

Check this link for more details of creating calculations,

http://msdn2.microsoft.com/en-us/library/ms181224.aspx

if you have specific reasons not to use this,

then you have to expand all your formulae to the lowest level.

That is to elements that are already avaliable in the dsv table.

Regards

Vijay R

Create NamedCalculation in DSV based on other NamedCalculation

Dear Friends,

How can I create a namedcalculation based on another named calculation?

The first named calculatoion has in expression textarea:

CASE
WHEN [D_INST_TIPO_ID]=1 THEN
CASE
WHEN [Price/NPV] = 0 THEN [Avg Flash]
ELSE [Price/NPV]
END
END

And I need to create a second named calculation based on the first!!

If it's not possible, can I set the expression of first named calculation to a variable? To use only one named calculation? example:

xx=(CASE
WHEN [D_INST_TIPO_ID]=1 THEN
CASE
WHEN [Price/NPV] = 0 THEN [Avg Flash]
ELSE [Price/NPV]
END
END)

xx/(Field3)+(Field4)*(Field5)

?

Thanks!!

I don't believe this is possible. You may need to repeat the first calculation in the context of the second one.|||Someone knows any solution?|||

Hi,

you can't use a named calculation inside another.

If you have formulae that are going to be used one inside another,

then you can use 'calculated measures'.

These are created from the 'calculation' tab of the BI studio.

and they are evaluated at run time.

Check this link for more details of creating calculations,

http://msdn2.microsoft.com/en-us/library/ms181224.aspx

if you have specific reasons not to use this,

then you have to expand all your formulae to the lowest level.

That is to elements that are already avaliable in the dsv table.

Regards

Vijay R

Create Mining Structure and Mining Model with code

Dear friends,

I encounter a serious problem.

I would like to develop an application that can create Data Mining structures and a mining model in SQL Server 2005 with VB.NET. I tried the code from book Data Mining with SQL server 2005 in chapter 14 but did not work. Any good idea?

Please help me.

Best regards,

Manolis

Can you post the errors you're seeing?

Some of the AMO objects/methods changed slightly in the final release (this book was based on pre-release APIs). The C# sample here should help you diagnose the errors: http://www.sqlserverdatamining.com/DMCommunity/Downloads/Links_LinkRedirector.aspx?id=78.

|||

The errata for chapter 14 (and others, ironically, including how to get to this site) is at http://www.wiley.com/WileyCDA/WileyAncillary/productCd-0471462616.html

|||

Thank you very much for your help.

The errors that I can see in the code that you gave in your answer are the following and they are more or less the same as I had previously

I tried the code but initially I have encounter the following problems.

1. In any line that have the declaration As Server, As Database like in

Public Function CreateDatabase(ByVal srv As Server, ByVal databaseName As String) As Database gives me the problem that type Database is not declared the same type Server is not declared and it does not give me any option.

2. In addition to that for As DataSource, As RelationalDataSource, As RelationalDataSourceView, As ScalarMiningStructureColumn, As DataSourceViewBinding, gives me the problem that type is not declared.

3. Finally in mc = New MiningModelColumn("Yearly income", Utils.GetSyntacticallyValidID("Yearly income", Type.GetType(MiningModelColumn))) is not accesible in this context because it is 'Private'.

I have some more problems but I thing that by solving the above that I referred I will solve the rest.

Thank you any way.

Best regards,

Manolis