Lost Ability To Debug CLR Stored Procs After SQL 2005 Upgrade To SP1

May 4, 2006

OS: Windows XP Pro, SP2
SQL: SQL Server 2005 (Server 9.0.2047) SP1
VS: Visual Studio 2005 RTM.050727-4200
--------

After upgrading our SQL Servers to SP1 we have lost the ability to debug .net CLR stored procedures. The debugging process worked fine until we installed the Service Pack. All other features we use test out fine after the upgrade. The only and very important feature that does not work is debugging CLR procs. In fact this problem is happening in every environment we have. Be it SQL2005 running on Windows XP SP2, Windows Server 2003 SP1, Window Server 2000 SP4. SQL Server remote or local on the machine doing the debugging. All 5 developer workstations with Visual Studio in virtual machines or installed on in base OS are behaving exactly the same. Every developer on our team has the exact same problem regardless of their configuration after the upgrade to the service pack 1. All developers and environments were working correctly before the upgrade.

Even building a new SQL box from scratch and installing all tools and creating a new clean database we fail with same error.

We have verified CLR Debugging is enabled for the database connection. We have dropped the connection and recreated it.

Here is an example of the output from the output window:

This is the output up to the CLR call

The thread 'sql_test2 [62]' (0x68c) has exited with code 0 (0x0).
The thread 'sql_test2 [62]' (0x68c) has exited with code 0 (0x0).
Auto-attach to process '[1072] [SQL] sql_test2' on machine 'sql_test2' succeeded.

Output AFTER the CLR Call

Auto-attach to process '[1072] sqlservr.exe' on machine 'sql_test2' succeeded.

'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_32mscorlib2.0.0.0__b77a5c561934e089mscorlib.dll', No symbols loaded.

'sqlservr.exe' (Managed): Loaded 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnSqlAccess.dll', No symbols loaded.

'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_32System.Data2.0.0.0__b77a5c561934e089System.Data.dll', No symbols loaded.

'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem2.0.0.0__b77a5c561934e089System.dll', No symbols loaded.

'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_32System.Transactions2.0.0.0__b77a5c561934e089System.Transactions.dll', No symbols loaded.

'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem.Security2.0.0.0__b03f5f7f11d50a3aSystem.Security.dll', No symbols loaded.

'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem.Xml2.0.0.0__b77a5c561934e089System.Xml.dll', No symbols loaded.

'sqlservr.exe' (Managed): Loaded 'CIS_Release6a', No symbols loaded.

'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILMicrosoft.VisualBasic8.0.0.0__b03f5f7f11d50a3aMicrosoft.VisualBasic.dll', No symbols loaded.

'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem.Configuration2.0.0.0__b03f5f7f11d50a3aSystem.Configuration.dll', No symbols loaded.

Program Locks up with blank screen and hangs. If we stop the Debugging then the correct results will appear in the output window.

Column1

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

<CIS><Header><MessageType>FosterParent</MessageType><Status>-1</Status><ErrorMsg><Code>-100</Code><Description>Requested Record Not Available</Description></ErrorMsg></Header></CIS>

No rows affected.

(1 row(s) returned)

@RETURN_VALUE =

Finished running [dbo].[qsp_FF_FD_FP_FosterParent].


Any ideas? TIA




View 1 Replies


ADVERTISEMENT

Should I Be Able To Debug Stored Procs With Enterprise Evaluation Copy?

Sep 12, 2007

