Execute SQL task problem

Question

Post reply

Execute SQL task problem

ggareth

SSCommitted

Points: 1589
More actions
June 15, 2010 at 3:15 am

#223804

I have a SSIS job containing an Execute SQL task which creates 2 non-parameterized stored procedures. No result set is configured for the task.
The issue I have is as follows. Whilst the task & job run successfully against instances of SQL Server Enterprise Edition, if I run the job against SQL Server Standard Edition (or Development Edition) the job fails at the Execute SQL task.
The code within the task executes successfully in Management Studio against all editions.
The error message is below.
[Execute SQL Task] Error: ...failed with the following error:
"Incorrect syntax near '('.".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.
Any suggestions as to why it won't run for Standard/Development editions but will for Enterprise edition would be appreciated!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ggareth SSCommitted Points: 1589 More actions · Answer 1

OK, I found the issue...I was calling the DB_ID() function inside a select from a system view:

SELECT

field1,

field2

INTO

table_1

FROM

sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

Once I declared a variable @dbid = DB_ID() and referenced it in the query, it worked:

DECLARE @dbid int

SET @dbid = DB_ID()

SELECT

field1,

field2

INTO

table_1

FROM

sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, 'LIMITED')

ggareth SSCommitted Points: 1589 More actions · Answer 2

I'm still interested if anyone can shed light on why this would run on an Enterprise instance but not on a Standard or Developer instance. :unsure:

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

I was able to reproduce when database compatibility level was set to 80..

I don't think it is a SQL version difference as much as DB compatibility difference.

CEWII

ggareth SSCommitted Points: 1589 More actions · Answer 4

Ah, thanks Elliot. That would make sense in this case as we have some legacy databases at level 80 on the affected instances.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

You are very welcome. I was struggling to come up with how they could behave different since these are not limitations inherent in the different editions.

CEWII