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 Connection Timeouts

I'm having an issue with what appears to be SQL Server 2005 deciding to randomly ignore new connections.

I currently have two virtual servers - one running just SQL Server 2005, the other running Reporting Services, Windows Sharepoint Services and Team Foundation Server.

For 3 weeks, it was all working perfectly, then on Wednesday night the server (and both Virtual Servers) was rebooted after installing the latest updates for Windows. Since then, I've had this issue.

It will work fine for a while, then it'll start throwing loads of Errors and Warnings into the Event Log, all along the lines of unable to connect to the database. The Reporting Services Configuration utility throws up the same problem. Then randomly, it'll start working again.

If anyone has any ideas, they would be much appreciated as this is driving me crazy!

Thanks!

How are users connecting to the database? If they are connecting through a webfarm, I've had similar troubles and might be able to help.
Tim|||

Hi Tim,

I have an ASP.NET 2.0 website connected to a SQL Server 2005 database hosted by a 3rd party that has been running without problems. In the last week we are experiencing intermittent connection problems with 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'

Any ideas on what might be causing this?

Regards Andy

sql

interesting question

Hi All,
When I open Enterprise manager on a few of my servers,
everything is doubled.
When I open the server in EM I see 2 folders for
databases,data transformation services,management,...
Does anyone have any idea why this would be?
I have 25 different servers that I check thru my EM and
only a few of them do this.
Thanks,
JoeDid you check if there's any version or build number difference between the
servers that do this and those who
do not?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Joe" <anonymous@.discussions.microsoft.com> wrote in message news:13cf501c4132a$94a67e70$a5
01280a@.phx.gbl...
> Hi All,
> When I open Enterprise manager on a few of my servers,
> everything is doubled.
> When I open the server in EM I see 2 folders for
> databases,data transformation services,management,...
> Does anyone have any idea why this would be?
> I have 25 different servers that I check thru my EM and
> only a few of them do this.
> Thanks,
> Joe
>

interesting error message

Hi,
I keep geeting this error message in one of my servers.
Replication: agent failed.: Unable to expand message 17055 [-1073724769]
18265 Log backed up: Database: mine, creation date(time):
2004/08/11(14:41:52), first LSN: 10:14151:1, last LSN: 10:14151:1, number of
dump devices: 1, device informa... ..
There is no replication configured on the server. The backup job is also
successful. What is this message about?
I don't know, but if it's really from August 11 of last year, are you sure
it's still relevant?
On 3/2/05 8:45 PM, in article
545CADDF-C5EF-48A2-967F-A4246D13D306@.microsoft.com, "Bharath"
<Bharath@.discussions.microsoft.com> wrote:

> Hi,
> I keep geeting this error message in one of my servers.
> Replication: agent failed.: Unable to expand message 17055 [-1073724769]
> 18265 Log backed up: Database: mine, creation date(time):
> 2004/08/11(14:41:52), first LSN: 10:14151:1, last LSN: 10:14151:1, number of
> dump devices: 1, device informa... ..
>
> There is no replication configured on the server. The backup job is also
> successful. What is this message about?
|||Aaron,
It is relevant - I think the message is of Aug 11 - because the db was
created on Aug 11th 2004 - but it is a current message only.
"Aaron [SQL Server MVP]" wrote:

> I don't know, but if it's really from August 11 of last year, are you sure
> it's still relevant?
>
> On 3/2/05 8:45 PM, in article
> 545CADDF-C5EF-48A2-967F-A4246D13D306@.microsoft.com, "Bharath"
> <Bharath@.discussions.microsoft.com> wrote:
>
>

interesting error message

