[AccessD] A2003: Deploying Stored Procs to SQL Server

Hi Jim,

Queries against server tables require connection settings which include uid
and pwd generally. Those are known to the server as read or read-write,
perhaps table by table. If the connection is read write then the tables
exposed to that user can be damaged whether PTQ or not. I don't  see any
special damage a PTQ can do unless it is saved with credentials, which
should not be allowed.
On Jun 4, 2014 11:37 PM, "Jim Lawrence" <accessd at shaw.ca> wrote:

> 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
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>