Calling SQL-scripts
-
Hi!
Is it possibel to call qn sql-script from another sql-script?
BR
Nils O.
Norway
-
You can use the ISQL or OSQL utility. Look at up in BOL. One of them is deprecated, and i always forget which one
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks for your reply, Frank!
I'm actually using osql to run some scripts. 3-4 of these scripts require a default script with temporary parameters to be run first, but if I run this script separately, the info is not available to the succeding script. I therefore need to call this script from within the other scripts. I often do this in Oracle, but haven't found a way to do this in T-SQL.
BR
Nils
-
you could
1. wrap the scripts in stored procedures and call appropriately
2. use Tables to store intermediate results
* Noel -
Yes, that would be a way to go.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
One quick way to submit concatenated scripts to osql is:
type script1.sql script2.sql | osql -E -n
where: script1.sql contains:
declare @x int set @x = 12345
and script2.sql contains:
select @x
result is:
----------- 12345
Cheers,
- Mark -
NICE!!!
* Noel -
Hi!
I tested the last suggestion from Mark, but get the following error:
[Shared Memory]SQL Server does not exist or access denied.
[Shared Memory]ConnectionOpen (Connect()).
br Nils
-
can you post the scripts ?
* Noel -
Nils,
The example I gave was for a trusted connection to my local SQL Server. You need to specify the -S server name (and possibly -U -P) to access a remote server or named instance.
What are your usual osql parameters?
Cheers,
- Mark -
Hi,
you can call another script from within an osql script (like oracle @) by using the osql !! command to call another instance of osql.
e.g script1.sql
!!osql -d -U -P -S -n -i script2.sql
regards
Mike
-
Hi!
I tested with my usual settings and it worked perfectly!!!
Thanks to Mark for his elegant solution!!
br
Nils
-
Wow! Thanks for sharing that! I wish I had known that months ago!
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Is there any way to print out the error of script (stored in sql file) in Dos mode using osql?
-
From BOL.
-b
Specifies that osql exits and returns a DOS ERRORLEVEL value when an error occurs. The value returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity of 10 or greater; otherwise, the value returned is 0. Microsoft MS-DOS® batch files can test the value of DOS ERRORLEVEL and handle the error appropriately.
-m error_level
Customizes the display of error messages. The message number, state, and error level are displayed for errors of the specified severity level or higher. Nothing is displayed for errors of levels lower than the specified level. Use -1 to specify that all headers are returned with messages, even informational messages. If using -1, there must be no space between the parameter and the setting (-m-1, not -m -1).
-r {0 | 1}
Redirects message output to the screen (stderr). If you do not specify a parameter, or if you specify 0, only error messages with a severity level 17 or higher are redirected. If you specify 1, all message output (including "print") is redirected.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply