cannot execute xp_cmdshell. Access is denied while copying data over the network

Hi,

I am having some trouble copying data over my workgroup network from my Windows 2003 Server Machine (machineA with SQL SERVER 2005) to one of my network Machine's drive(MachineB).
Here is the T-SQL code that I am trying to execute:

EXEC xp_cmdshell 'copy D:\Data\file.txt \\MachineB\Documents'

Whenever I tried to execute the above piece of code, I get the error message "Access is denied", but if I try to copy the file from the Command Prompt (cmd.exe) with the copy command, the file copies fine over the network.

I have already searched over the internet and I found out that loads of people have the same issue, and they were suggested something like this:
"Check in Services and make sure that the MSSQLServer service is run as a domain user and that domain user has rights to these network resources."

Well it sounds plausible, but I don't know what are the exact steps to do this. How do I know which user is running the MSSQL Server service? Are they referring to the user which I use to connect to my SQL Server Database engine throuhg the SQL Server Management Studio?
Also they are suggesting 'domain user', and as I said before I do not have domain network just regular simple workgroup network.

Here are some details of the user that I use to login. I generally login into my Windows 2003 Server machine with user called 'User1' and I use the same 'User1' to connect to SQL Server through the Management Studio Screen.
Should I create a user called 'User1' on my MachineB(Destination Machine)?

I would really appreciate, if someone can give me detailed steps explaning how to solve this problem.

Thank you very much once again.

Open the Surface Area Configuration Manager. Go to the features section. Make sure that xp_cmdshell is enabled. (It is turned off by default.) Then once you have enabled xp_cmdshell, make sure that the SQL Server service account has the appropriate permissions granted.|||Hi Michael,

Thank you very much for your reply. xp_cmdshell is already enabled. How do I check the permissions of SQL Server Srvice account? Is that the same account which I use to connect to SQL SERVER through Management Studio?

Thanks

|||No. It is the Windows account that the SQL Server service is running under. Specifically, you can find this within SQL Server Configuration Manager.|||

Michael Hotek wrote:

No. It is the Windows account that the SQL Server service is running under. Specifically, you can find this within SQL Server Configuration Manager.

Ok I went to the SQL SERVER Configuration Manager. Then I clicked on SQL SERVER 2005 Services in the left pane. On the right pane, it listen 5-6 services running like Intergration Service, Analysis Service, Reporting Service, and so on. One of them was SQL SERVER(MSSQLSERVER), so I think this must be the one that you were talking about. I right clicked on it-> Properties. Under the properties tab, I saw Log on as: Built-in Account (radio button) was checked and in the dropdown below it, Local System was selected. So basically the SQL Server Service is running under Local System. Now how do I figure out what user is associated with Local System?

Thank you once again for the help.Much appreciated.

|||Local system is the local machine account. That would be why you are getting an access denied on a network resource. The SID for localsystem is not valid external to the machine. In order to be able to execute that command you would need to create a Windows login and then use that login to run the SQL Server services. That login would have to have permissions on the network resource you are trying to write to.|||Thanks a lot Michael, that really makes sense .

Now for the extension based on your reply. Are there any disadvantages of using a Windows user to run the SQL Server services instead of a Local Machine account?

Thank you very very much for your prompt reply and insightful suggestion.

|||No, in fact, there are entire feature sets that you can't use in SQL Server if you are running under the localsystem account, replication being one of them. I don't run any SQL Server under a local machine account. I always run them under specific user accounts and in particular regular domain user accounts.|||I just did this and it worked like a charm. You someone already posted the answer. Go to SQL Server Configuration Manager. Right click on Sql Server, click properties and change the user account to run under a domain or local pc account. that has access to the share.

cannot execute xp_cmdshell. Access is denied while copying data over the network

Hi,

I am having some trouble copying data over my workgroup network from my Windows 2003 Server Machine (machineA with SQL SERVER 2005) to one of my network Machine's drive(MachineB).
Here is the T-SQL code that I am trying to execute:

EXEC xp_cmdshell 'copy D:\Data\file.txt \\MachineB\Documents'

Whenever I tried to execute the above piece of code, I get the error message "Access is denied", but if I try to copy the file from the Command Prompt (cmd.exe) with the copy command, the file copies fine over the network.