I've downloaded the enterprise evaluation copy of sql server 2005, patched it to sp2. I've fired up visual studio 2005 and opened a connection to my local database okay, (I've used windows authenication - though I'm not sure how to check whether I've got SA rights..).

When I open a stored procedure up, the breakpoints options are greyed out.

When I look in control panel/add programs, the version of Visual Studio is "Premier Partner Edition - ENU", if I look in the about of vs: it has installed products of sql server analysis services, integration services and reporting services

Looking on the web it says that I need visual studio 2005 professional to be able to debug sp's but on the features list for Premier Partner Edition, under the debug section it says "SQL"

Can anyone shed some light on this? Or is it the case that the eval copy of sql server 2005 ships with a version of vs2005 that precludes sp debugging !!!

ta

View 4 Replies View Related

Lost Ability To Export After Print Control Install

May 21, 2007

I have a user that says that after she installed the ActiveX control for printing, that she can no longer export the reports. Has this happened to anyone else? What settings do I need to check on her machine to fix this problem?

View 2 Replies View Related

Lost Ability To Manage SQL Server At Server Explorer In Visual Studio .NET

Dec 27, 2004

Good day all,

I am no longer able to manage SQL Server at the server explorer in Visual studio. i.e. create, modify and delete for Diagrams, Tables, Views, Stored Procs & Functions. Further more, it missing Diagram and function folder. I can only view or read data.

But I can access local MSDE as usual.

Whats I have done wrong? Please Help. Thanks.

View 2 Replies View Related

Stored Procs: Is There A Way To Divide Up (or Namespace) Groups Of Stored Procs

Jan 15, 2008

Is there a way to namespace groups of stored procs to reduce confusion when using them?

For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.

View 1 Replies View Related

Unable To Debug SQL Server 2005 Stored Procedures From Another Workstation Running Visual Studio 2005

Sep 18, 2006

I'm having some problems debugging SQL Server stored procedures on a SQL Server 2005 server. I have installed Visual Studio 2005 on a workstation running Windows XP, now I'm trying to debug a ASP.Net web application that has some code that executes the stored procedures on a Windows 2003 Server running SQL Server 2005.

I opened VS2005 ... created a connection to the SQL Server 2005 instance ... open the Stored procedure ... right click the stored procedure name and selected Step into Stored Procedure and the following message is displayed:

Unable to start T-SQL debugging.Could not attach to SQL Server process on 'ServerName'.

Any ideas.



Thanks,

View 2 Replies View Related

How To Debug My Stored Procedure In SQL 2005 ?

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

How To Debug The Stored Procedure In .net 2005

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

[sql Server 2005] - Debug A Stored Procedure

Nov 28, 2006

hello world,
how cani debug a stored procedure under sql server 2005?

View 2 Replies View Related

Debug Stored Proc's In MSSQL 2005

Mar 15, 2007

In SQL Server 2000's Query Analyzer, you can debug a stored procedure by right-clicking a SP and select "Debug".
You can then step through the SP one line at a time.I don't see this in SQL Server 2005 and have searched bu cannot find any documentation on what happened to this feature.What did Microsoft do with this?

View 1 Replies View Related

How To Debug Stored Procedure In SQL Server 2005

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

Debug Stored Proc's In MSSQL 2005

Mar 15, 2007

In SQL Server 2000's Query Analyzer, you can debug a stored procedure by right-clicking a SP and select "Debug".

You can then step through the SP one line at a time.

I don't see this in SQL Server 2005 and have searched but cannot find any documentation on what happened to this feature.
What did Microsoft do with this?

View 3 Replies View Related

Can I Debug A SQL 2005 Stored Procedure With The SA Account

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

How To Debug SQL 2005 Stored Proc With No Query Analyzer

Aug 8, 2007

Hi,
In SQL 2000, to debug a stored proc I would launch QA, right click and hit debug.
How do I accomplish this with SQL 2005. I can't see that it came with QA.
Thank you,
Steve

View 1 Replies View Related

Lost SQL 2005 Tables And Stored Procedures

Apr 21, 2006

I converted a program from SQL 2000 to SQL 2005 all went well. I created a number of tables and stored procedures after the conversion. I backed up my .mdf and .idf files. I was having problems with SQL so I uninstalled and re-installed it. Once I re-installed it I could no longer display some tables and files. Since I am the dbo, I think I should be able to access them. There obviously is something I am missing, hopefully not the tables and sps.

I would appreciate any suggestions.

Thank you.

LitePipe

View 4 Replies View Related

How To Debug Database Engine Stored Procedures On SQL Server 2005?

Mar 19, 2007

1). When you right click stored procedure in the Query Analyzer on 2000 you can select debug from the list of menues.

I can't find this functionality in the SQL Server Management studio.

2). By the way I also can't find where went the output of my print statement.

3). Does server cursor functionality is still working in 2k5?

View 3 Replies View Related

Problem With Using Stored Procs As I/p To Another Stored Procs

May 7, 2004

HI,

CREATE PROCEDURE PROC1
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
ORDER BY B.INTCUSTOMERID

END

IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?

CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC PROC1


ST......1,
ST......2,


END.

BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-


CREATE PROCEDURE MY_PROC
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (23,12,10)
ORDER BY B.INTCUSTOMERID

END

SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 12 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 10 then '1'
else '0' end) AS PREFER
FROM CUSTOMER
GROUP BY A.INTCUSTOMERID
ORDER BY A.INTCUSTOMERID
END

WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .



CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC MY_PROC


ST......1,
ST......2,

END.

BUT, HERE I WANT TO USE FIRST DATASET ONLY , HOW TO USE IT ?

THANKS.

View 4 Replies View Related

T-SQL (Stored Procedure) Upgrade From 7 To 2005

