"sp_executesql" says un-expected error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

"sp_executesql" says un-expected error

Author

Topic

SqlStar
Posting Yak Master

121 Posts

Posted - 2006-04-14 : 16:49:14

Hi,

Please see the following codes:

CREATE TABLE [dbo].[BarcodeStore] (
[BarcodeID] [uniqueidentifier] NOT NULL ,
[CompanyID] [uniqueidentifier] NOT NULL ,
[DestinationFolderID] [uniqueidentifier] NOT NULL ,
[DestinationDocumentName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DestinationTagValue] [varbinary] (150) NULL ,
[UserID] [uniqueidentifier] NOT NULL ,
[DateCreated] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE PROCEDURE dbo.AD_S_GetBarcodeInfo
(
@BarcodeID uniqueidentifier
)
AS
BEGIN
SET NOCOUNT ON

SELECT CompanyID, DestinationFolderID, DestinationDocumentName, DestinationTagValue
FROM BarcodeStore
WHERE BarcodeID = @BarcodeID
END

I have a table to store the required data and I written a SP to get a row from the table based on parameter value.The problem is, when I execute the SP using "EXECUTE" command it works fine.

EXEC AD_S_GetBarcodeInfo '1ADEEA9E-A815-4CD7-8B30-B94EE7656F09'

But when I execute the same using "sp_executesql",

sp_executesql
N'AD_S_GetBarcodeInfo',
N'@BarcodeID varchar(1000)',
@BarcodeID = '1ADEEA9E-A815-4CD7-8B30-B94EE7656F09'

it says an un-expected error like:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'AD_S_GetBarcodeInfo'.

Note: The "sp_executesql" execution signature generated by .NET framework.

Please help me to identify what I am missing?

Thanks in advance...

Rafiq
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"You have to dream before your dreams can come true" -- President Of India

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-14 : 16:54:51

try

sp_executesql
N'exec AD_S_GetBarcodeInfo @BarcodeID',
N'@BarcodeID varchar(1000)',
@BarcodeID = '1ADEEA9E-A815-4CD7-8B30-B94EE7656F09'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SqlStar
Posting Yak Master

121 Posts

Posted - 2006-04-14 : 17:05:57

Hi nr,

Thanks for your immediate response. Its working :) But I need to execute this Sp from .NET application. When I try to execute it thru .NET's "ExecuteReader" method, its says error like this. :(

Rafiq
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"You have to dream before your dreams can come true" -- President Of India

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-14 : 17:23:16

so why are you using sp_executesql?
Just call the sp with a parameter.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SqlStar
Posting Yak Master

121 Posts

Posted - 2006-04-14 : 18:40:33

Hi nr,

I got it. The problem is I missed to mention the "SqlCommand" object's command type as "Stored Procedure". By default, it assumes that the command text as "Plain SQL Statement". So it will try to execute using "sp_executesql". If we mention the command type as "stored procedure", it will use the "Execute" command instead of "sp_executesql".

Thanks

Rafiq
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"You have to dream before your dreams can come true" -- President Of India

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources