I am trying to create a script to run scripts from a file and would like to be able to declare a "rootDirectory" variable. Example:

BEGIN ATOMIC
DECLARE @rootDir VARCHAR(255) = 'D:\MyScriptDir\Dev\';
READ @rootDir + 'MyDevScript1.sql';
READ @rootDir + 'MyDevScript2.sql';
-- etc..
END;

I keep getting a syntax error and can't figure out where/what it is?

asked 02 Jun '14, 15:55

jkim's gravatar image

jkim
16114
accept rate: 0%


The READ statement is a client-side dbisql[c] statement and cannot be used in a server-side batch or procedure.

Try using xp_read_file or read_client_file. xp_read_file() will read a file on the server, while read_client_file() will read a file on the client (but the data is automatically sent to the server). See the docs for more information and examples.

HTH

permanent link

answered 02 Jun '14, 16:09

Mark%20Culp's gravatar image

Mark Culp
23.3k9132275
accept rate: 40%

As Mark indicates, you can't do exactly what you are asking, but perhaps you can do what you want with the PARAMETERS statement; see READ and PARAMETERS.

Here's an example...

run_dbisql16_READ_master.bat

"%SQLANY16%\bin64\dbisql.com"^
   -c "ENG=ddd16; DBN=ddd16; UID=dba; PWD=sql"^
   READ ENCODING Cp1252 "master.sql" [c:/temp/]

master.sql

PARAMETERS rootDir;
READ '{rootDir}MyDevScript1.sql';
READ '{rootDir}MyDevScript2.sql';

c:\temp\MyDevScript1.sql

MESSAGE 'MyDevScript1.sql has been executed.' TO CONSOLE;

c:\temp\MyDevScript2.sql

MESSAGE 'MyDevScript2.sql has been executed.' TO CONSOLE;

dbsrv16 console output...

MyDevScript1.sql has been executed.
MyDevScript2.sql has been executed.
permanent link

answered 02 Jun '14, 16:46

Breck%20Carter's gravatar image

Breck Carter
26.9k438609883
accept rate: 21%

Breck, it looks like you're right. I'm still getting a syntax error using "read_client_file()" it seems that any declared variable is being treated as a literal. The problem is that I need the script to be as simple and straight forward as possible without having to run/maintain external batch files.

(02 Jun '14, 17:00) jkim
Replies hidden

If you are having a problem with read_client_file(), please post your exact code in a new question, along with the exact error message.

(02 Jun '14, 17:17) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×32

question asked: 02 Jun '14, 15:55

question was seen: 473 times

last updated: 02 Jun '14, 17:17