Outer Join Syntax Problems (Multiple Tables)

Hello all--
I'm trying to run a SELECT on 3 tables:Class,Enrolled,Waiting.
I want to select the name of the class, the count of the students enrolled, and the count of the students waiting to enroll.
My current query...
SELECT Class.Name, COUNT(Enrolled.StudentID) AS EnrolledCount, COUNT(Waiting.StudentID) AS WaitingCount
FROM Class LEFT OUTER JOIN
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
Waiting ON Class.ClassID = Waiting.ClassID
GROUP BY Class.Name
...results in identical counts for enrolled and waiting, which I knowto be incorrect. Furthermore, it appears that the counts are beingmultiplied together (in one instance, enrolled should be 14, waitingshould be 2, but both numbers come back as 28).
If I run this query without one of the joined tables, the counts areaccurate. The problem only occurs when I try to pull counts from boththe tables.
Can anyone find the problem with my query? Should I be using something other than a LEFT OUTER JOIN?
Thanks very much for your time,
--Jeremy
Run this query and you'll see what it's doing:
SELECT Class.Name, Enrolled.StudentID AS EnrolledCount, Waiting.StudentID AS WaitingCount
FROM Class LEFT OUTER JOIN
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
Waiting ON Class.ClassID = Waiting.ClassID
Something like this will work:
SELECT c.Name, e.EnrolledCount, w.WaitingCount
FROM Class c
LEFT OUTER JOIN
(select classid, COUNT(*) AS EnrolledCount
from Enrolled
group by classid) e
on c.classid = e.classid
LEFT OUTER JOIN
(select classid, COUNT(*) AS WaitingCount
from Waiting
group by classid) w
on c.classid = w.classid
There's other ways to do it with subqueries. Something like this would also work
select c.classname, (select count(*) from enrolled where classid = c.classid) as Enrolled, (select count(*) from waiting where classid = c.classid) as Waiting
from Class c|||Thanks, PDraigh. I used your subquery example and it worked great.
Thanks!
--Jeremy

Outer Join syntax

I am now working on SQL Server 2000 having had previous experience on
a different database. Both of the OUTER JOIN syntaxes is different
from what I am used to and I am finding it slightly confusing.

For example, given two tables :

wipm_tbl_mi
wipm_tbl_wi (which may not have data in it for a specific record that
exists in the first table.)

If I use the old style syntax :

SELECT mi.workitemid, wi.datavalue
FROM wipm_tbl_mi mi , wipm_tbl_wi wi
WHERE mi.workitemid *= wi.workitemid
AND mi.workitemid = 1
AND wi.dataname = 'XXX'

I get back

1,NULL

when there is no matching record in wipm_tbl_wi, which is what I
expect.

However, if I try to use the SQL-92 syntax

SELECT mi.workitemid, wi.datavalue
FROM wipm_tbl_mi mi
LEFT OUTER JOIN wipm_tbl_wi wi
ON mi.workitemid = wi.workitemid
WHERE mi.workitemid = 1
AND wi.dataname = 'XXX'

I don't get anything back. Please can someone help me understand what
is wrong with the bottom query.

Thank you,

Martin"Martin" <cook_ml@.hotmail.com> wrote in message
news:ac63e8cd.0401300807.25d1f38f@.posting.google.c om...
> I am now working on SQL Server 2000 having had previous experience on
> a different database. Both of the OUTER JOIN syntaxes is different
> from what I am used to and I am finding it slightly confusing.
> For example, given two tables :
> wipm_tbl_mi
> wipm_tbl_wi (which may not have data in it for a specific record that
> exists in the first table.)
> If I use the old style syntax :
> SELECT mi.workitemid, wi.datavalue
> FROM wipm_tbl_mi mi , wipm_tbl_wi wi
> WHERE mi.workitemid *= wi.workitemid
> AND mi.workitemid = 1
> AND wi.dataname = 'XXX'
> I get back
> 1,NULL
> when there is no matching record in wipm_tbl_wi, which is what I
> expect.
> However, if I try to use the SQL-92 syntax
> SELECT mi.workitemid, wi.datavalue
> FROM wipm_tbl_mi mi
> LEFT OUTER JOIN wipm_tbl_wi wi
> ON mi.workitemid = wi.workitemid
> WHERE mi.workitemid = 1
> AND wi.dataname = 'XXX'
> I don't get anything back. Please can someone help me understand what
> is wrong with the bottom query.
> Thank you,
> Martin

