The following function works fine for 2 years but recently it crashes severa
l
times
and the error message is "Object reference not set to an instance of an
object."
The SQL query works fine in SQL server 2000. OS is Windows server 2003, and
this
function is used in ASP.Net project.
Can somebody figure it out? Your help is highly appreciated!
Public Function get_cart_number() As String
Dim cmd As New Command()
Dim conn As New Connection()
Dim strmIn As New Stream()
Dim strmOut As New Stream()
Dim SQLxml As String
Dim xml As New XmlDocument()
Dim strTemp As String
' Open a connection to the SQL Server.
conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
database=someDB;")
cmd.ActiveConnection = conn
'Build the command string in the form of an XML template
SQLxml = "<root
xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
SQLxml = SQLxml & "select distinct cart_number from Cart for xml auto"
SQLxml = SQLxml & "</sql:query></root>"
' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
' Open the command stream and write our template to it.
strmIn.Open()
strmIn.WriteText(SQLxml)
strmIn.Position = 0
cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open()
strmOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute(, , adExecuteStream)
strmOut.Position = 0
xml.LoadXml(strmOut.ReadText)
Dim cart As XmlNode
For Each cart In xml.SelectSingleNode("root").ChildNodes
strTemp = strTemp & "<cart>" & cart.Attributes(0).Value.ToString
& "</cart>"
Next
strmIn.Close()
strmOut.Close()
Return (strTemp)
End FunctionI don't know what line that this is failing on, but if I had to guess, I
think it would be in this area:
> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString & "</cart>"
> Next
You probably have some NULL values and therefore aren't bringing back the
attribute value that you are trying to retrive here:
"cart.Attributes(0).Value". I would check the results of the query first.
Jay Nathan
http://www.jaynathan.com/blog
"Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
news:35A167A6-1ECB-485D-A916-2B1A7690DA4C@.microsoft.com...
> The following function works fine for 2 years but recently it crashes
several
> times
> and the error message is "Object reference not set to an instance of an
> object."
> The SQL query works fine in SQL server 2000. OS is Windows server 2003,
and
> this
> function is used in ASP.Net project.
> Can somebody figure it out? Your help is highly appreciated!
> Public Function get_cart_number() As String
> Dim cmd As New Command()
> Dim conn As New Connection()
> Dim strmIn As New Stream()
> Dim strmOut As New Stream()
> Dim SQLxml As String
> Dim xml As New XmlDocument()
> Dim strTemp As String
> ' Open a connection to the SQL Server.
> conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
> database=someDB;")
> cmd.ActiveConnection = conn
> 'Build the command string in the form of an XML template
> SQLxml = "<root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
> SQLxml = SQLxml & "select distinct cart_number from Cart for xml
auto"
> SQLxml = SQLxml & "</sql:query></root>"
> ' Set the command dialect to XML.
> cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
> ' Open the command stream and write our template to it.
> strmIn.Open()
> strmIn.WriteText(SQLxml)
> strmIn.Position = 0
> cmd.CommandStream = strmIn
> ' Execute the command, open the return stream, and read the
result.
> strmOut.Open()
> strmOut.LineSeparator = adCRLF
> cmd.Properties("Output Stream").Value = strmOut
> cmd.Execute(, , adExecuteStream)
> strmOut.Position = 0
> xml.LoadXml(strmOut.ReadText)
> Dim cart As XmlNode
> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString
> & "</cart>"
> Next
> strmIn.Close()
> strmOut.Close()
> Return (strTemp)
> End Function
>
>sql
Showing posts with label message. Show all posts
Showing posts with label message. Show all posts
Wednesday, March 28, 2012
Question about Querying by "for xml auto" and retriving xml by Str
The following function works fine for 2 years but recently it crashes several
times
and the error message is "Object reference not set to an instance of an
object."
The SQL query works fine in SQL server 2000. OS is Windows server 2003, and
this
function is used in ASP.Net project.
Can somebody figure it out? Your help is highly appreciated!
Public Function get_cart_number() As String
Dim cmd As New Command()
Dim conn As New Connection()
Dim strmIn As New Stream()
Dim strmOut As New Stream()
Dim SQLxml As String
Dim xml As New XmlDocument()
Dim strTemp As String
' Open a connection to the SQL Server.
conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
database=someDB;")
cmd.ActiveConnection = conn
'Build the command string in the form of an XML template
SQLxml = "<root
xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
SQLxml = SQLxml & "select distinct cart_number from Cart for xml auto"
SQLxml = SQLxml & "</sql:query></root>"
' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
' Open the command stream and write our template to it.
strmIn.Open()
strmIn.WriteText(SQLxml)
strmIn.Position = 0
cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open()
strmOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute(, , adExecuteStream)
strmOut.Position = 0
xml.LoadXml(strmOut.ReadText)
Dim cart As XmlNode
For Each cart In xml.SelectSingleNode("root").ChildNodes
strTemp = strTemp & "<cart>" & cart.Attributes(0).Value.ToString
& "</cart>"
Next
strmIn.Close()
strmOut.Close()
Return (strTemp)
End Function
I don't know what line that this is failing on, but if I had to guess, I
think it would be in this area:
> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString & "</cart>"
> Next
You probably have some NULL values and therefore aren't bringing back the
attribute value that you are trying to retrive here:
"cart.Attributes(0).Value". I would check the results of the query first.
Jay Nathan
http://www.jaynathan.com/blog
"Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
news:35A167A6-1ECB-485D-A916-2B1A7690DA4C@.microsoft.com...
> The following function works fine for 2 years but recently it crashes
several
> times
> and the error message is "Object reference not set to an instance of an
> object."
> The SQL query works fine in SQL server 2000. OS is Windows server 2003,
and
> this
> function is used in ASP.Net project.
> Can somebody figure it out? Your help is highly appreciated!
> Public Function get_cart_number() As String
> Dim cmd As New Command()
> Dim conn As New Connection()
> Dim strmIn As New Stream()
> Dim strmOut As New Stream()
> Dim SQLxml As String
> Dim xml As New XmlDocument()
> Dim strTemp As String
> ' Open a connection to the SQL Server.
> conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
> database=someDB;")
> cmd.ActiveConnection = conn
> 'Build the command string in the form of an XML template
> SQLxml = "<root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
> SQLxml = SQLxml & "select distinct cart_number from Cart for xml
auto"
> SQLxml = SQLxml & "</sql:query></root>"
> ' Set the command dialect to XML.
> cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
> ' Open the command stream and write our template to it.
> strmIn.Open()
> strmIn.WriteText(SQLxml)
> strmIn.Position = 0
> cmd.CommandStream = strmIn
> ' Execute the command, open the return stream, and read the
result.
> strmOut.Open()
> strmOut.LineSeparator = adCRLF
> cmd.Properties("Output Stream").Value = strmOut
> cmd.Execute(, , adExecuteStream)
> strmOut.Position = 0
> xml.LoadXml(strmOut.ReadText)
> Dim cart As XmlNode
> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString
> & "</cart>"
> Next
> strmIn.Close()
> strmOut.Close()
> Return (strTemp)
> End Function
>
>
times
and the error message is "Object reference not set to an instance of an
object."
The SQL query works fine in SQL server 2000. OS is Windows server 2003, and
this
function is used in ASP.Net project.
Can somebody figure it out? Your help is highly appreciated!
Public Function get_cart_number() As String
Dim cmd As New Command()
Dim conn As New Connection()
Dim strmIn As New Stream()
Dim strmOut As New Stream()
Dim SQLxml As String
Dim xml As New XmlDocument()
Dim strTemp As String
' Open a connection to the SQL Server.
conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
database=someDB;")
cmd.ActiveConnection = conn
'Build the command string in the form of an XML template
SQLxml = "<root
xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
SQLxml = SQLxml & "select distinct cart_number from Cart for xml auto"
SQLxml = SQLxml & "</sql:query></root>"
' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
' Open the command stream and write our template to it.
strmIn.Open()
strmIn.WriteText(SQLxml)
strmIn.Position = 0
cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open()
strmOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute(, , adExecuteStream)
strmOut.Position = 0
xml.LoadXml(strmOut.ReadText)
Dim cart As XmlNode
For Each cart In xml.SelectSingleNode("root").ChildNodes
strTemp = strTemp & "<cart>" & cart.Attributes(0).Value.ToString
& "</cart>"
Next
strmIn.Close()
strmOut.Close()
Return (strTemp)
End Function
I don't know what line that this is failing on, but if I had to guess, I
think it would be in this area:
> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString & "</cart>"
> Next
You probably have some NULL values and therefore aren't bringing back the
attribute value that you are trying to retrive here:
"cart.Attributes(0).Value". I would check the results of the query first.
Jay Nathan
http://www.jaynathan.com/blog
"Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
news:35A167A6-1ECB-485D-A916-2B1A7690DA4C@.microsoft.com...
> The following function works fine for 2 years but recently it crashes
several
> times
> and the error message is "Object reference not set to an instance of an
> object."
> The SQL query works fine in SQL server 2000. OS is Windows server 2003,
and
> this
> function is used in ASP.Net project.
> Can somebody figure it out? Your help is highly appreciated!
> Public Function get_cart_number() As String
> Dim cmd As New Command()
> Dim conn As New Connection()
> Dim strmIn As New Stream()
> Dim strmOut As New Stream()
> Dim SQLxml As String
> Dim xml As New XmlDocument()
> Dim strTemp As String
> ' Open a connection to the SQL Server.
> conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
> database=someDB;")
> cmd.ActiveConnection = conn
> 'Build the command string in the form of an XML template
> SQLxml = "<root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
> SQLxml = SQLxml & "select distinct cart_number from Cart for xml
auto"
> SQLxml = SQLxml & "</sql:query></root>"
> ' Set the command dialect to XML.
> cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
> ' Open the command stream and write our template to it.
> strmIn.Open()
> strmIn.WriteText(SQLxml)
> strmIn.Position = 0
> cmd.CommandStream = strmIn
> ' Execute the command, open the return stream, and read the
result.
> strmOut.Open()
> strmOut.LineSeparator = adCRLF
> cmd.Properties("Output Stream").Value = strmOut
> cmd.Execute(, , adExecuteStream)
> strmOut.Position = 0
> xml.LoadXml(strmOut.ReadText)
> Dim cart As XmlNode
> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString
> & "</cart>"
> Next
> strmIn.Close()
> strmOut.Close()
> Return (strTemp)
> End Function
>
>
Saturday, February 25, 2012
Question about "Distribution clean up: distribution" Job
Can the manner in which you remove a publication effect the ability of this
job to run?
I had been experiencing errors with this job. The error message was as
follows:
"Executed as user: <SQLServerAgentDomainAccount>. Could not remove directory
'P:\ReplData\unc\YKCLNSE_CLGWAREHOUSEPROD_STAGINGD AILYADT\20061221142453\'.
Check the security context of xp_cmdshell and close other processes that may
be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed."
I run my SQL Server Agent process with an account that does not have
sysadmin rights. After reading a few other posts with this same error, I
experimented with running the job as a user with sysadmin rights.
Unfortunately, I still had the same error.
Then I went into the job step and copied the command it was attempting to
run. This is the command:
"EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
@.max_distretention = 72"
I pasted this command into a query window and attempted to run it under my
own login, which is a member of the sysadmin role and is also a member of the
Local Administrators group. It still could not run.
The folders the command was trying to delete were used for Publications that
no longer existed. So I went ahead and deleted the folders manually. Now
the command ran successfully.
I am thinking something went wrong during the process of removing the
publications and subscriptions. Anyone have any idea what might have
happened?
Is this SQL 2005? If so you need to enable xp_cmdshell.
If it is SQL 2000 you need to ensure that no one else is accessing this
directory.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
news:6E882295-B0F5-4FA8-A3ED-731CFB672615@.microsoft.com...
> Can the manner in which you remove a publication effect the ability of
> this
> job to run?
> I had been experiencing errors with this job. The error message was as
> follows:
> "Executed as user: <SQLServerAgentDomainAccount>. Could not remove
> directory
> 'P:\ReplData\unc\YKCLNSE_CLGWAREHOUSEPROD_STAGINGD AILYADT\20061221142453\'.
> Check the security context of xp_cmdshell and close other processes that
> may
> be accessing the directory. [SQLSTATE 42000] (Error 20015). The step
> failed."
> I run my SQL Server Agent process with an account that does not have
> sysadmin rights. After reading a few other posts with this same error, I
> experimented with running the job as a user with sysadmin rights.
> Unfortunately, I still had the same error.
> Then I went into the job step and copied the command it was attempting to
> run. This is the command:
> "EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
> @.max_distretention = 72"
> I pasted this command into a query window and attempted to run it under my
> own login, which is a member of the sysadmin role and is also a member of
> the
> Local Administrators group. It still could not run.
> The folders the command was trying to delete were used for Publications
> that
> no longer existed. So I went ahead and deleted the folders manually. Now
> the command ran successfully.
> I am thinking something went wrong during the process of removing the
> publications and subscriptions. Anyone have any idea what might have
> happened?
|||It is SQL Server 2005 and xp_cmdshell was already enabled.
"Hilary Cotter" wrote:
> Is this SQL 2005? If so you need to enable xp_cmdshell.
> If it is SQL 2000 you need to ensure that no one else is accessing this
> directory.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
> news:6E882295-B0F5-4FA8-A3ED-731CFB672615@.microsoft.com...
>
>
|||Hi Ken,
I am guessing that the SQLServerAgent service does not have sufficient
rights to remove folders under P:\ReplData. As such, you may want to try
right-clicking the folder in Windows Explorer and grant the SQLServerAgent
service account Full Control rights on it.
-Raymond
"Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
news:4F98FEE4-6C0D-4274-A6C4-8384EADB82A9@.microsoft.com...[vbcol=seagreen]
> It is SQL Server 2005 and xp_cmdshell was already enabled.
> "Hilary Cotter" wrote:
|||You're right. Originally, the Agent service account had not been granted
access to the Snapshot folder (P:\ReplData). However, I was still receiving
the error even after I granted the service account "Full Control" over the
share.
"Raymond Mak [MSFT]" wrote:
> Hi Ken,
> I am guessing that the SQLServerAgent service does not have sufficient
> rights to remove folders under P:\ReplData. As such, you may want to try
> right-clicking the folder in Windows Explorer and grant the SQLServerAgent
> service account Full Control rights on it.
> -Raymond
> "Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
> news:4F98FEE4-6C0D-4274-A6C4-8384EADB82A9@.microsoft.com...
>
>
|||I am guessing (again) that the SQL Server Agent service account is not what
xp_cmdshell running under in the cleanup scenario, another suspect will be
the SQL Server service account. In any case, you should be able to find out
exactly which security account is doing the delete using the filemon.exe
tool from sysinternals.com.
"Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
news:6B416C84-EBDE-469A-AA39-0E94DD2F2DE7@.microsoft.com...[vbcol=seagreen]
> You're right. Originally, the Agent service account had not been granted
> access to the Snapshot folder (P:\ReplData). However, I was still
> receiving
> the error even after I granted the service account "Full Control" over the
> share.
> "Raymond Mak [MSFT]" wrote:
|||You were right. I granted the SQL Server Service Account "Full Control" over
the Snapshot Folder and the job has been running successfully ever since.
Thanks for your help.
"Raymond Mak [MSFT]" wrote:
> I am guessing (again) that the SQL Server Agent service account is not what
> xp_cmdshell running under in the cleanup scenario, another suspect will be
> the SQL Server service account. In any case, you should be able to find out
> exactly which security account is doing the delete using the filemon.exe
> tool from sysinternals.com.
> "Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
> news:6B416C84-EBDE-469A-AA39-0E94DD2F2DE7@.microsoft.com...
>
>
job to run?
I had been experiencing errors with this job. The error message was as
follows:
"Executed as user: <SQLServerAgentDomainAccount>. Could not remove directory
'P:\ReplData\unc\YKCLNSE_CLGWAREHOUSEPROD_STAGINGD AILYADT\20061221142453\'.
Check the security context of xp_cmdshell and close other processes that may
be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed."
I run my SQL Server Agent process with an account that does not have
sysadmin rights. After reading a few other posts with this same error, I
experimented with running the job as a user with sysadmin rights.
Unfortunately, I still had the same error.
Then I went into the job step and copied the command it was attempting to
run. This is the command:
"EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
@.max_distretention = 72"
I pasted this command into a query window and attempted to run it under my
own login, which is a member of the sysadmin role and is also a member of the
Local Administrators group. It still could not run.
The folders the command was trying to delete were used for Publications that
no longer existed. So I went ahead and deleted the folders manually. Now
the command ran successfully.
I am thinking something went wrong during the process of removing the
publications and subscriptions. Anyone have any idea what might have
happened?
Is this SQL 2005? If so you need to enable xp_cmdshell.
If it is SQL 2000 you need to ensure that no one else is accessing this
directory.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
news:6E882295-B0F5-4FA8-A3ED-731CFB672615@.microsoft.com...
> Can the manner in which you remove a publication effect the ability of
> this
> job to run?
> I had been experiencing errors with this job. The error message was as
> follows:
> "Executed as user: <SQLServerAgentDomainAccount>. Could not remove
> directory
> 'P:\ReplData\unc\YKCLNSE_CLGWAREHOUSEPROD_STAGINGD AILYADT\20061221142453\'.
> Check the security context of xp_cmdshell and close other processes that
> may
> be accessing the directory. [SQLSTATE 42000] (Error 20015). The step
> failed."
> I run my SQL Server Agent process with an account that does not have
> sysadmin rights. After reading a few other posts with this same error, I
> experimented with running the job as a user with sysadmin rights.
> Unfortunately, I still had the same error.
> Then I went into the job step and copied the command it was attempting to
> run. This is the command:
> "EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
> @.max_distretention = 72"
> I pasted this command into a query window and attempted to run it under my
> own login, which is a member of the sysadmin role and is also a member of
> the
> Local Administrators group. It still could not run.
> The folders the command was trying to delete were used for Publications
> that
> no longer existed. So I went ahead and deleted the folders manually. Now
> the command ran successfully.
> I am thinking something went wrong during the process of removing the
> publications and subscriptions. Anyone have any idea what might have
> happened?
|||It is SQL Server 2005 and xp_cmdshell was already enabled.
"Hilary Cotter" wrote:
> Is this SQL 2005? If so you need to enable xp_cmdshell.
> If it is SQL 2000 you need to ensure that no one else is accessing this
> directory.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
> news:6E882295-B0F5-4FA8-A3ED-731CFB672615@.microsoft.com...
>
>
|||Hi Ken,
I am guessing that the SQLServerAgent service does not have sufficient
rights to remove folders under P:\ReplData. As such, you may want to try
right-clicking the folder in Windows Explorer and grant the SQLServerAgent
service account Full Control rights on it.
-Raymond
"Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
news:4F98FEE4-6C0D-4274-A6C4-8384EADB82A9@.microsoft.com...[vbcol=seagreen]
> It is SQL Server 2005 and xp_cmdshell was already enabled.
> "Hilary Cotter" wrote:
|||You're right. Originally, the Agent service account had not been granted
access to the Snapshot folder (P:\ReplData). However, I was still receiving
the error even after I granted the service account "Full Control" over the
share.
"Raymond Mak [MSFT]" wrote:
> Hi Ken,
> I am guessing that the SQLServerAgent service does not have sufficient
> rights to remove folders under P:\ReplData. As such, you may want to try
> right-clicking the folder in Windows Explorer and grant the SQLServerAgent
> service account Full Control rights on it.
> -Raymond
> "Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
> news:4F98FEE4-6C0D-4274-A6C4-8384EADB82A9@.microsoft.com...
>
>
|||I am guessing (again) that the SQL Server Agent service account is not what
xp_cmdshell running under in the cleanup scenario, another suspect will be
the SQL Server service account. In any case, you should be able to find out
exactly which security account is doing the delete using the filemon.exe
tool from sysinternals.com.
"Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
news:6B416C84-EBDE-469A-AA39-0E94DD2F2DE7@.microsoft.com...[vbcol=seagreen]
> You're right. Originally, the Agent service account had not been granted
> access to the Snapshot folder (P:\ReplData). However, I was still
> receiving
> the error even after I granted the service account "Full Control" over the
> share.
> "Raymond Mak [MSFT]" wrote:
|||You were right. I granted the SQL Server Service Account "Full Control" over
the Snapshot Folder and the job has been running successfully ever since.
Thanks for your help.
"Raymond Mak [MSFT]" wrote:
> I am guessing (again) that the SQL Server Agent service account is not what
> xp_cmdshell running under in the cleanup scenario, another suspect will be
> the SQL Server service account. In any case, you should be able to find out
> exactly which security account is doing the delete using the filemon.exe
> tool from sysinternals.com.
> "Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
> news:6B416C84-EBDE-469A-AA39-0E94DD2F2DE7@.microsoft.com...
>
>
Monday, February 20, 2012
Question
Does anyone know exactly what this error message means?
"Cannot create a worktable row larger than allowable maximum. Resubmit your
query with the ROBUST PLAN hint."
I get this error when trying to display SQL query results in my browser but
when I run the same query using Query Analyser, I don't get this error.
Is this ODBC related or pure SQL?
Thanks.Just search Books Online for "ROBUST PLAN" and you will find what optimizer hint you need to put in
the query. Basically, the optimizer selected a plan where temp storage resulted in a row size over
page size. The hint tell the optimizer to not do that.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Johann Castueras" <johann.castueras@.bigpond.com> wrote in message
news:%23YJw%23WXiDHA.2320@.TK2MSFTNGP12.phx.gbl...
> Does anyone know exactly what this error message means?
> "Cannot create a worktable row larger than allowable maximum. Resubmit your
> query with the ROBUST PLAN hint."
> I get this error when trying to display SQL query results in my browser but
> when I run the same query using Query Analyser, I don't get this error.
> Is this ODBC related or pure SQL?
> Thanks.
>|||What is the statement you are using ?
ROBUST PLAN -
Forces the query optimizer to attempt a plan that works for the maximum
potential row size, possibly at the expense of performance. When the query
is processed, intermediate tables and operators may need to store and
process rows that are wider than any of the input rows. The rows may be so
wide that, in some cases, the particular operator cannot process the row. If
this happens, SQL Server produces an error during query execution. By using
ROBUST PLAN, you instruct the query optimizer not to consider any query
plans that may encounter this problem.
Also
http://support.microsoft.com/default.aspx?scid=kb;en-us;280138
What version and SP level of SQL Server are you running?
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Johann Castueras" <johann.castueras@.bigpond.com> wrote in message
news:%23YJw%23WXiDHA.2320@.TK2MSFTNGP12.phx.gbl...
> Does anyone know exactly what this error message means?
> "Cannot create a worktable row larger than allowable maximum. Resubmit
your
> query with the ROBUST PLAN hint."
> I get this error when trying to display SQL query results in my browser
but
> when I run the same query using Query Analyser, I don't get this error.
> Is this ODBC related or pure SQL?
> Thanks.
>
"Cannot create a worktable row larger than allowable maximum. Resubmit your
query with the ROBUST PLAN hint."
I get this error when trying to display SQL query results in my browser but
when I run the same query using Query Analyser, I don't get this error.
Is this ODBC related or pure SQL?
Thanks.Just search Books Online for "ROBUST PLAN" and you will find what optimizer hint you need to put in
the query. Basically, the optimizer selected a plan where temp storage resulted in a row size over
page size. The hint tell the optimizer to not do that.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Johann Castueras" <johann.castueras@.bigpond.com> wrote in message
news:%23YJw%23WXiDHA.2320@.TK2MSFTNGP12.phx.gbl...
> Does anyone know exactly what this error message means?
> "Cannot create a worktable row larger than allowable maximum. Resubmit your
> query with the ROBUST PLAN hint."
> I get this error when trying to display SQL query results in my browser but
> when I run the same query using Query Analyser, I don't get this error.
> Is this ODBC related or pure SQL?
> Thanks.
>|||What is the statement you are using ?
ROBUST PLAN -
Forces the query optimizer to attempt a plan that works for the maximum
potential row size, possibly at the expense of performance. When the query
is processed, intermediate tables and operators may need to store and
process rows that are wider than any of the input rows. The rows may be so
wide that, in some cases, the particular operator cannot process the row. If
this happens, SQL Server produces an error during query execution. By using
ROBUST PLAN, you instruct the query optimizer not to consider any query
plans that may encounter this problem.
Also
http://support.microsoft.com/default.aspx?scid=kb;en-us;280138
What version and SP level of SQL Server are you running?
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Johann Castueras" <johann.castueras@.bigpond.com> wrote in message
news:%23YJw%23WXiDHA.2320@.TK2MSFTNGP12.phx.gbl...
> Does anyone know exactly what this error message means?
> "Cannot create a worktable row larger than allowable maximum. Resubmit
your
> query with the ROBUST PLAN hint."
> I get this error when trying to display SQL query results in my browser
but
> when I run the same query using Query Analyser, I don't get this error.
> Is this ODBC related or pure SQL?
> Thanks.
>
Subscribe to:
Posts (Atom)