executing package DTExec

I created a package and stored in SQL server.

I am running it as follows

master.dbo.xp_cmdshell dtexec /sq "ABC DEF GHI" /ser "Prod"

ABC DEF GHI --> is my package name.

When i run this from a job, it runs without error

but when i run the above sql i am getting error as

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near '/'.

Am i missing some thing, please advice. Is that the problem between spaces in the package name?

I missed the single quotes

master.dbo.xp_cmdshell 'dtexec /sq "ABC DEF GHI" /ser "Prod"'

ExecuteNonQuery syntax

Hi, I am trying to execute a nonquery as follows (look for bold):

Dim connStringSQLAsNew SqlConnection("Data Source=...***...Trusted_Connection=False")

'// Create the new OLEDB connection to Indexing Service

Dim connIndAsNew System.Data.OleDb.OleDbConnection(connStringInd)

Dim commandIndAsNew System.Data.OleDb.OleDbDataAdapter(strQueryCombined, connInd)

Dim commandSQLAsNew SqlCommand("GetAssetList2", connStringSQL)

commandSQL.CommandType = Data.CommandType.StoredProcedure

Dim resultDSAsNew Data.DataSet()

Dim resultDAAsNew SqlDataAdapter()

'// Fill the dataset with values

commandInd.Fill(resultDS)

'// Get the XML values of the dataset to send to SQL server and run a new query

...

'// Return the number of results

resultCount.Text = source.Count.ToString

results.DataSource = source

results.DataBind()

'// Record the search

commandSQL =New SqlCommand("RecordSearch", connStringSQL)

commandSQL.Parameters.Clear()

commandSQL.Parameters.Add("@.userName", Data.SqlDbType.VarChar, 50).Value = authUser.Text()

commandSQL.Parameters.Add("@.createdDateTime", Data.SqlDbType.DateTime).Value = DateTime.Now()

commandSQL.Parameters.Add("@.numRows", Data.SqlDbType.Int, 1000).Value = resultCount.Text

commandSQL.Parameters.Add("@.searchString", Data.SqlDbType.VarChar, 1000).Value = searchText.Text

connStringSQL.Open()

commandSQL.ExecuteNonQuery()

connStringSQL.Close()

The stored procedure looks like this:

Use GTGAssets

DROPPROC dbo.RecordSearch;

--New Procedure

GO

CREATEPROC dbo.RecordSearch

(

@.userNamevarchar(50),

@.createdDateTimeDateTime,

@.numRowsvarchar(1000),

@.searchStringvarchar(1000)

)

AS

BEGIN

SETNOCOUNTON

INSERTINTO SearchLog(SearchString, CreatedByUser, CreatedDTTM, RowsReturned)VALUES(@.searchString, @.userName, @.createdDateTime, @.numRows)

END

GO

Any ideas as to why this error is appearing?

Incorrect syntax near 'RecordSearch'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near 'RecordSearch'.

Source Error:

Line 169: commandSQL.Parameters.Add("@.searchString", Data.SqlDbType.VarChar, 1000).Value = searchText.TextLine 170: connStringSQL.Open()Line 171: commandSQL.ExecuteNonQuery()Line 172: connStringSQL.Close()Line 173: End If

Many thanks!

James

Alright, I found this - it's the way the DateTime is being passed in. The DateTime parameter for the last command looks like this:

#9/11/2006 4:01:25 PM#

However, when this procedure is run on SQL Server Query Analyzer:

USE [GTGAssets]
GO

DECLARE @.return_value int

EXEC @.return_value = [dbo].[RecordSearch]
@.userName = N'name',
@.createdDateTime = N'#9/11/2006 4:01:25 PM#',
@.numRows = N'10',
@.searchString = N'soa'

SELECT 'Return Value' = @.return_value

GO

I get this SQL Server error. I tried this insert (notice the lack of pound signs) it actually works.

USE [GTGAssets]
GO

DECLARE @.return_value int

EXEC @.return_value = [dbo].[RecordSearch]
@.userName = N'CVNS\JFogarty',
@.createdDateTime = N'9/11/2006 4:01:25 PM',
@.numRows = N'10',
@.searchString = N'soa'

SELECT 'Return Value' = @.return_value

GO

Why is this? The pound signs should not be added to the DateTime.Now(), should it?

Thanks!|||You need to enclose date with #'s for Access DB not SQL Server.|||

Well the problem is that VB.Net automattical adds the # - this is the code:

commandSQL.Parameters.Add("@.createdDateTime", Data.SqlDbType.DateTime).Value = DateTime.Now()

|||AFAIK it shouldnt be putting any #'s.|||You forgot to set the commandtype

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.