Auto fill collumn

I have an existing collumn in a table that contains a 5 digit number used as
an employee id field.(datatype nvarchar) I would like to auto fill the rows
that are '00000' or Null with an number beginning with X0001 and incrementin
g
by 1 thereafter.( I want the X as a prefix). Any Ideas'
Thanks, RobOn Mon, 31 Jan 2005 13:37:23 GMT, Robb Mann wrote:

> I have an existing collumn in a table that contains a 5 digit number used
as
> an employee id field.(datatype nvarchar) I would like to auto fill the ro
ws
>that are '00000' or Null with an number beginning with X0001 and incrementi
ng
>by 1 thereafter.( I want the X as a prefix). Any Ideas'
>Thanks, Rob
Hi Rob,
Define: "beginning" and "thereafter". Remember that tables are UNordered
sets of data, by definition. There is no "first" or "beginning" row. You
will have to define some ranking to base your numbers on.
Assuming you want to use the column date_entered to base the ranking, and
assuming you have no diuplicates in the date_entered, you could use the
following:
UPDATE MyTable
SET employee_id = 'X' + LEFT ('0000' +
CAST((SELECT COUNT(*)
FROM MyTable AS a
WHERE a.date_entered <= MyTable.date_entered
AND COALESCE(a.employee_id,'00000') = '00000') AS int), 4)
WHERE COALESCE (employee_id, '00000') = '00000'
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||What is the primary key of the table? SSN? Payroll number? Are you
saying that the table doesn't have a key right now? If you just want an
arbitrary surrogate key then use IDENTITY. If you requirements are more
complex then we'll need some more information: DDL, sample data,
required end result.
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||If your table does not have duplicated employees by first name and last name
,
then you can use this approach (if not, use also the department to calculate
the rank):
use northwind
go
create table t (
empid char(5) null,
fname varchar(25) not null,
lname varchar(25) not null
)
insert into t values ('00000', 'a', 'b')
insert into t values ('00000', 'c', 'd')
insert into t values (null, 'e', 'f')
insert into t values ('00001', 'v', 'w')
select * from t order by fname, lname
update
e
set
e.empid = 'x' + right('000' + ltrim(rank), 4)
from
t as e
inner join
(
select
a.fname,
a.lname,
count(*) as rank
from
t as a
inner join
t as b
on b.fname + b.lname <= a.fname + a.lname
and (a.empid is null or a.empid = '00000')
and (b.empid is null or b.empid = '00000')
group by
a.fname,
a.lname
) as f
on e.fname = f.fname and e.lname = f.lname
select * from t order by fname, lname
drop table t
go
AMB
"Robb Mann" wrote:

> I have an existing collumn in a table that contains a 5 digit number used
as
> an employee id field.(datatype nvarchar) I would like to auto fill the r
ows
> that are '00000' or Null with an number beginning with X0001 and increment
ing
> by 1 thereafter.( I want the X as a prefix). Any Ideas'
> Thanks, Rob
>|||On Mon, 31 Jan 2005 14:54:43 +0100, Hugo Kornelis wrote:

>UPDATE MyTable
>SET employee_id = 'X' + LEFT ('0000' +
> CAST((SELECT COUNT(*)
> FROM MyTable AS a
> WHERE a.date_entered <= MyTable.date_entered
> AND COALESCE(a.employee_id,'00000') = '00000') AS int), 4)
>WHERE COALESCE (employee_id, '00000') = '00000'
(snip)
Correction: I should have used RIGHT, not LEFT.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Authentication With RS 2000

I am using RS 2000 Eval Edition
I have just enstalled it on my notebook. This is where my existing copy of
SQL Server 2000 resides. The SQL 2000 Server is working fine (as it has for
the past several months)
I installed RS 2000 Eval Edition using all of the "default" settings. I
then re-booted, and installed both RS 2000 Service Packages. ASP.net is
running, and I have verified this.
When I try to open the Report Manager a window pops up and states an
Authentication Required. It prompts me for a User Name and Password. When I
try to type in the Windows User Name and password, it fails. When I try to
type in the Username and password for the SQL 2000 Database it fails.
What am I doing wrong. I have installed RS 2000 Eval on another computer
several years ago so I know what the Report Manager looks like.
Further, I have tried simply going to the Internet and typing in several
variations of: http://<ServerName> directly. Each time it prompts for
Authentication.
I am becoming very frustrated and very angry. It should not be so difficult.
Any suggestions would be welcomed. Also, I have been asked to undertake
this work using SQL 2000 and RS 2000 so please don't tell me to get of of the
dark ages and upgrade to SQL 2005 and RS 2005!
PatHi Again...
I solved my problem by changing the Anonymous Access and Authentication
Control settings in the Default Website folder in my IIS settings.
Thanks
Pat
"Pat (Disciple of Aloof-ism)" wrote:
> I am using RS 2000 Eval Edition
> I have just enstalled it on my notebook. This is where my existing copy of
> SQL Server 2000 resides. The SQL 2000 Server is working fine (as it has for
> the past several months)
> I installed RS 2000 Eval Edition using all of the "default" settings. I
> then re-booted, and installed both RS 2000 Service Packages. ASP.net is
> running, and I have verified this.
> When I try to open the Report Manager a window pops up and states an
> Authentication Required. It prompts me for a User Name and Password. When I
> try to type in the Windows User Name and password, it fails. When I try to
> type in the Username and password for the SQL 2000 Database it fails.
> What am I doing wrong. I have installed RS 2000 Eval on another computer
> several years ago so I know what the Report Manager looks like.
> Further, I have tried simply going to the Internet and typing in several
> variations of: http://<ServerName> directly. Each time it prompts for
> Authentication.
> I am becoming very frustrated and very angry. It should not be so difficult.
> Any suggestions would be welcomed. Also, I have been asked to undertake
> this work using SQL 2000 and RS 2000 so please don't tell me to get of of the
> dark ages and upgrade to SQL 2005 and RS 2005!
> Pat
>

Attribute Limit of 5000 When Mining a Cube

I'm trying to build a association model in the Standard Edition based on an existing cube. I keep getting the error:

Error (Data mining): The 'Product Recommendations' mining model has 60385 attributes. This number of attributes exceeds the attribute limit of 5000 allowed by the current version of the algorithm associated with the mining model.

I created Cube Slice filters and those limit the Customer and Product dimensions (Product is Nested) to well under 5000. The error message also does not change. The number of attributes is equal to the number of rows in the Product dimension, but I expected the cube slice to reduce the number. I tested all the SQL used while it processes and with the MDXFilters the number of rows returned is well under 5000.

So, in short, the final questions is, is it possible to create a mining model in standard edition based on an existing cube where the nested dimension in the model has more than 5000 rows? Is there some other way to filter the query?

I guess my only choice on this if there isn't a way is to extract the data into relational table with only the rows I want to analyze....that's a huge pain and doesn't really make sense when the filters should limit the model size.

What is annoying on this is I can't find one reference anywhere on the microsoft site that this limit even exists within the product...

Thanks for any assistance on this!

This sounds like a bug (and knowing the code, I can see where it comes from).

Standard edition is limited to 5000 attributes for Standard Edition. What you are doing is perfectly legal, assuming there are actually less than 5000 attributes. You may need to contact support so we can get this fixed, or start by writing up a bug report at connect.microsoft.com. If you cannot get this resolved through support and this is blocking you, please let us know and we will see if we can make something happen.

Thanks

-Jamie

|||

Thanks...I'll get a support case open and see what happens.

- L

|||

Jamie,

I opened a support case with Microsoft and have been working with them for a few days. They did agree that this is a defect in the product. The current response is that, "they may consider a design change to account for filtered cube slices". This is a pretty big deal for our development. I'd also question the limit in general since its not even documented, does the limit really drive more sales to the Enterprise Version of the product? I think if there is a limit, it should really be much larger since in practice, 5000 rows in a nested table is so small. It is very common for small companies to have product catalogs of 60k products.

THANKS!

|||

I have been working with the support person you are in contact with. The issue isn't if the problem with be fixed, it is simply how the problem will be fixed. I believe we will have a resolution today - in any case, it will resolve the issue you are seeing.

Regarding the 5000 attribute limit, that's a completely seperate debate that I don't want to get deeply into in the forums. I would only state that the differentiation between the various editions isn't limited to data volumes, functional differences arise as well (e.g. data mining transforms in Integration Services, etc.). Your cost structure in general may not support a wholesale changeover to Enterprise edition due to data volumes, but the ROI of some of the features that are made available in the larger edition may warrant an upgrade on a single machine. There are different licensing options for this as well, i.e. server CAL vs Processor licences, which for a small-medium size business, a 50-CAL Enterprise license may do the trick and still be in the affordable price range.

If you want to contact me directly, it's Jamie <dot> MacLennan <at> microsoft <dot> com

Thanks

Attribute Limit of 5000 When Mining a Cube

I'm trying to build a association model in the Standard Edition based on an existing cube. I keep getting the error:

Error (Data mining): The 'Product Recommendations' mining model has 60385 attributes. This number of attributes exceeds the attribute limit of 5000 allowed by the current version of the algorithm associated with the mining model.

I created Cube Slice filters and those limit the Customer and Product dimensions (Product is Nested) to well under 5000. The error message also does not change. The number of attributes is equal to the number of rows in the Product dimension, but I expected the cube slice to reduce the number. I tested all the SQL used while it processes and with the MDXFilters the number of rows returned is well under 5000.

So, in short, the final questions is, is it possible to create a mining model in standard edition based on an existing cube where the nested dimension in the model has more than 5000 rows? Is there some other way to filter the query?

