Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Exec SQL Proc

Author  Topic 

SQLNOVICE999
Yak Posting Veteran

62 Posts

Posted - 2013-12-03 : 14:02:18
Hi Guys,

I need to execute sql server proc from MS Access or Excel... the proc has couple of parameters, but do not have any data comming out. Can we do that? And how we can do it so that user can enter values for parameter and execute the proc.

Thanks in advance for the help.

Laura

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-03 : 23:34:05
you need to create a form for that and use excel vba to pass it down.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SQLNOVICE999
Yak Posting Veteran

62 Posts

Posted - 2013-12-04 : 02:06:09
thanks vaisakh. I do not know vba... do u have an example of this. I need to execute sql proc with two parameters and no data will returned by the proc. I just need the the form so that user can enter input parameters... Thanks again.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2013-12-04 : 04:14:20
In Excel I run Sprocs using MSQUERY. Its a bit Micky Mouse, but it works OK for end users to PULL some data from an Sproc (using parameters provided from cells in the spreadsheet). If the SProc doesn't return any data that would be fine.

Dunno about Access though.

Could you do it from Command Prompt (or a BATCH file) instead? That would be easy to achieve using OSQL
Go to Top of Page

SQLNOVICE999
Yak Posting Veteran

62 Posts

Posted - 2013-12-04 : 10:41:45
Kristen Batch File is a good suggestion I think. Let me check how I can do that. Thanks!
Go to Top of Page

SQLNOVICE999
Yak Posting Veteran

62 Posts

Posted - 2013-12-04 : 11:48:38
I am trying to use batch file to execute proc... never did it before

@echo off
SET /P path=Enter the location:
SET /P oldName=Enter the name:
SET /P newName=Enter the new name:
sqlcmd -E -Q "USE [TESTDB] EXEC sp_rename @path =N'%path%', @oldName=%oldName%", @newName=%newName%"
SET path=
SET oldName=
SET newName=


Is this the correct code? It doesn't pass and I cannot see error....
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2013-12-04 : 12:06:50
The environment variable PATH has special connotations, so perhaps best not to use that as a variable. Perhaps prefix all your variable names with your initials, or something like that?

"USE [TESTDB]" probably won't work within your Query. The parameter "-d" allows you to specify the database, so this would probably work:

sqlcmd -S MyServerName -E -d TESTDB -Q "EXEC sp_rename @path =N'%XXX_path%', @oldName='%XXX_oldName%', @newName='%XXX_newName%'"

I always include a parameter to save the output to a file (so I can look at it later, if necessary) e.g. -o MyOutputFile.TXT
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2013-12-04 : 12:08:36
P.S. You should not use the prefix "sp_" on your own Sprocs. That is used by the system, and there is a risk that your Sproc name clashes with a system name (including in a future version :( ), there is also a performance implication (because SQL will first look for a system Sproc of that name, in Master, before then looking for a specific one in your database.

"usp_" as often used as a prefix for "User Sproc"
Go to Top of Page

SQLNOVICE999
Yak Posting Veteran

62 Posts

Posted - 2013-12-04 : 12:11:41
Kristen thanks for your advice and help... my earlier script had " in the wrong place and it worked. Thanks!
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2013-12-04 : 12:47:50
Here's an example taken from real code that we use. It includes everything that we have ever needed :)

We have an Error Flag File. If there is a critical error at any point we append a message to this file. At the end of the job if the file exists then there was an error somewhere in the process. If the file does NOT exist then the outcome was successful.

MyProcessName.LOG is a permanent log file (we don't delete it at the start of the job) and we append a message if an error occurs in the job.

We store Output and Error files from each step in the process (each has a unique name). IF there is an error then we look in those files to see what was recorded. They can be deleted at the start of the next run (or, do as we do, and keep the last-10 so we get a chance to look at the day-before-yesterday when it takes the Client a few days to tell us something went wrong, or it goes wrong at the weekend)


REM Pre delete "Error Flag File"
DEL %MyLogPath%\MyErrorFlagFilename.ERR

... Other processes here ...
... Other processes here ...
... Other processes here ...

ECHO %DATE% %TIME% MyProcessName START>>%MyLogPath%\MyProcessName.ERR
ECHO Command: SQLCMD -d %MyDatabaseName% -Q "EXEC dbo.MySProc @Param1=123, @Param2=456" -o %MyLogPath%\MyProcessName.OUT -b -l 300 -S MyServerName -E >>%MyPath%\MyProcessName.ERR
SQLCMD -d %MyDatabaseName% -Q "EXEC dbo.MySProc @Param1=123, @Param2=456" -o %MyLogPath%\MyProcessName.OUT -b -l 300 -S MyServerName -E >>%MyPath%\MyProcessName.ERR
IF ERRORLEVEL 1 GOTO SQLCMD_Error
ECHO %DATE% %TIME% SQL MyProcessName END >>%MyLogPath%\MyProcessName.ERR
GOTO SQLCMD_Done

:SQLCMD_Error
ECHO %DATE% %TIME% SQL MyProcessName *** ERROR EXIT *** >>%MyLogPath%\MyProcessName.ERR
ECHO %DATE% %TIME% SQL MyProcessName *** ERROR EXIT *** : MyProcessName
ECHO %DATE% %TIME% SQL MyProcessName *** ERROR EXIT *** : MyProcessName>>%MyLogPath%\MyProcessName.LOG
ECHO %DATE% %TIME% SQL MyProcessName *** ERROR EXIT *** : MyProcessName>>%MyLogPath%\MyErrorFlagFilename.ERR
GOTO SQLCMD_Done

:SQLCMD_Done
ECHO %DATE% %TIME% SQL MyProcessName END
ECHO %DATE% %TIME% SQL MyProcessName END>>%MyLogPath%\MyProcessName.LOG

... Other processes here ...
... Other processes here ...
... Other processes here ...

IF NOT EXIST %MyLogPath%\MyErrorFlagFilename.ERR GOTO NoError
... Error Flag File exists, handle any error here ... e.g. send EMail alert to ADMINs

:NoError
... All Done ...
Go to Top of Page

SQLNOVICE999
Yak Posting Veteran

62 Posts

Posted - 2013-12-04 : 12:55:17
Kristen can't thank you enough for the assistance. Thanks for the detailed explanation and script.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2013-12-04 : 13:02:23
My pleasure.

If you need a Command Line tool to send an email then the one we use is Blat!

http://www.blat.net/
Go to Top of Page
   

- Advertisement -