outerjoint

Hi all - I am new to MsSQL and need a bit of help.
I have a query that works perfectly in Oracle but not in MsSQL. I understand that there are different commands in MsSQL and for the life of me I cannot find them. Can someone help? Here is the query.

Select c.*, ins.id from CASES c, INSPECTOR ins
where c.id = &intID
and ins.id (+) = decode(c.inspector_id,1,null,c.inspector_id)

Cheers
HekiSelect c.*, ins.id
from CASES c
LEFT OUTER JOIN
INSPECTOR ins ON
ins.id = decode(c.inspector_id,1,null,c.inspector_id) AND
c.id = &intID;|||sql server doesn't support DECODE

select c.*
, ins.id
from CASES c
left outer
join INSPECTOR ins
on case when c.inspector_id = 1
then null
else c.inspector_id end = ins.id
and c.id = &intID

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?

osql batch processing without -i

Hi,
I have just started using MSSQL and the DOS environment at work. I have
a lot of experience with Sybase and the UNIX environment, but this is a
whole new ball of wax.
I'd like to use osql from a batch file to log into the dataserver and
run a fairly long list of SQL and then exit. I don't want to have a bunch
of SQL files sitting around that I have to use the -i option to run, and
I'd rather not create temporary SQL files like this.

echo "exec sp_who2" >tmp.sql
echo "select * from...... " >>tmp.sql
osql -E -S <DATASERVER> -n -w999 -i tmp.sql
del tmp.sql

I can't use the -Q option, of course because as I said, I'll be writing
quite a few lines of SQL, and it won't all fit on the one line, or at least
it wouldn't be pretty if I did.

In UNIX, I can simply execute the following from either the command line
or in
a script.

isql -S<DATASERVER> -U<USER> <<-EOF

sp_who
go
select * from ....
go
EOF --EOF is the isql session terminator exits me back to the command line.

This behavior does not appear to work with OSQL. There is a -O option
which help mentions, and that talks about disabling the EOF terminator for
batch processing, but I wasn't able to find any usage or examples on the net
where someone is using EOF to terminate their OSQL SQL batch.

This is what help listed.
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1

Any help you could offer would be appreciated. Thanks.

DarrenOn Mon, 16 Jan 2006 18:44:19 -0600, <dphillip79@.comcast.net> wrote:

(snip)
> In UNIX, I can simply execute the following from either the command line
>or in
>a script.
>isql -S<DATASERVER> -U<USER> <<-EOF
>sp_who
>go
>select * from ....
>go
>EOF --EOF is the isql session terminator exits me back to the command line.
> This behavior does not appear to work with OSQL.

Hi,

It's not OSQL, it's Windows/DOS that is causing the difference. In UNIX,
utilities take their input from STDIN, which is either the next line in
the script (if run from a script) or the next line entered on the
console (if run from a console).

The same thing happens if you create a script (testme.bat) with the
following content:

copy con test.txt
This won't work

Execute it. The copy con command is started and the console will wait
for your input. Type one or more lines, then type Ctrl-Z (the EOF
marker). You'll next see an error because "Thins won't work" is not a
valid DOS command.

An equivalent script in Unix would enter "This won't work" in the file
test.txt.

> Any help you could offer would be appreciated.

I think that you'll have to settle for either a bunch of commonly used
SQL script files, or for dynamically building the SQL script using a
bunch of redirected echo statements.

--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:raiqs1pfephh7faud9sesnjot56c7tdng9@.4ax.com...
> On Mon, 16 Jan 2006 18:44:19 -0600, <dphillip79@.comcast.net> wrote:
> (snip)
> > In UNIX, I can simply execute the following from either the command
line
> >or in
> >a script.
> >isql -S<DATASERVER> -U<USER> <<-EOF
> >sp_who
> >go
> >select * from ....
> >go
> >EOF --EOF is the isql session terminator exits me back to the command
line.
> > This behavior does not appear to work with OSQL.
> Hi,
> It's not OSQL, it's Windows/DOS that is causing the difference. In UNIX,
> utilities take their input from STDIN, which is either the next line in
> the script (if run from a script) or the next line entered on the
> console (if run from a console).
> The same thing happens if you create a script (testme.bat) with the
> following content:
> copy con test.txt
> This won't work
> Execute it. The copy con command is started and the console will wait
> for your input. Type one or more lines, then type Ctrl-Z (the EOF
> marker). You'll next see an error because "Thins won't work" is not a
> valid DOS command.
> An equivalent script in Unix would enter "This won't work" in the file
> test.txt.
> > Any help you could offer would be appreciated.
> I think that you'll have to settle for either a bunch of commonly used
> SQL script files, or for dynamically building the SQL script using a
> bunch of redirected echo statements.

Thanks for the Info Hugo. I was afraid of that. I'm used being able to
do whatever I want in UNIX, so DOS seems to be a bit of a step back for me.
I did just download a version of sed for DOS, which I'm really excited
about. :) I also have a VB .net class next week, so I'm hoping to pick up
some tricks in there. Thanks again.

Darren

> --
> Hugo Kornelis, SQL Server MVP

os partition problems

Hi,
I recently installed MSSQL 2000 and sp3a onto a windows 2003 server in a test lab. I configured one big c: partion on this os and installed the db in the default location.

I need to detach the db's on this server and re-attach them onto another MSSQL 2000/sp3a server running 2003 os with a partition scheme like this:

c: = 20 gigs for the os
e: = 600 gigs for the data

I could not re-attach the db's onto the e:\default path\to\database

Is there a work-around for this? This makes sense to me as to why it is not working and was an install oversight on my part but there has to be a way to overcome this delima?

Thanks in advance,
damnitYou can change the location of the files.

EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

OS backup

Hello.

New MSSQL guy coming from Oracle. I've read and understand that you can create backup files for a database using the server management studio.

However, I'm wondering if that is really necessary. Suppose I do not care about the loss of transaction logs, is copying the .mdf database files via OS command a viable backup strategy? If so do I need to bring down the database engine before starting the file copy?

Thanks a lot

You can get a valid backup of a SQL Server database by shutting down the instance and then taking a full OS backup, but you do lose significant functionality by doing so:

Obviously, you're giving up the ability to take your backups with the database online. Native SQL database backups are all online.|||Thank you Kevin.

Our production operations run on Oracle systems. We have to run 1 small MSSQL instance since some MSFT application only use that. So, I'm not really concerned about that DB's size, around-the-clock availibility, or point-in-time recovery.

Given the above requirements and the fact that I have no experience in MSSQL, backing up via the OS route seems to be the path of least resistence :)

Thanks again!