Problem Executing Stored Procedures

We have an application that uses SQL-DMO to get a directory/file list for
the server by calling extended stored procedures, xp_availablemedia and
xp_cmdshell.
The app can successfully navigate the directories on systems running MSDE on
Window 2000 Professional, however, it fails to do so on systems running
Windows XP Professional.
There are no differences in the environment the application runs in other
than the version of Windows. Some of the characteristics of the
installation are: The application and MSDE are installed using the same
scripts on both versions of Windows. The systems where the app is installed
are running as part of a workgroup rather than a domain. We have run
svrnetcn.exe and verified that named pipes and TCP/IP are both enabled. To
confirm SQLDMO is enabled SQLDMO.DLL has been registered successfully from
the command line. Also, we can successfully execute the stored procedures
using SQL statements so it appears that the problems are related to SQLDMO.
Is there something additional or different that needs to be done on XP than
on 2000? Does anyone have any suggestions about how to solve this problem?
Thanks,
Tom
hi Tom,
"news.microsoft.com" <tom@.nospam.waspbarcode.com> ha scritto nel messaggio
news:erdTNo4EEHA.2740@.TK2MSFTNGP11.phx.gbl...
> We have an application that uses SQL-DMO to get a directory/file list for
> the server by calling extended stored procedures, xp_availablemedia and
> xp_cmdshell.
> The app can successfully navigate the directories on systems running MSDE
on
> Window 2000 Professional, however, it fails to do so on systems running
> Windows XP Professional.
> There are no differences in the environment the application runs in other
> than the version of Windows. Some of the characteristics of the
> installation are: The application and MSDE are installed using the same
> scripts on both versions of Windows. The systems where the app is
installed
> are running as part of a workgroup rather than a domain. We have run
> svrnetcn.exe and verified that named pipes and TCP/IP are both enabled.
To
> confirm SQLDMO is enabled SQLDMO.DLL has been registered successfully from
> the command line. Also, we can successfully execute the stored procedures
> using SQL statements so it appears that the problems are related to
SQLDMO.
> Is there something additional or different that needs to be done on XP
than
> on 2000? Does anyone have any suggestions about how to solve this
problem?
I do currently use SQL-DMO with success both on Win2k, WinXP pro and Win2003
server std...
I never had the need to manually register this somponent when installing
MSDE, and both procedures you are mentioning are run with success...
just the usual caveat... does the account running SQL Server and SQL Server
Agent have the right privileges?
did you try some simple SQL-DMO code to test it?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||My guess is that it has nothing to do with SQL-DMO, but is instead a
permissions issue. xp_cmdshell has some rather strict permission issues when
being invoked (its all in the BOL). By default, XP has a tighter security
setup than Win2k, so this may be part of the root of your problem. The fact
that you are running in a Workgroup network may also be a factor, since its
security model is much different than a domain network. Have you tried
running the profiler against the system when running your application
against MSDE on XP?
Jim
"news.microsoft.com" <tom@.nospam.waspbarcode.com> wrote in message
news:erdTNo4EEHA.2740@.TK2MSFTNGP11.phx.gbl...
> We have an application that uses SQL-DMO to get a directory/file list for
> the server by calling extended stored procedures, xp_availablemedia and
> xp_cmdshell.
> The app can successfully navigate the directories on systems running MSDE
on
> Window 2000 Professional, however, it fails to do so on systems running
> Windows XP Professional.
> There are no differences in the environment the application runs in other
> than the version of Windows. Some of the characteristics of the
> installation are: The application and MSDE are installed using the same
> scripts on both versions of Windows. The systems where the app is
installed
> are running as part of a workgroup rather than a domain. We have run
> svrnetcn.exe and verified that named pipes and TCP/IP are both enabled.
To
> confirm SQLDMO is enabled SQLDMO.DLL has been registered successfully from
> the command line. Also, we can successfully execute the stored procedures
> using SQL statements so it appears that the problems are related to
SQLDMO.
> Is there something additional or different that needs to be done on XP
than
> on 2000? Does anyone have any suggestions about how to solve this
problem?
> Thanks,
> Tom
>
|||Andrea,
Both SQL Server and Agent are running under the Local System account so they
should have enough privileges.
The same application that is having problems with SQL-DMO also uses SQL-DMO
to do backups and restores and doesn't have any problems.
Tom
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:c43nsp$2cuegu$1@.ID-207518.news.uni-berlin.de...
> hi Tom,
> "news.microsoft.com" <tom@.nospam.waspbarcode.com> ha scritto nel messaggio
> news:erdTNo4EEHA.2740@.TK2MSFTNGP11.phx.gbl...
for
MSDE
> on
other
> installed
> To
from
procedures
> SQLDMO.
> than
> problem?
> I do currently use SQL-DMO with success both on Win2k, WinXP pro and
Win2003
> server std...
> I never had the need to manually register this somponent when installing
> MSDE, and both procedures you are mentioning are run with success...
> just the usual caveat... does the account running SQL Server and SQL
Server
> Agent have the right privileges?
> did you try some simple SQL-DMO code to test it?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Jim,
Unfortunately, we have no control over the environment where the application
is being run, but, it is usually installed on stand-alone systems or
pier-to-pier networks that are not part of a domain.
Since we can execute the extended stored procedures in SQL commands, we are
in the process of converting the application so it doesn't use SQL-DMO
except for the backup and restore.
Tom
"J Young" <thorium48@.hotmail.com> wrote in message
news:OTbnG4OFEHA.2052@.TK2MSFTNGP11.phx.gbl...
> My guess is that it has nothing to do with SQL-DMO, but is instead a
> permissions issue. xp_cmdshell has some rather strict permission issues
when
> being invoked (its all in the BOL). By default, XP has a tighter security
> setup than Win2k, so this may be part of the root of your problem. The
fact
> that you are running in a Workgroup network may also be a factor, since
its
> security model is much different than a domain network. Have you tried
> running the profiler against the system when running your application
> against MSDE on XP?
> Jim
> "news.microsoft.com" <tom@.nospam.waspbarcode.com> wrote in message
> news:erdTNo4EEHA.2740@.TK2MSFTNGP11.phx.gbl...
for
MSDE
> on
other
> installed
> To
from
procedures
> SQLDMO.
> than
> problem?
>

