Has anyone noticed this -

If you use a versioned SP with some input parameters in the data transformation task of DTS on SQL Server 2005, you'll get an error while executing the package. The same DTS runs perfectly on SQL Server 2000.

I hope everyone is familiar with versioning of a stored procedure by suffixing a semicolon and version number e.g. WP_GetDespatches;2



See the attached image showing the data transformation task properties. SP named WP_GetDespatches (version 2 having 2 parameters) is being called here. For some reason SQL Server 2005 finds the syntax invalid. Looks like its unhappy to see both ; and ? together whereas SQL Server 2000 has no problem with this.

As a workaround I had to create a new stored procedure called WP_GetDespatches2 and had to use that instead like this -
exec WP_GetDespatches2 ?,?

Please share your ideas if you ever encountered this bug.

I've seen that problem, but it was fixed early in 2007. You need to apply service packs to both the DTS runtime and the SQL Server if you're still having that problem.

-PatP

Thanks Pat Phelan for your post.

We did apply SP3 on SQL Server 2005 but it didn't help. Which patch you applied on DTS runtime to make it work?