executing SQL script from Command promt not working

Hello
I am trying to execute SQl script from command prom like this :

C:\Inetput\wwwroot> osql -U sa -P -i MyComics.sql

(uid=sa and pwd=)

and I got the result like this :

[Shared Memory]SQl Server deos not exist or access denied
[Shared Memory]Connection Open (Connect()).

I already check the SQL server , it's running.
what do I do now?

Thanks in advanceAre you certain that the user name and password is correct? Are you certain that this instance of SQL Server is the main instance and not a named instance?|||Hello

thank for the response.

from my understanding sa is the default user name of SQL Server 2000 and password is blank. I did not set any password for my SQL server.

I'm really new to SQL server, if you have any advice I'll appreciate.|||Try osql -E <other parameters, except user and password
-E uses a trusted connection. Unless you set an sa password while installing SQL Server, SQL Server is set to use Windows authentication. You need to specify Mixed Mode authentication. You can change to mixed mode security in Enterprise Manager. Right click database, select properties, set Authentication to SQL Server and Windows. Make sure you set a good sa password. DO NOT leave it as blank!

Executing osql commands through batch file

Hello

I have a script ,which runs with osql

The script is :

osql -E
declare @.cmd nvarchar(1000)
declare @.cmd2 nvarchar(1000)
declare @.state1 varchar(100)
declare @.message varchar(100)
set @.message = ''
-- Build command to determine state of SQLSERVERAGENT service on Master Server
SET @.CMD = 'create table #state (state varchar(2000))' + char(10) +
'declare @.cmdx varchar(1000)' + char(10) +
'insert into #state EXEC master..xp_servicecontrol ''''QueryState'''', ''''SQLSERVERAGENT'' +
+ char(10) + 'select @.state=state from #state' + char(10) +
'drop table #state'
-- Build command to execute command that determines state of service being monitored
set @.cmd2 = 'declare @.state varchar(100)' + char(10) +
'exec ' + rtrim(@.@.servername) + '.master.dbo.sp_executesql N''' + @.CMD + ''',' +
'N''@.state varchar(100) out'',' +
'@.state out' + char(10) +
'set @.state1 = @.state'
-- Execute command and return state of service being monitored
exec master.dbo.sp_executesql @.cmd2,N'@.state1 varchar(100) out',@.state1 out
-- Is the service that was monitored not
IF (UPPER(@.state1) <> 'RUNNING.')
--if @.state1 <1 'Running.'
begin
-- Display message that primary monitor is down
select @.message = @.message+char(13)+ @.@.servername + ' -' + 'Sql Server Agent Not Running'+char(13)
print 'Master server "' + rtrim(@.@.servername) + '" for monitoring is not available.'
exec master.dbo.xp_smtp_sendmail

It works fine when I run it on the command line prompt.
And I receive a mail , if the server agent is running.

But when I save it as bat file and try to run , it stops and doesnot even give an error.

Can anyone let me know what I can do.

ThanksHello

I have got it working.

Just I need to use :

osql -E -iC:\serveragent.sql -oC:\outputfile.txt

Thanks

executing one .sql file from another

I need to run a x.sql fil from another y.sql file. The y.sql file is executed by OSQL.
I can't find out if it is posible or not. I triet using EXEC but that doesn't work.if you create each as stored procedures, you can exec them from eachother...otherwise (though it is a terrible habit and not recommended) you can use xp_CMDShell

Executing commands in parallel

I have the need to run multiple SQL commands (like osql and bcp) in parallel
from a single bat file. To describe this a little clearer...
While executing a single .BAT file, I want to be able to execute 3 OSQL
commands in parallel. Currently I have to run these 3 serially and it takes
too long.
Is there any way from within a single bat file that I could have 3 OSQL
commands running at the same time?
Thanks in advance."TJT" <T_homas.T_odd@.smed.com> wrote in message
news:OHrmXephDHA.604@.TK2MSFTNGP10.phx.gbl...
> I have the need to run multiple SQL commands (like osql and bcp) in
parallel
> from a single bat file. To describe this a little clearer...
> While executing a single .BAT file, I want to be able to execute 3 OSQL
> commands in parallel. Currently I have to run these 3 serially and it
takes
> too long.
> Is there any way from within a single bat file that I could have 3 OSQL
> commands running at the same time?
>
start osql.exe . . .
start osql.exe . . .
start osql.exe . . .
From a command prompt type
start /?
for help with the start command.
Davidsql

Executing a script and returning the results..

You know how osql can return the results of executing a .sql script to a window? Is there any way to achieve that same behavior in .NET? I am in the process of working on an application that needs to execute a .SQL script (which is easy to do) and then return the actual SQL output from the execution to a window. How do I get that output? None of the sql data objects seem to support this.I actually figured this one out. I can launch a process (like the command prompt) and then return the output through a stream.

Executing .sql file using SMO

Hi all,

Is it possible to execute a .sql file using SMO. like in old days we used to use osql.exe to execute the sql files

Thanks in advance

Rujith

Hi,

you don′t need SMO for that,executing scripts can be done via a *normal* sqlconnection (untested script, just wroite down in notepad)

static void Main(string[] args)
{

if (args[0] == null)
{
Console.WriteLine("No file passed to the program.");
return;
}

if (!File.Exists(args[0].ToString()))
{
Console.WriteLine("File does not exist.");
return;
}

try
{
FileStream file = new FileStream(args[0].ToString(), FileMode, FileAccess);
StreamReader sr = new StreamReader(file);
string Commands = sr.ReadToEnd();
sr.Close();
file.Close();

SqlCommand cmd = new SqlCommand(Commands);
cmd.Connection.ConnectionString = "Data Source=.;Integrated Authentication=true";
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(string.Format("Exeception occured: {0}", ex.Message));
}

}

HTH, Jens Suessmeyer.

|||

Hi

This code exevuted for some .sql file

but not for all .

how to do that ?

?

|||

Hi,

what do you mean by *all* ?

-Jens.

|||

I mean

that i do have some script file

One for TAble

One for View

One for Stored procedures and so on...

I also have developed same type of code.

But this code runs for only that table script file only

but not for othres;

specially

Code gives error where GO command is there in script;

|||Either replace the GOs in the script or slit the string into a string array which can be executed one by one.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Also check out ExecuteNonQuery function:

server.ConnectionContext.ExecuteNonQuery(sqlStatements);

This function "understands" GO batch separators. You would of course need to load the text from a file to a string.

Artur Laksberg
SQL Server Team
Microsoft

|||

Thnx Artur laksberg

but u have not mentioned what is this "server" object.

please tell about class to which this object does belongs to.

Waiting for yor reply

uday (Emersion)

|||Hi ermersion,
take a look inte namespace

Microsoft.SqlServer.Management.Smo
There is a server class which hold the method Arthur was talking about.
The namespace can be used by referencing the assembly directly (installed in the GAC) or by a file reference on
Microsoft.SqlServer.Smo.dll
SomeTime Ago, I created a small app for a user mapping, the server class is also used there, perhaps you can get a feeling from this:
http://www.sqlserver2005.de/SharedFiles/UserMappingwithSMO.zip
HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Artur laksberg MSFT wrote:

Also check out ExecuteNonQuery function:

server.ConnectionContext.ExecuteNonQuery(sqlStatements);

This function "understands" GO batch separators. You would of course need to load the text from a file to a string.

Artur Laksberg
SQL Server Team
Microsoft

That's what we did. It read the script line by line. When GO was encountered, it submitted what was accumulated to SQL Server using an ADO SQL command object ExecuteNonQuery method. It works fine.

Not familiar with the object you're talking about. We'll have to check that out because it would be more effecient to send scripts that contain multiple batches. However, that requires our customers to upgrade to SQL 2005 (or install the add-on's) to get SMO.

Joe

Executing .sql file using SMO

Hi all,

Is it possible to execute a .sql file using SMO. like in old days we used to use osql.exe to execute the sql files

Thanks in advance

Rujith

Hi,

you don′t need SMO for that,executing scripts can be done via a *normal* sqlconnection (untested script, just wroite down in notepad)

static void Main(string[] args)
{

if (args[0] == null)
{
Console.WriteLine("No file passed to the program.");
return;
}

if (!File.Exists(args[0].ToString()))
{
Console.WriteLine("File does not exist.");
return;
}

try
{
FileStream file = new FileStream(args[0].ToString(), FileMode, FileAccess);
StreamReader sr = new StreamReader(file);
string Commands = sr.ReadToEnd();
sr.Close();
file.Close();

SqlCommand cmd = new SqlCommand(Commands);
cmd.Connection.ConnectionString = "Data Source=.;Integrated Authentication=true";
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(string.Format("Exeception occured: {0}", ex.Message));
}

}

HTH, Jens Suessmeyer.

|||

Hi

This code exevuted for some .sql file

but not for all .

how to do that ?

?

|||

Hi,

what do you mean by *all* ?

-Jens.

|||

I mean

that i do have some script file

One for TAble

One for View

One for Stored procedures and so on...

I also have developed same type of code.

But this code runs for only that table script file only

but not for othres;

specially

Code gives error where GO command is there in script;

|||Either replace the GOs in the script or slit the string into a string array which can be executed one by one.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Also check out ExecuteNonQuery function:

server.ConnectionContext.ExecuteNonQuery(sqlStatements);

This function "understands" GO batch separators. You would of course need to load the text from a file to a string.

Artur Laksberg
SQL Server Team
Microsoft

|||

Thnx Artur laksberg

but u have not mentioned what is this "server" object.

please tell about class to which this object does belongs to.

Waiting for yor reply

uday (Emersion)

|||Hi ermersion,
take a look inte namespace

Microsoft.SqlServer.Management.Smo
There is a server class which hold the method Arthur was talking about.
The namespace can be used by referencing the assembly directly (installed in the GAC) or by a file reference on
Microsoft.SqlServer.Smo.dll
SomeTime Ago, I created a small app for a user mapping, the server class is also used there, perhaps you can get a feeling from this:
http://www.sqlserver2005.de/SharedFiles/UserMappingwithSMO.zip
HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Artur laksberg MSFT wrote:

Also check out ExecuteNonQuery function:

server.ConnectionContext.ExecuteNonQuery(sqlStatements);

This function "understands" GO batch separators. You would of course need to load the text from a file to a string.

Artur Laksberg
SQL Server Team
Microsoft

That's what we did. It read the script line by line. When GO was encountered, it submitted what was accumulated to SQL Server using an ADO SQL command object ExecuteNonQuery method. It works fine.

Not familiar with the object you're talking about. We'll have to check that out because it would be more effecient to send scripts that contain multiple batches. However, that requires our customers to upgrade to SQL 2005 (or install the add-on's) to get SMO.

Joe