Problem debugging SQL stored procedures

Hi
I am having a problem debugging SQL stored procedures from VS.net.
With some connections it works fine but when I try to debug a stored
procedure in a database on my local machine it fails. When I right
click and select Step into stored procedure' VS.NET goes into run
mode but I don't get the yellow arrow and I am unable to step through
the code.
I'm new to SQL and I'm not sure what other information I can supply
that will help. The connections themselves are the same except with
one the server is on my machine and with the other the server is on my
local network. If anyone has any suggestions as to what I can try to
get this to work, or even better, if someone has seen this problem
before, any help will be gratefully appreciated!
Many thanks
Julia.
Julia
Do you mean by click on Server Explorer in .NET Project?
Are you getting aby error when you run the SP?
For me it works fine.
I'd write TRY CATCH error handler in .NET with SQLExceptions to see what is
an error trown by SQL Server?
or run this stored procedure in QA and debug it step by step
"Julia" <julia_beresford@.hotmail.com> wrote in message
news:6c293529.0407260531.28ba29cf@.posting.google.c om...
> Hi
> I am having a problem debugging SQL stored procedures from VS.net.
> With some connections it works fine but when I try to debug a stored
> procedure in a database on my local machine it fails. When I right
> click and select 'Step into stored procedure' VS.NET goes into run
> mode but I don't get the yellow arrow and I am unable to step through
> the code.
> I'm new to SQL and I'm not sure what other information I can supply
> that will help. The connections themselves are the same except with
> one the server is on my machine and with the other the server is on my
> local network. If anyone has any suggestions as to what I can try to
> get this to work, or even better, if someone has seen this problem
> before, any help will be gratefully appreciated!
> Many thanks
> Julia.
|||Julia
Hi Uri, in answer to your questions...
Do you mean by click on Server Explorer in .NET Project?[vbcol=seagreen]
Are you getting aby error when you run the SP?[vbcol=seagreen]
debug mode or when I right click on the stored procedure in Server
Explorer and select Step Into Stored Procedure'.
For me it works fine.[vbcol=seagreen]
EXCEPT those on localhost
I'd write TRY CATCH error handler in .NET with SQLExceptions to see
what is an error trown by SQL Server? or run this stored procedure in
QA and debug it step by step[vbcol=seagreen]
suggested but now I get the errors...
"Unable to start SQL debug session. Ensure sqldbreg2.exe and
proxy/stub DLL sqldbg.dll are registered on this machine."
And
"Attaching the T-SQL debugger to process [2104] StoredProcedures.exe'
on machine BWJULIA' failed. Error code 0x80004004."
...when I try to run the stored procedure with a breakpoint on it.
This is different to yesterday and I haven't changed anything (that
I'm aware of). I have registered sqldbreg2.exe and sqldbg.dll. I
also have the correct permissions set on the required folders for user
SQLDebugger.
Has anyone got debugging stored procedures to work successfully? The
behavior on my machine seems temperamental and I'm not sure if I need
to reinstall VS.NET.
Many thanks again for any help anyone can offer.
Regards
Julia Beresford.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<utL0eYxcEHA.3420@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Julia
> Do you mean by click on Server Explorer in .NET Project?
> Are you getting aby error when you run the SP?
> For me it works fine.
> I'd write TRY CATCH error handler in .NET with SQLExceptions to see what is
> an error trown by SQL Server?
> or run this stored procedure in QA and debug it step by step
>
> "Julia" <julia_beresford@.hotmail.com> wrote in message
> news:6c293529.0407260531.28ba29cf@.posting.google.c om...

