Known SMTP bug in .NET 2.0?

Hi All,

A while back I posted a message referring to an inability to send e-mail from SQL 2005 Database mail; the error being "Command not supported" - which appeared to be coming back from the Exchange Server.

Looking on the SQL Sentry forums (SQL Sentry having its own alerting function) they report the same errro testing their smtp code, with the post

This is the result of a known bug in .NET 2.0. when working with a server that does not support ESMTP. Microsoft will provide a fix in their next release.

Can anyone from MS confirm that this is a bug, and tell me how to either work round it, or enable ESMTP on the Exchange server?

Regards,

Richard

Just to close out this thread, should anyone be reading it in the future:

It appears that the exchange server is set up using non-standard ports to receive on, and also McAfee security is running to block data on all but the narrowly specified ports. Our exchange people kindly ommitted to tell us this, but the upshot is that nothing can send mail out through the Exchange server. The error message is a bit confusing, but the root cause is security, not SQL or the client.

Regards,

Rich

Known SMTP bug in .NET 2.0?

Hi All,

A while back I posted a message referring to an inability to send e-mail from SQL 2005 Database mail; the error being "Command not supported" - which appeared to be coming back from the Exchange Server.

Looking on the SQL Sentry forums (SQL Sentry having its own alerting function) they report the same errro testing their smtp code, with the post

This is the result of a known bug in .NET 2.0. when working with a server that does not support ESMTP. Microsoft will provide a fix in their next release.

Can anyone from MS confirm that this is a bug, and tell me how to either work round it, or enable ESMTP on the Exchange server?

Regards,

Richard

Just to close out this thread, should anyone be reading it in the future:

It appears that the exchange server is set up using non-standard ports to receive on, and also McAfee security is running to block data on all but the narrowly specified ports. Our exchange people kindly ommitted to tell us this, but the upshot is that nothing can send mail out through the Exchange server. The error message is a bit confusing, but the root cause is security, not SQL or the client.

Regards,

Rich

sql

known error with Transfer SQL Server Objects Task...

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127100

I don't believe that the fix to the above issue has made its way into an SP yet. Can anyone confirm/refute this, and/or state when the fix will become publicly available &/or give a workaround?

Thanks,

Tamim.

This issue was fixed in SP1.

Known assembly FileIOPermission error, still no solution

