Default Non-queried report parameter not updated when project is deployed

Adding a value to a non-queried default report parameter value does not update on the target server after deployment.

To recreate

1. Create a report in Visual Studio and add a report parameter with the following properties:

Multi-value is checked Available values = "From Query" Dataset = [create a dataset that returns a table w/ a Id and Description column] Value field = [the Id field from the table] Label field=[the Description column from the table] Default values = "Non-queried" (add several values the match the IDs from the table so that some of the values in the report dropdown will show up as checked when rendering the report)

2. Build and deploy the report to the reporting server. View the report and verify the specified items are checked in the report parameter.

3. Go back to Visual Studio and add a value to the Non-queried Default values.

4. Build and deploy the report again. View the report. The newly added item is not selected.

Notes

I verified that the newly added ID exists in the rdl file (as xml) on both the development box and the server where the report was deployed. However, when I view the report parameter using Management Studio (connect to the reporting server), the newly added value for the report parameter does not exist. I verified that changes are being deployed by adding new parameters and changing other properties of the parameter. I thought maybe the rdl itself was being cached somehow - I tried restarting IIS, SQL Server, and SQL Reporting services. None worked. Note that running the report on the development box by running the project through Visual Studio DOES reflect the change to the parameter.

Work-arounds

1. Create a dataset for the report that returns a table of the Ids that you want pre-selected. The query could be something like this:

SELECT '4' AS SelectedId
UNION
SELECT '5' AS SelectedId
UNION
SELECT '6' AS SelectedId

2. Delete the report in Management Studio, then redeploy.

I have issue w/ both workarounds because for 1) it is not intuitive and you have to remember to do this for every similar case, and 2) this extra step has to occur each time the report is deployed w/ changes to the report parameter.

Hi there!

I recently discovered the same problem.
The option of deleting the report and do a redeploy is nothing for us, because of already made subscribtions that will also dissapear..

Is there a release comming to fix the problem?

Option 2 (using datasources) is not the most efficient one..
Any words from the developers?

//P?r

|||

My collegue and I are also having this problem. Non-queried defaults don't seem to work.

When will this be fixed?

|||

I have not reported this, other than posting here. Can anyone tell me the best place to submit this to support?

I tried going to http://support.microsoft.com/contactussupport/?ws=support and I received the following message:

"If you think you have found a bug in a Microsoft product, contact our Microsoft Product Support Services department.
(800) MICROSOFT (642-7676)."

I then tried to call the phone number and got a voice menu. After a couple of minutes of searching for the correct option, I gave up.

|||This is by design. The best way to update parameters on server is to delete existing report before deploying a new one.|||This is "by design"? That's not a logical response. If you read the entire thread you will see that deleting the report is not an option for many of us since all subscriptions will be lost. This is a serious bug that should be fixed ASAP.|||

Actually it really is By Design. I understand that you don't agree with the design. The reason for this design choice is that some things associated with the report depend on a certain parameter state, like subscriptions. Therefore, we consider the server's parameter state to be the "master". In order to change the parameter state, you need to do so on the server, either through Report Manager, Management Studio, or SharePoint integrated UI.

The alternative would be to allow republishing to reset the parameter state. In this case, any changes made to the parameters through a server call would be wiped out, since server changes don't cascade back to Report Designer. For example, Mary publishes the RDL with a display prompt of "Enter a parameter value." Some time later, Bob changes the display prompt (using Report Manager) to "Please enter the desired value." Some time after that, Mary republishes the report, this time with a display prompt of "Here is where you enter the user's department name." Which version of the display value is used? Sure, Mary and Bob shouldn't have made conflicting changes, but it happens, and the SW has to make a choice.

It is possible, of course, to enable a comingling of the server state and the project state, but that requires additional complexity and UI to allow conflict resolution. Such a conflict resolution feature is something we haven't implemented to-date. It's possible that this could be implemented in a future release.

A different workaround for the specific case of default values is to modify the default values in Report Manager, Management Studio, or SharePoint integrated UI. That doesn't require deleting the report, and allows you to retain the subscriptions and other bound entities.