Problem debugging SQL stored procedures

Hi
I am having a problem debugging SQL stored procedures from VS.net.
With some connections it works fine but when I try to debug a stored
procedure in a database on my local machine it fails. When I right
click and select ?Step into stored procedure' VS.NET goes into run
mode but I don't get the yellow arrow and I am unable to step through
the code.
I'm new to SQL and I'm not sure what other information I can supply
that will help. The connections themselves are the same except with
one the server is on my machine and with the other the server is on my
local network. If anyone has any suggestions as to what I can try to
get this to work, or even better, if someone has seen this problem
before, any help will be gratefully appreciated!
Many thanks
Julia.Julia
Do you mean by click on Server Explorer in .NET Project?
Are you getting aby error when you run the SP?
For me it works fine.
I'd write TRY CATCH error handler in .NET with SQLExceptions to see what is
an error trown by SQL Server?
or run this stored procedure in QA and debug it step by step
"Julia" <julia_beresford@.hotmail.com> wrote in message
news:6c293529.0407260531.28ba29cf@.posting.google.com...
> Hi
> I am having a problem debugging SQL stored procedures from VS.net.
> With some connections it works fine but when I try to debug a stored
> procedure in a database on my local machine it fails. When I right
> click and select 'Step into stored procedure' VS.NET goes into run
> mode but I don't get the yellow arrow and I am unable to step through
> the code.
> I'm new to SQL and I'm not sure what other information I can supply
> that will help. The connections themselves are the same except with
> one the server is on my machine and with the other the server is on my
> local network. If anyone has any suggestions as to what I can try to
> get this to work, or even better, if someone has seen this problem
> before, any help will be gratefully appreciated!
> Many thanks
> Julia.|||Julia
Hi Uri, in answer to your questions...
Do you mean by click on Server Explorer in .NET Project?
>> YES
Are you getting aby error when you run the SP?
>> YES, either by executing it from VB code and running the VB code in
debug mode or when I right click on the stored procedure in Server
Explorer and select ?Step Into Stored Procedure'.
For me it works fine.
>> For me it works (worked! ? see below) fine on all SQL databases
EXCEPT those on localhost
I'd write TRY CATCH error handler in .NET with SQLExceptions to see
what is an error trown by SQL Server? or run this stored procedure in
QA and debug it step by step
>> This morning I came in to work with the intention of doing as you
suggested but now I get the errors...
"Unable to start SQL debug session. Ensure sqldbreg2.exe and
proxy/stub DLL sqldbg.dll are registered on this machine."
And
"Attaching the T-SQL debugger to process ?[2104] StoredProcedures.exe'
on machine ?BWJULIA' failed. Error code 0x80004004."
...when I try to run the stored procedure with a breakpoint on it.
This is different to yesterday and I haven't changed anything (that
I'm aware of). I have registered sqldbreg2.exe and sqldbg.dll. I
also have the correct permissions set on the required folders for user
SQLDebugger.
Has anyone got debugging stored procedures to work successfully? The
behavior on my machine seems temperamental and I'm not sure if I need
to reinstall VS.NET.
Many thanks again for any help anyone can offer.
Regards
Julia Beresford.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<utL0eYxcEHA.3420@.TK2MSFTNGP12.phx.gbl>...
> Julia
> Do you mean by click on Server Explorer in .NET Project?
> Are you getting aby error when you run the SP?
> For me it works fine.
> I'd write TRY CATCH error handler in .NET with SQLExceptions to see what is
> an error trown by SQL Server?
> or run this stored procedure in QA and debug it step by step
>
> "Julia" <julia_beresford@.hotmail.com> wrote in message
> news:6c293529.0407260531.28ba29cf@.posting.google.com...
> > Hi
> >
> > I am having a problem debugging SQL stored procedures from VS.net.
> > With some connections it works fine but when I try to debug a stored
> > procedure in a database on my local machine it fails. When I right
> > click and select 'Step into stored procedure' VS.NET goes into run
> > mode but I don't get the yellow arrow and I am unable to step through
> > the code.
> >
> > I'm new to SQL and I'm not sure what other information I can supply
> > that will help. The connections themselves are the same except with
> > one the server is on my machine and with the other the server is on my
> > local network. If anyone has any suggestions as to what I can try to
> > get this to work, or even better, if someone has seen this problem
> > before, any help will be gratefully appreciated!
> >
> > Many thanks
> >
> > Julia.

