internal consistency error

I have a sql database
I run dbcc checkdb and get no errors
I go to backup the database in enterprise manager, and it won't
restore because it has internal consistency errors
I can go back to an older version of the database

I run a particular procedure in our accounting software package
Never do I get errors when running dbcc checkdb

Sometimes I get the backup and restore problem, sometimes I don't

I am doing this on a test server with about 220 megs of RAM

I am trying the dbcc checkdb ('dbname',REPAIR_REBUILD) command

Any other ideas?An update to the problem:

I ran the process on my test server on a one-gig or so database and it
runs fine but gets an internal consistency error when I backup and try
to restore (dbcc checkdb, with and without REPAIR_REBUILD, runs just
fine)

But I run the process on the real server and it backs up and restores
just fine

So .........

It points to a resource problem on the test server

But I am not sure of this

Any feedback on this from anyone?|||brucestromcpa@.aol.com (bruce strom) wrote in message news:<8a9196fd.0310300827.41041bda@.posting.google.com>...
> An update to the problem:
> I ran the process on my test server on a one-gig or so database and it
> runs fine but gets an internal consistency error when I backup and try
> to restore (dbcc checkdb, with and without REPAIR_REBUILD, runs just
> fine)
> But I run the process on the real server and it backs up and restores
> just fine
> So .........
> It points to a resource problem on the test server
> But I am not sure of this
> Any feedback on this from anyone?

A consistency error means that the backup set is corrupt, for some
reason. That could mean a hardware issue, or a bad tape, or perhaps
backing up across an unreliable network. There are also issues if the
two versions of MSSQL are not the same. Perhaps you can give some more
details - where are you backing up to (tape, local drive, network
drive)? What version of MSSQL do you have? What's the operating system
and filesystem? As a minimum test, is this reliable on your test
server:

backup database MyDB to disk = 'c:\MyDB.bak' -- or another local drive
restore database MyDB from disk = 'c:\MyDB.bak'

If that isn't reliable, do OS-level tools detect any integrity issues
with the filesystem?

Simon

intermittently my SSIS Packages which are run through the JOB are failing

Hi SSIS Guru's,

I am facing a very strange problem.

We have 2 Physical Servers (A and B) on which we have installed the SQL Server, one primary (A) and other as secondary (B). And there is a cluster (C) available to acces the running server. I have created some SSIS packages which we installed on the Server A (Primary), and created the job on the cluster server which initiates the SSIS packages, whcih are installed in the File System.

The problem i am facing is the some thing related to Connection time out. And interestingly i am not getting this error Always. Approxiamtely For Every 5 Times once it;s Failing. I am copying the errors Which i encountered in the different runs.

The thing i am confused is why i am not geting the error all the time? And Why am i getting this error all the time in a different data flow task. My SSIS Package structure is I have created one master package and 6 Child packages. I am getting the connection string for the Data base from the Configuration file which is defined in the XML File.

The connection string that i am using is

Data Source=<<server name>>;User ID=DOMAIN\user;Initial Catalog=DatabaseName;Provider=SQLNCLI.1;Integrated Security=SSPI;

*************************************************************************************************************************************

RUN 1 - Error
Executed as user: AMR\sys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights

reserved. Started: 2:00:07 PM Error: 2007-09-15 14:02:35.92 Code: 0xC0202009 Source: ssis_emp Connection manager

"DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An

OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to complete

login process due to delay in opening server connection". End Error Error: 2007-09-15 14:02:35.92 Code: 0xC020801C

Source: infr_char Get the Records from emp 1 [72] Description: SSIS Error Code

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireConnection method... The package execution fa... The step failed.

*************************************************************************************************************************************

*************************************************************************************************************************************

RUN 2 - Error
Message
Executed as user: AMR\sys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights

reserved. Started: 9:15:01 AM Error: 2007-09-15 09:17:01.64 Code: 0xC0202009 Source: ssis_emp Connection manager

"DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An

OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to complete

login process due to delay in opening server connection". End Error Error: 2007-09-15 09:17:01.64 Code: 0xC020801C

Source: Data Flow Task Get the Records from emp [473] Description: SSIS Error Code

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireConnection me... The package execution fa... The step failed.

*************************************************************************************************************************************

*************************************************************************************************************************************

Run -3 Error
Message
Executed as user: AMR\sys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights

reserved. Started: 11:30:01 PM Error: 2007-09-14 23:32:21.28 Code: 0xC0202009 Source: ssis_dept Connection

manager "DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code:

0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable

to complete login process due to delay in opening server connection". End Error Error: 2007-09-14 23:32:21.28 Code:

0xC020801C Source: Data Flow Task Get the Records from dept [632] Description: SSIS Error Code

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireConnection method ... The package execution fa... The step failed.

*************************************************************************************************************************************

*************************************************************************************************************************************

Run - 4 Error

Message
Executed as user: AMR\sys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights

reserved. Started: 11:00:02 PM Error: 2007-09-14 23:02:21.46 Code: 0xC0202009 Source: ssis_emp Connection

manager "DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code:

0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable

to complete login process due to delay in opening server connection". End Error Error: 2007-09-14 23:02:21.46 Code:

0xC020801C Source: infr_itm_char_val Get the Records from emp_master [1] Description: SSIS Error Code

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireCon... The package execution fa... The step failed.

*************************************************************************************************************************************

*************************************************************************************************************************************

Run -5 Error

Message
Executed as user: AMR\sys_calyp. ...Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp

1984-2005. All rights reserved. Started: 9:10:59 PM Error: 2007-09-14 21:12:23.25 Code: 0xC0202009 Source:

ssis_salgrade Connection manager "DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.

Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005

Description: "Unable to complete login process due to delay in opening server connection". End Error Error: 2007-09-14

21:12:23.25 Code: 0xC020801C Source: Data Flow Task - ssis_salgrade get salgrade [3227] Description: SSIS Error

Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireConnection method c. The step failed.

*************************************************************************************************************************************

I thought i found the solution my self... I changed the delay validation to all the packages to true and the problem dissappeared