I'm trying to write to a text file from my custom assembly and I keep
getting FileIOPermission. The only way I can get it to work is by
changing the PermissionSetName of the whole CodeGroup from Nothing to
FullTrust. The assembly itself is very simple and all it does is it
writes one line to a text file.
Here is what I did so far:
1. I asserted the permission in my code.
2. I put the text file and my assembly into ReportSevrer bin folder and
changed the text file's security to allow "NETWORK SECURITY" (it
is IIS6) and just in case "Everyone" to write to it.
3. I added CodeGroup just after the code group with Url="$CodeGen$/*"
to the rssrvpolicy.config file with PermissionSetName="FullTrust".
I even installed Visual Studio 2005 to get access to PermCalc tool.
All it showed me was that my dll needs FileIOPermission with
Unrestricted="true" and SecurityPermission with
Flags="Assertion" in the CodeGroup which I also tried by creating a
seperate PermissionSet.
What else can I possibly try?
My Code:
private void WriteLogFile(String msg)
{
FileIOPermission perm1 = new
FileIOPermission(FileIOPermissionAccess.Write, @."C:\Program
Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\ReportLogger.log");
perm1.Assert();
FileStream fs = new FileStream(@."C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\bin\ReportLogger.log",
FileMode.OpenOrCreate, FileAccess.ReadWrite);
StreamWriter w = new StreamWriter(fs);
w.BaseStream.Seek(0, SeekOrigin.End);
w.Write("{0} {1} ", DateTime.Now.ToLongTimeString(),
DateTime.Now.ToLongDateString());
w.Write(msg + "\r\n");
w.Flush();
w.Close();
}
My CodeGroup:
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="CGReportHelper"
Description="Allow execution of ReportHelper.dll">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="file://C:/Program Files/Microsoft SQL Server/MSSQL/Reporting
Services/ReportServer/bin/ReportHelper.dll"/>
</CodeGroup>
My Error:
w3wp!processing!2aa8!10/05/2005-16:47:17:: e ERROR: Failed to load
expression host assembly. Details: Request for the permission of type
System.Security.Permissions.FileIOPermission, mscorlib,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
failed.
System.Security.SecurityException: Request for the permission of type
System.Security.Permissions.FileIOPermission, mscorlib,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
failed.
at
System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet
grantedSet, PermissionSet deniedSet, CodeAccessPermission demand,
PermissionToken permToken)
at System.Security.CodeAccessSecurityEngine.Check(PermissionToken
permToken, CodeAccessPermission demand, StackCrawlMark& stackMark,
Int32 checkFrames, Int32 unrestrictedOverride)
at
System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission
cap, StackCrawlMark& stackMark)
at System.Security.CodeAccessPermission.Demand()
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess
access, FileShare share, Int32 bufferSize, Boolean useAsync, String
msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess
access)
at ReportHelper.ConfirmationStatement.WriteLogFile(String msg)
at ReportHelper.ConfirmationStatement..ctor(Int32 futureBatchSetId)
at CustomCodeProxy.OnInit()
at Microsoft.ReportingServices.ReportProcessing.ExprHostObjectModel.
CustomCodeProxyBase..ctor(IReportObjectModelProxyForCustomCode
reportObjectModel)
at ReportExprHostImpl..ctor(Boolean parametersOnly, Object
reportObjectModel)
The state of the failed permission was:
<IPermission class="System.Security.Permissions.FileIOPermission,
mscorlib, Version=1.0.5000.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089"
version="1"
Read="C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\bin\ReportLogger.log"
Write="C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\bin\ReportLogger.log"/>
w3wp!processing!2aa8!10/05/2005-16:47:17:: e ERROR: Throwing
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Exception of type
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
was thrown., ;
Info:
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Exception of type
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
was thrown.
w3wp!library!2aa8!10/05/2005-16:47:17:: i INFO: Initializing
EnableExecutionLogging to 'True' as specified in Server system
properties.
w3wp!webserver!2aa8!10/05/2005-16:47:17:: e ERROR: Reporting Services
error Microsoft.ReportingServices.Diagnostics.Utilities.RSException:
Failed to load expression host assembly. Details: Request for the
permission of type System.Security.Permissions.FileIOPermission,
mscorlib, Version=1.0.5000.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089 failed. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Failed to load expression host assembly. Details: Request for the
permission of type System.Security.Permissions.FileIOPermission,
mscorlib, Version=1.0.5000.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089 failed.Hi,
See this article....
http://www.c-sharpcorner.com/Code/2005/June/CustomAssemblyinRS.asp
If still you have the issue, write to me bkkrishnan [at] hotmail [dot] com
Balaji
Siwy wrote:
>I'm trying to write to a text file from my custom assembly and I keep
>getting FileIOPermission. The only way I can get it to work is by
>changing the PermissionSetName of the whole CodeGroup from Nothing to
>FullTrust. The assembly itself is very simple and all it does is it
>writes one line to a text file.
>Here is what I did so far:
>1. I asserted the permission in my code.
>2. I put the text file and my assembly into ReportSevrer bin folder and
>changed the text file's security to allow "NETWORK SECURITY" (it
>is IIS6) and just in case "Everyone" to write to it.
>3. I added CodeGroup just after the code group with Url="$CodeGen$/*"
>to the rssrvpolicy.config file with PermissionSetName="FullTrust".
>I even installed Visual Studio 2005 to get access to PermCalc tool.
>All it showed me was that my dll needs FileIOPermission with
>Unrestricted="true" and SecurityPermission with
>Flags="Assertion" in the CodeGroup which I also tried by creating a
>seperate PermissionSet.
>What else can I possibly try?
>My Code:
>private void WriteLogFile(String msg)
>{
> FileIOPermission perm1 = new
>FileIOPermission(FileIOPermissionAccess.Write, @."C:\Program
>Files\Microsoft SQL Server\MSSQL\Reporting
>Services\ReportServer\bin\ReportLogger.log");
> perm1.Assert();
> FileStream fs = new FileStream(@."C:\Program Files\Microsoft SQL
>Server\MSSQL\Reporting Services\ReportServer\bin\ReportLogger.log",
>FileMode.OpenOrCreate, FileAccess.ReadWrite);
> StreamWriter w = new StreamWriter(fs);
> w.BaseStream.Seek(0, SeekOrigin.End);
> w.Write("{0} {1} ", DateTime.Now.ToLongTimeString(),
> DateTime.Now.ToLongDateString());
> w.Write(msg + "\r\n");
> w.Flush();
> w.Close();
>}
>My CodeGroup:
><CodeGroup class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="CGReportHelper"
> Description="Allow execution of ReportHelper.dll">
> <IMembershipCondition class="UrlMembershipCondition"
> version="1"
>Url="file://C:/Program Files/Microsoft SQL Server/MSSQL/Reporting
>Services/ReportServer/bin/ReportHelper.dll"/>
></CodeGroup>
>My Error:
>w3wp!processing!2aa8!10/05/2005-16:47:17:: e ERROR: Failed to load
>expression host assembly. Details: Request for the permission of type
>System.Security.Permissions.FileIOPermission, mscorlib,
>Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
>failed.
>System.Security.SecurityException: Request for the permission of type
>System.Security.Permissions.FileIOPermission, mscorlib,
>Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
>failed.
> at
>System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet
>grantedSet, PermissionSet deniedSet, CodeAccessPermission demand,
>PermissionToken permToken)
> at System.Security.CodeAccessSecurityEngine.Check(PermissionToken
>permToken, CodeAccessPermission demand, StackCrawlMark& stackMark,
>Int32 checkFrames, Int32 unrestrictedOverride)
> at
>System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission
>cap, StackCrawlMark& stackMark)
> at System.Security.CodeAccessPermission.Demand()
> at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess
>access, FileShare share, Int32 bufferSize, Boolean useAsync, String
>msgPath, Boolean bFromProxy)
> at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess
>access)
> at ReportHelper.ConfirmationStatement.WriteLogFile(String msg)
> at ReportHelper.ConfirmationStatement..ctor(Int32 futureBatchSetId)
> at CustomCodeProxy.OnInit()
> at Microsoft.ReportingServices.ReportProcessing.ExprHostObjectModel.
>CustomCodeProxyBase..ctor(IReportObjectModelProxyForCustomCode
>reportObjectModel)
> at ReportExprHostImpl..ctor(Boolean parametersOnly, Object
>reportObjectModel)
>The state of the failed permission was:
><IPermission class="System.Security.Permissions.FileIOPermission,
>mscorlib, Version=1.0.5000.0, Culture=neutral,
>PublicKeyToken=b77a5c561934e089"
> version="1"
> Read="C:\Program Files\Microsoft SQL
>Server\MSSQL\Reporting Services\ReportServer\bin\ReportLogger.log"
> Write="C:\Program Files\Microsoft SQL
>Server\MSSQL\Reporting Services\ReportServer\bin\ReportLogger.log"/>
>w3wp!processing!2aa8!10/05/2005-16:47:17:: e ERROR: Throwing
>Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
>Exception of type
>Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
>was thrown., ;
> Info:
>Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
>Exception of type
>Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
>was thrown.
>w3wp!library!2aa8!10/05/2005-16:47:17:: i INFO: Initializing
>EnableExecutionLogging to 'True' as specified in Server system
>properties.
>w3wp!webserver!2aa8!10/05/2005-16:47:17:: e ERROR: Reporting Services
>error Microsoft.ReportingServices.Diagnostics.Utilities.RSException:
>Failed to load expression host assembly. Details: Request for the
>permission of type System.Security.Permissions.FileIOPermission,
>mscorlib, Version=1.0.5000.0, Culture=neutral,
>PublicKeyToken=b77a5c561934e089 failed. -->
>Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
>Failed to load expression host assembly. Details: Request for the
>permission of type System.Security.Permissions.FileIOPermission,
>mscorlib, Version=1.0.5000.0, Culture=neutral,
>PublicKeyToken=b77a5c561934e089 failed.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200510/1|||I finally figured it out.
The problem was with assertion in my code. I changed it from
FileIOPermissionAccess.Write to FileIOPermissionAccess.AllAccess and it
worked.
I guess when you open a file with FileAccess.ReadWrite then assertion
FileIOPermissionAccess.Write is not enough.
Regards,|||Hi,
I'm custom assemblie to access the registry and get some data..
i'm getting a error of "Requested registry access is not allowed"
I followed all the steps that you mentioned but i still get the same error..
but in case of File access it works im not getting any error but for
Registry acccess im getting that error. did you tried using registry. i even
gave "FullTrust" in the the permission policy file.
Please let me know if any one have tried registree.
Thanks
Bava
"BALAJI K via SQLMonster.com" wrote:
> Hi,
> See this article....
> http://www.c-sharpcorner.com/Code/2005/June/CustomAssemblyinRS.asp
> If still you have the issue, write to me bkkrishnan [at] hotmail [dot] com
> Balaji
>
> Siwy wrote:
> >I'm trying to write to a text file from my custom assembly and I keep
> >getting FileIOPermission. The only way I can get it to work is by
> >changing the PermissionSetName of the whole CodeGroup from Nothing to
> >FullTrust. The assembly itself is very simple and all it does is it
> >writes one line to a text file.
> >
> >Here is what I did so far:
> >
> >1. I asserted the permission in my code.
> >2. I put the text file and my assembly into ReportSevrer bin folder and
> >changed the text file's security to allow "NETWORK SECURITY" (it
> >is IIS6) and just in case "Everyone" to write to it.
> >3. I added CodeGroup just after the code group with Url="$CodeGen$/*"
> >to the rssrvpolicy.config file with PermissionSetName="FullTrust".
> >
> >I even installed Visual Studio 2005 to get access to PermCalc tool.
> >All it showed me was that my dll needs FileIOPermission with
> >Unrestricted="true" and SecurityPermission with
> >Flags="Assertion" in the CodeGroup which I also tried by creating a
> >seperate PermissionSet.
> >
> >What else can I possibly try?
> >
> >My Code:
> >
> >private void WriteLogFile(String msg)
> >{
> > FileIOPermission perm1 = new
> >FileIOPermission(FileIOPermissionAccess.Write, @."C:\Program
> >Files\Microsoft SQL Server\MSSQL\Reporting
> >Services\ReportServer\bin\ReportLogger.log");
> > perm1.Assert();
> >
> > FileStream fs = new FileStream(@."C:\Program Files\Microsoft SQL
> >Server\MSSQL\Reporting Services\ReportServer\bin\ReportLogger.log",
> >FileMode.OpenOrCreate, FileAccess.ReadWrite);
> > StreamWriter w = new StreamWriter(fs);
> > w.BaseStream.Seek(0, SeekOrigin.End);
> > w.Write("{0} {1} ", DateTime.Now.ToLongTimeString(),
> > DateTime.Now.ToLongDateString());
> > w.Write(msg + "\r\n");
> > w.Flush();
> >
> > w.Close();
> >}
> >
> >My CodeGroup:
> >
> ><CodeGroup class="UnionCodeGroup"
> > version="1"
> > PermissionSetName="FullTrust"
> > Name="CGReportHelper"
> > Description="Allow execution of ReportHelper.dll">
> > <IMembershipCondition class="UrlMembershipCondition"
> > version="1"
> >Url="file://C:/Program Files/Microsoft SQL Server/MSSQL/Reporting
> >Services/ReportServer/bin/ReportHelper.dll"/>
> ></CodeGroup>
> >
> >My Error:
> >
> >w3wp!processing!2aa8!10/05/2005-16:47:17:: e ERROR: Failed to load
> >expression host assembly. Details: Request for the permission of type
> >System.Security.Permissions.FileIOPermission, mscorlib,
> >Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
> >failed.
> >System.Security.SecurityException: Request for the permission of type
> >System.Security.Permissions.FileIOPermission, mscorlib,
> >Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
> >failed.
> > at
> >System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet
> >grantedSet, PermissionSet deniedSet, CodeAccessPermission demand,
> >PermissionToken permToken)
> > at System.Security.CodeAccessSecurityEngine.Check(PermissionToken
> >permToken, CodeAccessPermission demand, StackCrawlMark& stackMark,
> >Int32 checkFrames, Int32 unrestrictedOverride)
> > at
> >System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission
> >cap, StackCrawlMark& stackMark)
> > at System.Security.CodeAccessPermission.Demand()
> > at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess
> >access, FileShare share, Int32 bufferSize, Boolean useAsync, String
> >msgPath, Boolean bFromProxy)
> > at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess
> >access)
> > at ReportHelper.ConfirmationStatement.WriteLogFile(String msg)
> > at ReportHelper.ConfirmationStatement..ctor(Int32 futureBatchSetId)
> > at CustomCodeProxy.OnInit()
> > at Microsoft.ReportingServices.ReportProcessing.ExprHostObjectModel.
> >CustomCodeProxyBase..ctor(IReportObjectModelProxyForCustomCode
> >reportObjectModel)
> > at ReportExprHostImpl..ctor(Boolean parametersOnly, Object
> >reportObjectModel)
> >
> >The state of the failed permission was:
> ><IPermission class="System.Security.Permissions.FileIOPermission,
> >mscorlib, Version=1.0.5000.0, Culture=neutral,
> >PublicKeyToken=b77a5c561934e089"
> > version="1"
> > Read="C:\Program Files\Microsoft SQL
> >Server\MSSQL\Reporting Services\ReportServer\bin\ReportLogger.log"
> > Write="C:\Program Files\Microsoft SQL
> >Server\MSSQL\Reporting Services\ReportServer\bin\ReportLogger.log"/>
> >
> >w3wp!processing!2aa8!10/05/2005-16:47:17:: e ERROR: Throwing
> >Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> >Exception of type
> >Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
> >was thrown., ;
> > Info:
> >Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> >Exception of type
> >Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
> >was thrown.
> >w3wp!library!2aa8!10/05/2005-16:47:17:: i INFO: Initializing
> >EnableExecutionLogging to 'True' as specified in Server system
> >properties.
> >w3wp!webserver!2aa8!10/05/2005-16:47:17:: e ERROR: Reporting Services
> >error Microsoft.ReportingServices.Diagnostics.Utilities.RSException:
> >Failed to load expression host assembly. Details: Request for the
> >permission of type System.Security.Permissions.FileIOPermission,
> >mscorlib, Version=1.0.5000.0, Culture=neutral,
> >PublicKeyToken=b77a5c561934e089 failed. -->
> >Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> >Failed to load expression host assembly. Details: Request for the
> >permission of type System.Security.Permissions.FileIOPermission,
> >mscorlib, Version=1.0.5000.0, Culture=neutral,
> >PublicKeyToken=b77a5c561934e089 failed.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200510/1
>

