ExecuteWithResultsAndMessages2, syntax problem?

Here is a code snippet that I am using from Ken's sp_run_xml_proc.
I want to modify it, such that when I invoke sp_run_xml_proc I want to pass
a SP and a parameter. Something like EXEC sp_run_xml_proc 'get_xml_LAC',
@.parameter. This @.parameter should be used as a parameter to get_xml_LAC
stored proc. Is it possible with 'ExecuteWithResultsAndMessages2'? If yes,
then can you direct me how I can fix it? BOL hasn't been much help.
EXEC @.hr = sp_OAMethod @.object, 'ExecuteWithResultsAndMessages2',
@.results OUT, @.procname, @.msgs OUT, @.parameter
IF @.hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @.object, @.hr
RETURN
ENDHi
Have you tried adding the value to the procedure name?
set @.procname = @.procname + ' ' + @.parameter
John
"S" wrote:

> Here is a code snippet that I am using from Ken's sp_run_xml_proc.
> I want to modify it, such that when I invoke sp_run_xml_proc I want to pas
s
> a SP and a parameter. Something like EXEC sp_run_xml_proc 'get_xml_LAC',
> @.parameter. This @.parameter should be used as a parameter to get_xml_LAC
> stored proc. Is it possible with 'ExecuteWithResultsAndMessages2'? If yes,
> then can you direct me how I can fix it? BOL hasn't been much help.
> EXEC @.hr = sp_OAMethod @.object, 'ExecuteWithResultsAndMessages2',
> @.results OUT, @.procname, @.msgs OUT, @.parameter
> IF @.hr <> 0 BEGIN
> EXEC sp_displayoaerrorinfo @.object, @.hr
> RETURN
> END|||Worked like a charm. Appreciate it.
"John Bell" wrote:
> Hi
> Have you tried adding the value to the procedure name?
> set @.procname = @.procname + ' ' + @.parameter
> John
> "S" wrote:
>

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 stored proc on a named instance

I think I'm being a bit thick, but I just cannot figure out the proper syntax to call a stored proc on a SQL named instance I have. I've tried many variations, but here is the basic format of what I'm trying:

EXEC Server\Instance.DB.dbo.usp_fm_proc 1

It seems it doesn't like the \ as I get an error "Incorrect syntax near 'Instance'.

What am I missing here?

Have you created linekd server, try the following:

EXEC [Server\Instance].DB.dbo.usp_fm_proc 1

|||Dooh! That's it, I was overlooking the []. Thanks!

Execute SP if returns data

Hi all,
I keep getting this error message:
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'Else'.
I just want to execute a stored procedure if something is returned by
this: "Select hl7file from notes where FileWasCreated is null"
could be a large text file or nothing. If nothing then should do nothing.
I'm calling this from a SQLcommand object in VB.NET
Declare @.Cnt int
set @.Cnt = (Select count(*) from notes where FileWasCreated is null)
If @.Cnt = 0
Else
BEGIN
Select hl7file from notes where FileWasCreated is null
EXECUTE InsertIncrementnumber
END
thanks
gvDo the opposite
If @.Cnt <> 0
BEGIN
..
END
"gv" <viatorg@.musc.edu> wrote in message
news:OxvPKGGaFHA.1148@.tk2msftngp13.phx.gbl...
> Hi all,
> I keep getting this error message:
> Server: Msg 156, Level 15, State 1, Line 10
> Incorrect syntax near the keyword 'Else'.
> I just want to execute a stored procedure if something is returned by
> this: "Select hl7file from notes where FileWasCreated is null"
> could be a large text file or nothing. If nothing then should do nothing.
> I'm calling this from a SQLcommand object in VB.NET
>
> Declare @.Cnt int
> set @.Cnt = (Select count(*) from notes where FileWasCreated is null)
> If @.Cnt = 0
> Else
> BEGIN
> Select hl7file from notes where FileWasCreated is null
> EXECUTE InsertIncrementnumber
> END
>
> thanks
> gv
>|||thanks
gv
"news.microsoft.com" <zheka212@.hotmail.com> wrote in message
news:edjeJJGaFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Do the opposite
> If @.Cnt <> 0
> BEGIN
> ...
> END
> "gv" <viatorg@.musc.edu> wrote in message
> news:OxvPKGGaFHA.1148@.tk2msftngp13.phx.gbl...
>|||On Fri, 3 Jun 2005 13:27:36 -0400, gv wrote:

>Hi all,
>I keep getting this error message:
>Server: Msg 156, Level 15, State 1, Line 10
>Incorrect syntax near the keyword 'Else'.
>I just want to execute a stored procedure if something is returned by
>this: "Select hl7file from notes where FileWasCreated is null"
>could be a large text file or nothing. If nothing then should do nothing.
>I'm calling this from a SQLcommand object in VB.NET
>
> Declare @.Cnt int
> set @.Cnt = (Select count(*) from notes where FileWasCreated is null)
> If @.Cnt = 0
> Else
> BEGIN
> Select hl7file from notes where FileWasCreated is null
> EXECUTE InsertIncrementnumber
> END
>
>thanks
>gv
>
Hi gv,
Don't use COUNT(*) if you only want to know if there is none or at least
one - use EXISTS instead. (EXISTS will stop processing after finding a
row; COUNT will continue to count the other 300 million matching rows,
which is quite a shame if all you do is compare the result to 0).
IF EXISTS (SELECT * FROM notes WHERE FileWasCreated IS NULL
BEGIN
SELECT hl7file FROM notes WHERE FileWasCreated IS NULL
EXECUTE InsertIncrementnumber
END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Execute DTS package from ADO in VB

I'm using ADO objects in a VB Macro for Excel. I'd like to execute a DTS package located on SQLServer.

What is the syntax to do this?

Here's my current database connection code:Option Explicit

Dim db_connection As ADODB.Connection
Dim db_results As ADODB.Recordset
Dim db_error As ADODB.Error

Private Sub DB_Initialize()
Set db_connection = New ADODB.Connection
db_connection.Open "Provider='SQLOLEDB';Data Source='BACK_SQL';" & _
"Initial Catalog='my_db';Integrated Security='SSPI';"
Set db_results = New ADODB.Recordset
End SubIs it possible to use a stored procedure to execute a DTS package?

What would the stored procedure be?|||Originally posted by odinsdream
Is it possible to use a stored procedure to execute a DTS package?

What would the stored procedure be?

I don't know very much about VB but I definately know my DTS packages.. yes you can execute a DTS package via a stored procedure

server_name=server name dts package is on
user_name=login to access server
password=user's password
package_name = DTS package name
package_password=DTS package pwd

Create Procedure sp_ExecuteDTS AS

exec master.. xp_cmdshell 'dtsrun /Sserver_name /Uuser_name /Ppassword /N"package_name" /Mpackage_password'

GO
-------

if your package does not have a pwd then your sp should look like this

Create Procedure sp_ExecuteDTS AS

exec master.. xp_cmdshell 'dtsrun /Sserver_name /Uuser_name /Ppassword /N"package_name"

GO|||So how would one execute that stored procedure from VBA for Access?

Execute check sql syntax programatically

HI, Is there a way to check sql syntax from a .Net application? Sql server management studio is made in .Net and it has that option. Is teher a way to know how it is implemented in SSMS?

Thank you,
Ccote

Hi Ccote,

SSMS performs some farly rudimentary syntax checks, but not a complete TSQL syntax validation. This is done via TSQL command

SET PARSEONLY ON

This functionality is also exposed in SMO via ParseOnly option:

Code Snippet

Server.ConnectionContext.ExecuteNonQuery(myquery, ExecutionTypes.NoCommands);

|||HI, Thank you for your answer!|||Not being very familiar with VB.Net, how would I caputure the output of the ParseOnly result into a variable?