[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need to query an SP to determine its version number

Posted on 2014-07-16
6
Medium Priority
?
161 Views
Last Modified: 2014-07-17
Hi
I have a StoredProc which returns data to a sheet in an Excel file in a library on a Sharepoint.
I would like some method for the Excel file to determine if it is running the latest version of the StoredProc, the purpose of this is for when a user downloads a copy of the StoredProc to their local machine, so it may be the case that at some time they may not be using the latest version of the file since they are not accessing it from the Sharepoint.

As a side note: When changes are made to the StoredProc (eg; if a column is added in the middle of the existing columns), for some reason the column is added at the end of the returned Excel table as opposed to where the StoredProc advises where it should be.
If I remove the query and re-add it to the Excel, then the correct column order is preserved.

Anyway, I need the StoredProc to be updated in some way that when it executes with a certain flag/parameter set (eg; @VerInfo) then it returns a line of text advising the Version Info of the StoredProc.
Just not too sure of the best method to do this or whether you guys have some better suggestions.
Thanks
Fergal.
0
Comment
Question by:fjkilken
6 Comments
 
LVL 9

Assisted Solution

by:armchair_scouse
armchair_scouse earned 1000 total points
ID: 40199115
Hi Fergal,

You could keep a version number just as a variable in your stored procedure, e.g. @SP_VERSION_NO, and add some code that checks your @VerInfo parameter; if the @VerInfo parameter is set, then return your @SP_VERSION_NO variable.

You could also keep a table of your stored procedures and the up-to-date version numbers in a table, and then compare the version number in your stored procedure with the value in the table, and report back if the user is not using to most up-to-date version.

You could also potentially use extended properties, though I have no experience of these:  however, this link might be worth a look: Managing Extended Properties.

Hope this helps.
0
 
LVL 36

Expert Comment

by:ste5an
ID: 40199142
when a user downloads a copy of the StoredProc to their local machine

Your users are running their own SQL Servers? What does "download a procedure" mean exactly?

When changes are made to the StoredProc (eg; if a column is added in the middle of the existing columns), for some reason the column is added at the end of the returned Excel table as opposed to where the StoredProc advises where it should be.  If I remove the query and re-add it to the Excel, then the correct column order is preserved.

D'oh? Sounds like your using the asterisk where you shouldn't.

Anyway, I need the StoredProc to be updated in some way that when it executes with a certain flag/parameter set (eg; @VerInfo) then it returns a line of text advising the Version Info of the StoredProc. Just not too sure of the best method to do this or whether you guys have some better suggestions.

It should not be possible to "download" it in the first place.. How do you handle dependcies to other database objects? It sounds like you need a more general deployment scenario.
0
 

Author Comment

by:fjkilken
ID: 40199255
hi ste5an
Apologies - I had mean to type "when a user downloads a copy of the Excel file to their local machine"
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:fjkilken
ID: 40199275
hi armchair_scouse
extended properties do look interesting alright, I have run some code and was able to create some extended properties for my SP

EXEC sys.sp_addextendedproperty  
         @name = N'CurrentVersion',
         @value = N'1.0',  
         @level0type = N'SCHEMA',  @level0name = 'dbo',  
         @level1type = N'PROCEDURE', @level1name = 'my_stored_procedure';

Open in new window


This nearly gets me what I need, however, I'd like to add another property to the SP - ie; a small description of the SP, I simply tried adding the line:
         @level2type = N'DESCRIPTION',  @level2name = 'Updated stored proc to add various fields';
but this failed "An invalid parameter or option was specified for procedure 'sp_addextendedproperty'."

Any idea of how I would add a DESCRIPTION field to the extended properties for this SP?
thanks
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 40199933
EXEC sys.sp_addextendedproperty  
         @name = N'DESCRIPTION',
         @value = N'<Your description goes here.>',  
         @level0type = N'SCHEMA',  @level0name = 'dbo',  
         @level1type = N'PROCEDURE', @level1name = 'my_stored_procedure';
0
 

Author Closing Comment

by:fjkilken
ID: 40201428
thanks guys - the extended properties aspect is very useful!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Integration Management Part 2
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question