batch file for non sql user

Question

Post reply

batch file for non sql user

pat-261672

Hall of Fame

Points: 3325
More actions
October 24, 2007 at 9:26 pm

#180610

Hi all,
how can i create batch file which will take parameter by non sql user to make changes to SQL database on server!!!
such as
use test; --database name
declare @time as varchar(35), @endtime varchar(35)
update testtable
set endtime = @endtime
where time = @time
use wants it to be in batch file so they can run on their computer and run as command prompt and then call batch file and run with parameters!!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

Use sqlcmd which is new in 2005, isql, osql or bcp

--Ramesh

Lowell SSC Guru Points: 323546 More actions · Answer 2

if you are SP2 on your 2005 instance, you could use a logon trigger:

from some ms technote:

In SQL Server 2005 Service Pack 2, there is a new feature for Logon

Triggers. Logon triggers fire after the authentication phase of logging

in finishes, but before the user session is actually established. See

the updated Books Online for more information.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 3

1) Example of running system command using xp_cmdshell is SQL SERVER - Script to find SQL Server on Network

EXEC master..XP_CMDShell 'ISQL -L'2)

Example of running batch file using T-SQL

i) Running standalone batch file (without passed parameters)

EXEC master..XP_CMDShell 'c:findword.bat'

ii) Running parameterized batch file

DECLARE @PassedVariable VARCHAR(100)

DECLARE @CMDSQL VARCHAR(1000)

SET @PassedVariable = 'SqlAuthority.com'

SET @CMDSQL = 'c:findword.bat' + @PassedVariable

EXEC master..XP_CMDShell @CMDSQL

K. Brian Kelley SSC Guru Points: 114704 More actions · Answer 4

The solution Ramesh suggests, to use one of the SQL Server command-line clients, is the one I'd go with. Because it's command-line, you can put in a batch, meaning you can use the parameters. Logon triggers won't take parameters and xp_cmdshell is a solution from within SQL Server (and generally recommended against for security reasons).

K. Brian Kelley
@kbriankelley