Customized Error Message

Hi,
I have a table like this:
CREATE TABLE Table1 (
[ID] int NOT NULL Primary Key)
After inserting some records, obviously when I try to update all records to
a particular value, SQL server raises an error (number 2627) that indicates
the "Violation of PRIMARY KEY constraint" has happened.
What I need to do is to return an error message instead of SQL server's.
Suppose that I have this SP:
CREATE PROCEDURE UpdateTable1 AS
BEGIN TRAN
UPDATE table1 set id=1
IF @.@.Error = 2627
begin
print 'Duplicate Value'
raiserror('Duplicate Value',16,1)
rollback tran
end
else
begin
print 'Update was ok'
commit tran
end
GO
SQL server returns two error descriptions when I execute this SP: One from
its original messages and the other one from my raiserror statement.
I want to display my own error description to the client without writing
extra code for error handling in my client app(and also for centralizing my
own error descriptions those are returned instead of SQL server's error
messages).
Any help would be greatly appreciated.
Amin
See my reply in .programming. Please don't multipost.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Amin Sobati" <amins@.morva.net> wrote in message news:uy1qx39FEHA.1368@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a table like this:
>
> CREATE TABLE Table1 (
> [ID] int NOT NULL Primary Key)
>
> After inserting some records, obviously when I try to update all records to
> a particular value, SQL server raises an error (number 2627) that indicates
> the "Violation of PRIMARY KEY constraint" has happened.
> What I need to do is to return an error message instead of SQL server's.
> Suppose that I have this SP:
>
> CREATE PROCEDURE UpdateTable1 AS
> BEGIN TRAN
> UPDATE table1 set id=1
> IF @.@.Error = 2627
> begin
> print 'Duplicate Value'
> raiserror('Duplicate Value',16,1)
> rollback tran
> end
> else
> begin
> print 'Update was ok'
> commit tran
> end
> GO
> SQL server returns two error descriptions when I execute this SP: One from
> its original messages and the other one from my raiserror statement.
> I want to display my own error description to the client without writing
> extra code for error handling in my client app(and also for centralizing my
> own error descriptions those are returned instead of SQL server's error
> messages).
> Any help would be greatly appreciated.
> Amin
>
>
>

Customers & Phones

Hello,
I have a customers table linked to a phones table.
Is there a way to write an update query which will take the first & second
phone number of every customer and put it into respective fields (strPhone1
&
strPhone2) on the customer table, and dump any other phones into a memo fiel
d?
Thanks,
LomasWhy? There is so much wrong with this is is not funny. First normal form
violations in each of your columns, for starters, but beyond that:
Data put into memo fields (text columns, I assume) is pretty much useless in
your database from then on. All of the things you can do with it is
basically lost. Best case, you use a query to make a view of the data like
you are asking for and abstract it out from there. I know it takes more
work to do it right at first but it is worth it in the long run. Not to
mention that you can ask here for any query help to make the abstractions
work.
A moderately reasonable solution would be to have two columns <purpose>Phone
and <otherPurpose>Phone in your customer table if they will always have the
same purpose and then the phone table to keep other phone types.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"optidev" <optidev@.community.nospam> wrote in message
news:34051B1C-0F85-4F32-8A20-798B5F2FCD34@.microsoft.com...
> Hello,
> I have a customers table linked to a phones table.
> Is there a way to write an update query which will take the first & second
> phone number of every customer and put it into respective fields
> (strPhone1 &
> strPhone2) on the customer table, and dump any other phones into a memo
> field?
> Thanks,
> Lomas
>|||Ok, let me explain a little bit better.
I am writing this sql code as part of a modification to a database. After
this update query comes the drop tblPhones statement. This sql statement, i
n
its final form, will handle the range of phone types.
It seems to me that allowing a user an unlimited number of phone numbers
will have little practical value, and will be more difficult to query.
Modifying at this stage, while there are still few users, shouldn't cause
too much of an impact.
"Louis Davidson" wrote:

