Using a variable in an execute SQL Task issue

• Dunnes2002

  Grasshopper

  Points: 20

  More actions

  September 18, 2012 at 5:42 am

  #393201

  Good afternoon i wonder if anyone can give me some guidance.

  I have a variable named weeks (int32) which is populated by a for loop (values between 657 and 686)

  I am trying to pass this variable into an sql task to extract a weeks data.

  My SQL task is using an OLE DB connection and is set to direct input and no results set.

  My parameter mapping is set to

  Variable name: user::weeks

  Direction: Input

  Data type: Long

  Parameter Name: 0

  Parameter Size:-1

  I run some very simple check to check its working:

  declare @no as int

  set @no = ?

  insert into dbo.weeknumber(weekno)

  select @no

  but it throws an error saying nulls cannot be inserted into the table. Which i take it means that @no is not being set correctly.

  I have added a break point and the variable is showing as 686 but doesn't seem to be assigning to @no correctly.

  Could anyone offer any insight to this issue? I'm using Visual Studio 2005

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  September 18, 2012 at 6:38 am

  #1539061

  Dunnes2002 (9/18/2012)

  ---

  it throws an error saying nulls cannot be inserted into the table. Which i take it means that @no is not being set correctly.

  Not necessarily. Please will you post the DDL for the table? If it contains one or more columns (other than the one specified in your INSERT statement) that are not nullable and don't have defaults set, you'll get this error. Please will you also post the exact error message?

  John

• Dunnes2002

  Grasshopper

  Points: 20

  More actions

  September 18, 2012 at 8:06 am

  #1539114

  John,

  thankyou very much for your reply, i had been looking at that loop for far too long.

  You hit the nail on the head with the table i was updating, it had 2 columns of data that didn't accept nulls. I removed the unused one and everything is working.

  Feel a little stupid now, should have checked this earlier but it seemed alot of people were having a similar issue with variables os i though that must be the issue.

  thanks again