I am trying to modify data in a SQL Server database using the query below. But it fails; why??
update person_course datetimestamp = '10/6/1900 12:00:01 AM' where personid > 470
I get
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'datetimestamp'.You need the key word SET
update person_course SET datetimestamp = '10/6/1900 12:00:01 AM' where personid > 470|||Thanks. I saw it after I had sent the message. Aaarrrgh!!! Long day I guess.
Showing posts with label qry. Show all posts
Showing posts with label qry. Show all posts
Monday, March 12, 2012
qry wont use index
I've got a query which just won't use an index, instead, it does a table scan on a specific column. Basically the query is:
select count(*)
from table1 t1
inner join table2 t2 on (t1.myref = t2.myref)
where myint2 in (1,2,3)
and (myval between -1 and -2 or myint1 = 1)
In actual work, the 'myval between' uses variables which could be null, same goes for myint1. The values above are the values that I use to examine. According to the analyzer, a table scan is performed on myint2 (the in stuff), however, there's an index on myint2 also in combination with myint1.
I've tried to re-create the setup by creating the table1 and table2, including the indexes. Unfortunately, in the re-created setup, the index is used.
EDIT: Oddly, the OR ruins the plan to use an index: when leaving out the 'and (myval...)', the index is used.
What's going on?The first OR operation in a query (in this case, the IN clause) makes an index scan difficult. The second OR operation makes the index scan impossible (at least using present technology).
-PatP|||how's that? when changing
where myint2 in (1,2,3)
into
where myint2 = 1
I still get a table scan.
Besides, I do get an index-scan in the re-created setup.
I tried a defrag, reindex AND recompute statistics. It just wont show up.
I really don't get it.|||Hogtie the optimizer and try it. Use SET FORCEPLAN ON (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_8mni.asp) and hint the index. See if you get a result set while you are young enough to still care.
Let me know what you find out, I'm curious now!
-PatP|||hah! now your stuck! :>
the optimizer came up empty and even with the index hint (0) it still does a full table scan. The trouble is that the column resides in the facts-table (its a warehouse db) and in production it gets a 80% hit according to the exection plan (76% in dev). I'm beginning to wonder if the amount of indexes specified on the facts is too much (47 columns, 26 indexes).|||oh btw: it takes 14 minutes to come back with a rowcount of 85.
The forceplan option and index(0) hint does take the percentage down to 28% but it's still a table scan (forcedindex). btw: in the analyzer, execution plan, I've got these little round yellow circles on the tablescan, index, nested loops etc. I don't see 'em in BOL. Happen to know what their about?|||Unfortunately, nothing changed over the weekend.
I even changed the complete query to a simple count(*) with a single where-clause in which a single value is specified.
Still a full table scan, even with index-hint and forceplan set.
select count(*)
from table1 t1
inner join table2 t2 on (t1.myref = t2.myref)
where myint2 in (1,2,3)
and (myval between -1 and -2 or myint1 = 1)
In actual work, the 'myval between' uses variables which could be null, same goes for myint1. The values above are the values that I use to examine. According to the analyzer, a table scan is performed on myint2 (the in stuff), however, there's an index on myint2 also in combination with myint1.
I've tried to re-create the setup by creating the table1 and table2, including the indexes. Unfortunately, in the re-created setup, the index is used.
EDIT: Oddly, the OR ruins the plan to use an index: when leaving out the 'and (myval...)', the index is used.
What's going on?The first OR operation in a query (in this case, the IN clause) makes an index scan difficult. The second OR operation makes the index scan impossible (at least using present technology).
-PatP|||how's that? when changing
where myint2 in (1,2,3)
into
where myint2 = 1
I still get a table scan.
Besides, I do get an index-scan in the re-created setup.
I tried a defrag, reindex AND recompute statistics. It just wont show up.
I really don't get it.|||Hogtie the optimizer and try it. Use SET FORCEPLAN ON (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_8mni.asp) and hint the index. See if you get a result set while you are young enough to still care.
Let me know what you find out, I'm curious now!
-PatP|||hah! now your stuck! :>
the optimizer came up empty and even with the index hint (0) it still does a full table scan. The trouble is that the column resides in the facts-table (its a warehouse db) and in production it gets a 80% hit according to the exection plan (76% in dev). I'm beginning to wonder if the amount of indexes specified on the facts is too much (47 columns, 26 indexes).|||oh btw: it takes 14 minutes to come back with a rowcount of 85.
The forceplan option and index(0) hint does take the percentage down to 28% but it's still a table scan (forcedindex). btw: in the analyzer, execution plan, I've got these little round yellow circles on the tablescan, index, nested loops etc. I don't see 'em in BOL. Happen to know what their about?|||Unfortunately, nothing changed over the weekend.
I even changed the complete query to a simple count(*) with a single where-clause in which a single value is specified.
Still a full table scan, even with index-hint and forceplan set.
Qry with "OR" keyword assistance...
Is there a better more reliable or eaiser way to get what I'm looking for?
I want anything that was created after Jan. 1, 2003 that was declined, the
declined letter codes are as indicated, and created after Jan 1, 2003
Or, I want anything that was rated and created after Jan 1, 2003
Or, I want anything that was paid and created after Jan 1, 2005 and
(redundently) created after Jan 1, 2003
Or that was recently created in the most recent campaign occuring Sept 1, 20
05
and (redundently) created after Jan 1, 2003
where (c2.ltrcode in('4','13,'16')
or (c2.rated > '0')
or (isdate(c2.upremrecd = 1 and c1.createdt > '20050101')
or c1.createdt > '20050901')
and c1.createdt > '20030101'
TIA
JeffP...if you find this running slowly, you can use UNIONs to pull them all
together, e.g.
select <colum list>
from <tables>
where c1.createdt > '20030101'
and c2.ltrcode in ('4','13','16')
union
select <colum list>
from <tables>
where c1.createdt > '20030101'
and c2.rated > '0'
union
<etc>
JDP@.Work wrote:
> Is there a better more reliable or eaiser way to get what I'm looking for?
> I want anything that was created after Jan. 1, 2003 that was declined, the
> declined letter codes are as indicated, and created after Jan 1, 2003
> Or, I want anything that was rated and created after Jan 1, 2003
> Or, I want anything that was paid and created after Jan 1, 2005 and
> (redundently) created after Jan 1, 2003
> Or that was recently created in the most recent campaign occuring Sept 1,
2005
> and (redundently) created after Jan 1, 2003
> where (c2.ltrcode in('4','13,'16')
> or (c2.rated > '0')
> or (isdate(c2.upremrecd = 1 and c1.createdt > '20050101')
> or c1.createdt > '20050901')
> and c1.createdt > '20030101'
> TIA
> JeffP...
>
I want anything that was created after Jan. 1, 2003 that was declined, the
declined letter codes are as indicated, and created after Jan 1, 2003
Or, I want anything that was rated and created after Jan 1, 2003
Or, I want anything that was paid and created after Jan 1, 2005 and
(redundently) created after Jan 1, 2003
Or that was recently created in the most recent campaign occuring Sept 1, 20
05
and (redundently) created after Jan 1, 2003
where (c2.ltrcode in('4','13,'16')
or (c2.rated > '0')
or (isdate(c2.upremrecd = 1 and c1.createdt > '20050101')
or c1.createdt > '20050901')
and c1.createdt > '20030101'
TIA
JeffP...if you find this running slowly, you can use UNIONs to pull them all
together, e.g.
select <colum list>
from <tables>
where c1.createdt > '20030101'
and c2.ltrcode in ('4','13','16')
union
select <colum list>
from <tables>
where c1.createdt > '20030101'
and c2.rated > '0'
union
<etc>
JDP@.Work wrote:
> Is there a better more reliable or eaiser way to get what I'm looking for?
> I want anything that was created after Jan. 1, 2003 that was declined, the
> declined letter codes are as indicated, and created after Jan 1, 2003
> Or, I want anything that was rated and created after Jan 1, 2003
> Or, I want anything that was paid and created after Jan 1, 2005 and
> (redundently) created after Jan 1, 2003
> Or that was recently created in the most recent campaign occuring Sept 1,
2005
> and (redundently) created after Jan 1, 2003
> where (c2.ltrcode in('4','13,'16')
> or (c2.rated > '0')
> or (isdate(c2.upremrecd = 1 and c1.createdt > '20050101')
> or c1.createdt > '20050901')
> and c1.createdt > '20030101'
> TIA
> JeffP...
>
QRY question: If field1 is null then field2
Is there a way to create a column that will show the
value of field1 unless field1 is null in which case it
will show the value of field2? I do it all the time in
Access but can't seem to figure out if SQL Server can do
it as well. Any help or suggestions are geratly
appreciatd!
Here is how I do it in MS Access:
SELECT IIf([Field1] Is Null,[Field2],[Field1]) AS [Output]
FROM [Table];
Dan,
In SQL Server, you can write
CASE WHEN Field1 IS NULL THEN Field2 ELSE Field1 END AS [Output]
Since this particular need comes up often, there is a shorthand form:
COALESCE(Field1, Field2) AS [Output]
Steve Kass
Drew University
Dan wrote:
>Is there a way to create a column that will show the
>value of field1 unless field1 is null in which case it
>will show the value of field2? I do it all the time in
>Access but can't seem to figure out if SQL Server can do
>it as well. Any help or suggestions are geratly
>appreciatd!
>Here is how I do it in MS Access:
>SELECT IIf([Field1] Is Null,[Field2],[Field1]) AS [Output]
>FROM [Table];
>
value of field1 unless field1 is null in which case it
will show the value of field2? I do it all the time in
Access but can't seem to figure out if SQL Server can do
it as well. Any help or suggestions are geratly
appreciatd!
Here is how I do it in MS Access:
SELECT IIf([Field1] Is Null,[Field2],[Field1]) AS [Output]
FROM [Table];
Dan,
In SQL Server, you can write
CASE WHEN Field1 IS NULL THEN Field2 ELSE Field1 END AS [Output]
Since this particular need comes up often, there is a shorthand form:
COALESCE(Field1, Field2) AS [Output]
Steve Kass
Drew University
Dan wrote:
>Is there a way to create a column that will show the
>value of field1 unless field1 is null in which case it
>will show the value of field2? I do it all the time in
>Access but can't seem to figure out if SQL Server can do
>it as well. Any help or suggestions are geratly
>appreciatd!
>Here is how I do it in MS Access:
>SELECT IIf([Field1] Is Null,[Field2],[Field1]) AS [Output]
>FROM [Table];
>
Qry Analyzer: Processor utilization
I have noticed that when using SQL Query Analyzer some of my queries will use 100% CPU on my PC and next to nothing on the SQL server, while other Queries require 100% CPU on SQL server and do next to nothing on my PC. Does anyone know what determines this??
Right now I can produce this by executing two very similar T-SQL selects. The one that runs on the server only has one additional join - a very simply join at that. If I can change my SQL to make it run client side in some situations, that would be VERY HELPFUL!
Thanks in advance!
RyanWhat are you referring to when saying that the query takes 100% on your machine? Are you comparing the performance of MSDE running on your machine with SQL Server engine running on the server?|||No, MSDE is not involved. If I run a query with one join through SQL Query Analyzer on my PC, and against my production 7.0 sql server, it will use 100% of my PC's processor. If I execute a query with two inner joins in it, still from query analyzer and against the same SQL box, the query uses 100% of the server's processor and does not use the client PC processor.
I guess this would probably be occurring based on some decision made by the SQL optimizer, but I am trying to find out if anyone is familiar enough with such a situation that they know tricks to writing queries so that processing occurs on the client side.
I have some users that require atypical adhoc query support. When possible, I would like to write queries for them in such a way that I can place most of the processor requirements on the client PC.
Thanks
Ryan|||I don't think it's possible. ALL processing is done on the server. Unless you're talking about firing queries through MS Access that "claims" to be able to do data processing itself. But even then, whatever statement ends up going to the server from the Jet, - gets processed entirely by the SQL engine, the client just reads the results either from the named pipe file, or from TCP/IP packets (virtual file.)
Right now I can produce this by executing two very similar T-SQL selects. The one that runs on the server only has one additional join - a very simply join at that. If I can change my SQL to make it run client side in some situations, that would be VERY HELPFUL!
Thanks in advance!
RyanWhat are you referring to when saying that the query takes 100% on your machine? Are you comparing the performance of MSDE running on your machine with SQL Server engine running on the server?|||No, MSDE is not involved. If I run a query with one join through SQL Query Analyzer on my PC, and against my production 7.0 sql server, it will use 100% of my PC's processor. If I execute a query with two inner joins in it, still from query analyzer and against the same SQL box, the query uses 100% of the server's processor and does not use the client PC processor.
I guess this would probably be occurring based on some decision made by the SQL optimizer, but I am trying to find out if anyone is familiar enough with such a situation that they know tricks to writing queries so that processing occurs on the client side.
I have some users that require atypical adhoc query support. When possible, I would like to write queries for them in such a way that I can place most of the processor requirements on the client PC.
Thanks
Ryan|||I don't think it's possible. ALL processing is done on the server. Unless you're talking about firing queries through MS Access that "claims" to be able to do data processing itself. But even then, whatever statement ends up going to the server from the Jet, - gets processed entirely by the SQL engine, the client just reads the results either from the named pipe file, or from TCP/IP packets (virtual file.)
Qry Analyzer Debug broken: XP SP2
I read a post in this group that a hotfix (per KB839280) would take care of
this (posted by an MS support person). The post indicated this fix would take
care of the Visual Studio issue, and well as Query Analyzer.
I got the hotfix and installed it on my development server, however my XP
sp2 system still cannot remotely debug the server. I have always been able to
do this, until I installed XP sp2.
Any suggestions? Has anyone gotten remote debugging to work post XP sp2
installation?
Thanks for your assistance,
TomHi Tom,
Thanks for your posting!
From your descriptions, I understood that you could not use Query Analyzer
to debug after XP SP2 was upgraded. Have I understood you? If there is
anything I misunderstood, please feel free to let me know.
Based on my scope, as you said it is a remotely debugging ,does it used a
linked server? If so, here is another document address the probelm you may
meet.
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Microsoft
Windows XP Service Pack 2
http://support.microsoft.com/default.aspx?kbid=839279
What's the error message when it fails to debugging? Could you tell me how
to reporduce it on my machine? I need more detailed information for your
scenario, which, I believe, will make us closer to the resolution.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
this prior to installing XP sp2 on the WinXP system. The sql server is at
sp3, and runs on a Windows 2003 system.
The message I get when running the debugger is:
Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
connection 58.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||This is the error in the event log of the SQL Server system:
Event Type: Error
Event Source: SQLDebugging98
Event Category: None
Event ID: 1
Date: 9/14/2004
Time: 9:45:19 PM
User: N/A
Computer: HESPERUS
Description:
SQL Server is running as 'Force3\SQLAdmin' and cannot connect to the
debugger on machine 'TOMHOME2' (error = 0x80070005 Access is denied. ). Use
one of the following options to fix this error. 1) Run SQL Server as "Local
System", as a domain account, or as a local account with identical usernames
and passwords on both machine 'HESPERUS' and 'TOMHOME2'. 2) Verify that
machine 'HESPERUS' can open files on machine 'TOMHOME2'. Debugging disabled
for connection 56.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This setup did work prior to XP sp2, the logon account for the sql server
service is a domain (admin) account.
The odd thing now is, when I run the debugger, it actually runs, but does
not "step" thru the procedure, it goes straight thru to the end, and does not
show any of the values for the variables, connections, etc. In the results
pane, I get a result, and at the very bottom it says completed.
Putting in break points makes no difference, it just runs on to the end
regardless...
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Tom,
Thanks for your detailed information and descriptions!
First of all, The reason for Error 508 and Error code 0x80070005 is
permission issue. As you said it works fine before upgrading to XP SP2,
have you tried disable the Firewall os XP SP2. Close the firewall and then
tell me whether it works. Checking the following documents to make sure
your SQL Server in XP XP2 is correctly configured
How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
http://support.microsoft.com/?id=841249
Secondly, The error 0x800706ba generated is a Distributed COM communication
error when using RPC. The error code 0x800706ba translates to "RPC server
unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
allocation. By default, RPC dynamic port allocation randomly selects port
numbers above 1024, so the problem seems to be the firewall blocking the
rpc communication. Do the folloing things to make sure DCOM is running
fine.
* On the client machine
--run dcomcnfg.
--Go to the dcom config properties under default properties, there is a
check box stating "enable Distributed COM on this computer", if this was
unchecked on the client machine, this is a default setting that is checked.
make sure it is checked.
*Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
replcaed the old ones with these on the client machine and then ran the
regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
/RegServer on the client machine and rebooted the box and everything
started to work like a charm.Customer was able to debug the stored proc on
the server using SQL QA and t-sql debugger from his client machine.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||I found the problem: after applying the hotfix to the server, the client side
tools were no longer the same version as the server. Applying the hotfix to
the client fixed the problem.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your detailed information and descriptions!
> First of all, The reason for Error 508 and Error code 0x80070005 is
> permission issue. As you said it works fine before upgrading to XP SP2,
> have you tried disable the Firewall os XP SP2. Close the firewall and then
> tell me whether it works. Checking the following documents to make sure
> your SQL Server in XP XP2 is correctly configured
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
> http://support.microsoft.com/?id=841249
> Secondly, The error 0x800706ba generated is a Distributed COM communication
> error when using RPC. The error code 0x800706ba translates to "RPC server
> unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
> allocation. By default, RPC dynamic port allocation randomly selects port
> numbers above 1024, so the problem seems to be the firewall blocking the
> rpc communication. Do the folloing things to make sure DCOM is running
> fine.
> * On the client machine
> --run dcomcnfg.
> --Go to the dcom config properties under default properties, there is a
> check box stating "enable Distributed COM on this computer", if this was
> unchecked on the client machine, this is a default setting that is checked.
> make sure it is checked.
> *Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
> replcaed the old ones with these on the client machine and then ran the
> regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
> /RegServer on the client machine and rebooted the box and everything
> started to work like a charm.Customer was able to debug the stored proc on
> the server using SQL QA and t-sql debugger from his client machine.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||I also have the same problem after installed Windows XP SP2,
the similar error message when running debugger from query analyzer.
Is there any solution for this?
"TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...
> I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
> this prior to installing XP sp2 on the WinXP system. The sql server is at
> sp3, and runs on a Windows 2003 system.
> The message I get when running the debugger is:
> Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
> on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
> SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
> connection 58.
> ""Mingqing Cheng [MSFT]"" wrote:
> > Hi Tom,
> >
> > Thanks for your posting!
> >
> > From your descriptions, I understood that you could not use Query Analyzer
> > to debug after XP SP2 was upgraded. Have I understood you? If there is
> > anything I misunderstood, please feel free to let me know.
> >
> > Based on my scope, as you said it is a remotely debugging ,does it used a
> > linked server? If so, here is another document address the probelm you may
> > meet.
> >
> > You may receive a 7391 error message in SQL Server 2000 when you run a
> > distributed transaction against a linked server after you install Microsoft
> > Windows XP Service Pack 2
> > http://support.microsoft.com/default.aspx?kbid=839279
> >
> > What's the error message when it fails to debugging? Could you tell me how
> > to reporduce it on my machine? I need more detailed information for your
> > scenario, which, I believe, will make us closer to the resolution.
> >
> > Thank you for your patience and cooperation. If you have any questions or
> > concerns, don't hesitate to let me know. We are here to be of assistance!
> >
> >
> > Sincerely yours,
> >
> > Mingqing Cheng
> >
> > Online Partner Support Specialist
> > Partner Support Group
> > Microsoft Global Technical Support Center
> > ---
> > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> > This posting is provided "as is" with no warranties and confers no rights.
> > Please reply to newsgroups only, many thanks!
> >
> >|||Check my last entry on this topic, dated 9/16/2004
"duandd" wrote:
> I also have the same problem after installed Windows XP SP2,
> the similar error message when running debugger from query analyzer.
> Is there any solution for this?
> "TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...
> > I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
> > this prior to installing XP sp2 on the WinXP system. The sql server is at
> > sp3, and runs on a Windows 2003 system.
> >
> > The message I get when running the debugger is:
> >
> > Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
> > on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
> > SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
> > connection 58.
> >
> > ""Mingqing Cheng [MSFT]"" wrote:
> >
> > > Hi Tom,
> > >
> > > Thanks for your posting!
> > >
> > > From your descriptions, I understood that you could not use Query Analyzer
> > > to debug after XP SP2 was upgraded. Have I understood you? If there is
> > > anything I misunderstood, please feel free to let me know.
> > >
> > > Based on my scope, as you said it is a remotely debugging ,does it used a
> > > linked server? If so, here is another document address the probelm you may
> > > meet.
> > >
> > > You may receive a 7391 error message in SQL Server 2000 when you run a
> > > distributed transaction against a linked server after you install Microsoft
> > > Windows XP Service Pack 2
> > > http://support.microsoft.com/default.aspx?kbid=839279
> > >
> > > What's the error message when it fails to debugging? Could you tell me how
> > > to reporduce it on my machine? I need more detailed information for your
> > > scenario, which, I believe, will make us closer to the resolution.
> > >
> > > Thank you for your patience and cooperation. If you have any questions or
> > > concerns, don't hesitate to let me know. We are here to be of assistance!
> > >
> > >
> > > Sincerely yours,
> > >
> > > Mingqing Cheng
> > >
> > > Online Partner Support Specialist
> > > Partner Support Group
> > > Microsoft Global Technical Support Center
> > > ---
> > > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> > > This posting is provided "as is" with no warranties and confers no rights.
> > > Please reply to newsgroups only, many thanks!
> > >
> > >
>
this (posted by an MS support person). The post indicated this fix would take
care of the Visual Studio issue, and well as Query Analyzer.
I got the hotfix and installed it on my development server, however my XP
sp2 system still cannot remotely debug the server. I have always been able to
do this, until I installed XP sp2.
Any suggestions? Has anyone gotten remote debugging to work post XP sp2
installation?
Thanks for your assistance,
TomHi Tom,
Thanks for your posting!
From your descriptions, I understood that you could not use Query Analyzer
to debug after XP SP2 was upgraded. Have I understood you? If there is
anything I misunderstood, please feel free to let me know.
Based on my scope, as you said it is a remotely debugging ,does it used a
linked server? If so, here is another document address the probelm you may
meet.
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Microsoft
Windows XP Service Pack 2
http://support.microsoft.com/default.aspx?kbid=839279
What's the error message when it fails to debugging? Could you tell me how
to reporduce it on my machine? I need more detailed information for your
scenario, which, I believe, will make us closer to the resolution.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
this prior to installing XP sp2 on the WinXP system. The sql server is at
sp3, and runs on a Windows 2003 system.
The message I get when running the debugger is:
Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
connection 58.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||This is the error in the event log of the SQL Server system:
Event Type: Error
Event Source: SQLDebugging98
Event Category: None
Event ID: 1
Date: 9/14/2004
Time: 9:45:19 PM
User: N/A
Computer: HESPERUS
Description:
SQL Server is running as 'Force3\SQLAdmin' and cannot connect to the
debugger on machine 'TOMHOME2' (error = 0x80070005 Access is denied. ). Use
one of the following options to fix this error. 1) Run SQL Server as "Local
System", as a domain account, or as a local account with identical usernames
and passwords on both machine 'HESPERUS' and 'TOMHOME2'. 2) Verify that
machine 'HESPERUS' can open files on machine 'TOMHOME2'. Debugging disabled
for connection 56.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This setup did work prior to XP sp2, the logon account for the sql server
service is a domain (admin) account.
The odd thing now is, when I run the debugger, it actually runs, but does
not "step" thru the procedure, it goes straight thru to the end, and does not
show any of the values for the variables, connections, etc. In the results
pane, I get a result, and at the very bottom it says completed.
Putting in break points makes no difference, it just runs on to the end
regardless...
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Tom,
Thanks for your detailed information and descriptions!
First of all, The reason for Error 508 and Error code 0x80070005 is
permission issue. As you said it works fine before upgrading to XP SP2,
have you tried disable the Firewall os XP SP2. Close the firewall and then
tell me whether it works. Checking the following documents to make sure
your SQL Server in XP XP2 is correctly configured
How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
http://support.microsoft.com/?id=841249
Secondly, The error 0x800706ba generated is a Distributed COM communication
error when using RPC. The error code 0x800706ba translates to "RPC server
unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
allocation. By default, RPC dynamic port allocation randomly selects port
numbers above 1024, so the problem seems to be the firewall blocking the
rpc communication. Do the folloing things to make sure DCOM is running
fine.
* On the client machine
--run dcomcnfg.
--Go to the dcom config properties under default properties, there is a
check box stating "enable Distributed COM on this computer", if this was
unchecked on the client machine, this is a default setting that is checked.
make sure it is checked.
*Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
replcaed the old ones with these on the client machine and then ran the
regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
/RegServer on the client machine and rebooted the box and everything
started to work like a charm.Customer was able to debug the stored proc on
the server using SQL QA and t-sql debugger from his client machine.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||I found the problem: after applying the hotfix to the server, the client side
tools were no longer the same version as the server. Applying the hotfix to
the client fixed the problem.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your detailed information and descriptions!
> First of all, The reason for Error 508 and Error code 0x80070005 is
> permission issue. As you said it works fine before upgrading to XP SP2,
> have you tried disable the Firewall os XP SP2. Close the firewall and then
> tell me whether it works. Checking the following documents to make sure
> your SQL Server in XP XP2 is correctly configured
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
> http://support.microsoft.com/?id=841249
> Secondly, The error 0x800706ba generated is a Distributed COM communication
> error when using RPC. The error code 0x800706ba translates to "RPC server
> unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
> allocation. By default, RPC dynamic port allocation randomly selects port
> numbers above 1024, so the problem seems to be the firewall blocking the
> rpc communication. Do the folloing things to make sure DCOM is running
> fine.
> * On the client machine
> --run dcomcnfg.
> --Go to the dcom config properties under default properties, there is a
> check box stating "enable Distributed COM on this computer", if this was
> unchecked on the client machine, this is a default setting that is checked.
> make sure it is checked.
> *Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
> replcaed the old ones with these on the client machine and then ran the
> regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
> /RegServer on the client machine and rebooted the box and everything
> started to work like a charm.Customer was able to debug the stored proc on
> the server using SQL QA and t-sql debugger from his client machine.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||I also have the same problem after installed Windows XP SP2,
the similar error message when running debugger from query analyzer.
Is there any solution for this?
"TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...
> I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
> this prior to installing XP sp2 on the WinXP system. The sql server is at
> sp3, and runs on a Windows 2003 system.
> The message I get when running the debugger is:
> Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
> on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
> SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
> connection 58.
> ""Mingqing Cheng [MSFT]"" wrote:
> > Hi Tom,
> >
> > Thanks for your posting!
> >
> > From your descriptions, I understood that you could not use Query Analyzer
> > to debug after XP SP2 was upgraded. Have I understood you? If there is
> > anything I misunderstood, please feel free to let me know.
> >
> > Based on my scope, as you said it is a remotely debugging ,does it used a
> > linked server? If so, here is another document address the probelm you may
> > meet.
> >
> > You may receive a 7391 error message in SQL Server 2000 when you run a
> > distributed transaction against a linked server after you install Microsoft
> > Windows XP Service Pack 2
> > http://support.microsoft.com/default.aspx?kbid=839279
> >
> > What's the error message when it fails to debugging? Could you tell me how
> > to reporduce it on my machine? I need more detailed information for your
> > scenario, which, I believe, will make us closer to the resolution.
> >
> > Thank you for your patience and cooperation. If you have any questions or
> > concerns, don't hesitate to let me know. We are here to be of assistance!
> >
> >
> > Sincerely yours,
> >
> > Mingqing Cheng
> >
> > Online Partner Support Specialist
> > Partner Support Group
> > Microsoft Global Technical Support Center
> > ---
> > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> > This posting is provided "as is" with no warranties and confers no rights.
> > Please reply to newsgroups only, many thanks!
> >
> >|||Check my last entry on this topic, dated 9/16/2004
"duandd" wrote:
> I also have the same problem after installed Windows XP SP2,
> the similar error message when running debugger from query analyzer.
> Is there any solution for this?
> "TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...
> > I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
> > this prior to installing XP sp2 on the WinXP system. The sql server is at
> > sp3, and runs on a Windows 2003 system.
> >
> > The message I get when running the debugger is:
> >
> > Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
> > on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
> > SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
> > connection 58.
> >
> > ""Mingqing Cheng [MSFT]"" wrote:
> >
> > > Hi Tom,
> > >
> > > Thanks for your posting!
> > >
> > > From your descriptions, I understood that you could not use Query Analyzer
> > > to debug after XP SP2 was upgraded. Have I understood you? If there is
> > > anything I misunderstood, please feel free to let me know.
> > >
> > > Based on my scope, as you said it is a remotely debugging ,does it used a
> > > linked server? If so, here is another document address the probelm you may
> > > meet.
> > >
> > > You may receive a 7391 error message in SQL Server 2000 when you run a
> > > distributed transaction against a linked server after you install Microsoft
> > > Windows XP Service Pack 2
> > > http://support.microsoft.com/default.aspx?kbid=839279
> > >
> > > What's the error message when it fails to debugging? Could you tell me how
> > > to reporduce it on my machine? I need more detailed information for your
> > > scenario, which, I believe, will make us closer to the resolution.
> > >
> > > Thank you for your patience and cooperation. If you have any questions or
> > > concerns, don't hesitate to let me know. We are here to be of assistance!
> > >
> > >
> > > Sincerely yours,
> > >
> > > Mingqing Cheng
> > >
> > > Online Partner Support Specialist
> > > Partner Support Group
> > > Microsoft Global Technical Support Center
> > > ---
> > > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> > > This posting is provided "as is" with no warranties and confers no rights.
> > > Please reply to newsgroups only, many thanks!
> > >
> > >
>
Qry Analyzer Debug broken: XP SP2
I read a post in this group that a hotfix (per KB839280) would take care of
this (posted by an MS support person). The post indicated this fix would take
care of the Visual Studio issue, and well as Query Analyzer.
I got the hotfix and installed it on my development server, however my XP
sp2 system still cannot remotely debug the server. I have always been able to
do this, until I installed XP sp2.
Any suggestions? Has anyone gotten remote debugging to work post XP sp2
installation?
Thanks for your assistance,
Tom
Hi Tom,
Thanks for your posting!
From your descriptions, I understood that you could not use Query Analyzer
to debug after XP SP2 was upgraded. Have I understood you? If there is
anything I misunderstood, please feel free to let me know.
Based on my scope, as you said it is a remotely debugging ,does it used a
linked server? If so, here is another document address the probelm you may
meet.
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Microsoft
Windows XP Service Pack 2
http://support.microsoft.com/default.aspx?kbid=839279
What's the error message when it fails to debugging? Could you tell me how
to reporduce it on my machine? I need more detailed information for your
scenario, which, I believe, will make us closer to the resolution.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
this prior to installing XP sp2 on the WinXP system. The sql server is at
sp3, and runs on a Windows 2003 system.
The message I get when running the debugger is:
Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
connection 58.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||This is the error in the event log of the SQL Server system:
Event Type:Error
Event Source:SQLDebugging98
Event Category:None
Event ID:1
Date:9/14/2004
Time:9:45:19 PM
User:N/A
Computer:HESPERUS
Description:
SQL Server is running as 'Force3\SQLAdmin' and cannot connect to the
debugger on machine 'TOMHOME2' (error = 0x80070005 Access is denied. ). Use
one of the following options to fix this error. 1) Run SQL Server as "Local
System", as a domain account, or as a local account with identical usernames
and passwords on both machine 'HESPERUS' and 'TOMHOME2'. 2) Verify that
machine 'HESPERUS' can open files on machine 'TOMHOME2'. Debugging disabled
for connection 56.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This setup did work prior to XP sp2, the logon account for the sql server
service is a domain (admin) account.
The odd thing now is, when I run the debugger, it actually runs, but does
not "step" thru the procedure, it goes straight thru to the end, and does not
show any of the values for the variables, connections, etc. In the results
pane, I get a result, and at the very bottom it says completed.
Putting in break points makes no difference, it just runs on to the end
regardless...
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Tom,
Thanks for your detailed information and descriptions!
First of all, The reason for Error 508 and Error code 0x80070005 is
permission issue. As you said it works fine before upgrading to XP SP2,
have you tried disable the Firewall os XP SP2. Close the firewall and then
tell me whether it works. Checking the following documents to make sure
your SQL Server in XP XP2 is correctly configured
How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
http://support.microsoft.com/?id=841249
Secondly, The error 0x800706ba generated is a Distributed COM communication
error when using RPC. The error code 0x800706ba translates to "RPC server
unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
allocation. By default, RPC dynamic port allocation randomly selects port
numbers above 1024, so the problem seems to be the firewall blocking the
rpc communication. Do the folloing things to make sure DCOM is running
fine.
* On the client machine
--run dcomcnfg.
--Go to the dcom config properties under default properties, there is a
check box stating "enable Distributed COM on this computer", if this was
unchecked on the client machine, this is a default setting that is checked.
make sure it is checked.
*Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
replcaed the old ones with these on the client machine and then ran the
regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
/RegServer on the client machine and rebooted the box and everything
started to work like a charm.Customer was able to debug the stored proc on
the server using SQL QA and t-sql debugger from his client machine.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||I have tried all the suggestions but the behavior remains the same: the
debugger connects and then runs thru the procedure beginning to end without
stopping, providing variable info, etc.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your detailed information and descriptions!
> First of all, The reason for Error 508 and Error code 0x80070005 is
> permission issue. As you said it works fine before upgrading to XP SP2,
> have you tried disable the Firewall os XP SP2. Close the firewall and then
> tell me whether it works. Checking the following documents to make sure
> your SQL Server in XP XP2 is correctly configured
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
> http://support.microsoft.com/?id=841249
> Secondly, The error 0x800706ba generated is a Distributed COM communication
> error when using RPC. The error code 0x800706ba translates to "RPC server
> unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
> allocation. By default, RPC dynamic port allocation randomly selects port
> numbers above 1024, so the problem seems to be the firewall blocking the
> rpc communication. Do the folloing things to make sure DCOM is running
> fine.
> * On the client machine
> --run dcomcnfg.
> --Go to the dcom config properties under default properties, there is a
> check box stating "enable Distributed COM on this computer", if this was
> unchecked on the client machine, this is a default setting that is checked.
> make sure it is checked.
> *Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
> replcaed the old ones with these on the client machine and then ran the
> regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
> /RegServer on the client machine and rebooted the box and everything
> started to work like a charm.Customer was able to debug the stored proc on
> the server using SQL QA and t-sql debugger from his client machine.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||I found the problem: after applying the hotfix to the server, the client side
tools were no longer the same version as the server. Applying the hotfix to
the client fixed the problem.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your detailed information and descriptions!
> First of all, The reason for Error 508 and Error code 0x80070005 is
> permission issue. As you said it works fine before upgrading to XP SP2,
> have you tried disable the Firewall os XP SP2. Close the firewall and then
> tell me whether it works. Checking the following documents to make sure
> your SQL Server in XP XP2 is correctly configured
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
> http://support.microsoft.com/?id=841249
> Secondly, The error 0x800706ba generated is a Distributed COM communication
> error when using RPC. The error code 0x800706ba translates to "RPC server
> unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
> allocation. By default, RPC dynamic port allocation randomly selects port
> numbers above 1024, so the problem seems to be the firewall blocking the
> rpc communication. Do the folloing things to make sure DCOM is running
> fine.
> * On the client machine
> --run dcomcnfg.
> --Go to the dcom config properties under default properties, there is a
> check box stating "enable Distributed COM on this computer", if this was
> unchecked on the client machine, this is a default setting that is checked.
> make sure it is checked.
> *Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
> replcaed the old ones with these on the client machine and then ran the
> regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
> /RegServer on the client machine and rebooted the box and everything
> started to work like a charm.Customer was able to debug the stored proc on
> the server using SQL QA and t-sql debugger from his client machine.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||I also have the same problem after installed Windows XP SP2,
the similar error message when running debugger from query analyzer.
Is there any solution for this?
"TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...[vbcol=seagreen]
> I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
> this prior to installing XP sp2 on the WinXP system. The sql server is at
> sp3, and runs on a Windows 2003 system.
> The message I get when running the debugger is:
> Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
> on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
> SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
> connection 58.
> ""Mingqing Cheng [MSFT]"" wrote:
|||Check my last entry on this topic, dated 9/16/2004
"duandd" wrote:
> I also have the same problem after installed Windows XP SP2,
> the similar error message when running debugger from query analyzer.
> Is there any solution for this?
> "TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...
>
this (posted by an MS support person). The post indicated this fix would take
care of the Visual Studio issue, and well as Query Analyzer.
I got the hotfix and installed it on my development server, however my XP
sp2 system still cannot remotely debug the server. I have always been able to
do this, until I installed XP sp2.
Any suggestions? Has anyone gotten remote debugging to work post XP sp2
installation?
Thanks for your assistance,
Tom
Hi Tom,
Thanks for your posting!
From your descriptions, I understood that you could not use Query Analyzer
to debug after XP SP2 was upgraded. Have I understood you? If there is
anything I misunderstood, please feel free to let me know.
Based on my scope, as you said it is a remotely debugging ,does it used a
linked server? If so, here is another document address the probelm you may
meet.
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Microsoft
Windows XP Service Pack 2
http://support.microsoft.com/default.aspx?kbid=839279
What's the error message when it fails to debugging? Could you tell me how
to reporduce it on my machine? I need more detailed information for your
scenario, which, I believe, will make us closer to the resolution.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
this prior to installing XP sp2 on the WinXP system. The sql server is at
sp3, and runs on a Windows 2003 system.
The message I get when running the debugger is:
Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
connection 58.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||This is the error in the event log of the SQL Server system:
Event Type:Error
Event Source:SQLDebugging98
Event Category:None
Event ID:1
Date:9/14/2004
Time:9:45:19 PM
User:N/A
Computer:HESPERUS
Description:
SQL Server is running as 'Force3\SQLAdmin' and cannot connect to the
debugger on machine 'TOMHOME2' (error = 0x80070005 Access is denied. ). Use
one of the following options to fix this error. 1) Run SQL Server as "Local
System", as a domain account, or as a local account with identical usernames
and passwords on both machine 'HESPERUS' and 'TOMHOME2'. 2) Verify that
machine 'HESPERUS' can open files on machine 'TOMHOME2'. Debugging disabled
for connection 56.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This setup did work prior to XP sp2, the logon account for the sql server
service is a domain (admin) account.
The odd thing now is, when I run the debugger, it actually runs, but does
not "step" thru the procedure, it goes straight thru to the end, and does not
show any of the values for the variables, connections, etc. In the results
pane, I get a result, and at the very bottom it says completed.
Putting in break points makes no difference, it just runs on to the end
regardless...
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Tom,
Thanks for your detailed information and descriptions!
First of all, The reason for Error 508 and Error code 0x80070005 is
permission issue. As you said it works fine before upgrading to XP SP2,
have you tried disable the Firewall os XP SP2. Close the firewall and then
tell me whether it works. Checking the following documents to make sure
your SQL Server in XP XP2 is correctly configured
How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
http://support.microsoft.com/?id=841249
Secondly, The error 0x800706ba generated is a Distributed COM communication
error when using RPC. The error code 0x800706ba translates to "RPC server
unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
allocation. By default, RPC dynamic port allocation randomly selects port
numbers above 1024, so the problem seems to be the firewall blocking the
rpc communication. Do the folloing things to make sure DCOM is running
fine.
* On the client machine
--run dcomcnfg.
--Go to the dcom config properties under default properties, there is a
check box stating "enable Distributed COM on this computer", if this was
unchecked on the client machine, this is a default setting that is checked.
make sure it is checked.
*Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
replcaed the old ones with these on the client machine and then ran the
regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
/RegServer on the client machine and rebooted the box and everything
started to work like a charm.Customer was able to debug the stored proc on
the server using SQL QA and t-sql debugger from his client machine.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||I have tried all the suggestions but the behavior remains the same: the
debugger connects and then runs thru the procedure beginning to end without
stopping, providing variable info, etc.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your detailed information and descriptions!
> First of all, The reason for Error 508 and Error code 0x80070005 is
> permission issue. As you said it works fine before upgrading to XP SP2,
> have you tried disable the Firewall os XP SP2. Close the firewall and then
> tell me whether it works. Checking the following documents to make sure
> your SQL Server in XP XP2 is correctly configured
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
> http://support.microsoft.com/?id=841249
> Secondly, The error 0x800706ba generated is a Distributed COM communication
> error when using RPC. The error code 0x800706ba translates to "RPC server
> unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
> allocation. By default, RPC dynamic port allocation randomly selects port
> numbers above 1024, so the problem seems to be the firewall blocking the
> rpc communication. Do the folloing things to make sure DCOM is running
> fine.
> * On the client machine
> --run dcomcnfg.
> --Go to the dcom config properties under default properties, there is a
> check box stating "enable Distributed COM on this computer", if this was
> unchecked on the client machine, this is a default setting that is checked.
> make sure it is checked.
> *Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
> replcaed the old ones with these on the client machine and then ran the
> regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
> /RegServer on the client machine and rebooted the box and everything
> started to work like a charm.Customer was able to debug the stored proc on
> the server using SQL QA and t-sql debugger from his client machine.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||I found the problem: after applying the hotfix to the server, the client side
tools were no longer the same version as the server. Applying the hotfix to
the client fixed the problem.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your detailed information and descriptions!
> First of all, The reason for Error 508 and Error code 0x80070005 is
> permission issue. As you said it works fine before upgrading to XP SP2,
> have you tried disable the Firewall os XP SP2. Close the firewall and then
> tell me whether it works. Checking the following documents to make sure
> your SQL Server in XP XP2 is correctly configured
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
> http://support.microsoft.com/?id=841249
> Secondly, The error 0x800706ba generated is a Distributed COM communication
> error when using RPC. The error code 0x800706ba translates to "RPC server
> unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
> allocation. By default, RPC dynamic port allocation randomly selects port
> numbers above 1024, so the problem seems to be the firewall blocking the
> rpc communication. Do the folloing things to make sure DCOM is running
> fine.
> * On the client machine
> --run dcomcnfg.
> --Go to the dcom config properties under default properties, there is a
> check box stating "enable Distributed COM on this computer", if this was
> unchecked on the client machine, this is a default setting that is checked.
> make sure it is checked.
> *Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
> replcaed the old ones with these on the client machine and then ran the
> regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
> /RegServer on the client machine and rebooted the box and everything
> started to work like a charm.Customer was able to debug the stored proc on
> the server using SQL QA and t-sql debugger from his client machine.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||I also have the same problem after installed Windows XP SP2,
the similar error message when running debugger from query analyzer.
Is there any solution for this?
"TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...[vbcol=seagreen]
> I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
> this prior to installing XP sp2 on the WinXP system. The sql server is at
> sp3, and runs on a Windows 2003 system.
> The message I get when running the debugger is:
> Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
> on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
> SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
> connection 58.
> ""Mingqing Cheng [MSFT]"" wrote:
|||Check my last entry on this topic, dated 9/16/2004
"duandd" wrote:
> I also have the same problem after installed Windows XP SP2,
> the similar error message when running debugger from query analyzer.
> Is there any solution for this?
> "TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...
>
Subscribe to:
Posts (Atom)