Grrr!
I'm trying to run a script:
print 'Declaring cursor'
declare cInv cursor forward_only static for
select
distinct
inv.company,
inv.contact,
inv.address1,
inv.city,
inv.state,
inv.postalcode,
inv.cmcompanyid
from
dedupe.dbo.ln_invoice as inv
left join
dedupe.dbo.customerid as cid
on
dbo.fnCleanString(inv.company) = cid.searchco
where
((inv.customerid is null
and cid.searchco is null)
and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' and
inv.outstanding > 0.01))
and not inv.company is null)
print 'Cursor declared'
declare
@contact varchar(75),
@company varchar(50),
@address1 varchar(75),
@city varchar(30),
@state varchar(20),
@zip varchar(10),
@cmcompanyid varchar(32),
@iCount int,
@FetchString varchar(512)

open cInv
print 'cursor opened'
fetch cInv into
@company,@contact,@address1,@city,@state,@zip,@cmc ompanyid
print 'Cursor fetched @@Cursor_rows = ' + cast(@@cursor_rows as
varchar(5))

All the prints are there to help me figure out what's going on!
When I get to the Print 'Cursor fetched @@cursor_rows....
the value is 0 and the script skips down to the close and deallocate.
BUT, if I just highlight the Select...When section, I get over 2,000
rows. What am I missing?
Thanks.

I was wrong. I thought I highlighted the query to run just those lines
but that also returned no rows. When, however, I cut and paste the
query into a new window in Query Analyzer, it returns 2,000 rows. I'm
left with the notion there's something wrong either with my cursor
declaration or the first fetch and I just don't see it. I get no error
messages, just the print statements I have at the end saying the cursor
has been closed and deallocated. The one print statement inside the
while body never prints (as you would expect since there were no rows
in the cursor). So, I'm still lost.
Thanks.

What are you using a cursor for? Please post DDL, sample data inserts
and show your required end result so that we can understand the problem
fully. Cursors are rarely the best way to do things in SQL Server.

--
David Portas
SQL Server MVP
--

Plese forgive (or ignore from here) long post!
Imagine that for years your company has been using a word processor
with defined forms and free entry text, combined with a document
management system, to do its actual billing. For all those years, there
were NO data integrity checks on the customer info entered on the word
processing forms or the invoices and invoices were initially generated
and filled in by assistants to the billers. The only way bills got to
the correct client is that somebody in accounting manaually examined
each and every bill before printing it out. In the past four years,
however, the company has grown explosively and it can't be done by
hand. I say all this by preface to give you and understanding of the
data I have to work with... because The Powers That Be finally decided
Lotus Notes wasn't an effective billing system. So, I was able to build
an initial table, summarized here:

InvoiceTable
Contact Company Address City State Zip
-------------- ------------- ------------- ------- --------
-------
Ms. Lola EoLa, HR Mom & Pop Wigets Blah Blah Blah Blah
Lola Eola Mom and Pop Wigets .....
Lola Got Married Mom & Pop Wegits .....
Mr. Thomas Swift Acme Roadrunner ........
Tom Swift Acme Road Runner
Tom Watson IBM
Thomas Watson IBM Corp.
Dr. Thomas J WatsonIBM Global (Acme Roadrunner)

The new accounting package requires every invoice we import from the
old system to have a Customer ID and, where appropriate (should I say
possible?) an address ID. One customer ID relates to many address IDs.
Customer ID is being built by cleaning the company name of all
punctuation, spaces, and common words like The, Inc., LTD, etc. Each
unique string becomes an index into the Customer table. The Customer
ID, however, is the first six letters of the index string concatenated
with 4 digits to ensure uniqueness. So, Mom & Pop Wigets becomes
MOMPOP0000 and Mom & Pop Wegits (mis-spelled) becomes MOMPOP0001.
Fortunately, the user teams have given us a list (Excel) identifying
the first Mom & Pop as the "valid" billable entity. Whew! Here's where
I want to get for the temp customer ID table