intermittently my SSIS Packages which are run through the JOB are failing

Hi SSIS Guru's,

I am facing a very strange problem.

We have 2 Physical Servers (A and B) on which we have installed the SQL Server, one primary (A) and other as secondary (B). And there is a cluster (C) available to acces the running server. I have created some SSIS packages which we installed on the Server A (Primary), and created the job on the cluster server which initiates the SSIS packages, whcih are installed in the File System.

The problem i am facing is the some thing related to Connection time out. And interestingly i am not getting this error Always. Approxiamtely For Every 5 Times once it;s Failing. I am copying the errors Which i encountered in the different runs.

The thing i am confused is why i am not geting the error all the time? And Why am i getting this error all the time in a different data flow task. My SSIS Package structure is I have created one master package and 6 Child packages. I am getting the connection string for the Data base from the Configuration file which is defined in the XML File.

The connection string that i am using is

Data Source=<<server name>>;User ID=DOMAIN\user;Initial Catalog=DatabaseName;Provider=SQLNCLI.1;Integrated Security=SSPI;

*************************************************************************************************************************************

RUN 1 - Error
Executed as user: AMR\sys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights

reserved. Started: 2:00:07 PM Error: 2007-09-15 14:02:35.92 Code: 0xC0202009 Source: ssis_emp Connection manager

"DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An

OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to complete

login process due to delay in opening server connection". End Error Error: 2007-09-15 14:02:35.92 Code: 0xC020801C

Source: infr_char Get the Records from emp 1 [72] Description: SSIS Error Code

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireConnection method... The package execution fa... The step failed.

*************************************************************************************************************************************

*************************************************************************************************************************************

RUN 2 - Error
Message
Executed as user: AMR\sys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights

reserved. Started: 9:15:01 AM Error: 2007-09-15 09:17:01.64 Code: 0xC0202009 Source: ssis_emp Connection manager

"DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An

OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to complete

login process due to delay in opening server connection". End Error Error: 2007-09-15 09:17:01.64 Code: 0xC020801C

Source: Data Flow Task Get the Records from emp [473] Description: SSIS Error Code

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireConnection me... The package execution fa... The step failed.

*************************************************************************************************************************************

*************************************************************************************************************************************

Run -3 Error
Message
Executed as user: AMR\sys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights

reserved. Started: 11:30:01 PM Error: 2007-09-14 23:32:21.28 Code: 0xC0202009 Source: ssis_dept Connection

manager "DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code:

0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable

to complete login process due to delay in opening server connection". End Error Error: 2007-09-14 23:32:21.28 Code:

0xC020801C Source: Data Flow Task Get the Records from dept [632] Description: SSIS Error Code

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireConnection method ... The package execution fa... The step failed.

*************************************************************************************************************************************

*************************************************************************************************************************************

Run - 4 Error

Message
Executed as user: AMR\sys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights

reserved. Started: 11:00:02 PM Error: 2007-09-14 23:02:21.46 Code: 0xC0202009 Source: ssis_emp Connection

manager "DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code:

0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable

to complete login process due to delay in opening server connection". End Error Error: 2007-09-14 23:02:21.46 Code:

0xC020801C Source: infr_itm_char_val Get the Records from emp_master [1] Description: SSIS Error Code

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireCon... The package execution fa... The step failed.

*************************************************************************************************************************************

*************************************************************************************************************************************

Run -5 Error

Message
Executed as user: AMR\sys_calyp. ...Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp

1984-2005. All rights reserved. Started: 9:10:59 PM Error: 2007-09-14 21:12:23.25 Code: 0xC0202009 Source:

ssis_salgrade Connection manager "DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.

Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005

Description: "Unable to complete login process due to delay in opening server connection". End Error Error: 2007-09-14

21:12:23.25 Code: 0xC020801C Source: Data Flow Task - ssis_salgrade get salgrade [3227] Description: SSIS Error

Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireConnection method c. The step failed.

*************************************************************************************************************************************

I thought i found the solution my self... I changed the delay validation to all the packages to true and the problem dissappeared

intermittent replication issue

Hello,

I currently run a nightly replication from a windows server 2003 machine with sql server 2000 sp3 to a windows 2003 server machine with sql server 2005 sp1. I run a snapshot replication as merge is not supported from 2000 to 2005.

2 out 5 snapshots weekly fail with the following error:

2006-08-09 17:00:06.97 Message Code: 10054
2006-08-09 17:00:06.97
2006-08-09 17:00:06.97 [0%] The replication agent had encountered an exception.
2006-08-09 17:00:06.97 Source: Replication
2006-08-09 17:00:06.97 Exception Type: Microsoft.SqlServer.Replication.ConnectionFailureException
2006-08-09 17:00:06.97 Exception Message: TCP Provider: An existing connection was forcibly closed by the remote host.

I have setup the sql server 2005 agent service to automatic with a 'log on as' domain admin account. The sql server agent on the 2000 machine uses the local system account.

Does anyone know what i can try to fix this?

thks

geoff

Is this the snapshot agent or the distribution agent that's failing?|||

regardless, it's some kind of a security or network problem you're having. Check this similar post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=187500&SiteID=1.

sql

Intermittent issue with outer joins on subqueries