Problem debugging SQL stored procedures

Hi
I am having a problem debugging SQL stored procedures from VS.net.
With some connections it works fine but when I try to debug a stored
procedure in a database on my local machine it fails. When I right
click and select Step into stored procedure' VS.NET goes into run
mode but I don't get the yellow arrow and I am unable to step through
the code.
I'm new to SQL and I'm not sure what other information I can supply
that will help. The connections themselves are the same except with
one the server is on my machine and with the other the server is on my
local network. If anyone has any suggestions as to what I can try to
get this to work, or even better, if someone has seen this problem
before, any help will be gratefully appreciated!
Many thanks
Julia.Julia
Do you mean by click on Server Explorer in .NET Project?
Are you getting aby error when you run the SP?
For me it works fine.
I'd write TRY CATCH error handler in .NET with SQLExceptions to see what is
an error trown by SQL Server?
or run this stored procedure in QA and debug it step by step
"Julia" <julia_beresford@.hotmail.com> wrote in message
news:6c293529.0407260531.28ba29cf@.posting.google.com...
> Hi
> I am having a problem debugging SQL stored procedures from VS.net.
> With some connections it works fine but when I try to debug a stored
> procedure in a database on my local machine it fails. When I right
> click and select 'Step into stored procedure' VS.NET goes into run
> mode but I don't get the yellow arrow and I am unable to step through
> the code.
> I'm new to SQL and I'm not sure what other information I can supply
> that will help. The connections themselves are the same except with
> one the server is on my machine and with the other the server is on my
> local network. If anyone has any suggestions as to what I can try to
> get this to work, or even better, if someone has seen this problem
> before, any help will be gratefully appreciated!
> Many thanks
> Julia.|||Julia
Hi Uri, in answer to your questions...
Do you mean by click on Server Explorer in .NET Project?[vbcol=seagreen]
Are you getting aby error when you run the SP?[vbcol=seagreen]
debug mode or when I right click on the stored procedure in Server
Explorer and select Step Into Stored Procedure'.
For me it works fine.[vbcol=seagreen]
EXCEPT those on localhost
I'd write TRY CATCH error handler in .NET with SQLExceptions to see
what is an error trown by SQL Server? or run this stored procedure in
QA and debug it step by step[vbcol=seagreen]
suggested but now I get the errors...
"Unable to start SQL debug session. Ensure sqldbreg2.exe and
proxy/stub DLL sqldbg.dll are registered on this machine."
And
"Attaching the T-SQL debugger to process [2104] StoredProcedures.exe'
on machine BWJULIA' failed. Error code 0x80004004."
...when I try to run the stored procedure with a breakpoint on it.
This is different to yesterday and I haven't changed anything (that
I'm aware of). I have registered sqldbreg2.exe and sqldbg.dll. I
also have the correct permissions set on the required folders for user
SQLDebugger.
Has anyone got debugging stored procedures to work successfully? The
behavior on my machine seems temperamental and I'm not sure if I need
to reinstall VS.NET.
Many thanks again for any help anyone can offer.
Regards
Julia Beresford.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<utL0eYxcEHA.3420@.TK2MSFTNGP12.phx.gbl
>...[vbcol=seagreen]
> Julia
> Do you mean by click on Server Explorer in .NET Project?
> Are you getting aby error when you run the SP?
> For me it works fine.
> I'd write TRY CATCH error handler in .NET with SQLExceptions to see what i
s
> an error trown by SQL Server?
> or run this stored procedure in QA and debug it step by step
>
> "Julia" <julia_beresford@.hotmail.com> wrote in message
> news:6c293529.0407260531.28ba29cf@.posting.google.com...