Hi,
I keep geeting this error message in one of my servers.
Replication: agent failed.: Unable to expand message 17055 [-1073724769]
18265 Log backed up: Database: mine, creation date(time):
2004/08/11(14:41:52), first LSN: 10:14151:1, last LSN: 10:14151:1, number of
dump devices: 1, device informa... ..
There is no replication configured on the server. The backup job is also
successful. What is this message about?I don't know, but if it's really from August 11 of last year, are you sure
it's still relevant?
On 3/2/05 8:45 PM, in article
545CADDF-C5EF-48A2-967F-A4246D13D306@.microsoft.com, "Bharath"
<Bharath@.discussions.microsoft.com> wrote:
> Hi,
> I keep geeting this error message in one of my servers.
> Replication: agent failed.: Unable to expand message 17055 [-1073724769]
> 18265 Log backed up: Database: mine, creation date(time):
> 2004/08/11(14:41:52), first LSN: 10:14151:1, last LSN: 10:14151:1, number of
> dump devices: 1, device informa... ..
>
> There is no replication configured on the server. The backup job is also
> successful. What is this message about?|||Aaron,
It is relevant - I think the message is of Aug 11 - because the db was
created on Aug 11th 2004 - but it is a current message only.
"Aaron [SQL Server MVP]" wrote:
> I don't know, but if it's really from August 11 of last year, are you sure
> it's still relevant?
>
> On 3/2/05 8:45 PM, in article
> 545CADDF-C5EF-48A2-967F-A4246D13D306@.microsoft.com, "Bharath"
> <Bharath@.discussions.microsoft.com> wrote:
> > Hi,
> >
> > I keep geeting this error message in one of my servers.
> >
> > Replication: agent failed.: Unable to expand message 17055 [-1073724769]
> > 18265 Log backed up: Database: mine, creation date(time):
> > 2004/08/11(14:41:52), first LSN: 10:14151:1, last LSN: 10:14151:1, number of
> > dump devices: 1, device informa... ..
> >
> >
> > There is no replication configured on the server. The backup job is also
> > successful. What is this message about?
>

interesting error message

Hi,
I keep geeting this error message in one of my servers.
Replication: agent failed.: Unable to expand message 17055 [-1073724769]
18265 Log backed up: Database: mine, creation date(time):
2004/08/11(14:41:52), first LSN: 10:14151:1, last LSN: 10:14151:1, number of
dump devices: 1, device informa... ..
There is no replication configured on the server. The backup job is also
successful. What is this message about?I don't know, but if it's really from August 11 of last year, are you sure
it's still relevant?
On 3/2/05 8:45 PM, in article
545CADDF-C5EF-48A2-967F-A4246D13D306@.microsoft.com, "Bharath"
<Bharath@.discussions.microsoft.com> wrote:

> Hi,
> I keep geeting this error message in one of my servers.
> Replication: agent failed.: Unable to expand message 17055 [-107372476
9]
> 18265 Log backed up: Database: mine, creation date(time):
> 2004/08/11(14:41:52), first LSN: 10:14151:1, last LSN: 10:14151:1, number
of
> dump devices: 1, device informa... ..
>
> There is no replication configured on the server. The backup job is also
> successful. What is this message about?|||Aaron,
It is relevant - I think the message is of Aug 11 - because the db was
created on Aug 11th 2004 - but it is a current message only.
"Aaron [SQL Server MVP]" wrote:

> I don't know, but if it's really from August 11 of last year, are you sure
> it's still relevant?
>
> On 3/2/05 8:45 PM, in article
> 545CADDF-C5EF-48A2-967F-A4246D13D306@.microsoft.com, "Bharath"
> <Bharath@.discussions.microsoft.com> wrote:
>
>

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).

Integrity Checks job causing SQL 7 database to enter single-user mode

Hi,
I've encountered with very strange problem on this issue on one of my my SQL 7 Servers.
The integrity checks job on the database failed, and caused the database to enter the single user mode, thus preventing applications to access the database. I had to manually switch the database to normal mode to repair the problem.
Right now, every time i try to run the integrity checks job, it fails and causes the database to enter the single user mode again.
Any ideas how to solve the problem?
Thanks in advance,
BarakThis looks like a known issue. Have you seen this?
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B259551
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Barak Turovsky" <barak.turovsky@.comverse.com> wrote in message
news:7DA294DF-065F-431A-91D6-AA23B4AA50CE@.microsoft.com...
Hi,
I've encountered with very strange problem on this issue on one of my my SQL
7 Servers.
The integrity checks job on the database failed, and caused the database to
enter the single user mode, thus preventing applications to access the
database. I had to manually switch the database to normal mode to repair the
problem.
Right now, every time i try to run the integrity checks job, it fails and
causes the database to enter the single user mode again.
Any ideas how to solve the problem?
Thanks in advance,
Barak

Integrity check on selected tables

We do a general DB integrity check weekly through a DB maintenance plan on our SQL 2000 S.E. servers. I'd like to do a nightly integrity check on just a few tables on very large databases. The DB Maintenance Plan Wizard does not appear to allow this.

