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
>
>
>

Customize Prompt Message for Stored Procudure

Hello all,
I have a stored procedure that prompts the user for beginning date and
ending date to run a monthly report. The prompt says
Enter_Beginning_Date and Enter_Ending_Date. I want the prompt to say
Enter Beginning Date (Example:1-1-2003) or something like that. Is
there a way to do this?

CREATE PROCEDURE dbo.MonthlyReport(@.Enter_Beginning_Date datetime,
@.Enter_Ending_Date datetime)
AS SELECT incident, @.Enter_Beginning_Date AS BeginningDate,
@.Enter_Ending_Date AS EndingDate, COUNT(*) AS Occurances
FROM dbo.Incident
WHERE (DateOccured BETWEEN @.Enter_Beginning_Date AND
@.Enter_Ending_Date)
GROUP BY incident
GO"ndn_24_7" <ndn_24_7@.yahoo.com> wrote in message
news:1105983132.532065.27990@.c13g2000cwb.googlegro ups.com...
> Hello all,
> I have a stored procedure that prompts the user for beginning date and
> ending date to run a monthly report. The prompt says
> Enter_Beginning_Date and Enter_Ending_Date. I want the prompt to say
> Enter Beginning Date (Example:1-1-2003) or something like that. Is
> there a way to do this?
> CREATE PROCEDURE dbo.MonthlyReport(@.Enter_Beginning_Date datetime,
> @.Enter_Ending_Date datetime)
> AS SELECT incident, @.Enter_Beginning_Date AS BeginningDate,
> @.Enter_Ending_Date AS EndingDate, COUNT(*) AS Occurances
> FROM dbo.Incident
> WHERE (DateOccured BETWEEN @.Enter_Beginning_Date AND
> @.Enter_Ending_Date)
> GROUP BY incident
> GO

MSSQL is purely a server, so it doesn't have any idea about GUIs or
prompts - if you want to present a more user-friendly description of the two
parameters, then you would have to do that in the front-end application
where the users select the dates.

One possible approach would be to add an extended property to the two
parameters which has the description in it, then retrieve that from the
front end when you display the input screen. See "Using Extended Properties
on Database Objects" in Books Online for more details. But I don't know if
that would be a suitable solution for your toolset and design.

Simon|||I'm sorry
I should have been more discriptive. My program has a Access 2000 front
end and a SQL 2000 server backend. I have a button that the user clicks
that brings up the prompt window for the stored proc. So would I do
this on the Access front end, Where would Icustomize this message?|||Not a clue how you are prompting the user thru Stored Procedure. May be
you missed out some valuable info on the post........!|||ndn_24_7 (ndn_24_7@.yahoo.com) writes:
> I should have been more discriptive. My program has a Access 2000 front
> end and a SQL 2000 server backend. I have a button that the user clicks
> that brings up the prompt window for the stored proc. So would I do
> this on the Access front end, Where would Icustomize this message?

Sounds like you should try an Access newsgroup. It is possible that
you can use extended properties for this, but I have no knowledge
what Access makes use of. So try comp.databases.ms-access where the
expertise for this question might hang out.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||As far I understood,

You have a stored procedure that accepts parameters and you have an
Access front end that accepts input parameters thru prompts and pass
them to the backend stored procedure... Am I correct?

If so, what you are doing is correct so far. You need to find out how
to call a stored procedure thru access and once you know, all you need
to do is, thru access, get those inputs (you may display anyway you
need - that is irrelevent to the backend procedure...) and pass them as
parameters to the backend stored proc.. May be an access guru can show
some light on how to accomplish this..!|||"ndn_24_7" <ndn_24_7@.yahoo.com> wrote in message
news:1105990021.951317.205140@.c13g2000cwb.googlegr oups.com...
> I'm sorry
> I should have been more discriptive. My program has a Access 2000 front
> end and a SQL 2000 server backend. I have a button that the user clicks
> that brings up the prompt window for the stored proc. So would I do
> this on the Access front end, Where would Icustomize this message?

I suggest you want a form that the user enters the dates in and then clicks
the button to run the stored proc.
I'll assume you know a little about VB code.
Otherwise, you got a steep learning curve ahead mate.

You probably want to validate the fields using isdate()

Access now uses ADO, so the code involves using an ado connection and
command.
I found pretty much the below code by using google to search the access
newsgroup.
Not tested it and I had to add the execute line, so this is just to get you
started.
BTW You'll want to get used to doing such searches if you are new to this
lark.
I suggest also take a look at each of the bits in turn and read up using
msdn so you get a better understanding what you're up to.
I have deliberately not changed the parameter type to date and input because
you'd learn stuff all if I just gave you the code.

There's a couple gotchas with datetime. Inside access some bits want this
delimited by # but not with ado. Remember also the time bit of a date is
likely there. Not such a problem with > or < but throw that to the back of
your mind for later.

Anyhow.
You run the thing by using the execute method of the command.
There's a parameter collection associated with a command you add the values
to:

'start untested snippet
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "stored procedurename"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("@.CompanyID", adInteger, adParamOutput _
, , forms!yourformname!text1.Value)
cmd.Parameters.Append prm

cmd.execute

set prm = Nothing
Set cmd = Nothing

' end snippet

HTH

--
Regards,
Andy O'Neill

Custome code not working in Report manager

Hi all,
I have Written the Custom code to compare two different dates and
show a message Box depending on their values .It was Working Fine in
preview tab.But it is not working when i deploy to web.Only rhe message
box part is not working in Web
I do have the Custom code like this
Public Function Errorbox(ByVal s as Date,ByVal s1 as Date) As String
If s>s1 Then
System.Windows.Forms.MessageBox.Show("From Date is greater than
To Date,Please Click Ok" )
End if
Return "a"
End Function
and i gave a text box (Just a text box)
To call this custome code and i gave the expression
=Code.Errorbox(Parameters!FROM_DATE.Value,Parameters!TO_DATE.Value)
i added this Assembly also
"System.Windows.Forms"
Its not working in Report manager(web) .Can any body help me out to
knowwhat would be the Problem.i am very new to this VB Coding
Thank you
Raj Deep.AYou need to add the assembly to the following folders:
C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin
Hope this helps.
"RajDeep" wrote:
> Hi all,
> I have Written the Custom code to compare two different dates and
> show a message Box depending on their values .It was Working Fine in
> preview tab.But it is not working when i deploy to web.Only rhe message
> box part is not working in Web
> I do have the Custom code like this
> Public Function Errorbox(ByVal s as Date,ByVal s1 as Date) As String
> If s>s1 Then
> System.Windows.Forms.MessageBox.Show("From Date is greater than
> To Date,Please Click Ok" )
> End if
> Return "a"
> End Function
> and i gave a text box (Just a text box)
> To call this custome code and i gave the expression
> =Code.Errorbox(Parameters!FROM_DATE.Value,Parameters!TO_DATE.Value)
> i added this Assembly also
> "System.Windows.Forms"
> Its not working in Report manager(web) .Can any body help me out to
> knowwhat would be the Problem.i am very new to this VB Coding
> Thank you
> Raj Deep.A
>

Custom Task deployment

Hi all,

I'm having a nightmare trying to test my custom task.

I get the following error message when trying to add the task from the toolbox...

Cannot create a task with the name "myTask, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b8e53511af163eb7". Verify that the name is correct.
(Package)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Executables.Add(String moniker)
at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)

Any ideas?

Cheers.

Is the task registered in the GAC? Also, it has to be copied to your Program Files\Microsoft SQL Server\90\DTS\Tasks folder (sounds like you have already done that).

Have you signed the component? Again, sounds like you have already done this, just covering the bases.

|||

Hi,

thanks for your response, but yeah it turns up in the toolbox so I had to sign it and GAC it to get to that stage.

I've done everything that is supposed to be required to get it working so I'm just out of ideas now.

Cheers.

|||

Sorted it now, turns out you have to GAC all assemblies referenced by the task not just the task itself.

Silly me.

Custom Procedure Replication Error with Oracle Subscriber

The custom procedure was created successfully on Oracle.
The following is the error message that is recieved. What is really odd is
why SQL Server is sending a select of a stored procedure name? Any ideas
as to what is going on here?
SQL> select * from sp_upd_crms_repl where 0 = 1;
select * from sp_upd_crms_repl where 0 = 1
*
ERROR at line 1:
ORA-04044: procedure, function, package, or type is not allowed here
Oracle publishers only replicate tables. It looks like here Oracle is
treating the stored procedure as a table, and is trying to return all the
data as opposed to only the columns (where 1=1).
Oracle publications do add the following objects on the Oracle server:
http://msdn2.microsoft.com/en-us/library/ms152557.aspx
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Michael Meyer" <michael_meyer@.csgsystems.com> wrote in message
news:uoQ%232bjwHHA.4800@.TK2MSFTNGP05.phx.gbl...
> The custom procedure was created successfully on Oracle.
> The following is the error message that is recieved. What is really odd
> is why SQL Server is sending a select of a stored procedure name? Any
> ideas as to what is going on here?
> SQL> select * from sp_upd_crms_repl where 0 = 1;
> select * from sp_upd_crms_repl where 0 = 1
> *
> ERROR at line 1:
> ORA-04044: procedure, function, package, or type is not allowed here
>
>
|||The Oracle side is actually the subscriber. It does appear that it is
treating this as a table instead of a stored procedure. I can't see
anything wrong with the article. The article has the insert and update
commands defined as CALL stored procedures and the actual command names are
the correct stored procedure names.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uIyD82kwHHA.600@.TK2MSFTNGP05.phx.gbl...
> Oracle publishers only replicate tables. It looks like here Oracle is
> treating the stored procedure as a table, and is trying to return all the
> data as opposed to only the columns (where 1=1).
> Oracle publications do add the following objects on the Oracle server:
> http://msdn2.microsoft.com/en-us/library/ms152557.aspx
>
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Michael Meyer" <michael_meyer@.csgsystems.com> wrote in message
> news:uoQ%232bjwHHA.4800@.TK2MSFTNGP05.phx.gbl...
>
|||Configure the publication to use SQL Statements instead of the stored
procedures. In the Publication Properties, Articles tab, Select the
properties of the article and Insert, Update and delete deliver formats
select insert statements.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Michael Meyer" <michael_meyer@.csgsystems.com> wrote in message
news:OSxakFlwHHA.484@.TK2MSFTNGP06.phx.gbl...
> The Oracle side is actually the subscriber. It does appear that it is
> treating this as a table instead of a stored procedure. I can't see
> anything wrong with the article. The article has the insert and update
> commands defined as CALL stored procedures and the actual command names
> are the correct stored procedure names.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uIyD82kwHHA.600@.TK2MSFTNGP05.phx.gbl...
>
|||The reason we were drawn to the custom procedure is that we have long column
names > 30 characters in SQL Server and Oracle can only handle up to 30.
We are trying to handle the mapping in the custom stored procedure in Oracle
since there is no way to do this in SQL Server that we could come up with.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O$MTbRmwHHA.4076@.TK2MSFTNGP06.phx.gbl...
> Configure the publication to use SQL Statements instead of the stored
> procedures. In the Publication Properties, Articles tab, Select the
> properties of the article and Insert, Update and delete deliver formats
> select insert statements.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Michael Meyer" <michael_meyer@.csgsystems.com> wrote in message
> news:OSxakFlwHHA.484@.TK2MSFTNGP06.phx.gbl...
>

Custom Error Message for Check Constraints

Is it possible to make Sql Server throw a custom error message when a
CHECK Constraint fails please?Hi
No. The message and the behavior can not be changed.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John Smith" <postmaster@.sumanthcp.plus.com> wrote in message
news:1135092443.213942.71180@.f14g2000cwb.googlegroups.com...
> Is it possible to make Sql Server throw a custom error message when a
> CHECK Constraint fails please?
>

Custom Destination Component Logging - wrote 0 rows

I wrote a custom destination component. Everything works fine, except there is a logging message that is displayed that I cannot get rid of or correct. Here is the end of the output of a package containing my component:

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x0 at Data Flow Task, MyDestination: Inserted 40315 rows into C:\temp\file.txt
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "MyDestination" (9)" wrote 0 rows.
SSIS package "Package.dtsx" finished: Success.

I inserted a custom information message that contains the correct number of rows written by the component. I would like to either get rid of the last message "... wrote 0 rows", or figure out what to set to put the correct number of rows into that message.

This message seems to happen in the Cleanup phase. It appears whether I override the Cleanup method of the Pipeline component and do nothing, or not. Any ideas?

public override void Cleanup()

{

ComponentMetaData.FireInformation(0, ComponentMetaData.Name,

"Inserted " + m_rowCount.ToString() + " rows into " + m_fileName,

"", 0, ref m_cancel);

base.Cleanup(); // or not

}

This message comes from the engine and you can not stop it from occuring. To set it correctly you need to call the IncrementPipelinePerfCounters(counter, difference) method on the IDTSComponentMetaData90 interface.

The counter values are:

RowsRead: 101

RowsWritten: 103

BlobBytesRead: 116

BlobBytesWritten: 118

The difference value is the amount to increment the counter by.

Thanks,

Matt

|||

Thanks. That did the trick.

Apparently there are constants defined for the counter types, but I'm not sure where they are.

Here's the official help page:

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.pipeline.wrapper.idtscomponentmetadata90.incrementpipelineperfcounter.aspx

|||

The constants for the counter types are listed in the Remarks section of the BOL page for which you pasted the link. Are those not what you were looking for?

-Doug

|||

Those constants work. I was hoping to be able to access the constants by an actual constant, instead of having to hard-code the number.

I would rather have code that looks like this:

ComponentMetaData.IncrementPipelinePerfCounter(DTS_PIPELINE_ROWS_WRITTEN, m_rowCount);

than this:

ComponentMetaData.IncrementPipelinePerfCounter(103, m_rowCount);

For now I just created a local constant with the above name and that works fine.

private const uint DTS_PIPELINE_ROWS_WRITTEN = 103;

Maybe those constants are exposed somewhere, but I could not figure out where.

|||

Excuse me for missing the point.

I suspect that these counter constants are defined in the native pipeline engine and not exposed in any of the managed classes...that would explain why this managed method expects an integer value and not an enum value. You could create your own enumeration for this purpose.

-Doug

custom code and messagebox popup

Hi at all,
I have custom code written the to show a message Box.
It was working fine in DotNet2005 Development enviroment preview tab.
But it is't working when i deploy this report to the web.
Please use a reference to
System.Windows.Forms, Version=2.0.0.0
Public Function Test() As String
System.Windows.Forms.MessageBox.Show("Hello")
' Or
Msgbox "Hello2"
Return "OK"
End Function
Can anyone help me
martinYou can't use System.Windows.Forms for a web page. Please see the following
URL: http://www.freevbcode.com/ShowCode.asp?ID=5918. If you don't want to
go there, use the following function to stream out a message box
Public Sub ASPNET_MsgBox(ByVal Message As String)
System.Web.HttpContext.Current.Response.Write("<SCRIPT
LANGUAGE=""JavaScript"">" & vbCrLf)
System.Web.HttpContext.Current.Response.Write("alert(""" & Message &
""")" & vbCrLf)
System.Web.HttpContext.Current.Response.Write("</SCRIPT>")
End Sub
--
================Living proof that 95% of programmers are idiots.
"Martin Jau" wrote:
> Hi at all,
> I have custom code written the to show a message Box.
> It was working fine in DotNet2005 Development enviroment preview tab.
> But it is't working when i deploy this report to the web.
> Please use a reference to
> System.Windows.Forms, Version=2.0.0.0
> Public Function Test() As String
> System.Windows.Forms.MessageBox.Show("Hello")
> ' Or
> Msgbox "Hello2"
> Return "OK"
> End Function
> Can anyone help me
> martin
>
>

Custom Code Access Error

I've created a function Period in custom code block. Then I put expression
=Code!Period()
in the textbox. Then I got this message:
The value expression for the textbox â'textbox6â' contains an error: [BC30367]
Class 'ReportExprHostImpl.CustomCodeProxy' cannot be indexed because it has
no default property.
Am I doing something wrong?
--
Thanks in advance,
IDcode.period not code!period
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"exkievan" <exkievan@.discussions.microsoft.com> wrote in message
news:58940691-9E20-49AE-82E8-4F8BE0092E9C@.microsoft.com...
> I've created a function Period in custom code block. Then I put expression
> =Code!Period()
> in the textbox. Then I got this message:
> The value expression for the textbox 'textbox6' contains an error:
[BC30367]
> Class 'ReportExprHostImpl.CustomCodeProxy' cannot be indexed because it
has
> no default property.
> Am I doing something wrong?
> --
> Thanks in advance,
> ID

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 question

I'll greatly appreciate any help with the error message bellow. What I am
trying to do is first I am getting a customer type and nr of members of that
type. Next I list first 5 customers and if there are more than 5 I want a
message to appear indicating how many more records are remaining and the las
t
record. Here is what I get:
3 J, Total nr. of records 5
1 R, M
2 H, S
3 S, S
4 W, G
5 W, P
4 O, Total nr. of records 1
1 V, D
5 R, Total nr. of records 17
1 M, M
2 V, J
3 R, R
4 D, K
5 S, M
Server: Msg 16911, Level 16, State 1, Line 39
fetch: The fetch type last cannot be used with forward only cursors.
.. There are 12 more records
-->> The last record being S, S
My code looks something like this:
declare @.MemID varchar(10)
declare @.total int
declare @.RowNum int
declare @.NamesNum int
declare @.RowNum1 int
declare @.fname varchar(20)
declare @.lname varchar(20)
declare MemList cursor for
select benefit, count(*) total from cust where branch='xxx' group by benefit
OPEN MemList
FETCH NEXT FROM MemList
INTO @.MemID, @.total
set @.RowNum = 0
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.RowNum = @.RowNum + 1
print cast(@.RowNum as varchar) + ' ' + LEFT(@.MemID, 1) + ', Total nr. of
records ' + cast(@.total as varchar)
---
declare namesList cursor
FOR select surname, name from cust where branch='xxx' and benefit=@.MemID
OPEN namesList
FETCH NEXT FROM namesList
INTO @.lname, @.fname
set @.RowNum1 = 0
WHILE @.@.FETCH_STATUS = 0 AND @.RowNum1 < 5
BEGIN
set @.RowNum1 = @.RowNum1 + 1
print ' ' + cast(@.RowNum1 as varchar) + ' ' + LEFT(@.lname,1) + ', ' +
@.fname
FETCH NEXT FROM namesList
INTO @.lname, @.fname
END
IF @.total >= 6
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH LAST FROM namesList
INTO @.lname, @.fname
PRINT ' ... There are ' + ' ' + cast((@.total - 5) as varchar) + ' more
records'
PRINT ' -->> The last record being ' + LEFT(@.lname, 1) + ', ' + @.fname
END
CLOSE namesList
DEALLOCATE namesList
---
FETCH NEXT FROM MemList
INTO @.MemID, @.total
END
CLOSE MemList
DEALLOCATE MemList
Thanks for your help!Hi,
In Order to Use the LAST function you have to declare the Cursor as a
Scroll Cursor.
Please see code below - take note in a slight modification to stop the
Cursor repeating itself.
HTH
Barry
SQL CODE:
declare @.MemID varchar(10)
declare @.total int
declare @.RowNum int
declare @.NamesNum int
declare @.RowNum1 int
declare @.fname varchar(20)
declare @.lname varchar(20)
declare MemList cursor for
select benefit, count(*) total from cust where branch='xxx' group by
benefit
OPEN MemList
FETCH NEXT FROM MemList
INTO @.MemID, @.total
set @.RowNum = 0
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.RowNum = @.RowNum + 1
print cast(@.RowNum as varchar) + ' ' + LEFT(@.MemID, 1) + ', Total
nr. of records ' + cast(@.total as varchar)
---
declare namesList scroll cursor -- Declare the Cursor as a Scroll
Cursor
FOR select surname, name from cust where branch='xxx' and
benefit=@.MemID
OPEN namesList
FETCH NEXT FROM namesList
INTO @.lname, @.fname
set @.RowNum1 = 0
WHILE @.@.FETCH_STATUS = 0 AND @.RowNum1 < 5
BEGIN
set @.RowNum1 = @.RowNum1 + 1
print ' ' + cast(@.RowNum1 as varchar) + ' ' + LEFT(@.lname,1) +
', ' +
@.fname
FETCH NEXT FROM namesList
INTO @.lname, @.fname
END
--IF @.total >= 6 -- Remove this from here...
WHILE @.@.FETCH_STATUS = 0 And @.Total >= 6 ... and put it here...
BEGIN
FETCH LAST FROM namesList
INTO @.lname, @.fname
PRINT ' ... There are ' + ' ' + cast((@.total - 5) as varchar) + ' more
records'
PRINT ' -->> The last record being ' + LEFT(@.lname, 1) + ', ' +
@.fname
Set @.total = 0 --Set The Total to Zero so this loop finishes
END
CLOSE namesList
DEALLOCATE namesList
---
FETCH NEXT FROM MemList
INTO @.MemID, @.total
END
CLOSE MemList
DEALLOCATE MemList|||Why use a cursor to do all that formatting rather than just output a
query? Have you looked up the CUBE / ROLLUP operator in Books Online?
It would help you with the total counts.
If you want help to write a query instead of a cursor then please post
DDL and sample data. Cursors are rarely a good idea and surely not for
this - there really are much better ways to write reports.
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||hehe!
I was going to maybe post an example to use rather than a cursor -- but
I haven't really got the time!
And without any DDL - I definitely don't have time... ;-)
Barry|||This could be close to what you want but it is untested. It should give
you the first 5 customers per benefit. I've no way of knowing what you
mean by "first" and "last" so I've assumed alphabetical order by name.
You didn't specify ORDER BY so the order you will get from your cursor
is undefined and may be unreliable.
SELECT C.benefit, C.surname, C.name, B.row_cnt, B.last_cust
FROM cust AS C,
(SELECT benefit, COUNT(*) AS row_cnt,
MAX(surname+','+name) AS last_cust
FROM cust
GROUP BY benefit) AS B
WHERE branch='xxx'
AND 5 <=
(SELECT COUNT(*)
FROM cust
WHERE surname+','+name <
C.surname+','+C.name)
AND B.benefit = C.benefit
ORDER BY C.benefit, C.surname, C.name ;
The pretty formatting you can do at the front end or in a reporting
tool.
Hope this helps.
David Portas
SQL Server MVP
--|||Correction. Change "<=" to ">="
David Portas
SQL Server MVP
--