*= is the obsolete proprietary syntax used for outer joins by SQL Server
prior to version 6.5. OUTER JOIN is the Standard SQL way of doing
outer joins and should always be used. However, it's not simply syntactically
different but semantically different than *=. With outer joins, the join condition
must be evaluated separate from the condition applied to the rows resulting
from the join. The Standard SQL solution allows for this by being able to
specifiy the join condition for the outer join in the FROM clause and
the condition to be applied to the resulting rows of the outer join in the
WHERE clause. The *= syntax at best doesn't make this clear and at
worst won't allow you to specify the result you're looking for since both
kinds of conditions, for the join and the restriction of subsequent rows,
are specified in the WHERE clause.

It's always helpful to provide sample data in the form of INSERT
statements so a proposed solution can be tested, but try

SELECT mi.workitemid, wi.datavalue
FROM wipm_tbl_mi mi
LEFT OUTER JOIN
wipm_tbl_wi wi
ON mi.workitemid = wi.workitemid AND
wi.dataname = 'XXX'
WHERE mi.workitemid = 1

From the LEFT OUTER JOIN condition, every row of mi will be joined to
all rows in wi where mi.workitemid = wi.workitemid and wi.dataname = 'XXX'.
All rows of mi that don't satisfy this condition are joined to a single row
of all NULL values to indicate that there's no matching row from wi.
From the resulting outer join, the WHERE clause only keeps those rows
where mi.workitemid = 1. This will keep rows where wi.dataname is 'XXX'
or NULL, which is your desired result.

Regards,
jag|||Thank-you, Jag. Your explanation has helped me understand the general
point, and your suggested SQL has solved my specific problem.

Martin

OUTER JOIN on more than two tables