What T-SQL can be used to accomplish this?
Does Enterprise Manager offer a way to do this?See DBCC CHECKTABLE in BOL|||[See DBCC CHECKTABLE in BOL [/SIZE][/QUOTE]

I have checked into this in the past. However, it doesn't appear to allow me to list a group of tables to check. I get a parameter incorrect for this statement.

I'd like to do something where I list several tables. Or, all tables between the letters A and D. In this manner, I may be able to run 7 days of maintenace weekly but do integrity checking piecemeal.|||Originally posted by Fulvio Hayes
We do a general DB integrity check weekly through a DB maintenance plan on our SQL 2000 S.E. servers. I'd like to do a nightly integrity check on just a few tables on very large databases. The DB Maintenance Plan Wizard does not appear to allow this.

What T-SQL can be used to accomplish this?
Does Enterprise Manager offer a way to do this?

You should be able to to create a maint plan that just does integrity checks. On my SQL7 I can go through and click off the backup portions and just turn on the nightly integrity checks.|||Originally posted by Fulvio Hayes
[See DBCC CHECKTABLE in BOL

I have checked into this in the past. However, it doesn't appear to allow me to list a group of tables to check. I get a parameter incorrect for this statement.

I'd like to do something where I list several tables. Or, all tables between the letters A and D. In this manner, I may be able to run 7 days of maintenace weekly but do integrity checking piecemeal. [/SIZE][/QUOTE]

Create sp:

dbcc checktable for table from list (get list from table will be better)

dbcc checktable 'tableA'
dbcc checktable 'tableB'
.................
Also, you can save results of checking in table or return as recordset.

insert #tmp
dbcc checktable 'tableA'
insert #tmp
dbcc checktable 'tableB'

select * from #tmp|||Load a list of tables into a cursor dataset, and then loop through the set to execute your DBCC.

If you store the name of the last table completed, you can start with the next table the following night. You could even define a processing period by setting your code to exit the loop after a certain number of minutes, or at a specified hour.

blindman|||Originally posted by blindman [/i]
Load a list of tables into a cursor dataset, and then loop through the set to execute your DBCC.

If you store the name of the last table completed, you can start with the next table the following night. You could even define a processing period by setting your code to exit the loop after a certain number of minutes, or at a specified hour.

blindman

Thanks! I'll try that. In some cases, I may use 'snails' recommendation to use checktable repeatedly for a small number of recurring tables. But for my larger, high I/O databases I'll look to going the route of the cursor dataset you recommend. I'll let youknow how it works.

Fulvio

Integration Services Considerations

We have about 150 SQL servers and basically we're considering the pros and cons of installing SSIS on a central SSIS server - that is responsible for all DTS jobs - as opposed to installing SSIS on the local SQL instance.

On the plus side so far:

1./ Central administration, alerting, change management etc

2./ Possible performance gain on the local instance not having SSIS installed?

On the negative side:

1./ Central point of failure

2./ Possibility that it would need to be a clustered...

3./ Compatibility issues may mean having to make the central SSIS server 32-bit?

4./ Possible performance cost of remote SSIS?

5./ With multiple DTS packages running at different times, when would we take the server down for maintenace...?

Would appreciate your thoughts.

First, you presented us a root whitout leafs, in other terms what are data transforming/changing with these 150 SQL Servers ?

SSIS server is used to run packages and, let's say you have 150 packages to run, can the central server resolve this workload ?

Depends on business logic i should build a SSIS grid with 10-15 nodes that can run the packages and haave many point of failures (not single).

To the other part, moving data from a SQL Servers network (that is homogenous and i guess it don't need data cleaning/transforming) to another can be made using replication or service broker.

I guess you have to build a DataWarehouse that centralize data from 150 SQL Servers. That is made obviously nightly when the people (OLTP applications) sleep so the SSIS operations can't affect performance.

integration of 2 SQL 2005 DB Servers

Hi,

I have 2 database servers and each has same database.Program that inserts these databases randomly selects one of them to insert data.So only difference is data.

I have to generate some report using these 2 sources.System administrators reject integrating these 2 servers into one.

My question is , I want to run a query that looks the column x in table y and finds unique records in this table.But this works just in one DB.What do you recommend me to find solution that finds real unique records, i.e. looks both servers to find records.

Is there a Microsoft product that may be configured brain of different DB Servers or any other solution?

I have also memory restrictions and data is considered as more than 100 GBytes for each server.

Answers will be highly appreciated.

Thanks.

sysdamins' guys also denied you create links between these servers?|||No, link is allowed.