0

I'm able to easily get sql server agent job information via the following TSQL: 

sp_help_job @job_id=null, @job_name='<My Job Name>',@job_aspect='JOB'

However, when I try to execute this system stored proc in vb.net I get a data type clash error:

cmd = New OleDb.OleDbCommand("sp_help_job", jobConnection)
cmd.CommandType = CommandType.StoredProcedure
''job id value should be set to null - we will filter on the job name...
p1 = New OleDb.OleDbParameter("@Job_id", SqlDbType.UniqueIdentifier)
p1.Direction = ParameterDirection.Input
p1.Value = vbNull
cmd.Parameters.Add(p1)
''job name paramter is the currently running interface, strCurrentPackage...
p2 = New OleDb.OleDbParameter("@job_name", SqlDbType.NVarChar)
p2.Direction = ParameterDirection.Input
p2.Value = strCurrentPackage
cmd.Parameters.Add(p2)
''job aspect value = "JOB" to get only the job info result set...
p3 = New OleDb.OleDbParameter("@job_aspect", SqlDbType.NVarChar)
p3.Direction = ParameterDirection.Input
p3.Value = "JOB"
cmd.Parameters.Add(p3)
jobReader = cmd.ExecuteReader()

The error is "Operand type clash: int is incompatible with uniqueidentifier". I'm not sure where int is coming from, my code isn't using any int data types and all the parameter data types match what the system stored procedure is expecting.

A couple notes on the code: I am using OLEDB because my connection string is passed to me as an OLEDB connection. I am also connecting to the MSDB database.

bsivel
  • 2,821
  • 5
  • 25
  • 32
  • It may be due to the fact that JobID is being parsed as an `int` datatype, and you have it defined as `uniqueidentifier` on line 4. Try changing `SqlDbType.UniqueIdentifier` to `SqlDbType.Int` – Phoenix Jun 01 '15 at 22:43
  • Based on the documentation, it should be a unique identifier: https://msdn.microsoft.com/en-us/library/ms186722.aspx. I tried setting line 4 to data type of int but this doesn't work; I get the same error. – bsivel Jun 01 '15 at 22:53
  • You're right, it should be uniqueidentifier. I wonder if it has to do with how you're passing the value. I'll keep researching. – Phoenix Jun 01 '15 at 22:55
  • I removed the job_id parameter and I got the following: "Error converting data type nvarchar to uniqueidentifier" . This makes it appear like job_id must be used even though I don't need it. Perhaps vbNull doesn't equal a database null. – bsivel Jun 01 '15 at 23:00
  • Take a look at this: http://stackoverflow.com/questions/2490514/how-to-pass-null-for-a-parameter-in-stored-procedure-the-parameter-is-uniqueid It is for C# (I am unfamiliar with C# and VB.NET). Perhaps you are not able to send a NULL across to GUID? Or maybe it needs different formatting. Something like `DbNull.Value`. So `p1.Value = DbNull.Value` – Phoenix Jun 01 '15 at 23:06
  • DbNull.Value instead of VbNull fixed it - thanks! – bsivel Jun 01 '15 at 23:14

1 Answers1

1

SqlDbType.UniqueIdentifier references a GUID (see here). You may need to reformat how you're passing your NULL value. See this link for justification.

Try using DbNull.Value on line 6, so it reads like this:

p1 = New OleDb.OleDbParameter("@Job_id", SqlDbType.Int)
p1.Direction = ParameterDirection.Input
p1.Value = DbNull.Value
cmd.Parameters.Add
Community
  • 1
  • 1
Phoenix
  • 1,881
  • 5
  • 20
  • 28