Function In Access Vs. Stored Procedures In SQL Server

Mar 7, 2001

I am used to working in Access and just recently became somewhate proficient using custom functions in modules.
I am trying to figure out what the equivalent of functions is in SQL Server. I mean, does a Stored Procedure in SQL Server replace a module in Access? Can you declare different functions in SQL Server like you can in Access?
Thanks for your help.
Mike

View 1 Replies


ADVERTISEMENT

Running SQL Server Stored Procedures Through Access

Dec 13, 2004

Hi,
Can someone help me with this problem.
I have a stored procedure in SQL Server that updates a particular table. When I run it in SQL server Query Analyser, it works fine. But I want to invoke this stored procedure when I click a button on an MS Access Form. The code I'm using is:

Dim cn, cmd
Set cn = CreateObject("ADODB.Connection")
cn.Open "SQL" //Data Source Name
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "LoadApplicants" //Stored Procedure Name
cmd.CommandType = adCmdStoredProc
cmd.Execute

for some reason only a few records are updated everytime I click on the button. Is there any reason why this is happening?

View 4 Replies View Related

Control Access To SQL Server 2005 Database Only Through Stored Procedures Issue

Apr 14, 2008


Hello,

I have a database (SQL 2005) with two schemas (dbo and s1) and with tables defined in dbo and tables defined in s1. The stored procedures are also defined in both schemas, some of them in dbo some of them in s1. Some of the stored procedures query tables from dbo and s1 at the same time.
I want to have a new db role with access to the database only through sps and no other access read/write to the tables. I created a new db role and granted execute permission to it and assigned a user to it.
When I execute stored procedure defined in dbo with query against dbo tables, it works as expected.
However, if I run stored procedure defined in s1 with query to table in dbo, I receive error about missing select permission for the table in dbo. I am not sure why, but I can assume there is an issue with the ownership chain.
I can grand read/write permission for the tables, but this will break our original requirement for limited access to the db only through sp.
The other option is to have another role r2, with read/write, privilege and to use EXECUTE AS r2 in the sp.

I would like to ask first why the error for missing select permission happens and is there another way to have role restricted to only execute permission for all stored procedures.

Thanks,
IT

View 5 Replies View Related

ODBC Link From Access To SQL Server 2005 Stored Function

Sep 20, 2006

If I define a table-valued function in a SQL Server 2005 database, can I link to it from Access 2003 using ODBC?

I've defined the function successfully, and I can link from Access to tables in the database (so my ODBC link is basically functioning), but I can't see the table-valued function in the Linked Table Manager in Access.

I can define a pass-through query to grab the table, but with a pass-through query I have to provide the ODBC password every time.

What am I missing?

Suggestions?

View 1 Replies View Related

Diff. B/w Stored Procedures And Function??

Nov 28, 2006

Diff. b/w Stored Procedures and Function??
 
When any of them is appropriate to use?

View 2 Replies View Related

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function.

Jun 7, 2006

Hi, l've created an function [GSM].[KPIAging], and test it in studio by substitule declare value, i.e.
DECLARE @sCellName VARCHAR(8)
DECLARE @dDate DATETIME
SET @sCellName = "CELL1M_1"
SET @dDate = CAST('06/Jun/2006' AS DATETIME)

EXEC GSM.KPIAging @sCellName, 'CSSR', @dDate

It work fine and return the desired result, but when l used this function in SQL,
SELECT DATEKEY, CELLREGIONKEY, CELL_NAME, CELL_ID, CSSR, GSM.KPIAging(Cell_Name, 'CSSR', @dDate)
FROM GSM.GSMCellDaily_vw
WHERE CSSR BETWEEN 0 AND 85
AND FULLDATE = @dDate
AND CM_SERV > 30
AND (TCH_TRAFFIC > 2 AND TCH_SEIZURES_ATTS > 30)

I got the following error, i.e.
Msg 557, Level 16, State 2, Line 19Only functions and extended stored procedures can be executed from within a function.
Does anyone have any idea on this, and what's the workaround for this?

Thanks you!

View 15 Replies View Related

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function.

May 7, 2008

Hi mister, I have this script sql but I get this error:



Mens. 557, Nivel 16, Estado 2, LĂ­nea 1

Only functions and extended stored procedures can be executed from within a function.

DROP FUNCTION ObtenerTablaPorNombre2

GO

