[AccessD] A2003: Deploying Stored Procs to SQL Server

WOW David,
Many many thanks for the detailed reply. I've actioned none of it yet, I
will advise once I have.
Looking forward to your follow up.
Again, many thanks
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:01 AM
To: Access Developers discussion and problem solving
Subject: Re: [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?
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com