Remote SP debugging from VS 2003: error

I 'm still getting the error:
"Cannot debug stored procedures because the SQL Server database is not setup
correctly or user does not have permission to execute master.sp_sdidebug."
I have configured DCOM on the remote sql server according tothe MSDN
article.
I have added SQLDebugger account to default permissions in dcom.
I have also given full access to Master DB for the user
SQLDebugger and all stored procedures full access.
Does anybody have any idea why is it still giving the above error.
TIA
TSHi
Did you check out
http://support.microsoft.com/default.aspx?scid=kb;en-us;817178
John
"test" wrote:
> I 'm still getting the error:
> "Cannot debug stored procedures because the SQL Server database is not setup
> correctly or user does not have permission to execute master.sp_sdidebug."
> I have configured DCOM on the remote sql server according tothe MSDN
> article.
> I have added SQLDebugger account to default permissions in dcom.
> I have also given full access to Master DB for the user
> SQLDebugger and all stored procedures full access.
> Does anybody have any idea why is it still giving the above error.
> TIA
> TS
>
>|||Hi John,
Thanks for the reply.
According to the support article
I ran EXECUTE sp_sdidebug 'LEGACY_ON'
on the remote DB server on QA.
I am afraid it still gives me the same error.
In the system log file and the Application log file of the remote server
following errors were recorded respectively.
DCOM was unable to communicate with the computer WS06 using any of the
configured protocols.
Error: 504, Severity: 16, State: 1
Unable to connect to debugger on MELB (Error = 0x800706ba). Ensure that
client-side components, such as SQLDBREG.EXE, are installed and registered
on WS06. Debugging disabled for connection 58.
JFI: SQLDBREG.EXE is found on client machine and executed too.
thanks
TS
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8A208FDB-FBC4-43C0-A94F-BC2792FCE9EB@.microsoft.com...
> Hi
> Did you check out
> http://support.microsoft.com/default.aspx?scid=kb;en-us;817178
> John
> "test" wrote:
>> I 'm still getting the error:
>> "Cannot debug stored procedures because the SQL Server database is not
>> setup
>> correctly or user does not have permission to execute
>> master.sp_sdidebug."
>> I have configured DCOM on the remote sql server according tothe MSDN
>> article.
>> I have added SQLDebugger account to default permissions in dcom.
>> I have also given full access to Master DB for the user
>> SQLDebugger and all stored procedures full access.
>> Does anybody have any idea why is it still giving the above error.
>> TIA
>> TS
>>
>>|||Hi
This looks like:
http://support.microsoft.com/default.aspx?scid=kb;en-us;839280
Failing that you may want to check RPC is running
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servtools_5cfm.asp
and if you can use Query Analyser for debugging from this machine and others
as well as on the server.
John
"test" wrote:
> Hi John,
> Thanks for the reply.
> According to the support article
> I ran EXECUTE sp_sdidebug 'LEGACY_ON'
> on the remote DB server on QA.
> I am afraid it still gives me the same error.
>
> In the system log file and the Application log file of the remote server
> following errors were recorded respectively.
> DCOM was unable to communicate with the computer WS06 using any of the
> configured protocols.
> Error: 504, Severity: 16, State: 1
> Unable to connect to debugger on MELB (Error = 0x800706ba). Ensure that
> client-side components, such as SQLDBREG.EXE, are installed and registered
> on WS06. Debugging disabled for connection 58.
> JFI: SQLDBREG.EXE is found on client machine and executed too.
>
> thanks
> TS
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:8A208FDB-FBC4-43C0-A94F-BC2792FCE9EB@.microsoft.com...
> > Hi
> >
> > Did you check out
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;817178
> >
> > John
> >
> > "test" wrote:
> >
> >> I 'm still getting the error:
> >>
> >> "Cannot debug stored procedures because the SQL Server database is not
> >> setup
> >> correctly or user does not have permission to execute
> >> master.sp_sdidebug."
> >>
> >> I have configured DCOM on the remote sql server according tothe MSDN
> >> article.
> >> I have added SQLDebugger account to default permissions in dcom.
> >>
> >> I have also given full access to Master DB for the user
> >> SQLDebugger and all stored procedures full access.
> >>
> >> Does anybody have any idea why is it still giving the above error.
> >>
> >> TIA
> >> TS
> >>
> >>
> >>
> >>
>
>|||John,
By any chance can the problem that I am getting is due to the fact that we
are on two different domains, i.e. sql server and my working machine?
thanks
TS
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:58CD11E4-5320-4107-9384-15ECECC78C3D@.microsoft.com...
> Hi
> This looks like:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;839280
> Failing that you may want to check RPC is running
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servtools_5cfm.asp
> and if you can use Query Analyser for debugging from this machine and
> others
> as well as on the server.
> John
> "test" wrote:
>> Hi John,
>> Thanks for the reply.
>> According to the support article
>> I ran EXECUTE sp_sdidebug 'LEGACY_ON'
>> on the remote DB server on QA.
>> I am afraid it still gives me the same error.
>>
>> In the system log file and the Application log file of the remote server
>> following errors were recorded respectively.
>> DCOM was unable to communicate with the computer WS06 using any of the
>> configured protocols.
>> Error: 504, Severity: 16, State: 1
>> Unable to connect to debugger on MELB (Error = 0x800706ba). Ensure that
>> client-side components, such as SQLDBREG.EXE, are installed and
>> registered
>> on WS06. Debugging disabled for connection 58.
>> JFI: SQLDBREG.EXE is found on client machine and executed too.
>>
>> thanks
>> TS
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:8A208FDB-FBC4-43C0-A94F-BC2792FCE9EB@.microsoft.com...
>> > Hi
>> >
>> > Did you check out
>> >
>> > http://support.microsoft.com/default.aspx?scid=kb;en-us;817178
>> >
>> > John
>> >
>> > "test" wrote:
>> >
>> >> I 'm still getting the error:
>> >>
>> >> "Cannot debug stored procedures because the SQL Server database is not
>> >> setup
>> >> correctly or user does not have permission to execute
>> >> master.sp_sdidebug."
>> >>
>> >> I have configured DCOM on the remote sql server according tothe MSDN
>> >> article.
>> >> I have added SQLDebugger account to default permissions in dcom.
>> >>
>> >> I have also given full access to Master DB for the user
>> >> SQLDebugger and all stored procedures full access.
>> >>
>> >> Does anybody have any idea why is it still giving the above error.
>> >>
>> >> TIA
>> >> TS
>> >>
>> >>
>> >>
>> >>
>>|||Hi
I don't think that should be a problem expecially if you have a trusted
relationship between the two domains (which I assume you have!). The second
article points to a post SP3a hotfix. As you don't give you version numbe I
assume that you are still on SP3a, so you could try loading service pack 4 on
both client and server.
Did you try debugging from Query Analyser?
John
"test" wrote:
> John,
> By any chance can the problem that I am getting is due to the fact that we
> are on two different domains, i.e. sql server and my working machine?
> thanks
> TS
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:58CD11E4-5320-4107-9384-15ECECC78C3D@.microsoft.com...
> > Hi
> >
> > This looks like:
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;839280
> >
> > Failing that you may want to check RPC is running
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servtools_5cfm.asp
> >
> > and if you can use Query Analyser for debugging from this machine and
> > others
> > as well as on the server.
> >
> > John
> >
> > "test" wrote:
> >
> >> Hi John,
> >>
> >> Thanks for the reply.
> >>
> >> According to the support article
> >> I ran EXECUTE sp_sdidebug 'LEGACY_ON'
> >> on the remote DB server on QA.
> >> I am afraid it still gives me the same error.
> >>
> >>
> >> In the system log file and the Application log file of the remote server
> >> following errors were recorded respectively.
> >>
> >> DCOM was unable to communicate with the computer WS06 using any of the
> >> configured protocols.
> >>
> >> Error: 504, Severity: 16, State: 1
> >> Unable to connect to debugger on MELB (Error = 0x800706ba). Ensure that
> >> client-side components, such as SQLDBREG.EXE, are installed and
> >> registered
> >> on WS06. Debugging disabled for connection 58.
> >>
> >> JFI: SQLDBREG.EXE is found on client machine and executed too.
> >>
> >>
> >> thanks
> >> TS
> >>
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:8A208FDB-FBC4-43C0-A94F-BC2792FCE9EB@.microsoft.com...
> >> > Hi
> >> >
> >> > Did you check out
> >> >
> >> > http://support.microsoft.com/default.aspx?scid=kb;en-us;817178
> >> >
> >> > John
> >> >
> >> > "test" wrote:
> >> >
> >> >> I 'm still getting the error:
> >> >>
> >> >> "Cannot debug stored procedures because the SQL Server database is not
> >> >> setup
> >> >> correctly or user does not have permission to execute
> >> >> master.sp_sdidebug."
> >> >>
> >> >> I have configured DCOM on the remote sql server according tothe MSDN
> >> >> article.
> >> >> I have added SQLDebugger account to default permissions in dcom.
> >> >>
> >> >> I have also given full access to Master DB for the user
> >> >> SQLDebugger and all stored procedures full access.
> >> >>
> >> >> Does anybody have any idea why is it still giving the above error.
> >> >>
> >> >> TIA
> >> >> TS
> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Remote Database updates take long time to complete