I have already searched over the internet and I found out that loads of people have the same issue, and they were suggested something like this:
"Check in Services and make sure that the MSSQLServer service is run as

a domain user and that domain user has rights to these network

resources."

Well it sounds plausible, but I don't know what are the exact steps to do this. How do I know which user is running the MSSQL Server service? Are they referring to the user which I use to connect to my SQL Server Database engine throuhg the SQL Server Management Studio?
Also they are suggesting 'domain user', and as I said before I do not have domain network just regular simple workgroup network.

Here are some details of the user that I use to login. I generally login into my Windows 2003 Server machine with user called 'User1' and I use the same 'User1' to connect to SQL Server through the Management Studio Screen.
Should I create a user called 'User1' on my MachineB(Destination Machine)?

I would really appreciate, if someone can give me detailed steps explaning how to solve this problem.

Thank you very much once again.Open the Surface Area Configuration Manager. Go to the features section. Make sure that xp_cmdshell is enabled. (It is turned off by default.) Then once you have enabled xp_cmdshell, make sure that the SQL Server service account has the appropriate permissions granted.|||Hi Michael,

Thank you very much for your reply. xp_cmdshell is already enabled. How do I check the permissions of SQL Server Srvice account? Is that the same account which I use to connect to SQL SERVER through Management Studio?

Thanks|||No. It is the Windows account that the SQL Server service is running under. Specifically, you can find this within SQL Server Configuration Manager.|||

Michael Hotek wrote:

No. It is the Windows account that the SQL Server service is running under. Specifically, you can find this within SQL Server Configuration Manager.

Ok I went to the SQL SERVER Configuration Manager. Then I clicked on SQL SERVER 2005 Services in the left pane. On the right pane, it listen 5-6 services running like Intergration Service, Analysis Service, Reporting Service, and so on. One of them was SQL SERVER(MSSQLSERVER), so I think this must be the one that you were talking about. I right clicked on it-> Properties. Under the properties tab, I saw Log on as: Built-in Account (radio button) was checked and in the dropdown below it, Local System was selected. So basically the SQL Server Service is running under Local System. Now how do I figure out what user is associated with Local System?

Thank you once again for the help.Much appreciated.|||Local system is the local machine account. That would be why you are getting an access denied on a network resource. The SID for localsystem is not valid external to the machine. In order to be able to execute that command you would need to create a Windows login and then use that login to run the SQL Server services. That login would have to have permissions on the network resource you are trying to write to.|||Thanks a lot Michael, that really makes sense .

Now for the extension based on your reply. Are there any disadvantages of using a Windows user to run the SQL Server services instead of a Local Machine account?

Thank you very very much for your prompt reply and insightful suggestion.|||No, in fact, there are entire feature sets that you can't use in SQL Server if you are running under the localsystem account, replication being one of them. I don't run any SQL Server under a local machine account. I always run them under specific user accounts and in particular regular domain user accounts.|||I just did this and it worked like a charm. You someone already posted the answer. Go to SQL Server Configuration Manager. Right click on Sql Server, click properties and change the user account to run under a domain or local pc account. that has access to the share.

cannot execute xp_cmdshell. Access is denied while copying data over the network

Hi,

I am havingsome trouble copying data over my workgroup network from my Windows2003 Server Machine (machineA with SQL SERVER 2005) to one of mynetwork Machine's drive(MachineB).
Here is the T-SQL code that I am trying to execute:

EXEC xp_cmdshell 'copy D:\Data\file.txt \\MachineB\Documents'

WheneverI tried to execute the above piece of code, I get the error message"Access is denied", but if I try to copy the file from the CommandPrompt (cmd.exe) with the copy command, the file copies fine over thenetwork.

I have already searched over the internet and I foundout that loads of people have the same issue, and they were suggestedsomething like this:
"Check in Services and make sure that the MSSQLServer service is run asa domain user and that domain user has rights to these networkresources."

Wellit sounds plausible, but I don't know what are the exact steps to dothis. How do I know which user is running the MSSQL Server service? Arethey referring to the user which I use to connect to my SQL ServerDatabase engine throuhg the SQL Server Management Studio?
Also they are suggesting 'domain user', and as I said before I do not have domain network just regular simple workgroup network.

Hereare some details of the user that I use to login. I generally logininto my Windows 2003 Server machine with user called 'User1' and I usethe same 'User1' to connect to SQL Server through the Management StudioScreen.
Should I create a user called 'User1' on my MachineB(Destination Machine)?

