none
SQL Server 2008 - Stored Procedure

    Question

  • I work for a software manufacturing company and I am doing a project for a very large potential customer.  This customer has a SQL Server 2008 DB and claims that his emplpyee's can only access the DB (read or write) using a stored procedure.  Now the catch is we cant re-write the software to accomidate this customer, and our requests to see the stored procedure to see how hes using it have failed.  But I need to be able to access his DB at no additional cost to the customer.  Is there any way to call a SP using a Data Source Driver?  Or any other ideas, I need an answer for this cutomer today :(

    Wednesday, September 29, 2010 2:20 PM

Answers

All replies

  • Do you want to call them or see Stored Procedure's SQL statements?

    If you want to call them try to use ODBC connection to connect DB. 

    If you want to see SQL statement of stored procedures:

    Select 
    	sysProc.object_id As ObjectId, 
    	sysProc.name As ProcedureName, 
    	Convert(TinyInt, 1) As ProcedureVersion, 
    	sysProc.parent_object_id As ProcedureParentId, 
    	sysProc.create_date As ProcedureCreateDate, 
    	sysProc.modify_date As ProcedureModifyDate, 
    	sysMod.definition As ProcedureDefinition
    from 
    	sys.procedures As sysProc Inner Join sys.sql_modules sysMod On sysProc.object_id = sysMod.object_id
    Union All 
    Select 
    	sysProc.object_id As ObjectId, 
    	sysProc.name As ProcedureName, 
    	Convert(SmallInt,sysProcNum.procedure_number) As ProcedureVersion, 		
    	sysProc.parent_object_id As ProcedureParentId, 
    	sysProc.create_date As ProcedureCreateDate, 
    	sysProc.modify_date As ProcedureModifyDate, 
    	sysProcNum.definition As ProcedureDefinition 
    from 
    	sys.procedures As sysProc Inner Join sys.numbered_procedures sysProcNum On sysProc.object_id = sysProcNum.object_id
    


    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    Wednesday, September 29, 2010 3:22 PM
  • I want to call it.  My software points to the ODBC Connection (Data Source Driver), I can see the database in my client, the problem is in order to see the information, I need to call on the stored procedure to get read rights to the database.  So I need a way, if its possible, to call the stored procedure from the Data Source Driver inorder to get the information returned to my software.
    Wednesday, September 29, 2010 3:43 PM
  • Hi,

    If you want to call the SP, just execute below statement in your application.

    EXEC <your stored procedure name>  

    eg. EXEC uspCustomerDetails


    Ramesh S
    www.sequalserver.com
    Wednesday, September 29, 2010 4:42 PM
  • Hi,

    If you want to call the SP, just execute below statement in your application.

    EXEC <your stored procedure name>  

    eg. EXEC uspCustomerDetails


    Ramesh S
    www.sequalserver.com

    I cant re-write the software for one of 400,000 customers.  What I would like to do is know if there is a way for the DSD to run the procedure so that when my client calls on the DSD it has the correct information already.

    Wednesday, September 29, 2010 4:56 PM
  • Hi Kenneth,

     

    In this situation, I would like to recommend that you write a simple application which could call this stored procedure.

    Since you are using ODBC to connect to SQL Server, if you are using C++, please refer to this link to get more information about calling stored procedure: http://msdn.microsoft.com/en-us/library/ms131440.aspx

    If you are using .Net, please refer to this link: http://msdn.microsoft.com/en-us/library/ms811006.aspx

     

    If anything is unclear, please let me know.

     

     


    Regards,
    Tom Li
    Thursday, September 30, 2010 12:25 PM
    Moderator