[AccessD] A2003: Deploying Stored Procs to SQL Server

Hi Bill:

On an aside; it should be noted that being able to use pass-through queries, directly to the SQL server, leaves the server open to "insertion attacks". I personally do not condone pass-through type queries for that reason. When I worked for the government, on contract, for the reason above PTQs, were forbidden.

jim   

----- Original Message -----
From: "Bill Benson" <vbacreations at gmail.com>
To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
Sent: Wednesday, June 4, 2014 7:16:16 PM
Subject: Re: [AccessD] A2003: Deploying Stored Procs to SQL Server

I have used this approach all my programming life - and would not call a
pass through query called from a public sub a "stored procedure" by any
stretch of the imagination. Whose lexicon says it is? To me this is a pass
thru query, with dynamic SQL; no more or less. A Stored Procedure sits on
the server (RDBMS), waiting to be triggered by a call to it, via an execute
statement or script - or a chron job running on the server. At least that is
my definition. The only "stored procedure" being implemented below was the
stored Public Sub ExecuteStoredProcedureWithoutRecordset.

Let's not get carried away with SPROC envy to the point that we adopt that
mantle for every tom dick and harry local subroutine now!
:-)



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren
Sent: Tuesday, June 03, 2014 3:06 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] A2003: Deploying Stored Procs to SQL Server

WOW and WOW again
Can't wait to test this stuff. 
David, thank you so very very much for your efforts - I will advise.
Darren


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Tuesday, 3 June 2014 9:08 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] A2003: Deploying Stored Procs to SQL Server

Now onto the Access part ;)

I created a new blank MDB (Actually an ACCDB in 2007).

Creat a new query in design view.
Change the Query type to a passthrough query

Enter your call:

EXEC stpDistributeSproc 'YourServerNameHere', 'DBnameHere',
'C:\TestSproc.sql'


Create a new Module, paste the following in it:
Public Sub ExecuteStoredProcedureWithoutRecordset(strSQLStatement As String)
    'Execute a stored procedure without expecting a recordset

    'Variables
    Dim db As Database, qdf As QueryDef
    Set db = CurrentDb
    Set qdf = db.QueryDefs("Query1")
    qdf.SQL = strSQLStatement

    'Execute the SQL statement
    DoCmd.SetWarnings False
    qdf.ReturnsRecords = False
    DoCmd.OpenQuery qdf.Name
    DoCmd.SetWarnings True

    'Reset variables
    Set qdf = Nothing
    Set db = Nothing
End Sub


Now create a form with a button on it, place the following code in the
OnClick event of the button:


Private Sub cmdDistributeSprocs_Click()
 Dim YourServerName As String, YourDataBaseName As String,
YourFilePathAndName As String, strSQL As String  YourServerName = "MKDBSRV"
 YourDataBaseName = "SSIS_Test"
 YourFilePathAndName = "C:\TestSproc.sql"
 'strSQL = "EXEC xp_cmdshell sqlcmd -S " + YourServerName + " -d " +
YourDataBaseName + " -i " + YourFilePathAndName  'Debug.Print (strSQL)

strSQL = "EXEC stpDistributeSproc '" & YourServerName & "', '" &
YourDataBaseName & "', '" & YourFilePathAndName & "'"
' Debug.Print (strSQL)
Call modSQLServer.ExecuteStoredProcedureWithoutRecordset(strSQL)

End Sub


if it all works out ok, you'll only need to keep a table of paths and .sql
file names and then open and loop through the record set, calling the
function as above, in the onlclick even. Let me know if you need help with
that.

I have to give credit for calling the sproc  from this link:
http://blog.ambitionit.nl/archive/2009/07/15/execute-a-stored-procedure-from
-an-mdb.aspx

HTH
David
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com