Outer Joins and Substring Error

My problem exists on the last line - TOAD doesn't seem to like the two sets of parenthesis together. Am I supposed to put something in between? It's OK in the line above because the substring is on the other side.

SELECT distinct fp.active_fl, fp.empl_id, fp.proj_mgr_name, pm.project, fp.subctr_id, rd.CONT_CRNCY
FROM planner.rbws_proj_mgr pm, finance.fin_proj fp, finance.rbws_detl rd
WHERE SUBSTR(fp.proj_id, 1, 9) = pm.project (+)
AND pm.PROJECT = substr(rd.PROJ_ID,1,9) (+)Operative word being "think", I don't think that any version of TOAD can use Oracle outer join syntax in Microsoft SQL Server.

-PatP|||Oracle 8i or less no doubt

SELECT DISTINCT fp.active_fl
, fp.empl_id
, fp.proj_mgr_name
, pm.project
, fp.subctr_id
, rd.CONT_CRNCY
FROM planner.rbws_proj_mgr pm
, finance.fin_proj fp
, finance.rbws_detl rd
WHERE SUBSTR(fp.proj_id, 1, 9) *= pm.project
AND pm.PROJECT *= substr(rd.PROJ_ID,1,9)

Is that the old SQL Server syntax? I thankfully don't remember and have no desire to look it up...speaking of desiree...where is she?

Where is my Desire?

OTP: Jamie Thomson, ssis man?

are you sleeping inside a Data Flow? Where exists SSIS you are

LOL... he's already a SSIS legend

|||

There are no social forums here, this is not a question and therefore off topic. It's OK to make a social comment within a thread, but creating non-question threads pollutes the unanswered question area, and potentially the search.

|||you're right cgraus. I'll keep in mind

osql with script trouble...

I have a sql file that contains:
if EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name ='newShopcart')
DROP DATABASE [newShopcart];
CREATE DATABASE [newShopcart] ON (NAME ='newShopcart_dat', FILENAME ='C:\MSSQL7\DATA\newShopcart.mdf' , SIZE = 10, FILEGROWTH =
10%) LOG ON (NAME ='newShopcart_log', FILENAME ='C:\MSSQL7\DATA\newShopcart.ldf' , SIZE = 10, FILEGROWTH = 10%);
USE [newShopcart];
GO
And I call this using osql in this way:
osql -D TLC -U sa -P thepassword -i MakeDB.sql -o MakeDB.log -n
I get the following in the resulting log:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'i'.
I've noticed that changing the first line to USE [master] changes the log to:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'U'.
This leads me to believe that it sees the first character of the file as the name of a Stored Procedure.
Any idea how to get around this?
Thanks in advance.
Mandy wrote:
> I have a sql file that contains:
> if EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name
> ='newShopcart')
> DROP DATABASE [newShopcart];
> CREATE DATABASE [newShopcart] ON (NAME ='newShopcart_dat', FILENAME
> ='C:\MSSQL7\DATA\newShopcart.mdf' , SIZE = 10, FILEGROWTH = 10%) LOG
> ON (NAME ='newShopcart_log', FILENAME
> ='C:\MSSQL7\DATA\newShopcart.ldf' , SIZE = 10, FILEGROWTH = 10%);
> USE [newShopcart];
> GO
> And I call this using osql in this way:
> osql -D TLC -U sa -P thepassword -i MakeDB.sql -o MakeDB.log -n
> I get the following in the resulting log:
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'i'.
> I've noticed that changing the first line to USE [master] changes the
> log to:
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'U'.
> This leads me to believe that it sees the first character of the file
> as the name of a Stored Procedure.
> Any idea how to get around this?
> Thanks in advance.
Does this code work from Query Analyzer? I see on your last line you
are using a semi-colon and a go. Does OSQL understand "go"? What's the
"n" option for and do you need it?
David Gugick
Imceda Software
www.imceda.com
|||It does work under QA. -n removes line numbers from results.

