SQL 2005: Return a value to a given app when stored proc is accessed by app

[Connoisseur]

I kinda have an urgent request as I need to make a quick fix for an app. Basically, one application accesses the SQL back end for another application. Before doing this, the app does a simple versioning check by running a stored proc in the SQL back end which only returns a version number. If the version (which is hard-coded in the first app) is supported by the first app, then it runs without issue. Otherwise, it returns an error that the version isn't supported. The issue is that we upgraded versions on the second app. Although the back end schema did not change at all, the version stored proc returns an unsupported version. Until the new version number can be hardcoded into the first app, is there any way to tell the stored proc to return a new value IF and ONLY IF it is accessed by the first app. Basically:
1. App 1 Accesses Stored Proc
2. Stored Proc Logic:
If (being accessed by app 1)
BEGIN
RETURN(X)
ELSE
RETURN(Stored Proc)
END

I hope the example clarifies the issue. This is a temporary fix until we can get the developers to change the hard-coded version in App1. Any help would be greatly appreciated.

 

[MrChad]

LOL, reminds me of this: http://thedailywtf.com/Article...-I-Say-is-Calling.aspx

Do both apps use the same user id? If not, you could set up two stored procedures and direct each id to the appropriate version.

 

[Connoisseur]

Originally posted by: MrChad
LOL, reminds me of this: http://thedailywtf.com/Article...-I-Say-is-Calling.aspx

Do both apps use the same user id? If not, you could set up two stored procedures and direct each id to the appropriate version.

That's more or less exactly what I'm looking for except with reverse logic. is p_program_name an actual system stored proc?

When I run profiler and run App1 all it says in the ApplicationName field is ".Net SqlClient Data Provider". My assumption is that this is a generic portal for all programs to access the SQL server. Would this be an issue when trying to detect the app?

 

[KB]

All .Net apps that use ADO.Net will show themselves as ".Net SqlClient Data Provider" unless you set an application name in the connection string:

connectionString="Data Source=server;Initial Catalog=Northwind;Integrated Security=True;Application Name=App1"

Then you can do:

If APP_NAME() = 'App1'
BEGIN
RETURN(X)
ELSE
RETURN(Stored Proc)
END

 

[KLin]

Originally posted by: KB
All .Net apps that use ADO.Net will show themselves as ".Net SqlClient Data Provider" unless you set an application name in the connection string:

connectionString="Data Source=server;Initial Catalog=Northwind;Integrated Security=True;Application Name=App1"

Then you can do:

If APP_NAME() = 'App1'
BEGIN
RETURN(X)
ELSE
RETURN(Stored Proc)
END

Thanks for that bit of info about including the app name in the connection string. That'll help troubleshooting in the future for any .net apps I may or may not support. :thumbsup: