We help IT Professionals succeed at work.

The Sql Statement is not Valid

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:

Test
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

Live
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.
Comment
Watch Question

32-bit/64-bit data source and data destinations often don't mix well; I'd start by looking there; the oledb drivers are different, one from the other, and unless you've updated your package, one won't work when run (locally) on the other.  
It would also help if you would supply the SQL statement. ;-)

Author

Commented:
Attached is the code.  It looks like I am able to create the package now.  I thought it was the sql native client driver versus the sql odbc driver but it is working either way now.  The only thing that changed is that I gave the dbo alter procedure rights in addition to execute rights.  Not sure why this would have made a difference.
IF DATEPART(WEEKDAY, GETDATE()) in ('5', '6')
select orgcode, modality,lastname,firstname,middlename, phonenumber, min(scheduleddttm)as 'ScheduledDTTM' from
(SELECT vusrExamForm.OrgCode, vusrExamForm.ScheduledDTTM, vusrExamForm.Modality, vusrExamForm.LastName, vusrExamForm.FirstName, vusrExamForm.MiddleName, 
'PhoneNumber' = 
CASE
      WHEN len(vusrPatientAddress.PhoneLast4) = 3 THEN (cast(vusrPatientAddress.PhoneArea as varchar(5))) +  (cast(vusrPatientAddress.PhoneExchange as varchar(5))) + '0' + (cast(vusrPatientAddress.PhoneLast4 as varchar(5)))
      ELSE (cast(vusrPatientAddress.PhoneArea as varchar(5))) +  (cast(vusrPatientAddress.PhoneExchange as varchar(5))) + (cast(vusrPatientAddress.PhoneLast4 as varchar(5)))
END
 FROM   IDXrad.dbo.vusrExamForm vusrExamForm (NOLOCK) INNER JOIN vusrPatientAddress (NOLOCK) ON vusrExamForm.PatientID = vusrPatientAddress.PatientID
AND vusrpatientaddress.addresstype = 'HO'
where (vusrExamForm.ScheduledDTTM >= cast(convert(varchar(8),getdate()+4,1) as datetime) and vusrExamForm.ScheduledDTTM < cast(convert(varchar(8),getdate()+5,1) as datetime)) AND vusrExamForm.Modality <> 'CR' AND ((vusrPatientAddress.PhoneArea + vusrPatientAddress.PhoneExchange + vusrPatientAddress.PhoneLast4) <> 0))
as temprisdb 
group by orgcode, modality,lastname,firstname,middlename, phonenumber

Else If DATEPART(WEEKDAY, GETDATE()) in ('2', '3', '4')
select orgcode, modality,lastname,firstname,middlename, phonenumber, min(scheduleddttm)as 'ScheduledDTTM' from
(SELECT vusrExamForm.OrgCode, vusrExamForm.ScheduledDTTM, vusrExamForm.Modality, vusrExamForm.LastName, vusrExamForm.FirstName, vusrExamForm.MiddleName,
'PhoneNumber' = 
CASE
      WHEN len(vusrPatientAddress.PhoneLast4) = 3 THEN (cast(vusrPatientAddress.PhoneArea as varchar(5))) +  (cast(vusrPatientAddress.PhoneExchange as varchar(5))) + '0' + (cast(vusrPatientAddress.PhoneLast4 as varchar(5)))
      ELSE (cast(vusrPatientAddress.PhoneArea as varchar(5))) +  (cast(vusrPatientAddress.PhoneExchange as varchar(5))) + (cast(vusrPatientAddress.PhoneLast4 as varchar(5)))
END
 FROM   IDXrad.dbo.vusrExamForm vusrExamForm (NOLOCK) INNER JOIN vusrPatientAddress (NOLOCK) ON vusrExamForm.PatientID = vusrPatientAddress.PatientID
AND vusrpatientaddress.addresstype = 'HO'
where (vusrExamForm.ScheduledDTTM >= cast(convert(varchar(8),getdate()+2,1) as datetime) and vusrExamForm.ScheduledDTTM < cast(convert(varchar(8),getdate()+3,1) as datetime)) AND vusrExamForm.Modality <> 'CR' AND ((vusrPatientAddress.PhoneArea + vusrPatientAddress.PhoneExchange + vusrPatientAddress.PhoneLast4) <> 0))
as temprisdb 
group by orgcode, modality,lastname,firstname,middlename, phonenumber

Open in new window

You might try explicitly referencing the local DB Table name on your inner join -- sometimes permissions get in the way

so instead of :
FROM   IDXrad.dbo.vusrExamForm vusrExamForm (NOLOCK)
            INNER JOIN vusrPatientAddress

use

FROM   IDXrad.dbo.vusrExamForm vusrExamForm (NOLOCK)
            INNER JOIN DBNAME.dbo.vusrPatientAddress