'Add' to a character.

Question

'Add' to a character.

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply

saivko SSC Eights! Points: 898 More actions · Answer 1

Thanks, at this point no more than one user will add a new record... but as you well know that's not future proof and not safe against non-normal circumstances. But it's actually Access that works off the linked SQL tables, so I think Access handles concurrency on it's own. Though I'm not 100% it will catch everything.

I'll see if I have the good opportunity to do what you said rather than put in a quick band-aid fix.

Thanks again.

saivko SSC Eights! Points: 898 More actions · Answer 2

Thanks again! I'll try one of your suggestions.

Is there a good link you have in mind that talks about a sequence table? Or is it almost anything google brings up?

I'm also tempted to just use a calculated column. Off hand, do you know of anything (MS Access, DTS, other tables etc...) that might take issue when the column gets changed? I know you said extensive testing. Just wondering if issues come to your mind beforehand.

GSquared SSC Guru Points: 260824 More actions · Answer 3

Lots of things might get upset about a column change. The way to find out is research it.

For example, you'll need to find any foreign keys that reference the existing column. You can query the metadata for that (I don't have a copy of SQL 2000, and don't remember how to get it, so can't help much on the specifics for that), or you can create a dev copy of the database and brute force your way through by updating the data in one of the rows, and seeing what objects.

You'll need to find out how exactly Access is connecting to the table. Does it use a direct connection, or a stored procedure? Does it try to update that column? What will it take to distribute a new copy of the Access file to every user if it does directly access or otherwise update the column?

Those are going to be specific to your situation, not generic rules.

One thing you can do that might be a good place to start is run a trace on the server, looking for commands that update or otherwise touch that column. If you run that for a while, you'll get a pretty good idea for what you'll need to handle.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 4

saivko (8/10/2009)
Thanks again! I'll try one of your suggestions.
Is there a good link you have in mind that talks about a sequence table? Or is it almost anything google brings up?
I'm also tempted to just use a calculated column. Off hand, do you know of anything (MS Access, DTS, other tables etc...) that might take issue when the column gets changed? I know you said extensive testing. Just wondering if issues come to your mind beforehand.

I haven't had to lookup "sequence tables" on Google because I've seen the right and wrong ways to update one. I guess my recommendation there would be that you should probably Google it. Be warned that there's a lot of bad ways to do it... any method that only allows one row at a time or any method that uses an explicit transaction is bad, period. If you decide that a sequence table would be better than a SELECT MAX() (and it is) or any of the other proposed methods (it's not) and that you really want to use it, post back with some details about what you believe you'd like the table to look like and we'll go from there. The thing I'm worried about is the current ability for a customer to just up and enter a number... if you're fixing that, why can't it be fixed using one of the more automated methods?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

saivko SSC Eights! Points: 898 More actions · Answer 5

Thanks again! Great advice. I hope/wish I can have time to do things right.

Gsquared,

Access connects to SQL via linked tables (using OBDC). Access might not care what I do. Other people connect to the same access file, so distribution is no problem. Thanks for the tip on a trace. I would really like to try converting it to computed column, but I'm not sure if I have the time to test it out and resolve all the issues that may arise. I'll see what the tracer might say.

Jeff Moden,

What methods don't use explicit transactions? I'm also not sure of what other automated methods you have in mind besides changing the ID column itself to be computed.

I thought up a really hokey idea, more like a quick 'fix', and I'm sure you won't find it good…. I would have a generator table with an identity column, a computed column and maybe some other info such as the identity of the creator of the row. A user, via Access code, would insert into the generator table thus making a unique value that the user would then take to insert into the general table. All this would happen behind the code in the Access forms, unknown to the user. No one is going to add records directly using T-SQL, so all the Access users *shouldn't* be able to foil the system.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 6

saivko (8/11/2009)
What methods don't use explicit transactions?

On most systems, everything is an "implicit" transaction and you have to use a BEGIN TRANSACTION to start an "explicit" transaction.

I'm also not sure of what other automated methods you have in mind besides changing the ID column itself to be computed.

Heh... It's easy... read what I posted. 😉

I thought up a really hokey idea, more like a quick 'fix', and I'm sure you won't find it good…. I would have a generator table with an identity column, a computed column and maybe some other info such as the identity of the creator of the row. A user, via Access code, would insert into the generator table thus making a unique value that the user would then take to insert into the general table. All this would happen behind the code in the Access forms, unknown to the user. No one is going to add records directly using T-SQL, so all the Access users *shouldn't* be able to foil the system.

Why not just do that on the original table so that you don't have to worry about which row or rows you just inserted?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

saivko SSC Eights! Points: 898 More actions · Answer 7

Thanks for your patience!

I'm trying the single table / calculated column solution. I made an identical test database to test. I created an integer identity column [ID] on the same table, so that [EIN] can be calculated from it. [EIN] is the nvarchar that needs to preserve the leading zeros, of which almost all tables are dependent upon. (I think I mistakenly wrote varchar earlier)

Before I made [ID] the identity column I copied all the values from [EIN] cast as int to [ID]. Now, I'm trying to make the [EIN] column to be calculated. In the formula on the designer I put in:

=right('0000000000' + cast([ID] + 1 as nvarchar(10)), 9) *

But I get "Error validating column for 'EIN'. Do you want to edit the formula?"

See anything wrong?

* Yes, that nvarchar column is 10 length, but all cells are of length 9. Why? I wish i new.

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Try

REPLACE(STR(ID, 9), ' ', '0')

_____________
Code for TallyGenerator

saivko SSC Eights! Points: 898 More actions · Answer 9

Hmmm, can't validate that either. It seems string/text isn't allowed?

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 10

saivko (8/13/2009)
Hmmm, can't validate that either. It seems string/text isn't allowed?

Correct... Sergiy's recommendation is based on the same thing I said... the ID column should be an INTEGER IDENTITY column and his good code formats it for display.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

saivko SSC Eights! Points: 898 More actions · Answer 11

Well, there is way too much to change on other tables, dts, access, website etc to make an int for every other table. I'm sort of stuck, needing the text column to be the goto point for everything else without changing everything else. I would just use the integer ID column to control and safeguard a record's identity.

Would it work fine if I had a trigger for INSERTs that would edit the nvarchar "fake id" column based upon the identity column?

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 12

saivko (8/13/2009)
Well, there is way too much to change on other tables, dts, access, website etc to make an int for every other table. I'm sort of stuck, needing the text column to be the goto point for everything else without changing everything else. I would just use the integer ID column to control and safeguard a record's identity.
Would it work fine if I had a trigger for INSERTs that would edit the nvarchar "fake id" column based upon the identity column?

Well, I didn't suggest that... look into what I said... Add the ID column as a different name and rename the calculated column to be ID. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

saivko SSC Eights! Points: 898 More actions · Answer 13

Correct me, but this is what I understood you to say. Previously there has been no column named 'ID'. Hopefully this wasn't a source of confusion. I probably have been ambiguous.

But to be sure and name terms, right now, I have a column 'EIN' which is a nvarchar of numeric digits with leading zeros, which is *supposed* to be unique for each record. It is the primary key. But as we know users can choose it's value when a record is created.

You suggest adding a new integer auto-numbering column to act as the identity column. We'll call this 'ID'. Perhaps you mean, rename column 'EIN' to be 'ID' and change the column to be integer and then identity? I just copied the data (converted nvarchar->int) over to 'ID'. After that, I made the 'ID' column as identity. As far as I can tell this has the same effect as what I though you said.

Whatever 'EIN' was (whether recreated or cleared) , it no longer exists as before. It is now a nvarchar calculated from the integer 'ID' column now.

Is that what you were thinking?

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 14

saivko (8/13/2009)
Whatever 'EIN' was (whether recreated or cleared) , it no longer exists as before. It is now a nvarchar calculated from the integer 'ID' column now.
Is that what you were thinking?

Yes... the only problem is that users will no longer be able to enter a number... rather, it will be assigned. That may be the only sticking point but that may also be enough to break your code. You would need to do a simple test. The rest of the app and all the code probably won't give a hoot if the app classes and sql code objects were properly designed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

saivko SSC Eights! Points: 898 More actions · Answer 15

Thanks! That's fine, my only worry about that, is that when I tested it out the numbers that insert next index after the last used used number. To use an illustration of what I'm saying (think 9 digits than rather the 3 of my mock example):

I have these numbers used up:

003, 004, 005, 100, 101, 102, 103, 900, 901, 902, 903. Must the next generated number always start at 904? After I get to 999, can I start filling out the other empty numbers like 006 - 099? and 104 - 899?

If not, I still can probably get away with starting at 904, since it will be unlikely for the numbers to fill out.

How again do you make that 'EIN' column calculated? I missed how to make the nvarchar calculated based off the integer identity column.