Cursor Help

Hi,

Can somebody help me with this code. I have a table of 1000 records wiith unque key Contactid. This table has some duplicates on First, Name and ZipCode. What I am trying to do is open a cursor with the name ContactsDedup and if it is the first record insert it into another table just contactid and personid(It is just some value so i am giving it from my row counter @.counter. When my cursor goes to next record I am supposed to check if it is the same record as the first an dif it is look for ts personid in the other table and give the same personid to that contact also, if not then insert the that contactid with new personid(which I can use @.counter).

After writing this code I am getting as syntax error near else.

Can somebody help me with this. I really willl appreciate I

Create Procedure sp_ContactsDedupProcedure as
Declare @.Cid int
Declare @.FName varchar(100)
Declare @.LName varchar(100)
Declare @.ALine varchar(100)
Declare @.CC varchar(50)
Declare @.St varchar(50)
Declare @.Zip varchar(50)
Declare @.WA varchar(50)
Declare @.WP varchar(50)
Declare @.HA varchar(50)
Declare @.HP varchar(50)
Declare @.Email varchar(50)
Declare @.Login varchar(50)
Declare @.PID int
Declare @.Counter int
Declare @.RowCounter int
Declare @.FID int
Declare @.PPID int
Set @.counter = 0
set @.rowcounter = 0

Declare ContactsDedup Cursor
Scroll Dynamic For
Select Contactid, FirstName, LastName, AddressLine1, City, StateOrProvince, PostalCode, WorkArea, Workphone,
HomeArea, HomePhone, EmailAddress, LoginID, Personid from ContactsSample

Declare @.Contactid Int
Declare @.FirstName varchar(100)
Declare @.LastName varchar(100)
Declare @.AddressLine1 varchar(100)
Declare @.City varchar(50)
Declare @.Stateorprovince varchar(50)
Declare @.PostalCode varchar(50)
Declare @.WorkArea varchar(50)
Declare @.WorkPhone varchar(50)
Declare @.HomeArea varchar(50)
Declare @.HomePhone varchar(50)
Declare @.EmailAddress varchar(50)
Declare @.LoginID varchar(50)
Declare @.Personid varchar(50)

Declare @.Contactid1 Int
Declare @.FirstName1 varchar(100)
Declare @.LastName1 varchar(100)
Declare @.AddressLine11 varchar(100)
Declare @.City1 varchar(50)
Declare @.Stateorprovince1 varchar(50)
Declare @.PostalCode1 varchar(50)
Declare @.WorkArea1 varchar(50)
Declare @.WorkPhone1 varchar(50)
Declare @.HomeArea1 varchar(50)
Declare @.HomePhone1 varchar(50)
Declare @.EmailAddress1 varchar(50)
Declare @.LoginID1 varchar(50)
Declare @.Personid1 varchar(50)

Declare PersonContacts Cursor For
Select Contactid, Personid

Declare @.PContactId int
Declare @.PPersonID int

open ContactsDedup

Fetch Next from ContactsDedup into
@.Contactid, @.FirstName, @.LastName, @.AddressLine1, @.City, @.StateOrProvince, @.PostalCode, @.WorkArea, @.Workphone,
@.HomeArea, @.HomePhone, @.EmailAddress, @.LoginID, @.Personid

While @.@.Fetch_Status = 0
Begin

set @.Counter = @.counter + 1

If @.Counter = 1

Insert into PersonContactID(Personid, Contactid)
select @.counter, @.contactid

else

set @.counter = @.counter + 1
set @.Cid = @.contactid
set @.FName = @.FirstName
Set @.LName = @.LastName
Set @.ALine = @.AddressLine1
set @.CC = @.City
set @.St = @.Stateorprovince
set @.zip = @.PostalCode
set @.WA = @.WorkArea
set @.WP = @.WorkPhone
set @.HA = @.HomeArea
set @.HP = @.HomePhone
Set @.Email = @.EmailAddress
Set @.Login = @.loginid
set @.Pid = @.Personid

Declare ContactsDedupCheck Cursor For
Select Contactid, FirstName, LastName, AddressLine1, City, StateOrProvince, PostalCode, WorkArea, Workphone,
HomeArea, HomePhone, EmailAddress, LoginID, Personid from ContactsSample

open ContactsDedupCheck

Fetch Next from ContactsDedupCheck into @.Contactid1, @.FirstName1, @.LastName1, @.AddressLine11, @.City1, @.StateOrProvince1, @.PostalCode1, @.WorkArea, @.Workphone1,
@.HomeArea1, @.HomePhone1, @.EmailAddress1, @.LoginID1, @.Personid1

While @.rowcounter >= @.counter
Begin

set @.rowcounter = @.rowcounter + 1

If @.FName = @.FirstName

--If @.Lname = @.LastName

--If @.ALine = @.AddressLine1

Set @.FID = @.Contactid1

else


Fetch Next from ContactsDedupCheck into @.Contactid1, @.FirstName1, @.LastName1, @.AddressLine11, @.City1, @.StateOrProvince1, @.PostalCode1, @.WorkArea, @.Workphone1,
@.HomeArea1, @.HomePhone1, @.EmailAddress1, @.LoginID1, @.Personid1
End

Open PersonContacts
Fetch Next from ContactsPerson into @.Contactid , @.Personid
While @.@.Fetch_Status = 0
Begin
If @.FId = @.Contactid

set @.PPId = @.personid
Insert into PersonContacts(Contactid, Personid)
select @.CID, @.PPID
else
Insert into PersonContacts(Contactid, Personid)
select @.CID, @.counter
Fetch Next from ContactsPerson into @.Contactid , @.Personid

End


Fetch Next from ContactsDedup into
@.Contactid, @.FirstName, @.LastName, @.AddressLine1, @.City, @.StateOrProvince, @.PostalCode, @.WorkArea, @.Workphone,
@.HomeArea, @.HomePhone, @.EmailAddress, @.LoginID, @.Personid


End

Close ContactsDedup
Deallocate ContactsDedup

Close ContactsDedupCheck
Deallocate ContactsDedupCheck

Close PersonContacts
Deallocate PersonContacts

I will appreciate any help. This project d due today, please help as i am a novice to this.Check this part:

If @.FId = @.Contactid
-- begin -- I guess you have to add begin
set @.PPId = @.personid
Insert into PersonContacts(Contactid, Personid)
select @.CID, @.PPID
--end -- the same
else
Insert into PersonContacts(Contactid, Personid)
select @.CID, @.counter
Fetch Next from ContactsPerson into @.Contactid , @.Personid

End

cursor and update

Hi,

I have a table t1 (part_id int, gen_code int) part_id is a primary key.

I have a cursor on t1 for part_id , I take part_id
go thro different tables , do calculations and update gen_code for that part_id in t1 . This process is very slow and I see waittype 'LATCH_EX' and 'CXPACKET' all the time in process info .

do I have to declare cursor for update? how does it diff from read_only cursor.

please reply how do I make this process faster.

thanks,
RamIt would be helpful if you woul dexplain your process in more detail. Otherwise, it's even for a guru difficult to look into a crystal ball.|||Update cursor may even slow down the process by locking rows

cube processing error - perhaps someone can explain

I have one fact table that is also a dimension table + one other dimension table.

When I deployed the cube I was getting an key attribute missing error.

I went back and did a "Process Full" on the fact diminsion and then the cube would

deploy without the error.

Should a fact dimension always be set to process full?

thanks

What could have happen here is:

You've processed your dimension earlier, and then you got some more data inserted into your table. Processing of cube will figure out that dimension is already processed and will only start processing of partitions. The partition processing will see new members that came with new data and will complain during processing.

You dont have to use ProcessFull for your dimensions to keep them in sync. You can run ProcessUpdate for dimensions. But if processing of your dimensions and the cube doesnt take long, I would say do the ProcessFull.

Here is whitepaper that gives bit more details about processing: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_asprocarch.asp

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I went in and changed the process options for the dimension to 'process full'

however it always defaults back to 'process update'

is there a way to lock it on process full?

|||

Click on the "script" button in your processing dialog to get XMLA processing command generated.

You can send same XMLA command to process your dimension and not to use ProcessingDialog.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

ctrl + alt + some key

I accidently pressed some combination of keys and my sql query analyzer has stopped recognising keywords,comments etc.

Has anybody faced this problem ...

have reset all the options availible in QA ... no results ...
tried restarting the comp ... no results ...

any ideas ?here is a screen shot|||did you look at all the QA keyboard shortcuts in BOL?

e.g. Parse the query and check syntax. CTRL+F5|||haha ... knew someone would say that ...
another response i am expecting is someone saying I am colorblind ... or even more than that "I am blind man"|||There can be only one.

Check the Fonts tab in the Options dialog box.|||Are you color blind?? ;)|||Originally posted by blindman Check the Fonts tab in the Options dialog box.

Exactly, seems you have cleared foreground colors for category 'Editor'.|||Pay attention to my first post
--------------------
have reset all the options availible in QA ... no results ...
--------------------

That includes the font tab ...

I would not have posted it here had it been that ... it was the first thing i checked :)|||Forgive us, but you know it is often the obvious that is overlooked!

I couldn't find any documentation about this either. What if, instead of clicking the Reset button on the Options/Font tab, you manually change the comment color?|||The query analyzer shows some sign of activity .. but no color changes ... :(|||Maybe you have to wait until the fall?

OK. Pretty lame.

Do you use the object browser much? I cna not seem to get my colors to go away without messing with the options/fonts tab, myself.|||"shows some sign of activity"?|||Ya .. maybe it was an easter egg for DBA's ...

reinstalled client tools .. problem solved :)|||hey, i like that load/save feature in options, you can even use it as a template for more than 1 pc, or for situations like yours ;)|||I too have my SQC file ... comes in very handy ... coz I have to work on a lot of servers ...

If you wanna see it ... here it is ... just rename it to .SQC

OK .. OK .. I know no one is gonna work on my config settings .. everyone has his own ... but I am uploading it so that I can always download it whenever I am working out of town and do not have my laptop around (which happens quite frequently though)|||Originally posted by Enigma
OK .. OK .. I know no one is gonna work on my config settings .. everyone has his own ... but I am uploading it so that I can always download it whenever I am working out of town and do not have my laptop around (which happens quite frequently though) Cheat. Get one of those little keyring USB RAMDisk doo-dads and carry the critical files on one of those. Stick the RAMDisk into a USB port, and you are in business!

