I have two SQL servers, one is running SQLServer 7.0 (7.00.842) and the other is running SQLServer 2000 (8.00.760). I've linked the servers (on the SQL 7.0 box).
I need to copy some data from the 7.0 box to the 2000 box on a nightly basis. I wrote the query, and it works fine in QA when connected to the 7.0 box. Thinking everything was going ok, I built a DTS package with the same sql code on the same server.
Strangely, when I run the DTS package I receive an "Invalid Pointer" error. And of course, when I schedule the job to run, it also fails.
Any idea why this is happening? The only information on this "Invalid Pointer" error I can find refers to an older service pack, which is not the problem in this case.
Any pointers would be much appreciated. Thanks!
I was getting "Invalid Pointer" when trying to export results of a query (from a stored procedure) to an Excel spreadsheet.
The problem was that I had one line in the stored procedure that inserted on record in another table. Then I had set Nocount on. As a result if you ran my qurery Analyzer you would get a message in the message window.
It truns out that DTS packages do not like messages. Make sure you have Set NOCOUNT ON and SET ANSI_WARNINGS OFF.
It took me half a day to figure this out!
"Make sure you have Set NOCOUNT ON and SET ANSI_WARNINGS OFF."
After including these statements as first two rows in my stored procedure, i was finally able to access two different databases (select from DB1, insert into DB2) in the stored procedure, when called from DTS.
(SQL Server 2000)