Default Non-queried report parameter not updated when project is deployed

Adding a value to a non-queried default report parameter value does not update on the target server after deployment.

To recreate

1. Create a report in Visual Studio and add a report parameter with the following properties:

Multi-value is checked

Available values = "From Query"

Dataset = [create a dataset that returns a table w/ a Id and Description column]

Value field = [the Id field from the table]

Label field=[the Description column from the table]

Default values = "Non-queried" (add several values the match the IDs from the table so that some of the values in the report dropdown will show up as checked when rendering the report)

2. Build and deploy the report to the reporting server. View the report and verify the specified items are checked in the report parameter.

3. Go back to Visual Studio and add a value to the Non-queried Default values.

4. Build and deploy the report again. View the report. The newly added item is not selected.

Notes

I verified that the newly added ID exists in the rdl file (as xml) on both the development box and the server where the report was deployed. However, when I view the report parameter using Management Studio (connect to the reporting server), the newly added value for the report parameter does not exist. I verified that changes are being deployed by adding new parameters and changing other properties of the parameter. I thought maybe the rdl itself was being cached somehow - I tried restarting IIS, SQL Server, and SQL Reporting services. None worked. Note that running the report on the development box by running the project through Visual Studio DOES reflect the change to the parameter.

Work-arounds

1. Create a dataset for the report that returns a table of the Ids that you want pre-selected. The query could be something like this:

SELECT '4' AS SelectedId
UNION
SELECT '5' AS SelectedId
UNION
SELECT '6' AS SelectedId

2. Delete the report in Management Studio, then redeploy.

I have issue w/ both workarounds because for 1) it is not intuitive and you have to remember to do this for every similar case, and 2) this extra step has to occur each time the report is deployed w/ changes to the report parameter.

Hi there!

I recently discovered the same problem.
The option of deleting the report and do a redeploy is nothing for us, because of already made subscribtions that will also dissapear..

Is there a release comming to fix the problem?

Option 2 (using datasources) is not the most efficient one..
Any words from the developers?

//P?r

|||

My collegue and I are also having this problem. Non-queried defaults don't seem to work.

When will this be fixed?

|||

I have not reported this, other than posting here. Can anyone tell me the best place to submit this to support?

I tried going to http://support.microsoft.com/contactussupport/?ws=support and I received the following message:

"If you think you have found a bug in a Microsoft product, contact our Microsoft Product Support Services department.
(800) MICROSOFT (642-7676)."

I then tried to call the phone number and got a voice menu. After a couple of minutes of searching for the correct option, I gave up.

|||This is by design. The best way to update parameters on server is to delete existing report before deploying a new one.|||This is "by design"? That's not a logical response. If you read the entire thread you will see that deleting the report is not an option for many of us since all subscriptions will be lost. This is a serious bug that should be fixed ASAP.|||

Actually it really is By Design. I understand that you don't agree with the design. The reason for this design choice is that some things associated with the report depend on a certain parameter state, like subscriptions. Therefore, we consider the server's parameter state to be the "master". In order to change the parameter state, you need to do so on the server, either through Report Manager, Management Studio, or SharePoint integrated UI.

The alternative would be to allow republishing to reset the parameter state. In this case, any changes made to the parameters through a server call would be wiped out, since server changes don't cascade back to Report Designer. For example, Mary publishes the RDL with a display prompt of "Enter a parameter value." Some time later, Bob changes the display prompt (using Report Manager) to "Please enter the desired value." Some time after that, Mary republishes the report, this time with a display prompt of "Here is where you enter the user's department name." Which version of the display value is used? Sure, Mary and Bob shouldn't have made conflicting changes, but it happens, and the SW has to make a choice.

It is possible, of course, to enable a comingling of the server state and the project state, but that requires additional complexity and UI to allow conflict resolution. Such a conflict resolution feature is something we haven't implemented to-date. It's possible that this could be implemented in a future release.

A different workaround for the specific case of default values is to modify the default values in Report Manager, Management Studio, or SharePoint integrated UI. That doesn't require deleting the report, and allows you to retain the subscriptions and other bound entities.