I guess my only choice on this if there isn't a way is to extract the data into relational table with only the rows I want to analyze....that's a huge pain and doesn't really make sense when the filters should limit the model size.

What is annoying on this is I can't find one reference anywhere on the microsoft site that this limit even exists within the product...

Thanks for any assistance on this!

This sounds like a bug (and knowing the code, I can see where it comes from).

Standard edition is limited to 5000 attributes for Standard Edition. What you are doing is perfectly legal, assuming there are actually less than 5000 attributes. You may need to contact support so we can get this fixed, or start by writing up a bug report at connect.microsoft.com. If you cannot get this resolved through support and this is blocking you, please let us know and we will see if we can make something happen.

Thanks

-Jamie

|||

Thanks...I'll get a support case open and see what happens.

- L

|||

Jamie,

I opened a support case with Microsoft and have been working with them for a few days. They did agree that this is a defect in the product. The current response is that, "they may consider a design change to account for filtered cube slices". This is a pretty big deal for our development. I'd also question the limit in general since its not even documented, does the limit really drive more sales to the Enterprise Version of the product? I think if there is a limit, it should really be much larger since in practice, 5000 rows in a nested table is so small. It is very common for small companies to have product catalogs of 60k products.

THANKS!

|||

I have been working with the support person you are in contact with. The issue isn't if the problem with be fixed, it is simply how the problem will be fixed. I believe we will have a resolution today - in any case, it will resolve the issue you are seeing.

Regarding the 5000 attribute limit, that's a completely seperate debate that I don't want to get deeply into in the forums. I would only state that the differentiation between the various editions isn't limited to data volumes, functional differences arise as well (e.g. data mining transforms in Integration Services, etc.). Your cost structure in general may not support a wholesale changeover to Enterprise edition due to data volumes, but the ROI of some of the features that are made available in the larger edition may warrant an upgrade on a single machine. There are different licensing options for this as well, i.e. server CAL vs Processor licences, which for a small-medium size business, a 50-CAL Enterprise license may do the trick and still be in the affordable price range.

If you want to contact me directly, it's Jamie <dot> MacLennan <at> microsoft <dot> com

Thanks

attaching system database

Hi,
I would like to ask if system database from other server can be
attached to other server. So basically, the process is, the existing
system database(including user databases) will be replaced by other
set of database(system and user databases) the new set of the
databases are just located in a separate drive in a cluster
environment. Will this procedure can apply?
http://support.microsoft.com/kb/224071
I am not sure if this will work. Just need to verify.
Thanks.This will work. In some cases, you can simply copy the MDF/LDF files on the
same location where the system database files exist while the SQL Server
service is stopped. Once it starts, it should see those files. I used this
approach when I needed to set the collation settings for my tempdb. Instead
of rebuilding the master database, I just copied the model database files
from one server which has the same collation settings I need for my tempdb
and it worked
"Bry" <fqies_bry@.yahoo.com> wrote in message
news:1186108853.215054.30080@.j4g2000prf.googlegroups.com...
> Hi,
> I would like to ask if system database from other server can be
> attached to other server. So basically, the process is, the existing
> system database(including user databases) will be replaced by other
> set of database(system and user databases) the new set of the
> databases are just located in a separate drive in a cluster
> environment. Will this procedure can apply?
> http://support.microsoft.com/kb/224071
> I am not sure if this will work. Just need to verify.
> Thanks.
>|||What if I have to use the other drive where they put the database
files? like I will modify the SQL(as KB said) to modify the drive of
the system database. will there be implications/problems?|||Hi,
Make sure that your destination SQL Server instance is patched up to the
same build as the instance from which you are taking the system databases.
Jonathan
Bry wrote:
> Hi,
> I would like to ask if system database from other server can be
> attached to other server. So basically, the process is, the existing
> system database(including user databases) will be replaced by other
> set of database(system and user databases) the new set of the
> databases are just located in a separate drive in a cluster
> environment. Will this procedure can apply?
> http://support.microsoft.com/kb/224071
> I am not sure if this will work. Just need to verify.
> Thanks.
>|||I was about to say this...this is very important as your backups will not
work
"Jonathan Psaila-Depasquale" <nospam@.nospamplease.com> wrote in message
news:OLlqTka1HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Make sure that your destination SQL Server instance is patched up to the
> same build as the instance from which you are taking the system databases.
> Jonathan
>
> Bry wrote:
>> Hi,
>> I would like to ask if system database from other server can be
>> attached to other server. So basically, the process is, the existing
>> system database(including user databases) will be replaced by other
>> set of database(system and user databases) the new set of the
>> databases are just located in a separate drive in a cluster
>> environment. Will this procedure can apply?
>> http://support.microsoft.com/kb/224071
>> I am not sure if this will work. Just need to verify.
>> Thanks.