I would really appreciate, if someone can give me detailed steps explaning how to solve this problem.

Thank you very much once again.Check out the SQL Server Surface Area Configuration under SQL Server 2005 configuration. xp_cmdshell is disabled by default. You need to enable it manually and give permissions.|||

ndinakar:

Check out the SQL Server Surface Area Configuration under SQL Server 2005 configuration. xp_cmdshell is disabled by default. You need to enable it manually and give permissions.

Thank you very much for your reply. xp_cmdshell is already enabled.

|||

modest:

"Check in Services and make sure that the MSSQLServer service is run as a domain user and that domain user has rights to these network resources."

Well it sounds plausible, but I don't know what are the exact steps to do this.

You're on the rigth way goto Start->All Programs->Microsoft SQL Server2005-> Configuration Tools->SQL Server Configuration Manager->SQL Server 2005 Services->locate the service for the SQL2005 instance, should be "SQL Server (InstanceName)"->double click it-> on the Log On tab, you can see the start up account for the service. Just make sure this account has proper permission on the remote resource.

|||Yeah I did that and it says under buit-in account, the user is Local System. Now how do I find out which user is associated with Local System.

Thanks for the reply.|||

modest:

Yeah I did that and it says under buit-in account, the user is Local System. Now how do I find out which user is associated with Local System.

Thanks for the reply.

xp_cmdshell runs with SQL Server Agent permissions Microsoft have finally documented the permission requirements. Try the link below and follow the instructions. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms175046.aspx

|||

Caddre:

modest:

Yeah I did that and it says under buit-in account, the user is Local System. Now how do I find out which user is associated with Local System.

Thanks for the reply.

xp_cmdshell runs with SQL Server Agent permissions Microsoft have finally documented the permission requirements. Try the link below and follow the instructions. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms175046.aspx

Well I already have the proxy credential created for the User1 Windows account as I use that credential to execute the dtsRun command from the xp_cmdshell and that runs fine. The problem is when I am trying to copy something over the network. For example I can copy a file within my local machine by using the xp_cmdshell stored procedure, but it won;t work if the destination drive is a network drive.|||That is because you installed SQL Server with the local systems account when you do that and most developers do, SQL Server service and SQL Server Agent are without access to network resources. Try running replication service and you will see what I am talking about. Run a search for local systems account limitations in SQL Server BOL (books online). Hope this helps.|||

Caddre:

That is because you installed SQL Server with the local systems account when you do that and most developers do, SQL Server service and SQL Server Agent are without access to network resources. Try running replication service and you will see what I am talking about. Run a search for local systems account limitations in SQL Server BOL (books online). Hope this helps.

Thanks. Actually I was worried, if changing the startup user for SQLSERVER 2005 will affect my ASP/ASP.NET scripts or for that matter IIS6.0. Will this change be transparent to all the users on the machineand the other machine on my network which connect to the SQL SERVER onmy machine?|||It is not complicated just run full backup of all your databases and rerun setup and make sure you install SQL Server service and SQL Server Agent with two Domain accounts with network permissions. And no it will not affect your application if you do the above because if you run into permissions problem your backup will help if recreate the databases before the changes. But I don't think it will affect anything because you are just giving SQL Server service and the Agent network access. Hope this helps.|||

Caddre:

It is not complicated just run full backup of all your databases and rerun setup and make sure you install SQL Server service and SQL Server Agent with two Domain accounts with network permissions. And no it will not affect your application if you do the above because if you run into permissions problem your backup will help if recreate the databases before the changes. But I don't think it will affect anything because you are just giving SQL Server service and the Agent network access. Hope this helps.

Thanks Caddre very much. I just have one question based on your suggestion. What do you mean by domain accout? Do you mean like actual Domain Network on which my computer is connected? As I said in my first post, we have a workgroup network and not domain network. I know I am confusing with the word Domain. Does domain account mean, a new Windows user that I create on my machine? Or does it mean something lese?

Thank you once again.|||OK my bad create service accounts in your workgroup to run SQL Server service and SQL Server Agent and no you cannot run both on one account because the Agent can do a lot of automation in your application for you so it is good practice to run them on separate accounts. Hope this helps.

Cannot execute through ISQL

Hi,
I cannot execute ISQL query from dos prompt to one
specific MS SQL Server 2000 production machine, but works
fine with other server any reason
Please let me know ASAP
Thanks & Regards,
Mustaq
What 's the problem? Any error messages?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Mustaq" <mustaqhussain@.hotmail.com> wrote in message
news:2700c01c46271$e512d660$a301280a@.phx.gbl...
Hi,
I cannot execute ISQL query from dos prompt to one
specific MS SQL Server 2000 production machine, but works
fine with other server any reason
Please let me know ASAP
Thanks & Regards,
Mustaq
|||Hi Vyas,
It doesn't prompts me any error message. It just comes out.
Regards,
Mustaq

>--Original Message--
>What 's the problem? Any error messages?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Mustaq" <mustaqhussain@.hotmail.com> wrote in message
>news:2700c01c46271$e512d660$a301280a@.phx.gbl...
>Hi,
>I cannot execute ISQL query from dos prompt to one
>specific MS SQL Server 2000 production machine, but works
>fine with other server any reason
>Please let me know ASAP
>Thanks & Regards,
>Mustaq
>
>.
>
|||Can you show us the query and the way you are trying to run the query? Are
you running it interactively or running it from a batch, by specifying a
query or input file?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Mustaq" <mustaqhussain@.hotmail.com> wrote in message
news:2667101c4627f$d424b2c0$a401280a@.phx.gbl...
Hi Vyas,
It doesn't prompts me any error message. It just comes out.
Regards,
Mustaq

>--Original Message--
>What 's the problem? Any error messages?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Mustaq" <mustaqhussain@.hotmail.com> wrote in message
>news:2700c01c46271$e512d660$a301280a@.phx.gbl...
>Hi,
>I cannot execute ISQL query from dos prompt to one
>specific MS SQL Server 2000 production machine, but works
>fine with other server any reason
>Please let me know ASAP
>Thanks & Regards,
>Mustaq
>
>.
>
|||Vyas,
Here is the command and query..
C:\Documents and Settings\husmu02>isql -S AH_Stage -d
AH_Baseline -U sa -q "select @.@.version"
Just to let you know the back ground
Client machine is only installed with MS SQL 7.0 Client
network utility with Mdac 2.7 and configured, Server is
SQL Server 2000 SP3, which is in WAN.
I am able to do ODBC connection and run the query but not
through isql, understand isql doesnot make use of ODBC.
Initially I was thinking to be network problem, but may
not be true, I installed MS SQL Query Analyser from 7.0
release, I can execute the query from Query Analyser
Same command and query if I specify to another MS SQL
server 2000 it works fine.
Any reason on this issue.
And can let me know how does isql work, which ports, dlls
or drivers.
Regards,
Mustaq
Regards,
Mustaq

>--Original Message--
>Can you show us the query and the way you are trying to
run the query? Are
>you running it interactively or running it from a batch,
by specifying a
>query or input file?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Mustaq" <mustaqhussain@.hotmail.com> wrote in message
>news:2667101c4627f$d424b2c0$a401280a@.phx.gbl...
>Hi Vyas,
>It doesn't prompts me any error message. It just comes
out.
>Regards,
>Mustaq
>
>
>.
>
|||Without any error messages, it is difficult to predict what's going on, but
can you try OSQL instead, and let me know how it goes?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Mustaq" <mustaqhussain@.hotmail.com> wrote in message
news:26d5001c462fa$7c5d1e80$a501280a@.phx.gbl...
Vyas,
Here is the command and query..
C:\Documents and Settings\husmu02>isql -S AH_Stage -d
AH_Baseline -U sa -q "select @.@.version"
Just to let you know the back ground
Client machine is only installed with MS SQL 7.0 Client
network utility with Mdac 2.7 and configured, Server is
SQL Server 2000 SP3, which is in WAN.
I am able to do ODBC connection and run the query but not
through isql, understand isql doesnot make use of ODBC.
Initially I was thinking to be network problem, but may
not be true, I installed MS SQL Query Analyser from 7.0
release, I can execute the query from Query Analyser
Same command and query if I specify to another MS SQL
server 2000 it works fine.
Any reason on this issue.
And can let me know how does isql work, which ports, dlls
or drivers.
Regards,
Mustaq
Regards,
Mustaq

>--Original Message--
>Can you show us the query and the way you are trying to
run the query? Are
>you running it interactively or running it from a batch,
by specifying a
>query or input file?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Mustaq" <mustaqhussain@.hotmail.com> wrote in message
>news:2667101c4627f$d424b2c0$a401280a@.phx.gbl...
>Hi Vyas,
>It doesn't prompts me any error message. It just comes
out.
>Regards,
>Mustaq
>
>
>.
>
|||Vyas,
Thanks, got resolved by setting TCP/IP and configuring it.
Regards,
Mustaq

>--Original Message--
>Without any error messages, it is difficult to predict
what's going on, but[vbcol=seagreen]
>can you try OSQL instead, and let me know how it goes?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Mustaq" <mustaqhussain@.hotmail.com> wrote in message
>news:26d5001c462fa$7c5d1e80$a501280a@.phx.gbl...
>Vyas,
>Here is the command and query..
>C:\Documents and Settings\husmu02>isql -S AH_Stage -d
>AH_Baseline -U sa -q "select @.@.version"
>Just to let you know the back ground
>Client machine is only installed with MS SQL 7.0 Client
>network utility with Mdac 2.7 and configured, Server is
>SQL Server 2000 SP3, which is in WAN.
>I am able to do ODBC connection and run the query but not
>through isql, understand isql doesnot make use of ODBC.
>Initially I was thinking to be network problem, but may
>not be true, I installed MS SQL Query Analyser from 7.0
>release, I can execute the query from Query Analyser
>Same command and query if I specify to another MS SQL
>server 2000 it works fine.
>Any reason on this issue.
>And can let me know how does isql work, which ports, dlls
>or drivers.
>Regards,
>Mustaq
>Regards,
>Mustaq
>run the query? Are
>by specifying a
>out.
works
>
>.
>

Cannot execute sub-report in list or table

I have a report that needs to invoke a sub-report for each row in a dataset.
I can execute the sub-report manually without problems. I can place the
sub-report on the main report and set the parameter to the first ID in my
recordset, and the report executes. If I place the sub-report in a list or
table, I get the following error:
An error has occurred while processing a sub-report. Details: Object
reference not set to an instance of an object
For both the table and list, I just added the container control to the main
report and just set the data source. I then add the subreport to the
container and hook up the parameters. I don't modify any other attributes
on the container. To experiment, I added a list to my main report and then
dragged the single-instance sub-report control that was working into the
container but I recieved the same error (I then drag the sub-report to
outside of the container and it renders just fine). I tried hard-coding the
parameters, but I continue to receive the error. I recieve this error in
both VS 2005 and SRS 2005 SP1.
Since the sub-report will execute outside the container, I believe I am
doing something incorrectly with having the sub-report in a container.
1) Does SRS support invoking a sub-report in a container, once per row?
2) If so, what is the recommend container? List, Table, or Matrix?
3) I found the log files to be of little help. I'm including the stack
trace for this error below. Any suggestions on how to troubleshoot this
issue?
Any help would be appreciated
- Chris
==============[ BEGIN STACK TRACE]=============================== w3wp!library!6!09/22/2006-15:50:27:: i INFO: Call to CreateReport( 'Trip',
'/BBSReporting', def)
w3wp!library!6!09/22/2006-15:50:27:: i INFO: Call to SetReportDefinition(
'/BBSReporting/Trip', def )
w3wp!library!7!09/22/2006-15:51:35:: i INFO: Call to GetPermissions:/
w3wp!library!7!09/22/2006-15:51:35:: i INFO: Call to GetSystemPermissions
w3wp!library!e!09/22/2006-15:51:40:: i INFO: Call to
GetPermissions:/BBSReporting
w3wp!library!e!09/22/2006-15:51:40:: i INFO: Call to GetSystemPermissions
w3wp!library!1!09/22/2006-15:51:43:: i INFO: Call to
GetPermissions:/BBSReporting/Trip
w3wp!library!1!09/22/2006-15:51:43:: i INFO: Call to GetSystemPermissions
w3wp!library!7!09/22/2006-15:51:50:: i INFO: Call to
GetPermissions:/BBSReporting/Trip
w3wp!library!1!09/22/2006-15:51:50:: i INFO: Call to GetSystemPermissions
w3wp!library!7!09/22/2006-15:51:52:: i INFO: Call to RenderFirst(
'/BBSReporting/Trip' )
w3wp!processing!7!9/22/2006-15:51:56:: e ERROR: An error has occurred while
processing a sub-report. Details: Object reference not set to an instance of
an object. Stack trace:
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ProcessingContext.GetScopeValues(GroupingList
containingScopes, IScope containingScope)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeRICollection.CreateSubReportInstance(SubReport
subReport, ProcessingContext processingContext, Int32 index, IScope
containingScope, String& label)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeRICollection.CreateInstance(ReportItem
reportItem, Boolean setupEnvironment, Int32 index)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeRICollection.CreateInstances(ReportItemColInstance
collectionInstance, ReportItemCollection reportItemsDef)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.Merge.CreateInstances(ParameterInfoCollection
parameters, Boolean noRows)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.Merge.Process(ParameterInfoCollection
parameters, Boolean mergeTran)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeRICollection.CreateSubReportInstance(SubReport
subReport, ProcessingContext processingContext, Int32 index, IScope
containingScope, String& label)
w3wp!webserver!7!09/22/2006-15:51:57:: i INFO: Processed report.
Report='/BBSReporting/Trip', Stream=''Hello Chris,
Based on my research, the sub report could be in the data region such as
table, matrix and list.
The error message showes that the seb report could not render correctly.
Since you could render the sub report correctly out of the data region, it
may be related with your parameter.
Would you please let me know the parameter you use in the sub report?
Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Wei,
The sub-report takes a single integer parameter named CompanyID. I have a
recordset that returns a CompanyID. I bound the recordset to the list
control. I have the Parameter Name set to CompanyID and the Paramter Value
set to =Fields!CompanyID.Value. I selected both using the Sub-Report
Properties dialog box, the Parameters tab, using the dropdown arrows.
To continue to troubleshoot the issue, I've done the following:
=========================* Added a textbox to the list that displays the CompanyID value
(=Fields!CompanyID.Value). It displays the expected value.
* I attempted to hard-code the parameter value using the following
syntax:
="100"
=100
100
=CInt(100)
=CInt("100")
* I'm using the following SQL to simplify the main report: SELECT 100 as
CompanyID
* I created a new Main Report that had no controls other than the
sub-report. I used the query above, binding the sub-report parameter to
=First(Fields!CompanyID.Value, "DataSet1"). The sub-report renders. If I
add a list control, bind it to the dataset, drag the sub-report into it, it
will not render. If I drag it out of the list control, it will render. I
didn't change a thing on the sub-report control.
Is there anyway to find out what *exactly* is NULL?
I appreciate your assistance with this.
- Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:dtkYvMG4GHA.4280@.TK2MSFTNGXA01.phx.gbl...
> Hello Chris,
> Based on my research, the sub report could be in the data region such as
> table, matrix and list.
> The error message showes that the seb report could not render correctly.
> Since you could render the sub report correctly out of the data region, it
> may be related with your parameter.
> Would you please let me know the parameter you use in the sub report?
> Thank you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Chris,
Based on my test, I put the sub-report into the List control successfully.
I would like to send the sample to you. To get the sample, could you send
me a email to me? Please remove the ONLINE in my email address. I will keep
the information secure.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
I sent my email address yesterday morning.
This afternoon I was able to recreate the problem with simple reports. If
necessary, I can send this to you.
Create three (3) reports, each with a single textbox with their name. I
named them "Report A", "Report B", and "Report C".
I add "C" to "B" as a sub-report. B renders fine - I see B and C.
I add "B" to "A" as a sub-report. A renders fine - I see A, B, and C.
I then added a dataset to A.
Declare @.Report table ( ID int )
INSERT INTO @.Report values (1);
INSERT INTO @.Report values (2);
SELECT * FROM @.Report
I added a List control and bound it to the dataset.
I then added a second instance of report B to the list.
At this point I get: Warning 1 [rsErrorExecutingSubreport] An error occurred
while executing the subreport 'subreport1': Object reference not set to an
instance of an object. [Path]\ReportA.rdl 0 0
I'm not accessing the database.
I'm not passing parameters.
If I add Report C to the list, it renders correctly, but B still does not.
This leads me to believe that there may be an issue with multiple levels of
nested reports within a list. This should be very easy to recreate. I can
zip my samples and send them to you if need be.
Visual Studio 2005
SQL Server Reporting Services 2005 SP1
Thanks,
Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:tFEApXf4GHA.4344@.TK2MSFTNGXA01.phx.gbl...
> Hello Chris,
> Based on my test, I put the sub-report into the List control successfully.
> I would like to send the sample to you. To get the sample, could you send
> me a email to me? Please remove the ONLINE in my email address. I will
> keep
> the information secure.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Chris,
Thanks for the example of your issue.
I have reproduced it on my side.
I checked our internal database and found a workaround:
In "ReportB", you could add a List control and bound to a dataset which
have only one record. Then put the subreport for "ReportC" in the list
control.
Then in the "ReportA", you could add the subreport and put it in to the
list control without any error.
Please try this workaround on your side and let me know the result. Thank
you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
I was able to resolve this as you suggested, but I ended up having to modify
other reports as once I added the report in question to the "dummy" list
control, any subsequent sub-reports resulted in the same error.
Any ideas when this will be fixed? A hot fix, SP2, etc?
Thanks for your help with this.
- Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:jceAud54GHA.2336@.TK2MSFTNGXA01.phx.gbl...
> Hello Chris,
> Thanks for the example of your issue.
> I have reproduced it on my side.
> I checked our internal database and found a workaround:
> In "ReportB", you could add a List control and bound to a dataset which
> have only one record. Then put the subreport for "ReportC" in the list
> control.
> Then in the "ReportA", you could add the subreport and put it in to the
> list control without any error.
> Please try this workaround on your side and let me know the result. Thank
> you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Chris,
Thanks for the update.
The workaround need to be done on all the sub-reports.
After contact with the product team, this issue will be fixed in SQL 2005
SP2.
If you have any questions, or concerns, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Chris,
If you have any question or concerns, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.sql