Problem creating stored procedures

I am getting the following error message every time I try to create a stored procedure - any stored procedure:

Msg 6354, Level 16, State 10, Procedure AuditOperations, Line 14

Target string size is too small to represent the XML instance

A search of books online and MSDN didn't return anything.

Thanks.

Please post some code.

Adamus

|||

OK, but the code doesn't appear to matter. I get the message for any view or stored proc I try to create. I've scripted some of AW views and stored procedures with the same result.

CREATE PROCEDURE ListEmployeesByDepartment

@.DepartmentName NVARCHAR(50)

AS

SELECT c.Lastname, c.FirstName

FROM Person.Contact c

INNER JOIN HumanResources.Employee e

ON c.ContactID = e.ContactID

INNER JOIN HumanResources.EmployeeDepartmentHistory h

ON e.EmployeeID = h.EmployeeID

INNER JOIN HumanResources.Department d

ON h.DepartmentID = d.DepartmentID

WHERE d.name = @.DepartmentName AND h.EndDate IS NULL

ORDER BY 1

|||

TennesseeSQL wrote:

OK, but the code doesn't appear to matter. I get the message for any view or stored proc I try to create. I've scripted some of AW views and stored procedures with the same result.

Do you have any triggers on the table?

If so, disable them temporarily.

Adamus

|||The message indicates the object in which the error happened. So check "AuditOperations" SP or trigger code.|||Geez! How stupid?! I'm working through examples preparing for the 70-441 test. That was it. Thanks so much!