default data value in gridview?

Hi,

I am trying to set a default value for a date field in my update parameters:

When I try an update with the DefaultValue="<% Now %>", I get this error:
"String was not recognized as a valid DateTime."

The updates work fine if no default value is set and it also works ok if I change the default value to a set string, such as "6/24/06". I've tried using different data sources and datasets, but no luck.

Anyone have any ideas on this?

Thanks!

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:db1ConnectionString%>" ProviderName="<%$ ConnectionStrings:db1ConnectionString.ProviderName%>" SelectCommand="SELECT [ID], [Name], [Date] FROM [Table1]" DeleteCommand="DELETE FROM [Table1] WHERE [ID] = ?" InsertCommand="INSERT INTO [Table1] ([ID], [Name], [Date]) VALUES (?, ?, ?)" UpdateCommand="UPDATE [Table1] SET [Name] = ?, [Date] = ? WHERE [ID] = ?"> <DeleteParameters> <asp:Parameter Name="ID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Date" Type="DateTime" DefaultValue="<%Now()%>" /> <asp:Parameter Name="ID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="ID" Type="Int32" /> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Date" Type="DateTime" /> </InsertParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" /> <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" /> <asp:BoundField DataField="Date" HeaderText="Date" ReadOnly="true" SortExpression="Date" /> </Columns> </asp:GridView>

I'm not sure you're using the right way to call the function in page script, but it works if I set the default value in the code behind:

SqlDatasSource1.SelectParameters.Add("@.OD", TypeCode.DateTime, DateTime.Now.ToString());

|||Great, thanks! I don't know why I didn't think to do it that way.

Just in case anyone else runs this, I ended up using an ObjectDataSource and added this event handler to set the parameters...it works great:

Protected Sub ObjectDataSource1_Inserting(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.ObjectDataSourceMethodEventArgs)Handles ObjectDataSource1.Inserting e.InputParameters("Date") = DateTime.NowEnd Sub Protected Sub ObjectDataSource1_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.ObjectDataSourceMethodEventArgs)Handles ObjectDataSource1.Updating e.InputParameters("Date") = DateTime.NowEnd Sub

|||I'm glad to hear that you managed to make it works BTW someone told me the reason why your code in page script (set DefaultValue for a parameter a value returned by a function) didn't work is that the Parameter elements can not be boundsql

Decrease Costs in INSERTS/UPDATES Queries.

Hi, :)

I wanna know how can I do to reduce the Costs of INSERT/UPDATE Queries that I have, but in some Traces of SQL Profiler, I see that this queries taken most performance of my server.

Remember that all tables that I have are Indexed Tables with the default value of Fill Factor.

Tanks,The more indexes you have the longer an insert will take.
On heavy insert systems (telecoms for instance) I only ever have a single clustered index on the identity column of the call table.