cannot execute sp/access sp properties using gui

I am not able to right click to execute a stored procedure or right click to access the stored procedure properties using Management Studio. Any ideas on what is causing this or if it is supposed to be this way?

Any help is much appreciated.

hi,

does the user you are in the database have enought permissions to do that?

regards

|||

Thanks for the response Andrea,

I login as sa.

Suppose I could mention this is on my local machine. Executing @.@.version returns:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I am the first one to be running on 2005 locally. Everyone else is still at 2000 so I have not been able to compare against other machines running express.

cannot execute sp/access sp properties using gui

I am not able to right click to execute a stored procedure or right click to access the stored procedure properties using Management Studio. Any ideas on what is causing this or if it is supposed to be this way?

Any help is much appreciated.

hi,

does the user you are in the database have enought permissions to do that?

regards

|||

Thanks for the response Andrea,

I login as sa.

Suppose I could mention this is on my local machine. Executing @.@.version returns:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I am the first one to be running on 2005 locally. Everyone else is still at 2000 so I have not been able to compare against other machines running express.

Cannot execute extended stored procedure?

I've written an extended stored procedure using VS 2005/C++. The sp can be
added and executed on my local SQL Server 2000. However, after I copied to
production server. I can sp_addextendedproc it but executing shows error:
Msg 0, Level 16, State 0, Procedure xp_calc, Line 1
Cannot load the DLL C:\Program Files\Microsoft SQL
Server\80\Tools\Binn\XPDLLs\xp_calc.dll, or one of the DLLs it references.
Reason: 126(The specified module could not be found.)
I remember I need to copy some C++ runtime(?) dll to the server when I was
using VS 2003. Which files are needed for VS2005?
Thanks,nick (nick@.discussions.microsoft.com) writes:
> I've written an extended stored procedure using VS 2005/C++. The sp can be
> added and executed on my local SQL Server 2000. However, after I copied to
> production server. I can sp_addextendedproc it but executing shows error:
> Msg 0, Level 16, State 0, Procedure xp_calc, Line 1
> Cannot load the DLL C:\Program Files\Microsoft SQL
> Server\80\Tools\Binn\XPDLLs\xp_calc.dll, or one of the DLLs it references.
> Reason: 126(The specified module could not be found.)
> I remember I need to copy some C++ runtime(?) dll to the server when I was
> using VS 2003. Which files are needed for VS2005?
That would be MSVCR80.DLL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You can find the dependent DLLs using "Dependency Walker"
which can be downloaded from the Microsoft website
or from here
http://www.dependencywalker.com/