My cell phone uses SD RAM, so I carry a JumpDrive Trio (http://www.lexarmedia.com/jumpdrive/index.html) which does about the same thing. It also allows me to load MS-Excel and MS-Word files to my cell phone easily, so I can read them on the road.

-PatP

CSDVersion key for Service Pack

I was looking up the changes in SP3 and SP3a, and basically the ways to
identify the service pack is installed on the system. In one of the
references I found that the CSDVersion key under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\CurrentVersion
has changed.
I wanted to know whether the CSDVersion key changes with every service pack
release.
From what I have heard, that is not a reliable measure.
"Harshal" <Harshal@.discussions.microsoft.com> wrote in message
news:AAE3B70A-AEDE-4350-B872-D44697DD759F@.microsoft.com...
> I was looking up the changes in SP3 and SP3a, and basically the ways to
> identify the service pack is installed on the system. In one of the
> references I found that the CSDVersion key under
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\CurrentVersion
> has changed.
> I wanted to know whether the CSDVersion key changes with every service
> pack
> release.

CSDVersion key for Service Pack

I was looking up the changes in SP3 and SP3a, and basically the ways to
identify the service pack is installed on the system. In one of the
references I found that the CSDVersion key under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion
has changed.
I wanted to know whether the CSDVersion key changes with every service pack
release.From what I have heard, that is not a reliable measure.
"Harshal" <Harshal@.discussions.microsoft.com> wrote in message
news:AAE3B70A-AEDE-4350-B872-D44697DD759F@.microsoft.com...
> I was looking up the changes in SP3 and SP3a, and basically the ways to
> identify the service pack is installed on the system. In one of the
> references I found that the CSDVersion key under
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion
> has changed.
> I wanted to know whether the CSDVersion key changes with every service
> pack
> release.

CSDVersion key for Service Pack

I was looking up the changes in SP3 and SP3a, and basically the ways to
identify the service pack is installed on the system. In one of the
references I found that the CSDVersion key under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\CurrentVersion
has changed.
I wanted to know whether the CSDVersion key changes with every service pack
release.From what I have heard, that is not a reliable measure.
"Harshal" <Harshal@.discussions.microsoft.com> wrote in message
news:AAE3B70A-AEDE-4350-B872-D44697DD759F@.microsoft.com...
> I was looking up the changes in SP3 and SP3a, and basically the ways to
> identify the service pack is installed on the system. In one of the
> references I found that the CSDVersion key under
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\CurrentVersi
on
> has changed.
> I wanted to know whether the CSDVersion key changes with every service
> pack
> release.