SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Exec SQL Proc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLNOVICE999
Yak Posting Veteran

57 Posts

Posted - 12/03/2013 :  14:02:18  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 12/03/2013 :  23:34:05  Show Profile  Reply with Quote
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

57 Posts

Posted - 12/04/2013 :  02:06:09  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 12/04/2013 :  04:14:20  Show Profile  Reply with Quote
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

57 Posts

Posted - 12/04/2013 :  10:41:45  Show Profile  Reply with Quote
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

57 Posts

Posted - 12/04/2013 :  11:48:38  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 12/04/2013 :  12:06:50  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 12/04/2013 :  12:08:36  Show Profile  Reply with Quote
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

57 Posts

Posted - 12/04/2013 :  12:11:41  Show Profile  Reply with Quote
Kristen thanks for your advice and help... my earlier script had " in the wrong place and it worked. Thanks!

Edited by - SQLNOVICE999 on 12/04/2013 12:38:33
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 12/04/2013 :  12:47:50  Show Profile  Reply with Quote
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 ...

Edited by - Kristen on 12/04/2013 12:50:28
Go to Top of Page

SQLNOVICE999
Yak Posting Veteran

57 Posts

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

Kristen
Test

United Kingdom
22415 Posts

Posted - 12/04/2013 :  13:02:23  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000