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

EXECUTE SQL string with parameter and return value

Hi all,

I would like execute an SQL string who calls a stored procedure with param and return a value:

declare @.query nvarchar(50)

set @.query = 'sp_test 1'

declare @.resultat int

exec @.resultat = @.query

select @.resultat

Its returns a error message:

"Could not find stored procedure 'sp_test 1'"

The command

exec(@.query)

works fine, but I can't retreive the return value and I can't do

exec @.resultat = (@.query)

How can I do?

Thanks,

Aurlien

use sp_executesql|||

Thanks very much,

With sp_executesql, the stored procedure is correctly executed, but it don't return the return value...

exec @.resultat = sp_executesql @.requete

@.resultat is still at 0 event if my stored procedure returns other :'(

How can I do?

Thanks very much

|||

check this example i use a cursor.. good luck

CREATE procedure test
as
begin
DECLARE @.AuthorID char(11)
declare @.sql nvarchar(4000)
set @.sql=' SET @.c1 = CURSOR STATIC FOR SELECT au_id FROM authors; OPEN @.c1'--'SELECT au_id FROM authors'

DECLARE @.c1 CURSOR

EXEC sp_executesql N'SET @.c1 = CURSOR STATIC FOR SELECT au_id FROM authors; OPEN @.c1', N'@.c1 cursor OUTPUT', @.c1 OUTPUT

FETCH NEXT FROM @.c1
INTO @.AuthorID
WHILE @.@.FETCH_STATUS = 0
BEGIN

PRINT @.AuthorID

FETCH NEXT FROM @.c1
INTO @.AuthorID
END
CLOSE @.c1
DEALLOCATE @.c1
end

|||

Another exemple when using dynamic queries

declare @.query nvarchar(50)

set @.query = 'select ''' + 'sp_test 1' + ''''

CREATE TABLE #resultat
(resultat sql_variant)

INSERT INTO #resultat exec sp_executesql @.query

select resultat from #resultat

Execute procedure

Hello,

I have an SQL procedure as follows:

...

INSERT dbo.Levels (LevelName)
VALUES (@.LevelName)
...

LevelName is an input parameter of nvarchar type.

What should be the best way to execute this procedure from my C# / VB.Net code? And what would it return?

Thanks,

Miguel

Create a SqlConnection to the database.

Use the SqlConnection as a parameter to create a SqlCommand.

Set the command's CommandType to StoredProcedure.

Add a parameter called "@.LevelName" to the command with the value you want to insert.

Call ExecuteNonQuery on the command.

Clean up.

(The indicated SQL won't return any value.)

|||

I always use something like this (or a variation of, like ExecuteSPReturnDataTable, DataSet, etc depeding on the type of proc)

In a data access layer

1public long ExecuteStoredProcedure(string ProcedureName, Object[] Parameters)2 {3long result =new long();4 SqlCommand command;56try7 {8 Logon();910 command =new SqlCommand(ProcedureName, _conn);11 command.CommandType = CommandType.StoredProcedure;1213if (Parameters !=null){14foreach (SqlParameter paramin Parameters)15 {16 command.Parameters.Add(param);17 }18 }19 result = command.ExecuteNonQuery();2021 }22catch (Exception ex)23 {24 result = 0;25 }26finally27 {28try29 {30 Logoff();31 }32catch (Exception ex2)33 {34 }35 }3637return result;38 }

|||

Hi,

Could you please tell me what are the Logon and Logoff functions?

Could you provide me an example?

Anyway, I created a new code but it works not only with SQL database but also with other databases.

I didn't test it yet but here it is:

1' ExecuteStoredProcedure2Public Shared Function ExecuteStoredProcedure(ByVal procedureNameAs String,ByVal ParametersAs Object())As Long34' Create output5Dim outputAs New Long67' Define the connection string8Dim connectionStringAs ConnectionStringSettings = ConfigurationManager.ConnectionStrings("ConnStr")910' Construct an ADO.NET provider factory11Dim dbProviderAs DbProviderFactory = DbProviderFactories.GetFactory(connectionString.ProviderName)1213' Create and define the connection14Dim connectionAs DbConnection = dbProvider.CreateConnection()15 connection.ConnectionString = connectionString.ConnectionString1617' Run command18Try1920' Open the connection21 connection.Open()2223' Create command24Dim commandAs DbCommand = dbProvider.CreateCommand()2526' Define command properties27With command28 .CommandText = procedureName29 .Connection = connection30 .CommandType = CommandType.StoredProcedure31End With3233' Add command parameters34If Not (ParametersIs Nothing)Then35 For Each parameterAs ParameterIn Parameters36 command.Parameters.Add(parameter)37Next38 End If3940' Execute command41 output = command.ExecuteNonQuery4243Catch exAs Exception4445' Define output as 046 output = 04748Finally4950' Close the connection51 connection.Close()5253End Try5455' Return output56Return output5758End Function' ExecuteStoredProcedure

What do you think?

Cheers,

Miguel

|||

My preference would be to use the Enterprise Library 2.0 Data Access Application block.

In it there are procedures for ExecuteStoredProcedure.

It is the neatest way since you not worried about the Data Access part and concentrate on the business intricacies of the project.

Let me know your thoughts.

|||

Hello,

This seems interesting.

Does it work also with Microsoft Access databases and MySQL or only with MS SQL and Oracle?

Thanks,

Miguel

|||

Hello,

Any idea how to run a stored procedure with Enterprise Library 2.0?

Any information on this anywhere?

Thanks,

Miguel

|||

Quoting from the help of the Enterprise Library

"

The application block supplements the code in ADO.NET 2.0 that allows you to use the same code with different database types. It includes classes for SQL Server and Oracle databases. These classes contain code that provides database-specific implementations for features such as parameter handling and cursors. In addition, theGenericDatabase class allows you to use the application block with any configured ADO.NET 2.0DbProviderFactoryobject. You can extend the application block by adding new database types that include database-specific features or that provide a custom implementation of an existing database. The only requirement is that an ADO.NET 2.0DbProviderFactory class exists for the target database.

"

Therefore you have to write code for MS Access and MySQL in the block to extend it.I saw the code for the block. It had classes for SQL server and Oracle only.