I am trying to create a .dtsx package in BIDS. Basically a stored procedure is called and the results are written to a .csv file. We have a test server with the stored procedure on it which this package will execute fine and does not give me an error when parsing the exec (stored procedure) command. However on the live server where the same stored procedure is located when parsing the exec (stored procedure) command I get the error "the sql statement is not a query". Here are the details of the test and live server:
1. Running windows 2003 enterprise edition.
2. I am an administrator of the machine.
3. Sql Product Version 9.00.3054.00, SP2, Standard Edition
4. Using windows authentication to connect to the test sql database
1. Running windows 2003 enterprise x64 edition
2. Sql Product Version 9.00.3042.00, SP2, Enterprise Edition
3. Using sql authenticatio to connect to the live sql database
I have seen post where using temp tables in the stored procedure can cause this problem. There is no temp table in my stored procedure and why would it work on a test database and not the live one? It would seem if it did not work in SSIS then it would not work at all. Any ideas? Please advise.