Kirk: Importing/Exporting with column ErrorCode, ErrorColumns

I am currently redirecting lookup failures into error tables with ErrorCode and ErrorColumn. It works fine until I want to transfer data into the archived database. The SSIS pacakage generate by SQL Exporting tool is throwing an "duplicate name of 'output column ErrorCode and ErrorColumn" error. This is caused by oledb source error output. The error output automatically add ErrorCode and ErrorColumn to the error output selection and not happy with it.

I think the question is down to "How to importing/exporting data when table contains ErrorCode or ErrorColumn column?"

Can you not use the derived column component to create 2 differntly named rows containing the same data?

-Jamie

|||

Yes, we can use different name and map them on ole db destination when writing to the error tables. We really don't want to go that way unless there is no other option.

Currently it if failing on the first step of the Data Flow, OLE DB Source, it is not reaching Derived column Transformation, and the build in SQL import/export is not working because of the same issue.

It will be good to verify so we can enhence in our sql naming standard. "Don't use ErrorCode or ErrorColumn as column name in the table; otherwise you can't use sql import/exprot tool. They are reserved keywords in SSIS."

-tianyu

|||

Are you inserting into a database table? If so then of course you cannot do this - a table cannot have 2 columns with the same name.

That doesn't mean that you can't insert identically named pipeline columns into that table. You just have to set up the mappings correctly in the destination adapter.

Have I misunderstood the problem?

-Jamie

|||

Use case for my question,

In PayRoll package, data failed username lookup redirect to Error_Dim_PayRoll table during the process. Later on I want to export the error rows to an archiving database. When you use the SQL exporting tool, the wizard will fail and complaint duplicate ErrorCode and ErrorColumn.

It is caused by OleDb Source in the package created and used by import/export tool. The OleDb source will automatically add ErrorCode and ErrorColumn column on its Error output stream.

This is based on default settings for both SQL 2005 and SSIS.

Repro Steps:

1. Create ErrorDB and ErrorDB_Reporting
2. Create Error_Dim_PayRoll tables for both database (script included bellow)
3. Run the Insert statement in ErrorDB
4. Run SQL Import/Export tool to export the row from ErrorDB to ErrorDB_Reporting (you can save generated SSIS package somewhere)
5. You will get complaints and export fails
6. Run the generated package, still fails with duplicate column name error.

CREATE TABLE [dbo].[Error_Dim_PayRoll](
[UserName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ErrorCode] [int] NULL,
[ErrorColumn] [int] NULL, [FailureReason] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO

INSERT INTO Error_Dim_Customer (UserName, ErrorCode, ErrorColumn, FailureReason) VALUES(NULL, 1, 1, 'Can not lookup UserKey from Employee table by UserName')
GO

|||

Tianyu Li wrote:

It is caused by OleDb Source in the package created and used by import/export tool. The OleDb source will automatically add ErrorCode and ErrorColumn column on its Error output stream.

It is caused by OleDb Source in the package created and used by import/export tool. The OleDb source will automatically add ErrorCode and ErrorColumn column on its Error output stream which conflict the columns come in from the data source.

Kinda urgent Connection error messages

Websphere application for nurses license renewal application that hits our SQL Server 2000.
This was the first error that we decided to stop and start the application:
Unable to load SQL Server Data Driver driver.
Unable to create database connection.
[Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
com.obs.data.DataObjectException: [Microsoft][SQLServer 2000 Driver for JDBC]Err or establishing socket.
This is the send stream of errors after we had thought we had solved the problem. This is kinda urgent because there are nurses out there waiting to get their licenses renewed and mad as heck:
[Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: Connection reset by peer
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection res et by peer: Connection reset by peer
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
Source
)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown
Sour
ce)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType
(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal(Unknown S
ource)
at com.microsoft.jdbc.base.BaseStatement.executeUpdat e(Unknown
Source)
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Hi John. Check the DBMS error log. I believe the DBMS is in trouble. The driver is just
finding out that the socket connection it sues to the DBMS is dieing.
Joe Weinstein at BEA
jdnospam@.hevanet.com wrote:

> Websphere application for nurses license renewal application that hits our SQL Server 2000.
> This was the first error that we decided to stop and start the application:
> Unable to load SQL Server Data Driver driver.
> Unable to create database connection.
> [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
> com.obs.data.DataObjectException: [Microsoft][SQLServer 2000 Driver for JDBC]Err or establishing socket.
>
> This is the send stream of errors after we had thought we had solved the problem. This is kinda urgent because there are nurses out there waiting to get their licenses renewed and mad as heck:
> [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: Connection reset by peer
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection res et by peer: Connection reset by peer
> at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
> Source
> )
> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
> Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown
> Sour
> ce)
> at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType
> (Unknown Source)
> at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal(Unknown S
> ource)
> at com.microsoft.jdbc.base.BaseStatement.executeUpdat e(Unknown
> Source)
>
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Hi John. Check the DBMS error log. I believe the DBMS is in trouble. The driver is just
finding out that the socket connection it sues to the DBMS is dieing.
Joe Weinstein at BEA
jdnospam@.hevanet.com wrote:

> Websphere application for nurses license renewal application that hits our SQL Server 2000.
> This was the first error that we decided to stop and start the application:
> Unable to load SQL Server Data Driver driver.
> Unable to create database connection.
> [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
> com.obs.data.DataObjectException: [Microsoft][SQLServer 2000 Driver for JDBC]Err or establishing socket.
>
> This is the send stream of errors after we had thought we had solved the problem. This is kinda urgent because there are nurses out there waiting to get their licenses renewed and mad as heck:
> [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: Connection reset by peer
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection res et by peer: Connection reset by peer
> at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
> Source
> )
> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
> Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown
> Sour
> ce)
> at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType
> (Unknown Source)
> at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal(Unknown S
> ource)
> at com.microsoft.jdbc.base.BaseStatement.executeUpdat e(Unknown
> Source)
>
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Joe,
There are a ton of locks in SQL Server under Locks/Process ID that reference the web renewal update table, but we know no one is using it.
Most of the properties of these locks show this:
sp_executesql;1
or this:
BeginTrans
So you are probably right, but I don't know where to look for/which error log. Do I use Enterprise Manager or get on the SQL Server box and look at Win 2000 error log?
What would you do to clean up these locks?
John
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||jdnospam@.hevanet.com wrote:

> Joe,
> There are a ton of locks in SQL Server under Locks/Process ID that
> reference the web renewal update table, but we know no one is using it.
Are you doing XA transactions? If so, in-doubt transactions may be holding
locks. Your DBA should be able to associate the locks with the connection
that holds them. Are you managing transactions yourself or are they managed
by websphere?
You may have to cycle websphere to truly kill all the connections it's
made. If that doesn't clear it I'll be surprised, and you might have to cycle
the DBMS. I can tell you that nothing you've shown implicates the driver at
all. If you were using WebLogic, I'd have lots of tools to help you...

> Most of the properties of these locks show this:
> sp_executesql;1
That implies use of PreparedStatements.
> or this:
> BeginTrans
That sounds like maybe bad application code. How are these transactions started?
You should never be sending "BEGIN TRAN" SQL. You should be only using setAutoCommit()
and commit()/rollback() JDBC calls.

> So you are probably right, but I don't know where to look for/which error log. Do I use Enterprise Manager or get on the SQL Server box and look at Win 2000 error log?
> What would you do to clean up these locks?
As above. First thing is to cycle the client (websphere).
Joe Weinstein at BEA

> John
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Hi Joe, As mentioned by John A. Davis, we are getting the following 3
different error message at a random fashion
[Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket
[Microsoft][SQLServer 2000 Driver for JDBC]Connection reset
[Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: socket
write error
Our prodcut is web based Java application, our application is configured
under Tomcat5 in one machine and the MS-SQL Server 2000 is running on another
machine, both the machines are in the same network in intranet. Right now we
are using MS-JDBC Driver SP2. The above listed errors occurring atlest 2 to 3
times a day, otherwise the application works fine. I am still wondering why
these error occurring in intranet based setup? any help on this appreciated.
"Joe Weinstein" wrote:

> Hi John. Check the DBMS error log. I believe the DBMS is in trouble. The driver is just
> finding out that the socket connection it sues to the DBMS is dieing.
> Joe Weinstein at BEA
> jdnospam@.hevanet.com wrote:
>
>
|||Saran wrote:

> Hi Joe, As mentioned by John A. Davis, we are getting the following 3
> different error message at a random fashion
> [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket
> [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset
> [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: socket
> write error
> Our prodcut is web based Java application, our application is configured
> under Tomcat5 in one machine and the MS-SQL Server 2000 is running on another
> machine, both the machines are in the same network in intranet. Right now we
> are using MS-JDBC Driver SP2. The above listed errors occurring atlest 2 to 3
> times a day, otherwise the application works fine. I am still wondering why
> these error occurring in intranet based setup? any help on this appreciated.
Hi. I wish I could help you more, but I can tell you that from the JDBC
driver perspective, these are all unexpected problems the driver is suffering
when the raw TCP socket it is using to talk to the DBMS either dies or fails
to be created. I guarantee that the driver is making the socket the same way
each time, so every creation should work the same way, and once it's open,
it should just work until it's closed. This is a problem beyond the application
and driver. Do you hold JDBC connections open indefinitely, sometimes without using
them for a long time? Does the problem seem to occur right after a long idle
period? Maybe the DBMS has an idle connection timeout, and is closing your
connections out from under you. Also, if you do have a limit on the number of
DBMS connections you are allowed, how are you controlling the number of connections
you try to make.
Joe Weinstein at BEA
[vbcol=seagreen]
> "Joe Weinstein" wrote:
>
|||We have adapted connection pool concept from the article below, to manage
connection objects.
http://java.sun.com/developer/online...k/conpool.html
We made a log file to see how many connections objects are maintained in
given time, most of the i noticed there will be only one connection object in
the pool to server, very occassionally i could see 2 to 3 connection object
in the pool.
does the above pooling concept may have problem?
"Joe Weinstein" wrote:

>
> Saran wrote:
>
> Hi. I wish I could help you more, but I can tell you that from the JDBC
> driver perspective, these are all unexpected problems the driver is suffering
> when the raw TCP socket it is using to talk to the DBMS either dies or fails
> to be created. I guarantee that the driver is making the socket the same way
> each time, so every creation should work the same way, and once it's open,
> it should just work until it's closed. This is a problem beyond the application
> and driver. Do you hold JDBC connections open indefinitely, sometimes without using
> them for a long time? Does the problem seem to occur right after a long idle
> period? Maybe the DBMS has an idle connection timeout, and is closing your
> connections out from under you. Also, if you do have a limit on the number of
> DBMS connections you are allowed, how are you controlling the number of connections
> you try to make.
> Joe Weinstein at BEA
>
>
|||Saran wrote:

> We have adapted connection pool concept from the article below, to manage
> connection objects.
> http://java.sun.com/developer/online...k/conpool.html
> We made a log file to see how many connections objects are maintained in
> given time, most of the i noticed there will be only one connection object in
> the pool to server, very occassionally i could see 2 to 3 connection object
> in the pool.
> does the above pooling concept may have problem?
Hi. The *concept* has no problem, but the implementation may. If it is not threadsafe,
you may well have one thread closing a connection while another is using it, in which
case you could have any sort of odd failure... but, not, these symptoms are all below
the driver or pool or application. Pooling is good. I would verify if the problems
that come up are when you use a connection that's been idle in the pool for a long
time. You could easily add a checker in the pool, which does a quick "select 1" during
your call to the pool for a connection. If the test passes, the connection is as
good as you can ensure. If the query fails, the pool can replace it and give you a
new one. BEA's WebLogic connection pools do a lot of this pretesting and protection...
Joe Weinstein at BEA
[vbcol=seagreen]
> "Joe Weinstein" wrote:
>
|||All the calls to the pool are syncronized as well pool itself checks if a
connection object is NOT in use before it returns to request.
As you suggested i can run a simple query before the pool returns a
connection to a request. You said "select 1", is it a valid query to validate
a connection object or just a sample query that i need to convert to "SELECT
Column1 FROM Table1".
"Joe Weinstein" wrote:

>
> Saran wrote:
>
> Hi. The *concept* has no problem, but the implementation may. If it is not threadsafe,
> you may well have one thread closing a connection while another is using it, in which
> case you could have any sort of odd failure... but, not, these symptoms are all below
> the driver or pool or application. Pooling is good. I would verify if the problems
> that come up are when you use a connection that's been idle in the pool for a long
> time. You could easily add a checker in the pool, which does a quick "select 1" during
> your call to the pool for a connection. If the test passes, the connection is as
> good as you can ensure. If the query fails, the pool can replace it and give you a
> new one. BEA's WebLogic connection pools do a lot of this pretesting and protection...
> Joe Weinstein at BEA
>
>

Killing a user/login

I have a user that I need to get rid of in a database. I tried to us
sp_droplogin but got an error regarding the user, I used sp_user and
then was told that the user owns objects and cannot be dropped.
This user has over 300 objects encompassing stored procedures, views,
tables and so on -- and I don't have enterprise manager. Is there a
quick and clean way of just zapping the user and everything it owns?!
Essentially I need to create a script to send to somebody to do this --
their user and objects are all out of wack.
JamesIt's not really clear what you are attempting to accomplish.
Removing a user is easy, removing all objects owned by that user isn't very
difficult either.
BUT, do you really want the objects to be DELETED from the database?
OR do you really want the user to be removed and the objects kept -but just
assigned to other ownership?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<starritt@.gmail.com> wrote in message
news:1158605443.645160.227840@.e3g2000cwe.googlegroups.com...
>I have a user that I need to get rid of in a database. I tried to us
> sp_droplogin but got an error regarding the user, I used sp_user and
> then was told that the user owns objects and cannot be dropped.
> This user has over 300 objects encompassing stored procedures, views,
> tables and so on -- and I don't have enterprise manager. Is there a
> quick and clean way of just zapping the user and everything it owns?!
> Essentially I need to create a script to send to somebody to do this --
> their user and objects are all out of wack.
> James
>

Killing a user/login

I have a user that I need to get rid of in a database. I tried to us
sp_droplogin but got an error regarding the user, I used sp_user and
then was told that the user owns objects and cannot be dropped.
This user has over 300 objects encompassing stored procedures, views,
tables and so on -- and I don't have enterprise manager. Is there a
quick and clean way of just zapping the user and everything it owns?!
Essentially I need to create a script to send to somebody to do this --
their user and objects are all out of wack.
James
It's not really clear what you are attempting to accomplish.
Removing a user is easy, removing all objects owned by that user isn't very
difficult either.
BUT, do you really want the objects to be DELETED from the database?
OR do you really want the user to be removed and the objects kept -but just
assigned to other ownership?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<starritt@.gmail.com> wrote in message
news:1158605443.645160.227840@.e3g2000cwe.googlegro ups.com...
>I have a user that I need to get rid of in a database. I tried to us
> sp_droplogin but got an error regarding the user, I used sp_user and
> then was told that the user owns objects and cannot be dropped.
> This user has over 300 objects encompassing stored procedures, views,
> tables and so on -- and I don't have enterprise manager. Is there a
> quick and clean way of just zapping the user and everything it owns?!
> Essentially I need to create a script to send to somebody to do this --
> their user and objects are all out of wack.
> James
>
sql

Killing a user/login

I have a user that I need to get rid of in a database. I tried to us
sp_droplogin but got an error regarding the user, I used sp_user and
then was told that the user owns objects and cannot be dropped.
This user has over 300 objects encompassing stored procedures, views,
tables and so on -- and I don't have enterprise manager. Is there a
quick and clean way of just zapping the user and everything it owns?!
Essentially I need to create a script to send to somebody to do this --
their user and objects are all out of wack.
JamesIt's not really clear what you are attempting to accomplish.
Removing a user is easy, removing all objects owned by that user isn't very
difficult either.
BUT, do you really want the objects to be DELETED from the database?
OR do you really want the user to be removed and the objects kept -but just
assigned to other ownership?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<starritt@.gmail.com> wrote in message
news:1158605443.645160.227840@.e3g2000cwe.googlegroups.com...
>I have a user that I need to get rid of in a database. I tried to us
> sp_droplogin but got an error regarding the user, I used sp_user and
> then was told that the user owns objects and cannot be dropped.
> This user has over 300 objects encompassing stored procedures, views,
> tables and so on -- and I don't have enterprise manager. Is there a
> quick and clean way of just zapping the user and everything it owns?!
> Essentially I need to create a script to send to somebody to do this --
> their user and objects are all out of wack.
> James
>

killing a process with a variable

I wish to kill all the processes for a given database.
have written the script that gives me all the spids for the database,
however I get an error when trying to execute;
KILL @.spid;
(Incorrect syntax near @.spid)
where @.spid is declared as a smallint.
Can anybody help?
TIA
Hi
You can't pass a variable. You need to create dynamic sql to execute that:
DECLARE @.exstring VARCHAR(20)
SELECT @.exstring = 'KILL ' + @.spid
executesql @.exstring
Regards
Mike
"Dan" wrote:

> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>
|||Hi Dan - I think this works.
DECLARE @.i INT
DECLARE @.strSQL NVARCHAR(255)
SET @.i = 73
SET @.strSQL = 'KILL ' + CAST(@.i AS CHAR (2))
--PRINT @.strSQL
EXEC sp_executesql @.strSQL
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
|||Hi,
If your Sql server version is 2000 then go for ALTER Database command rather
than KILL command.
ALTER Database <dbname> set single_user with rollback immediate
The above command will remove all the connected users to that database
immediately. After the activity u can change the db the multiuser.
ALTER Database <dbname> set multi_user
Thanks
Hari
MCDBA
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
>I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
|||Hi Dan,
I wrote the following script and tested it:
Use master
go
SET NOCOUNT ON
DECLARE @.strSQL varchar(255)
PRINT 'Killing Users'
PRINT '--'
CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30))
INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = 'YOUR DATABASE NAME
HERE'
DECLARE @.spid varchar(10)
DECLARE @.dbname2 varchar(40)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT 'Killing ' + @.spid
SET @.strSQL = 'KILL ' + @.spid
EXEC (@.strSQL)
END
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
PRINT 'Done'
go
Just replace 'YOUR DATABASE NAME HERE' with your database name.
Sasan
"Dan" wrote:

> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>

killing a process with a variable

I wish to kill all the processes for a given database.
have written the script that gives me all the spids for the database,
however I get an error when trying to execute;
KILL @.spid;
(Incorrect syntax near @.spid)
where @.spid is declared as a smallint.
Can anybody help?
TIAHi
You can't pass a variable. You need to create dynamic sql to execute that:
DECLARE @.exstring VARCHAR(20)
SELECT @.exstring = 'KILL ' + @.spid
executesql @.exstring
Regards
Mike
"Dan" wrote:
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>|||Hi Dan - I think this works.
DECLARE @.i INT
DECLARE @.strSQL NVARCHAR(255)
SET @.i = 73
SET @.strSQL = 'KILL ' + CAST(@.i AS CHAR (2))
--PRINT @.strSQL
EXEC sp_executesql @.strSQL
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>|||Hi,
If your Sql server version is 2000 then go for ALTER Database command rather
than KILL command.
ALTER Database <dbname> set single_user with rollback immediate
The above command will remove all the connected users to that database
immediately. After the activity u can change the db the multiuser.
ALTER Database <dbname> set multi_user
Thanks
Hari
MCDBA
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
>I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>|||Hi Dan,
I wrote the following script and tested it:
--
Use master
go
SET NOCOUNT ON
DECLARE @.strSQL varchar(255)
PRINT 'Killing Users'
PRINT '--'
CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30))
INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = 'YOUR DATABASE NAME
HERE'
DECLARE @.spid varchar(10)
DECLARE @.dbname2 varchar(40)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT 'Killing ' + @.spid
SET @.strSQL = 'KILL ' + @.spid
EXEC (@.strSQL)
END
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
PRINT 'Done'
go
--
Just replace 'YOUR DATABASE NAME HERE' with your database name.
Sasan
"Dan" wrote:
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>

Kill process is not working

Trying to kill a process which is causing the following
error in the log every 10 minutes.
Time out occurred while waiting for buffer latch type 2,
bp 0x144e5000, page (5120:811953513), stat 0x405, object
ID 10:1:0, waittime 3500. Continuing to wait.
How can I kill this process?Hi,
Look into the below link:-
Q310834 PRB: Common Causes of Error Message 844 or Error Message 845
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q310834
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:294bc01c465c7$a5ac5b50$a601280a@.phx.gbl...
> Trying to kill a process which is causing the following
> error in the log every 10 minutes.
> Time out occurred while waiting for buffer latch type 2,
> bp 0x144e5000, page (5120:811953513), stat 0x405, object
> ID 10:1:0, waittime 3500. Continuing to wait.
> How can I kill this process?

kill a large spid

hi,
from profiler, i've a spid that has a value of 1994834. the kill command
won't work... The error message is:
Msg 6101, Level 16, State 1, Line 1
Process ID 1994834is not a valid process ID. Choose a number between 1 and
1024.
here is the sql info for the box...
Microsoft SQL Server 2005 - 9.00.3161.00 (X64) Apr 3 2007 11:14:20
Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on
Windows NT 5.2 (Build 3790: Service Pack 2)Why are you using profiler to view the SPID? What does sp_who2 show as a
SPID for that process?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:C5420913-B755-44B3-8885-172D02FC2FD1@.microsoft.com...
> hi,
> from profiler, i've a spid that has a value of 1994834. the kill command
> won't work... The error message is:
> Msg 6101, Level 16, State 1, Line 1
> Process ID 1994834is not a valid process ID. Choose a number between 1 and
> 1024.
> here is the sql info for the box...
> Microsoft SQL Server 2005 - 9.00.3161.00 (X64) Apr 3 2007 11:14:20
> Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit)
> on
> Windows NT 5.2 (Build 3790: Service Pack 2)|||You could also use the Activity Monitor.
--
Ekrem Ã?nsoy
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:C5420913-B755-44B3-8885-172D02FC2FD1@.microsoft.com...
> hi,
> from profiler, i've a spid that has a value of 1994834. the kill command
> won't work... The error message is:
> Msg 6101, Level 16, State 1, Line 1
> Process ID 1994834is not a valid process ID. Choose a number between 1 and
> 1024.
> here is the sql info for the box...
> Microsoft SQL Server 2005 - 9.00.3161.00 (X64) Apr 3 2007 11:14:20
> Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit)
> on
> Windows NT 5.2 (Build 3790: Service Pack 2)|||i was using profiler to see other things and bump into that spid.
that process is cleared, now. profiler showed me it had duration over
2000000 and using 5 cpu. after clearning that process, it released the
memory pressure of the box; released 20% of the memory.
how else would be a better way to find run away process (like extra long
duration) and find the assoicated spid on an 05 box? sp_who2 doesn't have
"duration".
thanks.|||Profiler will only show duration after the statement or sp is completed. So
in the case of long running code that you may want to kill it does not help
very much. You might want to take a look at sys.dm_exec_requests and some of
the associated dmvs.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:B21F4598-393E-43B5-950D-3E2AA1CC32AA@.microsoft.com...
>i was using profiler to see other things and bump into that spid.
> that process is cleared, now. profiler showed me it had duration over
> 2000000 and using 5 cpu. after clearning that process, it released the
> memory pressure of the box; released 20% of the memory.
> how else would be a better way to find run away process (like extra long
> duration) and find the assoicated spid on an 05 box? sp_who2 doesn't have
> "duration".
> thanks.

Kick off procs

Hello. I was wondering what is the best way to kick off multiple procs
trapping the ones that had an error. Here's an example of what I came up
with.
alter procedure dbo.testError
@.problem int = 0 OUTPUT
AS
set nocount on
print 'start'
Declare @.error_msg int
set @.error_msg = 0
set @.error_msg = (Select count(*) from notable)
print 'yo'
select @.error_msg = @.@.error
IF @.error_msg != 0 GOTO handle_error
return @.Problem
handle_error:
set @.Problem = @.error_msg + @.Problem
print @.Problem
-- this is where I would kick off the processes in sequence
declare @.msg int
EXEC @.msg = testError
print 'testing = ' + convert(varchar(20), @.msg)
on testError I have an output variable. when you kick off the proc in my
kick off code, the print line never gets executed. and the subroutine in the
main proc never gets called. Really all I want to do is kick off a list of
sprocs and write to a table wether it was a success or not, then go on
kicking off the next sproc. Also, is it nessecarry to alter all my existing
sprocs to have an output variable and catch @.@.error on all calls to the db,
if not that would be ideal.
just wondering how everyone handles trapping errors with a kick off sproc,
and why this is not working.
Thanks,
RobRobert H wrote:
> Hello. I was wondering what is the best way to kick off multiple procs
> trapping the ones that had an error. Here's an example of what I came
> up with.
> alter procedure dbo.testError
> @.problem int = 0 OUTPUT
> AS
> set nocount on
> print 'start'
> Declare @.error_msg int
> set @.error_msg = 0
> set @.error_msg = (Select count(*) from notable)
> print 'yo'
> select @.error_msg = @.@.error
> IF @.error_msg != 0 GOTO handle_error
>
> return @.Problem
> handle_error:
> set @.Problem = @.error_msg + @.Problem
> print @.Problem
>
> -- this is where I would kick off the processes in sequence
> declare @.msg int
> EXEC @.msg = testError
> print 'testing = ' + convert(varchar(20), @.msg)
>
> on testError I have an output variable. when you kick off the proc in
> my kick off code, the print line never gets executed. and the
> subroutine in the main proc never gets called. Really all I want to
> do is kick off a list of sprocs and write to a table wether it was a
> success or not, then go on kicking off the next sproc. Also, is it
> nessecarry to alter all my existing sprocs to have an output variable
> and catch @.@.error on all calls to the db, if not that would be ideal.
> just wondering how everyone handles trapping errors with a kick off
> sproc, and why this is not working.
> Thanks,
> Rob
You're using a return value and an OUTPUT paramer in some interleaved
fashion, but they are not compatible.
You can return an INT return value using the following exec code and a
return statement:
DECLARE @.iRet INT
EXEC @.iRet = dbo.MyProc
PRINT @.iRet
You can use an OUTPUT parameter of most any data type and access the
value with the following exec code:
DECLARE @.NewName VARCHAR(50)
EXEC dbo.MyProc @.NewName OUTPUT
PRINT @.NewName
Or to do both:
DECLARE @.iRet INT
DECLARE @.NewName VARCHAR(50)
EXEC @.iRet = dbo.MyProc @.NewName OUTPUT
SELECT @.iRet, @.NewName
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi
Your print statement will reset @.@.ERROR therefore it is not going to give
you an error value.
You may want to read
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
John
"Robert H" wrote:

> Hello. I was wondering what is the best way to kick off multiple procs
> trapping the ones that had an error. Here's an example of what I came up
> with.
> alter procedure dbo.testError
> @.problem int = 0 OUTPUT
> AS
> set nocount on
> print 'start'
> Declare @.error_msg int
> set @.error_msg = 0
> set @.error_msg = (Select count(*) from notable)
> print 'yo'
> select @.error_msg = @.@.error
> IF @.error_msg != 0 GOTO handle_error
>
> return @.Problem
> handle_error:
> set @.Problem = @.error_msg + @.Problem
> print @.Problem
>
> -- this is where I would kick off the processes in sequence
> declare @.msg int
> EXEC @.msg = testError
> print 'testing = ' + convert(varchar(20), @.msg)
>
> on testError I have an output variable. when you kick off the proc in my
> kick off code, the print line never gets executed. and the subroutine in t
he
> main proc never gets called. Really all I want to do is kick off a list of
> sprocs and write to a table wether it was a success or not, then go on
> kicking off the next sproc. Also, is it nessecarry to alter all my existin
g
> sprocs to have an output variable and catch @.@.error on all calls to the db
,
> if not that would be ideal.
> just wondering how everyone handles trapping errors with a kick off sproc,
> and why this is not working.
> Thanks,
> Rob
>
>

kick off multiple procs

Hello. I was wondering what is the best way to kick off multiple procs
trapping the ones that had an error. Here's an example of what I came up
with.
alter procedure dbo.testError
@.problem int = 0 OUTPUT
AS
set nocount on
print 'start'
Declare @.error_msg int
set @.error_msg = 0
set @.error_msg = (Select count(*) from notable)
print 'yo'
select @.error_msg = @.@.error
IF @.error_msg != 0 GOTO handle_error
return @.Problem
handle_error:
set @.Problem = @.error_msg + @.Problem
print @.Problem
-- this is where I would kick off the processes in sequence
declare @.msg int
EXEC @.msg = testError
print 'testing = ' + convert(varchar(20), @.msg)
on testError I have an output variable. when you kick off the proc in my
kick off code, the print line never gets executed. and the subroutine in the
main proc never gets called. Really all I want to do is kick off a list of
sprocs and write to a table wether it was a success or not, then go on
kicking off the next sproc. Also, is it nessecarry to alter all my existing
sprocs to have an output variable and catch @.@.error on all calls to the db,
if not that would be ideal.
just wondering how everyone handles trapping errors with a kick off sproc,
and why this is not working.
Thanks,
RobRobert,
Check out:
http://www.sommarskog.se/error-handling-I.html
and
http://www.sommarskog.se/error-handling-II.html
HTH
Jerry
"Robert H" <thestripe@.yahoo_spamno.com> wrote in message
news:u%23p0BJQyFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello. I was wondering what is the best way to kick off multiple procs
> trapping the ones that had an error. Here's an example of what I came up
> with.
> alter procedure dbo.testError
> @.problem int = 0 OUTPUT
> AS
> set nocount on
> print 'start'
> Declare @.error_msg int
> set @.error_msg = 0
> set @.error_msg = (Select count(*) from notable)
> print 'yo'
> select @.error_msg = @.@.error
> IF @.error_msg != 0 GOTO handle_error
>
> return @.Problem
> handle_error:
> set @.Problem = @.error_msg + @.Problem
> print @.Problem
>
> -- this is where I would kick off the processes in sequence
> declare @.msg int
> EXEC @.msg = testError
> print 'testing = ' + convert(varchar(20), @.msg)
>
> on testError I have an output variable. when you kick off the proc in my
> kick off code, the print line never gets executed. and the subroutine in
> the
> main proc never gets called. Really all I want to do is kick off a list of
> sprocs and write to a table wether it was a success or not, then go on
> kicking off the next sproc. Also, is it nessecarry to alter all my
> existing
> sprocs to have an output variable and catch @.@.error on all calls to the
> db,
> if not that would be ideal.
> just wondering how everyone handles trapping errors with a kick off sproc,
> and why this is not working.
> Thanks,
> Rob
>
>

Keyset does not exist

I get this error when trying to deploy a report from VS2005, when trying to get to the report manager page, when trying to do anything in SSMS with the report server.

Keyset does not exist (Exception from HRESULT: 0x80090016) (rsRPCError) (Microsoft.ReportingServices.Diagnostics)

Can anyone give me a clue what to do?

Hi GraemeH.

Have you had any luck resolving this?

I am getting the same error while attempting to deploy a report from VS 2005. Report Manager is running. The Report project folders deployed from VS fine, just not the report.

I am running XP Pro SP2.

Thanks.

|||

Hi Folks - I'm hitting the same brick wall. Did you manage to find a resolution? I would be very interested in your solution.

Thanks!

|||I did but can't remember how - I think it was a security problem and RS was running under a wrong account, or I had a renegade version of SQL Express. Sorry can't do better than that!|||I had the same problem so changed my Windows Service Identity to Network Service. I know this is a security issue but at least now I can work back from here to create an account that has just the permissions it needs... later, when I have some time.|||but, I can't create encryption key backup ... how about that?

Keyset does not exist

I get this error when trying to deploy a report from VS2005, when trying to get to the report manager page, when trying to do anything in SSMS with the report server.

Keyset does not exist (Exception from HRESULT: 0x80090016) (rsRPCError) (Microsoft.ReportingServices.Diagnostics)

Can anyone give me a clue what to do?

Hi GraemeH.

Have you had any luck resolving this?

I am getting the same error while attempting to deploy a report from VS 2005. Report Manager is running. The Report project folders deployed from VS fine, just not the report.

I am running XP Pro SP2.

Thanks.

|||

Hi Folks - I'm hitting the same brick wall. Did you manage to find a resolution? I would be very interested in your solution.

Thanks!

|||I did but can't remember how - I think it was a security problem and RS was running under a wrong account, or I had a renegade version of SQL Express. Sorry can't do better than that!|||I had the same problem so changed my Windows Service Identity to Network Service. I know this is a security issue but at least now I can work back from here to create an account that has just the permissions it needs... later, when I have some time.|||but, I can't create encryption key backup ... how about that?

Keyset does not exist

I get this error when trying to deploy a report from VS2005, when trying to get to the report manager page, when trying to do anything in SSMS with the report server.

Keyset does not exist (Exception from HRESULT: 0x80090016) (rsRPCError) (Microsoft.ReportingServices.Diagnostics)

Can anyone give me a clue what to do?

Hi GraemeH.

Have you had any luck resolving this?

I am getting the same error while attempting to deploy a report from VS 2005. Report Manager is running. The Report project folders deployed from VS fine, just not the report.

I am running XP Pro SP2.

Thanks.

|||

Hi Folks - I'm hitting the same brick wall. Did you manage to find a resolution? I would be very interested in your solution.

Thanks!

|||I did but can't remember how - I think it was a security problem and RS was running under a wrong account, or I had a renegade version of SQL Express. Sorry can't do better than that!|||I had the same problem so changed my Windows Service Identity to Network Service. I know this is a security issue but at least now I can work back from here to create an account that has just the permissions it needs... later, when I have some time.|||but, I can't create encryption key backup ... how about that?

Keys out of order on page error

We have found we have corruption in our db.
After running DBCC CHECKDB the following error were displayed on
multiple pages.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1285579618, Index ID 3. Keys out of order on page
(1:384662), slots 75 and 76.
I read a post that suggested running DBCC CHECKDB with the repair_build
option.
Since we are a 24/7 shop I cannot restore from tape, because it would
take several hours.
What are the risks of running DBCC CHECKDB with the repair_build option,
and will this fix the problem properly?
We are still unsure what has caused this corruption, any body else have
this problem?
TIA,
-BenThere are a couple of things.
Firstly it is very intensive so turn off as many services
you can get away with, i.e SQL Agent
Secondly depending upon the severity of the error it may
result in data loss. Personally I would try firstly with
REPAIR_FAST, then REPAIR_REBUILD and if all else fails
REPAIR_ALLOW_DATA_LOSS, its that option where you may lose
data.
If the REPAIR_ALLOW_DATA_LOSS doesn't work then you will
have to restore from backup.
As for the reason why to difficult to say.
Good luck
Peter
>--Original Message--
>We have found we have corruption in our db.
>After running DBCC CHECKDB the following error were
displayed on
>multiple pages.
>Server: Msg 2511, Level 16, State 1, Line 1
>Table error: Object ID 1285579618, Index ID 3. Keys out
of order on page
>(1:384662), slots 75 and 76.
>I read a post that suggested running DBCC CHECKDB with
the repair_build
>option.
>Since we are a 24/7 shop I cannot restore from tape,
because it would
>take several hours.
>What are the risks of running DBCC CHECKDB with the
repair_build option,
>and will this fix the problem properly?
>We are still unsure what has caused this corruption, any
body else have
>this problem?
>TIA,
>-Ben
>.
>|||Hi Ben,
As the error is in a non-clustered index, you can just rebuild the index
with DBCC DBREINDEX (<table_name>, <index_name>). Or if you want to be on
the safe side, just drop and recreate the index.
After that, investigate why the corruption occurred in the first place. You
might have hardware problems.
--
Jacco Schalkwijk
SQL Server MVP
"Ben" <bmilliron@.gmail.com> wrote in message
news:OJgEzgufEHA.636@.TK2MSFTNGP12.phx.gbl...
> We have found we have corruption in our db.
> After running DBCC CHECKDB the following error were displayed on multiple
> pages.
> Server: Msg 2511, Level 16, State 1, Line 1
> Table error: Object ID 1285579618, Index ID 3. Keys out of order on page
> (1:384662), slots 75 and 76.
> I read a post that suggested running DBCC CHECKDB with the repair_build
> option.
> Since we are a 24/7 shop I cannot restore from tape, because it would take
> several hours.
> What are the risks of running DBCC CHECKDB with the repair_build option,
> and will this fix the problem properly?
> We are still unsure what has caused this corruption, any body else have
> this problem?
> TIA,
> -Ben|||In addition:
The latest update of Books Online has some very detailed recommendations for most error numbers.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:374a01c47eee$f6a6a890$a501280a@.phx.gbl...
> There are a couple of things.
> Firstly it is very intensive so turn off as many services
> you can get away with, i.e SQL Agent
> Secondly depending upon the severity of the error it may
> result in data loss. Personally I would try firstly with
> REPAIR_FAST, then REPAIR_REBUILD and if all else fails
> REPAIR_ALLOW_DATA_LOSS, its that option where you may lose
> data.
> If the REPAIR_ALLOW_DATA_LOSS doesn't work then you will
> have to restore from backup.
> As for the reason why to difficult to say.
> Good luck
> Peter
>
> >--Original Message--
> >We have found we have corruption in our db.
> >
> >After running DBCC CHECKDB the following error were
> displayed on
> >multiple pages.
> >
> >Server: Msg 2511, Level 16, State 1, Line 1
> >Table error: Object ID 1285579618, Index ID 3. Keys out
> of order on page
> >(1:384662), slots 75 and 76.
> >
> >I read a post that suggested running DBCC CHECKDB with
> the repair_build
> >option.
> >
> >Since we are a 24/7 shop I cannot restore from tape,
> because it would
> >take several hours.
> >
> >What are the risks of running DBCC CHECKDB with the
> repair_build option,
> >and will this fix the problem properly?
> >
> >We are still unsure what has caused this corruption, any
> body else have
> >this problem?
> >
> >TIA,
> >
> >-Ben
> >.
> >|||I have dropped and recreated the index, thanks. I have also opened up
an issue with our vendor to see if this may be a possible cause.
Jacco Schalkwijk wrote:
> Hi Ben,
> As the error is in a non-clustered index, you can just rebuild the index
> with DBCC DBREINDEX (<table_name>, <index_name>). Or if you want to be on
> the safe side, just drop and recreate the index.
> After that, investigate why the corruption occurred in the first place. You
> might have hardware problems.
>

Keys out of order on page error

There are a couple of things.
Firstly it is very intensive so turn off as many services
you can get away with, i.e SQL Agent
Secondly depending upon the severity of the error it may
result in data loss. Personally I would try firstly with
REPAIR_FAST, then REPAIR_REBUILD and if all else fails
REPAIR_ALLOW_DATA_LOSS, its that option where you may lose
data.
If the REPAIR_ALLOW_DATA_LOSS doesn't work then you will
have to restore from backup.
As for the reason why to difficult to say.
Good luck
Peter

>--Original Message--
>We have found we have corruption in our db.
>After running DBCC CHECKDB the following error were
displayed on
>multiple pages.
>Server: Msg 2511, Level 16, State 1, Line 1
>Table error: Object ID 1285579618, Index ID 3. Keys out
of order on page
>(1:384662), slots 75 and 76.
>I read a post that suggested running DBCC CHECKDB with
the repair_build
>option.
>Since we are a 24/7 shop I cannot restore from tape,
because it would
>take several hours.
>What are the risks of running DBCC CHECKDB with the
repair_build option,
>and will this fix the problem properly?
>We are still unsure what has caused this corruption, any
body else have
>this problem?
>TIA,
>-Ben
>.
>
In addition:
The latest update of Books Online has some very detailed recommendations for most error numbers.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:374a01c47eee$f6a6a890$a501280a@.phx.gbl...[vbcol=seagreen]
> There are a couple of things.
> Firstly it is very intensive so turn off as many services
> you can get away with, i.e SQL Agent
> Secondly depending upon the severity of the error it may
> result in data loss. Personally I would try firstly with
> REPAIR_FAST, then REPAIR_REBUILD and if all else fails
> REPAIR_ALLOW_DATA_LOSS, its that option where you may lose
> data.
> If the REPAIR_ALLOW_DATA_LOSS doesn't work then you will
> have to restore from backup.
> As for the reason why to difficult to say.
> Good luck
> Peter
>
> displayed on
> of order on page
> the repair_build
> because it would
> repair_build option,
> body else have