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 and carc.:

I run a script with Osql and got no error but the accent caracter (french message) are modified.

the script is
CREATE PROCEDURE dbo.AccentTest
AS
Print(' ')
GO
and come
CREATE PROCEDURE dbo.AccentTest
AS
Print('Θ α Φ ε ∩ ∙')
GO
:o
This is very bad! Can someone can help me?
Any suggestion?
PLease....

osql.exe -S MyServer -U username-P pwd-d TestDB -n -o "C:\Dev\osql_test.log" -i "C:\Dev\AccentTest.sql" :What's the default language of the database? I don't have a lot of experience here, but I would start there. Also take a look at varchar and nvarchar.

[Edit: I should note that I have a default install of SQL Server 2000 (SP4). I created the proc as shown above and executed it and it printed the characters correctly.]

Regards,

hmscott

orphans user

I restored db from sql2000 to sql2005, i did fix the all
orphans users except SYS and DBO. How can i drop them from my db?
thanks
Read about sp_change_users_login in Books Online. Also Google for sp_help_revlogin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Viktor" <serguienkov@.hotmail.com> wrote in message news:OmbaV67yHHA.1188@.TK2MSFTNGP04.phx.gbl...
>I restored db from sql2000 to sql2005, i did fix the all
> orphans users except SYS and DBO. How can i drop them from my db?
> thanks
>

orphans user

I restored db from sql2000 to sql2005, i did fix the all
orphans users except SYS and DBO. How can i drop them from my db?
thanksRead about sp_change_users_login in Books Online. Also Google for sp_help_revlogin.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Viktor" <serguienkov@.hotmail.com> wrote in message news:OmbaV67yHHA.1188@.TK2MSFTNGP04.phx.gbl...
>I restored db from sql2000 to sql2005, i did fix the all
> orphans users except SYS and DBO. How can i drop them from my db?
> thanks
>

orphans user

I restored db from sql2000 to sql2005, i did fix the all
orphans users except SYS and DBO. How can i drop them from my db?
thanksRead about sp_change_users_login in Books Online. Also Google for sp_help_re
vlogin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Viktor" <serguienkov@.hotmail.com> wrote in message news:OmbaV67yHHA.1188@.TK2MSFTNGP04.phx.g
bl...
>I restored db from sql2000 to sql2005, i did fix the all
> orphans users except SYS and DBO. How can i drop them from my db?
> thanks
>

Orphaned Users - Spreading

Came into work this morning and found several database on different servers
had orphaned DBO users.
We can fix using sp_changedbowner, but I'm more concerned with HOW this is
happening.
We have serveral database servers, and over 100 databases.
Scripting a solution per server isnt a problem - but I dont want to find out
some virus-type script is running through the servers.
Has anyone else had any similar issues ?
There are no database restores bring carried out - these are mainly stable
working production servers.
There only two boxes (a development and production box) that has so far
escaped any issues.
Machines that are not in the domain are also being effected.
All machines run on non-standard ports (not 1433) - but most servers can see
each other (for replication etc).
The main account to be orphaned at the moment is the DBO one, but there have
been another couple without consistancy..
Any help/comments appreciated
TIA
JonathanA likely cause is that the databases were owned by Windows accounts that
were removed. This will cause the database owner SID to be invalid.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jonathan Christie" <jonathan.christie@.ed-coll.ac.uk> wrote in message
news:%23wEVcxXbEHA.3476@.tk2msftngp13.phx.gbl...
> Came into work this morning and found several database on different
servers
> had orphaned DBO users.
> We can fix using sp_changedbowner, but I'm more concerned with HOW this is
> happening.
> We have serveral database servers, and over 100 databases.
> Scripting a solution per server isnt a problem - but I dont want to find
out
> some virus-type script is running through the servers.
> Has anyone else had any similar issues ?
> There are no database restores bring carried out - these are mainly stable
> working production servers.
> There only two boxes (a development and production box) that has so far
> escaped any issues.
> Machines that are not in the domain are also being effected.
> All machines run on non-standard ports (not 1433) - but most servers can
see
> each other (for replication etc).
> The main account to be orphaned at the moment is the DBO one, but there
have
> been another couple without consistancy..
> Any help/comments appreciated
> TIA
> Jonathan
>|||Hi Dan
I agree - and I would spot these ones.
However, these are all the dbo users /sa logins
All maintained/created under my own account - which hasn't been removed ;)
It is a bit worrying that it has spread over the last few days - almost
virus like.
Thanks anyway
J
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23kYRzPZbEHA.1292@.TK2MSFTNGP11.phx.gbl...
> A likely cause is that the databases were owned by Windows accounts that
> were removed. This will cause the database owner SID to be invalid.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jonathan Christie" <jonathan.christie@.ed-coll.ac.uk> wrote in message
> news:%23wEVcxXbEHA.3476@.tk2msftngp13.phx.gbl...
> servers
is[vbcol=seagreen]
> out
stable[vbcol=seagreen]
> see
> have
>|||Was there a restore done over the weekend?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||> However, these are all the dbo users /sa logins
I'm not sure what you mean by this. You may experience problems with the
'dbo' user in the following cases.
1) The sid in sysdatabases doesn't match the 'dbo' user sid in sysusers.
These should be the same.
2) The sid in sysdatabases (or sysusers) is invalid (SUSER_SNAME(sid)
returns NULL).
A restore can cause #1 and perhaps #2 and is probably why Kevin mentioned
this in his response. Deleting the owner's account can cause #2.
sp_changedbowner can fix the problem in either case.

> It is a bit worrying that it has spread over the last few days - almost
> virus like.
I'm not aware of any malicious code that deliberately orphans database
owners. This seems like a strange thing to do since code that exploits a
vulnerability that allows system tables to be hacked would presumably do a
lot more.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jonathan Christie" <jonathan.christie@.ed-coll.ac.uk> wrote in message
news:Okmt1ZZbEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi Dan
> I agree - and I would spot these ones.
> However, these are all the dbo users /sa logins
> All maintained/created under my own account - which hasn't been removed ;)
> It is a bit worrying that it has spread over the last few days - almost
> virus like.
> Thanks anyway
> J
>
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:%23kYRzPZbEHA.1292@.TK2MSFTNGP11.phx.gbl...
this[vbcol=seagreen]
> is
find[vbcol=seagreen]
> stable
far[vbcol=seagreen]
can[vbcol=seagreen]
there[vbcol=seagreen]
>

Orphaned user owns objects as dbo and cannot be dropped.

Hi,
Hope someone can give me a hint. I restored a database from the Development
SQL server to the production server. Normally, the database dbo user is
associated with sa login name, but this database's dbo user is associated
with NULL. The developer had dbo permission on this database in the Dev
environment. Now I am trying to drop his orphaned user name from this
database on the production server, but I got an error message: "The selected
user cannot be dropped because the user owns objects. However, I checked all
tables, views, and stored procedures they are all owned by the dbo not this
user. How can I get rid of this use name in this database?
Thank you in advance.
Jack.Jack
EXEC sp_change_users_login 'Report'
What do you get?
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:856F9A25-73B3-4668-8E8F-68E3CBD70C57@.microsoft.com...
> Hi,
> Hope someone can give me a hint. I restored a database from the
> Development
> SQL server to the production server. Normally, the database dbo user is
> associated with sa login name, but this database's dbo user is associated
> with NULL. The developer had dbo permission on this database in the Dev
> environment. Now I am trying to drop his orphaned user name from this
> database on the production server, but I got an error message: "The
> selected
> user cannot be dropped because the user owns objects. However, I checked
> all
> tables, views, and stored procedures they are all owned by the dbo not
> this
> user. How can I get rid of this use name in this database?
> Thank you in advance.
> Jack.|||> Normally, the database dbo user is
> associated with sa login name, but this database's dbo user is associated
> with NULL.
You can fix the database owner with:
USE MyDatabase
EXEC sp_changedbowner 'sa'

> "The selected
> user cannot be dropped because the user owns objects. However, I checked
> all
> tables, views, and stored procedures they are all owned by the dbo not
> this
> user. How can I get rid of this use name in this database?
I suspect this is a different problem than the database owner. The script
below will identify objects owned by the problem user:
SELECT name, xtype
FROM dbo.sysobjects
WHERE uid = USER_ID('DevUser')
Uri mentioned that you can use sp_change_users_login to identify problem
user/login mapping.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:856F9A25-73B3-4668-8E8F-68E3CBD70C57@.microsoft.com...
> Hi,
> Hope someone can give me a hint. I restored a database from the
> Development
> SQL server to the production server. Normally, the database dbo user is
> associated with sa login name, but this database's dbo user is associated
> with NULL. The developer had dbo permission on this database in the Dev
> environment. Now I am trying to drop his orphaned user name from this
> database on the production server, but I got an error message: "The
> selected
> user cannot be dropped because the user owns objects. However, I checked
> all
> tables, views, and stored procedures they are all owned by the dbo not
> this
> user. How can I get rid of this use name in this database?
> Thank you in advance.
> Jack.|||Thank you Dan and Uri,
I did run the sp_change_users_login 'Report' but the DevUser was not listed
as an orphaned user. When I run:
select name, xtype from dbo.sysobjects where uid = USER_ID('DevUser')
I got following result:
Name xtype
current_date D
empty_string D
zero D
Those three object are default constraints. I don't how this could happen? I
tried to use sp_changeobjectowner to change the owership on these objects,
but got the following message:
Object 'zero' does not exist or is not a valid object for this operation.
So I had to directly update the dbo.sysobjects system table to chane the uid
to 1 for these three objects, then I dropped the DevUser successfully. What
I
did might not be a safe way to solve this problem. Do you have any other
better ways to get this problem corrected?
Thank you so much for your help.
Jack
"Dan Guzman" wrote:

> You can fix the database owner with:
> USE MyDatabase
> EXEC sp_changedbowner 'sa'
>
> I suspect this is a different problem than the database owner. The script
> below will identify objects owned by the problem user:
> SELECT name, xtype
> FROM dbo.sysobjects
> WHERE uid = USER_ID('DevUser')
> Uri mentioned that you can use sp_change_users_login to identify problem
> user/login mapping.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jack" <Jack@.discussions.microsoft.com> wrote in message
> news:856F9A25-73B3-4668-8E8F-68E3CBD70C57@.microsoft.com...
>
>|||I haven't run into orphaned default constraints before. You might try DBCC
CHECKCATALOG to see if turns up anything else.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:A1CCBE29-5946-4235-A8FC-C53D7058853F@.microsoft.com...[vbcol=seagreen]
> Thank you Dan and Uri,
> I did run the sp_change_users_login 'Report' but the DevUser was not
> listed
> as an orphaned user. When I run:
> select name, xtype from dbo.sysobjects where uid = USER_ID('DevUser')
> I got following result:
> Name xtype
> current_date D
> empty_string D
> zero D
> Those three object are default constraints. I don't how this could happen?
> I
> tried to use sp_changeobjectowner to change the owership on these objects,
> but got the following message:
> Object 'zero' does not exist or is not a valid object for this operation.
> So I had to directly update the dbo.sysobjects system table to chane the
> uid
> to 1 for these three objects, then I dropped the DevUser successfully.
> What I
> did might not be a safe way to solve this problem. Do you have any other
> better ways to get this problem corrected?
> Thank you so much for your help.
> Jack
>
> "Dan Guzman" wrote:
>