Cannot edit RS reports...!

I'm new at using RS, so please bear with me...
I need to make small changes to existing reports. I'm able to execute
the reports and view the report list through the Report Manager, but I
don't ge the "Edit" options for the reports.
What security options must I have to be able to edit the reports?
Also, are the RDLs stored in the Catalog table? I ask because I can't
find an RDL on the report server to save my life...
Thanks!The edit button allows saving the RDL out to a file. Then you use the
designer to modify it and then deploy it back to the server. You should
install the designer on your local machine.
Are you getting prompted for a place to save the file when you click on the
edit button. If not then it is a security issue and you need to be added to
the appropriate role in Reporting Services.
RS stores the RDL in the database but you should not go directly against the
database. I haven't bothered to look but I doubt if it is in a readable
format anyway.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kerc" <jkercado@.gmail.com> wrote in message
news:1128533548.040646.285220@.g44g2000cwa.googlegroups.com...
> I'm new at using RS, so please bear with me...
> I need to make small changes to existing reports. I'm able to execute
> the reports and view the report list through the Report Manager, but I
> don't ge the "Edit" options for the reports.
> What security options must I have to be able to edit the reports?
> Also, are the RDLs stored in the Catalog table? I ask because I can't
> find an RDL on the report server to save my life...
> Thanks!
>|||I'm not getting the "Edit" button...I just get the "General" tab with
the information...That's why I asked about security settings, something
is preventing me from getting the report source.|||When you click on properties you say you see the General tab. There should
be a section on that page that says Report Definition and has a link that
says Edit.
You have to click on the report (i.e. one by one) and then do properties for
that repor. You cannot do a whole directory.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kerc" <jkercado@.gmail.com> wrote in message
news:1128538895.020947.111580@.f14g2000cwb.googlegroups.com...
> I'm not getting the "Edit" button...I just get the "General" tab with
> the information...That's why I asked about security settings, something
> is preventing me from getting the report source.
>

Cannot create a row of size %d which is greater than the allowable maximum of %d

I'm trying to execute an insert with a PreparedStatement object and I am getting the following Error message:

java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a row of size 8320 which is greater than the allowable maximum of 8060.

Does anyone have a solution for this?You're inserting data that is larger than the maximum page size for SQL Server. See this page (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_20hd.asp).|||Originally posted by Mulligan
You're inserting data that is larger than the maximum page size for SQL Server. See this page (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_20hd.asp).

Thanks for the link. I had already taken a look there before finding this forum but I couldn't understand why my data was longer than the page size for SQL because I was only inserting a small amount of data.

I found that my problem due to MS SQL creating a temporary table based on my Insert Statement using the table definition for the size of the columns. In this case, I had a nvarchar(4000) column. This then generated a temp table with a column size equivalent to 8363 bytes but the page size is 8060. This happens even though the data I was inserting was much smaller than the max length.

To resolve the problem I had to reduce to the column size to a maximum of 3868. This resolved the problem.|||Nice bit of detective work. And thanks for posting back with the solution - it makes a refreshing change from all the "How can I fix problem X?" "Like this." "Bye" postings :)

Mull.

Cannot connect to Database

HI,
I have a simple report which i am trying to execute. The ODBC datasource
returns success when the tested.
I get the following error when i try to run the report :
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'DataSource1'.
(rsErrorOpeningConnection) Get Online Help
ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does
not exist or access denied. ERROR [01000] [Microsoft][ODBC SQL Server
Driver][DBNETLIB]ConnectionOpen (Connect()).
----
How do i go about debugging it.
regards
-sarabsarab wrote:
> An error has occurred during report processing. (rsProcessingAborted)
> Get Online Help
> Cannot create a connection to data source 'DataSource1'.
> (rsErrorOpeningConnection) Get Online Help
> ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server
> does
> not exist or access denied. ERROR [01000] [Microsoft][ODBC SQL Server
> Driver][DBNETLIB]ConnectionOpen (Connect()).
On the machine with sql-server ->klick start -> run -> type
svrnetcn.exe ->enter
a window with sql-server-network-configuration appears->watch into right
window
there should be ("normally") two activated protocolls (named pipes and
tcp/ip)
If not activate the missing protocoll
Hope that helps
regards
Frank