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