SQL Stored Procedure DOES NOT Execute Using ADODB In Excel VBA
Nov 15, 2007
Hi to All,
I have the codes below in excel VBA that always returns a "-1" value of the rs.RecordCount. The sql connection is open, BUT IS NOT ABLE TO EXECUTE THE STORED PROCEDURE. Anyone please I need help ;(
Database: SQL Server 2005
Programming language: VBA (Excel 2003)
Dim cn as new ADODB.Connection
Dim cmd as new ADODB.Command
Dim rs as new ADODB.Recordset
Dim connstr as string
Hi!I already sent this to the ACCESS newsgroup. But since I do not know reallywhich side is really causing the problem, I have decided to send thisinquiryto this newsgroup also, if I may.Below is the environment of the application:a. MS Access 2003 application running on Windows XPb. SQL Server 2000 - backend running MS Server 2003 OSBelow is the code that is giving me an error:Dim com As ADODB.CommandSet com = New ADODB.CommandWith com.ActiveConnection = "DSN=YES2;DATABASE=YES100SQLC;".CommandText = "sp_Recalculate".CommandType = adCmdStoredProc.Parameters.Refresh.Parameters("@ItemNumber") = ItemNum.Execute ' This is where it hangs up...TotalItems = .Parameters("@TotalInStock")TotalCost = .Parameters("@TotalCost")End WithSet com = Nothingand the store procedure is:CREATE PROCEDURE DBO.sp_Recalculate@ItemNumber nvarchar(50),@TotalInStock int = 0,@TotalCost money = 0ASBEGINSET @TotalInStock = (SELECT Sum([Quantity in Stock])FROM [Inventory Products]WHERE [Item Number] = @ItemNumber)SET @TotalCost = (SELECT Sum([Cost] * [Quantity in Stock])FROM [Inventory Products]WHERE [Item Number] = @ItemNumber)ENDWhen the process goes to the ".Execute" line, it hangs up for a long timethen gives me an error message "Everflow". I have been trying to solvethis issue but do not have an idea for now of the cause.Below is my finding:a. When I run the stored procedure in the SQL analyzer, it works just fine.I placed a SELECT statement to view the result of the stored procedure.It gives the correct values.Can anyone have ideas or similar problems?Thanks.
We have an ASP 3.0 application that currently works "correctly" on one server, Server A, and we€™re testing it on another server, Server B, which is 64 bit.
For this example, the stored procedure sp_TestProcedure is:
CREATE PROCEDURE sp_TestProcedure @Name varchar(50) AS INSERT INTO tblTest ([Name], Date) VALUES (@Name, getDate()) SELECT COUNT(*) AS 'Count' FROM tblTest
The basic point is the stored procedure does an INSERT and then a SELECT.
Now... to the issue. On Server A, the variable rs above ends up with a single open Recordset which is the results of the SELECT statement. However, on Server B, rs is set to a closed recordset, and rs.NextRecordset() gets a second recordset of the results of the SELECT statement.
I understand what's going on. Server B is first returning the number of rows affected by the INSERT which translates to a closed recordset. But Server A does not do this.
I would like to know why the default behavior of the command's .Execute is different on the different servers. Does it relate to the Provider/Driver settings in the connection string? Does it have anything to do with 64 bit VS. 32 bit servers?
I know that one way to address this issue to add SET NOCOUNT ON to the start of the stored procedure. But we have many stored procedures, and if the solution is a change in the connection string, that would be preferred. Also, whatever the possible solution is, I also looking to discover *why* it's happening.
I use VBA to call a MSSQL server 2000 to get data from one of the DB inside, using ADODB.
My problem is, the DB is around 20GB, and if we get data from it for few days, say 7 days, is fine. When I come to trying get more data like 1 month, the excel returns no data after waiting for about 30 sec.
The query is actually calling a stored procedure on the server side to return data.
I have add connectiontimeout=10000 to my connection string but still not working...
I run the same execute statement in query analyzer on the same machine using the Excel and it is fine. Around 2 minute waiting I can see my data. But in Excel, never.
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
Hello, I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first? The Stored Procs are: CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency @CID int, @CY char(3) AS SELECT Distinct S.BalancePoolName FROM SiteRef S INNER JOIN Account A ON A.PoolId=S.ID Inner JOIN AccountBalance AB ON A.Id = AB.AccountId Inner JOIN AccountPool AP On AP.Id=A.PoolId Where A.CompanyId=@CID And AB.Currency=@CY
CREATE PROCEDURE USP_BalanceDateByAccountPool @PoolName varchar(50) AS Declare @DT datetime Select @DT= (Select MAX(AccountBalance.DateX) From Company Company INNER JOIN Account Account ON Company.Id = Account.CompanyId INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId WHERE SiteRef.BalancePoolName = @PoolName) SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance FROM Company Company INNER JOIN Account Account ON Company.Id = Account.CompanyId INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT Order By AccountBalance.DateX DESC
Any assistance would be greatly appreciated. Thank you, Dave
hi have written SSIS script and i am using script component to Row count below my code what i have written. and i am getting error below i have mention...after code see the error using System;
} the error Script component has encountered an exception in user code Object is not an ADODB.RecordSet or an ADODB.Record. Parameter name: adodb at System.Data.OleDb.OleDbDataAdapter.FillFromADODB(Object data, Object adodb, String srcTable, Boolean multipleResults) at System.Data.OleDb.OleDbDataAdapter.Fill(DataTable dataTable, Object ADODBRecordSet) at ScriptMain.CreateNewOutputRows() at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend. Â As a result, I'm going to write a number of Stored Procedures to replace the MS Access code. Â My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-
USE [VMSProd] GO /****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
How can I use in stored procedure the faction ‘’in’’ to select values, using execute and the values must not be specified before in the stored procedure
How do you execute a stored procedure in MS SQL Server?I can design and execute them from MS Access dev front end, but cannot seemto find how to run them in the Enterprise Manager.TIA.~ Duane Phillips.
I have setup a user which has execute rights on a stored procedure. The sp is owned by dbo. The user can execute the stored procedure, but it fails, because the stored procedure calls other tables and procedures that the user does not have rights to. Is there a way to allow those procedures to execute without allowing access to everything else for the user I setup? Thanks!
I need to disable and move orphaned computer objects in my Active Directory. The SQL Agent has permission to do this. I have created a stored procedure for the task with intentions of executing it with sp_start_job. However, I cannot execute it in SQL 2005. How can I grant permission to this (login) to execute sp_start_job? This is all run from a web page and NOT the Query Window.
Please anyone help me with my question How do I execute a stored Procedure using SSIS ? I have a stored procedure in SQL SERVER 2005 database that I need to execute using a SSIS package using Execute SQL Task from Toolbox in Visual studio 2005
Hi, I am strugling to execute a insert stored procedure on a button click. The stored procedure is taking values from a temp table and inserting them into a identical table. The procedure is expecting 1 value from a Query string, the stored procedure works as expected when hard coded.
Im completely new to this and have no idea where to begin, i have been looking through the forums for several hours and am still none the wiser.
please can someone point me in the right direction
i have a stored procedure and i want to run a program from it.. i think that i need use api functions but how can i do that.. if there is a nother ways please tell me..
I have a button in a form that I want to execute a simple stored procedure to insert a single value into a database table. The Stored Procedure is:=================CREATE PROCEDURE bplog_insert_invoice_detail (@invo_Id Int) ASINSERT INTO Invoice_Detail (Invo_Id) VALUES(@Invo_Id)GO=================How do i pass the value from and text field (@invo_id) and execute the stored procedure when a button is clicked.Regards.
Hello,I was wondering if it is possible to dynamically execute a stored procedure; for example, in SQL, you can do:insert into Table1( id, name)select id, namefrom Table2Can you do something like:exec spProc @id = id, @name = namefrom Table1Or something like that? I know I can select a row at a time and execute, or write a program, but I was looking to see if there was an easier way.Thanks.
Hello, I'm having trouble trying to execute a simple stored procedure. Could someone please take a look at this and let me know where I went wrong. Dim cn As SqlConnection = New SqlConnection("Data Source=localhost;Initial Catalog=TEST;Persist Security Info=True;User ID=sa;Password=test") Dim cmd As SqlCommand = cn.CreateCommand cn.Open() cmd.CommandText = "cssp_family" cmd.CommandType = CommandType.StoredProcedure cmd.ExecuteNonQuery() cn.closed
Within a stored procedure, is it possible to call multiple other stored procedures asychronously? For example, I'd like to execute both local and remote stored procedures, but don't want/need to wait for the output while the original stored procedure continues to execute each subsequent command.
Hi, hoping someone may be able to shed some light on this.
I have a stored procedure that conditionally executes different DTS Packages. Everything seems to work, there's no errors at all, but the DTS Packages are never executed. Wondering if anyone has any ideas.
Here's the stored procedure....
CREATE PROCEDURE exportLists AS
DECLARE @script VARCHAR(8000) DECLARE @id INT DECLARE @max INT
select regionid,count(distinct homephone) as phone into #phone from tbl_template Where exportid is null group by regionid
select officeid,listpath into #office from tbl_office WHERE startdate <= GETDATE() AND (enddate >= GETDATE() OR enddate IS NULL) and listpath is not null and listpath <> ''
Select case when phone > 2500 then '"exportLists" /A MDBFile="' + listpath + '" /A OfficeID ="' + convert(varchar(10),officeid) + '" ' Else '"resetTemplate" /A OfficeID="' + convert(varchar(10),officeid) + '" ' end as cmd ,Identity(int,1,1) as rowno ,phone into #temp from #office as O inner Join #phone as p on o.officeid=p.regionid
Set @max=@@rowcount set @id = 1
While @id <=@max Begin Select @script='"Dtsrun /S "' + @@SERVERNAME + '" /E /N ' + cmd + case when phone > 2500 then '/A ExportID="' + convert(varchar(10),(Select coalesce(Max(listid),0) + 1 from tbl_sf3Lists)) + '" ' else ' ' end , @id=@id+1 from #temp Where rowno = @id End
Which fixed database role allows a user to execute a user defined stored procedure while minimizing the amount of permissions given. I think db_Datareader will do the trick.
I am trying to execute the following simple stored procedure. The procedure runs but doesn't create the table I am trying to create and doesn't insert the records into the table I created. But again, the procedure runs without any errors.
Can someone please correct the syntax so my procedure will run properly. It pretty basic.
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[PremierData1]
AS
IF EXISTS(SELECT 1 FROM sys.sysojects where type='U' AND name='PremierTest') DROP TABLE dbo.[PremierTest]
BEGIN
CREATE TABLE dbo.[PremierTest] ( [AYQ] nvarchar(6) null , [CYQ] nvarchar(6) null , [Description] nvarchar(50) null, [PIP] [decimal] (17,2) , [BI] [decimal](17,2) Not null, [PD] [decimal](17,2) Not null, [COLL] [decimal](17,2) not null, [COMP] [decimal](17,2) not null, [DCRAT] [nvarchar](2) null , [Agent][nvarchar](3) null , ) ON [Primary]
insert into dbo.premiertest select * from dbo.[new agt type tri 0804] where dir_ceded_ind = 'c'
I don't know if this is a newbie question or not, I want SQL Server to automatically execute a stored procedure at the first of each month, how can I do this ?
Regards, AJ
Be nice to nerds. Chances are you'll end up working for one. (Bill Gates)
Is there a programatic way to execute an SSIS package (passing the appropriate parameters) from within a stored procedure. I've seen some examples using xp_CmdShell, but would rather not go that route if possible
I have a sql server resultset that returns productid numbers. I would like to loop thru this resultset and run a stored procedure using the productid as the input parameter. Any samples would be helpful. thanks
Can i execute a URL in tsql stored procedure. Waht i want's is to hit a url on some event. I know i can do it in CLR stored procedure but for that i have to deploy assembly on the server which i want's to avoid. Is there any way i can hit a url from tsql stored procedure
SQL 7.0 Stored Procedure Execute Privileges Only?Does anyone know if a user/pass can be set up to givea sql 7.0 user stored procedure execute privileges only.That way, even if they get into the database, they cando nothing except run the stored procedures.Thanks,Ed