Hello, we are running into an issue which seems to be a bug in the way
MSSQL joins on subqueries. The below query is run daily by a scheduled
task and usually runs correctly. About once a week the query produces
a resultset that makes it appear that the outer join on the subquery
(alias: POINFO) was changed to an inner join. The resultset has only a
percentage of the records it should have as a result. Are there any
know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
problem happens on a Win2000 server. Thanks.
SELECT *
FROM rc.dbo.LineItems LI
INNER JOIN rc.dbo.orders O
ON O.guidPK = LI.orderguidPK
INNER JOIN decode DECODE
ON O.status = DECODE.status
INNER JOIN products P
ON LI.productPK = P.PK
/* The below join periodically seems to become an inner join
*/
LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
from purchase_orders PO
INNER JOIN purchase_orders_lines POL
ON PO.PK = POL.purchaseorderPK
INNER JOIN fulfillment_products FP
ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
INNER JOIN I18NManufacturer MAN
ON FP.manPK = MAN.PK
INNER JOIN suppliers S
ON FP.supplierPK = SM.PK
WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
ON O.order_number = POINFO.order_number AND LI.productPK =
POINFO.productPK
WHERE O.order_type <> 1 and O.active=1
and O.status between 2 and 32
Can you capture the plan when the bad result happens, and compare it to the
normal plan?
(In other words, does it happen frequently enough that if you tried, you
could reproduce the problem in Query Analyzer?)
"Jesse Hogan" <JesseHogan0@.gmail.com> wrote in message
news:049ac85e-1cde-4fd9-8755-0b03494302d2@.1g2000hsl.googlegroups.com...
> Hello, we are running into an issue which seems to be a bug in the way
> MSSQL joins on subqueries. The below query is run daily by a scheduled
> task and usually runs correctly. About once a week the query produces
> a resultset that makes it appear that the outer join on the subquery
> (alias: POINFO) was changed to an inner join. The resultset has only a
> percentage of the records it should have as a result. Are there any
> know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
> problem happens on a Win2000 server. Thanks.
> SELECT *
> FROM rc.dbo.LineItems LI
> INNER JOIN rc.dbo.orders O
> ON O.guidPK = LI.orderguidPK
> INNER JOIN decode DECODE
> ON O.status = DECODE.status
> INNER JOIN products P
> ON LI.productPK = P.PK
> /* The below join periodically seems to become an inner join
> */
> LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
> from purchase_orders PO
> INNER JOIN purchase_orders_lines POL
> ON PO.PK = POL.purchaseorderPK
> INNER JOIN fulfillment_products FP
> ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
> INNER JOIN I18NManufacturer MAN
> ON FP.manPK = MAN.PK
> INNER JOIN suppliers S
> ON FP.supplierPK = SM.PK
> WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
> ON O.order_number = POINFO.order_number AND LI.productPK =
> POINFO.productPK
> WHERE O.order_type <> 1 and O.active=1
> and O.status between 2 and 32
|||I would be much more likely to think the WHERE clause is limiting the rows
'unexpectedly'. Perhaps you should set up an audit trail whereby you store
the COUNT(*) of the entire SELECT as well as the COUNT(*) for the SELECT
without the join to POINFO by itself into a table each time this process is
run (with enough identifying information added to ensure you can pinpoint
the rows for each run). This will help you see if you are affecting fewer
rows with the main query simply because of the WHERE clause or is the LOJ
magically translating into an INNER JOIN. That will give you definitive
evidence to take to Microsoft if it is a bug too (which I will definitely
admit isn't out of the question). :-)
Doing a quick check of post-SP4 hotfixes, see if this one applies
http://support.microsoft.com/kb/892310/
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jesse Hogan" <JesseHogan0@.gmail.com> wrote in message
news:049ac85e-1cde-4fd9-8755-0b03494302d2@.1g2000hsl.googlegroups.com...
> Hello, we are running into an issue which seems to be a bug in the way
> MSSQL joins on subqueries. The below query is run daily by a scheduled
> task and usually runs correctly. About once a week the query produces
> a resultset that makes it appear that the outer join on the subquery
> (alias: POINFO) was changed to an inner join. The resultset has only a
> percentage of the records it should have as a result. Are there any
> know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
> problem happens on a Win2000 server. Thanks.
> SELECT *
> FROM rc.dbo.LineItems LI
> INNER JOIN rc.dbo.orders O
> ON O.guidPK = LI.orderguidPK
> INNER JOIN decode DECODE
> ON O.status = DECODE.status
> INNER JOIN products P
> ON LI.productPK = P.PK
> /* The below join periodically seems to become an inner join
> */
> LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
> from purchase_orders PO
> INNER JOIN purchase_orders_lines POL
> ON PO.PK = POL.purchaseorderPK
> INNER JOIN fulfillment_products FP
> ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
> INNER JOIN I18NManufacturer MAN
> ON FP.manPK = MAN.PK
> INNER JOIN suppliers S
> ON FP.supplierPK = SM.PK
> WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
> ON O.order_number = POINFO.order_number AND LI.productPK =
> POINFO.productPK
> WHERE O.order_type <> 1 and O.active=1
> and O.status between 2 and 32

Intermittent issue with outer joins on subqueries

Hello, we are running into an issue which seems to be a bug in the way
MSSQL joins on subqueries. The below query is run daily by a scheduled
task and usually runs correctly. About once a week the query produces
a resultset that makes it appear that the outer join on the subquery
(alias: POINFO) was changed to an inner join. The resultset has only a
percentage of the records it should have as a result. Are there any
know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
problem happens on a Win2000 server. Thanks.
SELECT *
FROM rc.dbo.LineItems LI
INNER JOIN rc.dbo.orders O
ON O.guidPK = LI.orderguidPK
INNER JOIN decode DECODE
ON O.status = DECODE.status
INNER JOIN products P
ON LI.productPK = P.PK
/* The below join periodically seems to become an inner join
*/
LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
from purchase_orders PO
INNER JOIN purchase_orders_lines POL
ON PO.PK = POL.purchaseorderPK
INNER JOIN fulfillment_products FP
ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
INNER JOIN I18NManufacturer MAN
ON FP.manPK = MAN.PK
INNER JOIN suppliers S
ON FP.supplierPK = SM.PK
WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
ON O.order_number = POINFO.order_number AND LI.productPK = POINFO.productPK
WHERE O.order_type <> 1 and O.active=1
and O.status between 2 and 32Can you capture the plan when the bad result happens, and compare it to the
normal plan?
(In other words, does it happen frequently enough that if you tried, you
could reproduce the problem in Query Analyzer?)
"Jesse Hogan" <JesseHogan0@.gmail.com> wrote in message
news:049ac85e-1cde-4fd9-8755-0b03494302d2@.1g2000hsl.googlegroups.com...
> Hello, we are running into an issue which seems to be a bug in the way
> MSSQL joins on subqueries. The below query is run daily by a scheduled
> task and usually runs correctly. About once a week the query produces
> a resultset that makes it appear that the outer join on the subquery
> (alias: POINFO) was changed to an inner join. The resultset has only a
> percentage of the records it should have as a result. Are there any
> know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
> problem happens on a Win2000 server. Thanks.
> SELECT *
> FROM rc.dbo.LineItems LI
> INNER JOIN rc.dbo.orders O
> ON O.guidPK = LI.orderguidPK
> INNER JOIN decode DECODE
> ON O.status = DECODE.status
> INNER JOIN products P
> ON LI.productPK = P.PK
> /* The below join periodically seems to become an inner join
> */
> LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
> from purchase_orders PO
> INNER JOIN purchase_orders_lines POL
> ON PO.PK = POL.purchaseorderPK
> INNER JOIN fulfillment_products FP
> ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
> INNER JOIN I18NManufacturer MAN
> ON FP.manPK = MAN.PK
> INNER JOIN suppliers S
> ON FP.supplierPK = SM.PK
> WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
> ON O.order_number = POINFO.order_number AND LI.productPK => POINFO.productPK
> WHERE O.order_type <> 1 and O.active=1
> and O.status between 2 and 32|||I would be much more likely to think the WHERE clause is limiting the rows
'unexpectedly'. Perhaps you should set up an audit trail whereby you store
the COUNT(*) of the entire SELECT as well as the COUNT(*) for the SELECT
without the join to POINFO by itself into a table each time this process is
run (with enough identifying information added to ensure you can pinpoint
the rows for each run). This will help you see if you are affecting fewer
rows with the main query simply because of the WHERE clause or is the LOJ
magically translating into an INNER JOIN. That will give you definitive
evidence to take to Microsoft if it is a bug too (which I will definitely
admit isn't out of the question). :-)
Doing a quick check of post-SP4 hotfixes, see if this one applies
http://support.microsoft.com/kb/892310/
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jesse Hogan" <JesseHogan0@.gmail.com> wrote in message
news:049ac85e-1cde-4fd9-8755-0b03494302d2@.1g2000hsl.googlegroups.com...
> Hello, we are running into an issue which seems to be a bug in the way
> MSSQL joins on subqueries. The below query is run daily by a scheduled
> task and usually runs correctly. About once a week the query produces
> a resultset that makes it appear that the outer join on the subquery
> (alias: POINFO) was changed to an inner join. The resultset has only a
> percentage of the records it should have as a result. Are there any
> know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
> problem happens on a Win2000 server. Thanks.
> SELECT *
> FROM rc.dbo.LineItems LI
> INNER JOIN rc.dbo.orders O
> ON O.guidPK = LI.orderguidPK
> INNER JOIN decode DECODE
> ON O.status = DECODE.status
> INNER JOIN products P
> ON LI.productPK = P.PK
> /* The below join periodically seems to become an inner join
> */
> LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
> from purchase_orders PO
> INNER JOIN purchase_orders_lines POL
> ON PO.PK = POL.purchaseorderPK
> INNER JOIN fulfillment_products FP
> ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
> INNER JOIN I18NManufacturer MAN
> ON FP.manPK = MAN.PK
> INNER JOIN suppliers S
> ON FP.supplierPK = SM.PK
> WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
> ON O.order_number = POINFO.order_number AND LI.productPK => POINFO.productPK
> WHERE O.order_type <> 1 and O.active=1
> and O.status between 2 and 32

intergration services comportant

Can you creat a SSIS package and run the package without installing the SQL server intergration services?

During the SQL server installation, you can choose which component you want to install ( SQL server services, intergration services, and analysis services, etc..)..

I didn't check to install the intergration services comportant..but I can still be able to create a intergration services project thru the business intelligent studio... ( but i don;t see it under SQL server configuration maanger)

what the intergration services if for? is it seperated from SQL server services.. why i can still create a SSIS package even though i didn;t install the intergration services?

You may have installed Workstation Components which includes the designer support, but you have not installed the engine. Do your packages run properly? Probably not, see http://blogs.msdn.com/michen/archive/2006/11/11/ssis-product-level-is-insufficient.aspx

Interesting question

hi,
I am thinking if Transactional backup is based on the data in the
Transaction Log. what if i set up a the Log Reader to be run every 1 hour,
but within that 1 hour, i do a transactional backup and issue the command
"CheckPoint"!!! Can the log reader still be able to read what i have changed
before the backup?
Thanks
Ed
Ed,
transactions are only removed from the transaction log once sp_repldone has
been issued. This is called by the log reader agent, so backing up the log,
using simple recovery mode, issuing checkpoints etc won't make any
difference.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||transactions are never removed from the log otherwise doing any form of
recovery using the log would be useless. Rather they are marked as
replicated.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23X7hZXnKFHA.3512@.TK2MSFTNGP15.phx.gbl...
> Ed,
> transactions are only removed from the transaction log once sp_repldone
has
> been issued. This is called by the log reader agent, so backing up the
log,
> using simple recovery mode, issuing checkpoints etc won't make any
> difference.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Thanks for the correction! What I meant is that they can
be removed on backup of the log or truncation, although
my Englissh let me down
Rgds,
Paul
|||I thought that was what you meant, but it wasn't clear And you know me -
I am very easily confused.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:142101c52afc$b2e69bf0$a601280a@.phx.gbl...
> Thanks for the correction! What I meant is that they can
> be removed on backup of the log or truncation, although
> my Englissh let me down
> Rgds,
> Paul
>

Interdev, SQL Server 2000 vs. SQL Server 7; open/design problems

I run Visual Interdev 6... I have 2 web servers and 2 database
servers... (1 of each located in my home - A; and 1 of each co-located
at an ISP - B with our own firewall at each location).
Location A:
Firewall, port 1433 opened (for specific IP#'s)
Web Server: IIS5
SQL Server: 7
Location B:
Firewall, port 1433 opened (for specific IP#'s)
Web Server: IIS6
SQL Server: 2000
For both locations, I can edit files and open databases. For location
A, I can also DESIGN databases (create new ones, edit existing ones)
through InterDev. I can NOT do this at Location B and its driving me
nuts. While I can open databases and view the contents, I can not
create new databases through Interdev nor add fields to existing ones.
I've made sure that BUILTIN/Administrators has the same access (and I'm
an administrator on all 4 servers) on both SQL servers.
Any ideas what I am missing here? Its driving me nuts having to
terminal service into Location B's database server everytime I want to
create a new database or even a new field!
I should mention that while I'm away from home, I can still design
databases from my home servers so it isn't being "inside" a firewall
vs. "outside". I have the exact same problem no matter WHERE I am
connected from (I tend to travel alot).

Interdev, SQL Server 2000 vs. SQL Server 7; open/design problems

I run Visual Interdev 6... I have 2 web servers and 2 database
servers... (1 of each located in my home - A; and 1 of each co-located
at an ISP - B with our own firewall at each location).
Location A:
Firewall, port 1433 opened (for specific IP#'s)
Web Server: IIS5
SQL Server: 7
Location B:
Firewall, port 1433 opened (for specific IP#'s)
Web Server: IIS6
SQL Server: 2000
For both locations, I can edit files and open databases. For location
A, I can also DESIGN databases (create new ones, edit existing ones)
through InterDev. I can NOT do this at Location B and its driving me
nuts. While I can open databases and view the contents, I can not
create new databases through Interdev nor add fields to existing ones.
I've made sure that BUILTIN/Administrators has the same access (and I'm
an administrator on all 4 servers) on both SQL servers.
Any ideas what I am missing here? Its driving me nuts having to
terminal service into Location B's database server everytime I want to
create a new database or even a new field!I should mention that while I'm away from home, I can still design
databases from my home servers so it isn't being "inside" a firewall
vs. "outside". I have the exact same problem no matter WHERE I am
connected from (I tend to travel alot).

Interactively showing reports detailing results/stats of executing a package?

Hi,

I'm just starting out with SSIS and I'm currently looking to migrate some non-SSIS ETL processes to SSIS. Some of these processes are run manually depending on the results from a previous step. In these processes a user remotely executes an individual ETL program and is shown reports detailing the result of the program and various statistics about the data involved.

Is it possible to replicate this behaviour with SSIS / SSRS, or would the user have to execute a package and then manually find and bring up each of the relevant reports?

I'm new to SSIS and I'm still struggling to find out SSIS's capabilities, so I'd be really grateful if anyone could shed some light on this...

Thanks in advance,
Lawrie.
If you have one report, it is possible to stream data directly into SSRS from SSIS. In this case SSRS report uses SSIS as data source. See Books Online for detailed instructions.

Or (this works for multiple reports as well) you can simply start several IExplore.exe using Execute Process tasks and point them to URLs of appropriate SSRS reports - they are usually published to a web server, so you can view them in IE.

Integrity Checks Fail

I'm not sure what my response to this error should be.
JOB RUN: 'Integrity Checks Job for DB Maintenance Plan 'DB Maintenance
Plan2'' was run on 11/26/2006 at 00:00:03
DURATION: 0 hours, 1 minutes, 14 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 190 (Schedule 1).
The last step to run was step 1 (Step 1).
CheckDB reported
CHECKDB found 0 allocation errors and 0 consistency errors in database
'ATOMS'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
However CheckCatalog reports:
Server: Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 231723928 (object '231723928') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
(many more)
And
Table error: Object ID 423724612 (object '423724612') does not match between
'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Help appreciated.
--
Regards,
JamieYou might want to have a look at these:
http://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC/default.aspx
Andrew J. Kelly SQL MVP
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:1162D111-8C87-40B9-99FD-91BF48290290@.microsoft.com...
> I'm not sure what my response to this error should be.
> JOB RUN: 'Integrity Checks Job for DB Maintenance Plan 'DB Maintenance
> Plan2'' was run on 11/26/2006 at 00:00:03
> DURATION: 0 hours, 1 minutes, 14 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 190 (Schedule
> 1).
> The last step to run was step 1 (Step 1).
> CheckDB reported
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'ATOMS'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> However CheckCatalog reports:
> Server: Msg 2513, Level 16, State 2, Line 1
> Table error: Object ID 231723928 (object '231723928') does not match
> between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> (many more)
> And
> Table error: Object ID 423724612 (object '423724612') does not match
> between
> 'SYSINDEXES' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 1
> Help appreciated.
> --
> Regards,
> Jamie|||And a very short summary of options and recommendations:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGNhkfkFHHA.1240@.TK2MSFTNGP03.phx.gbl...
> You might want to have a look at these:
> http://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC/default.aspx
>
> --
> Andrew J. Kelly SQL MVP
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:1162D111-8C87-40B9-99FD-91BF48290290@.microsoft.com...
>> I'm not sure what my response to this error should be.
>> JOB RUN: 'Integrity Checks Job for DB Maintenance Plan 'DB Maintenance
>> Plan2'' was run on 11/26/2006 at 00:00:03
>> DURATION: 0 hours, 1 minutes, 14 seconds
>> STATUS: Failed
>> MESSAGES: The job failed. The Job was invoked by Schedule 190 (Schedule 1).
>> The last step to run was step 1 (Step 1).
>> CheckDB reported
>> CHECKDB found 0 allocation errors and 0 consistency errors in database
>> 'ATOMS'.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> However CheckCatalog reports:
>> Server: Msg 2513, Level 16, State 2, Line 1
>> Table error: Object ID 231723928 (object '231723928') does not match between
>> 'SYSCOLUMNS' and 'SYSOBJECTS'.
>> (many more)
>> And
>> Table error: Object ID 423724612 (object '423724612') does not match between
>> 'SYSINDEXES' and 'SYSOBJECTS'.
>> Server: Msg 2513, Level 16, State 1, Line 1
>> Help appreciated.
>> --
>> Regards,
>> Jamie
>

Integrity Checks Fail

I'm not sure what my response to this error should be.
JOB RUN: 'Integrity Checks Job for DB Maintenance Plan 'DB Maintenance
Plan2'' was run on 11/26/2006 at 00:00:03
DURATION: 0 hours, 1 minutes, 14 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 190 (Schedule 1).
The last step to run was step 1 (Step 1).
CheckDB reported
CHECKDB found 0 allocation errors and 0 consistency errors in database
'ATOMS'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
However CheckCatalog reports:
Server: Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 231723928 (object '231723928') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
(many more)
And
Table error: Object ID 423724612 (object '423724612') does not match between
'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Help appreciated.
Regards,
JamieYou might want to have a look at these:
http://blogs.msdn.com/sqlserverstor...CC/default.aspx
Andrew J. Kelly SQL MVP
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:1162D111-8C87-40B9-99FD-91BF48290290@.microsoft.com...
> I'm not sure what my response to this error should be.
> JOB RUN: 'Integrity Checks Job for DB Maintenance Plan 'DB Maintenance
> Plan2'' was run on 11/26/2006 at 00:00:03
> DURATION: 0 hours, 1 minutes, 14 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 190 (Schedule
> 1).
> The last step to run was step 1 (Step 1).
> CheckDB reported
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'ATOMS'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> However CheckCatalog reports:
> Server: Msg 2513, Level 16, State 2, Line 1
> Table error: Object ID 231723928 (object '231723928') does not match
> between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> (many more)
> And
> Table error: Object ID 423724612 (object '423724612') does not match
> between
> 'SYSINDEXES' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 1
> Help appreciated.
> --
> Regards,
> Jamie|||And a very short summary of options and recommendations:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGNhkfkFHHA.1240@.TK2MSFTNGP03.phx.gbl...
> You might want to have a look at these:
> [url]http://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC/default.aspx[/url
]
>
> --
> Andrew J. Kelly SQL MVP
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:1162D111-8C87-40B9-99FD-91BF48290290@.microsoft.com...
>

Integrity check failures during maintenance plans

Hi
I have maintenance plans which run weekly to check the integrity of all my
databases. I have the option 'repair minor errors' turned on and I then
recieve the following error when the maintenenace plan runs. I have placed
the database in question into single user mode and run the same task on the
database and it completes successfully. The problem is though that I can run
the integrity check with the 'repair minor errors' option turned off and it
does not pick up any errors.
Any advise on solving this issue would be great?
Check Data and Index Linkage
Error Number: 7919
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.Don't use the repair option. If you have a problem in the database, you want to know about it,
analyze why it happened, make sure you repair the faulty hardware (for example) etc. In short, that
option isn't a good idea to use in an automated job. If you still want to use that option, you
either have to add something to the job that kicks out all users, or live with that error if there
are users connected.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DeanM" <DeanM@.discussions.microsoft.com> wrote in message
news:AFC39BF6-9706-489A-83B5-BF7F7AD366CA@.microsoft.com...
> Hi
> I have maintenance plans which run weekly to check the integrity of all my
> databases. I have the option 'repair minor errors' turned on and I then
> recieve the following error when the maintenenace plan runs. I have placed
> the database in question into single user mode and run the same task on the
> database and it completes successfully. The problem is though that I can run
> the integrity check with the 'repair minor errors' option turned off and it
> does not pick up any errors.
> Any advise on solving this issue would be great?
>
> Check Data and Index Linkage
> Error Number: 7919
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.

Integrity check failures during maintenance plans

Hi
I have maintenance plans which run weekly to check the integrity of all my
databases. I have the option 'repair minor errors' turned on and I then
recieve the following error when the maintenenace plan runs. I have placed
the database in question into single user mode and run the same task on the
database and it completes successfully. The problem is though that I can ru
n
the integrity check with the 'repair minor errors' option turned off and it
does not pick up any errors.
Any advise on solving this issue would be great?
Check Data and Index Linkage
Error Number: 7919
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not
processed. Database needs to be in single user mode.Don't use the repair option. If you have a problem in the database, you want
to know about it,
analyze why it happened, make sure you repair the faulty hardware (for examp
le) etc. In short, that
option isn't a good idea to use in an automated job. If you still want to us
e that option, you
either have to add something to the job that kicks out all users, or live wi
th that error if there
are users connected.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DeanM" <DeanM@.discussions.microsoft.com> wrote in message
news:AFC39BF6-9706-489A-83B5-BF7F7AD366CA@.microsoft.com...
> Hi
> I have maintenance plans which run weekly to check the integrity of all my
> databases. I have the option 'repair minor errors' turned on and I then
> recieve the following error when the maintenenace plan runs. I have place
d
> the database in question into single user mode and run the same task on th
e
> database and it completes successfully. The problem is though that I can
run
> the integrity check with the 'repair minor errors' option turned off and i
t
> does not pick up any errors.
> Any advise on solving this issue would be great?
>
> Check Data and Index Linkage
> Error Number: 7919
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not
> processed. Database needs to be in single user mode.

Integrity check failures during maintenance plans

Hi
I have maintenance plans which run weekly to check the integrity of all my
databases. I have the option 'repair minor errors' turned on and I then
recieve the following error when the maintenenace plan runs. I have placed
the database in question into single user mode and run the same task on the
database and it completes successfully. The problem is though that I can run
the integrity check with the 'repair minor errors' option turned off and it
does not pick up any errors.
Any advise on solving this issue would be great?
Check Data and Index Linkage
Error Number: 7919
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
Don't use the repair option. If you have a problem in the database, you want to know about it,
analyze why it happened, make sure you repair the faulty hardware (for example) etc. In short, that
option isn't a good idea to use in an automated job. If you still want to use that option, you
either have to add something to the job that kicks out all users, or live with that error if there
are users connected.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DeanM" <DeanM@.discussions.microsoft.com> wrote in message
news:AFC39BF6-9706-489A-83B5-BF7F7AD366CA@.microsoft.com...
> Hi
> I have maintenance plans which run weekly to check the integrity of all my
> databases. I have the option 'repair minor errors' turned on and I then
> recieve the following error when the maintenenace plan runs. I have placed
> the database in question into single user mode and run the same task on the
> database and it completes successfully. The problem is though that I can run
> the integrity check with the 'repair minor errors' option turned off and it
> does not pick up any errors.
> Any advise on solving this issue would be great?
>
> Check Data and Index Linkage
> Error Number: 7919
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.

integrity check error and single user mode on Project Server database

When I try to run an integrity check on my project Server
2002 database, I get the following error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
[Microsoft][ODBC SQL Server Driver][SQL Server]Database
state cannot be changed while other users are using the
database 'ProjectServer'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[31] Database ProjectServer: Check Data and Index
Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
This same error comes up if I try to manually change the
database to single user mode.
Is there any way to see which users are using the
database, close their sessions and then do the integrity
check.
This is the only database on my SQL server that I have
any issues with.
Any help would be appreciated.
Regards,
Mike Walraven
..
You can use sp_who2 to see who is in a particular database but you should
not be running this in Single User mode anyway. Single User is only
required to fix issues not to do the initial checks.
Andrew J. Kelly SQL MVP
"Mike Walraven" <mwalraven@.syncroness.com> wrote in message
news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> When I try to run an integrity check on my project Server
> 2002 database, I get the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Database
> state cannot be changed while other users are using the
> database 'ProjectServer'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [31] Database ProjectServer: Check Data and Index
> Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> This same error comes up if I try to manually change the
> database to single user mode.
> Is there any way to see which users are using the
> database, close their sessions and then do the integrity
> check.
> This is the only database on my SQL server that I have
> any issues with.
> Any help would be appreciated.
> Regards,
> Mike Walraven
> .
>

integrity check error and single user mode on Project Server database

When I try to run an integrity check on my project Server
2002 database, I get the following error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
[Microsoft][ODBC SQL Server Driver][SQL Server]Database
state cannot be changed while other users are using the
database 'ProjectServer'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[31] Database ProjectServer: Check Data and Index
Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
This same error comes up if I try to manually change the
database to single user mode.
Is there any way to see which users are using the
database, close their sessions and then do the integrity
check.
This is the only database on my SQL server that I have
any issues with.
Any help would be appreciated.
Regards,
Mike Walraven
.You can use sp_who2 to see who is in a particular database but you should
not be running this in Single User mode anyway. Single User is only
required to fix issues not to do the initial checks.
--
Andrew J. Kelly SQL MVP
"Mike Walraven" <mwalraven@.syncroness.com> wrote in message
news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> When I try to run an integrity check on my project Server
> 2002 database, I get the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Database
> state cannot be changed while other users are using the
> database 'ProjectServer'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [31] Database ProjectServer: Check Data and Index
> Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> This same error comes up if I try to manually change the
> database to single user mode.
> Is there any way to see which users are using the
> database, close their sessions and then do the integrity
> check.
> This is the only database on my SQL server that I have
> any issues with.
> Any help would be appreciated.
> Regards,
> Mike Walraven
> .
>|||Thanks! I changed my integrity check to not attempt to repair any minor problems, and it doesn't require single user mode to do the check. The integrity check came back fine, so I assume it didn't need to do any repairing anyway.
Regards,
Mike Walraven
"Andrew J. Kelly" wrote:
> You can use sp_who2 to see who is in a particular database but you should
> not be running this in Single User mode anyway. Single User is only
> required to fix issues not to do the initial checks.
> --
> Andrew J. Kelly SQL MVP
>
> "Mike Walraven" <mwalraven@.syncroness.com> wrote in message
> news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> > When I try to run an integrity check on my project Server
> > 2002 database, I get the following error:
> >
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Database
> > state cannot be changed while other users are using the
> > database 'ProjectServer'
> > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> > DATABASE statement failed.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]
> > sp_dboption command failed.
> > [31] Database ProjectServer: Check Data and Index
> > Linkage...
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> > statement not processed. Database needs to be in single
> > user mode.
> >
> > This same error comes up if I try to manually change the
> > database to single user mode.
> >
> > Is there any way to see which users are using the
> > database, close their sessions and then do the integrity
> > check.
> >
> > This is the only database on my SQL server that I have
> > any issues with.
> >
> > Any help would be appreciated.
> >
> > Regards,
> > Mike Walraven
> > .
> >
> >
>
>|||That is usually the case. If there is an error you can then decide the best
approach to handle it instead of letting the wizard make those decisions.
--
Andrew J. Kelly SQL MVP
"Mike Walraven" <Mike Walraven@.discussions.microsoft.com> wrote in message
news:23F9018D-52F4-4BA4-8A23-05AD4A83D080@.microsoft.com...
> Thanks! I changed my integrity check to not attempt to repair any minor
problems, and it doesn't require single user mode to do the check. The
integrity check came back fine, so I assume it didn't need to do any
repairing anyway.
> Regards,
> Mike Walraven
> "Andrew J. Kelly" wrote:
> > You can use sp_who2 to see who is in a particular database but you
should
> > not be running this in Single User mode anyway. Single User is only
> > required to fix issues not to do the initial checks.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Mike Walraven" <mwalraven@.syncroness.com> wrote in message
> > news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> > > When I try to run an integrity check on my project Server
> > > 2002 database, I get the following error:
> > >
> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]Database
> > > state cannot be changed while other users are using the
> > > database 'ProjectServer'
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> > > DATABASE statement failed.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]
> > > sp_dboption command failed.
> > > [31] Database ProjectServer: Check Data and Index
> > > Linkage...
> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> > > statement not processed. Database needs to be in single
> > > user mode.
> > >
> > > This same error comes up if I try to manually change the
> > > database to single user mode.
> > >
> > > Is there any way to see which users are using the
> > > database, close their sessions and then do the integrity
> > > check.
> > >
> > > This is the only database on my SQL server that I have
> > > any issues with.
> > >
> > > Any help would be appreciated.
> > >
> > > Regards,
> > > Mike Walraven
> > > .
> > >
> > >
> >
> >
> >