Hi
I have have two linked SQL Servers and I am trying to get remote writes
working correctly (fast).

I have configured the DB link on both machines to:
Point at each others DB.
I have security set up to map each others server logins
and Server Options: Collation Compatible, Data Access, RPC, RPC Out, Use
Remote Collation all checked

My problem is that when a SP performs

Begin Transaction

Update Local Table

Update Remote Table

Commit Tran

It takes several seconds to complete. (about 7 seconds not acceptable to
us)
This is due to the remote update - how can I improve the response time?

example of a stored procedures that takes time

where ACSMSM is a remote (linked) SQL Server.

procedure [psm].ams_Update_VFE
@.strResult varchar(8) = 'Failure' output,
@.strErrorDesc varchar(512) = 'SP Not Executed' output,
@.strVFEID varchar(16),
@.strDescription varchar(64),
@.strVFEVirtualRoot varchar(255),
@.strVFEPhysicalRoot varchar(255),
@.strAuditPath varchar(255),
@.strDefaultBranding varchar(16),
@.strIPAddress varchar(23)
as
declare @.strStep varchar(32)
declare @.trancount int

Set XACT_ABORT ON
set @.trancount = @.@.trancount
set @.strStep = 'Start of Stored Proc'

if (@.trancount = 0)
BEGIN TRANSACTION mytran
else
save tran mytran

