Calling Sql Server Stored Procedures From C#

Apr 3, 2008



I have a simple stored procedure:


create procedure sp_testres

@mult1 int,

@mult2 int,

@result int output

as

select @result = (10*@mult1) + @mult2

go



When I call it


declare @result int

exec sp_testres 5, 6, @result output

print @result

(Result is correctly shown as 56).

I then in C# wrote the following:



m_cmd.CommandText = "sp_testres";

SqlParameter param2 = new SqlParameter("@mult2", SqlDbType.Int);

param2.Value = 6;

SqlParameter param1 = new SqlParameter("@mult1", SqlDbType.Int);

param1.Value = 5;

SqlParameter param3 = new SqlParameter("@result", SqlDbType.Int);

param3.Direction = ParameterDirection.Output;

m_cmd.CommandType = CommandType.StoredProcedure;

m_cmd.Parameters.Add(param1);

m_cmd.Parameters.Add(param2);

m_cmd.Parameters.Add(param3);

m_cmd.ExecuteNonQuery();


This works and param3.Value holds the result value.
I also notice that I can supply the parameters in any order, and things work fine.

What I want to know is: can I call the stored procedure with parameters, where I haven't supplied the parameter name, and just rely on the parameter order matching instead?

View 5 Replies


ADVERTISEMENT

Calling Stored Procedures From C#

Jul 1, 2007

Hi All...  I'm calling a stored procedure from C#.  I'm sending it input parameters as follows:SqlCommand c = new SqlCommand("AddAuthor", myConnection);c.CommandType = CommandType.StoredProcedure;
c.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 100));c.Parameters["@LastName"].Value = "Last";
c.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 100));c.Parameters["@FirstName"].Value = "First";
In my opinion, that works nice - and it's easy.  The reference I'm using says I can also specify "output" parameters - instead of supplying a value, one needs to change a property called direction. 
But my stored procedure doesnt return any output parameters per se, but it does return a value.  That is the last statement in the stored procedure is "RETURN @@IDENTITY" - it returns the identity field after an INSERT...  So how do I get that value back in my C# code?
Thanks for the help in advance.  Happy 4th to all!  : )    -- Curt
 

View 4 Replies View Related

Calling Stored Procedures Via Vb.net

Jan 16, 2008

Hi,
 I was wondering if someone can tell me why my sub for calling stored procedures doesn't work.The string strFinFileId is not null.
I keep getting the following error System.IndexOutOfRangeException: Total
Here's my sub.
 '//get GrandTotal    Protected Sub GetGrandTotal(ByVal finfileid As String)        Dim myConnection As SqlConnection        Dim connString As String        Dim strFinFileId As String        Dim strGrandTotal As Decimal
        strFinFileId = finfileid        connString = ConfigurationManager.ConnectionStrings("PMOConnectionString1").ConnectionString
        myConnection = New SqlConnection(connString)        'you need to provide password for sql server        myConnection.Open()
        Dim sql1 As String
        sql1 = "GetGrandTotal " & Convert.ToInt32(strFinFileId)
        Dim myCommand As SqlCommand
        'Response.Write(sql1)        'Response.End()
        myCommand = New SqlCommand(sql1, myConnection)
        Dim reader As SqlDataReader = myCommand.ExecuteReader()        While reader.Read()            strGrandTotal = reader.Item("Total")            LabelGrandTotal.Text = strGrandTotal            'Response.Write(strFinFileId)            'Response.End()
        End While        reader.Close()        myConnection.Close()    End Sub
 Cheers
Mark :) 
 

View 2 Replies View Related

Help W. Calling Stored Procedures

Apr 16, 2008