integrity check error and single user mode on Project Server database

When I try to run an integrity check on my project Server
2002 database, I get the following error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
[Microsoft][ODBC SQL Server Driver][SQL Server]Database
state cannot be changed while other users are using the
database 'ProjectServer'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[31] Database ProjectServer: Check Data and Index
Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
This same error comes up if I try to manually change the
database to single user mode.
Is there any way to see which users are using the
database, close their sessions and then do the integrity
check.
This is the only database on my SQL server that I have
any issues with.
Any help would be appreciated.
Regards,
Mike Walraven
.You can use sp_who2 to see who is in a particular database but you should
not be running this in Single User mode anyway. Single User is only
required to fix issues not to do the initial checks.
Andrew J. Kelly SQL MVP
"Mike Walraven" <mwalraven@.syncroness.com> wrote in message
news:2bb0601c46940$d40d2070$a601280a@.phx
.gbl...
> When I try to run an integrity check on my project Server
> 2002 database, I get the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Database
> state cannot be changed while other users are using the
> database 'ProjectServer'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [31] Database ProjectServer: Check Data and Index
> Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> This same error comes up if I try to manually change the
> database to single user mode.
> Is there any way to see which users are using the
> database, close their sessions and then do the integrity
> check.
> This is the only database on my SQL server that I have
> any issues with.
> Any help would be appreciated.
> Regards,
> Mike Walraven
> .
>

