0

I have a stored procedure running on SQL Server 2008 that returns a list of all procedures and their parameters:

procedure [dbo].[p_ListProcedures]
as

select
  sp.name as 'Procedure',
  p.name as 'Parameter',
  t.name as 'Type',
  p.is_output as 'Output',
  dbo.GetParamDefault(p.name, object_definition(sp.object_id)) as 'Value'
from sys.procedures sp
left join sys.parameters p
  on p.object_id=sp.object_id
left join sys.types t
  on t.user_type_id=p.user_type_id
where sp.name like 'p_%'
order by sp.name

This procedure is ran on two different databases and is used to generate some ADO.NET code for us. It runs fine on one database, but on the other database, the last column Value always returns the value of (what seems to be) the first row.

For this output:

  1. x, x, x, x, NULL
  2. x, x, x, x, NULL
  3. x, x, x, x, NULL
  4. x, x, x, x, NULL
  5. x, x, x, x, 'test'
  6. x, x, x, x, 'test'
  7. x, x, x, x, NULL
  8. x, x, x, x, NULL

Reading the values of the last column through a SqlDataReader (reader["Value"]) will always return null when it should be retuning 'test' for rows 5 and 6.

I copied both databases to a local SQL Server Express 2012 install and the tool ran fine. So there is some peculiarity with that one database on the remote server that I just can't figure out.

Any ideas on what might be causing this to happen?

Chris Cartwright
  • 1
  • Make sure the stored procedure definitions are the same on both servers, after that it's loony tunes explanations, Like different versions of .net, sql server, collations etc. – Tony Hopkinson May 24 '13 at 15:58
  • All code was running on my local host and would connect remotely to the SQL server, so same version of .NET for all 4 runs. Both remote databases were running on the same instance to SQL Server. – Chris Cartwright May 25 '13 at 17:31
  • That still leaves a lot of wiggle room. Are the database schemas the same as well? If so, I'm clueless. :( – Tony Hopkinson May 25 '13 at 18:47

0 Answers0