Oct 15, 2006

Hi,I'd like to know if anyone has had experience upgrading their storedprocedures directly from 7 over to 2005, and what issues they've runinto.Also, if there are any articles available on this subject, couldsomeone please point me in the right direction?Thanks!!

View 2 Replies View Related

How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
 SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

View 1 Replies View Related

Stored Procs Are BAD!! BAD, I Tell You!!!

Feb 5, 2005

almost choked when i read the following recent post on The Daily WTF (http://thedailywtf.com/) the other day --

Logical Tiers? That Makes No Sense! (http://thedailywtf.com/ShowPost.aspx?PostID=28959)

i don't think i can do justice to how utterly stupid that stored procedure is

read the comments and have a laugh

one of the points made was that stored procedures are bad

this twigged something in my memory, so i dug around in my bookmarks, and sure enough, here's another decent discussion about stored procs --

Stored procedures are bad, m'kay? (http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx)

enjoy!

View 6 Replies View Related

.exe From Stored Procs

Jun 28, 2007

Hey guys.

I am wondering if it is possible to execute an .exe from a stored proc. I have an application that calls a stored proc and I am wanting for that stored proc to call up an app to show certain results. I have no way of doing this on the application itself as I dont have the source code. So, since I do have access to the SQL I am wondering if it could be done there.

Thanks
tibor

View 14 Replies View Related

I Need Help With Stored Procs And UDF

Apr 8, 2004

First off, this is a cross post which is also located here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34073

Now, with the following stored procs and UDF, the Result is NULL and I cannot figure out why.

What I am doing is calling a stored procedure pass in an OUTPUT variable of type float. This stored proceudre then calls a different stored procedure passing the same OUTPUT variable of type float. The second stored procedure then calls a UDF passing in two variables to be multiplied ans this should set the OUTPUT variable to the result.

The UDF does return the correct result to the interior stored procedure, but the interior stored procedure does not pass the value back to the original stored procudure resultint in a Result value = NULL.

The code is below, just copy / past, execute and you will see exactly what I mean.

Any thoughts?


USE NORTHWIND
GO

CREATE FUNCTION RECTANGULAR_XSECTION
(@fWidth float, @fHeight float)

RETURNS float

AS
BEGIN
RETURN (@fWidth * @fHeight)
END
GO


CREATE PROCEDURE usp_shapes_GetRectangularXSection

@fResult float OUTPUT

AS

declare @fWidth float, @fHeight float
SELECT @fWidth = 108, @fHeight = 10

SELECT @fResult = [dbo].[RECTANGULAR_XSECTION](@fWidth, @fHeight)
SELECT @fResult AS CalledProcedureOkHere

GO

CREATE PROCEDURE usp_shapes_GetXSection

@fResult float OUTPUT

AS

EXECUTE usp_shapes_GetRectangularXSection @fResult
SELECT @fResult AS CallingProcedure_NULL?
GO

declare @fResult float
EXECUTE usp_shapes_GetXSection @fResult
SELECT @fResult as OutsideCallingProcedure_NULL?
GO

DROP FUNCTION RECTANGULAR_XSECTION
GO
DROP PROCEDURE usp_shapes_GetRectangularXSection
GO
DROP PROCEDURE usp_shapes_GetXSection
GO


Mike B

View 3 Replies View Related

New To Stored Procs

Nov 13, 2007

I am trying to improve my SQL and reduce the number of connections my website is making to the server.

Currently I have a stored procedure that gets all the games (and their details) for a single user which works fine which uses a WHERE userid = @userid which is an Int value.

I now need to create a new procedure which brings back the to all their "friends" (again by their userid). How is it best to do this? I originally tried to do a WHERE userID IN (@userid) but was unable to work out what variable type to use for @userid.

Is it best to do a single query in this way or is there a way to use the existing SP, loop through the collection of userids and join the result together into a single set to return? If an IN is the best route, what is the correct datatype for the variable?

View 5 Replies View Related

SQL Stored Procs

Aug 8, 2006

can anyone help me out.... i need to compare a date that was retrieved from the database and the system date then it must be coded in the stored procs of the database.. help!!!!

View 10 Replies View Related

Debug A Stored Procedure

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

How Can I Debug An Stored Procedure?

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

How To Debug Stored Procedure

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

Debug Stored Procedure

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

How Can I Debug My Sql Stored Procedure

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

Debug Stored Procedure

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

How To Debug Stored Procedure?

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

How To Debug My Stored Procedure.

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

How To Debug Stored Procedure

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







Copyrights 2005-15 www.BigResource.com, All rights reserved