Prev: sqlcrl exception >> attn Charles Wang <<
Next: DBCC Memorystatus Results across a linked server
From: tshad on 19 Apr 2010 15:31
Is there a good tool for testing results of changes to stored procedure,
functions, views etc?

Some way to compare results of activity to the results of the same activity
after changes are made.

Not just for a particular procedure but after you have made changes to
multiple procedures that may be used by other procedures.

Then run some type of robot function that does the same set of actions that
was done on an identical database before the changes were made and then
compare the database to see what is different.

Thanks,

Tom


From: John Bell on 20 Apr 2010 15:56
On Mon, 19 Apr 2010 12:31:45 -0700, "tshad" <tfs(a)dslextreme.com>
wrote:

>Is there a good tool for testing results of changes to stored procedure,
>functions, views etc?
>
>Some way to compare results of activity to the results of the same activity
>after changes are made.
>
>Not just for a particular procedure but after you have made changes to
>multiple procedures that may be used by other procedures.
>
>Then run some type of robot function that does the same set of actions that
>was done on an identical database before the changes were made and then
>compare the database to see what is different.
>
>Thanks,
>
>Tom
>
Hi Tom

It depends what you mean by results! There are various database
compare tools available including that Visual Studio Database Pro
edition that have some level of database compare functionality.

If you want to compare output, then you could run profiler to capture
the procedure calls and save the T-SQL. Then run the procedures before
and after the procedures have changed and compare the results. If you
change the procedure parameters then it may cause problems with this
procedure and the data may not be returned in the same order.

John
From: tshad on 20 Apr 2010 17:57

"John Bell" <jbellnewsposts(a)hotmail.com> wrote in message
news:f61ss5hf8bi6oampnp94clk3s1c0jafj7p(a)4ax.com...
> On Mon, 19 Apr 2010 12:31:45 -0700, "tshad" <tfs(a)dslextreme.com>
> wrote:
>
>>Is there a good tool for testing results of changes to stored procedure,
>>functions, views etc?
>>
>>Some way to compare results of activity to the results of the same
>>activity
>>after changes are made.
>>
>>Not just for a particular procedure but after you have made changes to
>>multiple procedures that may be used by other procedures.
>>
>>Then run some type of robot function that does the same set of actions
>>that
>>was done on an identical database before the changes were made and then
>>compare the database to see what is different.
>>
>>Thanks,
>>
>>Tom
>>
> Hi Tom
>
> It depends what you mean by results! There are various database
> compare tools available including that Visual Studio Database Pro
> edition that have some level of database compare functionality.
>

We could also use RedGates SqlDataCompare (not sure which is better)

But the problem we ran into is that we are changing/refactoring many stored
procedures but have found some issues in the applications because of changes
that looked correct but obviously weren't.
We wanted to see if there was some type of automated way to check to see if
we are getting the same results.

One of the ideas I had was to use triggers to write data to a table and look
at the results between the database before the change and after.

The problem with using something that checks the data after the fact is that
you can't see what caused the difference, just that is was different.

> If you want to compare output, then you could run profiler to capture
> the procedure calls and save the T-SQL. Then run the procedures before
> and after the procedures have changed and compare the results. If you
> change the procedure parameters then it may cause problems with this
> procedure and the data may not be returned in the same order.
>
That would work as well and was something I had thought about. And had the
same issue about changes in parameters. But it also wouldn't show
differences in results if it were just passing back query results that the
application then uses where no tables are actually updated.

Thanks,

Tom

> John


 | 
Pages: 1
Prev: sqlcrl exception >> attn Charles Wang <<
Next: DBCC Memorystatus Results across a linked server