Two questions:
1) Can an OUTER JOIN, such as a LEFT OUTER JOIN be done on more than two
tables and, if so, what is the syntax for it ?
I want to do a LEFT OUTER JOIN so that all rows from my left table are
selected which meet my where conditions, and where matching rows from
more than one other table are selected based on a match between my left
table and the other tables.
2) In the above scenario, one of the matches occurs between a column
value in my non-left table and a column value in another one of my
non-left tables. How can this also be specified in a LEFT OUTER JOIN
with more than one table in my LEFT OUTER JOIN syntax ?
Example:
Table1: ColumnA, ColumnB, other columns etc.
Table2: ColumnC, ColumnD
Table3: ColumnE, ColumnF
Table4: ColumnG, ColumnH
I want to join these tables such that all rows and columns from Table1
are selected matching my where condition. ColumnC is also selected when
ColumnA matches ColumnD, else ColumnC is null. ColumnE is also selected
when ColumnA matches ColumnF, else ColumnE is null. Finally ColumnG is
also selected when Table3's ColumnE matches ColumnH, else ColumnG is null.For example:
SELECT *
FROM TableA AS A
LEFT JOIN TableB AS B
ON A.x = B.x
LEFT JOIN TableC AS C
ON B.x = C.x
If that doesn't answer the question then please post DDL and sample data:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Edward,see it this helps you
SELECT
ColumnA,
ColumnB,
CASE
WHEN EXISTS (
SELECT ColumnC FROM Table2 t2
WHERE t2.ColumnD = table1.ColumnA)
THEN 1
ELSE 0
END AS ColumnC,
CASE
WHEN EXISTS (
SELECT ColumnE FROM Table3 t3
WHERE t3.ColumnF = table1.ColumnA)
THEN 1
ELSE 0
END AS ColumnE,
CASE
WHEN EXISTS (
SELECT ColumnG FROM Table4 t4
WHERE t3.ColumnH = table1.ColumnA)
THEN 1
ELSE 0
END AS ColumnE
FROM Table1
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:O656jo9UFHA.2036@.TK2MSFTNGP10.phx.gbl...
> Two questions:
> 1) Can an OUTER JOIN, such as a LEFT OUTER JOIN be done on more than two
> tables and, if so, what is the syntax for it ?
> I want to do a LEFT OUTER JOIN so that all rows from my left table are
> selected which meet my where conditions, and where matching rows from
> more than one other table are selected based on a match between my left
> table and the other tables.
> 2) In the above scenario, one of the matches occurs between a column
> value in my non-left table and a column value in another one of my
> non-left tables. How can this also be specified in a LEFT OUTER JOIN
> with more than one table in my LEFT OUTER JOIN syntax ?
> Example:
> Table1: ColumnA, ColumnB, other columns etc.
> Table2: ColumnC, ColumnD
> Table3: ColumnE, ColumnF
> Table4: ColumnG, ColumnH
> I want to join these tables such that all rows and columns from Table1
> are selected matching my where condition. ColumnC is also selected when
> ColumnA matches ColumnD, else ColumnC is null. ColumnE is also selected
> when ColumnA matches ColumnF, else ColumnE is null. Finally ColumnG is
> also selected when Table3's ColumnE matches ColumnH, else ColumnG is null.|||Edward,
Conceptually. each "Join" is a join between only two "Relations", or
"Resultsets". When you have more than two tables in a From Clause, and,
therefore, you have two or more joins. the second "Join" that takes place ca
n
be thought of as a Join between the intermediate resultset created by the
first join, and the third table. So the answer to your question depends on
what order, and what exactly, you wish to Join in this second Join...
Two possibilities exist:
You could Join Tables B to A, using Outer Join syntax, and then Join C to
that resultset, also using Outer Join SyntAX...
From TableA
Left Outer Join Table B On .....
Left Outer Join Table C On ......
Or 2) you might be wishing to Join the COmbined Inner Join of Tables B & C
to Table A. In this case you would be joining B & C FIrst, and then Joining
THAT resultset to TableA using Outer Join Syntax
From TableA
Left Outer Join (Table B Join Table C On ....)
On ....
This approach might be used to get ALL Customers, (even the ones with no
Invoices), plus the data from a Invoices and connected Invoice Details table
s
, but only include invoices that have details...
"Edward Diener" wrote:

> Two questions:
> 1) Can an OUTER JOIN, such as a LEFT OUTER JOIN be done on more than two
> tables and, if so, what is the syntax for it ?
> I want to do a LEFT OUTER JOIN so that all rows from my left table are
> selected which meet my where conditions, and where matching rows from
> more than one other table are selected based on a match between my left
> table and the other tables.
> 2) In the above scenario, one of the matches occurs between a column
> value in my non-left table and a column value in another one of my
> non-left tables. How can this also be specified in a LEFT OUTER JOIN
> with more than one table in my LEFT OUTER JOIN syntax ?
> Example:
> Table1: ColumnA, ColumnB, other columns etc.
> Table2: ColumnC, ColumnD
> Table3: ColumnE, ColumnF
> Table4: ColumnG, ColumnH
> I want to join these tables such that all rows and columns from Table1
> are selected matching my where condition. ColumnC is also selected when
> ColumnA matches ColumnD, else ColumnC is null. ColumnE is also selected
> when ColumnA matches ColumnF, else ColumnE is null. Finally ColumnG is
> also selected when Table3's ColumnE matches ColumnH, else ColumnG is null.
>sql

Out Join Syntax

Hello,

I need to write and Ansi Outer Join that has 2 columns from the same table.

I keep getting

Server: Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "dim_person.person_key" could not be bound.

here is what the code looks like. any help is appreciated.

select ...

from dim_day_of_year
, dim_company
, dim_currency
, dim_account
, dim_person
, ods_conference
left outer join fact_usage_alloc fua1 on ods_conference.conf_key=fua1.conf_key
left outer join fact_usage_alloc fua2
on dim_person.person_key=fua2.requestor_person_key

where...from ...
, ods_conference
left outer
join fact_usage_alloc fua
on fua.conf_key = ods_conference.conf_key
and fua.requestor_person_key = dim_person.person_key|||That certainly makes sense to me but produces a syntax error :mad:

left outer join fact_usage_alloc fua
on fua.conf_key=ods_conference.conf_key
and on fua.requestor_person_key=dim_person.person_key
where fua.company_key = dim_company.company_key...........

Thanks for the reply|||you're welcome

you can't say "and on"|||hmmm. Still getting the bind error. I even cut and pasted your example in and I get the same thing. I didn't realize mssql was so particular :S|||okay, here's the deal: re-write your other comma-delimited table joins as INNER JOINs

i bet the error goes away ;)|||Mixing non ansi and ansi forms is just a bad idea

Plus, unless I missing it, your outter join tables don't seem to be joing to anything

Plus, you really should get into good formatting, it will make your like so much easier

SELECT ColumnList
FROM dim_day_of_year a
INNER JOIN dim_company b
ON b.col = ?.col
INNER JOIN dim_currency c
ON c.col = ?.col
INNER JOIN dim_account d
ON d.col =?.col
INNER JOIN dim_person e
ON e.col = ?.col
INNER JOIN ods_conference f
ON f.col = ?.col
LEFT JOIN fact_usage_alloc g
ON e.conf_key = f.conf_key -- This one makes no sense
LEFT JOIN fact_usage_alloc h
ON d.person_ke = g.requestor_person_key -- Nor does this one|||okay, here's the deal: re-write your other comma-delimited table joins as INNER JOINs

i bet the error goes away ;)

You funny

The message you have enetered is too short|||You funnyno, i was serious -- i bet the error goes away because of how the FROM clause is parsed

the explicit JOINS are probably being interpreted first, before being combined with the comma-delimited tables

sort of like * and / are done before + and -

thus, in this local context:
ods_conference
left outer
join fact_usage_alloc fua
on fua.conf_key = ods_conference.conf_key
and fua.requestor_person_key = dim_person.person_keythis explains the error message -- "dim_person.person_key" could not be bound

dim_person hasn't been parsed as a table yet|||Thanks for the effort guys. This was a quick test to see how easy it would be to convert a legacy system to MSSQL. I think we got a portion of the answer.

Thanks Again.sql

Osql Syntax Help

Dear friends,

I was trying to run this script from a dos batch file under win xp but it's not working. Please help with the syntax.
================================================== =======
OSQL -U sa -P samsde -S firebird /Q "RESTORE DATABASE NAVIMEX FROM DISK = 'C:\30704' WITH

REPLACE, MOVE'NAVIMEX_Data' TO 'C:\Program Files\Microsoft SQL

Server\MSSQL\Data\NAVIMEX_Data.MDF',MOVE 'NAVIMEX_Log' TO 'C:\Program Files\Microsoft SQL

Server\MSSQL\Data\NAVIMEX_LOG.LDF'" QUIT
================================================== ========

Please provide me with the correct syntax to be put in a 'restore.bat' file :rolleyes:

Thanks in advance.
HotBirdWhat errors are you getting? You can add "/o<filename>" or use command line redirection to capture all errors. And don't use QUIT unless your RESTORE was unsuccessful.|||make sure that you dont hit enter after "microsoft sql" in the path
try a shorter path
also i believe that when you issue a restore cmd from disk, you have to specify the file name
ex
from disk = 'c:\data\northwind.mdf'

to back up robert, what errors do you get?|||Dear Friends,

Thank you for your replies,

The script works but I'm facing a problem while trying to restore from a network drive or a remote path (\\xxx.xxx.xxx.xxx\mypath\filename)

can you help please?

Regards,

Samer Chaer :rolleyes:|||It's usually due to a lack of necessary permissions on behalf of the security context under which the process is invoked.

osql syntax

Hi
I am running osql utility like this...
C:\Temp>osql -U sa -P 5a -S %ServerName% -d pubs -Q "select title, notes
from titles" -o .\out.txt -s"," -w300 -h-1
And it produces the following in "out.txt":
== start of output ==
The Busy Executive's Database Guide
,An overview of available database systems with emphasis on common business
applications. Illustrated.
...
...
...
Sushi, Anyone?
,Detailed instructions on how to make authentic Japanese sushi in your spare
time.
(18 rows affected)
== end of output ==
Can anybody tell me
(a) how to remove the "(18 rows affected)" comment
(b) how to remove the %FieldValue% right padding. Ideally I'd like the
output to be
== start of output ==
%FieldValue%,%FieldValue%
%FieldValue%,%FieldValue%
== end of output ==
Thanks for any tips.
PS My actual queries will be bigger than bcp allows. I'd prefer not to have
to write the query as:
select '"' + title + '", "' + notes + '"' from titles
Cheers,
Neil Evans-Mudie
-. . .. .-.. / .--. ... -- -. .. -.-. .--. / . ...- .- -.
... -...- -- ..- -.. .. .
e: My@.myorg.com address is a spam sink
If you wish to email me, try neilevans underscore mudie at hotmail dot com
w: http://groups.msn.com/TheEvansMudieF...ew.msnw?&pps=k
Neil
to avoid the rowcount, you can use -Q "set nocount on select title, notes
from titles". As far as I understand, you can't avoid the fixed width nature
of the output, apart from the concatenation solution you propose.
HTH,
Paul Ibison

osql syntax

Hi
I am running osql utility like this...
C:\Temp>osql -U sa -P 5a -S %ServerName% -d pubs -Q "select title, notes
from titles" -o .\out.txt -s"," -w300 -h-1
And it produces the following in "out.txt":
== start of output ==
The Busy Executive's Database Guide
,An overview of available database systems with emphasis on common business
applications. Illustrated.
...
...
...
Sushi, Anyone?
,Detailed instructions on how to make authentic Japanese sushi in your spare
time.
(18 rows affected)
== end of output ==
Can anybody tell me
(a) how to remove the "(18 rows affected)" comment
(b) how to remove the %FieldValue% right padding. Ideally I'd like the
output to be
== start of output ==
%FieldValue%,%FieldValue%
%FieldValue%,%FieldValue%
== end of output ==
Thanks for any tips.
PS My actual queries will be bigger than bcp allows. I'd prefer not to have
to write the query as:
select '"' + title + '", "' + notes + '"' from titles
Cheers,
Neil Evans-Mudie
-. . .. .-.. / .--. ... -- -. .. -.-. .--. / . ...- .- -.
... -...- -- ..- -.. .. .
e: My@.myorg.com address is a spam sink
If you wish to email me, try neilevans underscore mudie at hotmail dot com
w: http://groups.msn.com/TheEvansMudie...new.msnw?&pps=kNeil
to avoid the rowcount, you can use -Q "set nocount on select title, notes
from titles". As far as I understand, you can't avoid the fixed width nature
of the output, apart from the concatenation solution you propose.
HTH,
Paul Ibison

osql syntax

Hi
I am running osql utility like this...
C:\Temp>osql -U sa -P 5a -S %ServerName% -d pubs -Q "select title, notes
from titles" -o .\out.txt -s"," -w300 -h-1
And it produces the following in "out.txt":
== start of output ==
The Busy Executive's Database Guide
,An overview of available database systems with emphasis on common business
applications. Illustrated.
...
...
...
Sushi, Anyone?
,Detailed instructions on how to make authentic Japanese sushi in your spare
time.
(18 rows affected)
== end of output ==
Can anybody tell me
(a) how to remove the "(18 rows affected)" comment
(b) how to remove the %FieldValue% right padding. Ideally I'd like the
output to be
== start of output ==
%FieldValue%,%FieldValue%
%FieldValue%,%FieldValue%
== end of output ==
Thanks for any tips.
PS My actual queries will be bigger than bcp allows. I'd prefer not to have
to write the query as:
select '"' + title + '", "' + notes + '"' from titles
Cheers,
Neil Evans-Mudie
-. . .. .-.. / .--. ... -- -. .. -.-. .--. / . ...- .- -.
... -...- -- ..- -.. .. .
e: My@.myorg.com address is a spam sink
If you wish to email me, try neilevans underscore mudie at hotmail dot com
w: http://groups.msn.com/TheEvansMudieFamily/_whatsnew.msnw?&pps=kNeil
to avoid the rowcount, you can use -Q "set nocount on select title, notes
from titles". As far as I understand, you can't avoid the fixed width nature
of the output, apart from the concatenation solution you propose.
HTH,
Paul Ibison

OSQL query command

I am trying to execute a job through the OSQL command but
the job never executes. Can anyone help me with the
syntax ?. I am executing it on the server:
C:\osql -E -d msdb -q "sp_start_job @.job_name = 'DB Backup
Job for DB Maintenance Plan TEST'"
If there is a way, I would also like to execute this
remote.
Thanks for any help.Hi,
Try firing the below query from Query analyzer .
msdb..sp_start_job @.job_name = 'DB Backup Job for DB Maintenance Plan TEST'"
Thanks
Hari
MCDBA
"Rick" <anonymous@.discussions.microsoft.com> wrote in message
news:b6e101c4377f$989bfb30$a601280a@.phx.gbl...
> I am trying to execute a job through the OSQL command but
> the job never executes. Can anyone help me with the
> syntax ?. I am executing it on the server:
> C:\osql -E -d msdb -q "sp_start_job @.job_name = 'DB Backup
> Job for DB Maintenance Plan TEST'"
> If there is a way, I would also like to execute this
> remote.
> Thanks for any help.|||Thanks. I found what was wrong. This syntax works:
osql -S MYSERVER -E -d msdb -Q "sp_start_job @.job_name
= 'DB Backup Job for DB Maintenance Plan TEST'
But now I have another problem. The job name actually is:
DB Backup Job for DB Maintenance Plan 'TEST' (Microsoft
Default when you create a maintenance plan)
so when I enter
'DB Backup Job for DB Maintenance Plan 'TEST''
it doesn't work.
How do I deal with the single quotes. Should I just rename
the jobs without single quotes ?
Thanks.
>--Original Message--
>Hi,
>Try firing the below query from Query analyzer .
>msdb..sp_start_job @.job_name = 'DB Backup Job for DB
Maintenance Plan TEST'"
>Thanks
>Hari
>MCDBA
>"Rick" <anonymous@.discussions.microsoft.com> wrote in
message
>news:b6e101c4377f$989bfb30$a601280a@.phx.gbl...
>> I am trying to execute a job through the OSQL command
but
>> the job never executes. Can anyone help me with the
>> syntax ?. I am executing it on the server:
>> C:\osql -E -d msdb -q "sp_start_job @.job_name = 'DB
Backup
>> Job for DB Maintenance Plan TEST'"
>> If there is a way, I would also like to execute this
>> remote.
>> Thanks for any help.
>
>.
>|||I got that too: ''TEST''' would do it.
Thanks.
>--Original Message--
>Thanks. I found what was wrong. This syntax works:
>osql -S MYSERVER -E -d msdb -Q "sp_start_job @.job_name
>= 'DB Backup Job for DB Maintenance Plan TEST'
>
>But now I have another problem. The job name actually is:
>DB Backup Job for DB Maintenance Plan 'TEST' (Microsoft
>Default when you create a maintenance plan)
>so when I enter
>'DB Backup Job for DB Maintenance Plan 'TEST''
>it doesn't work.
>How do I deal with the single quotes. Should I just
rename
>the jobs without single quotes ?
>Thanks.
>
>>--Original Message--
>>Hi,
>>Try firing the below query from Query analyzer .
>>msdb..sp_start_job @.job_name = 'DB Backup Job for DB
>Maintenance Plan TEST'"
>>Thanks
>>Hari
>>MCDBA
>>"Rick" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:b6e101c4377f$989bfb30$a601280a@.phx.gbl...
>> I am trying to execute a job through the OSQL command
>but
>> the job never executes. Can anyone help me with the
>> syntax ?. I am executing it on the server:
>> C:\osql -E -d msdb -q "sp_start_job @.job_name = 'DB
>Backup
>> Job for DB Maintenance Plan TEST'"
>> If there is a way, I would also like to execute this
>> remote.
>> Thanks for any help.
>>
>>.
>.
>

osgl syntax problem

I have a script that runs fine in SQL Query Analyzer but has a syntax error
using osql. It restores several databases and log files. One of the database
s
has a space in the database name. Similar to RESTORE DATABASE "XXXX XXXX"
FROM DISK (BLAH). When run using osql all the databases restore correctly
except for the database with the space in the name. SQL Query Analyzer has n
o
problems with the statement. Is there any way to make this work or do I have
to rename the database with no spaces?
--
Mark Koenig
Tucson, AZIdeally, you should have no spaces in object names, or you get the problem
you encountered. Try:
SET QUOTED IDENTIFIER ON
GO
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"KMK" <kkoenig@.email(DOT)arizona(DOT)edu> wrote in message
news:BFFEDC2D-C3BE-4365-8369-F6F1C7D6D875@.microsoft.com...
>I have a script that runs fine in SQL Query Analyzer but has a syntax error
> using osql. It restores several databases and log files. One of the
> databases
> has a space in the database name. Similar to RESTORE DATABASE "XXXX XXXX"
> FROM DISK (BLAH). When run using osql all the databases restore correctly
> except for the database with the space in the name. SQL Query Analyzer has
> no
> problems with the statement. Is there any way to make this work or do I
> have
> to rename the database with no spaces?
> --
> Mark Koenig
> Tucson, AZ|||It's been awhile since I've used osql but with embedded spaces, you might
need to use brackets. i.e. [My Database] Perhaps, enclose in double quote
marks. Or, single and double quote marks.
If this was a stored procedure, a fully qualified name would be:
[My Server].[My Databse].[My Owner].[sp Select Data]
I did some batch osql about a year ago and I had to send filenames into SQL
Server to generate and execute some bulk insert statements.
In DOS, I passed the filename to a common script that called SQL Server. In
the calling script, I passed the filename as "'C:\Program Files...TXT'".
Note, the single quotes inside the double quotes. Then in the common script
that called SQL Server, I had the following line:
@.osql -E -S %SQLServerName% -d %Database% -Q "exec %1 %~2"
Note: Parameter %1 contained the name of the stored procedure to execute.
Parameter %~2 is the fully qualified name of the input file.
In the stored procedure, I received the filename as varchar(128). When I
concatenated it into the bulk insert statement, I had to enclose it in singl
e
quote marks in order to accomodate embedded spaces. For example, the
statement ended up as BULK INSERT...FROM 'C:\PROGRAM FILES...TXT'. To embed
single quote marks just repeat it. So it's EXEC ('bulk insert ' + @.TableNam
e
+ ' from ''' + @.DataFilename + ''' with (tablock, firstrow = ' +
@.StartRowNumber + ', formatfile = ''' + @.FormatFilename + ''', maxerrors = '
+ @.MaxNumberOfErrors + ')')
Bottom line, you need double quote marks to pass a filename with embedded
spaces from one DOS/batch command script to another. (I don't know if you'r
e
doing this part or not.) Then, it has to be enclosed in single quote marks
for the osql statement. (At least it did for me using the -Q (query)
parameter.) Then, if your concatenating it in a stored procedure, you need
to embed the incoming value in single quote marks.
Hope that helps,
Joe
"KMK" wrote:

> I have a script that runs fine in SQL Query Analyzer but has a syntax erro
r
> using osql. It restores several databases and log files. One of the databa
ses
> has a space in the database name. Similar to RESTORE DATABASE "XXXX XXXX"
> FROM DISK (BLAH). When run using osql all the databases restore correctly
> except for the database with the space in the name. SQL Query Analyzer has
no
> problems with the statement. Is there any way to make this work or do I ha
ve
> to rename the database with no spaces?
> --
> Mark Koenig
> Tucson, AZ|||This did work after using SET QUOTED_IDENTIFIER ON. Your response left out
the underline. Thanks for the info!
--
Mark Koenig
Tucson, AZ
"Tom Moreau" wrote:

> Ideally, you should have no spaces in object names, or you get the problem
> you encountered. Try:
> SET QUOTED IDENTIFIER ON
> GO
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "KMK" <kkoenig@.email(DOT)arizona(DOT)edu> wrote in message
> news:BFFEDC2D-C3BE-4365-8369-F6F1C7D6D875@.microsoft.com...
>
>