> Does this code work from Query Analyzer? I see on your last line you
> are using a semi-colon and a go. Does OSQL understand "go"? What's the
> "n" option for and do you need it?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Are you sure this is an odbc data source and not just a regular SQL Server
connection? What happens if you remove the -D option and value? You could
also try adding the option -e to echo the commands as they are read, for
troubleshooting.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mandy" <clafarge@.NOSPAMgraphiced.com> wrote in message
news:eAn4RG%232EHA.1192@.tk2msftngp13.phx.gbl...
>I have a sql file that contains:
> if EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name
> ='newShopcart')
> DROP DATABASE [newShopcart];
> CREATE DATABASE [newShopcart] ON (NAME ='newShopcart_dat', FILENAME
> ='C:\MSSQL7\DATA\newShopcart.mdf' , SIZE = 10, FILEGROWTH =
> 10%) LOG ON (NAME ='newShopcart_log', FILENAME
> ='C:\MSSQL7\DATA\newShopcart.ldf' , SIZE = 10, FILEGROWTH = 10%);
> USE [newShopcart];
> GO
> And I call this using osql in this way:
> osql -D TLC -U sa -P thepassword -i MakeDB.sql -o MakeDB.log -n
> I get the following in the resulting log:
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'i'.
> I've noticed that changing the first line to USE [master] changes the log
> to:
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'U'.
> This leads me to believe that it sees the first character of the file as
> the name of a Stored Procedure.
> Any idea how to get around this?
> Thanks in advance.
>
|||I changed to specifying -S and ip address. When I added -e, it shows only one line of output:
I
So it still appears to only see the first character of the file.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message news:%23PyX8MA3EHA.1396@.tk2msftngp13.phx.gbl...
> Are you sure this is an odbc data source and not just a regular SQL Server
> connection? What happens if you remove the -D option and value? You could
> also try adding the option -e to echo the commands as they are read, for
> troubleshooting.
|||-S is usually followed by the server name.
What version of SQL Server? Are you running osql remotely or locally?
Can you verify the version of osql?
How did you create the .sql file?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mandy" <clafarge@.NOSPAMgraphiced.com> wrote in message
news:Ogr83FH3EHA.2312@.TK2MSFTNGP15.phx.gbl...
>I changed to specifying -S and ip address. When I added -e, it shows only
>one line of output:
> I
> So it still appears to only see the first character of the file.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23PyX8MA3EHA.1396@.tk2msftngp13.phx.gbl...
>
|||I tried -S with ip and also server name.
MSSQL Version 7
osql on my machine, Server on network
osql version 2000.80.194.0
created script by Right-click on existing DB in Enterrprise and "Generate SQL Script", then changing names as needed for new
database.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message news:u0bXMoH3EHA.1300@.TK2MSFTNGP14.phx.gbl...
> -S is usually followed by the server name.
> What version of SQL Server? Are you running osql remotely or locally?
> Can you verify the version of osql?
> How did you create the .sql file?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Mandy" <clafarge@.NOSPAMgraphiced.com> wrote in message
> news:Ogr83FH3EHA.2312@.TK2MSFTNGP15.phx.gbl...
>
|||Mandy wrote:
> I tried -S with ip and also server name.
> MSSQL Version 7
> osql on my machine, Server on network
> osql version 2000.80.194.0
> created script by Right-click on existing DB in Enterrprise and
> "Generate SQL Script", then changing names as needed for new database.
>
You really should patch your client tools. You are running the retail
release of OSQL.EXE from years ago. Apply the latest SQL Server SP3a
service pack to your client PC.
Also, try running a very simple script like "Select * from sysobjects"
from a script file and see if that works.
David Gugick
Imceda Software
www.imceda.com
|||Thanks for the pointer...
When I changed to a Select, I get:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'S'.
Is there any reason why it would only see the first character?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:uBnghHJ3EHA.524@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Mandy wrote:
|||Mandy wrote:[vbcol=seagreen]
> Thanks for the pointer...
> When I changed to a Select, I get:
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'S'.
> Is there any reason why it would only see the first character?
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:uBnghHJ3EHA.524@.TK2MSFTNGP09.phx.gbl...
What format are you saving the file in? Is it unicode? Maybe OSQL cannot
read unicode files and requires an ANSI formatted file.
David Gugick
Imceda Software
www.imceda.com

osql vs Query Analyzer

We have a script with code like this
use master
go
-- do some stuff here
if exists (select name from sysdatabases where name = 'db_name')
begin
use db_name
-- do some stuff
exec storedproc
end
this works fine with Query Analyzer.
With OSQL it creates problem in the line exec storedproc. It says that
the stored procedure is not found.
we call osql as follows
osql -S servername -U username -P Password -i sqlfilename
now if we add the database name to osql
osql -S servername -U username -P Password -i sqlfilename -d db_name
it works fine.
Why should it matter. After all we are using USE statement inside the
script correctly.
How does it work in Query Analyzer.
TIA.
That should work. Can you post code which we can run that displays this?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1120679914.292796.77430@.g44g2000cwa.googlegro ups.com...
> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>
|||Hi
You may want to use three part naming for the procedure call.
John
"Data Cruncher" wrote:

> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>

osql vs Query Analyzer

We have a script with code like this
use master
go
-- do some stuff here
if exists (select name from sysdatabases where name = 'db_name')
begin
use db_name
-- do some stuff
exec storedproc
end
this works fine with Query Analyzer.
With OSQL it creates problem in the line exec storedproc. It says that
the stored procedure is not found.
we call osql as follows
osql -S servername -U username -P Password -i sqlfilename
now if we add the database name to osql
osql -S servername -U username -P Password -i sqlfilename -d db_name
it works fine.
Why should it matter. After all we are using USE statement inside the
script correctly.
How does it work in Query Analyzer.
TIA.That should work. Can you post code which we can run that displays this?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1120679914.292796.77430@.g44g2000cwa.googlegroups.com...
> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>|||Hi
You may want to use three part naming for the procedure call.
John
"Data Cruncher" wrote:

> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>

osql vs Query Analyzer

We have a script with code like this
use master
go
-- do some stuff here
if exists (select name from sysdatabases where name = 'db_name')
begin
use db_name
-- do some stuff
exec storedproc
end
this works fine with Query Analyzer.
With OSQL it creates problem in the line exec storedproc. It says that
the stored procedure is not found.
we call osql as follows
osql -S servername -U username -P Password -i sqlfilename
now if we add the database name to osql
osql -S servername -U username -P Password -i sqlfilename -d db_name
it works fine.
Why should it matter. After all we are using USE statement inside the
script correctly.
How does it work in Query Analyzer.
TIA.That should work. Can you post code which we can run that displays this?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1120679914.292796.77430@.g44g2000cwa.googlegroups.com...
> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>|||Hi
You may want to use three part naming for the procedure call.
John
"Data Cruncher" wrote:
> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>

OSQL command for checking to see if a DB exists

What is the OSQL command for checking to see if a database exists (for MSDE
2000)
I am getting an error after my install that says database does not exist or
access denied. Not sure about the best way to troubleshoot this.
Thanks.
STom
Select DB_ID('Northwind')
6
(1 row(s) affected)
Select DB_ID('SomeDBThatDoesntexists')
NULL
(1 row(s) affected)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"STom" <stombiztalker@.hotmail.com> schrieb im Newsbeitrag
news:eYMp39CbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> What is the OSQL command for checking to see if a database exists (for
> MSDE 2000)
> I am getting an error after my install that says database does not exist
> or access denied. Not sure about the best way to troubleshoot this.
> Thanks.
> STom
>
|||Do you mean from the command line:
osql Select DB_ID('Northwind') ...I'm pretty sure you meant to preface it
with more commands.
The first thing I would like to do is make sure that the password and
username that I think is supposed to be used is being used. If this is
correct, then I would like to check to see if the database exists.
What is the command line for this?
Thanks!
STom
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uv91zDDbFHA.348@.TK2MSFTNGP14.phx.gbl...
> Select DB_ID('Northwind')
> --
> 6
> (1 row(s) affected)
>
> Select DB_ID('SomeDBThatDoesntexists')
> --
> NULL
> (1 row(s) affected)
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "STom" <stombiztalker@.hotmail.com> schrieb im Newsbeitrag
> news:eYMp39CbFHA.2968@.TK2MSFTNGP10.phx.gbl...
>
|||hi STomn
STom wrote:
> I am getting an error after my install that says database does not
> exist or access denied. Not sure about the best way to troubleshoot
> this.
the error you probably get is "SQL Server does not exists or access denied",
which is a generic MDAC error..
please have a look at
http://support.microsoft.com/default...06&Product=sql
if helps..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi,
STom wrote:
> Do you mean from the command line:
> osql Select DB_ID('Northwind') ...I'm pretty sure you meant to
> preface it with more commands.
actually Jens intended for sure you were already logged in with oSql.exe..

> The first thing I would like to do is make sure that the password and
> username that I think is supposed to be used is being used. If this is
> correct, then I would like to check to see if the database exists.
> What is the command line for this?
there's non command line for that...
you can execute a call to oSql.exe providing a -Q"SELECT ..." and inspect
results, like
c:\..\>osql -Usa -Ppwd -Q"SELECT DB_ID('Northwind')" -oC:\err.txt
wich will output, in the C:\err.txt file, something like
--<--
6
(1 row affected)
if the correct userid and pwd are provided, else something like
--<--
Login failed for user 'sa'.
will be reported if a wrong pwd is provided...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply