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...
>
>