CREATE FUNCTION ObtenerTablaPorNombre2 (@ParamNombreTabla VARCHAR(100))

RETURNS @T Table ( Descripcion VARCHAR(20) NOT NULL, CIF VARCHAR(8) NULL )

AS

BEGIN

DECLARE @cmd nvarchar(max)

DECLARE @params nvarchar(max)

DECLARE @NombreTabla VARCHAR(MAX)

DECLARE @Descripcion VARCHAR(MAX)

DECLARE @CIF VARCHAR(MAX)

SELECT @NombreTabla = [CD_NOMBRE_TABLA], @Descripcion = [DS_CAMPO_DESCRIPCION] , @CIF = [DS_CAMPO_CIF]

FROM [TABLA_MAESTRA] WHERE [CD_NOMBRE_TABLA] = @ParamNombreTabla

SET @cmd = 'SELECT ' + @Descripcion + ',' + @CIF + ' FROM ' + @NombreTabla

--EXEC (@cmd)

SET @cmd = 'SELECT @pDescripcion, @pCIF FROM @pNombreTabla'

SET @params = N'@pDescripcion varchar(100), @pCIF varchar(100), @pNombreTabla varchar(100) '

EXEC sp_executesql @cmd, @params, @pDescripcion = @Descripcion, @pCIF = @CIF, @pNombreTabla = @NombreTabla

RETURN

END

GO

SELECT * FROM [dbo].ObtenerTablaPorNombre2 ('tabla2')
-- Only functions and extended stored procedures can be executed from within a function

View 2 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

Transact SQL :: Only Functions And Some Extended Stored Procedures Can Be Executed From Within A Function

Sep 11, 2013

I have this function called fn_GetTimedKey() when I run it in my SQL2005 environment it works fine.

When I run it in my SQL2008R2 environment I get the error:

Only functions and some extended stored procedures can be executed from within a function.

The SQL2008R2 server is new. What can I look for?

Here's the code for the function:

BEGIN
DECLARE @v_Key CHAR(12)
EXEC master..xp_usg_gettimedkey_VAL @v_Key OUTPUT
RETURN @v_Key
END

View 4 Replies View Related

Data Access :: Transfer Data From One To Another Table On Another Server Using Stored Procedures

Jun 9, 2013

I have two database(MYDB1 , MYDB2) on two different server's(SERVER1 , SERVER2) . I want to create an store procedure in MYDB1 on SERVER1 and get some data from a table of MYDB2 on SERVER2. How can i do this?

View 5 Replies View Related

STORED PROCEDURES FROM ACCESS 97

Dec 8, 1999

COULD ANYONE PLEASE TELL ME HOW I CALL A STORED PROCEDURE FROM AN ACCESS 97 DATABASE WHICH ARE HELD ON AN SQL 7 DATABASE.

I HAVE USED THE USAULL CALL STATEMENT, BUT THIS DOES NOT WORK.

SO ANY HELP WOULD BE GRATEFULLY APPRECIATED.

GARY J

View 3 Replies View Related

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function. Sp_executesql Is A Extended Stored Prod

May 15, 2008

i have created the folowing function but keep geting an error.

Only functions and extended stored procedures can be executed from within a function.

Why am i getting this error!

Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)

set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'

execute sp_executesql @sqlval
return @ret
end

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

Call Stored Procedures Or Job From Access

Nov 20, 2003

How can I get a group of stored procedures or a job to run on the sql server from Access?

Thanks

View 1 Replies View Related

Access To Extended Stored Procedures

Jul 18, 2007

Hi,

How can I remove access to extended (xp_) stored procedures?
Is there any revoke on <stored_procedure_name> ... command? How can I generate a script of all users who have execute privileges for these procedures? Also, is there any way of restricting (instead of removing) access to those procedures?

Any help will be greatly appreciated!!!
Thanks,
Alla

View 2 Replies View Related

Restricting Access To Stored Procedures

Oct 5, 2005

I'm going through the SQLSecurity Checklist I found at sqlsecurity.com. One of the points it says to "Restrict to sysadmins-only access to stored procedures and extended stored procedures that you believe could pose a threat." It also lists a bunch of stored procs and extended stored procs that you should consider restricting to sysadmins only. I was wondering if someone could give me some pointers on how to do this? I would like to write a script that I could run on every sql server 2000 install that would do this. How could I ensure that every user does not have access except the sysadmins?