For updates try not to update indexed fields, also do not extend the length of any fields then you should get a direct update in place (as long as you don't have triggers or replication enabled on the table) which is the fastest type (hope that statement is still valid).

Otherwise make sure the transaction log is on a different device to the data and that you are not getting contention on the table.

Declaring and using an UPDATE CURSOR with SQL SERVER

In databases like Oracle or Sybase, if you have multiple records returned
from a query and want to update them, you declare an update cursor.
While I have read the "Help" files available in Enterprise, I have not
figured out the syntax for declaring and using an UPDATE CURSOR.
The query I am running is like this:
UPDATE A
SET A.Field1 = (SELECT B.Field1
FROM B INNER JOIN A ON A.id = B.id)
The tables have a many to one relationship on id.
I do not know if when I declare the cursor, if I put the whole update
statement in it... I do not know if when I use an update cursor, I have to
fetch next... And beyond that, if I do get the syntax and put it into a
stored procedure, how do I execute the query from within Enterprise Manager
(I am not writing code to call the procedure, I just want to execute it
against the table. I would execute it by just creating a query, but the
cursor format is not recognized in the query pane.)
I am completely new to SQL Server but not to databases.
What I am looking for is just a simple example of declaring and using an
UPDATE CURSOR.
Any help you can provide would be appreciated. Thanks!Hi
you can use somewhat this
Update
Set A.Field1 = B.Field
From
Where A.id = B.i
This statement this convert to UPDATE CURSOR in Oracle
Hermilson Tinoco.|||The simple syntax for declaring a cursor which allows updates is doc'd in
SQL books online , search for "Declare cursor", but it looks like
declare mycur Cursor for <Select statement> for update
you can then open it, fetch rows, and update table set col = value where
current of mycur
Generally in SQL, we try to avoid cursors and use relational update
statements whenever possible, because cursors (generally) do not perform as
well.
hope this helps.
"Carol Berry" <carol@.123marbella.com> wrote in message
news:OAGSMlx6DHA.2404@.TK2MSFTNGP11.phx.gbl...
> In databases like Oracle or Sybase, if you have multiple records returned
> from a query and want to update them, you declare an update cursor.
> While I have read the "Help" files available in Enterprise, I have not
> figured out the syntax for declaring and using an UPDATE CURSOR.
> The query I am running is like this:
> UPDATE A
> SET A.Field1 => (SELECT B.Field1
> FROM B INNER JOIN A ON A.id = B.id)
> The tables have a many to one relationship on id.
> I do not know if when I declare the cursor, if I put the whole update
> statement in it... I do not know if when I use an update cursor, I have
to
> fetch next... And beyond that, if I do get the syntax and put it into a
> stored procedure, how do I execute the query from within Enterprise
Manager
> (I am not writing code to call the procedure, I just want to execute it
> against the table. I would execute it by just creating a query, but the
> cursor format is not recognized in the query pane.)
> I am completely new to SQL Server but not to databases.
> What I am looking for is just a simple example of declaring and using an
> UPDATE CURSOR.
> Any help you can provide would be appreciated. Thanks!
>

Declaring and using an UPDATE CURSOR with SQL SERVER

In databases like Oracle or Sybase, if you have multiple records returned
from a query and want to update them, you declare an update cursor.
While I have read the "Help" files available in Enterprise, I have not
figured out the syntax for declaring and using an UPDATE CURSOR.
The query I am running is like this:
UPDATE A
SET A.Field1 =
(SELECT B.Field1
FROM B INNER JOIN A ON A.id = B.id)
The tables have a many to one relationship on id.
I do not know if when I declare the cursor, if I put the whole update
statement in it... I do not know if when I use an update cursor, I have to
fetch next... And beyond that, if I do get the syntax and put it into a
stored procedure, how do I execute the query from within Enterprise Manager
(I am not writing code to call the procedure, I just want to execute it
against the table. I would execute it by just creating a query, but the
cursor format is not recognized in the query pane.)
I am completely new to SQL Server but not to databases.
What I am looking for is just a simple example of declaring and using an
UPDATE CURSOR.
Any help you can provide would be appreciated. Thanks!Hi.
you can use somewhat this:
Update A
Set A.Field1 = B.Field1
From B
Where A.id = B.id
This statement this convert to UPDATE CURSOR in Oracle.
Hermilson Tinoco.|||The simple syntax for declaring a cursor which allows updates is doc'd in
SQL books online , search for "Declare cursor", but it looks like
declare mycur Cursor for <Select statement> for update
you can then open it, fetch rows, and update table set col = value where
current of mycur
Generally in SQL, we try to avoid cursors and use relational update
statements whenever possible, because cursors (generally) do not perform as
well.
hope this helps.
"Carol Berry" <carol@.123marbella.com> wrote in message
news:OAGSMlx6DHA.2404@.TK2MSFTNGP11.phx.gbl...

quote:

---

> In databases like Oracle or Sybase, if you have multiple records returned
> from a query and want to update them, you declare an update cursor.
> While I have read the "Help" files available in Enterprise, I have not
> figured out the syntax for declaring and using an UPDATE CURSOR.
> The query I am running is like this:
> UPDATE A
> SET A.Field1 =
> (SELECT B.Field1
> FROM B INNER JOIN A ON A.id = B.id)
> The tables have a many to one relationship on id.
> I do not know if when I declare the cursor, if I put the whole update
> statement in it... I do not know if when I use an update cursor, I have

---

to

quote:

---

> fetch next... And beyond that, if I do get the syntax and put it into a
> stored procedure, how do I execute the query from within Enterprise

---

Manager

quote:

---

> (I am not writing code to call the procedure, I just want to execute it
> against the table. I would execute it by just creating a query, but the
> cursor format is not recognized in the query pane.)
> I am completely new to SQL Server but not to databases.
> What I am looking for is just a simple example of declaring and using an
> UPDATE CURSOR.
> Any help you can provide would be appreciated. Thanks!
>

---

|||Thank you all for your help. If I need more help, I will post a more
complete problem. I am new to "posting" issues, too.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Decimal Data Type losing scale?

I'm trying to update a table that has decimal values. They are defined at
precision of 15 and scale of 2.
When I use a T-Sql update query, I'm sending a value "with pennies", but the
table is only reflecting the integer portion. I've even tried
UPDATE (myTableName)
SET myCost = CAST(@.Cost AS decimal(15,2))
without success.
When I step through the code of my VB.NET program and view the value of item
I've added to the parameters collection of my update query command object, I
DO SEE the pennies. When the query has executed, they aren't in the table.
I CAN TYPE the pennies into the record in the table with Enterprise Mgr. An
d
I can retrieve them with my program. But I can't send new values with
pennies and get them respected in the new table values.
I have seen in Books on Line that we are supposed to explicitly CAST our
decimal values. But shouldn't this take care of it?Hello Q,
When you run profiler what do you see the values being sent as? If you have
your precision and scale not matching exactly in your VB.NET application
it can cause it to send it incorrectly to the database.
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> I'm trying to update a table that has decimal values. They are
> defined at precision of 15 and scale of 2.
> When I use a T-Sql update query, I'm sending a value "with pennies",
> but the
> table is only reflecting the integer portion. I've even tried
> UPDATE (myTableName)
> SET myCost = CAST(@.Cost AS decimal(15,2))
> without success.
> When I step through the code of my VB.NET program and view the value
> of item I've added to the parameters collection of my update query
> command object, I DO SEE the pennies. When the query has executed,
> they aren't in the table. I CAN TYPE the pennies into the record in
> the table with Enterprise Mgr. And I can retrieve them with my
> program. But I can't send new values with pennies and get them
> respected in the new table values.
> I have seen in Books on Line that we are supposed to explicitly CAST
> our decimal values. But shouldn't this take care of it?
>|||Is it possible that you've failed to specify precision and scale
for your decimal parameter? The default precision and scale for
a decimal parameter is precision 18, scale 0. If this doesn't seem
to help, could you post the relevant VB.NET code dealing with
the parameter?
Steve Kass
Drew University
Q Johnson wrote:

>I'm trying to update a table that has decimal values. They are defined at
>precision of 15 and scale of 2.
>When I use a T-Sql update query, I'm sending a value "with pennies", but th
e
>table is only reflecting the integer portion. I've even tried
> UPDATE (myTableName)
> SET myCost = CAST(@.Cost AS decimal(15,2))
>without success.
>When I step through the code of my VB.NET program and view the value of ite
m
>I've added to the parameters collection of my update query command object,
I
>DO SEE the pennies. When the query has executed, they aren't in the table.
>I CAN TYPE the pennies into the record in the table with Enterprise Mgr. A
nd
>I can retrieve them with my program. But I can't send new values with
>pennies and get them respected in the new table values.
>I have seen in Books on Line that we are supposed to explicitly CAST our
>decimal values. But shouldn't this take care of it?
>
>

Debugging stored procedures

I have complete error handling and printing the error after every insert or update statements or after calling another procedure.

But somehow when executing the proc it is not printing the error.

The query analyzer shows a general message 'Query batch completed with errors'

All the logic seems to be working properly, but this message is bothering me. Why is this message displayed if everything is run correctly [or] is something wrong ?

Example:

[code]

/*************************************************************
** Error Handling
**************************************************************/

SELECT @.rowcount = @.@.rowcount
,@.error = @.@.error
,@.short_msg = 'Error Creating MCTM. - AG_SP_FAC_MCTN_INSERT'
,@.long_msg = 'Error in executing proc AG_SP_FAC_MCTN_INSERT'
,@.resolution_msg = 'Stored procedure error. Contact Technical Support for fix'
,@.log_cd = 'AG.CONV.ERR' + convert(char,@.exec_seq_no)
,@.log_level = 'O'
,@.log_severity = 3

IF (@.error <> 0)
BEGIN

EXEC amgrp_conv..AG_SP_LOG
@.LOG_CD = @.log_cd
,@.LOG_DTM = @.log_dtm
,@.LOG_LEVEL = @.log_level
,@.LOG_SEVERITY = @.log_severity
,@.APP_NAME = @.app_name
,@.SHORT_MSG = @.short_msg
,@.LONG_MSG = @.long_msg
,@.RESOLUTION_MSG = @.resolution_msg
,@.MAIN_STORED_PROC_NAME = @.main_stored_proc_name
,@.STEP_STORED_PROC_NAME = @.step_stored_proc_name
,@.SYBASE_CD = @.error

PRINT 'ERROR=' + convert(varchar(255),@.error)

ROLLBACK TRANSACTION TRAN_PRAC_PAR
CLOSE prac_par_cursor
DEALLOCATE prac_par_cursor
RETURN @.failure

END

[/code]

IF (@.@.error <> 0) --this should be @.@.error @.error from previous statement is unreliable
BEGIN

EXEC amgrp_conv..AG_SP_LOG
@.LOG_CD = @.log_cd
,@.LOG_DTM = @.log_dtm
,@.LOG_LEVEL = @.log_level
,@.LOG_SEVERITY = @.log_severity
,@.APP_NAME = @.app_name
,@.SHORT_MSG = @.short_msg
,@.LONG_MSG = @.long_msg
,@.RESOLUTION_MSG = @.resolution_msg
,@.MAIN_STORED_PROC_NAME = @.main_stored_proc_name
,@.STEP_STORED_PROC_NAME = @.step_stored_proc_name
,@.SYBASE_CD = @.error

PRINT 'ERROR=' + convert(varchar(255),@.error)

ROLLBACK TRANSACTION TRAN_PRAC_PAR
CLOSE prac_par_cursor
DEALLOCATE prac_par_cursor
RETURN @.failure

END

|||

Not quite getting it.

Does this mean @.@.error may not return anything. My understanding is it will be '0' if success and any other number if its an error

In other words, the following doesn't work ?

declare @.error int

select @.error = @.@.error

if (@.error <> 0)

begin

end

|||

QUOTED:

Not quite getting it.

Does this mean @.@.error may not return anything. My understanding is it will be '0' if success and any other number if its an error

In other words, the following doesn't work ?

declare @.error int

select @.error = @.error + (other select clause ) --<-- what if the error lies in here

if (@.error <> 0)

begin

end

|||

thanks joeydj,

ok i see...

@.@.error is for select statements too ?

i can check the selects, but its a standard select as shown above and there seems to be no error there.

|||

thats a wild guess anyway.

|||

can you please check if this line is valid

,@.log_cd = 'AG.CONV.ERR' + convert(char,@.exec_seq_no)

|||

still not getting it try this. this one should do it.

hahaha

declare @.error int

select @.error=0

SELECT @.rowcount = @.@.rowcount
,@.error = @.@.error
,@.short_msg = 'Error Creating MCTM. - AG_SP_FAC_MCTN_INSERT'
,@.long_msg = 'Error in executing proc AG_SP_FAC_MCTN_INSERT'
,@.resolution_msg = 'Stored procedure error. Contact Technical Support for fix'
,@.log_cd = 'AG.CONV.ERR' + convert(char,@.exec_seq_no)
,@.log_level = 'O'
,@.log_severity = 3

IF (@.error <> 0)
BEGIN

EXEC amgrp_conv..AG_SP_LOG
@.LOG_CD = @.log_cd
,@.LOG_DTM = @.log_dtm
,@.LOG_LEVEL = @.log_level
,@.LOG_SEVERITY = @.log_severity
,@.APP_NAME = @.app_name
,@.SHORT_MSG = @.short_msg
,@.LONG_MSG = @.long_msg
,@.RESOLUTION_MSG = @.resolution_msg
,@.MAIN_STORED_PROC_NAME = @.main_stored_proc_name
,@.STEP_STORED_PROC_NAME = @.step_stored_proc_name
,@.SYBASE_CD = @.error

PRINT 'ERROR=' + convert(varchar(255),@.error)

ROLLBACK TRANSACTION TRAN_PRAC_PAR
CLOSE prac_par_cursor
DEALLOCATE prac_par_cursor
RETURN @.failure

END

Debug - How to Step into

I have a stored procedure, in the name Proc_UpdateProductionShare. This
procedure has got a select statement and a couple of update statements. I
wish to debug this stored procedure. By debug i mean that i wish to step
into the procedure code line by line and debug it. I also have used some
local variable and setting the values to these variables based on the
actions in the procedure. I also need to trace the value of these local
variables.
For this i had used the T-SQL Debugger. In Query Analyzer i had opened the
Object Browser. In the object browser i had selected the database in which
the stored procedure resides. Then i had selected the particular stored
procedure and right clicked on the stored procedure and selected the "Debug"
option. A small dialog window opened mentioning the stored procedure i had
selected and also listing the parameters for this stored procedure, it's
data type, type of the parameter (input or output) and the value text field.
Since in this particular procedure i had a input parameter (int datatype) to
be passed on, it was listed. I specified the value for this parameter and
then clicked "Execute" button.
This opened the source code of the procedure, in a new pane at the top. But
when press "F5" or click the "Go" button in this pane's toolbar, the
procedure executes immediately and completes execution. Iam not able to step
into each line of the stored procedure and execute it one by one and
checking the result of the execution of each and every line. Then i also
tried by setting breakpoints at 2, 3 lines of the code and then clicked the
"Go" button. But this time too the procedure completed execution without
stopping execution at the breakpoints.
All i need is to use the "F11 (step into)" and "F10 (step out)" buttons to
check the execution. These buttons were enabled only for a fraction of
second after the "Go" button is clicked. But before i start using them, the
procedure execution completes and these buttons get disabled. How do i do
this task '
I searched for help in BOL. But i was not able to find any useful info on
this part. Can you tell me the steps to be followed to debug a stored
procedure or mention some links where, i can get info regarding this.
Thanks in Advance.
With Regard,
SQL UserThere's a troubleshooting section about the debugger in Books Online. Did
you find it? Also, make sure you have the updated Books Online:
http://www.microsoft.com/SQL/techinfo/productdoc/2000/default.asp
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"MS User" <sqlman@.sql.com> wrote in message
news:eKeMw%23KpDHA.2776@.tk2msftngp13.phx.gbl...
> I have a stored procedure, in the name Proc_UpdateProductionShare. This
> procedure has got a select statement and a couple of update statements. I
> wish to debug this stored procedure. By debug i mean that i wish to step
> into the procedure code line by line and debug it. I also have used some
> local variable and setting the values to these variables based on the
> actions in the procedure. I also need to trace the value of these local
> variables.
> For this i had used the T-SQL Debugger. In Query Analyzer i had opened the
> Object Browser. In the object browser i had selected the database in which
> the stored procedure resides. Then i had selected the particular stored
> procedure and right clicked on the stored procedure and selected the
"Debug"
> option. A small dialog window opened mentioning the stored procedure i had
> selected and also listing the parameters for this stored procedure, it's
> data type, type of the parameter (input or output) and the value text
field.
> Since in this particular procedure i had a input parameter (int datatype)
to
> be passed on, it was listed. I specified the value for this parameter and
> then clicked "Execute" button.
> This opened the source code of the procedure, in a new pane at the top.
But
> when press "F5" or click the "Go" button in this pane's toolbar, the
> procedure executes immediately and completes execution. Iam not able to
step
> into each line of the stored procedure and execute it one by one and
> checking the result of the execution of each and every line. Then i also
> tried by setting breakpoints at 2, 3 lines of the code and then clicked
the
> "Go" button. But this time too the procedure completed execution without
> stopping execution at the breakpoints.
> All i need is to use the "F11 (step into)" and "F10 (step out)" buttons to
> check the execution. These buttons were enabled only for a fraction of
> second after the "Go" button is clicked. But before i start using them,
the
> procedure execution completes and these buttons get disabled. How do i do
> this task '
> I searched for help in BOL. But i was not able to find any useful info on
> this part. Can you tell me the steps to be followed to debug a stored
> procedure or mention some links where, i can get info regarding this.
> Thanks in Advance.
> With Regard,
> SQL User
>|||It's already going when that last window opens
Note the yellow arrow is present.
So just hit StepOver to run just the first line.
Bye,
Delbert Glass|||Thanks for pointing to the right direction.
It was permission to execute the SP_SDIDEBUG extended procedure AND
It is disabled for security reasons. To enable Transact-SQL Debugger for
legacy clients, a member of the sysadmins server role, such as "sa," must
explicitly enable debugging by running the following code:
Exec sp_sdidebug 'legacy_on'
You must repeat this procedure whenever you restart the server. It is not
recommended to use Transact-SQL Debugger on a production server.
To disable Transact-SQL Debugger for legacy clients after you enable it, as
a member of the sysadmins server role, you can run the following code:
Exec sp_sdidebug 'legacy_off'
HTH
Shamim
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23q2yjVLpDHA.2592@.TK2MSFTNGP10.phx.gbl...
> There's a troubleshooting section about the debugger in Books Online. Did
> you find it? Also, make sure you have the updated Books Online:
> http://www.microsoft.com/SQL/techinfo/productdoc/2000/default.asp
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "MS User" <sqlman@.sql.com> wrote in message
> news:eKeMw%23KpDHA.2776@.tk2msftngp13.phx.gbl...
> > I have a stored procedure, in the name Proc_UpdateProductionShare. This
> > procedure has got a select statement and a couple of update statements.
I
> > wish to debug this stored procedure. By debug i mean that i wish to step
> > into the procedure code line by line and debug it. I also have used some
> > local variable and setting the values to these variables based on the
> > actions in the procedure. I also need to trace the value of these local
> > variables.
> >
> > For this i had used the T-SQL Debugger. In Query Analyzer i had opened
the
> > Object Browser. In the object browser i had selected the database in
which
> > the stored procedure resides. Then i had selected the particular stored
> > procedure and right clicked on the stored procedure and selected the
> "Debug"
> > option. A small dialog window opened mentioning the stored procedure i
had
> > selected and also listing the parameters for this stored procedure, it's
> > data type, type of the parameter (input or output) and the value text
> field.
> > Since in this particular procedure i had a input parameter (int
datatype)
> to
> > be passed on, it was listed. I specified the value for this parameter
and
> > then clicked "Execute" button.
> >
> > This opened the source code of the procedure, in a new pane at the top.
> But
> > when press "F5" or click the "Go" button in this pane's toolbar, the
> > procedure executes immediately and completes execution. Iam not able to
> step
> > into each line of the stored procedure and execute it one by one and
> > checking the result of the execution of each and every line. Then i also
> > tried by setting breakpoints at 2, 3 lines of the code and then clicked
> the
> > "Go" button. But this time too the procedure completed execution without
> > stopping execution at the breakpoints.
> >
> > All i need is to use the "F11 (step into)" and "F10 (step out)" buttons
to
> > check the execution. These buttons were enabled only for a fraction of
> > second after the "Go" button is clicked. But before i start using them,
> the
> > procedure execution completes and these buttons get disabled. How do i
do
> > this task '
> >
> > I searched for help in BOL. But i was not able to find any useful info
on
> > this part. Can you tell me the steps to be followed to debug a stored
> > procedure or mention some links where, i can get info regarding this.
> >
> > Thanks in Advance.
> >
> > With Regard,
> > SQL User
> >
> >
>