> Why? There is so much wrong with this is is not funny. First normal form
> violations in each of your columns, for starters, but beyond that:
> Data put into memo fields (text columns, I assume) is pretty much useless
in
> your database from then on. All of the things you can do with it is
> basically lost. Best case, you use a query to make a view of the data lik
e
> you are asking for and abstract it out from there. I know it takes more
> work to do it right at first but it is worth it in the long run. Not to
> mention that you can ask here for any query help to make the abstractions
> work.
> A moderately reasonable solution would be to have two columns <purpose>Pho
ne
> and <otherPurpose>Phone in your customer table if they will always have th
e
> same purpose and then the phone table to keep other phone types.
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "optidev" <optidev@.community.nospam> wrote in message
> news:34051B1C-0F85-4F32-8A20-798B5F2FCD34@.microsoft.com...
>
>|||On a conceptual level, SSN, LastName and Ethnicity are attributes of the
Customer, but PhoneNumber and Address are not. One or more PhoneNumber(s)
are related to a specific Customer at a given moment in time, but could
belong to an entirely different Customer tomorrow. Consider, relating them
using a CustomerPhone table with StartDate and EndDate columns to keep an
audit whenever their profile changes.
"optidev" <optidev@.community.nospam> wrote in message
news:6B7323C5-3FE7-41AB-8EFD-0F13C978E1F3@.microsoft.com...
> Ok, let me explain a little bit better.
> I am writing this sql code as part of a modification to a database. After
> this update query comes the drop tblPhones statement. This sql statement,
> in
> its final form, will handle the range of phone types.
> It seems to me that allowing a user an unlimited number of phone numbers
> will have little practical value, and will be more difficult to query.
> Modifying at this stage, while there are still few users, shouldn't cause
> too much of an impact.
>
> "Louis Davidson" wrote:
>|||This is exactly what he wants to avoid, though it is still not a good idea
:)
To answer your original question, you will have to define what data makes a
phone number first or second. If you go ahead with this change, I would at
least suggest you have a column for home phone, one for mobile phone, etc.
rather than phone1, phone2. This is really bad design. On the other hand,
limiting the user to only one phone number of a specific type is a valid
design that doesn't leave the end user guessing what they are storing where.
Post your table structures and someone will help write the query..
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"JT" <someone@.microsoft.com> wrote in message
news:u89fgLkqFHA.272@.TK2MSFTNGP15.phx.gbl...
> On a conceptual level, SSN, LastName and Ethnicity are attributes of the
> Customer, but PhoneNumber and Address are not. One or more PhoneNumber(s)
> are related to a specific Customer at a given moment in time, but could
> belong to an entirely different Customer tomorrow. Consider, relating them
> using a CustomerPhone table with StartDate and EndDate columns to keep an
> audit whenever their profile changes.
> "optidev" <optidev@.community.nospam> wrote in message
> news:6B7323C5-3FE7-41AB-8EFD-0F13C978E1F3@.microsoft.com...
>|||Hi ,
Write a update trigger for your main table and put a code like this , i cant
exactly remember right now
if updated(firstPhone) || updated(secondPhone)
......
"optidev" <optidev@.community.nospam> wrote in message
news:34051B1C-0F85-4F32-8A20-798B5F2FCD34@.microsoft.com...
> Hello,
> I have a customers table linked to a phones table.
> Is there a way to write an update query which will take the first & second
> phone number of every customer and put it into respective fields
> (strPhone1 &
> strPhone2) on the customer table, and dump any other phones into a memo
> field?
> Thanks,
> Lomas
>|||I *was* planning to have have different columns for the different phone
types. I just didn't want to make the question too complicated. :)
Do you still think it is better to completely normilize the tables? In that
case I would need to keep track of the current primary address for use in
display and reporting. I don't see much practicality in knowing a customer'
s
previous addresses. Either way, I would also split the phones table if I wa
s
going to keep it this way.
Here are the tables with the added columns for phone types:
tblFamilies: --
pkFamilyID
strLastName
strAddress1
strAddress2
strCity
strZip
strHomePhone1
strHomePhone2
strHomeFax
strFamilyNotes
tblCustomers: --
pkCustomerID
fkFamilyID
strTitle
strFirstName
strMiddleInitial
strPostTitle
strWorkPhone1
strWorkPhone2
strWorkFax
strMobilePhone
strPager
strVoiceMail
strCustomerNotes
tblPhones: --
pkPhoneID
fkPhoneTypeID
fkFamilyID
fkCustomerID (one of these is null - either family or customer)
strPhoneNumber
strExtension (I also want to combine the extension with the phone number)
tblPhoneTypes: --
pkPhoneTypeID
numOrder
strDescription
The primary key currently defines what makes the phone number first or secon
d.
This code, for example, retrives all home phone numbers:
SELECT tblFamilies.pkFamilyID, tblPhoneTypes.strDescription, CASE WHEN
tblPhones.strExtension IS NULL
THEN tblPhones.strPhoneNumber ELSE
tblPhones.strPhoneNumber + ' x' + tblPhones.strExtension END AS PhoneNumber
FROM tblPhones INNER JOIN
tblFamilies ON tblPhones.fkFamilyID =
tblFamilies.pkFamilyID LEFT OUTER JOIN
tblPhoneTypes ON tblPhones.fkPhoneTypeID =
tblPhoneTypes.pkPhoneTypeID
WHERE (tblPhoneTypes.strDescription = N'Home')
UNION
SELECT tblFamilies.pkFamilyID, tblPhoneTypes.strDescription, CASE WHEN
tblPhones.strExtension IS NULL
THEN tblPhones.strPhoneNumber ELSE
tblPhones.strPhoneNumber + ' x' + tblPhones.strExtension END AS PhoneNumber
FROM tblPhones INNER JOIN
tblCustomers ON tblPhones.fkCustomerID =
tblCustomers.pkCustomerID INNER JOIN
tblFamilies ON tblCustomers.fkFamilyID =
tblFamilies.pkFamilyID LEFT OUTER JOIN
tblPhoneTypes ON tblPhones.fkPhoneTypeID =
tblPhoneTypes.pkPhoneTypeID
WHERE (tblPhoneTypes.strDescription = N'Home')
I can add this kind of complexity to the statement after I know the coding
method.
Thanks,
Lomas
"Louis Davidson" wrote:

> This is exactly what he wants to avoid, though it is still not a good idea
> :)
> To answer your original question, you will have to define what data makes
a
> phone number first or second. If you go ahead with this change, I would a
t
> least suggest you have a column for home phone, one for mobile phone, etc.
> rather than phone1, phone2. This is really bad design. On the other hand
,
> limiting the user to only one phone number of a specific type is a valid
> design that doesn't leave the end user guessing what they are storing wher
e.
> Post your table structures and someone will help write the query..
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "JT" <someone@.microsoft.com> wrote in message
> news:u89fgLkqFHA.272@.TK2MSFTNGP15.phx.gbl...
>
>|||Interesting... Now I am thinking of normalizing the addresses and
denormalizing the phones... I still don't really see a point in keeping a
history of phone numbers.
What would be the best way to set up the query for retriveing the current
primary address in such a case?
As for the normalized phones table, I ran into a problem when a customer
asked me to export a list of them. He needed a column for each phone type
(Home1, Home2, HomeFax, etc.). How would I set up the query to put the firs
t
home number (it found) into the Home1 column, the second into Home2, etc.
What it there are (let's say) 15 of them? Also, how would I set up the quer
y
for reporting purposes. (I don't want every one to print when I print the
report.)
Thanks,
Lomas
"JT" wrote:

> Ask the users if they need a history of changes to a user's profile. If yo
u
> are shipping product to a customer's address, then knowing the address whe
re
> you actually shipped a product on a specific date would not only be useful
> but critical. Also, the marketing department may be interested in datamini
ng
> the changes in their customer's demographics. If the customers who generat
e
> the most sales are migrating to another geographical area, then that would
> be significant.
> "JT" <someone@.microsoft.com> wrote in message
> news:u89fgLkqFHA.272@.TK2MSFTNGP15.phx.gbl...
>
>|||What is is the customer's most recent address?
select top 1
Address
from
CustomerAddress
where
CustomerID = @.CustomerID
order by
StartDate desc
What was the customer's address on the date that the product was shipped?
select top 1
Address
from
CustomerAddress
where
CustomerID = @.CustomerID and
StartDate <= @.ShipDate
order by
StartDate desc
If you have a normalized database and want to return a crosstab query
(Home1,Home2,Cell1,Cell2,etc) for reporting purposes, you can use inline
sub-queries like below. However, this will perform better and is more
flexible when implemented on the client side using Crystal, Excel or
whatever your reporting tool happens to be.
select
CustomerName,
(select bottom top 1 Phone from CustomerPhone where CustomerID =
@.CustomerID and PhoneType='H' order by StartDate desc) as Home1,
(select bottom 1 Phone from (select bottom top 2 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='H' order by
StartDate desc) as x) as Home2,
(select bottom 1 Phone from (select bottom top 3 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='H' order by
StartDate desc) as x) as Home3,
(select bottom top 1 Phone from CustomerPhone where CustomerID =
@.CustomerID and PhoneType='C' order by StartDate desc) as Cell1,
(select bottom 1 Phone from (select bottom top 2 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='C' order by
StartDate desc) as x) as Cell2,
(select bottom 1 Phone from (select bottom top 3 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='C' order by
StartDate desc) as x) as Cell3
from
Customers
where
CustomerID = @.CustomerID
"optidev" <optidev@.community.nospam> wrote in message
news:A0E6085D-D6EE-42FC-BCC9-524FFFDEAF5A@.microsoft.com...
> Interesting... Now I am thinking of normalizing the addresses and
> denormalizing the phones... I still don't really see a point in keeping a
> history of phone numbers.
> What would be the best way to set up the query for retriveing the current
> primary address in such a case?
> As for the normalized phones table, I ran into a problem when a customer
> asked me to export a list of them. He needed a column for each phone type
> (Home1, Home2, HomeFax, etc.). How would I set up the query to put the
> first
> home number (it found) into the Home1 column, the second into Home2, etc.
> What it there are (let's say) 15 of them? Also, how would I set up the
> query
> for reporting purposes. (I don't want every one to print when I print the
> report.)
> Thanks,
> Lomas
>
> "JT" wrote:
>|||select bottom doesn't work
"optidev" wrote:
> Interesting... Now I am thinking of normalizing the addresses and
> denormalizing the phones... I still don't really see a point in keeping a
> history of phone numbers.
> What would be the best way to set up the query for retriveing the current
> primary address in such a case?
> As for the normalized phones table, I ran into a problem when a customer
> asked me to export a list of them. He needed a column for each phone type
> (Home1, Home2, HomeFax, etc.). How would I set up the query to put the fi
rst
> home number (it found) into the Home1 column, the second into Home2, etc.
> What it there are (let's say) 15 of them? Also, how would I set up the qu
ery
> for reporting purposes. (I don't want every one to print when I print the
> report.)
> Thanks,
> Lomas
>
> "JT" wrote:
>

Custom Stored Procs: Determining source table name

Hello all ...

I'm looking at writing some customized insert, update and delete stored procs for a replication target. For various reasons I would like to write a "one size fits all" custom stored proc for each of these tasks.

It looks like I can get the data values passed as parameters just fine.

I was wondering if there's a way to also pass the source schema and table name as parameters, or to determine these on the fly in my all purpose stored procs. Some replication products refer to these types of values as "tokens" that can be included in the replication data stream sent to the target.

I can adjust the source database replication publications, and article definitions, but I cannot modify the actual source database tables to include these as values in data columns. It is possible a view that contains these elements as strings might fly, but I was hoping to avoid cluttering the source database.

A handy trick or technique would be helpful!

Thanks!

DB

To answer your question, no the distribution agent does not and cannot pass the table name as a parameter. Since the custom procs are per article, it is expected the table name to be included in the proc.|||

OK, I'll work with that.

Thanks very much!

DB

Custom Report Item

Hello.
I need to write a CRI which will update a attribute in the RDL file.
Can anyone point me in the right direction.
This is my view I
will have a textbox and a button on the CRI.
While building a report through BI , the report author adds CRI.
enter a value in the text box and click ok.
Then the text in the textbox needs to be added to the RDL file.
Thanks!!
RadhikaHello,
Does any one have ideas.
Thanks!!
"Radhika" wrote:
> Hello.
> I need to write a CRI which will update a attribute in the RDL file.
> Can anyone point me in the right direction.
> This is my view I
> will have a textbox and a button on the CRI.
> While building a report through BI , the report author adds CRI.
> enter a value in the text box and click ok.
> Then the text in the textbox needs to be added to the RDL file.
>
> Thanks!!
> Radhika

Custom procs for replication

SQL Server 2000 Enterprise

Hello,

I have a test publication of two tables. Both tables are supposed to use a custom update stored procedure
(you know, "Replace UPDATE commands with this stored procedure call:")
When updating Table1 the system calls my custom stored stored procedure.

Here is a small excerpt captured by the Profiler when updating Table1:

exec usp_FNLC_MSupd_Table1 'Jun 24 2005 12:00:00:000AM', NULL, 100081142, 0x01

Looks good. I see that my custom update proc was used (usp_FNLC_MSupd_Table1).

Now, when updating Table2 (Table2 is pretty identical to Table1) Profiler sends this back and the update on the subscriber fails:

exec sp_executesql N'delete from "Table2" where "Dates3ID" = @.P1 insert into "Table2" values (@.P2, @.P3) ', N'@.P1 int,@.P2 int,@.P3 datetime', 8, 8, 'Mar 24 2006 12:00:00:000AM'

Then..

exec sp_executesql N'insert into "Table1" values (@.P1, @.P2) ', N'@.P1 int,@.P2 datetime', 8, 'Mar 24 2006 12:00:00:000AM'
Go

This is what the distribution agent throws back:

Insert Error: Column name or number of supplied values does not match table definition.
(Source: DFBPBSO (Data source); Error number: 213)

So, why isn't the article Table2 using my custom update proc? All I do is updates, no deletes or inserts. No matter what I do for this article, it will not use the custom update proc.

I need help, please. Thank you in advance,
L

When you update a column that's part of a unique constraint, it will be replicated as delete/insert. I'm guessing you're updating a PK column on Table2?

You can find more info on deferred udates here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;238254

|||Yes, thanks, that is what is was. I fixed it. Its working now.

Custom Database Roles -- Insert

I am trying to set up a Custom Database Role that allows the user to
select, update, insert and delete data. I created the role by
combining the built-in roles of db_datareader and db_datawriter.
However when I logged with the custom role I was not able to insert a
new record.
When I then went in and assigned Insert rights to the table I was
trying to insert to, I was able to do it. But if I go that route I
end up having to set permissions on every table in the database. I
just want to grant these rights to ALL the tables.
Is there a straighforward way to do this and why didn't adding
db_datawriter to the role definition accomplish this?
Thanks.
StevenHello,
See if there is any Deny permissions set for the user for the specific table
you tried to insert.
Thanks
Hari
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1180227296.829693.158290@.i13g2000prf.googlegroups.com...
>I am trying to set up a Custom Database Role that allows the user to
> select, update, insert and delete data. I created the role by
> combining the built-in roles of db_datareader and db_datawriter.
> However when I logged with the custom role I was not able to insert a
> new record.
> When I then went in and assigned Insert rights to the table I was
> trying to insert to, I was able to do it. But if I go that route I
> end up having to set permissions on every table in the database. I
> just want to grant these rights to ALL the tables.
> Is there a straighforward way to do this and why didn't adding
> db_datawriter to the role definition accomplish this?
> Thanks.
> Steven
>|||ExcelMan (sfarkas@.sjfcg.com) writes:
> I am trying to set up a Custom Database Role that allows the user to
> select, update, insert and delete data. I created the role by
> combining the built-in roles of db_datareader and db_datawriter.
> However when I logged with the custom role I was not able to insert a
> new record.
> When I then went in and assigned Insert rights to the table I was
> trying to insert to, I was able to do it. But if I go that route I
> end up having to set permissions on every table in the database. I
> just want to grant these rights to ALL the tables.
> Is there a straighforward way to do this and why didn't adding
> db_datawriter to the role definition accomplish this?
As Hari suggested, a DENY permission may be the problem. Here is a
script for SQL 2000 that demonstrates that what you want to do really
works. By the way, if you are using SQL 2005, you are better off granting
access on schema or database level.
USE tempdb
go
CREATE DATABASE rolle
EXEC sp_addlogin rollerull, '12'
go
USE rolle
go
CREATE TABLE mulle (a int NOT NULL, x sysname DEFAULT USER)
go
EXEC sp_addrole rolle
EXEC sp_addrolemember 'db_datawriter', 'rolle'
EXEC sp_addrolemember 'db_datareader', 'rolle'
EXEC sp_grantdbaccess rollerull
EXEC sp_addrolemember rolle, rollerull
go
SETUSER 'rollerull'
go
INSERT mulle (a) VALUES (12)
go
SETUSER
go
SELECT * FROM mulle
go
use tempdb
go
DROP DATABASE rolle
EXEC sp_droplogin rollerull
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Custom Data Flow Component Column Mapping Question

Hi,

I'm having my first go at developing a destination adapter which will send data to an update Web Service.

I've got some rather big gaps in my understanding. I've been following the various samples I've found on the net and have validated my mapping and picked up all the available column names and datatypes which are appearing in the Input and Output Properties tab of the Advanced Editor but I only have a tab for "Input Columns" and not "Column Mappings".

Which method defines the availble columns for the user to map?

Let me know if I haven't given enough information.

cheers

sorry - I had searched already but somehow missed this post which answered my question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=239126&SiteID=1

I was having the same confusion about input and output, thinking that because i was creating a *destination* adapter, that I should be creating outputs not inputs [if that makes sense].

Custom Conflict Resolution Question

Hello,
I have to setup custom conflict resolution so that the
most recent update wins using a column 'lasteditdate' in a
a merge replication scenario and have about 150 tables to
do this on. Do I have to configure each table/article
individually or can I do all necessary tables in one shot?
Any help would be greatly appreciated!
Thanks in advance
Gene,
each one has to be done separately. However, you could create the
publication in SQLDMO then loop round the tables if you want a more
efficient way of doing it.
HTH,
Paul Ibison

cursors problem

hi i am getting that message to update field value into table
Msg 16933, Level 16, State 1, Procedure RmDupValWSWCode, Line 39
The cursor does not include the table being modified or the table is not
updatable through the cursor.
I have two databases with same tables, in one database it work on that table
but in another database its not working on the same table with the same
data......
why cursor behave like thatHello!
Could you post your code?
Thanks!|||Since you haven't posted the code. I am guessing :)
are you using the two part name where ever you refer to the table.
I mean refer to all the table names as
dbo.table1
or whichever schema you are using.
Hope this helps.|||Also, make sure the user running the stored procedure has access to the
tables.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:2919D9C0-492D-4582-89B0-BEDABA2B0E6E@.microsoft.com...
> Since you haven't posted the code. I am guessing :)
> are you using the two part name where ever you refer to the table.
> I mean refer to all the table names as
> dbo.table1
> or whichever schema you are using.
> Hope this helps.|||Hey Jim..
long time no clashes.. was missing you :)
"Jim Underwood" wrote:

> Also, make sure the user running the stored procedure has access to the
> tables.
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:2919D9C0-492D-4582-89B0-BEDABA2B0E6E@.microsoft.com...
>
>|||Lol. Yeah, you missed an entire day last w. All day long I was looking
for some posts from you. You usually have a few creative and thought
provoking solutions. I think today's is the negative flag * 2 +1 to get
either a positive or negative number. I had to get some caffeine before I
could figure it out.
Actually, I was hoping to see if you had a mathematical formula to solve the
matrix (Matriz) problem posted yesterday. The best I could come up with was
a mapping table. I couldn't seem to find a mathematical approach to the
problem, so I went with simple brute force.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:6FEE9DDC-CEBC-46B8-8AE1-17E1CCEE47A1@.microsoft.com...
> Hey Jim..
> long time no clashes.. was missing you :)
> "Jim Underwood" wrote:
>|||Gee...Thanks for the complments..
Well network was down yesterday.. and didn't see the post on matrix.. will
look into it now :)
"Jim Underwood" wrote:

> Lol. Yeah, you missed an entire day last w. All day long I was lookin
g
> for some posts from you. You usually have a few creative and thought
> provoking solutions. I think today's is the negative flag * 2 +1 to get
> either a positive or negative number. I had to get some caffeine before I
> could figure it out.
> Actually, I was hoping to see if you had a mathematical formula to solve t
he
> matrix (Matriz) problem posted yesterday. The best I could come up with w
as
> a mapping table. I couldn't seem to find a mathematical approach to the
> problem, so I went with simple brute force.
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:6FEE9DDC-CEBC-46B8-8AE1-17E1CCEE47A1@.microsoft.com...
>
>

Cursors and Temp Table : in SQL Server

1. Can I pass Parameter to a Cursor ?
2. Is it must to declare the Holding variable for a Cursor above it
before opening ?
3. Can a temp table be update ?1) Yes (see example below)
2) No (see example below)
Cursor example:
use pubs
DECLARE @.State char(2)
set @.State = 'UT'
PRINT '-- Utah Authors report --'
DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = @.State
ORDER BY au_id
OPEN authors_cursor
DECLARE @.au_id varchar(11), @.au_fname varchar(20), @.au_lname varchar(40),
@.message varchar(80), @.title varchar(80)
FETCH NEXT FROM authors_cursor
INTO @.au_id, @.au_fname, @.au_lname
print @.au_id
CLOSE authors_cursor
DEALLOCATE authors_cursor
3) Yes (see example below)
Create table #test (a char(1))
insert into #test values ('a')
select * from #test
update #test
set a = 'b'
select * from #test
drop table #test
"akpatelrs@.googlemail.com" wrote:

> 1. Can I pass Parameter to a Cursor ?
> 2. Is it must to declare the Holding variable for a Cursor above it
> before opening ?
> 3. Can a temp table be update ?
>