ADODB Command

Hi,
I am trying to use an ADODB.Command to execute a stored procedure with som
parameters but gets the following error:
ADOError Number:-2147217900
ADOError Description:[Microsoft][ODBC SQL Server Driver][SQL Server]Rad 1:
Felaktig syntax nra 'lsc_getAllMatchesToLab'.
ADOError Source:Microsoft OLE DB Provider for ODBC Drivers
ADOError SQLState:37000
ADOError Native:170
In SQL Profiler I get this SQL text:
exec sp_executesql N' lsc_getAllMatchesToLab 1, 2, @.P1, @.P2, @.P3, @.P4, 0,
0', N'@.P1 float,@.P2 float,@.P3 float,@.P4 float',
7.361767578125000e+001, -1.058640384674072e+001, -1.775020027160645e+001,
2.500000000000000e+001
Trying to use this in Query Analyzer givs the followin error:
Server: Msg 170, Level 15, State 1, Line 1
Rad 1: Felaktig syntax nra 'lsc_getAllMatchesToLab'.
(Line 1: Syntax error near' lsc_getAllMatchesToLab')
If I run the stored procedure in Query Analyzer not using sp_executesql and
no variables as parameters as follows, I get an OK result table:
lsc_getAllMatchesToLab 1, 2,
7.361767578125000e+001, -1.058640384674072e+001, -1.775020027160645e+001,
2.500000000000000e+001, 0, 0
Is this just a simple error from my side?
Thanks for any help
Anders NilssonYou are executing a Stored Proc right?
instead of this
exec sp_executesql N' lsc_getAllMatchesToLab 1, 2, @.P1, @.P2, @.P3, @.P4, 0,
0', N'@.P1 float,@.P2 float,@.P3 float,@.P4 float',
7.361767578125000e+001, -1.058640384674072e+001, -1.775020027160645e+001,
2.500000000000000e+001
do this in your code
exec lsc_getAllMatchesToLab 1, 2, @.P1, @.P2, @.P3, @.P4, 0, 0
http://sqlservercode.blogspot.com/
"Anders Nilsson" wrote:

> Hi,
> I am trying to use an ADODB.Command to execute a stored procedure with som
> parameters but gets the following error:
> ADOError Number:-2147217900
> ADOError Description:[Microsoft][ODBC SQL Server Driver][SQL Server]Rad 1:
> Felaktig syntax n?ra 'lsc_getAllMatchesToLab'.
> ADOError Source:Microsoft OLE DB Provider for ODBC Drivers
> ADOError SQLState:37000
> ADOError Native:170
> In SQL Profiler I get this SQL text:
> exec sp_executesql N' lsc_getAllMatchesToLab 1, 2, @.P1, @.P2, @.P3, @.P4, 0,
> 0', N'@.P1 float,@.P2 float,@.P3 float,@.P4 float',
> 7.361767578125000e+001, -1.058640384674072e+001, -1.775020027160645e+001,
> 2.500000000000000e+001
> Trying to use this in Query Analyzer givs the followin error:
> Server: Msg 170, Level 15, State 1, Line 1
> Rad 1: Felaktig syntax n?ra 'lsc_getAllMatchesToLab'.
> (Line 1: Syntax error near' lsc_getAllMatchesToLab')
> If I run the stored procedure in Query Analyzer not using sp_executesql an
d
> no variables as parameters as follows, I get an OK result table:
> lsc_getAllMatchesToLab 1, 2,
> 7.361767578125000e+001, -1.058640384674072e+001, -1.775020027160645e+001,
> 2.500000000000000e+001, 0, 0
> Is this just a simple error from my side?
> Thanks for any help
> Anders Nilsson
>
>|||Anders Nilsson wrote:
> Hi,
> I am trying to use an ADODB.Command to execute a stored procedure
> with som parameters but gets the following error:
> ADOError Number:-2147217900
> ADOError Description:[Microsoft][ODBC SQL Server Driver][SQL
> Server]Rad 1: Felaktig syntax nra 'lsc_getAllMatchesToLab'.
> ADOError Source:Microsoft OLE DB Provider for ODBC Drivers
> ADOError SQLState:37000
> ADOError Native:170
>
Why didn't you show us your ADO code? It's extremely difficult to diagnose
your problem without seeing the code that produced the symptom. If you show
us your procedure's CREATE statement, particularly, the section between
CREATE PROCEDURE lsc_getAllMatchesToLab ... AS (IOW, the parameter
declarations), and let us know what programming language you are using,
somebody should be able to diagnose your problem.
Bob Barrows
Nothing to do with your problem, but I would use the native SQL Server OLE
DB provider rather than the ODBC driver.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

ADO.NET, SqlParameter, and NULL

Im trying to execute the following:

mySqlCmd.Parameters.Add("@.Parent_ID", SqlDbType.Int).Value = (sectionUpdate.iSection.ParentID == 0 ? DBNull.Value : myParentID);

However i get an error that Null cant be converted to Int. Is there any way i can uyse the SqlParameters approach but pass in a Null value for the field or must i wrap the entire connection within a IF/ELSE statement one containing a hard-coded NULL within the query and the other as a standard parameter with a proper int value?

Thanks

I found a solution:

SqlParameter sp = mySqlCmd.Parameters.Add("@.ParentID", SqlDbType.Int);
sp.IsNullable = true;

if (sectionUpdate.iSection.ParentID == 0)
sp.Value = DBNull.Value;
else
sp.Value = sectionUpdate.iSection.ParentID;

ADO.NET 1.x, How to mimick Query Analyzer Batch Execute?

Hi,
.NET v1.x SP1
VS 2003
SQL Server 2000 SP3
Server 2000, XP, Server 2003
I would like to programmatically execute {possibly many} SQL Server
batch scripts. Aka I have many scripts that drop/add stored procedure
definitions, alter table definitions & constraints, etc... and I would
like to run them from within a C# program.
All of the batch scripts were generated by Visual Studio and they run
OK in Query Analyzer; however they do not work from a
SQLConnection/SQLCommand instance in C# code. I assume the problem is
because the script file is a command batch, here's the exception
message:
Line 2: Incorrect syntax near 'GO'.
Line 4: Incorrect syntax near 'GO'.
Line 9: Incorrect syntax near 'GO'.
'CREATE PROCEDURE' must be the first statement in a query batch.
Must declare the variable '@.tiRequestStatusID'.
Must declare the variable '@.tiRequestStatusID'.
Must declare the variable '@.iWireAccessRequestID'.
Must declare the variable '@.iProcessedByID'.
A RETURN statement with a return value cannot be used in this context.
Line 123: Incorrect syntax near 'GO'.
Line 126: Incorrect syntax near 'GO'.
Line 128: Incorrect syntax near 'GO'.
Line 131: Incorrect syntax near 'GO'.
Is there a "best practice" for executing a batch script
programmatically in .NET 1.x' Should I spawn an OSQL command line for
each script file?Crash wrote:
> Hi,
> .NET v1.x SP1
> VS 2003
> SQL Server 2000 SP3
> Server 2000, XP, Server 2003
> I would like to programmatically execute {possibly many} SQL Server
> batch scripts. Aka I have many scripts that drop/add stored procedure
> definitions, alter table definitions & constraints, etc... and I would
> like to run them from within a C# program.
> All of the batch scripts were generated by Visual Studio and they run
> OK in Query Analyzer; however they do not work from a
> SQLConnection/SQLCommand instance in C# code. I assume the problem is
> because the script file is a command batch, here's the exception
> message:
> Line 2: Incorrect syntax near 'GO'.
> Line 4: Incorrect syntax near 'GO'.
> Line 9: Incorrect syntax near 'GO'.
> 'CREATE PROCEDURE' must be the first statement in a query batch.
> Must declare the variable '@.tiRequestStatusID'.
> Must declare the variable '@.tiRequestStatusID'.
> Must declare the variable '@.iWireAccessRequestID'.
> Must declare the variable '@.iProcessedByID'.
> A RETURN statement with a return value cannot be used in this context.
> Line 123: Incorrect syntax near 'GO'.
> Line 126: Incorrect syntax near 'GO'.
> Line 128: Incorrect syntax near 'GO'.
> Line 131: Incorrect syntax near 'GO'.
> Is there a "best practice" for executing a batch script
> programmatically in .NET 1.x' Should I spawn an OSQL command line
> for each script file?
"GO" is not a T-SQL keyword. It is a user-defined batch separator that
tools like Query Analyzer understand and use to parse a script into
individual batches. What I normally do when I write batch processing
programs is to leave the the "GO" statements in the file (you can use
any batch separator you want). You should read the file, line by line,
and every time you encouter a "GO" fire off the batch to SQL Server
(without the GO line) and continue through the file.
David Gugick - SQL Server MVP
Quest Software

