Re: Executing a SQL script on server

David De Bono wrote:

> Hi,
>
> How can I using ADO send a SQL script to the server to be executed there ?
>
> Example script:
>
> USE [e-Lector]
>
> GO
>
> EXEC sp_addrolemember N'db_datareader', N'eanew'
>
> GO
>
> USE [e-Lector]
>
> GO
>
> EXEC sp_addrolemember N'db_datawriter', N'eanew'
>
> GO
>
> ---------------------------------
>
> I have tride to send it via the Execute method, but it fails on "GO"
>

Hi,

An example VBScript program to execute a stored procedure:
=============================
Option Explicit

Dim adoCommand
Dim strConnect
Dim strServer
Dim strDatabase

' Specify the SQL Server and Instance (if any).
' If the default instance, just specify the server name.
strServer = "MyServer\MyInstance"

' Specify the database.
strDatabase = "e-Lector"

' Connection string for SQL database.
strConnect = "DRIVER=SQL Server;Trusted_Connection=Yes;" _
& "DATABASE=" & strDatabase & ";" _
& "SERVER=" & strServer

' Open connection with an ADO Command object.
Set adoCommand = CreateObject("ADODB.Command")
adoCommand.ActiveConnection = strConnect

' Execute a system stored procedure.
adoCommand.CommandText = "EXEC sp_addrolemember N'db_datareader', N'eanew'"
adoCommand.Execute
============================

The tricky part is usually the connection string. Also, all SQL statements
must be enclosed in quotes. The GO is not used. I generally run such scripts
at a command prompt with the cscript host. The script can be run from any
machine with connectivity to the SQL Server. Of course, the above assumes
Windows Authentication and that you have sufficient privileges. If you use
SQL Logins, the connection string needs credentials. I'd have to lookup how
to specify userID and password.

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net


.

Relevant Pages

  • Re: IP addresses and JS.
    ... >>A computer language is just that, ... Javascript is just a programming language, and may be used to script all ... The interaction between a web browser and a server is through HTTP. ... execute scripts and/or programs, ...
    (comp.lang.javascript)
  • Re: Converting Perl Web Report to Python
    ... lets say that your sql script looks like this: ... This capability would allow each sql script to be extendable. ... How would you execute ...
    (comp.lang.python)
  • Re: Stored procedure/trigger and scripts
    ... SQL Server has permissions to execute xp_cmdshell. ... Please read up on xp_cmdshell within Books Online (within the SQL Server program group). ... >> client to change their password they have to call the "Client Relations" ...
    (microsoft.public.sqlserver.programming)
  • Re: How to call one SQL script from another
    ... If you execute the host script through OSQL, you can use:r, but the ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Problem with bulk load security.
    ... The only user that didn't have the bulkadmin roll was the ID that SQL ... Did you to EXECUTE AS USER or EXECUTE ... Links for SQL Server Books Online: ... DDL Triggers with Server Scope and logon triggers ...
    (microsoft.public.sqlserver.security)