CustID SearchCO
MOMPOP0000 MOMPOPWIGETS
MOMPOP0001 MOMANDPOPWIGETS
MOMPOP0002 MOMPOPWEGITS
ACMERO0000 ACMEROADRUNNER
etc.
The cursor I'm trying to use is supposed to populate this temp customer
table. After creating the temp table, the customer table will be
created by giving all Mom & Pop variants the same MOMPOP0000 identifier
(user's Excel spreadsheet says so) but at least 2 IBM identifiers
IBM0000 and IBMGLO000 (bad example because the alpha is too short --
two divisions of the same company are two customers (I call them
billable entities)). If you've gotten this far, and I don't blame you
if you haven't, here's the script for step 1.
declare cInv cursor forward_only static for
select
distinct
inv.company,
inv.contact,
inv.address1,
inv.city,
inv.state,
inv.postalcode,
inv.cmcompanyid
from
dedupe.dbo.ln_invoice as inv
left join
dedupe.dbo.customerid as cid
on
dbo.fnCleanString(inv.company) = cid.searchco
where
((inv.customerid is null
and cid.searchco is null)
and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' and
inv.outstanding > 0.01))
and not inv.company is null)
declare
@contact varchar(75),
@company varchar(50),
@address1 varchar(75),
@city varchar(30),
@state varchar(20),
@zip varchar(10),
@cmcompanyid varchar(32),
@iCount int,
@FetchString varchar(512)
open cInv
fetch cInv into
@company,@contact,@address1,@city,@state,@zip,@cmc ompanyid
while @@fetch_status = 0
begin
set @FetchString = null
set @FetchString = dbo.fnCleanString(@company)
begin tran
insert into customerid
values

(@cmcompanyid,@company,@fetchstring,dbo.fnMakeAlph a(@company,4),'')
set @iCount = @iCount + 1
commit tran
fetch next from cInv into @contact, @company, @address1, @city,
@state, @zip, @cmcompanyid
end
close cInv
deallocate cInv

fnCleanString strips out punctuation, spaces, inc, ltd, etc.
fnMakeAlpha gets the 6 character substring plus the initial 4 zeros
then calls a function to test that string against the temp Customer ID
table and increments the digits if XXXXXX0000 is already there -- and
keeps incrementing until the ID is unique.
The problem I'm having right now, hoewver, is that the select returns
no rows in the script but if I cut and paste it into a Query Analyzer
window by itself, I get 2000 + records.
Does this make any sense at all?
Thanks

Don't these problems tell you something? If you can't validate the data
itself then what's the point assigning new arbitrary IDs? Use or obtain
the tax id / company registration number to validate these companies -
make that a key to prevent future duplicates. Otherwise, use some
address validation software to validate addresses and then audit the
duplicates. Assigning an arbitrary new ID just because some of the
letters differ seems little better than what you already have.

Anyway, how about this (I'm assuming you'll clean up the names first).
For a one-off update of 2000 names performance should be reasonable if
not great.

UPDATE Contacts
SET cust_id =
(SELECT LEFT(Contacts.cleaned_name,6)+
RIGHT('0000'+CAST(COUNT(DISTINCT cleaned_name) AS VARCHAR),4)
FROM Contacts AS C
WHERE cleaned_name LIKE LEFT(Contacts.cleaned_name,6)+'%'
AND cleaned_name < Contacts.cleaned_name)

--
David Portas
SQL Server MVP
--

Thanks for the tip -- never occurred to me to count the matching
alphas. But doesn't this only work when adding one record at a time?
And if I'm adding one record at a time, aren't I back to a cursor?
Sorry to be so dense.

As for your first paragraph, I don't even know where to begin. The new
accounting package was supposed to go live the first of Feb. It
requires the IDs in the given format. I've been standing on the
soap-box and pounding my shoe on the table since I got here over two
years ago. The Powers That Be didn't see the problem because the poor
slobs in accounting were really doing it all manually. Then they added
over 500 billers in the last year and it's finally bitten them on the
fundament. In the meantime my toungue is bleeding from biting it so
hard.

Thanks for the tip.

> But doesn't this only work when adding one record at a time?

No, it works on any data-set. You will only get duplicated IDs if
cleaned_name is duplicated (i.e. the WHOLE name is duplicated exactly).
Having said that, I only tested it with the few rows of sample data you
posted. If you need more help please come back with some runnable code
to reproduce the problem in full: CREATE TABLE ..., INSERT INTO... and
show what end result you want from the data.

--
David Portas
SQL Server MVP
--