Cannot Debug Activated CLR Stored Procedure
Jan 11, 2007
Hi
I'm having a problem debugging (with VS2005,) a CLR Stored Procedure which is activated on a queue. I know it is working because it is consuming and processing messages when one appears in the queue.
I can debug it 'manually' by either right-clicking on the SP within VS2005 and selecting 'Step into Stored Procedure' or by attaching to the SQL Server Management Studio process, setting a breakpoint and executing the stored procedure from a Management Studio query window.
However, if I send a message to my queue within Management Studio, my breakpoints are NOT being hit within VS2005, but the message IS reaching my queue and it IS being processed by the SP.
Can anyone help?
Thanks
View 3 Replies
ADVERTISEMENT
Nov 23, 2006
Hi guys!
Is it possible for a stored procedure to be activated more than once even if a message is sent only once? After sending the message, I check the sys.dm_broker_activated_tasks and see that it activates more than once. Is there a way for me to prevent this from happening?
Thanks a lot! :)
View 4 Replies
View Related
Sep 4, 2007
Hi I have a couple of questions to understand better Service Broker..
Every time I alter the activation Stored Procedure on the receiving queue I also execute the:
alter database set new_broker rollback
1) can somebody explain me in a detailed way why I have to do that? what happen inside SQL Server when there is a Stored Procedure activated on a queue??
Also link to white papers, resouces on so on are appreciated.
My Activation Stored Procedure calls other stored procedure.
2) If ONLY one of the nested stored procedures changes I have to execute the command:
alter database set new_broker rollback ???
I think that the answer to the first question will answer the second one..
Thank you for any helps!
Marina B.
View 3 Replies
View Related
Apr 26, 2006
I've read Remus' article on Fire and Forget tactics when it comes to ending conversations, and I have to admit I am guilty of sending a message to a service and immediately ending the conversation. I've set up a stored procedure to end the conversation on the initiator queue, but I'm guessing it's never being fired because I am not ending the conversation in the target activated stored proc, which is a CLR stored proc.
Can you tell me how to end the conversation from a CLR stored proc?
Now that I think of it none of the code I use in my TSQL activated stored procs to handle different message types and error-checking is done in the CLR stored proc.
Thanks
View 5 Replies
View Related
Oct 4, 2006
hello,I want to know if it is possible to debug a stored procedure using visual studio 2003 and SQL server 2000.if yes can u help me how to do this?Thanks.
View 5 Replies
View Related
May 16, 2008
Hi,I am using SQL Server Express bundled with Visual Studio 2008 on top of a ASP.NET/C# web application.I have written a stored procedure that appears to behave differently when stepping into the procedure than it does when called from my application, even though the parameter values are apparently identical. I need to debug the parameter values from within the stored procedure itself to see what the difference is. But how can I debug the stored procedure when it is called from the application?I followed the instructions at this page, but VS2008 does not stop within the procedure as expected. It seems I foolishly downloaded VS2008 Professional Edition, so these instructions apparently do not apply to that version (!!).So I thought I would try debugging the parameter values by writing them to a file. I used the procedure on this page to write to an arbitrary file, but every time I try to call it I get the following error: Error: SQL Server blocked access to procedure 'sys.sp_OACreate' of
component 'Ole Automation Procedures' because this component is turned
off as part of the security configuration for this server. A
system administrator can enable the use of 'Ole Automation Procedures'
by using sp_configure. For more information about enabling 'Ole
Automation Procedures', see "Surface Area Configuration" in SQL Server
Books Online. I opened the SQL Server Surface Area Configuration and enabled the "OLE Automation". I restarted the SQL Server service and VS2008, but I still got the same error, even though "OLE Automation" remains enabled for SQL Server 2005. So I tried issuing the following commands: sp_configure 'show advanced options', 1; GO RECONFIGURE;GOsp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE;GO But if I issue those commands from within a stored procedure, or even an SQL Query window, I get the following error:SQL Execution Error:Executed SQL Statement: sp_configure 'show advanced options', 1; GO RECONFIGURE;GOsp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE;GOError Source: .Net SqlClient Data ProviderError Message: Incorrect syntax near 'GO'.Please - I can't be the only person who is unable to debug his stored procedures. How can I either enable Application Debugging, or enable "OLE Automation"?I appreciate any help you can offer!
View 3 Replies
View Related
May 18, 2005
If I right-click on a stored procedure, I get the option to "step into
stored procedure". However, no little yellow arrow appears that would
show which line it's on. Debug/Continue is grayed out. Any ideas?
NOTE: I tried to do this in SQL Query Analyzer. Same thing. Just seems like nothing is happening.
View 8 Replies
View Related
Jan 18, 2006
I created a stored procedure that is referenced by a SQLDataSource
& DetailView on a web page. I tested the stored procedure in Query
Analyzer & everyting looks good.
When I get to the page to display the detailview, nothing is displayed.
I ran SQL Profiler, to see what was happening & the stored
procedure isn't being called.
I checked & rechecked the references to the SQLDataSource & even tested them in the configuration wizard.
I replaced the stored procedure with the query & it worked, except I had to 'hardcode' the variable values.
The stored procedure does return error results when they occur - how can I intercept them?
How can I determine what is happening with this stored procedure? Is there a way to trace it other than with SQL Profiler?
Thanks in advance.
OldSam
View 3 Replies
View Related
Apr 21, 2006
Hi Friends, I have a worst doubt, plese excuse me. and help me to find solution for my doubt.
How can i Debug my Sql Stored procedure. and where to do this.
View 1 Replies
View Related
Jan 10, 2003
Debugging Stored Procedure
hi ...
How to debug a stored procedure line by line.
As i know there is one method of debugging the stored procedure by rightclicking on stored procedure in ObjectBrowser and click Debug. But it is not working properly .
Is there any other ways/methods of debugging a stored procedure
View 3 Replies
View Related
Nov 6, 1998
Does anybody know how to debug stored procedure ,
especially big stored procedure with cursor?
Any existing tools? I'v heard it's easy in SQL Server 7
but we don't have it.
Please, any advice will be very appreciated.
Thanks.
View 3 Replies
View Related
Mar 20, 2007
Dear Sir,
I am from ASP.NET back ground now I am working in SQL Server 2005. I want to debug my stored procedure after coding end. In SQL 2000 there is an option of debug right click on the stored procedure but No such option present in SQL Server 2005 so I am unable to debug my Stored Procedure when any error occur.
Can you please guide me?
Regards
Sandeep Mishra
Bangalore
Have A Nice Day...!!!
View 10 Replies
View Related
Mar 8, 2008
Hi friends,
I am santhosh, working as a Sql developer.
I have created Stored Procedure in 200 lines.
After compilation its ok then created.
But the functionality is not good so how to debugging my stored procedure in step by step.
How to insert a break point.
I am using SQL server 2005.
I am not using any front end application like Dotnet using only SQL. (Front end department is different)
Please help me, line by line checking.
Advance Thanks,
Yours Santhosh.
Santhosh
View 4 Replies
View Related
Jul 20, 2005
Hi thereHow can I debug Stored Procedure?Thanks
View 1 Replies
View Related
Dec 1, 2006
I have a initiator and a target service broker peer.
Both are controlled by a C# unit test. The initiator uses the Microsoft.Samples.SqlServer class. The target service uses stored procedure activation.
Sending a message from the initiator to the target, saves the content of the message, along with its conversation handle in the target's database specific table.
The unit test needs - at a later time - to instruct the target to send a message back on the same conversation handle to the initiator service.
For this the C# unit test creates a Conversation off of the saved conversation handle:
Service client = new Service("cleintservicename", conn, tran);
Conversation dialog = null;
dialog = new Conversation(client, convHandle);
Sending the message on this dialog generates an error "Message body: <Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8495</Code><Description>The conversation has already been acknowledged by another instance of this service.</Description></Error>".
Is the error due to the fact that a service - using the activated stored procedure already picked up the conversation, so that a new reference to the service can not be created through the Service class in CLR?
If so, I might need then to skip the activated stored procedure in favor or a CLR service, alltogether?
Any help - greatly appreciated.
View 7 Replies
View Related
Jun 15, 2006
How can I debug a Stored Procedure called by an ASP.Net application using SqlCommand.ExecuteScalar function?
View 1 Replies
View Related
Mar 20, 2007
Dear Sir,
I am from ASP.NET back ground now I am working in SQL Server 2005. I want to debug my stored procedure after coding end. In SQL 2000 there is an option of debug right click on the stored procedure but No such option present in SQL Server 2005 so I am unable to debug my Stored Procedure when any error occur.
Can you please guide me?
Regards
Sandeep Mishra
Bangalore
View 4 Replies
View Related
Aug 7, 2007
I am trying to debug the SQL Server Stored Procedure from .net 2005.
The process is as :
After setting Breakpoints at SP , it cant stop at that break point.
If anybody konws please write in detail.
Shyam.
View 3 Replies
View Related
Nov 26, 2007
hi, i'm trying to debug a stored procedure (in sql server 2005) with visual studio 2005, but all my tables and procedures are inside a schema and when i configure my login in sql server for being in sysadmin role, it dosn't map the schema so i need to write the schema before the tables and procedures, and i don't need it in that way...
how can i do to have a login in sysadmin role and a schema maping in than login???,
thanks ....
View 1 Replies
View Related
Nov 28, 2006
hello world,
how cani debug a stored procedure under sql server 2005?
View 2 Replies
View Related
Aug 17, 2007
hi
i have developed asp.net application which calls web service of localsystem. it also contains logic for interaction with database. we are also creating connection string through coding not from web.config. i want to debug stored procedure which is called by webmethod. then pls suggess me how can i debug this stored procedure from asp.net which is called in local webservice???
if any one have solve pls help...
atul
View 1 Replies
View Related
Jan 18, 2007
Hi all,
I really need to know that
" How to debug stored procedure in SQL server 2005 ?"
please help me to solve my problem ?
regards
sujithf
View 23 Replies
View Related
Oct 17, 2007
I want to know if I can debug stored procedures remotely with the SA account.
I can not have Active Directory in my system right now.
Thanks
View 1 Replies
View Related
Jul 16, 2005
I have a stored procedure into which a bit type of parameter called '@activated' is being passed. This stored procedure gets all the users matching the condition :
Activated = isnull(@activated, -1)
In users table, one of the columns is 'Activated' of bit type.
The strange thing happening here is that if @activated is null, then the above condition is true for all rows in users table, even though we know that the column Activation is either 1 or 0 only and never -1.Can someone explain why this strange thing is happening?
View 2 Replies
View Related
Jan 18, 2006
I need some help with a stored procedure to insert multiple rows into a join table from a checkboxlist on a form. The database structure has 3 tables - Products, Files, and ProductFiles(join). From a asp.net formview users are able to upload files to the server. The formview has a products checkboxlist where the user selects all products a file they are uploading applies too. I parse the selected values of the checkboxlist into a comma delimited list that is then passed with other parameters to the stored proc. If only one value is selected in the checkboxlist then the spproc executed correctly. Also, if i run sql profiler i can confirm that the that asp.net is passing the correct information to the sproc:
exec proc_Add_Product_Files @FileName = N'This is just a test.doc', @FileDescription = N'test', @FileSize = 24064, @LanguageID = NULL, @DocumentCategoryID = 1, @ComplianceID = NULL, @SubmittedBy = N'Kevin McPhail', @SubmittedDate = 'Jan 18 2006 12:00:00:000AM', @ProductID = N'10,11,8'
Here is the stored proc it is based on an article posted in another newsgroup on handling lists in a stored proc. Obviously there was something in the article i did not understand correctly or the author left something out that most people probably already know (I am fairly new to stored procs)
CREATE PROCEDURE proc_Add_Product_Files_v2/*Declare variables for the stored procedure. ProductID is a varchar because it will receive a comma,delimited list of values from the webform and then insert a rowinto productfiles for each product that the file being uploaded pertains to. */@FileName varchar(150),@FileDescription varchar(150),@FileSize int,@LanguageID int,@DocumentCategoryID int,@ComplianceID int,@SubmittedBy varchar(50),@SubmittedDate datetime,@ProductID varchar(150)
ASBEGIN
DECLARE @FileID INT
SET NOCOUNT ON
/*Insert into the files table and retrieve the primary key of the new record using @@identity*/ INSERT INTO Files (FileName, FileDescription, FileSize, LanguageID, DocumentCategoryID, ComplianceID, SubmittedBy, SubmittedDate) Values (@FileName, @FileDescription, @FileSize, @LanguageID, @DocumentCategoryID, @ComplianceID, @SubmittedBy, @SubmittedDate)
Select @FileID=@@Identity
/*Uses dynamic sql to insert the comma delimited list of productids into the productfiles table.*/ DECLARE @ProductFilesInsert varchar(2000)
SET @ProductFilesInsert = 'INSERT INTO ProductFiles (FileID, ProductID) SELECT ' + CONVERT(varchar,@FileID) + ', Product1ID FROM Products WHERE Product1ID IN (' + @ProductID + ')' exec(@ProductFilesInsert) EndGO
View 4 Replies
View Related
Oct 21, 2007
Hi all,
I couldn't debug SQL Server by "Step into Stored Procedure" on Server Explorer of VS2008 (or VS2005) to SQL 2005 Developer on remote Windows Server 2003 machine, it allway issue exception "Unable to start T-SQL Debugging. Could not attach to SQL Server process on 'Server'. Click Help for more information"
1) The environment:
The Client: Windows XP SP2 (WORKGROUP)
Visual Studio 2008 (or VS2005)
The SQL Server Machine: Windows 2003 Server Sp1 (DOMAIN)
SQL Server 2005 Developer
2) User account and Permission login:
I create the same user account for both Client and Domain Server with the same password, i also add that user to "Administrators" group in both machine.
At the SQL Server on Server machine, i added that account to ServerSecurityLogin with 'sysadmin' role already
3) Connection and authentication:
I used "Windows Authentication" for my connection to SQL server, and i checked sure my account of the connection by SQL command
SELECT SYSTEM_USER,
IS_SRVROLEMEMBER ('sysadmin')
4) Firewall:
I checked firewall like MSDN helping (i also tried to test by turn off firewall in both machine)
5) Visual Studio Remote Debugger:
I read "How to: Enable SQL Server 2005 Debugging" on MSDN with comment "The SQL Server can run on the same machine as the application or on a remote machine. If you are debugging T-SQL code only, then no remote setup is required."
so i didn't config Visual Studio Remote Debugger any thing.
Note: If i "Step Into Store Procedure" at Server locally, it works okey, so on at my PC client locally. But if i move debugging from my client to my Server, it occur error "Unable to start T-SQL Debugging. Could not attach to SQL Server process on 'Server'. Click Help for more information"???
If i execute store procedure on Server Explore, it works okey!
Please help me to find out what problem is???
Thanks,
Haiasc
View 2 Replies
View Related
Aug 2, 2006
Hi There
2 Questions :
1. Almost in every SB example you will see this sql :
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP (1)
@MessageType = message_type_name,
@Message = message_body
FROM [Queue1]
WHERE conversation_handle = @ConversationHandle
), timeout 5000;
If this sql in an activated sp do you really have to have the waitfor ? Since the sp will only be fired if there is a message on the queue ?
2. It is reccomended that for high volume SB apps you do not do a top(1) receive but process batches. Exactly what is the best practice to do this. Receive a batch into a table variable and then what ? Process through it with a cursor ? That is not very efficient either, i would just like some insight into batch queue processing as everywhere i have seen uses top (1) from the queue ?
Thanx
View 3 Replies
View Related
Aug 24, 2006
Hi There
I was wondering, i have experienced alot of stored procedure blocking where you have a stored procedure that get executed very frequently and is complex, when the sp has to recompile it is locked and cannot be executed by other processes.
How will this work with an activated stored procedure, our activated stored procedure will probably not be too complex but it may exec complex ones base on the message type.
Now lets say that a message is recieved by the activated sp he in turn executes a complex sp to process the message this causes the complex sp to recompile, during this time another activated sp is spawned by the queue with the same message type he execs the same complex sp that is busy recompiling, i presume that the second activated sp will be blocked until the sp has completed recompiling.
Is this correct, because on a very busy queue with very complex sp's being called by the activated stored procedure, the benefit of having multiple queue readers is negated by the blocking caused by the recompiling sp's called by the activated sp.
Basically no matter how many queue readers kick in to handle the messages they all wait for the sp to recompile from the first message.
I am hoping multiple spawned activated stored procedures somehow over come this? Or work differently somehow.
Does anyone know ?
Thanx
View 3 Replies
View Related
Oct 23, 2006
I have an activated SP that is issuing PRINT statements.
These are raised as ErrorLog and EventLog events when using SQLProfiler
However if you attach an event notification for ERRORLOG and EVENTLOG, nothing appears in the queue. Any other ERRORLOG or EVENTLOG event caught by profiler is caught by the event notification as expected.
View 2 Replies
View Related
Oct 19, 2006
Hi everyone! Good day!
I'm not really sure if my question should fall on Service Broker or T-SQL, but I hope someone helps me with this... After activating the stored procedure assigned to the queue, is there any way for me to find out if the stored procedure is already finished executing?
I have successfully sent messages to my queue but I have no way to know if all the processing is already done.
Thanks so much!
View 4 Replies
View Related
Mar 7, 2008
Hi,
I am getting deadlock on activated procedure which I am using to receive message from the Service Broker Queue.
Deadlock details:
Two threads are tring to do delete on internal table queue_messages_122847900 ends up in a dead lock.
Activated procedure code
RECEIVE TOP(1) @xmlMessage = message_body,
@handle = conversation_handle,
@message_type = message_type_name
FROM TransactionQueue;
IF (@message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
END CONVERSATION @handle;
RETURN 0
END
.........................
.........................
After this I do process the message and some other processing
And then
END CONVERSATION @handle;
Note I do have single conversation group
Is their a problem in the way I am receiving and processing messages. Is it possible because of the delay between RECEIVE and END CONVERSATION same message is read by two different threads.
Thanks
View 1 Replies
View Related
Aug 31, 2000
how to debug store procedure from Visual interdev. I am working on Windows NT 4.0 workstation, and SQL database server is on an other Windows NT server machine. I can not step through the store procedure or set breakpoint.
Thanks in advanced.
YU
View 1 Replies
View Related
Nov 23, 2007
How can i debug the store procedure in SQL Server 2005
View 1 Replies
View Related
