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
ADVERTISEMENT
Nov 3, 2005
I am using sql 2005 beta
View 23 Replies
View Related
Mar 19, 2002
Whenever I run this DTS package that has a SQLTask, I get the following error message: "Maximum stored procedure nesting level exceeded(Limit 32)"
What does this mean? My query was working fine, but now I'm recieving this error message. I won't complet my required task. Is there any way to change the nesting level to be unlimited. I am totally confused. If you can please help. Thanks again.
Dre
View 1 Replies
View Related
Apr 4, 2007
I have the following stroed procedure and need someone to tell me what i am doing wrong. First off i am passing the value of sName from ASP to my sql stored procedure.
the following will work if i do (select distinct * )
Code:
SQL:
CREATE PROCEDURE Get_codes
@sName varchar(255)
AS
Select DISTINCT @sName
From CheckDetail
Where @sName is not Null and @sName <> ''
/*Order by @sName*/
GO
View 1 Replies
View Related
Feb 21, 2008
I am using a stored procedure to update data while in detailsview.I am having a rough time. Dont know why this wont workthanks a lotheres my codeALTER PROCEDURE UpdateCustomerInfo ( @CustomerID as INT, @LName as varchar(50), @FName as varchar(50), @Dept as varchar(50)=NULL, @PhoneType as varchar(50)=NULL, @OfficeType as varchar(50) = NULL, @Ext as Int=NULL, @BridgeExt as int=NULL, @Workstation as varchar(50)=NULL, @OldPrinters as varchar(50)=NULL, @NewPrinters as varchar(50)=NULL, @PhoneImg as varchar(50)=NULL, @OfficeImg as varchar(50)=NULL, @Complete as Bit=NULL, @FaxModel as varchar(50)=NULL, @FaxNumber as varchar(50)=NULL, @ConfPhone as varchar(50)=NULL, @ConfExt as varchar(50)=NULL, @ReutersDacsId as Varchar(50)=NULL, @Bloomberg as Varchar(50)=NULL, @NewFloor as Int=NULL, @OldFloor as Int=NULL )AS UPDATE CustomerInfo SET LName = @LName, FName = @FName, Dept = @Dept, PhoneType = @PhoneType, Ext = @Ext, BridgeExt = @BridgeExt, Workstation = @Workstation, OldPrinters = @OldPrinters, NewPrinters = @NewPrinters, OfficeImg = @OfficeImg, PhoneImg = @PhoneImg, Complete = @Complete Where CustomerId = @CustomerId UPDATE Extras SET CustomerID = @CustomerID, FaxModel=@FaxModel, FaxNumber=@FaxNumber, ConfPhone=@ConfPhone, ConfExt=@ConfExt, ReutersDacsId=@ReutersDacsId, Bloomberg=@Bloomberg where CustomerID = @CustomerID RETURN
View 2 Replies
View Related
Feb 1, 2007
I have a table that contains 5 columns (VarChar); where column(0) is a unique ID. Using the unique ID I would like to get the other 4 columns return to me via a stored procedure. Is it possible to have a sproc that has one input var and 4 output?
View 4 Replies
View Related
Jan 16, 2004
I am trying to call an RPC from SQL2KSP3 to Sybase ASE 12.5. SQL2K is on an IBM Intel Server running Win2KSP4, ASE is on an IBM RS6000 w/ AIX 5.2.
I have the ASE server set up as a linked server in SQL 2000, and doing queries is working fine, i.e. I can
SELECT * FROM asesrvr.testdb.dbo.tablename
and get back all the data in tablename from the testdb database on the ASE server asesrvr.
However, when I try to run an RPC using the same syntax, I get a generic 7212 error (Could not execute procedure 'procname' on remote server 'asesrvr'. I am calling the rpc with
DECLARE @output char(1)
EXEC asesrvr.testdb.dbo.procname @output output
I'm using Sybase's ASE OLEDB provider; on the Linked Server Properties, I have
General Tab
Product name: asesrvr
Data source: devprod
Provider string: <empty>
Location: greyed out
Catalog: <empty>
Security Tab
One local login, sa, which has impersonate checked as the sa passwords on the two servers are the same (I want to eliminate security as an issue while I try to get this working).
Server Options Tab
Collation Compatible: checked
Data Access: checked
RPC: checked
RPC Out: checked
Use Remote Collation: unchecked
Collation Name: <empty>
Connection Timeout: 0
Query Timeout: 0
SQL2K's @@version is
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Any ideas on how I can troubleshoot this further? It does not appear to be limited to this one procedure (this one only has one output parameter and one line of code, so it's about as simple as it can get for testing purposes). Again, queries work fine, so the linked server itself is connected and working, at least for queries.
Thanks very much for your help,
Vince
View 2 Replies
View Related
Feb 25, 2008
Hello,
How can I call UDFs on linked servers in SQL Server 2005?
More details:
I have some UDFs on Instance "VAXPServerA" in Database "DB1", for example "getSomething(number)".
It might look like this (for example):
----
CREATE FUNCTION [dbo].[getSomething] (
@theNumber int
)
RETURNS int AS
BEGIN
DECLARE @result int
SET @result = theNumber * 5
RETURN @result
END
----
I can call this function inside of the DB by typing:
----
SELECT dbo.getSomething(5)
----
I also can call the function from another DB on instance "VAXPServerA" by typing:
----
SELECT DB1.dbo.getSomething(5)
----
I have a second instance called "VAXPServerB", and "VAXPServerA" is a linked server in "VAXPServerB". I can do selects and stuff, it works fine.
But when I want to call the function...
----
SELECT [VAXPServerA].DB1.dbo.getSomething(5)
----
I obtain the next error:
Msg. 207, Level 16, State 1, Line 1
The column name 'VAXPServerA' is not valid.
Any hint?
Thanks in advance
David
View 1 Replies
View Related
Dec 12, 2007
Hi People, i'm having some trouble with a stored proceddure used for an updat from a Formview.
Error message i'm getting is Must declare the scalar variable "@CategoryID".
I can't seem to work out why this is occuring and not allowing my proc to run properly. If anyone could help me that would be great :-)
Here is the whole store procedure.
ALTER PROCEDURE dbo.DeluxeGuideAdmin_Update
@ID int,@CategoryID varchar(10),
@RegionID int,@CompanyName nvarchar(25),
@Email nvarchar(25),@PDFFront nvarchar(50),
@PDFBack nvarchar(50),@ThumbFront nvarchar(50),
@ThumbBack nvarchar(50),@Notes nvarchar(max)
AS
DECLARE @SQL varchar(1000)
SET @SQL = 'UPDATE DeluxeGuide SET CategoryID = @CategoryID, RegionID = @RegionID, CompanyName = @CompanyName, Email = @Email, Notes = @Notes'IF (@PDFFront IS NOT NULL) OR (@PDFFront != '')
SET @SQL = @SQL + ', PDFFront = @PDFFront'IF (@PDFBack IS NOT NULL) OR (@PDFBack != '')
SET @SQL = @SQL + ', PDFBack = @PDFBack'IF (@ThumbFront IS NOT NULL) OR (@ThumbFront != '')
SET @SQL = @SQL + ', ThumbFront = @ThumbFront'IF (@ThumbBack IS NOT NULL) OR (@ThumbBack != '')
SET @SQL = @SQL + ', ThumbBack = @ThumbBack'
SET @SQL = @SQL + ' WHERE (ID = @ID)'
Print '@SQL = ' + @SQLEXEC(@SQL)
RETURN
View 14 Replies
View Related
May 21, 2008
Hi guys,
I am trying to call a function from a remote sql server (linked server) using the following syntax:
select [ServerName].[dbName].dbo.CLRHelloWorld('SomeMessage'). I am getting the following error:
The object name '[ServerName].[dbName].dbo.' contains more than the maximum number of prefixes. The maximum is 2.
I need to call this function remotely and using this syntax; I don't want to use OPENQUERY...
Any hints?
Yousef
View 4 Replies
View Related
Jul 18, 2006
Is there a way to bypass the syntax checking when adding a stored procedure via a script?
I have a script that has a LINKed server reference (see below) .
INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.
ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.
PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.
Thanks,
Terry
View 4 Replies
View Related
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
View Related
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
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
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
May 24, 2004
I have linked Oracle Server as a linked server on my SQL server.
Now i want to execute the stored procedure on oracle server which has one input parameter and one output parameter.
i am using the following sql statements in SQL Server Query analyzer to execute that procedure
declare @sal float
exec [GENEVA].testDB.dbo.EmployeeOutPrm
'1',
@sal output
but getting the following error:
Server: Msg 7212, Level 17, State 1, Line 3
Could not execute procedure 'EmployeeOutPrm' on remote server 'GENEVA'.
[OLE/DB provider returned message: One or more errors occurred during processing of command.]
[OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
COULD ANYBODY HELP ME TO RESOLVE THE SAME
many thanks in advance
bhavya
View 7 Replies
View Related
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
Jul 13, 2004
As part of a nightly process, I must call a stored procedure that uses cursors to update a Sybase linked server from SQL Server. The stored proc was written by someone else and is intended to interface to a finance system to upload new transactions.
The stored proc works fine most of the time but occasionally dies part-way through with the following confusing error message:
[OLE/DB provider returned message: Internally generated command failed to affect a row.]
OLE DB error trace [OLE/DB Provider 'Sybase.ASEOLEDBProvider' IRowsetChange::SetData returned 0x80004005: ].
Msg 7399, Level 16, State 1, Server ********, Procedure ********, Line 162
OLE DB provider 'Sybase.ASEOLEDBProvider' reported an error.
I initially ran this stored proc from a perl script via an ODBC link but in the futile hope of fixing this problem I tried manually executing it via isql.exe instead. Both return exactly the same error. I have also increased the OLE/DB query timeout to 15 minutes with no success in stopping the problem.
There seems to be no connection I can spot between when it succeeds and when it fails. When scheduled to run just after midnight it fails about 30% of the time (at a time when there should be a light load on the servers). When run manually it almost always suceeds (I have only managed to get it to fail through running manually once in dozens of attempts).
I can't seem to find anything relevant about this error via Google or any of my usual reference sources so hoped someone else might have a clue as to what the hell's going on. :confused:
The stored procedure uses a cursor to traverse a SELECT statement then performs several INSERTs, UPDATEs and DELETEs depending upon each row processed and attempts (but usually fails) to perform a ROLLBACK upon an error.
I'd appreciate any pointers in the right direction here. Is it because I'm performing cursor-based operations on a linked server? Is it due to the linked server being a Sybase server? Could it be due to locking problems or timeouts? Is my server in a bad mood and simply trying to taunt me? I'm tearing my hair out trying to find more information on this annoyingly intermittent problem and really appreciate any help or tales of similar errors.
Thanks in advance
View 11 Replies
View Related
Mar 11, 2006
HiWe're trying to use call a stored procedure to update information in aremote Ingres database. We've linked the server, and can readinformation using SELECT * FROM OPENQUERY (..........), but we can'tfind a suitable syntax for executing a procedure.Using SELECT * FROM OPENQUERY and passing the EXEC statement in astring gives a message about not returning any columns - not surprisingas there aren't any, and trying to execute the procedure more directlyusing:-EXECUTE abrs..vipdba.ats_reader_pi0 ........Gives the errorCould not execute procedure 'ats_reader_pi0' on remote server 'abrs'.[OLE/DB provider returned message: Parameter type cannot be determinedfor at least one variant parameter.]Any bright ideas?Chloe
View 1 Replies
View Related
Jul 20, 2005
When I create a stored procedure like the one below and save it,everything seems fine. But when I close Enterprise Manager andre-open it, the stored procedure is gone.Any ideas?SET ANSI_DEFAULTS ONGOCREATE PROCEDURE [dbo].[sp_Test] ASSELECT * FROM OPENQUERY(LINKEDSERVER, 'SELECT V.VENDOR_ID,V.VENDOR_NAME FROM VENDOR V')GOTed
View 2 Replies
View Related
Sep 27, 2007
I have a linked server from Sql Server 2000 to Sybase Adaptive Server 12.5.1.
When i try to call a stored procedure on Sybase from Sqlserver i get the following message:
"could not execute procedure sp_who on remote server 'linked server name'(42000,7212)
command executed from sql server:
exec <linked_server>.<database>..sp_who
i am able to user open openquery for selects and inserts, successfully
Help appreciated
Thanks.
View 4 Replies
View Related
Jun 29, 2004
I would like to capture any errors thrown by linked server in my stored procedure, How do I do this? eg: Select statement in sp to remote server fails if login credentials were changed for some reason on remote server. @@error is not able to capture this error and stored procedure is terminated when this type of error occurs.
View 1 Replies
View Related
Jul 3, 2006
What is the syntax for creating a update trigger and passing the values which were updated to a stored procedure on a linked server?? Specifically need syntax for updated value, as well as the syntax for executing the stored proc on the linked server.
Thank you
View 1 Replies
View Related
Dec 18, 2007
Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
Any and all help appreciated.
Kal
View 3 Replies
View Related
Jul 20, 2005
Hi all,I have a stored procedure that return a resultsete.g. stored proc: get_employee_detailsselect emp_id, emp_name, emp_salary, emp_positionfrom empoloyeeI would like to write another stored procedure that executes the abovestored procedure - returning the same number of records but it willonly show 2 columnse.g. new stored proc: get_employee_pay -- executesget_employee_detailsI only need to know emp_id, emp_salary.How can this be done in sql stored procedure?Thanks,June Moore.
View 2 Replies
View Related
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
Mar 27, 2007
what pro's cons would there be to having a linked server run a local stored proc against another sql server or create that stored proc on that other sql server and call it from there in the c# code.
i would think that calling the stored proc would be more efficient that running a linked server - but please let me know your thoughts. I'm not sure i can have permission to add a stored proc on that server, so possibly the linked server is the only solution - but if i can put a stored proc on that server should i?
thanks.
Jeff
View 4 Replies
View Related
Jan 7, 2004
Hello,
I have created a MS Exchange 2000 link server in my MS SQL Server 2k. I have created a stored procedure (and a view...) using info from that linked server. When I am logged on the server as the Administrator, I can call my stored proc without any problems. When I use another computer (and I am not logged as the admin of the server) and I call the stored procedure, the following error is always raised :
Server: Msg 7302, Level 16, State 1, Procedure test_proc, Line 3
" Impossible de créer une instance du fournisseur OLE DB 'exoledb.DataSource.1'. "
<== I know it is a french error but it can be translated as : "Unable to instancied the OLE DB 'exoledb.DataSource.1' provider"
I would like to know if I can make run my stored proc in the admin account or what should I do to make it work
View 1 Replies
View Related
Feb 12, 2007
Hello there,
I have a scenario where I need a few stored procs to auto-execute on an hourly basis so I thought it would be nicely done in a maintenance plan job list. I have experience with this in sql 2000 but I am struggling with sql 2005.
I have been struggling with my maintenance plan to successfully run the 2 jobs that it has to complete:
1) execute a stored proc that creates/updates a client in the Client table on the local server
(This step works fine without hassles)
2) execute a stored proc that synchronizes this entry with a database on another server. This stored proc works fine outside the maintenance plan, but inside the maintenance plan job it gives me an error :
Executed as user: NT AUTHORITYSYSTEM. Cannot roll back T1. No transaction or savepoint of that name was found. [SQLSTATE 25000] (Error 6401)
I have tried looking on the net and forum to see whether i can solve this but i am stuck. What do I have to keep in mind executing this stored proc as a maintenance plan? What am i missing.
Thanks for any advice
Mike
View 3 Replies
View Related
Aug 2, 2006
I would like to create a script that lists out all of the SQL code for the procedures and triggers I have created in a database.
What I want to do is save the code for each procedure/trigger as a *.SQL file without having to open up each one in Enterprise Manager and save them individually.
Listing the names is easy by looking in sysobjects, but is the SQL code stored in a system table anywhere?
Does anybody have any alternative approach to this problem?
Thanks in advance,
Mark
View 1 Replies
View Related
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
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View 11 Replies
View Related
Jun 19, 2006
Can i call tigger in store procdure and vice versa??
if yes how it ?????????
View 2 Replies
View Related
