[AccessD] A2003: Deploying Stored Procs to SQL Server

I just built a working copy and it executes the .sql file, keeping the
comments and formatting as it was in the .sql file.

I tried to run the xp_cmdshell command from access via the pass through
query, but it doesn't work. I had to create a stored procedure (sproc) on
the server and execute the server.

So the only downside to using my method is that you would have to create
this sproc on every server/database that you want to load/execute .SQL
files in.

Another thing that I noticed was that xp_cmdshell doesn't like spaces in
the path, so for my test, I ended up placing the .sql files in c:\


if you have access to Sql Server Management Studio (SSMS), run the
following:

--**********************Start TSSQL*********

 DECLARE @YourServerName AS NVARCHAR(10), @YourDataBaseName AS
NVARCHAR(10), @YourFilePathAndName AS NVARCHAR(50)

SET @YourServerName ='YourServerNameHere'
SET @YourDataBaseName ='YourSQLDBNameHere'
SET @YourFilePathAndName ='C:\TestSproc.sql'

DECLARE @strSQL AS NVARCHAR(200)

SET @strSQL = 'sqlcmd -S ' + @YourServerName + ' -d ' + @YourDataBaseName +
' -i ' + @YourFilePathAndName

 PRINT(@strSQL)

--**********************End TSSQL*********
Run the script by pressing F5.

If the EXEC statement looks good, copy it to a new SSMS Query window and
try running it. If it works, we're all good, add the following line:

EXEC xp_cmdshell @strSQL


Then turn the thing into a sproc.

If it doesn't work, you may need to enable xp_cmdshell.

To do so, run the following in SSMS:


-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO

Now run the exec statement. I just want to make sure the SQL part is
working, before moving on to the Access part.

If all is good, turn the first script into a sproc, that should end up
looking like this:

CREATE PROCEDURE [dbo].[stpDistributeSproc] (@YourServerName AS
NVARCHAR(10), @YourDataBaseName AS NVARCHAR(10), @YourFilePathAndName AS
NVARCHAR(50)) AS
 --DECLARE @YourServerName AS NVARCHAR(10), @YourDataBaseName AS
NVARCHAR(10), @YourFilePathAndName AS NVARCHAR(50)
 --SET @YourServerName ='MKDBSRV'
 --SET @YourDataBaseName ='SSIS_Test'
 --SET @YourFilePathAndName ='C:\TestSproc.sql'
 DECLARE @strSQL AS NVARCHAR(200)
 SET @strSQL = 'sqlcmd -S ' + @YourServerName + ' -d ' +
@YourDataBaseName  + ' -i ' + @YourFilePathAndName
 --PRINT(@strSQL)
 EXEC xp_cmdshell @strSQL


I'll follow up with another email with the Access part

HTH
David McAfee
On Sun, Jun 1, 2014 at 6:34 PM, Darren <darren at activebilling.com.au> wrote:

> Hi David,
> Many thanks for your reply.
> I have been told (Haven't tested for myself) that the SQLCMD command line
> tool works as advertised, but strips all the nice indenting etc., built
> into
> any SPROC in the *.SQL file.
> Thus the SPROC is very difficult to read from a human point of view. Hence
> my attempt to perform this task for my colleague.
> So 2 issues
> 1    Deploying it against many dbs across mostly one but sometimes a couple
> of SQL Servers (I can do that bit no prob)
> 2    Have the SPROC inserted into the destination dB with formatting intact
> (My passthrough fails on the first "GO" statement in any SPROC not sure why
> and I am told the SQLCMD tool strips indenting etc.))
> With the SQLCMD striping formatting (Indents etc.), has this been your
> experience?
>