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

custom system proc

Hello, I would like to create a stored procedure, which would manipulate dat
a
in different databases, where all my databases have the same structure.
I would expect , when this proc is called in DB1 to use tables from DB1,
when called in DB2 .. tables from DB2 .. and so on.
However, when I created my sample stored proc in master..
create proc sp_sample as select * from table1
and then I tried to call this proc in DB1.. even that DB1 had also table1 ,
stored procedure was selecting data from master database. I was expecting it
to use DB1..table1.
I hope I am clear on what I am going to achieve, I would like to have one
copy for each of my stored procedures, stored in master database instead of
10' copies stored across all different databases.
I would appreciate any tips on how this could be done.
LucjanThere's no supported or documented way to achieve what you want to do. Sugge
sted method is to have
the same proc in all databases and use some sw to manage versions. You *can*
achieve what you want
by marking the proc as a system proc using sp_MS_marksystemobject (Google fo
r usage), but again, it
is not supported or documented.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lucjan" <Lucjan@.discussions.microsoft.com> wrote in message
news:BB44926A-3624-4F56-B47C-299338E2F222@.microsoft.com...
> Hello, I would like to create a stored procedure, which would manipulate d
ata
> in different databases, where all my databases have the same structure.
> I would expect , when this proc is called in DB1 to use tables from DB1,
> when called in DB2 .. tables from DB2 .. and so on.
> However, when I created my sample stored proc in master..
> create proc sp_sample as select * from table1
> and then I tried to call this proc in DB1.. even that DB1 had also table1
,
> stored procedure was selecting data from master database. I was expecting
it
> to use DB1..table1.
> I hope I am clear on what I am going to achieve, I would like to have one
> copy for each of my stored procedures, stored in master database instead
of
> 10' copies stored across all different databases.
> I would appreciate any tips on how this could be done.
> Lucjan
>|||The supported behavior of special procedures (sp_ in master) is to resolve
only system tables in the current database, but user tables in master.
There's an undocumented "feature" that causes the proc to resolve user
tables in the current database as well. To achieve this, you run:
EXEC dbo.sp_MS_marksystemobject 'dbo.sp_procname'
Tough I'd be careful from relying on such undocumented behavior in
production systems. You never know when support for such a "feature" will be
dropped.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Lucjan" <Lucjan@.discussions.microsoft.com> wrote in message
news:BB44926A-3624-4F56-B47C-299338E2F222@.microsoft.com...
> Hello, I would like to create a stored procedure, which would manipulate
> data
> in different databases, where all my databases have the same structure.
> I would expect , when this proc is called in DB1 to use tables from DB1,
> when called in DB2 .. tables from DB2 .. and so on.
> However, when I created my sample stored proc in master..
> create proc sp_sample as select * from table1
> and then I tried to call this proc in DB1.. even that DB1 had also table1
> ,
> stored procedure was selecting data from master database. I was expecting
> it
> to use DB1..table1.
> I hope I am clear on what I am going to achieve, I would like to have one
> copy for each of my stored procedures, stored in master database instead
> of
> 10' copies stored across all different databases.
> I would appreciate any tips on how this could be done.
> Lucjan
>|||Lucjan (Lucjan@.discussions.microsoft.com) writes:
> Hello, I would like to create a stored procedure, which would manipulate
> data in different databases, where all my databases have the same
> structure.
> I would expect , when this proc is called in DB1 to use tables from DB1,
> when called in DB2 .. tables from DB2 .. and so on.
> However, when I created my sample stored proc in master..
> create proc sp_sample as select * from table1
> and then I tried to call this proc in DB1.. even that DB1 had also
> table1 , stored procedure was selecting data from master database. I was
> expecting it to use DB1..table1.
> I hope I am clear on what I am going to achieve, I would like to have
> one copy for each of my stored procedures, stored in master database
> instead of 10' copies stored across all different databases.
You can do this in SQL 2000, but it is not supported. And I don't think
you can do it at all in SQL 2005, since there is a radical change how
stored procedures, system tables etc are stored in SQL 2005.
Keep your code under version control and write a script that can update
one or more databases. The script can be Perl, VB, VBscript or even a
BAT file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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

Hi, I've tried to develop custom function for AS 2005, written in C#, and registered as database assembly

Code snippet is here

public string MySampleMethod()

{

AdomdCommand command = new AdomdCommand();

command.CommandText = string.Format("with member A AS 'username' select A on 0 FROM MyCube");

return (string)command.ExecuteScalar();

}

Error that I've got was "XML for Analysis parser: The input query is not in the language specified in the Dialect property for this request."

When I execute the same query from SQL Management Studio, everything works OK.

What I did wrong and how it can be resolved?

Thanks in advance

Borko

Hi Borko,

You can't use AS stored procs to execute queries on the same connection as you're using to call the proc, unfortunately. You can open a new connection within the proc but that may or may not be particularly useful to you - here's an example of how to do this:

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.AnalysisServices.AdomdServer;

using Microsoft.AnalysisServices.AdomdClient;

namespace MDXTests

{

public class RunMDXQueries

{

public static Microsoft.AnalysisServices.AdomdClient.AdomdDataReader RunAQuery()

{

Microsoft.AnalysisServices.AdomdClient.AdomdCommand c = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand("select measures.members on 0 from [adventure works]");

AdomdConnection conn = new AdomdConnection(@."Data Source=localhost\sh; Provider=msolap.3; initial catalog=adventure works dw");

conn.Open();

c.Connection = conn;

return c.ExecuteReader();

}

}

}

You need to set security permissions to 'unrestricted' to get this to work; you can then call this from SQLMS using the following:

CALL SPSRUNNINGQUERIES.RUNAQUERY()

HTH,

Chris

|||

Chris,

information that is not possible to execute queries on the same connection as connection used to call the proc is very usefull.

Thank you very much.

Borko

Custom sort records in a stored proc

Is there any way to modify this proc so that:
If it recieves a non-negative centre_id the data is ordered by
centre_name but with the centre whose id was passed, being first in
the the list.
If -1 is passed then just order by centre_name.
thanks.
CREATE PROCEDURE [dbo].[get_centres]
@.centre_id smallint
AS
SELECT centre_id, centre_name FROM tbl_centre
ORDER BY CASE
WHEN centre_id= @.centre_id Then 0
WHEN centre_id=-1 THEN centre_name
ELSE centre_id End
GOSELECT centre_id, centre_name
FROM tbl_centre
ORDER BY
CASE WHEN centre_id <> @.centre_id THEN 1 END,
centre_name
David Portas
SQL Server MVP
--|||Try,
...
order by
case when @.centre_id > -1 and centre_id = @.centre_id then 0 else 1 end,
centre_name;
AMB
"hals_left" wrote:

> Is there any way to modify this proc so that:
> If it recieves a non-negative centre_id the data is ordered by
> centre_name but with the centre whose id was passed, being first in
> the the list.
> If -1 is passed then just order by centre_name.
> thanks.
> CREATE PROCEDURE [dbo].[get_centres]
> @.centre_id smallint
> AS
> SELECT centre_id, centre_name FROM tbl_centre
> ORDER BY CASE
> WHEN centre_id= @.centre_id Then 0
> WHEN centre_id=-1 THEN centre_name
> ELSE centre_id End
> GO
>|||Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<1114789890.6
70395.10780@.g14g2000cwa.googlegroups.com>...
> SELECT centre_id, centre_name
> FROM tbl_centre
> ORDER BY
> CASE WHEN centre_id <> @.centre_id THEN 1 END,
> centre_name

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 procedures in master

