- 论坛徽章:
- 0
|
We have our main SQL server (SQL2000 SP4) with over a 1000 customer databases. Each database has 2 important tables that we wish to backup as one. We used to copy all of the data into a single ALLCUSTS database on the original server every evening, but this got rather large. (around 14 million records in one table and 23 million in another and counting).
We now have another server (SQL2000 SP4) which we wish to use to hold the ALLCUSTS tables.
This is run as a job every evening with the tables truncated then this query is run.
CODE
DECLARE @Database VARCHAR(100), @SQL VARCHAR(1000)
DECLARE CURS CURSOR FOR SELECT C.CustID FROM NTSERVER4.CustInfo.dbo.CUSTOMER C JOIN NTSERVER4.master.dbo.sysdatabases D ON C.CustID=D.name ORDER BY C.CustID
OPEN CURS
FETCH CURS INTO @Database
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL=\'INSERT INTO ALLCUSTS..STORAGE SELECT \'\'\'+@Database+\'\'\', * FROM NTSERVER4.\'+@Database+\'.dbo.STORAGE\'
EXEC(@SQL)
FETCH CURS INTO @Database
END
CLOSE CURS
DEALLOCATE CURS
We have a similar job to collect the other tables data run at the same time
The job history shows that it fails with [SQLSTATE 42000] (Error 11)Check your network documentation.
Sometimes it fails on one job, other times on the other, occaisionaly on both, and sometimes they both work OK. Arrrggghh!
I have Googled this error but I can not find an exact refernce to it. Both the servers are on the same subnet, the first is running 2000 and the second is 2003.
Any help would be appreciated |
|
免费注册
发表于 2007-01-31 08:29
