link from sql2005 (64-bit) to sql 7 (32-bit).

Hi: I'm trying to link from sql2005 (64-bit) SP1 to sql 7 (32-bit) SP4.
I get the following error:
OLE DB provider "SQLNCLI" for linked server "sql7" returned message
"Unspecified error".
OLE DB provider "SQLNCLI" for linked server "sql7" returned message "The
stored procedure required to complete this operation could not be found on
the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
"SQLNCLI" for linked server "sql7". The provider supports the interface, but
returns a failure code when it is used.
How can I get these servers linked?
Regards,I just ran into a similar problem, but from 64 bit SQL 2005 trying to link
to 32 bit SQL 2000. What I did on the 2000 server was run the instcat.sql
from the latest service pack installation I did on the 2000 server. In my
case it was SP3a. There are instructions to change the last insert statement
to the table to reflect the version of SQL Server (select @.@.version) that
you are running. In my case it was .977. That fixed my problems.
Jackie
"José" <josedc@.starmedia.com> wrote in message
news:OE2iT1GlGHA.3720@.TK2MSFTNGP03.phx.gbl...
> Hi: I'm trying to link from sql2005 (64-bit) SP1 to sql 7 (32-bit) SP4.
> I get the following error:
> OLE DB provider "SQLNCLI" for linked server "sql7" returned message
> "Unspecified error".
> OLE DB provider "SQLNCLI" for linked server "sql7" returned message "The
> stored procedure required to complete this operation could not be found on
> the server. Please contact your system administrator.".
> Msg 7311, Level 16, State 2, Line 1
> Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
> "SQLNCLI" for linked server "sql7". The provider supports the interface,
> but
> returns a failure code when it is used.
> How can I get these servers linked?
> Regards,
>sql

Limiting log growth during DTS Package

SQL Server 2000 SP4. I built a large DTS package that grabs a number
of tables from an Oracle DB, does some scrubbing and date verification
and loads to a SQL Server DB. Most of the tables are full refresh and
a few are incremental.

Main DW: DwSQL
Staging Area: DwLoadAreaSQL

The DW is about 60 Gigs. The Staging Area is about 80 Gigs. This is
all good.

However, the log file for the staging area is 50 Gigs and I'm trying
to find ways to not require such a large log file. I tried adding a
few "BACKUP LOG DwLoadAreaSQL WITH TRUNCATE_ONLY" statements in the
DTS package but figured out that because it's 1 DTS package it's all 1
transaction. I've thought about breaking it up into multiple DTS
packages and truncating the log between running them but was hoping to
avoid this. To be clear, I know how to shrink DB's and Log
Files...that's not the issue.

Any Ideas? Thanks.On Jul 24, 3:49 pm, davisutt <davis...@.aol.comwrote:

Quote:

Originally Posted by

SQL Server 2000 SP4. I built a large DTS package that grabs a number
of tables from an Oracle DB, does some scrubbing and date verification
and loads to a SQL Server DB. Most of the tables are full refresh and
a few are incremental.
>
Main DW: DwSQL
Staging Area: DwLoadAreaSQL
>
The DW is about 60 Gigs. The Staging Area is about 80 Gigs. This is
all good.
>
However, the log file for the staging area is 50 Gigs and I'm trying
to find ways to not require such a large log file. I tried adding a
few "BACKUP LOG DwLoadAreaSQL WITH TRUNCATE_ONLY" statements in the
DTS package but figured out that because it's 1 DTS package it's all 1
transaction. I've thought about breaking it up into multiple DTS
packages and truncating the log between running them but was hoping to
avoid this. To be clear, I know how to shrink DB's and Log
Files...that's not the issue.
>
Any Ideas? Thanks.

Make sure both databases are in bulked log recovery mode. You should
have a step at the end of your DTS to run CHECKPOINT, backup truncate
the log. Also, manaually shrink the log file to your desired log size.

Hope it helps...

MNDBA|||On Jul 28, 4:55 pm, kmounkh...@.gmail.com wrote:

Quote:

Originally Posted by

On Jul 24, 3:49 pm, davisutt <davis...@.aol.comwrote:
>
>
>
>
>

Quote:

Originally Posted by

SQL Server 2000 SP4. I built a large DTS package that grabs a number
of tables from an Oracle DB, does some scrubbing and date verification
and loads to a SQL Server DB. Most of the tables are full refresh and
a few are incremental.

>

Quote:

Originally Posted by

Main DW: DwSQL
Staging Area: DwLoadAreaSQL

>

Quote:

Originally Posted by

The DW is about 60 Gigs. The Staging Area is about 80 Gigs. This is
all good.

>

Quote:

Originally Posted by

However, the log file for the staging area is 50 Gigs and I'm trying
to find ways to not require such a large log file. I tried adding a
few "BACKUP LOG DwLoadAreaSQL WITH TRUNCATE_ONLY" statements in the
DTS package but figured out that because it's 1 DTS package it's all 1
transaction. I've thought about breaking it up into multiple DTS
packages and truncating the log between running them but was hoping to
avoid this. To be clear, I know how to shrink DB's and Log
Files...that's not the issue.

>

Quote:

Originally Posted by

Any Ideas? Thanks.

>
Make sure both databases are in bulked log recovery mode. You should
have a step at the end of your DTS to run CHECKPOINT, backup truncate
the log. Also, manaually shrink the log file to your desired log size.
>
Hope it helps...
>
MNDBA- Hide quoted text -
>
- Show quoted text -

Thanks. I think that's what I was looking for. I changed the
recovery mode and shrunk the log size to about 20% of what it was.
I'll run the process and see what kind of growth occurs.