I have a stored procedure written to update a table:
The stored procedure  has the following parameters:
@Original_TypeID int,
@Type_Desc varchar(35),
@Contact_Name varchar(20),
@Contact_Ad1 varchar(25),
@Contact_Ad2 varchar(25),
@Contact_City varchar(10),
@Contact_Phone varchar(12),
@Contact_Fax varchar(12),
@Contact_Email varchar(35)
And I want to call this procedure when the "update" button is clicked, my code is:Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("myConnectionString").ConnectionString)Dim myCommand As SqlCommandDim UpdteParam As New SqlParameter()
myConnection.Open()myCommand = New SqlCommand("[dbo].[sp_Update_Types]", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
????????????????????????myCommand.Parameters.Add(UpdateParam)
 
???????????myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub
 
Can you please help me with the ????? areas? I don't know how to pass the parameters into the procedure, and also the second set of ??? s, I'm not sure if I am executing the command correctly.
Thank you.

View 3 Replies View Related

Stored Procedures Calling DLL's

Jun 21, 2001

I'm relatively new to SQL Server and I was wondering if there is any way to call a DLL from a regular stored procedure? I believe I've read that the extended stored procedure is a DLL and therefore should be able to call another DLL. Can this also be verified? We're looking to speed up some sections of Visual Basic code which does some heavy I/O work. I've also asked this once before but didn't get an absolute answer - can you write the extended stored procedures in Visual Basic or is C/C++ the only language supported right now?

Thanks.

Caroline Kaplonski
ckaplonski@buckconsultants.com

View 2 Replies View Related

Calling Stored Procedures

Jul 20, 2005

Hi AllI was wondering if there is a way to call a stored procedure from insideanother stored procedure. So for example my first procedure will call asecond stored procedure which when executed will return one record and iwant to use this data in the calling stored procedure. Is this possible ?Thanks in advance

View 18 Replies View Related

Calling Stored Procedures From Different DBs

Jul 20, 2005

Is it possible to have a stored procedure in database A while callingit from database B and have it manipulate the tables in database B(whatever the calling database happens to be)?We have a large-scale app that uses many complex stored procedures,and as of now, we're copying the SPs to every new database that iscreated, and it will soon become a nightmare for propagating updatesand fixes. We'd like to keep a master set of the SPs in one DB and"use" them from other DBs so that they only query data and manipulatetables in the calling DB. I hope someone has some suggestions. Thanks.

View 1 Replies View Related

Calling Stored Procedures In SQL Statement

Aug 25, 2007

How can we call Stored Procedure inside any SQL Statement
For Example.
If we have procedure name sprocCurrentPriority
select * from tablename where colmunname = exec sprocCurrentPriority

View 7 Replies View Related

Calling Stored Procedures On Different Servers

Oct 4, 2004

I'm using reporting services to build a report and I plan on using a stored procedure to gather my data for the report. My question is:

I know it's possible to call a stored procedure from a stored procedure, both within the same dB and in different dB's, but is it possible to call a SP that's on a different server? My gut says "definitely not. at least not easily." But I wanted to see if anyone had any thoughts on this before I pursue a different course of action.

View 3 Replies View Related

Calling Stored Procedures In MS Access

Nov 3, 2000

can anyone tell me how to call SQL stored procedures in MS Access97...
appreciate any help

View 4 Replies View Related

Calling Stored Procedures From A Web Page

Jan 15, 2001

I am currently developing web pages based on data pulled from SQL Server with ASP. I've mastered the art of SELECTing, INSERTing, etc, but want to know if it is possible to run STored Procedures from a page by clicking on a button on a particualr web page.

View 1 Replies View Related

Calling Stored Procedures Using Plain ADO In C#

Sep 7, 2006

Hello All,

We are trying to figure out how to make a stored procedure call and pass some inputs using C# and plain ADO. We are able to call an empty stored procedure but cannot pass parameters into a stored procedure.

Does anyone have sample code in C# whereby we can open a connection and pass inputs into a stored procedure? The following is a sample code we are using:


ADODB.Parameter param = new ADODB.Parameter();

param=cmd.CreateParameter("@StoreID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput,sizeof(int) , 9);

cmd.Parameters.Append(param);

          ADODB.Recordset rsInv = cmd.Execute(out objAffected, ref objAffected,-1 );

In the above example, we create a parameter of type integer and try to add it to the ADO Command object. It is supposed to return a recordset. It returns a recordset with record count -1.

 

Sincerely,

Dwight Kulkarni

View 3 Replies View Related

Calling Stored Procedures From Another Stored Procedure

May 8, 2008

I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.

The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?

Thanks in advance

John

View 5 Replies View Related

Calling Stored Procedures Inside Cursors

Aug 13, 2001

I have created a cursor using the following type of syntax:

DECLARE MyCursor CURSOR FOR
SELECT ID FROM tblEmployees
OPEN MyCursor
BEGIN
FETCH NEXT FROM MyCursor
END
CLOSE MyCursor

Instead of the SELECT statement (SELECT ID FROM tblEmployees), I actually have a very complex select statement. I have created a stored procedure to handle this select. However, I cannot find a way to call a stored procedure in place of the SELECT statement in the cursor. Is this possible? Thanks.

View 2 Replies View Related

Calling Stored Procedures In A Select Statement

Feb 26, 2004

I am trying to call a stored procedure inside a SQL SELECT statement. Has anybody had to do this in the past? I have a SELECT statement in a Microsoft Access database and I need that SELECT statement to call the stored procedure in the SQL server. Any help would be appreciated

View 4 Replies View Related

Calling Stored Procedures From Back End C# Codings In ASP .net Designing

Nov 21, 2007

I writte a stored procedure for username , password ....
How can i call that stored procedure to verify the username & password 
then if both match in the database redirected to next page..? in asp with c#.net back end programming
 

View 2 Replies View Related

Problems Calling A Stored Procedures Depending On Parameters

Dec 10, 2007

Hi guys, hoping one of you may be able to help me out. I am using VS 2005, and VB.net for a Windows application.
I have a table in SQL that has a list of Storedprocedures:  Sprocs Table: SPID - PK (int), ID (int), NAME (string), TYPE (string)The ID is a Foreign key (corresponding to a Company ID), the name is the stored procedure name, and Type (is the type of SP).
On my application I need to a certain SP depending on the company selected and what page you are on. I have a seperate SP that passes in parameters for both Company, and Type and should output the Name value:
ALTER PROCEDURE [dbo].[S_SPROC] ( @ID int, @TYPE CHAR(10), @NAME CHAR(20) OUTPUT )AS
SELECT @NAME = NAME FROM SPROCSWHERE [ID] = @IDAND [TYPE] = @TYPE
Unfortunately I dont seem to be able to get the output in .Net, or then be able to fill my dataset with the Stored Procedure.Has anyone done something similar before, or could point me in the right direction to solving this problem.
ThanksPhil
 

View 8 Replies View Related

Using EXECUTE Statements Calling An Extended Stored Procedures From Function..

Apr 29, 2004

Hi, all
I'm using Sql server 2000
I want to make select statement dynamically and return table using function.
in sp, I've done this but, in function I don't know how to do so.
(I have to create as function since our existing API..)

Following is my tials...
1.
alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS

RETURN Exec spTest @from, @to
GO

Yes, it give syntax error..

2. So, I found the following


From Sql Server Books Online, Remark section of CREATE FUNCTION page of Transact-SQL Reference , it says following..

"The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function: "
.....
* EXECUTE statements calling an extended stored procedures.

So, I tried.

alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS

RETURN Exec master..xp_msver
GO

It doesn't work... syntax err...

Here I have quick question.. How to execute statements calling an extended stored procedures. any examples?


Now, I'm stuck.. how can I create dynamic select statement using function?

I want to know if it's possible or not..

View 13 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

Calling DB2 Store Procedures From SQL Server 2000

Feb 5, 2004

I am trying to call DB2 stroe procedure from within SQL server 2000 using DTS. I have the IBM odbc driver installed on the server. I have created an ACtiveX script to run in DTS and it fails staing it could not findor load the DB2 store procedure.

Has anyone come across doing this and how they did it?

THanks for the help....

View 4 Replies View Related

Calling Stored Procedures From ADO.NET-VB 2005 Express:1)Compile Errors && Warnings,2)Northwind Database In Database Explorer?

Feb 13, 2008

Hi all,

I try to learn "How to Access Stored Procedures with ADO.NET 2.0 - VB 2005 Express: (1) Handling the Input and Output Parameters and (2) Reporting their Values in VB Forms". I found a good article "Calling Stored Procedures from ADO.NET" by John Paul Cook in http://www.dbzine.com/sql/sql-artices/cook6. I downloaded the source code into my VB 2005 Express:



Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form_Cook

Inherits System.Windows.Form.Form

#Region " Windows Form Designer generated code "

Public Sub New()

MyBase.New()

'This call is required by the Windows Form Designer.

InitializeComponent()

'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.

Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)

If disposing Then

If Not (components Is Nothing) Then

components.Dispose()

End If

End If

MyBase.Dispose(disposing)

End Sub

'Required by the Windows Form Designer

Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer

'It can be modified using the Windows Form Designer.

'Do not modify it using the code editor.

Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox

Friend WithEvents labelPAF As System.Windows.Forms.Label

Friend WithEvents labelNbrPrices As System.Windows.Forms.Label

Friend WithEvents UpdatePrices As System.Windows.Forms.Button

Friend WithEvents textBoxPAF As System.Windows.Forms.TextBox

Friend WithEvents TenMostExpensive As System.Windows.Forms.Button

Friend WithEvents grdNorthwind As System.Windows.Forms.DataGrid

Friend WithEvents groupBox2 As System.Windows.Forms.GroupBox

<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

Me.GroupBox1 = New System.Windows.Forms.GroupBox()

Me.labelPAF = New System.Windows.Forms.Label()

Me.labelNbrPrices = New System.Windows.Forms.Label()

Me.textBoxPAF = New System.Windows.Forms.TextBox()

Me.UpdatePrices = New System.Windows.Forms.Button()

Me.groupBox2 = New System.Windows.Forms.GroupBox()

Me.TenMostExpensive = New System.Windows.Forms.Button()

Me.grdNorthwind = New System.Windows.Forms.DataGrid()

Me.GroupBox1.SuspendLayout()

Me.groupBox2.SuspendLayout()

CType(Me.grdNorthwind, System.ComponentModel.ISupportInitialize).BeginInit()

Me.SuspendLayout()

'

'GroupBox1

'

Me.GroupBox1.Controls.AddRange(New System.Windows.Forms.Control() {Me.labelPAF, Me.labelNbrPrices, Me.textBoxPAF, Me.UpdatePrices})

Me.GroupBox1.Location = New System.Drawing.Point(8, 8)

Me.GroupBox1.Name = "GroupBox1"

Me.GroupBox1.Size = New System.Drawing.Size(240, 112)

Me.GroupBox1.TabIndex = 9

Me.GroupBox1.TabStop = False

'

'labelPAF

'

Me.labelPAF.Location = New System.Drawing.Point(8, 16)

Me.labelPAF.Name = "labelPAF"

Me.labelPAF.Size = New System.Drawing.Size(112, 32)

Me.labelPAF.TabIndex = 2

Me.labelPAF.Text = "Enter Price Adjustment Factor"

'

'labelNbrPrices

'

Me.labelNbrPrices.Location = New System.Drawing.Point(8, 80)

Me.labelNbrPrices.Name = "labelNbrPrices"

Me.labelNbrPrices.Size = New System.Drawing.Size(216, 16)

Me.labelNbrPrices.TabIndex = 5

'

'textBoxPAF

'

Me.textBoxPAF.Location = New System.Drawing.Point(120, 16)

Me.textBoxPAF.Name = "textBoxPAF"

Me.textBoxPAF.TabIndex = 0

Me.textBoxPAF.Text = ""

'

'UpdatePrices

'

Me.UpdatePrices.Location = New System.Drawing.Point(8, 48)

Me.UpdatePrices.Name = "UpdatePrices"

Me.UpdatePrices.Size = New System.Drawing.Size(88, 23)

Me.UpdatePrices.TabIndex = 6

Me.UpdatePrices.Text = "Update Prices"

'

'groupBox2

'

Me.groupBox2.Controls.AddRange(New System.Windows.Forms.Control() {Me.TenMostExpensive, Me.grdNorthwind})

<Part 1----To be continued due to the length of this post>

View 1 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

Calling A .NET Dll From A SQL Server 2005 Stored Procedure

Sep 13, 2006

Setup:  I have a C# 2.0 class library that contains certain business logic that is to be triggered by certain database states.  There is a trigger that calls a stored procedure that is working properly (i.e. the stored procedure is being executed). Problem:  I have not yet figured out how to call the dll from the stored procedure.  Does anybody have any tutorials they could point me to or any advice to point me in the right direction? Thanks in advance for any help. 

View 2 Replies View Related

Calling Linked Server Stored Procdure From Another

Oct 27, 2007

Hi Sir,

I want to call one linked server stored procedure from another stored procedure.

--BATCH FOR LINKED SERVER SETUP

SET @IBatchNum = 2


EXEC PR_Linked_Server

@VServer = 'Linked_Amisys'
,@VSrvProduct = ''
,@VProvider = 'SQLOLEDB'
,@VDataSrc = 'DTCORPMXDV9'
,@VCatalog = 'pubs'
,@VCurrentUsr = 'CORPajohri'
,@VServerUsr = 'dba'
,@VServerPwd = 'dba'

END TRY

The linked server was not created When I executed the query. However, I was able to create the linked server when I executed this query(batch 2) alone.
Is there any limitation of SP which I am not aware of?

View 1 Replies View Related

Off Topic - Calling Procedures From MS Access

Nov 15, 1999

Hi all!

Is there a possibility to call (and receive records) SQL Server (or other databases) procedure from MS Access?

View 1 Replies View Related

SQL Server 2012 :: Can't Get Row Count To Return To Calling Stored Procedure

Jul 9, 2014

SQL Server 2012 Standard SP 1.

Stored procedure A calls another stored procedure B. Rowcount is set properly in called procedure B, but does not seem to return it to calling procedure A. Otherwise the two stored procedures are working correctly. Here is the relevant code from the calling procedure A:

declare @NumBufferManagerRows int = 0;
exec persist.LoadBufferManager @StartTicks, @EndTicks, @TimeDiff, @NumBufferManagerRows;
print 'BufferManagerRows';
print @NumBufferManagerRows;

Print statement prints @NumBufferManagerRows as 0.

Here is the called stored procedure B:

CREATE PROCEDURE [persist].[LoadBufferManager]
-- Add the parameters for the stored procedure here
@StartTicks bigint,
@EndTicks bigint,
@TimeDiff decimal(9,2),
@NumRows int OUTPUT

[Code] ...

View 2 Replies View Related

SQL Server 2008 :: Calling Stored Procedure Into Report Builder

Oct 23, 2015

I am trying to call a stored procedure into report builder. The stored procedure has 2 parameters. When I run the report with allowing nulls and blank value in the parameters it works fine, but when I enter a value in a parameter it ignores the where clause I had in the original query(stored procedure) and displays everything.

View 3 Replies View Related

Calling An SQL Server 2005 Stored Procedure Within Microsoft Access And Reading Values

Jan 14, 2008

Goodday.

I have finally been able to create a connection from Access to the SQL 2005 Server and was able to call a stored proc (in Server) in the following way





Code Block
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

cnn.ConnectionString = "Provider='sqloledb'; Data Source='Private';" & _
"Initial Catalog='DBName';Integrated Security='SSPI';"

cnn.Open

cmd.ActiveConnection = cnn
cmd.CommandText = "sp_DefaultEntityData"
cmd.CommandType = adCmdStoredProc

Set rst = cmd.Execute


rst.MoveFirst
Do While Not rst.EOF
lstEntity.AddItem (rst.Fields(0)), 0
'lstEntity.AddItem (rst.Fields(1)), 1
rst.MoveNext
Loop






The Stored Proc is as follow:




Code Block
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_DefaultEntityData]
AS
BEGIN

SET NOCOUNT ON;

SELECT tblEntities.[Name], tblEntities.PrimaryKey
FROM tblEntities
ORDER BY tblEntities.PrimaryKey;

END






The table contains 24 entries

As you can see in the VB code, I am trying to read the returned "table" into an Access ListBox. The listbox should display the entities Name but not the Primary key, but the primary key should still be "stored" in the to so that it can be used to access other data.

I have moved the tables from Access to SQL Server 2005, and would also like to port all the sql queries to sp's in SQL Server. The old way for populating the listbox was a direct SQL query in the RowSource property field. I have tried to set the lstEntity.RowSource = rst but it did not work.

Here are my Q's:
1) As what does the SP return when it is called and is there a better way to catch it than I am doing at the moment?
2) How do I read the values into the listbox, without displaying the primary key in die box?

Thank you in advance!
Any help is very much appreciated.

View 2 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related

SQL Server Stored Procedures/VB.Net

Mar 24, 2004

IS there a way inside code for vb.net to programmatically change sorting in a stored procedure???Reason is I need a two way sort and did not want to have to write a SP for each way ....If so Do you have an example.....

View 3 Replies View Related

Stored Procedures In Sql Server 6.5

Jan 4, 2000

Hi,

Everytime after I restore any database on the sql server 6.5, I have to recompile all the stored procedures in that database.Is there any method to overcome this problem?


Thanks,
Manu.

View 1 Replies View Related

ADO And SQL Server Stored Procedures

Apr 19, 1999

Trying to run a SQL Server 6.5 stored procedure via ADO 2.0 in Visual Basic 6. The stored procedure moves roughly 10000 records from one table to another within the same database. The procedure works just fine when run from a SQL editor such as I/SQL, but only transfers a few hundred records when it is executed from a VB application using ADO 2.0.
The guilty code can be seen below. Any thoughts?

Public cnSQL As New ADODB.Connection
Public qry As New ADODB.Command

With cnSQL
.ConnectionString = ConnectStringSQL
.ConnectionTimeout = 20
.Open
End With

Set qry.ActiveConnection = cnSQL
qry.CommandType = adCmdStoredProc
qry.CommandTimeout = 120

qry.CommandText = "sp_VGInsertBOMStructure"
qry.Execute

Peter

View 1 Replies View Related

ADO SQL/Server Stored Procedures

Jun 6, 2002

I have a stored procedure on SQL/Server 2000 that inserts into a #temptable. When I execute it from Query Analyzer it works fine.
When I execute it from an ASP using ADO it gives me an Open Error message. When I comment out the
#temptable the ASP works fine. Can someone shed some light on this? Do I have to do an ADO opent on the
#temptable?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved