OSQL.exe question

Is there a way to process multiple SQL scripts in a single transaction using
osql.exe ?
Ideally, I'd like to be able to start transaction, execute multiple sql
scripts and commit transaction when all of them succeeded or rollback when
there was a failure.
I appreciate your help.
I would copy and paste them together.
-jens S=FC=DFmeyer.
|||"Marek" <nospam@.nowhere.com> wrote in
news:#BmMdSD8FHA.2676@.TK2MSFTNGP15.phx.gbl:

> Is there a way to process multiple SQL scripts in a single transaction
> using osql.exe ?
> Ideally, I'd like to be able to start transaction, execute multiple
> sql scripts and commit transaction when all of them succeeded or
> rollback when there was a failure.
> I appreciate your help.
As far as I know this is not possible in osql, but if you try sqlcmd from
SQL Server 2005, I do believe that you can. However, I have not tried this.
SqlCmd is also available in SQL Server 2005 Express, which is free.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in
news:Xns9717A547BD7D7olekristianbangaas@.207.46.248 .16:

> "Marek" <nospam@.nowhere.com> wrote in
> news:#BmMdSD8FHA.2676@.TK2MSFTNGP15.phx.gbl:
>
> As far as I know this is not possible in osql, but if you try sqlcmd
> from SQL Server 2005, I do believe that you can. However, I have not
> tried this. SqlCmd is also available in SQL Server 2005 Express, which
> is free.
And as Tibor so kindly answered in another group: You can download SqlCmd
here:
http://www.microsoft.com/downloads/d...D09C1D60-A13C-
4479-9B91-9E8B9D835CDC&displaylang=en
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||> As far as I know this is not possible in osql
I guess you could try the :r option to have OSQL read sub-scripts from a mother script. The
sub-scripts cannot have GO. A test is needed to verify that new connections will not be opened for
subscripts, use Profiler for that. Also, :r is not documented for OSQL, but I think it is for
SQLCMD. However, I don't think that error handling will be the easiest thing to accomplish.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns9717A547BD7D7olekristianbangaas@.207.46.248 .16...
> "Marek" <nospam@.nowhere.com> wrote in
> news:#BmMdSD8FHA.2676@.TK2MSFTNGP15.phx.gbl:
>
> As far as I know this is not possible in osql, but if you try sqlcmd from
> SQL Server 2005, I do believe that you can. However, I have not tried this.
> SqlCmd is also available in SQL Server 2005 Express, which is free.
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging

osql.exe

Is there some registry key that tell me if and where osql.exe is lcoated?
--
Sincerely
Andrea Morohi Andrea,
"Andrea Moro" <moroandreaET@.tiscalinet.it> ha scritto nel messaggio
news:%23JY7YyJcDHA.616@.TK2MSFTNGP11.phx.gbl...
> Is there some registry key that tell me if and where osql.exe is lcoated?
have a look at HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\80\
SharedCode key which should point to
c:\program files\Microsor SQL Server\80\Tools\
or HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup
SQLPAth key which should point to
c:\program files\Microsor SQL Server\80\Tools\
just consider oSql.exe resides in the Binn subfoler...
hth
Andrea Montanari
andrea.sql@.virgilio.it
montanari_andrea@.virgilio.it
http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.4.0 - DbaMgr ver 0.50.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||Refer to this url
http://www.vb2themax.com/Item.asp?PageID=CodeBank&ID=688
--
-Vishal
"Andrea Moro" <moroandreaET@.tiscalinet.it> wrote in message
news:#JY7YyJcDHA.616@.TK2MSFTNGP11.phx.gbl...
> Is there some registry key that tell me if and where osql.exe is lcoated?
> --
> Sincerely
> Andrea Moro
>|||with t-sql you can have something like this:
create table #test (value varchar(500), data varchar(500))
nsert into #test
exec master..xp_regread N'HKEY_LOCAL_MACHINE',N'SOFTWARE\Microsoft\Microsoft
SQL Server\80\Tools\ClientSetup', N'SQLPath'
select data + '\bin' as 'osql_path' from #test
--
-Vishal
"Vishal Parkar" <vgparkar@.hotmail.com> wrote in message
news:e0ysoXKcDHA.2688@.TK2MSFTNGP11.phx.gbl...
> Refer to this url
> http://www.vb2themax.com/Item.asp?PageID=CodeBank&ID=688
> --
> -Vishal
> "Andrea Moro" <moroandreaET@.tiscalinet.it> wrote in message
> news:#JY7YyJcDHA.616@.TK2MSFTNGP11.phx.gbl...
> > Is there some registry key that tell me if and where osql.exe is
lcoated?
> >
> > --
> > Sincerely
> > Andrea Moro
> >
> >
>

osql vs. QA and extended characters

Hi, I have some sql commands I incorporate into an install and I've been
using scripts and osql.exe to perform these with generally good results.
A recent addition to these installs is a script that populates a table
of usernames and encryped passwords, and this is where I'm having
trouble. If I run a script in Query Analyzer that contains this line:
insert into operator (op_code, password, first_name, last_name,
security, operator, salespoint) values ('ADMIN', '??', 'ADMIN',
'ADMIN', 9, 'SYSTEM', 'SYSTEM')
I get predictable results and the encrypted password gets correctly
decrypted in our application.
If I run the very same script using osql like this:
osql.exe -E -i MyScript.sql -o "Output.txt"
The password doesn't end up in the database as '??', it seems to get
changed to '?+-' which obviously wouldn't decrypt correctly in the
application.
So this makes me think there's some kind of translation going on with
these extended characters when running the script via osql. Anyone know
how I can achieve the same results with osql as QA? I'm hoping there's a
switch or something that will do the trick. Thanks!
Matt
MattB wrote:
> Hi, I have some sql commands I incorporate into an install and I've
> been using scripts and osql.exe to perform these with generally good
> results.
> A recent addition to these installs is a script that populates a table
> of usernames and encryped passwords, and this is where I'm having
> trouble. If I run a script in Query Analyzer that contains this line:
> insert into operator (op_code, password, first_name, last_name,
> security, operator, salespoint) values ('ADMIN', '??', 'ADMIN',
> 'ADMIN', 9, 'SYSTEM', 'SYSTEM')
> I get predictable results and the encrypted password gets correctly
> decrypted in our application.
> If I run the very same script using osql like this:
> osql.exe -E -i MyScript.sql -o "Output.txt"
> The password doesn't end up in the database as '??', it seems to
> get changed to '?+-' which obviously wouldn't decrypt correctly in
> the application.
> So this makes me think there's some kind of translation going on with
> these extended characters when running the script via osql. Anyone
> know how I can achieve the same results with osql as QA? I'm hoping
> there's a switch or something that will do the trick. Thanks!
> Matt
See this page:
http://www.windowsitpro.com/SQLServe...ver_37470.html
David Gugick
Imceda Software
www.imceda.com
|||David Gugick wrote:
> MattB wrote:
>
> See this page:
> http://www.windowsitpro.com/SQLServe...ver_37470.html
>
Great. Thanks!
Matt

osql utility dependency

Hi,
Does the command-line utility osql.exe has any external dependencies?
I'm building a java installer that needs to interact with MSSQL with
osql.exe. Now the temporary solution is to ensure the machine on which
the installer is run has either MSDE or MSSQL client installed, but that
is clearly not a long-term solution. Can i just take osql.exe and
package it into my installer so that i can interact with MSSQL database
server without MSDE or MSSQL client installed?You can't just take osql.exe and package it in the installer, as you are not
allowed to redistribute it. But anything you can do with osql.exe, you can
do with JDBC as well.
Jacco Schalkwijk
SQL Server MVP
"Zhu Bo" <bo.zhu@.encentuate.om> wrote in message
news:%2375I4hENFHA.3420@.tk2msftngp13.phx.gbl...
> Hi,
> Does the command-line utility osql.exe has any external dependencies? I'm
> building a java installer that needs to interact with MSSQL with osql.exe.
> Now the temporary solution is to ensure the machine on which the installer
> is run has either MSDE or MSSQL client installed, but that is clearly not
> a long-term solution. Can i just take osql.exe and package it into my
> installer so that i can interact with MSSQL database server without MSDE
> or MSSQL client installed?

os_oa* run exe written in vb6

I need to launch an exe application written in VB6 from stored procedure or
trigger. I know I have to use os_oa* but I cannot find any document to help
me build one. Many examples that I found have something to do with com but I
only have exe. I know this is not the way to go but I was told to do it. Can
any one help me by pointing me in the right directions? I know nothing about
this so I am learning from scratch for os_oa*.
The exe was developed to run silent in the background with no user
interface.Search for xp_cmdshell in BOL, it may be useful.
Francesco Anti
"UGH" <nospam@.noSPam.com> wrote in message
news:eRxL9tFaFHA.1368@.tk2msftngp13.phx.gbl...
>I need to launch an exe application written in VB6 from stored procedure or
>trigger. I know I have to use os_oa* but I cannot find any document to help
>me build one. Many examples that I found have something to do with com but
>I only have exe. I know this is not the way to go but I was told to do it.
>Can any one help me by pointing me in the right directions? I know nothing
>about this so I am learning from scratch for os_oa*.
>
> The exe was developed to run silent in the background with no user
> interface.
>|||Do you mean sp_oa* ' If so, here is everything you need:
1. Compile your VB6 class into a DLL. You better have good error handling in
it otherwise you risk crashing your SQL Server.
2. Place that DLL into the WIN32 directory on your SQL Server, and REGISTER
it on your SQL Server by using RegSvr32.exe
3. Call methods of your VB6 class/dll from any stored procedure, using
something like the following (some of the names have been changed to protect
the innocent):
BEGIN
blah blah blah
DECLARE @.Object int -- holds a reference to your object instantiated
from the vb6 class.
SET @.MethodToCall = 'CalculatePayment(' + @.RatePercent + ', ' +
@.RateIncrease + ', ' + @.Months + ', ' + @.Fees + ')'
--Instantiate an instance of our DataConversion class and put it's
reference in @.object
EXEC sp_OACreate 'Your_VB_Class.DataConversion', @.object OUT
--Run the CalculatePayment method of our DataConversion class - and
place it's output into @.Return
EXEC sp_OAMethod @.object, @.MethodToCall, @.return OUT
-- Destroy the instance of our DataConversion class now that we're
done with it.
EXEC sp_OADestroy @.object
blah blah blah
END
4. Any time you need to upgrade your VB6 DLL, be sure to Unregister the old
one (using RegSvr32.exe... -U), then replace the old DLL with the new one,
and then register the new one like in step 2 above. No need to restart your
SQL Server.
That's it - really quite straight-forward, actually.
-HTH
"UGH" <nospam@.noSPam.com> wrote in message
news:eRxL9tFaFHA.1368@.tk2msftngp13.phx.gbl...
>I need to launch an exe application written in VB6 from stored procedure or
>trigger. I know I have to use os_oa* but I cannot find any document to help
>me build one. Many examples that I found have something to do with com but
>I only have exe. I know this is not the way to go but I was told to do it.
>Can any one help me by pointing me in the right directions? I know nothing
>about this so I am learning from scratch for os_oa*.
>
> The exe was developed to run silent in the background with no user
> interface.
>|||Hi,
Use the following :
EXEC xp_cmdshell 'your vb program.exe'
you want to grant the right to execute xp_cmdshell to the SQL login
LimitedUser.
You'll need an NT account to execute the program. Here's the script:
use master
go
xp_sqlagent_proxy_account N'SET'
, N'<mydomain>'
, N'<ntuser>'
, N'<ntuser's password>'
go
-- retrieve the proxy account to check that it's correct.
xp_sqlagent_proxy_account N'GET'
go
-- grant database access in master
sp_grantdbaccess 'LimitedUser'
go
grant exec on xp_cmdshell to LimitedUser
go
Thanks,
Tarek ghazali
"UGH" <nospam@.noSPam.com> wrote in message
news:eRxL9tFaFHA.1368@.tk2msftngp13.phx.gbl...
>I need to launch an exe application written in VB6 from stored procedure or
>trigger. I know I have to use os_oa* but I cannot find any document to help
>me build one. Many examples that I found have something to do with com but
>I only have exe. I know this is not the way to go but I was told to do it.
>Can any one help me by pointing me in the right directions? I know nothing
>about this so I am learning from scratch for os_oa*.
>
> The exe was developed to run silent in the background with no user
> interface.
>