OSQL Question

I created a sql script that transfers data from my test system to production adn vice versa. I would like to use OSQL and be able to pass parameters to the SQL script.

Example: param 1 = test; param 2 = production.

Can anyone tell me if this is possible and how to do it?

Thanks in advance for all the help.You can't directly pass parameters as part of the OSQL command-line utility, but you have several alternatives for solving your problem.

As stated in Winnet mag article :
OSQL won't let you pass in parameters, but you can use the sp_executesql stored procedure, which can process parameterized SQL. (For detailed information about sp_executesql, see SQL Server Books OnlineBOL.) The following example lets you pass parameters into a T-SQL command but doesn't completely address how to pass a parameter as part of an OSQL command-line session (remember that you must issue the OSQL command from a command prompt):

osql -E -MyServer -Q "EXECUTE sp_executesql N'SELECT *
FROM northwind..
orders WHERE OrderId = @.OrderId' ,N'@.OrderId int' ,@.OrderId = 10248"
Another solution relies on the power of Windows to handle the parameterization for you. For DBAs who aren't familiar with Windows-level command-file processing, a batch file is a text file that has a .bat extension. Windows treats batch files as executables that run in the Command Prompt environment. You can think of batch files as mini programs that Windows runs.

You can simply create a file called SQLVariableBatch.bat, and put the following text in it:

osql -E -MyServer -Q "SELECT *
FROM northwind..orders WHERE OrderId = %1"
From the directory where you saved the .bat file, issue the following command from a command prompt window:

SQLVariableBatch 10248
When running this command, Windows will replace the %1 in the SQLVariableBatch.bat file with what comes after the batch file's name in the command linein this case, 10248. This is a simple example of batch processing in Windows; to learn more about batch files, see the Windows Help files.

You can also use Windows Scripting Host (WSH) to manage the parameterization. Using WSH for scripting and batch processing is much more flexible and powerful than using simple Windows batch files. I'm not a WSH expert, so I don't include an example of this solution, but I wanted to note that the option exists. For information about using WSH, see the MSDN Web site for Windows Script at

http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28001169 .|||Thanks for your help. I will try your suggestions and see if I can figure it out.

Osql and CommandLine stored procedures

I'm currently developing a number of SQL scripts to handle a major upgrade of our database from a development server to a production server using Osql.

Everything is working fine, but I have each of the scripts generating text-output and am hoping someone may know how to suppress some the Caution text (specifically triggered when a column has been renamed).

Can anyone provide a possible solution? (there's so many notes being written to the console its easy to miss the errors I'm concerned about.

Thanks!

TedNot that I am aware of. You might try SET ANSI_WARNINGS OFF but that will not suppress the types of warnings to which you are referring.

Terri

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