Quick question - if I create my own system stored
procedures in the master database, and I have my own
naming convention, presumably they shouldn't be affected
by service packs?
TIA,
JoeNo they won't be unless they are calling another SP or
exteneded procedure in the master database that is already
a Microsoft System DB.
Peter
"Information is the oxygen of the modern age. It seeps
through the walls topped by barbed wire, it wafts across
the electrified borders.""
Ronald Reagan
>--Original Message--
>Quick question - if I create my own system stored
>procedures in the master database, and I have my own
>naming convention, presumably they shouldn't be affected
>by service packs?
>TIA,
>Joe
>.
>|||> "Information is the oxygen of the modern age. It seeps
> through the walls topped by barbed wire, it wafts across
> the electrified borders.""
> Ronald Reagan
Ahhhh he was such a philosopher.... ;)

Custom Paging on Stored Procedure

Hello,

I receive this error "Incorrect syntax near 'GetGalleryPaged'." I'm trying to use custom paging on a stored procedure.

......
Dim mySqlConn As New SqlConnection(ConnStr)
Dim objDA As New SqlDataAdapter("GetGalleryPaged", mySqlConn)
objDA.SelectCommand.Parameters.Add("@.startRowIndex", SqlDbType.Int, 1)
objDA.SelectCommand.Parameters.Add("@.@.maximumRows", SqlDbType.Int, 9)
Dim objDS As New DataSet()
Dim objPds As PagedDataSource = New PagedDataSource
objDA.Fill(objDS, "Gallery") <<--error here
mySqlConn.Close()

objPds.DataSource = objDS.Tables(0).DefaultView
objPds.AllowPaging = True

......

ALTER PROCEDURE dbo.GetGalleryPaged
(
@.startRowIndex int,
@.maximumRows int
)
AS
SELECT idgallery, g_picpath
FROM
( SELECT idgallery, g_picpath, ROW_NUMBER() OVER (ORDER BY idgallery DESC) AS RowRank
FROM Gallery
) AS GalleryWithRowNumber
WHERE RowRank > @.startRowIndex AND RowRank <= (@.startRowIndex + @.maximumRows)
ORDER BY idgallery DESC

cheers,

imperialx

First problem is here

@.@.maximumRows in following line
objDA.SelectCommand.Parameters.Add("@.@.maximumRows", SqlDbType.Int, 9)

and second is you are adding parameter but not setting their value, try this

objDA.SelectCommand.Parameters.Add("@.startRowIndex", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.startRowIndex").Value = 1

objDA.SelectCommand.Parameters.Add("@.maximumRows", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.maximumRows").Value = 9

|||

Hi Vish4forum,

I still got the same error.

...
Dim mySqlConn As New SqlConnection(ConnStr)
Dim objDA As New SqlDataAdapter("GetGalleryPaged", mySqlConn)

objDA.SelectCommand.Parameters.Add("@.startRowIndex", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.startRowIndex").Value = 1

objDA.SelectCommand.Parameters.Add("@.maximumRows", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.maximumRows").Value = 9

Dim objDS As New DataSet()
Dim objPds As PagedDataSource = New PagedDataSource
objDA.Fill(objDS, "Gallery")
mySqlConn.Close()

objPds.DataSource = objDS.Tables(0).DefaultView
objPds.AllowPaging = True
...

|||

Hi Vish4forum,

I got it, I add this line

...
Dim mySqlConn As New SqlConnection(ConnStr)
Dim objDA As New SqlDataAdapter("GetGalleryPaged", mySqlConn)
objDA.SelectCommand.CommandType = CommandType.StoredProcedure

objDA.SelectCommand.Parameters.Add("@.startRowIndex", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.startRowIndex").Value = 1

objDA.SelectCommand.Parameters.Add("@.maximumRows", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.maximumRows").Value = 9

Dim objDS As New DataSet()
Dim objPds As PagedDataSource = New PagedDataSource
objDA.Fill(objDS, "Gallery")
mySqlConn.Close()

objPds.DataSource = objDS.Tables(0).DefaultView
objPds.AllowPaging = True
...

One thing is, have I set the stored procedure for a custom paging right?

|||

Hi Guys,

If I use the stored procedure that uses custom paging, the paging link controls doesn't work as intended (visible property is always set to false)

ALTER PROCEDURE dbo.GetGalleryPaged
(
@.startRowIndex int,
@.maximumRows int
)
AS
SELECT idgallery, g_picpath
FROM
( SELECT idgallery, g_picpath, ROW_NUMBER() OVER (ORDER BY idgallery DESC) AS RowRank
FROM Gallery
) AS GalleryWithRowNumber
WHERE RowRank > @.startRowIndex AND RowRank <= (@.startRowIndex + @.maximumRows)
ORDER BY idgallery DESC

....

Imports DataSet1TableAdapters
Imports System.Data.SqlClient
Imports System.Data

Partial Class gallery
Inherits System.Web.UI.Page
Dim TotalRowCount As Integer
Dim isLastPage As Boolean

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
DataGalleryList()
End If
End Sub

Protected Sub DataGalleryList()
Dim cs As ClientScriptManager = Page.ClientScript 'just to initialize Ajax ScriptManager
Dim ConnStr = System.Configuration.ConfigurationManager.ConnectionStrings("smsdataConnectionString1").ConnectionString
Dim mySqlConn As New SqlConnection(ConnStr)
Dim objPds As PagedDataSource = New PagedDataSource
Dim objDA As New SqlDataAdapter("GetGalleryPaged", mySqlConn)
objDA.SelectCommand.CommandType = CommandType.StoredProcedure

If isLastPage Then
CurrentPage = objPds.PageCount - 1
objPds.CurrentPageIndex = objPds.PageCount - 1
Else
objPds.CurrentPageIndex = CurrentPage
End If

objDA.SelectCommand.Parameters.Add("@.startRowIndex", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.startRowIndex").Value = objPds.CurrentPageIndex
objDA.SelectCommand.Parameters.Add("@.maximumRows", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.maximumRows").Value = 9

Dim objDS As New DataSet()
objDA.Fill(objDS, "Gallery")
mySqlConn.Close()

objPds.DataSource = objDS.Tables(0).DefaultView
objPds.AllowPaging = True
''''''objPds.PageSize = 9

Dim gallery As New DataSet1TableAdapters.galleryTableAdapter
Dim galleryGetPaged As DataSet1.galleryDataTable = gallery.GetDataGallery
TotalRowCount = gallery.TotalNumGallery

lblTotalImage.Text = ("Total Image: " + TotalRowCount.ToString)
lblTotalImage_dview.Text = ("Total Image: " + TotalRowCount.ToString)
lblCurrentPage.Text = ("Page: " + ((CurrentPage + 1).ToString + (" of " + objPds.PageCount.ToString)))

PrevPage.Visible = Not objPds.IsFirstPage
NextPage.Visible = Not objPds.IsLastPage
FirstPage.Visible = Not objPds.IsFirstPage
LastPage.Visible = Not objPds.IsLastPage

DataList1.DataSource = objPds
DataList1.DataBind()

ViewState("currentItemIndex") = currentItemIndex
End Sub

Protected Sub FirstPage_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles FirstPage.Click
CurrentPage = 0
DataGalleryList()
End Sub
Protected Sub PrevPage_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles PrevPage.Click
CurrentPage -= 1
DataGalleryList()
End Sub
Protected Sub NextPage_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles NextPage.Click
CurrentPage += 1
DataGalleryList()
End Sub
Protected Sub LastPage_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles LastPage.Click
isLastPage = True
DataGalleryList()
End Sub
Public Property CurrentPage() As Integer
Get
Dim o As Object = ViewState("_CurrentPage")
If (o = Nothing) Then
Return 0
End If
Return CType(o, Integer)
End Get
Set(ByVal value As Integer)
ViewState("_CurrentPage") = value
End Set
End Property

...

<DIV class="totalimage">
<asp:Label id="lblTotalImage" runat="server" CssClass="tot_img"></asp:Label>
<asp:Label id="lblCurrentPage" runat="server" CssClass="crntpnum" Text="Label"></asp:Label>
</DIV>
<BR /><BR />
<DIV style="PADDING-RIGHT: 0px; PADDING-LEFT: 0px; FLOAT: left; PADDING-BOTTOM: 0px; MARGIN: 0px 0px 0px 25px; WIDTH: 500px; PADDING-TOP: 0px; TEXT-ALIGN: center">
<asp:DataList id="DataList1" runat="server" Width="480px" HorizontalAlign="Center" RepeatDirection="Horizontal" RepeatColumns="3" DataKeyField="idGallery">
<ItemTemplate>
<asp:ImageButton CommandName="Select" CssClass="g_thumbs" ImageUrl='<%#Eval("g_picpath") %>' ID="ImageButton2" runat="server" />
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" Wrap="True" />
</asp:DataList>
</DIV>
<DIV style="WIDTH: 347px" class="g_paginglink">
<UL>
<LI><asp:LinkButton id="FirstPage" runat="server">First</asp:LinkButton></LI>
<LI><asp:LinkButton id="PrevPage" runat="server">Previous</asp:LinkButton></LI>
<LI><asp:LinkButton id="NextPage" runat="server">Next</asp:LinkButton></LI>
<LI><asp:LinkButton id="LastPage" runat="server">Last</asp:LinkButton></LI>
</UL>
</DIV>

|||

Hello,

I got it now.

The Stored Procedure parameter values must be called first before the PagedDataSource values.

Protected Sub DataGalleryList()
Dim cs As ClientScriptManager = Page.ClientScript 'just to initialize Ajax ScriptManager
Dim ConnStr = System.Configuration.ConfigurationManager.ConnectionStrings("smsdataConnectionString1").ConnectionString
Dim mySqlConn As New SqlConnection(ConnStr)
Dim objPds As PagedDataSource = New PagedDataSource
Dim objDA As New SqlDataAdapter("GetGalleryPaged", mySqlConn)
Dim objDS As New DataSet()
Dim gallery As New DataSet1TableAdapters.galleryTableAdapter
Dim galleryGetPaged As DataSet1.galleryDataTable = gallery.GetDataGallery
TotalRowCount = gallery.TotalNumGallery

objDA.SelectCommand.CommandType = CommandType.StoredProcedure
objDA.SelectCommand.Parameters.Add("@.startRowIndex", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.startRowIndex").Value = objPds.CurrentPageIndex
objDA.SelectCommand.Parameters.Add("@.maximumRows", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.maximumRows").Value = TotalRowCount

objDA.Fill(objDS, "Gallery")
mySqlConn.Close()

objPds.DataSource = objDS.Tables(0).DefaultView
objPds.AllowPaging = True
objPds.PageSize = 9

If isLastPage Then
CurrentPage = objPds.PageCount - 1
objPds.CurrentPageIndex = CurrentPage
Else
objPds.CurrentPageIndex = CurrentPage
End If

currentItemIndex = objPds.FirstIndexInPage 'indexing datailview control

lblTotalImage.Text = ("Total Image: " + TotalRowCount.ToString)
lblTotalImage_dview.Text = ("Total Image: " + TotalRowCount.ToString)
lblCurrentPage.Text = ("Page: " + ((CurrentPage + 1).ToString + (" of " + objPds.PageCount.ToString)))

PrevPage.Visible = Not objPds.IsFirstPage
NextPage.Visible = Not objPds.IsLastPage
FirstPage.Visible = Not objPds.IsFirstPage
LastPage.Visible = Not objPds.IsLastPage

DataList1.DataSource = objPds
DataList1.DataBind()

ViewState("currentItemIndex") = currentItemIndex
End Sub

Custom Error Messages

My understanding is that in a stored procedure (or any code for that
matter) if an error occurs you can detect it by checking @.@.error
variable and raise your own error with raiserror statement.

The problem is that the original error is not suppressed. For example
I received the following output from a stored procedure from the same
error:

Server: Msg 547, Level 16, State 1, Procedure spUpdateSecurityMaster,
Line 49
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK_SM_mm_Exchange_Exchanges'. The conflict occurred in database
'Trading', table 'Exchanges', column 'IsoCode'.
Server: Msg 50000, Level 14, State 1, Procedure
spUpdateSecurityMaster, Line 57
Unable to insert into "SM_mm_Exchange" table
The statement has been terminated.

So why should we bother to use raiseerror if the orginal error is
going to be given to the client anyways? The end result is two error
messages.On 1 Oct 2003 06:41:42 -0700, JayCallas@.hotmail.com (Jason) wrote:

>My understanding is that in a stored procedure (or any code for that
>matter) if an error occurs you can detect it by checking @.@.error
>variable and raise your own error with raiserror statement.
>The problem is that the original error is not suppressed. For example
>I received the following output from a stored procedure from the same
>error:
>Server: Msg 547, Level 16, State 1, Procedure spUpdateSecurityMaster,
>Line 49
>INSERT statement conflicted with COLUMN FOREIGN KEY constraint
>'FK_SM_mm_Exchange_Exchanges'. The conflict occurred in database
>'Trading', table 'Exchanges', column 'IsoCode'.
>Server: Msg 50000, Level 14, State 1, Procedure
>spUpdateSecurityMaster, Line 57
>Unable to insert into "SM_mm_Exchange" table
>The statement has been terminated.
>So why should we bother to use raiseerror if the orginal error is
>going to be given to the client anyways? The end result is two error
>messages.
The reason is that SQL Server will not allow you to trap errors of a
certain severity.

If the error was less severe then what you want to happen will work.|||Jason (JayCallas@.hotmail.com) writes:
> My understanding is that in a stored procedure (or any code for that
> matter) if an error occurs you can detect it by checking @.@.error
> variable and raise your own error with raiserror statement.
> The problem is that the original error is not suppressed. For example
> I received the following output from a stored procedure from the same
> error:
> Server: Msg 547, Level 16, State 1, Procedure spUpdateSecurityMaster,
> Line 49
> INSERT statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_SM_mm_Exchange_Exchanges'. The conflict occurred in database
> 'Trading', table 'Exchanges', column 'IsoCode'.
> Server: Msg 50000, Level 14, State 1, Procedure
> spUpdateSecurityMaster, Line 57
> Unable to insert into "SM_mm_Exchange" table
> The statement has been terminated.
> So why should we bother to use raiseerror if the orginal error is
> going to be given to the client anyways? The end result is two error
> messages.

It's actually even three. That last "The statement has been terminated"
is a separate message.

No, there is not much with a RAISERROR here. But there might be
occasions where RAISERROR is your sole choice. Say that you have a
procedure that has an parameter that controls the logic, and it
have have the values A, B and C. You procedure would look like:

IF @.action = 'A'
BEGIN
...
END
ELSE IF @.action = 'B'
BEGIN
...
END
ELSE IF @.action = 'C'
BEGIN
...
END
ELSE
BEGIN
RAISERROR ('Illegal action "%s" passed!', 16, 1, @.action)
RETURN 1
END

And, no there is no way to suppress the error message from SQL. You
need a client to do that.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9408F0145FFYazorman@.127.0.0.1>...
> It's actually even three. That last "The statement has been terminated"
> is a separate message.
> No, there is not much with a RAISERROR here. But there might be
> occasions where RAISERROR is your sole choice. Say that you have a
> procedure that has an parameter that controls the logic, and it
> have have the values A, B and C. You procedure would look like:
> IF @.action = 'A'
> BEGIN
> ...
> END
> ELSE IF @.action = 'B'
> BEGIN
> ...
> END
> ELSE IF @.action = 'C'
> BEGIN
> ...
> END
> ELSE
> BEGIN
> RAISERROR ('Illegal action "%s" passed!', 16, 1, @.action)
> RETURN 1
> END
> And, no there is no way to suppress the error message from SQL. You
> need a client to do that.

I figured that. So basically it makes no sense to use raiserror when a
database statement (delete, insert, update) is used since one will
already be thrown.|||Jason (JayCallas@.hotmail.com) writes:
> I figured that. So basically it makes no sense to use raiserror when a
> database statement (delete, insert, update) is used since one will
> already be thrown.

Yes, it would be fairly redundant. I guess there might be occassions
where it could make sense, for instance convey information about what
was going on, like "Error when adding account 98989". But as a matter
of routine, it would be pointless.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Custom error messages

Some of our reports use a stored proc as the source for base data. The proc contains RAISEERROR statements to handle error scenarios.

Is there a way in which I could display the message that is output from the RAISEERROR statements in the report?

ThanksThis is not natively supported. You would need to return it as a data column.

Custom Conflict Resolver

Hi All,
I'm using a custom conflict resolver (using stored procedure) for one of any
articles.
I want to determine which columns were in conflict in this stored procedure.
Could you pls point me to some pointers on how to achieve the same.
Thanking you.....
I don't believe this can be done. If you are using column level tracking you
might be able to parse the message returned.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pinkesh" <Pinkesh@.discussions.microsoft.com> wrote in message
news:9E9B4BA9-BCCF-4FE6-8EE4-C5A506B87E73@.microsoft.com...
> Hi All,
> I'm using a custom conflict resolver (using stored procedure) for one of
any
> articles.
> I want to determine which columns were in conflict in this stored
procedure.
> Could you pls point me to some pointers on how to achieve the same.
> Thanking you.....
>
>
|||Hi Hilary,
Thanks for the prompyt reply.
I'm using Column level tracking for my publication.
Sorry but I didn't understand what you mean by "parse the message returned".
Could you pls help by providing some details regarding it.
Thanking you.
"Hilary Cotter" wrote:

> I don't believe this can be done. If you are using column level tracking you
> might be able to parse the message returned.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Pinkesh" <Pinkesh@.discussions.microsoft.com> wrote in message
> news:9E9B4BA9-BCCF-4FE6-8EE4-C5A506B87E73@.microsoft.com...
> any
> procedure.
>
>

CUSTOM ASSEMBLY IN RDL - ISSUE - NEED YOUR HELP!

Hi All MSTR Mentor's
I have a RDL, where the stored procedure returns 10 fields among that 1
field is the creditcard# which is encrypted, I need to decryt the
creditcard # and display it in the report.
I use a .net custom aseembly which has a decrypt function and it is added
as a reference to the rdl and it is WORKING FINE when i view the report in
the PREVIEW TAB of the report designer.
When I deploy the report to my ReportServer of the localmachine and view
the report it is showing #Error on the field value.
What could be the problem, plese help me on this issue.
using System;
using System.Text;
using System.Collections.Specialized;
using System.Configuration;
using System.Security.Cryptography;
using FCLX509 = System.Security.Cryptography.X509Certificates;
using WSEX509 = Microsoft.Web.Services2.Security.X509;
using WSECRY = Microsoft.Web.Services2.Security.Cryptography;
namespace RDLCustomCode
{
public class DataDecryptionClass
{
public DataDecryptionClass()
{
}
public static string DecryptCardInfo(string cc,string subjectName,string
storeName)
{
try
{
string sCreditCard = "";
WSEX509.X509CertificateStore.StoreLocation location = WSEX509.X509CertificateStore.StoreLocation.CurrentUser;
WSEX509.X509CertificateStore.StoreProvider provider = WSEX509.X509CertificateStore.StoreProvider.System;
WSEX509.X509CertificateStore store = new WSEX509.X509CertificateStore
(provider, location, storeName);
bool fopen = store.OpenRead();
if(fopen)
{
WSEX509.X509CertificateCollection certs = store.FindCertificateBySubjectString(subjectName);
if (certs.Count > 0)
{
WSEX509.X509Certificate cer = certs[0];
WSECRY.RSACryptoServiceProvider rsaCsp = (WSECRY.RSACryptoServiceProvider)cer.Key;
byte[] cipherData = Convert.FromBase64String(cc);
byte[] plainData = rsaCsp.Decrypt(cipherData, false);
sCreditCard = Encoding.UTF8.GetString(plainData);
}
}
if (store != null)
store.Close();
return sCreditCard;
}
catch
{
return "";
}
}
--
Message posted via http://www.sqlmonster.comHave you copied custom assembly to the ReportServer bin directory (e.g. to
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin)?
"BALAJI KRISHNAN via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:05caacfe42a94a4c95e196c9e9cd672b@.SQLMonster.com...
> Hi All MSTR Mentor's
> I have a RDL, where the stored procedure returns 10 fields among that 1
> field is the creditcard# which is encrypted, I need to decryt the
> creditcard # and display it in the report.
> I use a .net custom aseembly which has a decrypt function and it is added
> as a reference to the rdl and it is WORKING FINE when i view the report in
> the PREVIEW TAB of the report designer.
> When I deploy the report to my ReportServer of the localmachine and view
> the report it is showing #Error on the field value.
> What could be the problem, plese help me on this issue.
>
>
>
>
>
>
> using System;
> using System.Text;
> using System.Collections.Specialized;
> using System.Configuration;
> using System.Security.Cryptography;
> using FCLX509 = System.Security.Cryptography.X509Certificates;
> using WSEX509 = Microsoft.Web.Services2.Security.X509;
> using WSECRY = Microsoft.Web.Services2.Security.Cryptography;
> namespace RDLCustomCode
> {
> public class DataDecryptionClass
> {
> public DataDecryptionClass()
> {
> }
> public static string DecryptCardInfo(string cc,string subjectName,string
> storeName)
> {
> try
> {
> string sCreditCard = "";
> WSEX509.X509CertificateStore.StoreLocation location => WSEX509.X509CertificateStore.StoreLocation.CurrentUser;
> WSEX509.X509CertificateStore.StoreProvider provider => WSEX509.X509CertificateStore.StoreProvider.System;
> WSEX509.X509CertificateStore store = new WSEX509.X509CertificateStore
> (provider, location, storeName);
> bool fopen = store.OpenRead();
> if(fopen)
> {
> WSEX509.X509CertificateCollection certs => store.FindCertificateBySubjectString(subjectName);
> if (certs.Count > 0)
> {
> WSEX509.X509Certificate cer = certs[0];
> WSECRY.RSACryptoServiceProvider rsaCsp => (WSECRY.RSACryptoServiceProvider)cer.Key;
> byte[] cipherData = Convert.FromBase64String(cc);
> byte[] plainData = rsaCsp.Decrypt(cipherData, false);
> sCreditCard = Encoding.UTF8.GetString(plainData);
> }
> }
> if (store != null)
> store.Close();
> return sCreditCard;
> }
> catch
> {
> return "";
> }
> }
> --
> Message posted via http://www.sqlmonster.com|||Dmitry Nechipor,
Yes, I have copied the dll in C:\Program Files\Microsoft SQL Server\MSSQL\
ReportingServices\ReportServer\bin.
But still the same result.
Do I need to set CAS to the code..if so how to give the permission.
Balaji
--
Message posted via http://www.sqlmonster.com|||Please give CAS permission in file:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\rssrvpolicy.xml
See Online book with RS installation for XML entry for your DLL
"BALAJI KRISHNAN via SQLMonster.com" wrote:
> Hi All MSTR Mentor's
> I have a RDL, where the stored procedure returns 10 fields among that 1
> field is the creditcard# which is encrypted, I need to decryt the
> creditcard # and display it in the report.
> I use a .net custom aseembly which has a decrypt function and it is added
> as a reference to the rdl and it is WORKING FINE when i view the report in
> the PREVIEW TAB of the report designer.
> When I deploy the report to my ReportServer of the localmachine and view
> the report it is showing #Error on the field value.
> What could be the problem, plese help me on this issue.
>
>
>
>
>
>
> using System;
> using System.Text;
> using System.Collections.Specialized;
> using System.Configuration;
> using System.Security.Cryptography;
> using FCLX509 = System.Security.Cryptography.X509Certificates;
> using WSEX509 = Microsoft.Web.Services2.Security.X509;
> using WSECRY = Microsoft.Web.Services2.Security.Cryptography;
> namespace RDLCustomCode
> {
> public class DataDecryptionClass
> {
> public DataDecryptionClass()
> {
> }
> public static string DecryptCardInfo(string cc,string subjectName,string
> storeName)
> {
> try
> {
> string sCreditCard = "";
> WSEX509.X509CertificateStore.StoreLocation location => WSEX509.X509CertificateStore.StoreLocation.CurrentUser;
> WSEX509.X509CertificateStore.StoreProvider provider => WSEX509.X509CertificateStore.StoreProvider.System;
> WSEX509.X509CertificateStore store = new WSEX509.X509CertificateStore
> (provider, location, storeName);
> bool fopen = store.OpenRead();
> if(fopen)
> {
> WSEX509.X509CertificateCollection certs => store.FindCertificateBySubjectString(subjectName);
> if (certs.Count > 0)
> {
> WSEX509.X509Certificate cer = certs[0];
> WSECRY.RSACryptoServiceProvider rsaCsp => (WSECRY.RSACryptoServiceProvider)cer.Key;
> byte[] cipherData = Convert.FromBase64String(cc);
> byte[] plainData = rsaCsp.Decrypt(cipherData, false);
> sCreditCard = Encoding.UTF8.GetString(plainData);
> }
> }
> if (store != null)
> store.Close();
> return sCreditCard;
> }
> catch
> {
> return "";
> }
> }
> --
> Message posted via http://www.sqlmonster.com
>|||Hi Sunnet,
I have added the CAS PERMISSION by adding this code group to the
rssrvpolicy.config file on C:\Program Files\Microsoft SQL Server\MSSQL\
Reporting Services\ReportServer
Here is the code I have added to the config file
</CodeGroup>
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="XMLCodeGroup"
Description="Code group for my XML data processing extension">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\RDLCustomCode.dll" />
</CodeGroup>
But still I have the same problem.
Balaji
--
Message posted via http://www.sqlmonster.com|||Hi Sunnet,
C:\Program Files\Microsoft SQL Server\MSSQL\
Reporting Services\ReportServer
I have changed class="AllMembershipCondition" instead of
class="UrlMembershipCondition"
Now, I am not seeing the #Error, but still i could not see the value, it is
blank now...
What could be the problem
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="Report_Expressions_Default_Permissions"
Description="A special code group for my custom assembly.">
<IMembershipCondition
class="AllMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\RDLCustomCode.dll"
/>
--
Message posted via http://www.sqlmonster.com

Custom Assembly

We have written a custom assembly which makes a call to database and
executes one stored procedure and based on stored procedure returned value a
method called IsAuthorized() returns true or false. In the development
environment assembly and report behaves properly. But when we deploy it to
production it gives us error. Any ideas what we are doing wrong ? We are
guessing its some kind of permission issues. Any help greatly appreciated.There is a very useful article on permissions with custom assemblies here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_16g2.asp
"new.microsoft.com" <gsinthoju@.mail.educo-int.com> wrote in message
news:O0ka2itaFHA.3620@.TK2MSFTNGP09.phx.gbl...
> We have written a custom assembly which makes a call to database and
> executes one stored procedure and based on stored procedure returned value
> a
> method called IsAuthorized() returns true or false. In the development
> environment assembly and report behaves properly. But when we deploy it to
> production it gives us error. Any ideas what we are doing wrong ? We are
> guessing its some kind of permission issues. Any help greatly appreciated.
>|||two books specifically talk about this. microsoft reporting services in
action and hitchhikers guide to sql server 2000.
they solved my problems.
"new.microsoft.com" <gsinthoju@.mail.educo-int.com> wrote in message
news:O0ka2itaFHA.3620@.TK2MSFTNGP09.phx.gbl...
> We have written a custom assembly which makes a call to database and
> executes one stored procedure and based on stored procedure returned value
> a
> method called IsAuthorized() returns true or false. In the development
> environment assembly and report behaves properly. But when we deploy it to
> production it gives us error. Any ideas what we are doing wrong ? We are
> guessing its some kind of permission issues. Any help greatly appreciated.
>

Custom assemblies and accessing report parameters

I am trying to access the datasource parameters (connection string etc)
through the report with the intention of using them to fire off a stored
procedure at the end of the report. The parameters will be passed to a custom
assembly will execute the sp to update several status flags in the database.
The assembly is done and updates the status parameters, however the
connection string is currently hard coded.
Is there any way to access these parameters direct from the report, or an
interface I could implement in my assembly that would allow me access to them?Please see my response on your other thread with the same title.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Benugo" <Benugo@.discussions.microsoft.com> wrote in message
news:484BE97E-55FF-456B-94C7-C7AECE733CF1@.microsoft.com...
> I am trying to access the datasource parameters (connection string etc)
> through the report with the intention of using them to fire off a stored
> procedure at the end of the report. The parameters will be passed to a
custom
> assembly will execute the sp to update several status flags in the
database.
> The assembly is done and updates the status parameters, however the
> connection string is currently hard coded.
> Is there any way to access these parameters direct from the report, or an
> interface I could implement in my assembly that would allow me access to
them?

Cursors within stored procedures

Hi,
I've got a stored procedure that makes use of several cursors to
collate data and then place it into a temp table, which is then
eventually returned to the calling code. The problem is that when I
execute the stored proc, opening the cursors seems to be returning a
recordset, when all I need is the temp table. Code is below, any help
much appreciated!
CREATE PROCEDURE CRM_AccountManagerAnticipatedDeliverable
s2
AS
DECLARE @.Measure varchar(50)
DECLARE @.NumberOfCEFSentToLSCNew int
DECLARE @.NumberOfCEFSentToLSCRepeat int
DECLARE @.NumberOfFASentToLSC int
DECLARE @.NumberOfLAEnteredAccredited int
DECLARE @.NumberOfLAEnteredBespoke int
DECLARE @.AccountManager varchar(50)
DECLARE @.CurrentCount int
/* Create a temporary table */
CREATE TABLE #CalculatedValues
(
Measure varchar(50),
AccountManager varchar(50),
NumberOfCEFSentToLSCNew int,
NumberOfCEFSentToLSCRepeat int,
NumberOfFASentToLSC int,
NumberOfLAEnteredAccredited int,
NumberOfLAEnteredBespoke int
)
/* get measures into a cursor */
DECLARE MeasureCursor CURSOR FOR
SELECT new_name
FROM CRMDEMO_MSCRM.dbo.FilteredNew_Measure
OPEN MeasureCursor
/* start cursor loop */
FETCH NEXT FROM MeasureCursor INTO @.Measure
WHILE @.@.FETCH_STATUS = 0
BEGIN
/* NumberOfCEFSentToLSCNew */
INSERT INTO #CalculatedValues
SELECT @.Measure,
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY,
COUNT(CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_NAME),
0, 0, 0, 0
FROM
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_COMPANYACCOUNTID =
CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.ACCOUNTID
WHERE
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
'Account Manager'
AND CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_CEFSENTTOLSC = 0
AND
CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_HAVEYOUHADACCESSTOCOF1FUNDINGBEF
= 0
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
@.Measure
GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
/* NumberOfCEFSentToLSCRepeat */
DECLARE TempCursor1 CURSOR FOR
SELECT CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as
a1a,
COUNT(CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_NAME)
FROM
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_COMPANYACCOUNTID =
CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.ACCOUNTID
WHERE
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
'Account Manager'
AND CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_CEFSENTTOLSC = 0
AND
CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_HAVEYOUHADACCESSTOCOF1FUNDINGBEF
= 1
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
@.Measure
GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
OPEN TempCursor1
FETCH NEXT FROM TempCursor1 INTO @.AccountManager, @.CurrentCount
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(AccountManager)
FROM #CalculatedValues
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure) > 0
BEGIN
UPDATE #CalculatedValues
SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure
END
ELSE
BEGIN
INSERT INTO #CalculatedValues
(Measure,
AccountManager,
NumberOfCEFSentToLSCNew,
NumberOfCEFSentToLSCRepeat,
NumberOfFASentToLSC,
NumberOfLAEnteredAccredited,
NumberOfLAEnteredBespoke)
VALUES(@.Measure, @.AccountManager, 0, @.CurrentCount, 0, 0, 0)
END
FETCH NEXT FROM TempCursor1
END
CLOSE TempCursor1
DEALLOCATE TempCursor1
/*NumberOfFASentToLSC*/
DECLARE TempCursor2 CURSOR FOR
SELECT
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as a2a,
COUNT(CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_NAME)
FROM
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
INNER JOIN
CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_COMPANYACCOUNTID =
CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.ACCOUNTID
WHERE
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
'Account Manager'
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_FASENTTOTHELSC =
0
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
@.Measure
GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
OPEN TempCursor2
FETCH NEXT FROM TempCursor2 INTO @.AccountManager, @.CurrentCount
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(AccountManager)
FROM #CalculatedValues
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure) > 0
BEGIN
UPDATE #CalculatedValues
SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure
END
ELSE
BEGIN
INSERT INTO #CalculatedValues
(Measure,
AccountManager,
NumberOfCEFSentToLSCNew,
NumberOfCEFSentToLSCRepeat,
NumberOfFASentToLSC,
NumberOfLAEnteredAccredited,
NumberOfLAEnteredBespoke)
VALUES(@.Measure, @.AccountManager, 0, 0, @.CurrentCount, 0, 0)
END
FETCH NEXT FROM TempCursor2
END
CLOSE TempCursor2
DEALLOCATE TempCursor2
/* NumberOfLAEnteredAccredited */
DECLARE TempCursor3 CURSOR FOR
SELECT
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as a3a,
COUNT(CAST(CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_LEARNINGAIMID
AS VARCHAR(255)))
FROM CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_FINANCIALANNEXID =
CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_PARENTFAID
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.new_nationialinsuranceno =
CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.new_name
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_QNUMBERID =
CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_COURSEID
WHERE
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
'Account Manager'
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
@.Measure
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.NEW_LEARNERSTATUSNAME =
'Awaiting Training'
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_ISTHECOURSEACREDITED = 1
GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
OPEN TempCursor3
FETCH NEXT FROM TempCursor3 INTO @.AccountManager, @.CurrentCount
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(AccountManager)
FROM #CalculatedValues
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure) > 0
BEGIN
UPDATE #CalculatedValues
SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure
END
ELSE
BEGIN
INSERT INTO #CalculatedValues
(Measure,
AccountManager,
NumberOfCEFSentToLSCNew,
NumberOfCEFSentToLSCRepeat,
NumberOfFASentToLSC,
NumberOfLAEnteredAccredited,
NumberOfLAEnteredBespoke)
VALUES(@.Measure, @.AccountManager, 0, 0, 0, @.CurrentCount, 0)
END
FETCH NEXT FROM TempCursor3
END
CLOSE TempCursor3
DEALLOCATE TempCursor3
/* NumberOfLAEnteredBespoke */
DECLARE TempCursor4 CURSOR FOR
SELECT
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as a4a,
COUNT(CAST(CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_LEARNINGAIMID
as varchar(255)))
FROM CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_FINANCIALANNEXID =
CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_PARENTFAID
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.new_nationialinsuranceno =
CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.new_name
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_QNUMBERID =
CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_COURSEID
WHERE
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
'Account Manager'
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
@.Measure
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.NEW_LEARNERSTATUSNAME =
'Awaiting Training'
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_ISTHECOURSEACREDITED = 0
GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
OPEN TempCursor4
FETCH NEXT FROM TempCursor4 INTO @.AccountManager, @.CurrentCount
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(AccountManager)
FROM #CalculatedValues
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure) > 0
BEGIN
UPDATE #CalculatedValues
SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure
END
ELSE
BEGIN
INSERT INTO #CalculatedValues
(Measure,
AccountManager,
NumberOfCEFSentToLSCNew,
NumberOfCEFSentToLSCRepeat,
NumberOfFASentToLSC,
NumberOfLAEnteredAccredited,
NumberOfLAEnteredBespoke)
VALUES(@.Measure, @.AccountManager, 0, 0, 0, 0, @.CurrentCount)
END
FETCH NEXT FROM TempCursor4
END
CLOSE TempCursor4
DEALLOCATE TempCursor4
FETCH NEXT FROM MeasureCursor INTO @.Measure
END
/* end cursor loop */
CLOSE MeasureCursor
DEALLOCATE MeasureCursor
/* return values back */
SELECT * FROM #CalculatedValues
GODo you mean when the recordset are returned to the client. Would SET NOCOUNT
ON solve your problem?
--
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
<peteandrews@.hotmail.com> wrote in message
news:1138029917.821223.30410@.g49g2000cwa.googlegroups.com...
> Hi,
> I've got a stored procedure that makes use of several cursors to
> collate data and then place it into a temp table, which is then
> eventually returned to the calling code. The problem is that when I
> execute the stored proc, opening the cursors seems to be returning a
> recordset, when all I need is the temp table. Code is below, any help
> much appreciated!
> CREATE PROCEDURE CRM_AccountManagerAnticipatedDeliverable
s2
> AS
> DECLARE @.Measure varchar(50)
> DECLARE @.NumberOfCEFSentToLSCNew int
> DECLARE @.NumberOfCEFSentToLSCRepeat int
> DECLARE @.NumberOfFASentToLSC int
> DECLARE @.NumberOfLAEnteredAccredited int
> DECLARE @.NumberOfLAEnteredBespoke int
> DECLARE @.AccountManager varchar(50)
> DECLARE @.CurrentCount int
> /* Create a temporary table */
> CREATE TABLE #CalculatedValues
> (
> Measure varchar(50),
> AccountManager varchar(50),
> NumberOfCEFSentToLSCNew int,
> NumberOfCEFSentToLSCRepeat int,
> NumberOfFASentToLSC int,
> NumberOfLAEnteredAccredited int,
> NumberOfLAEnteredBespoke int
> )
> /* get measures into a cursor */
> DECLARE MeasureCursor CURSOR FOR
> SELECT new_name
> FROM CRMDEMO_MSCRM.dbo.FilteredNew_Measure
> OPEN MeasureCursor
> /* start cursor loop */
> FETCH NEXT FROM MeasureCursor INTO @.Measure
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> /* NumberOfCEFSentToLSCNew */
> INSERT INTO #CalculatedValues
> SELECT @.Measure,
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY,
> COUNT(CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_NAME),
> 0, 0, 0, 0
> FROM
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_COMPANYACCOUNTID =
> CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.ACCOUNTID
> WHERE
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
> 'Account Manager'
> AND CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_CEFSENTTOLSC = 0
> AND
> CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_HAVEYOUHADACCESSTOCOF1FUNDINGBEF
> = 0
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
> @.Measure
> GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
>
> /* NumberOfCEFSentToLSCRepeat */
> DECLARE TempCursor1 CURSOR FOR
> SELECT CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as
> a1a,
> COUNT(CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_NAME)
> FROM
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_COMPANYACCOUNTID =
> CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.ACCOUNTID
> WHERE
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
> 'Account Manager'
> AND CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_CEFSENTTOLSC = 0
> AND
> CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_HAVEYOUHADACCESSTOCOF1FUNDINGBEF
> = 1
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
> @.Measure
> GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
> OPEN TempCursor1
> FETCH NEXT FROM TempCursor1 INTO @.AccountManager, @.CurrentCount
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(AccountManager)
> FROM #CalculatedValues
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure) > 0
> BEGIN
> UPDATE #CalculatedValues
> SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure
> END
> ELSE
> BEGIN
> INSERT INTO #CalculatedValues
> (Measure,
> AccountManager,
> NumberOfCEFSentToLSCNew,
> NumberOfCEFSentToLSCRepeat,
> NumberOfFASentToLSC,
> NumberOfLAEnteredAccredited,
> NumberOfLAEnteredBespoke)
> VALUES(@.Measure, @.AccountManager, 0, @.CurrentCount, 0, 0, 0)
> END
> FETCH NEXT FROM TempCursor1
> END
> CLOSE TempCursor1
> DEALLOCATE TempCursor1
> /*NumberOfFASentToLSC*/
> DECLARE TempCursor2 CURSOR FOR
> SELECT
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as a2a,
> COUNT(CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_NAME)
> FROM
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
> INNER JOIN
> CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_COMPANYACCOUNTID =
> CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.ACCOUNTID
> WHERE
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
> 'Account Manager'
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_FASENTTOTHELSC =
> 0
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
> @.Measure
> GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
> OPEN TempCursor2
> FETCH NEXT FROM TempCursor2 INTO @.AccountManager, @.CurrentCount
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(AccountManager)
> FROM #CalculatedValues
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure) > 0
> BEGIN
> UPDATE #CalculatedValues
> SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure
> END
> ELSE
> BEGIN
> INSERT INTO #CalculatedValues
> (Measure,
> AccountManager,
> NumberOfCEFSentToLSCNew,
> NumberOfCEFSentToLSCRepeat,
> NumberOfFASentToLSC,
> NumberOfLAEnteredAccredited,
> NumberOfLAEnteredBespoke)
> VALUES(@.Measure, @.AccountManager, 0, 0, @.CurrentCount, 0, 0)
> END
> FETCH NEXT FROM TempCursor2
> END
> CLOSE TempCursor2
> DEALLOCATE TempCursor2
> /* NumberOfLAEnteredAccredited */
> DECLARE TempCursor3 CURSOR FOR
> SELECT
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as a3a,
> COUNT(CAST(CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_LEARNINGAIMID
> AS VARCHAR(255)))
> FROM CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_FINANCIALANNEXID =
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_PARENTFAID
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.new_nationialinsuranceno =
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.new_name
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_QNUMBERID =
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_COURSEID
> WHERE
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
> 'Account Manager'
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
> @.Measure
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.NEW_LEARNERSTATUSNAME =
> 'Awaiting Training'
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_ISTHECOURSEACREDITED = 1
> GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
> OPEN TempCursor3
> FETCH NEXT FROM TempCursor3 INTO @.AccountManager, @.CurrentCount
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(AccountManager)
> FROM #CalculatedValues
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure) > 0
> BEGIN
> UPDATE #CalculatedValues
> SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure
> END
> ELSE
> BEGIN
> INSERT INTO #CalculatedValues
> (Measure,
> AccountManager,
> NumberOfCEFSentToLSCNew,
> NumberOfCEFSentToLSCRepeat,
> NumberOfFASentToLSC,
> NumberOfLAEnteredAccredited,
> NumberOfLAEnteredBespoke)
> VALUES(@.Measure, @.AccountManager, 0, 0, 0, @.CurrentCount, 0)
> END
> FETCH NEXT FROM TempCursor3
> END
> CLOSE TempCursor3
> DEALLOCATE TempCursor3
> /* NumberOfLAEnteredBespoke */
> DECLARE TempCursor4 CURSOR FOR
> SELECT
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as a4a,
> COUNT(CAST(CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_LEARNINGAIMID
> as varchar(255)))
> FROM CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_FINANCIALANNEXID =
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_PARENTFAID
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.new_nationialinsuranceno =
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.new_name
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_QNUMBERID =
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_COURSEID
> WHERE
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
> 'Account Manager'
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
> @.Measure
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.NEW_LEARNERSTATUSNAME =
> 'Awaiting Training'
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_ISTHECOURSEACREDITED = 0
> GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
>
> OPEN TempCursor4
> FETCH NEXT FROM TempCursor4 INTO @.AccountManager, @.CurrentCount
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(AccountManager)
> FROM #CalculatedValues
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure) > 0
> BEGIN
> UPDATE #CalculatedValues
> SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure
> END
> ELSE
> BEGIN
> INSERT INTO #CalculatedValues
> (Measure,
> AccountManager,
> NumberOfCEFSentToLSCNew,
> NumberOfCEFSentToLSCRepeat,
> NumberOfFASentToLSC,
> NumberOfLAEnteredAccredited,
> NumberOfLAEnteredBespoke)
> VALUES(@.Measure, @.AccountManager, 0, 0, 0, 0, @.CurrentCount)
> END
> FETCH NEXT FROM TempCursor4
> END
> CLOSE TempCursor4
> DEALLOCATE TempCursor4
> FETCH NEXT FROM MeasureCursor INTO @.Measure
> END
> /* end cursor loop */
> CLOSE MeasureCursor
> DEALLOCATE MeasureCursor
> /* return values back */
> SELECT * FROM #CalculatedValues
> GO
>|||I found at least on other problem (apart from the possible NOCOUNT problem):
FETCH NEXT FROM TempCursor1
There might be more than one FETCH without specifying INTO @.variable(s). Suc
h will return a
resultset instead of adding the value for each column into the variables. I
didn't want to post this
as I don't have the time to go through the logic and propose a set based sol
ution (which is always
preferred to look at). :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:dr31d7$p6h$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
> Do you mean when the recordset are returned to the client. Would SET NOCOU
NT
> ON solve your problem?
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> <peteandrews@.hotmail.com> wrote in message
> news:1138029917.821223.30410@.g49g2000cwa.googlegroups.com...
>|||Thanks for the replies, SET NOCOUNT didn't make any difference but
Tibor's suggestion sorted it - I'd left out the INTO clause from the
FETCH statements, I fixed that and it works great, so thanks very much!
Tibor - out of interest (and a desire to get away from using cursors),
I'd be really grateful if you could point me in the direction of where
I can read up on set based solutions?|||A set based solution is more a way of attacking the problem. You can for ins
tance Google these
newsgroup archives and you should find some posts where a single (or a few)
UPDATE, SELECT, etc have
been suggested instead of a proposed or current cursor solution. The idea is
to manage to do the
whole operation in one UPDATE statement (for example) instead of looping and
looking at each row. An
extremely simplified example would be to increase product price by 10% for e
ach product from country
"Spain":
Procedural thinking:
Create a cursor to loop all rows in product table.
For each row, check if it is from Spain.
If it is, execute an UPDATE statement against that product's PK value.
Set based solution:
UPDATE products SET price = price * 1.1 WHERE country = 'Spain'
I know that above is stupidly simple, but the reasoning goes even if you add
a couple of complexity
levels.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<peteandrews@.hotmail.com> wrote in message
news:1138052525.777723.271910@.g14g2000cwa.googlegroups.com...
> Thanks for the replies, SET NOCOUNT didn't make any difference but
> Tibor's suggestion sorted it - I'd left out the INTO clause from the
> FETCH statements, I fixed that and it works great, so thanks very much!
> Tibor - out of interest (and a desire to get away from using cursors),
> I'd be really grateful if you could point me in the direction of where
> I can read up on set based solutions?
>

Cursors in SQL Server

I have read ample technical papers on Cursors. I like to use cursors because it gives you a row by row control in a stored procedure. But most articles I have read advise against the use of cursors (server side).
I typically use read only or Forward Only Cursors. This seems to be the best way to transpose data.
My questions is, is there any guildlines to using cursors? Do you look at the performance of the cursor? If the query runs in 30 seconds and the cursor process is 15 seconds, is that acceptable (based on a million transactions, but filtering 1000-2000 records based on indexed fields.)?
Or is it better to break cursor based steps into multiple queries with insert / update to generate a transposed output? Even if this involves a base query with multiple joins.
Is it better to hammer SQL Server once and then output the results on a row by row, column by column basis, or do multiple queries.
The bottom line is I have been able to provide the required output using cursors, but I just don't want to potentially bog down the system.
Can anyone list specific cases where a cursor was a better method than multiple queries? And particularily with large volume data mining operations.

Thanks
DotNetNow

When you are dealing with large amount of data, cursors become bottle neck to the applications. In an old application I used to handle over 30 million rows in a table. in this kind of situation, cursors fail. In our company we rarely use cursors. Most of the places temp table will solve the purpose of cursors.
Thanks,
Ram

|||Well there is definitely a limitation when you expand the number of records. So I am going to have to rework the flow of the query.
Would like to still hear some comments and thoughts!
DotNetNow|||

ANSI SQL expert Peter Gulutzan who runs test on query engines said when you create a Cursor the query engine says you have asked me to perform task I am not equiped to do so you think I need five loops to perform the task I think I need twenty loops.
Query engines are created to perform SET based tasks a Cursor is Row based it confuses the query engine. Peter Gulutzan and his team gave MySQL stored procs in six months browse his many books at your local book store because before the MySQL job he was RDBMS(relational database management systems) vendor agnostic. Hope this helps.

|||

Don't use cursors. They are a sign of a bad SQL programmer with the mindset of a procedural programmer. SQL is a DECLARTIVE language, and the use of cursors is a throw back to the "old" way of doing things record-by-record. Think about the problem first before sitting down and coding. You'll figure out the right way to do it.

|||Well advise well taken. Cursors are not replace with temp tables and things are well. If they are such a proformance problem, why did Microsoft put in this capability?
Maybe for real complex situations. Just need to slice and dice.
They must have some purpose? But your right if you think about it, you can pretty much do things with temp table(s).
Thanks again for the feedback!
DotNetNow

Cursors and Remote Stored Procedures

given it would appear i can use a cusor as an OUTPUT parameter to a remote
stored procedure, what is the best way to retrieve/return a batch of data
from a stored procedure.On Tue, 8 Nov 2005 07:26:06 -0800, "Martin Samm" <Martin
Samm@.discussions.microsoft.com> wrote:

>given it would appear i can use a cusor as an OUTPUT parameter to a remote
>stored procedure, what is the best way to retrieve/return a batch of data
>from a stored procedure.
Hi Martin,
http://www.sommarskog.se/share_data.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)