Integration Services

When I try to run Data Flow to get multiple mdb files getting this error
any thoughts '
TITLE: Package Validation Error
--
Package Validation Error
ADDITIONAL INFORMATION:
Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection m
ethod
call to the connection manager "Northwind_1" failed with error code
0xC0202009.
Error at Data Flow Task [DTS.Pipeline]: component "OLE DB Source" (1) fa
iled
validation and returned error code 0xC020801C.
Error at Data Flow Task [DTS.Pipeline]: One or more component failed
validation.
Error at Data Flow Task: There were errors during task validation.
Error at ForEachLoop [Connection manager "Northwind_1"]: An OLE DB error
has
occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC
Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
Manager]
Data source name not found and no default driver specified".
(Microsoft.DataTransformationServices.VsIntegration)Gopinath,
Please see:
http://www.aspfaq.com/sql2005/show.asp?id=1
HTH
Jerry
"Gopinath" <Gopinath@.discussions.microsoft.com> wrote in message
news:628161DE-68B2-47F5-9420-897A93BB7D32@.microsoft.com...
> When I try to run Data Flow to get multiple mdb files getting this error
> any thoughts '
>
> TITLE: Package Validation Error
> --
> Package Validation Error
> --
> ADDITIONAL INFORMATION:
> Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection
method
> call to the connection manager "Northwind_1" failed with error code
> 0xC0202009.
> Error at Data Flow Task [DTS.Pipeline]: component "OLE DB Source" (1)
> failed
> validation and returned error code 0xC020801C.
> Error at Data Flow Task [DTS.Pipeline]: One or more component failed
> validation.
> Error at Data Flow Task: There were errors during task validation.
> Error at ForEachLoop [Connection manager "Northwind_1"]: An OLE DB err
or
> has
> occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft OLE DB Provider for
> ODBC
> Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driv
er
> Manager]
> Data source name not found and no default driver specified".
> (Microsoft.DataTransformationServices.VsIntegration)
>|||Hi Jerry,
Please check the URL its not have any details about my question, Am I
missing anything.
Thanks
Gopi
"Jerry Spivey" wrote:

> Gopinath,
> Please see:
> http://www.aspfaq.com/sql2005/show.asp?id=1
> HTH
> Jerry
> "Gopinath" <Gopinath@.discussions.microsoft.com> wrote in message
> news:628161DE-68B2-47F5-9420-897A93BB7D32@.microsoft.com...
>
>|||If the SSIS package doesn't validate then a likely cause is that you
have configured something incorrectly.
The first thing I would check is the OLE DB Connection Manager that
you are using.
Go over each step of the configuration and check that you set it up
correctly.
If you don't see a solution by that simple expedient I suggest that
you go to
http://forums.microsoft.com/msdn/de...ForumGroupID=19 and post
your question on the Integration Services forum.
Andrew Watt
MVP - InfoPath
On Wed, 19 Oct 2005 08:00:05 -0700, "Gopinath"
<Gopinath@.discussions.microsoft.com> wrote:

>When I try to run Data Flow to get multiple mdb files getting this error
>any thoughts '
>
>TITLE: Package Validation Error
>--
>Package Validation Error
>--
>ADDITIONAL INFORMATION:
>Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection
method
>call to the connection manager "Northwind_1" failed with error code
>0xC0202009.
>Error at Data Flow Task [DTS.Pipeline]: component "OLE DB Source" (1) f
ailed
>validation and returned error code 0xC020801C.
>Error at Data Flow Task [DTS.Pipeline]: One or more component failed
>validation.
>Error at Data Flow Task: There were errors during task validation.
>Error at ForEachLoop [Connection manager "Northwind_1"]: An OLE DB erro
r has
>occurred. Error code: 0x80004005.
>An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC
>Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Drive
r Manager]
>Data source name not found and no default driver specified".
> (Microsoft.DataTransformationServices.VsIntegration)|||Gopi,
The URL is basically indicating the *best* community to answer your SQL
Server 2005 questions. SQL Server 2005 has not yet been released and is
still in BETA. Choosing the best NG to answer your post will likely expite
an answer and will help minimize a duplication of effort when posting to a
2000 and 2005 NG.
HTH
Jerry
"Gopinath" <Gopinath@.discussions.microsoft.com> wrote in message
news:5CA99ED7-07C4-48A2-883A-046CD61F007E@.microsoft.com...[vbcol=seagreen]
> Hi Jerry,
> Please check the URL its not have any details about my question, Am I
> missing anything.
> Thanks
> Gopi
>
> "Jerry Spivey" wrote:
>