/* start insert sp code here */

set @.strStep = 'Write VFE to MSM'

update
ACSMSM.msmprim.msm.VFECONFIG
set
DESCRIPTION = @.strDescription,
VFEVIRTUALROOT = @.strVFEVirtualRoot,
VFEPHYSICALROOT = @.strVFEPhysicalRoot,
AUDITPATH = @.strAuditPath,
DEFAULTBRANDING = @.strDefaultBranding,
IPADDRESS = @.strIPAddress
where
VFEID = @.strVFEID;

set @.strStep = 'Write VFE to PSM'

update
ACSPSM.psmprim.psm.VFECONFIG
set
DESCRIPTION = @.strDescription,
VFEVIRTUALROOT = @.strVFEVirtualRoot,
VFEPHYSICALROOT = @.strVFEPhysicalRoot,
AUDITPATH = @.strAuditPath,
DEFAULTBRANDING = @.strDefaultBranding,
IPADDRESS = @.strIPAddress
where
VFEID = @.strVFEID

/* end insert sp code here */

if (@.@.error <> 0)
begin
rollback tran mytran
set @.strResult = 'Failure'
set @.strErrorDesc = 'Fail @. Step :' + @.strStep + ' Error : ' + @.@.Error
return -1969
end
else
begin
set @.strResult = 'Success'
set @.strErrorDesc = ''
end
-- commit tran if we started it

