How to Manage Errors with one file

Hi,

I am pretty new in SSIS 2005, and I have some problems... I want to add logging and error management in my package. I found how to made logging. But for errors managing i have some difficulties.

In my package I have only a flat file source and an ole db destination. I want add errors management for both of them. So I create a connection manager for errors on a file. For both element i add redirect row for all available error type and then i add 2 flat file destination. I branch red arrows of flat file source and ole db destination to the flat file destination.

When i run packge i have an error which indicate me that file error is already take by another process... I don't understand why. And i don't want to create on file for each element on package. Have you any idea on why i have this error? Or how can i made what i want do?

Krest

Before going too far - could you checj if your redirect destination do not point to the same file as primary error file? What happens if you turn off package logging?

|||

Hi,

Thanks for you help.

So, i use the same file for the two flat file destination, because i want all my error in the same file. I i turn off logging (SSISmenu->logging and all checkboxes are not checked.

I have the same error as before. here is the exact error message [Flat File Destination 1 [806]] Warning: The process cannot access the file because it is being used by another process.

krest

how to make sure a upgrade from sql 2005 standard to enterprise edition?

Randy,

I did run upgrade advisior to check the existed sql 2005 standard edition to upgrade to enterprise editon. I got the following error message:

SQL Server version: 09.00.1399 is not supported by this release of Upgrade Advisor

Is it means the upgrade advisor can only work on from 7.0, 2000 to 2005? If I need check from standard to enterjprise in 2005, what kind of tool I can use?

One of the SQL Server forums would be a better place to ask this question:

http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=19&SiteID=1

You'll have better luck finding an answer there.

-Tom

How to make self-backup log in msde?

Hi.
I am trying to automatically backup transaction log when error 9002
happened. So i have created appropriate job and alert to catch this error.
I have two instances of sql server under Windows 2000. One of them is full
SQL Server, another is msde.
When transaction log is full in full SQL Server error 9002 severity 17
state 2 is logged in sql server log and in Windows Application log. My
alert firing my job. All is fine.
But when transaction log is full in MSDE error 9002 severity 17 state 6 is
logged only in sql server log. It is not logged into Windows Application
log so my alert does not work.
So here is my questions:
1. Why MSDE does not log error into Windows Application log?
2. Why error 9002 has severity 19 in sysmessages table but is generated
with severity 17?
3. Why state of error 9002 differs under sql server and msde?

Thank you.

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/"Igor Solodovnikov" <igor@.helpco.kiev> wrote in message
news:opr5ullxv2fk7eds@.124-225.dialup.alfacom.net...
> Hi.
> I am trying to automatically backup transaction log when error 9002
> happened. So i have created appropriate job and alert to catch this error.
> I have two instances of sql server under Windows 2000. One of them is full
> SQL Server, another is msde.
> When transaction log is full in full SQL Server error 9002 severity 17
> state 2 is logged in sql server log and in Windows Application log. My
> alert firing my job. All is fine.
> But when transaction log is full in MSDE error 9002 severity 17 state 6 is
> logged only in sql server log. It is not logged into Windows Application
> log so my alert does not work.
> So here is my questions:
> 1. Why MSDE does not log error into Windows Application log?
> 2. Why error 9002 has severity 19 in sysmessages table but is generated
> with severity 17?
> 3. Why state of error 9002 differs under sql server and msde?
> Thank you.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

1. I've also set this up (MSDE2000 SP3a on Windows XP Pro SP1), and it works
correctly - error 9002 is logged in both the SQL log and the Windows event
log, and the alert executes a job. This post suggests that restarting SQL
Server may help:

http://groups.google.com/groups?hl=...%40cpmsftngxa08

If that doesn't work, then perhaps you can give more details about your
setup - which version of MSDE do you have? Have you installed the latest
servicepack? Also, if you execute RAISERROR ... WITH LOG to raise a test
error, is it logged in the Windows event log?

2. No idea, but 17 seems to be more correct, since it's the severity level
for insufficient resources.

3. State is not a fixed value - the process which raises the error can use
different states with the same error, to provide extra information about
exactly how the error was caused. In this case, it could be something like 2
for system databases and 6 for user ones, or 2 in Standard Edition, but 6 in
MSDE etc. I got state 6 with a user DB in MSDE.

Simon|||On Sat, 3 Apr 2004 12:37:08 +0200, Simon Hayes <sql@.hayes.ch> wrote:

> "Igor Solodovnikov" <igor@.helpco.kiev> wrote in message
> news:opr5ullxv2fk7eds@.124-225.dialup.alfacom.net...
>> Hi.
>> I am trying to automatically backup transaction log when error 9002
>> happened. So i have created appropriate job and alert to catch this
>> error.
>> I have two instances of sql server under Windows 2000. One of them is
>> full
>> SQL Server, another is msde.
>> When transaction log is full in full SQL Server error 9002 severity 17
>> state 2 is logged in sql server log and in Windows Application log. My
>> alert firing my job. All is fine.
>> But when transaction log is full in MSDE error 9002 severity 17 state 6
>> is
>> logged only in sql server log. It is not logged into Windows Application
>> log so my alert does not work.
>> So here is my questions:
>> 1. Why MSDE does not log error into Windows Application log?
>> 2. Why error 9002 has severity 19 in sysmessages table but is generated
>> with severity 17?
>> 3. Why state of error 9002 differs under sql server and msde?
>>
>> Thank you.
>>
>> --
>> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
> 1. I've also set this up (MSDE2000 SP3a on Windows XP Pro SP1), and it
> works
> correctly - error 9002 is logged in both the SQL log and the Windows
> event
> log, and the alert executes a job. This post suggests that restarting SQL
> Server may help:
> http://groups.google.com/groups?hl=...%40cpmsftngxa08
> If that doesn't work, then perhaps you can give more details about your
> setup - which version of MSDE do you have? Have you installed the latest
> servicepack? Also, if you execute RAISERROR ... WITH LOG to raise a test
> error, is it logged in the Windows event log?
> 2. No idea, but 17 seems to be more correct, since it's the severity
> level
> for insufficient resources.
> 3. State is not a fixed value - the process which raises the error can
> use
> different states with the same error, to provide extra information about
> exactly how the error was caused. In this case, it could be something
> like 2
> for system databases and 6 for user ones, or 2 in Standard Edition, but
> 6 in
> MSDE etc. I got state 6 with a user DB in MSDE.
> Simon

Thank you for answer. OS is Windows 2000 SP4. I have MSDE 2000 SP3a and
MSDE 2000 Release A instances. Both do not write error 9002 into Windows
error log identically. SQL 2000 instance in the same OS works fine.
I have rebooted computer few times. It does not resolved my problem. I
think rebooting computer is equivalent to restarting SQL Server. Am i
wrong?
I got state 2 overloading user DB in MSDE -> Windows error log is not
written
State 6 overloading identical user DB in SQL Server -> Windows error log
is written correct
I will try to execute RAISERROR...WITH LOG and will post its results later.

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||On Sat, 3 Apr 2004 12:37:08 +0200, Simon Hayes <sql@.hayes.ch> wrote:

> "Igor Solodovnikov" <igor@.helpco.kiev> wrote in message
> news:opr5ullxv2fk7eds@.124-225.dialup.alfacom.net...
>> Hi.
>> I am trying to automatically backup transaction log when error 9002
>> happened. So i have created appropriate job and alert to catch this
>> error.
>> I have two instances of sql server under Windows 2000. One of them is
>> full
>> SQL Server, another is msde.
>> When transaction log is full in full SQL Server error 9002 severity 17
>> state 2 is logged in sql server log and in Windows Application log. My
>> alert firing my job. All is fine.
>> But when transaction log is full in MSDE error 9002 severity 17 state 6
>> is
>> logged only in sql server log. It is not logged into Windows Application
>> log so my alert does not work.
>> So here is my questions:
>> 1. Why MSDE does not log error into Windows Application log?
>> 2. Why error 9002 has severity 19 in sysmessages table but is generated
>> with severity 17?
>> 3. Why state of error 9002 differs under sql server and msde?
>>
>> Thank you.
>>
>> --
>> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
> 1. I've also set this up (MSDE2000 SP3a on Windows XP Pro SP1), and it
> works
> correctly - error 9002 is logged in both the SQL log and the Windows
> event
> log, and the alert executes a job. This post suggests that restarting SQL
> Server may help:
> http://groups.google.com/groups?hl=...%40cpmsftngxa08
> If that doesn't work, then perhaps you can give more details about your
> setup - which version of MSDE do you have? Have you installed the latest
> servicepack? Also, if you execute RAISERROR ... WITH LOG to raise a test
> error, is it logged in the Windows event log?
> 2. No idea, but 17 seems to be more correct, since it's the severity
> level
> for insufficient resources.
> 3. State is not a fixed value - the process which raises the error can
> use
> different states with the same error, to provide extra information about
> exactly how the error was caused. In this case, it could be something
> like 2
> for system databases and 6 for user ones, or 2 in Standard Edition, but
> 6 in
> MSDE etc. I got state 6 with a user DB in MSDE.
> Simon

I have executed RAISERROR ... WITH LOG statement in my MSDE database. It
does not write into the Windows log. But i have found some warning
messages in Application log. Here is my application for today. May be you
can give me some advice about that warnings:

03.04.2004,19:53:27,MSSQLSERVER,Information,(2),17 055,S-1-5-21-1699048451-83243044-507081533-1009,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 8128; Using 'sqlmap70.dll'
version '2000.80.194' to execute extended stored procedure
'xp_get_mapi_profiles'.
..
03.04.2004,19:53:21,MSSQLSERVER,Information,(2),17 055,S-1-5-21-1699048451-83243044-507081533-1009,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 8128; Using 'xpstar.dll'
version '2000.80.194' to execute extended stored procedure
'sp_MSgetversion'.
..
03.04.2004,19:33:42,EventSystem,Warning,Firing Agent,4100,N/A,W2000,The
COM+ Event System failed to create an instance of the subscriber
{6295DF2D-35EE-11D1-8707-00C04FD93327}. CoCreateInstanceEx returned
HRESULT 8000401A.
03.04.2004,19:33:37,WinMgmt,Warning,None,41,N/A,W2000,WMI ADAP was unable
to create object index 1848 for Performance Library RSVP because no value
was found in the 009 subkey
03.04.2004,19:33:32,WinMgmt,Warning,None,41,N/A,W2000,WMI ADAP was unable
to create object index 2212 for Performance Library MSDTC because no value
was found in the 009 subkey
03.04.2004,19:33:31,SQLSERVERAGENT,Information,(2) ,101,N/A,W2000,The
description for Event ID ( 101 ) in Source ( SQLSERVERAGENT ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: The event log file is corrupt..
03.04.2004,19:33:30,SQLAgent$FAVGAME,Information,( 2),101,N/A,W2000,The
description for Event ID ( 101 ) in Source ( SQLAgent$FAVGAME ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: The event log file is corrupt..
03.04.2004,19:33:22,MSSQLSERVER,Information,(2),17 055,S-1-5-21-1699048451-83243044-507081533-1009,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 8128; Using 'xpsqlbot.dll'
version '2000.80.194' to execute extended stored procedure 'xp_qv'.
..
03.04.2004,19:33:14,MSSQLSERVER,Information,(2),17 055,N/A,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 17052; Recovery complete.
..
03.04.2004,19:33:10,MSSQLSERVER,Information,(2),17 055,N/A,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 3455; Analysis of database
'cashbasedmir' (10) is 100% complete (approximately 0 more seconds)
..
03.04.2004,19:33:06,SQLAgent$ALERT,Information,(2) ,101,N/A,W2000,The
description for Event ID ( 101 ) in Source ( SQLAgent$ALERT ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: The event log file is corrupt..
03.04.2004,19:33:04,EvntAgnt,Information,None,2018 ,N/A,W2000,The
description for Event ID ( 2018 ) in Source ( EvntAgnt ) cannot be found.
The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. The
following information is part of the event: The event log file is corrupt..
03.04.2004,19:32:57,SceCli,Information,None,1704,N /A,W2000,Security policy
in the Group policy objects are applied successfully.
03.04.2004,19:32:50,SQLSERVERAGENT,Information,(2) ,101,N/A,W2000,The
description for Event ID ( 101 ) in Source ( SQLSERVERAGENT ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: The event log file is corrupt..
03.04.2004,19:32:45,MSSQLSERVER,Information,(2),17 055,N/A,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 17126; SQL Server is ready for
client connections
..
03.04.2004,19:32:45,MSSQLSERVER,Information,(2),17 055,N/A,W2000,"The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 19013; SQL server listening on
192.168.0.4:1433, 127.0.0.1:1433.
.."
03.04.2004,19:32:45,MSSQLSERVER,Information,(2),17 055,N/A,W2000,"The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 19013; SQL server listening on
TCP, Shared Memory, Named Pipes.
.."
03.04.2004,19:32:45,MSSQLServer,Warning,(8),19011, N/A,W2000,The
description for Event ID ( 19011 ) in Source ( MSSQLServer ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: (SpnRegister) : Error 1355.
03.04.2004,19:32:45,MSSQLSERVER,Information,(2),17 055,N/A,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 17834; Using 'SSNETLIB.DLL'
version '8.0.194'.
..
03.04.2004,19:32:43,SQLAgent$FAVGAME,Information,( 2),101,N/A,W2000,The
description for Event ID ( 101 ) in Source ( SQLAgent$FAVGAME ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: The event log file is corrupt..
03.04.2004,19:32:39,SQLAgent$ALERT,Information,(2) ,101,N/A,W2000,The
description for Event ID ( 101 ) in Source ( SQLAgent$ALERT ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: The event log file is corrupt..
03.04.2004,19:32:37,MSSQL$FAVGAME,Warning,(8),1901 1,N/A,W2000,The
description for Event ID ( 19011 ) in Source ( MSSQL$FAVGAME ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: (SpnRegister) : Error 1355.
03.04.2004,19:32:34,MSSQLSERVER,Information,(2),17 055,N/A,W2000,"The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 17125; Using dynamic lock
allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
.."
03.04.2004,19:32:34,MSSQLSERVER,Information,(2),17 055,N/A,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 17124; SQL Server configured
for thread mode processing.
..
03.04.2004,19:32:34,MSSQL$ALERT,Warning,(8),19011, N/A,W2000,The
description for Event ID ( 19011 ) in Source ( MSSQL$ALERT ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: (SpnRegister) : Error 1355.
03.04.2004,19:32:33,MSSQLSERVER,Information,(2),17 055,N/A,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 17162; SQL Server is starting
at priority class 'normal'(1 CPU detected).
..
03.04.2004,19:32:32,MSSQLSERVER,Information,(2),17 055,N/A,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 17104; Server Process ID is
728.
..
03.04.2004,19:32:32,MSSQLSERVER,Information,(2),17 055,N/A,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 17052; Microsoft SQL Server
2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)|||"Igor Solodovnikov" <igor@.helpco.kiev> wrote in message
news:opr5wgspdvfk7eds@.124-204.dialup.alfacom.net...
> On Sat, 3 Apr 2004 12:37:08 +0200, Simon Hayes <sql@.hayes.ch> wrote:
> > "Igor Solodovnikov" <igor@.helpco.kiev> wrote in message
> > news:opr5ullxv2fk7eds@.124-225.dialup.alfacom.net...
> >> Hi.
> >> I am trying to automatically backup transaction log when error 9002
> >> happened. So i have created appropriate job and alert to catch this
> >> error.
> >> I have two instances of sql server under Windows 2000. One of them is
> >> full
> >> SQL Server, another is msde.
> >> When transaction log is full in full SQL Server error 9002 severity 17
> >> state 2 is logged in sql server log and in Windows Application log. My
> >> alert firing my job. All is fine.
> >> But when transaction log is full in MSDE error 9002 severity 17 state 6
> >> is
> >> logged only in sql server log. It is not logged into Windows
Application
> >> log so my alert does not work.
> >> So here is my questions:
> >> 1. Why MSDE does not log error into Windows Application log?
> >> 2. Why error 9002 has severity 19 in sysmessages table but is generated
> >> with severity 17?
> >> 3. Why state of error 9002 differs under sql server and msde?
> >>
> >> Thank you.
> >>
> >> --
> >> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
> > 1. I've also set this up (MSDE2000 SP3a on Windows XP Pro SP1), and it
> > works
> > correctly - error 9002 is logged in both the SQL log and the Windows
> > event
> > log, and the alert executes a job. This post suggests that restarting
SQL
> > Server may help:
http://groups.google.com/groups?hl=...%40cpmsftngxa08
> > If that doesn't work, then perhaps you can give more details about your
> > setup - which version of MSDE do you have? Have you installed the latest
> > servicepack? Also, if you execute RAISERROR ... WITH LOG to raise a test
> > error, is it logged in the Windows event log?
> > 2. No idea, but 17 seems to be more correct, since it's the severity
> > level
> > for insufficient resources.
> > 3. State is not a fixed value - the process which raises the error can
> > use
> > different states with the same error, to provide extra information about
> > exactly how the error was caused. In this case, it could be something
> > like 2
> > for system databases and 6 for user ones, or 2 in Standard Edition, but
> > 6 in
> > MSDE etc. I got state 6 with a user DB in MSDE.
> > Simon
> I have executed RAISERROR ... WITH LOG statement in my MSDE database. It
> does not write into the Windows log. But i have found some warning
> messages in Application log. Here is my application for today. May be you
> can give me some advice about that warnings:

<snip
It looks like something is fundamentally wrong with your installation, if
you get so many DLL errors. Personally, I would completely remove all MSDE
instances on the server and reinstall it, instead of trying to identify each
error:

http://support.microsoft.com/defaul...3&Product=sql2k

By the way, the error 17055 suggests that you are truncating the log even if
the database is in full recovery mode. This isn't a good idea, because then
you can't recover your previous backups if you need to:

http://support.microsoft.com/defaul...2&Product=sql2k

Simon|||"Igor Solodovnikov" <igor@.helpco.kiev> wrote in message news:<opr5ullxv2fk7eds@.124-225.dialup.alfacom.net>...
> Hi.
> I am trying to automatically backup transaction log when error 9002
> happened. So i have created appropriate job and alert to catch this error.
> I have two instances of sql server under Windows 2000. One of them is full
> SQL Server, another is msde.
> When transaction log is full in full SQL Server error 9002 severity 17
> state 2 is logged in sql server log and in Windows Application log. My
> alert firing my job. All is fine.
> But when transaction log is full in MSDE error 9002 severity 17 state 6 is
> logged only in sql server log. It is not logged into Windows Application
> log so my alert does not work.
> So here is my questions:
> 1. Why MSDE does not log error into Windows Application log?
> 2. Why error 9002 has severity 19 in sysmessages table but is generated
> with severity 17?
> 3. Why state of error 9002 differs under sql server and msde?
> Thank you.

I do this by defining a Database Maintenance plan, but leaving the
jobs this generates disabled. I then use msdb.dbo.sp_start_job to call
the job when wanted. Not only does this method log to the msdb history
tables, but old backups, execution logs, etc are cleaned up per the
retention settings.

You may already be "losing data" when you get the 9002 error. Take a
look at performance alerts and set a threshold (say 70%) so the system
may be able to backup the log and recover space before transactions
fail because the log is full.

HTH
JIV

John W. Pollins IV
Readi Operations DBA
Equitable Resources Inc

How to make MDAC 2.8 behave like 2.7 regarding 'Object was open' e

On SQL Server 2K, recently put on SP4. Some VB code began to break with a
-2147217915 'Object was open' error. The code is opening a new
ADODB.Recordset, and that recordset is already open. This is clearly a bug
in the VB code, but the bug was tolerated under MDAC 2.7. The bug is
present in many code segments so it will take some time to fix it.
My DBA is hounding me to fix the code, he wants to get SP4 on to fix a
memory problem.
Is there a way to make MDAC 2.8 work like MDAC 2.7. That is, can MDAC 2.8
be configured to tolerate opening an open recordset?
BBHi
I don't think there is an option to do this. There are several versions of
2.8 you may want to check that is consistent (with the component checker) an
d
if you are on the SP1.
http://msdn.microsoft.com/data/mdac...ds/default.aspx
John
"bearcreek" wrote:

> On SQL Server 2K, recently put on SP4. Some VB code began to break with a
> -2147217915 'Object was open' error. The code is opening a new
> ADODB.Recordset, and that recordset is already open. This is clearly a b
ug
> in the VB code, but the bug was tolerated under MDAC 2.7. The bug is
> present in many code segments so it will take some time to fix it.
> My DBA is hounding me to fix the code, he wants to get SP4 on to fix a
> memory problem.
> Is there a way to make MDAC 2.8 work like MDAC 2.7. That is, can MDAC 2.8
> be configured to tolerate opening an open recordset?
> --
> BBsql

How to make MDAC 2.8 behave like 2.7 regarding 'Object was open' e

On SQL Server 2K, recently put on SP4. Some VB code began to break with a
-2147217915 'Object was open' error. The code is opening a new
ADODB.Recordset, and that recordset is already open. This is clearly a bug
in the VB code, but the bug was tolerated under MDAC 2.7. The bug is
present in many code segments so it will take some time to fix it.
My DBA is hounding me to fix the code, he wants to get SP4 on to fix a
memory problem.
Is there a way to make MDAC 2.8 work like MDAC 2.7. That is, can MDAC 2.8
be configured to tolerate opening an open recordset?
--
BBHi
I don't think there is an option to do this. There are several versions of
2.8 you may want to check that is consistent (with the component checker) and
if you are on the SP1.
http://msdn.microsoft.com/data/mdac/downloads/default.aspx
John
"bearcreek" wrote:
> On SQL Server 2K, recently put on SP4. Some VB code began to break with a
> -2147217915 'Object was open' error. The code is opening a new
> ADODB.Recordset, and that recordset is already open. This is clearly a bug
> in the VB code, but the bug was tolerated under MDAC 2.7. The bug is
> present in many code segments so it will take some time to fix it.
> My DBA is hounding me to fix the code, he wants to get SP4 on to fix a
> memory problem.
> Is there a way to make MDAC 2.8 work like MDAC 2.7. That is, can MDAC 2.8
> be configured to tolerate opening an open recordset?
> --
> BB

How to log in to my DB ?

Hi,

I have a website with mssql data base recently I got this error:

it's showing error as 'Microsoft OLE DB Provider for SQL Server error '80040e4d'

Login failed for user

/includes/connect.inc, line 23

As I am a new to this data base I dont know how to Log in to my data base and rectify it can any one help me in this I am a naive in this....recently I got this error...So, where you able to log in previously, and now suddenly you can't? Or have you never made a connection with this database server before?

Who is the DBA for the server?|||KBA http://support.microsoft.com/default.aspx/kb/222828 fyi.|||So, where you able to log in previously, and now suddenly you can't? Or have you never made a connection with this database server before?

Who is the DBA for the server?

Till now i have not made a connection with this data base server as everything was done by my developer now he is not seen|||KBA http://support.microsoft.com/default.aspx/kb/222828 fyi.

I dont know how to open that control panel and do that as I dont know anything about this pls can you guide me ?|||No, back up way further than that.

Who administers the database server?

Have you been granted access to the server either through your network login or a specified SQL Server login and password.|||KBA http://support.microsoft.com/default.aspx/kb/222828 fyi.

I used this now and rectified but the problem still excists I think I have to try doing this :

http://support.microsoft.com/kb/292644/en-us

I am not sure how to do it Can any one help me ?|||No, back up way further than that.

Who administers the database server?

Have you been granted access to the server either through your network login or a specified SQL Server login and password.

I have the user id and password to log in before my developer did it after that he left not seen around so to safe guard the data base and ftp i asked my hosting company to change my password after which this i get this error message.|||that explains that. you changed the password to the sql server without updating the one the application uses in the sourcecode.|||Yes I recently asked my hosting company to change the password Now kindly let me know how should i rectify the error using this ?
http://support.microsoft.com/kb/292644/en-us

wishes|||you probably have to go into your application sourcecode and update the password in your connection string(s).|||you probably have to go into your application sourcecode and update the password in your connection string(s).

Yes I know I have to do this :

http://support.microsoft.com/kb/292644/en-us

But I dont know How to do this as I tried many ways but I dont know how to do it|||I had not clicked on the link. Are you using the sa account for your application? If so you have bigger problems. That is a ginormous security no-no.

As for how to change the password in the application, I guess you need to hire a computer programmer.|||Could be in a config file...|||This happend only after i changed the data base password from the hosting side I dont know how to solve this problem|||Julie,

The password for your application is either in a config file or embedded in the application code. You will need the original application code, or you will need to contact the application developer. Other than that, there is not much help we can give you.|||From the original error message, go to the file at:
/includes/connect.inc
Open this up with notepad. Look for the old password on line 23 or before. Change that out with the new password, and test.|||From the original error message, go to the file at:
/includes/connect.inc
Open this up with notepad. Look for the old password on line 23 or before. Change that out with the new password, and test.

I went to the /inlcudes/connect.inc and I dont see the password I just see the following:

Function Login
'StrUsername = cint(Request.Form("username"))
'StrPassword = Trim(Request.Form("password"))
Dim conn, rs, sql

Set conn = Server.CreateObject("ADODB.Connection")
conn.open Strpro
Set rs = Server.CreateObject("ADODB.Recordset")
'SQL = "SELECT ID,Password FROM register WHERE ID= ('"& Replace(StrUsername,"'","''") &" ') AND password ('"& Replace(StrPassword,"'","''") &" ') OR Email= ('"& Replace(StrUserid,"'","''") &" ') "
sql = "SELECT * FROM admin_login WHERE username= '" & Replace(Request.Form("Username"),"'", "''") & "' AND password = '" & Replace(Request.Form("Password"),"'", "''") & "' "

Kindly advice what should I do in this ?|||you want the password that will be set in this variable "Strpro". The password you are looking at in this code is for the application. The one set in "Strpro" is for the server.

The code you need will look like...

Set Strpro = "some stuff in here"|||you want the password that will be set in this variable "Strpro". The password you are looking at in this code is for the application. The one set in "Strpro" is for the server.

The code you need will look like...

Set Strpro = "some stuff in here"

ok let me try to find out but Even if you see this is for the Application we dont see any password ?|||under all probability the connection details are there in the file /includes/connect.inc somewhere near line 23 and the application is an ASP application. open that file (download it from the server if needed) and you will find the connection string where the password is written.|||sorry for the misleading late post...i missed the 2nd page...|||I tried my level best but I was not able to get connected so I just went back to my old password :(|||I tried to change my password from the Host Once again I get the same problem.
I get a error report :

Microsoft OLE DB Provider for SQL Server error '80040e4d'

Login failed for user

/includes/connect.inc, line 23

Kindly help me in this|||Line 23 in the Connect.inc file is where the connection object is having the Open method invoked. However, there are several ways to supply all the required parameters. Do you see the word "Password" in the code before line 23? Or does (oh the horror) the connect.inc require other files?|||Hire someone who know what they are doing?|||Hire someone who know what they are doing?
...about the same solution I arrived at a week ago:
Julie,

The password for your application is either in a config file or embedded in the application code. You will need the original application code, or you will need to contact the application developer. Other than that, there is not much help we can give you.

how to let others connect to my sql server 2005?

Hi,

My collegue is trying to connect to my sql server 2005 using login 'sa' but he is not able to. It gives the following error :-

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 233)

Please suggest

Ashu sharma

You have to enable remote connections:

http://www.developer.com/db/article.php/3496511
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=204199&SiteID=1

HTH, Jens Suessmeyer.

|||

Still not working, all the protocols are enable and i have also allowed remote connection.

Any Idea?

Ashu

|||Did you fire up SQL Browser? Do you have a firewall running? Checkout this blog: http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx|||

Check also this here:

http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

HTH, Jens Suessmeyer.