Dear all,
I am using SQL 2000 and would like to dynamically assign ODBC data source to transform data task. Do you have a stored procedure to perform read/write from/to ODBC data source? I would like to input data source and table name.
Thank you and Best regards,
Chaivat
Hi,Using Chandu Thota's book Mappoint.net I attempted to set up a findnearby query using his example Implementing Spatial Search Using SQL Server.
The book uses C# I have attempted to convert the code to call the stored procedure as follows:
TryDim units As Int16 = 0
Dim cmd As SqlCommand = New SqlCommand("FindNearby")
'Assign input values to the sql command
cmd.CommandType = CommandType.StoredProcedurecmd.Parameters.AddWithValue("@CenterLat", latitude)
cmd.Parameters.AddWithValue("@CenterLon", longitude)cmd.Parameters.AddWithValue("@SearchDistance", distance)
cmd.Parameters.AddWithValue("@Units", units)Dim con As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("VehicleMarshallConnectionString").ConnectionString)
If con.State = ConnectionState.Closed Then con.Open()
If con.State = ConnectionState.Open Then
cmd.Connection = conDim dreader As SqlDataReader = cmd.ExecuteReader()
If dreader.Read() Then
If Not dreader.IsDBNull(1) ThenDim ordnum As String = dreader(1).ToString
End If
End If
End IfIf con.State = ConnectionState.Open Then con.Close()
Catch ex As Exception
Finally
End Try
The code returns no values but when I execute the stored procedure from the server explorer in visual studio I get the records I would expect in the output window. How would I read the returned values from my code.
Output Window
Running [dbo].[FindNearby] ( @CenterLat = 53.10531, @CenterLon = -2.4769, @SearchDistance = 100, @Units = 0 ).
ID ordnum ProxDistance
----------- ------------------------- -------------------------
1 009999/USP 0
2 109999/USP 14.5971373147639
3 119999/USP 57.7144756947325
No rows affected.
(3 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[FindNearby].
Regards,
JoeBo
Hi
I have decided to approach the problem here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103310
by using a Stored procedure, someone mentioned it's possible
for a SP to read the contents of a text file ?
IS it ?
For example i have a DB table
with fields to,from,subject,body
problem is the body field references a file (full path)
and i need to extract all info into a txt file
the full txt body, so can i perform some sort of inner
operation on the select body .. part of the SP ?
I need to create a flat file as word document, may i know how to write text from stored procedure if a file is already exist then the text will append, how to do it ?
Thank you.
Stored procedure in SQL Server 2K is as follows..
CREATE PROCEDURE TestDataRead
@TestString varchar(20) OUTPUT
AS
SET @TestString = 'Mr.String'
GO
-----------------------------------------------------------------------
SSIS package details.
OLEDB connection manager; Connection works fine.
Execute SQL Task Editor properties are as follows.
ResultSetà None
SQLStatementà exec TestDataRead
SQLSourceTypeirectInput
Parameter Mapping:
VariableName: selected the user variable from the list.
Direction: Output
DataType:varchar
Parameter Name: 0
Parameter Type: 20
When I run I am getting the following error.
SSIS package "TEST SSIS1.dtsx" starting.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec TestDataRead" failed with the following error: "Procedure 'TestDataRead' expects parameter '@TestString', which was not supplied.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. ...........................................
...................................................................
Please help.
Smith
Hi
I wounder if there is a sp in the master db that can give me what usergroup a username is connected to / and or give me info if the username is valid (Windows NT login).
If not: then how do I read what usergroup a username is connected to? sysxlogin table in master db? And can I connect to a sp in master db from another db? Can I create a sp in my db that reads in the master db?
Thanks!!
Best Regards
Staffan
Problem Description:
Stored Procedure hanging when try to execute in READ_ONLY Database. Though the stored procedure is highly complicated,it runs smoothly on the same server if the Database option is set to READ/WRITE. Not all stored procedures are hanging in READ_ONLY database,only if the stored procedure tries to retrieve more records (say 10000 ).
If the SQL Statements present inside the stored procedures are executed,they are getting executed perfectly but hangs if it is executed as stored procedure.
On restarting the SQL Server, stored procedure getting executed only for the first time in READ_ONLY database but executing it again for the second time, it starts hanging. ( I tried executing the stored procedure after clearing the procedure cache but still hangs ).
Environment:
Windows NT 4.0 (Service Pack 5 )
SQL Server 7.0 ( Service Pack 3 )
Multi Processor ( 2 - 500 Mhz )
Let me know if you need more information on it.
In a stored procedure, I am trying to create a table and then read it from within that SP or from another one (nestsed). When I run the below code (simplified version of my real SP), no records are returned even though I know that there is data. The table is created and records are inserted, I just cannot read the records from the SP or one that calls it with an EXEC.
Any ideas? My code is listed below. (When I get this to work, I plan to convert the code to manipulate a temporaty table).
Alter Procedure spWod_rptWoStatusSummary_9
As
BEGIN
CREATE TABLE tblStatusSummary (TckStaffCd_F Char(3), RecType Char(20))
INSERT INTO tblStatusSummary
SELECT TckStaffCd_F, RecType
END
BEGIN
Select * from tblStatusSummary
end
Hi,
How to read from a flat file in a stored procedure. Is there any built in utility available ? Please let me know.
Mahesh.
Hi All,
I need some help from you experts.
I need to develop something that reads from xml files and writes in to sqlserver, also it should read from SQLServer and writes to xml.
I should be able to give this as a job to exectue daily ,etc.
Can we do this using stored procedures in SQLServer.
Pls paste some sample code, if any or direct me to any url with better info.
Thanks in advance
Hey guys,I'm trying to get the column names from a table to which I'm connectedthrough OBDC. I don't want to use linked server. I get my data withOpenrowset.I found the SQLDescribeCol and SQLColumns ODBC functions.Can anybody tell me how to use those functions in a SQL Server storedprocedure?Thanks a lot
View 1 Replies View Related I am coding a DataList control for 2 column data from a stored procedure. The 1st column isprogramName and the 2nd is programLink. I used Edit Template -> Edit DataBonding to add<h ref="http.....>, but can not get it right. Please help. Also, what is the Parameter Sourcefor using Logon User ID as a stored procedure paramter when configuring a SqlDataSource? None, cookie, control,..? TIA,Jeffrey
<asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1" Style="z-index: 16;left: 29px; position: absolute; top: 82px"><ItemTemplate>programName:<asp:Label ID="programNameLabel" runat="server" Text='<%# Eval("programName") %>'></asp:Label><br />programlink:<asp:Label ID="programlinkLabel" runat="server" Text='<%# Eval("programlink") %>'></asp:Label><br /><br /></ItemTemplate></asp:DataList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:iTemplatesConnectionString %>"SelectCommand="MyPrograms" SelectCommandType="StoredProcedure"><SelectParameters><asp:Parameter Name="meid" Type="Int32" DefaultValue="3532" /></SelectParameters></asp:SqlDataSource>
Hi
I was wondering how I could pass on the following parameters from an ini file to a stored procedure within a DTS package. The parameters in the ini file look like:
[DatabaseCleaner]
! -- TableToBeCleaned_N=<table name>,<months to hold on db>,<months to hold on hd>
! -- <N> must be a successive number starting from 1 ...
TableToBeCleaned_1=Transactions,24,24
TableToBeCleaned_2=Payments,24,24
TableToBeCleaned_3=PresenceTickets,24,24
As I do not know how many tables that will be declared in the ini file I have to loop through until the last parameters and pass it over to the SP.
How can I do that? Any idea?
Thanks
mipo
In general as understand if we have a stored procedure that does operations like inserts or updates, it makes perfect sense to use a rollback operation within a transaction.
So, if something goes wrong and the transaction does not complete, all changes will be reverted and an error description will be thrown for example.
Nevertheless, does using a rollback within a try catch statement, make sense in a read only stored procedure, that practically executes some dynamic sql just to select data from some tables?
I have around 100 Stored procedures, all of them read only. Today a colleague suggested adding try-catch blocks with rollback to all of them. But since they are just selecting data, I don't see a clear benefit of doing so, compared to the hassle of changing such a big number of SP's.
Can anyone tell me if this can be done?
I'm using CR8 against SQL Server 7 and am trying to use a stored procedure as my data source.
Basically my boss would like to move all the code that is now client-side(formula fields, parameters, suppressions, etc.) to the server-side.
I connect by Database>ODBC and then choose my sp here.
I get the error: "There are no fields in the file"
All my sp is doing is accepting two parameters: a report type and a user name, and then generating a report based on this data.
I can post the exact sp but it is a pretty long IF THEN ELSE block.
I checked the Seagate site and it said to convert the database driver to the native driver;
I guess this would mean to connect by: Database>More Data Sources>Microsoft SQL Server
But we need to connect by ODBC since we don't want the popup for the Login to Database, Server, etc., to be entered by the client.
Can someone tell me if there is a way around this to connect by ODBC using stored procedures.
Thanks in Advance.
I have a stored procedure written in MS SQL Server2000 which takes argument(OUTPUT) as a cursor, and fills in the cursor with the record from the table.
I have to run this stored procedure from my C application program running in SUN OS2.9 with the help of ODBC calls.
Can anyone guide me through steps as to how to run the store procedure from my C program and receive records with the help of the cursor.
The store procedure is as follows
CREATE PROCEDURE testCursor @xyzCursor cursor varying OUT AS
DECLARE temp CURSOR
LOCAL
FOR SELECT * FROM table
OPEN temp
SET @xyzCursor=temp
RETURN(0)
GO
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 RelatedHi,
I have a ms access application with a button. Clicking this button runs a sql server 2000 stored procedure. The stored procedure updates product information. When I click the button it updates the products fine. When the users (with lesser permissions than me) click the button, no error message is sent but the transactions are rolled back. Does anyone know why or how this could happen? Please let me know before I pull out the last strands of hair from my head.
Thank you,
Anand
Hi,I have a loopback connection using ODBC in the DLL initialization codeofthe SQL Server ESP Module (SQL Server 2000). The loopback connectionworksfine when the DSN is specifed with the "NT Authentication", however thesamefails when specified with the "SQL Server user authentication". I havetriedusing both the SQLConnect and SQLDriverConnect calls, butu none of themworks. Also the same code works fine on SQL Server 2005. Is this aknownproblem with some fix, or am I doing something wrong here??The code is as given below,// ESPODBCLoopback.cpp : Defines the entry point for the DLLapplication.//#include "stdafx.h"#include <sql.h>#include <sqlext.h>#include <srv.h>#define XP_NOERROR 0#define XP_ERROR 1#define SEND_ERROR(szMessage, pServerProc) { srv_sendmsg(pServerProc, SRV_MSG_ERROR, 20001, SRV_INFO, 1, NULL, 0, (DBUSMALLINT) __LINE__, szMessage, SRV_NULLTERM); srv_senddone(pServerProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0); }// typedef const char* (_MakeODBCConnection)(void);static const char* _szMessage = "ODBC Working out....";void_MakeODBCConnection(void){char szConnOut[1024];SQLSMALLINT nOut = 0;const char* szDSNName = "TestOdbc";const char* szUsername = "test";const char* szPassword = "test";SQLHANDLE hEnvironment = NULL;SQLHANDLE hDBConnection = NULL;if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,&hEnvironment)) {_szMessage = "Failed to create the environment handle";return;}SQLSetEnvAttr(hEnvironment, SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,SQL_IS_INTEGER);if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_DBC, hEnvironment,&hDBConnection)) {SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);_szMessage = "Failed to create the database connection";return;}/*----------------- This is where it fails -------------------*//* Tried both the with/Without database name */if (SQL_ERROR == SQLDriverConnect(hDBConnection, GetWindow(,(SQLCHAR*)"{DSN=TestOdbc;UID=test;PWD=test;DATABASE=test;}", SQL_NTS,(SQLCHAR*)szConnOut, sizeof(szConnOut), &nOut, SQL_DRIVER_COMPLETE)){SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);_szMessage = "Failed to connect to the database";return;}/*if (SQL_ERROR == SQLConnect(hDBConnection, (SQLCHAR*)szDSNName,SQL_NTS,(SQLCHAR*)szUsername, SQL_NTS, (SQLCHAR*)szPassword, SQL_NTS)) {SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);_szMessage = "Failed to connect to the database";return;}*/SQLFreeConnect(hDBConnection);SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);_szMessage = "ODBC Connection cycle completed successfully";}BOOL APIENTRY DllMain( HANDLE hModule,DWORD ul_reason_for_call,LPVOID lpReserved){switch (ul_reason_for_call){case DLL_PROCESS_ATTACH:_MakeODBCConnection();break;case DLL_THREAD_ATTACH:break;case DLL_THREAD_DETACH:break;case DLL_PROCESS_DETACH:break;}return TRUE;}static void_CheckODBCConnection(void){//_MakeODBCConnection pFunction = NULL;//_szMessage = pFunction();}extern "C" __declspec(dllexport)RETCODE xp_test_odbc(SRV_PROC *pServerProc){//_szMessage = _MakeODBCConnection();if (FAIL == srv_paramsetoutput(pServerProc, 1, (BYTE*)_szMessage,(ULONG)strlen(_szMessage),FALSE)) {return XP_ERROR;}return XP_NOERROR;}Thanks,Anil KumarArizcon Corporation ( http://www.arizcon.com )
View 6 Replies View RelatedI have created a stored procedure on the iSeries that creates a cursor and opens it. I am trying to write my report to use the stored procedure. I cannot get the data source to work. How do I create my data source so that it uses the stored procedure? My SP has three parameters I am trying to pass from the report. The parms are created in the layout.
Thank you
I have a Login that I setup in SQL Server 2005. The only database roles given to the login are db_datareader and public. On SQL 2000 these permissions are sufficient to allow me to setup a read only ODBC connection using this login and creating an import table in Access. When using the login in SQL 2005 and creating an import table, I am able to edit the underlying data, something I am not able to do with a SQL 2000 ODBC connection. Is there something in SQL 2005 that I am unaware of that would allow this login to modify (update) the table?
View 6 Replies View RelatedHi,
I have an Access database Front End which use SQL server as a Back End. The two are connected using ODBC. Occasionally, some of the linked tables in Access go read only. I can't add or edit records. The only way I know to get round the problem is to delete the link and re-create it. Refreshing the link does not work. Can anybody suggest why this would happen, and the best way to fix it when it does occur?
Edited 12:10 06/14/07
Some extra info. At the same time it goes read only, the link loses it's primary key.
Thanks
Colin
Hi,I have two, similar SQL Server 7.0 databases (in the same SQL ServerGroup) that I use as the backend for two Access 2000 front endapplications - one is the live version, the other is the developmentversion.The live version works fine. But the other version is read-only, i.e.no changes can be made to the data in the tables, over the ODBCconnection.Both systems have similar ODBC connection properties, the permissionsettings on the SQL Server databases are the same - users, tableaccess, database access, etc. The connections use Windowsauthenication and the users are set up in SQL Server security loginswith public and db_owner selected.Has anyone seen or heard of this problem and does anyone have anyideas on how to fix it?I appreciate any comments or suggestions you may have.Thanks,Chris
View 3 Replies View RelatedI am trying to read an Informix 9.3 blob field into a TSQL (SQL Server Stored Procedure) and the data type conversion is not supported thru the MS ODBC Driver installed with Win2000/SQL2000. Does anyone know of another method to retrieve this data, or of a vendor that provides an ODBC driver that supports the Informix blob data type. Thanks!
View 1 Replies View RelatedHi there,
I have successfully installed SQL Server Express.
I have copied NorthWind to SQL Server.
I have created an ODBC to the SQL NorthWind.
But my problem is, I open the data but I cannot edit or insert records
to the Customer table.
I have gone into SQL Management Studio and modified Permissions
on NorthWind and the Customer table with Alter, Insert, Update.
But no luck.
Has anyone experienced this?
Email me at justintoronto@hotmail.com if you have a solution.
I will try to check back here also.
Thanks,
Justin
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?
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.
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies View RelatedI have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
Hi,
We currently use an access database to manipulate data in tables on a SQL server. The end user copies a list of ProjNo from a spreadsheet into an Access table. The Access table is used as the criteria for 5 Access queries. It is common that one of the queries does not get run.
I figured I would create a Storied Procedure on the SQL server, call the storied procedure from MS Access using the MS Access table as the criteria to run the Storied Procedure. I do not know how to provide the Storied Procedure the ProjNo in the MS Access table. The number of ProjNo records in that table can vary.
I've found info on how to add one record at a time but I need a more of a batch process approach.
Can you point me in the right direction?
Thanks,
Mary