if (@.trancount = 0)
commit tran

return 0Steve Thorpe (stephenthorpe@.nospam.hotmail.com) writes:
> It takes several seconds to complete. (about 7 seconds not acceptable to
> us)
> This is due to the remote update - how can I improve the response time?

Have you tried moving the updates of the remote table to a stored
procedures that runs on the remote server, and then call that stored
procedure remotely? I would expect that to reduce the ping-pong over
the network.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I have found the cause of the problem:

'For linked server DELETEs or UPDATEs, SQL Server retrieves data from the
table, performs any filtering that is necessary, and then performs the
deletes or updates through the OLEDB rowset. This processing can result in a
round-trip to the remote server for each row that is to be deleted or
updated. '

The solution is:

EXEC remserver.master.dbo.sp_executesql
@.stmt = N'DELETE sometable WHERE col1 = 10 OR col1 = 20'

EXEC localserver.master.dbo.sp_executesql
@.stmt = N'DELETE sometable WHERE col1 = 10 OR col1 = 20'

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94004CF4CD0FYazorman@.127.0.0.1...
> Steve Thorpe (stephenthorpe@.nospam.hotmail.com) writes:
> > It takes several seconds to complete. (about 7 seconds not acceptable
to
> > us)
> > This is due to the remote update - how can I improve the response time?
> Have you tried moving the updates of the remote table to a stored
> procedures that runs on the remote server, and then call that stored
> procedure remotely? I would expect that to reduce the ping-pong over
> the network.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Remote connection issues

My experience with SQL Server Express 2005 has been nothing short of a nightmare.

It is installed correctly, and is running, I can connect through management studio express and have created a database, but only to instances running locally. If I try from another machine, it won't connect, saying that the problem may be due to the fact that by default, SQL Server does not allow remote connections.

However, I have enabled remote connections (through TCP/IP and named pipes). I have added sqlserver as a firewall exception, to no avail, and disabled the firewall as well. Nothing seems to work.

Ultimately, I want to connect to a database and execute queries from a VB program.

I am a competent programmer but I am at my wits end.

Hi,

from your remote machine are you able to ping?! have you added a Exception for SQL Port in firewall? Is SQL Browser services and RPC service are running?! Refer below threads also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1084910&SiteID=1 & http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=817457&SiteID=1

Hemantgiri S. Goswami

|||
Thanks Hemantgiri,

Yes, I can ping, I have added an exception (and turned off the firewall as well). SQL Browser is not running, I didn't think I need it. RCP seems fine.

Maybe there is another problem as well; when connecting locally an exception is raised:
System.Security.SecurityException was unhandled
Message="Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version.... " failed.

I still have no idea what to do, I can't progress until this is fixed.|||

Hi,

SSUser wrote:

Thanks Hemantgiri,

Yes, I can ping, I have added an exception (and turned off the firewall as well). SQL Browser is not running, I didn't think I need it. RCP seems fine.

Maybe there is another problem as well; when connecting locally an exception is raised:
System.Security.SecurityException was unhandled
Message="Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version.... " failed.

I still have no idea what to do, I can't progress until this is fixed.

refer threads for the error http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1027859&SiteID=1 & http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=881572&SiteID=1

SQL Browser service is required refer http://www.teratrax.com/articles/connecting_sql_server_express.html for why!!

HTH

Hemantgiri S. Gsowami