ADO, SQL Server 2000 and VB5

I am use ADO, SQL S.erver 2000 and aplication in VB 5
When I execute recordset.open VB return error 6 overflow.

Set rsSql = New ADODB.Recordset
rsSql.ActiveConnection = oConn
rsSql.CursorLocation = adUseClient
rsSql.CursorType = adOpenDynamic
rsSql.LockType = adLockPessimistic
rsSql.Index = "ChaveAto"

Sqlquery = "select id_ato from ato where chave_ato = " & "MP00002183200108240120010827PR"

' MsgBox (Sqlquery)

rsSql.Open Sqlqueryif chave_ato is a char/varchar data type you might need to enclose MP00002183200108240120010827PR in quotes.

ado - stored procedure warnings

I am trying to run a stored procedure in an access front end using ado and the execute command. The process fails with the error "Warning: Null value is eliminated by an aggregate or other SET operation." - I get this warning when running the sp in query analyser but I know this is not a problem and it just continues. Is there a way to get ado to ignore these warnings and continue the code.

thanksOnce you establish a connection use <Connection Object>.Execute "SET ANSI_WARNINGS OFF", , adCmdText.

You might also want to issue a SET NOCOUNT ON.

Administrative Tasks

Hi
Can somebody please point me to good URL or may be some scripts that I can
use to execute as part of DB health check.
Also, I would like to see if the DB is well designed or thought of while it
was created; I know its a wide subject, but I will be happy to get started
with some script that you guys have been using in your environment.
TIA
PPHi
You may want to read up on maintenance plans in Books online along with
other procedures such as the DBCC commands. Also check out the article
http://www.sqlservercentral.com/col...enanceplans.asp s
well as the other scripts/articles on this site. If you want to do things
yourself you may want to look at what the expressmaint procedure at
http://www.sqldbatips.com/code.asp does.
John
"PP" wrote:

> Hi
> Can somebody please point me to good URL or may be some scripts that I can
> use to execute as part of DB health check.
> Also, I would like to see if the DB is well designed or thought of while i
t
> was created; I know its a wide subject, but I will be happy to get started
> with some script that you guys have been using in your environment.
> TIA
> PP|||SQL Server 2000 Administrator's Pocket Consultant
http://www.microsoft.com/technet/pr...in/sqlops0.mspx
SQL Server 2000 Resource Kit
http://www.microsoft.com/technet/pr...it/default.mspx
Microsoft SQL Server Developer Center - Architecture and Design
http://msdn.microsoft.com/sql/learn/arch/
SQL-Server-Performance.Com
http://www.sql-server-performance.com/
"PP" <PP@.discussions.microsoft.com> wrote in message
news:AA0FF976-9140-42B6-BDF4-8A741D03DAF5@.microsoft.com...
> Hi
> Can somebody please point me to good URL or may be some scripts that I can
> use to execute as part of DB health check.
> Also, I would like to see if the DB is well designed or thought of while
> it
> was created; I know its a wide subject, but I will be happy to get started
> with some script that you guys have been using in your environment.
> TIA
> PP