Thanks,
Chris

View 11 Replies View Related

Which Frontends Access My Stored Procedures

Dec 17, 2007

I am working on a corporate project. I have many stored procedures(>100) and wanted to know which applications( or Frontends) in the network use or consume my stored procedures. How can i do that in SQL 2005??

NBU

View 5 Replies View Related

Can't Access Properties On Stored Procedures

Jul 3, 2007

Hi. I have set up an instance of SQL 2005 on my local computer. I use MS SQL Server Management Studio to login into my 2005 instance via Windows login. When I expand "Stored Procedures" under "Programmability" of the Database I want and go to a stored procedure and right click, I don't see the "properities" selection.

I know it's there because I can get to it on other instances but I can't get to it on my local instance. I added my windows user to my Logins and then to my database users but it didn't help.

Can anyone please tell me what I need to do so I can access the properties of the stored procedures?



Thanks

View 3 Replies View Related

Stored Procedures - Restricting Access

Dec 12, 2006

Hello everyone,

I have a design problem which I am hoping somebody can shed some light on.

I am running SQL Server 2000 using SQL authenticaiton (due to be changed to Windows authentication in the next 6 months). I have a table in my database which we shall call monthly. I want to restrict the ability to insert to the monthly table to 2 stored procedures (proc_abc & proc_xy) which I have written which do various other validation checks before it inserts the data into monthly.

Users with the Foo function assigned are able to execute proc_abc & proc_xy

I have written a VB application which can be used by users who are not familiar with SQL to be able to execute these stored procedures. (Must have Foo function in order to login to this application).

I want to restrict the ability to execute the stored procedures to users using the VB application only, and thus not be able to execute the stored procedure using Query Analyzer or such like for any Foo user.

Is there anyway I can do this?

One suggestion put to me is two split the functions. Have one function lets call it Top which can access the VB application and then have another function called Bottom which is able to execute the stored procedures. Only the VB app would have access to the Bottom credentials. But is this secure? Would I just hard code the credentials for the Bottom function user within the VB app? This doesn's seem a secure way of doing things to me.

Thanks for the help!

View 6 Replies View Related

MS Access And Stored Procedures Or Views

Mar 13, 2008

Can Microsoft Access access a stored procedure on the SQL Server? If not, is there any way to assign a parameter to a view? I can link to a view in Access, but I cannot link to a procedure. I need to SELECT * from aTable WHERE ID = [@PassidInID]. Any way to do this through Access? The reason for this, is because I am trying to run an existing query (which is very busy - using other queries with queries in them etc.), and I keep getting an ODBC error. I am thinking that if I move the queries to the SQL Server, it may get rid of this error?
It's a 2-tier app I have. Tables on the server, forms, queries, and everything else in Access.
Thanks in advance - I hope.

View 9 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

Allowing Access To Database Only Through Stored Procedures

Aug 31, 2006

I have read that it is possible to configure sql server express  so that the database can only be accessed through stored procedures. Can anyone tell me how to do this. Many thanks.martin

View 2 Replies View Related

Stored Procedures To Limit SELECT Access

Sep 17, 2007

Hi there,
I have a database table with a field to specify the username of records that particular user is allowed to access (read only).  I want to be certain the user does not have access to any other records.  So I'm using a stored procedure to query the table with "WHERE dbuser = USER".
My problem is that the stored procedure is returning an error that the table I'm querying cannot be found, and I suspect it is because the user does not have proper permissions to see those tables, even through the stored procedure.
So my question is, how do I grant the user sufficient access for this stored procedure to work without outright granting them select permission on the tables?
Much appreciated! 
 

View 3 Replies View Related

Parameter Stored Procedures And Access Listboxes...Help!!!

Apr 22, 2001

I have upsized a complex Access 97 application to SQL Server 7, and want to substantially preserve the user interface design by using an Access 2000 project (*.adp) as the front end.

Most of the original Access queries accepted their parameters from screen form objects, and this works well using SQL 7 stored procedures as form recordsources.

However, Access 2000 does not seem to provide a way to tell a listbox or combo box how to pass a parameter value from a screen form object to its SQL 7 stored procedure rowsource.

These parametized listboxes are critical to the interface design.
Please help!!!

View 1 Replies View Related

Converting Stored Procedures Back To MS Access Queries

Mar 15, 2007

I know its a weird request, but we have created an application with sql server but our client wants a version which can be put onto disk.

We decided to create the stored procedures into queries, would this be the best idea and if so does anyone know if there is a freeware software that can do this or will I have to painstakingly re-create the queries?

Any advice would be greatly appreciated.

Many thanks
Smilla

View 1 Replies View Related

Passing Parameters To Action Stored Procedures Using ADO, In Access Project

Jul 20, 2005

There is a form in an Access Project (.adp, Access front end with SQLServer) for entering data into a table for temporary storing. Then, byclicking a botton, several action stored procedures (update, append) shouldbe activated in order to transfer data to other tables.I tried to avoid any coding in VB, as I am not a professional, but I havefound a statement in an article, that, unlike select queries, form's InputProperty can't be used for action queries. Therefore, parameters can bepassed to action stored procedure only by using ADO through VB.As I'm not very familiar with VB, I had to search in literature.So, this is a solution based on creating Parameter object in ADO and thenappending values to Parameter collection.Please, consider the following procedure I created for passing parametersfrom form's control objects (Text boxes) to a stored procedureDTKB_MB_UPDATE:Private Sub Command73_Click()Dim cmd As ADODB.CommandSet cmd = New ADODB.Commandcmd.ActiveConnection = CurrentProject.Connectioncmd.CommandText = "DTKB_MB_UPDATE"cmd.CommandType = adCmdStoredProcDim par As ADODB.ParameterSet par = cmd.CreateParameter("@DATE", adDBTimeStamp, adParamInput)cmd.Parameters.Append parSet par = cmd.CreateParameter("@BATCH_NUMBER", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@STATUS", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@DEPARTMENT", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@PRODUCTION", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@SAMPLING_TYPE", adVarWChar, adParamInput,50)cmd.Parameters.Append parcmd.Parameters("@DATE") = Me.DATEcmd.Parameters("@BATCH_NUMBER") = Me.BATCH_NUMBERcmd.Parameters("@STATUS") = Me.STATUScmd.Parameters("@DEPARTMENT") = Me.DEPARTMENTcmd.Parameters("@PRODUCTION") = Me.PRODUCTIONcmd.Parameters("@SAMPLING_TYPE") = Me.SAMPLING_TYPEcmd.ExecuteSet cmd = NothingEnd SubUnfortunately, when clicking on the botton, the following error apears:"Run-time error'-2147217913 (80040e07)':Syntax error converting datetimefrom character string."Obviously, there is some problem regarding parameter @DATE. In SQL Server itis datetime, on the form's onbound text box it is short date (dd.mm.yyyy)data type. I have found in literature that in ADO it should beadDBTimeStamp.So, what is the problem ?Greetings,Zlatko

View 2 Replies View Related

Access 2007 Runtime: Stored Procedures As Record Source

Feb 15, 2008


Hello

In our company, after switching to Office 2007, Access 2007 runtime is installed on our general user machines.
Everything is just fine and beautiful, except that there is one issue with runtime:

Several forms in my Access Project use stored procedures in SQL Server 2000 as their record source. When i try to open the forms in Access 2007 Runtime on user machines, i receive an error that "The record source dbo.Myprocedure speified on this form or report does not exist ".
I use dbo.ProcName format and the forms open correctly in my full version Access.
What could be the problem?

Thanks!

Elman

View 10 Replies View Related

SQL Security :: How To Disable User Access To The Critical And System Stored Procedures

Sep 22, 2015

disable the following system stored procedure (xp_grantlogin and xp_revokelogin)?

View 5 Replies View Related

Database Access Via COM Objects - V- Database Access Via Stored Procedures

Aug 17, 2000

We have been asked to look into using stored procedures with SQL Server 7.0 as a way to speed up a clients site. 99% of all the articles I have read along with all the books all say Stored Procedure should be used whenever possible as opposed to putting the SQL in your ASP script. However one of my colleagues has been speaking to Microsoft and they said that that they were surprised that our client wanted to use Stored Procedures as this was the old method of database access and that now he should really consider using COM objects for data access as itis much faster. Has anyone got any views on this or know of any good aticles regarding this matter ?

View 1 Replies View Related

Learn To Access Stored Procedures With ADO.NET 2.0-VB 2005:How To Work With Output Parameters &&amp; Report Their Values In VB Forms?

Feb 11, 2008

Hi all,

In my SQL Server Management Studio Express (SSMSE), pubs Database has a Stored Procedure "byroyalty":

ALTER PROCEDURE byroyalty @percentage int

AS

select au_id from titleauthor

where titleauthor.royaltyper = @percentage

And Table "titleauthor" is:
au_id title_id au_ord royaltyper




172-32-1176
PS3333
1
100

213-46-8915
BU1032
2
40

213-46-8915
BU2075
1
100

238-95-7766
PC1035
1
100

267-41-2394
BU1111
2
40

267-41-2394
TC7777
2
30

274-80-9391
BU7832
1
100

409-56-7008
BU1032
1
60

427-17-2319
PC8888
1
50

472-27-2349
TC7777
3
30

486-29-1786
PC9999
1
100

486-29-1786
PS7777
1
100

648-92-1872
TC4203
1
100

672-71-3249
TC7777
1
40

712-45-1867
MC2222
1
100

722-51-5454
MC3021
1
75

724-80-9391
BU1111
1
60

724-80-9391
PS1372
2
25

756-30-7391
PS1372
1
75

807-91-6654
TC3218
1
100

846-92-7186
PC8888
2
50

899-46-2035
MC3021
2
25

899-46-2035
PS2091
2
50

998-72-3567
PS2091
1
50

998-72-3567
PS2106
1
100

NULL
NULL
NULL
NULL
////////////////////////////////////////////////////////////////////////////////////////////
I try to do an ADO.NET 2.0-VB 2005 programming in my VB 2005 Express to get @percentage printed out in the VB Form1. I read some articles in the websites and MSDN about this task and I am very confused about "How to Work with Output Parameters & Report their Values in VB Forms": (1) Do I need the Form.vb [Design] and specify its properties of the object and classes I want to printout? (2) After the SqlConnectionString and the connection.Open(), how can I bring the value of @percentage to the Form.vb? (3) The following is my imcomplete, crude draft code:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes

Public Class Form1

Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI;"

Dim connection As SqlConnection = New

SqlConnection(connectionString)

Try

connection.Open()

Dim command As SqlCommand = New SqlCommand("byroyalty", connection)

command.CommandType = CommandType.StoredProcedure
...................................................................
..................................................................
etc.
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From the above-mentioned (1), (2) and (3), you can see how much I am lost/confused in attempting to do this task. Please help and give me some guidances and good key instructions for getting the output parameter printed out in the FORM.vb in my VB 2005 Express project.

Thanks in advance,
Scott Chang


View 11 Replies View Related

Can Anyone Tell Me How To Access Data From Stored Procedures Using Data Adapters?

May 21, 2008

 Can anyone tell me how to access data from Stored Procedures using data adapters? My task is to select a row which is valid with data particular value. Suppose i had to get all values of particular user after validating username and password. Can anyone give me some hint regarding store procedure and retriving data from stored procedure using data adapters ? How can i bind data to dropdownbox of one field in the table using datasets and data adapters? How can i insert data in database using data adapters?Can any one solve this? 

View 1 Replies View Related

Access Val Function In SQL Server

Oct 5, 2005

What is the counterpart of this function when using SQL Server 2000? Im
getting this error "System.Data.SqlClient.SqlException: 'Val' is not a
recognized function name"

What do I have to change in my queryString?

SELECT ASP_MainForm.UserID, Val([FormNo]) AS Expr1,
ASP_MainForm.DateCreated, ASP_MainForm.DateNeeded,
ASP_MainForm.FormStatus, ASP_MainForm.Print, ASP_MainForm.PRNo,
ASP_MainForm.ForUse, [FirstName]+' '+[LastName] AS CompName FROM
ASP_MainForm INNER JOIN CEN_USERS ON ASP_MainForm.UserID =
CEN_USERS.UserBadgeNo WHERE
(((ASP_MainForm.FormStatus)<>'Approved') AND
((ASP_MainForm.ForUse)<>'Test')) ORDER BY Expr1 DESC

Pls help thanks

View 2 Replies View Related

MS Access' IIF Function In A MS SQL Server View

Oct 16, 2006

I am translating some of my Access queries to SQL views. In one of those, I had a very convenient function called "IIF" (e.g. IIf(IsNull([Remark]),"NULL","NOT NULL").

How is this function called in the MS SQL Server 2000? Apparently I cannot use either "IIF" nor "CASE" in the query/view.....

View 10 Replies View Related







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