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
Is there an easy way to convert Access Queries to SQL Views without doing it manually?I have used the Databse tool to migrate tables, but cannot see to find something similiar for queries.
View 3 Replies View RelatedI writte a stored procedure for username , password ....
How can i call that stored procedure to verify the username & password
then if both match in the database redirected to next page..? in asp with c#.net back end programming
Hi,
I have a quick question about pushing things onto the database to do. It is supposed to be more efficient, and it is more secure. The downside as far as I can tell is that one can end up with incredibly expensive and complex database deployment using costly servers and software.
Is there an approach that minimizes database query time by moving much of the processing to the (less efficient) web server calling the database, and then only using simply queries. One would then process the data in code, stripping it down to exactly what is needed, rather than doing complex things on the db itself.
An example of this might be that you want to parse a field on a particular char. and return only the first part of the string, up until this char occurs. This can be done on the db server, but...
I understand that one might return extra information, but it seems there is a large potential payoff in less complexity and expense.
Anyway, if you know of such an approach and the name of it, I would appreciate hearing it,
Thanks
Hello, I'm trying to create a Stored Procedure who receives the table name as a parameter, then uses a cursor to obtain every column name and then builds a string like SELECT col1, col2, ... from TABLE
In fact that would be the same as SELECT * FROM table; but I can't do this, because I'll be using this stored procedure to loop through many tables that has different quantity of columns with a DTS, and if a specify the *, then the DTS wouldn't let me do the select with tables with different quantity of fields.
Could you help me please, because my code isn't working:
CREATE PROCEDURE dbo.stp_Test
(
@tablename AS VARCHAR(50)
)
AS
DECLARE @columnname varchar(50)
DECLARE @strsql Nvarchar(500)
DECLARE @query varchar(4000)
SET NOCOUNT ON
DECLARE c1 CURSOR FOR
SELECT column_name FROM information_schema.columns
WHERE table_name = @tablename
OPEN c1
FETCH NEXT FROM c1 INTO @columnname
WHILE @@fetch_status = 0
BEGIN
IF (@strsql is null)
BEGIN
SET @strsql=@columnname
END
ELSE
BEGIN
SET @strsql = @strsql + ',' + @columnname
END
FETCH NEXT FROM c1 INTO @columnname
END
CLOSE c1
DEALLOCATE c1
SELECT @query = 'SELECT ' + @strsql + ' FROM ' + @tablename
EXEC @query
SET NOCOUNT OFF
GO
Hi,
We have our DWH built in SQL Server 2005. We are doing reporting against the DWH and most of query logic is complex. For this purpose we have proposed to write SPs to encapsulate the logic and which in turn can be called from the Report Data Tab. But our IT Architect suggest us to have inline queries instead of SPs as that will reduce the overhead of going to the DBAs everytime the logic changes. As I am new to DWH+Reporting, I was really pondering over which way to go. Can anyone suggest a way out on this.
Thanks,
S Suresh
Help!
I have converted my Access Jet database to a SQL Server database. In Access, I had many functions that I created to use in my queries. SQL Server does not allow for functions to be created. So, I had to create procedures in SQL Server to perform what my functions did.
I am trying to use the procedures I created on calculated fields within my SQL statement in my view. It returns an error stating that the procedure is not recognized.
How can I create functions to be used in my query?
Thanks.
Hi,I'm adapting access queries to sql server and I have difficulties withthe following pattern :query1 : SELECT * FROM Query2 WHERE A=@param1query 2: SELECT * FROM Table2 WHERE B=@param2The queries are nested, and they both use parameters.In MS Acccess the management of nested queries with parameters is soeasy (implicit declaration of parameters, transmission of parametersfrom main query to nested query)that I don't know what the syntax should be for stored procedures.The corresponding stored procedure would be something likeCREATE TABLE #TempTable (...table definition...)INSERT INTO #TempTable ExecProc spQuery2 @Param2SELECT * FROM #TempTable WHERE A=@Param1And spQuery2 would be : SELECT * FROM Table2 WHERE B=@ParamI was wondering if this syntax would work and if I can skip theexplicit declaration of #TempTable definition.Thanks for your suggestions.
View 5 Replies View RelatedIm reviewing my stored procedures for a new application and got to thinking about protecting against sql injection. I think im pretty safe since im using stored procedures and none of them use any 'exec' commands within them, but im not sure.
I was reading this article, and again all the examples that list a stored procedure, have an 'exec' command somewhere that is the culprit. So, in my case lets say I was doing something like this:
Im generally using regularexpression validation controls on the client side of the application and limiting the max length of the input there as well.
Am I safe, or do I need further input checking within the procedure ?
Code Snippet
CREATE PROCEDURE [dbo].[get_Uploads]
@app varchar(50)
--Init variables
SET @error_number = 0
BEGIN TRY
SELECT [Logid],[Filename],[Label],[UploadDate],[App]
FROM UploadLog au
WHERE [App]=@app
END TRY
BEGIN CATCH
SET @error_number = -2
END CATCH
I'm using sql 2005. I tried using Profiler with a filter on TempDB but it doesn't seem to record the activity.
Thanks.
Hello everyone,
I don't know what category would be appropriate for this question but security seems to be close enough.
I have this case scenario: I am running an automated application that extracts data from a web site and stores the data into a table on SQL server 2005. This information is not confidential in the extreme of social insurance #'s, bank account #s, but should not be seen by a typical employee (it has no use for them). After the data has been stored, it retrieves the data from the same table, processes it, and updates the same table. This application runs every hour infinitely.
Should all the insert, update, and select queries be stored under a stored procedure? I am not concern with performance. My concern would fall under design and security.
Is it worth to hide the details of inserting/updating/selecting behind a stored procedure? Or should I just allow the program to send select/update/insert SQL queries?
No employee (other then the developer and the DB admin) or customer ever access this table (They do not have permission from SQL). The username and passwords were created with security in mind.
Any thoughts or ideas?
Thanks for your time, Adrian
I am switching my database from MS access to SQL server, and i want the following query to br converted to SQL stored procedure
CREATE PROCEDURE FORUM_MESSAGE AS
SELECT *
FROM FORUM_MESSAGES
WHERE ID=MessageID;
here "MessageID" is a run time generated parameter, and is not a field in the database.
thanx
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
can anyone tell me how to call SQL stored procedures in MS Access97...
appreciate any help
How can I get a group of stored procedures or a job to run on the sql server from Access?
Thanks
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
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
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
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
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!
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.
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 RelatedHi 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!
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!!!
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
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?
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
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
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
disable the following system stored procedure (xp_grantlogin and xp_revokelogin)?
View 5 Replies View RelatedWe 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 RelatedI am trying use the decimal data type for a field in SQL Server. When I input the values below, they round off.
73.827 Rounds to 74
1925.1 Rounds to 1925
119.79 Rounds to 120
What am I missing? Access never gave me this issue. Do you see any reason this would happen? I am entering the values into the table directly!
Hi all,I have a problem converting datetime to integer (and than back todatetime).Depending whether the time is AM or PM, same date is converted to twodifferent integer representations, which holds as true on reversalback to datetime.AM Example:declare @DI integer; declare @DD datetimeset @DI = cast(cast('3/12/2003 11:34:02 AM' as datetime) as integer)set @DD = cast (@DI as datetime)print @DI; print @DDResult:37690Mar 12 2003 12:00AMPM Example:declare @DI integer; declare @DD datetimeset @DI = cast(cast('3/12/2003 11:34:02 PM' as datetime) as integer)set @DD = cast (@DI as datetime)print @DI; print @DDResult:37691Mar 13 2003 12:00AMNow, this is not a big problem if I knew that this is how it issupposed to work. Is this how SQL Server is supposed to work?
View 3 Replies View Related