Tuesday, February 14, 2012

Creating Database in OSQL Error

How can I create a new database on sql server using OSQL command in
command prompt. I have the sql file for database creation and I use the
following command

C:\> osql -S servername -U sa -i db1.sql
it prompt my for password n i enter it since sa has a blank password
but after that it returns the following error.

1> 2> 3> Msg 170, Level 15, State 1, Server servername, Procedure ,
Line 2
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
syntax near 'COLLATE'.
1> 2> 3> Msg 15010, Level 16, State 1, Server servername, Procedure
sp_dboption, Line 95
[Microsoft][ODBC SQL Server Driver][SQL Server]The database
'db1' does not exist. Use sp_helpdb to show available databases
Can anyone help me on that?This is just a guess since you didn't post the problem script but perhaps
the target is SQL Server 7 and your script was developed expecting SQL
Server 2000.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<arfanameer@.hotmail.com> wrote in message
news:1104123215.472796.136200@.z14g2000cwz.googlegr oups.com...
> How can I create a new database on sql server using OSQL command in
> command prompt. I have the sql file for database creation and I use the
> following command
> C:\> osql -S servername -U sa -i db1.sql
> it prompt my for password n i enter it since sa has a blank password
> but after that it returns the following error.
> 1> 2> 3> Msg 170, Level 15, State 1, Server servername, Procedure ,
> Line 2
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
> syntax near 'COLLATE'.
> 1> 2> 3> Msg 15010, Level 16, State 1, Server servername, Procedure
> sp_dboption, Line 95
> [Microsoft][ODBC SQL Server Driver][SQL Server]The database
> 'db1' does not exist. Use sp_helpdb to show available databases
> Can anyone help me on that?|||arfanameer@.hotmail.com (arfanameer@.hotmail.com) writes:
> How can I create a new database on sql server using OSQL command in
> command prompt. I have the sql file for database creation and I use the
> following command
> C:\> osql -S servername -U sa -i db1.sql
> it prompt my for password n i enter it since sa has a blank password

That's bad! I strongly recommend you to change the password.

If you insist on using a blank password, -P "", saves you from the prompt.

> but after that it returns the following error.
> 1> 2> 3> Msg 170, Level 15, State 1, Server servername, Procedure ,
> Line 2
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
> syntax near 'COLLATE'.
> 1> 2> 3> Msg 15010, Level 16, State 1, Server servername, Procedure
> sp_dboption, Line 95
> [Microsoft][ODBC SQL Server Driver][SQL Server]The database
> 'db1' does not exist. Use sp_helpdb to show available databases
> Can anyone help me on that?

It certainly helps if you post the script itself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You need to specify the master database as your target database by specifying
-dmaster

Also, make sure your script is correct and works without problem in your QA.

Joe
MCDBA, MCSD, OCP

>How can I create a new database on sql server using OSQL command in
>command prompt. I have the sql file for database creation and I use the
>following command
>C:\> osql -S servername -U sa -i db1.sql
>it prompt my for password n i enter it since sa has a blank password
>but after that it returns the following error.
>1> 2> 3> Msg 170, Level 15, State 1, Server servername, Procedure ,
>Line 2
>[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
>syntax near 'COLLATE'.
>1> 2> 3> Msg 15010, Level 16, State 1, Server servername, Procedure
>sp_dboption, Line 95
>[Microsoft][ODBC SQL Server Driver][SQL Server]The database
>'db1' does not exist. Use sp_helpdb to show available databases
>Can anyone help me on that?
>
>

No comments:

Post a Comment