Code View Access
May 7, 2007Hello all, I need to prevent all users (other than sysadmin/dbo) privs to access the database code (SP/Views/UDF). What would be the best way to achieve that? TIA
View 5 Replies
ADVERTISEMENT
Retrieve Code Of A View?
Dec 11, 2014I can retrieve the sql code of a view as follows:
exec sp_helptext @viewName
The result will be in several lines which shows the lines of the sqlcode...
Is it possible to return the sql but not the line numbers?
How To Get Source Code Of A View ?
Apr 11, 2008
Hi,
I am trying to get source code of a view (SELECT part) into an TSQL variable, but I have no idea, where to find this source code and how to proceede the assignment ? Any idea ? thanks
Need Help Calculating Values In View Code
Jan 26, 2008I'm in the process of building various queries and I'm having issue calculation values in the following code. I need to calculate the maximum and minimum salary for exempt employees and include a column that calculates the difference in these numbers. I have no issue creating views and outputing data. When I try to calculate the values I receive errors:
My code is:
GO
IF OBJECT_ID ('Salaries') IS NOT NULL
DROP VIEW Salaries ;
GO
CREATE VIEW Salaries
AS
select max(Salary) from EMPLOYEE join JOB_TITLE on EMPLOYEE.JobID = JOB_TITLE.JobID where ExemptStatus='Exempt'
Group By Salary, ExemptStatus;
GO
SELECT * FROM [Salaries]
I'm receiving the following
Msg 4511, Level 16, State 1, Procedure Salaries, Line 3
Create View or Function failed because no column name was specified for column 1.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Salaries'.
Thanks folks..
SQL Sp Or View To Retrieve Originial Sql Code
Nov 5, 2007I am interested in retrieving the original SQL code of various stored procedures & views that I have created & pass them into a field within a table.
I have used one before but cannot remember whether I used a stored proc or system view to retrieve this information, can anyone be of assistance???
Integration Services :: SSIS View Code After Encrypting
Sep 15, 2015Before we release an SSIS package into our test environment and then eventually into our production environment, we set the package protection level to "EncryptAllWithPassword"
The "View Code" feature is a nice tool to find things that might be buried in the package somewhere (e.g an complex expression to a variable).
PROBLEM : After one sets the package protection level to "EncryptAllWithPassword", one cannot see the xml source code any longer. It's like compiling and saving cs to the bin. Is their a way to view the code again??
How To Change View Code Or Table Form Stored Procedure + -
Jun 1, 2008need help with my problem
i have this view
this code change the value field "new_unit" evry month from 1 > 2 > 3 > 4
like this evry 4 month it return to 1 >2..........
------------------------------------------ for example
if i put
unit_date = 01/05/2008
and unit=1
than new_unit=1
Code Snippet
SELECT empID, ShiftType, unit_date, unit, ISNULL(NULLIF ((unit - DATEDIFF(mm, unit_date, '01/01/' + CONVERT(varchar, YEAR(GETDATE())))) % 4, 0), 4)
AS new_unit
FROM dbo.empList
my question is how to create a stored procedure that move forward (all the employee) the "new_unit" field
in +1 OR "unit_date" value MONTH +1
like create stored procedure name "plus" + so if i run this name stored procedure name "plus"
the stored procedure go to the viewor table and change the code view or table value
so i forward all the the "new_unit" or "unit_date" value IN one (change the cycle)+1
it doesn't matter if it change the "unit" value in the table "dbo.empList" or "unit_date" value
the important thing is that i can forward +1 or backward -1
evry time i run the stored procedure i get +1 (in the "new_unit")
and olso
create stored procedure name "minus" + so if i run this name stored procedure name "minus"
this stored procedure that move backward the the "unit" value in the table "dbo.empList" or "unit_date" value in -1
TABLE dbo.empList
empid ShiftType unit_date unit
----------------------------------------------------------------------------
11111 6 01/01/2008 1
2222 8 01/03/2008 4
3333 9 01/04/2008 3
TNX for the help
Code Access
Mar 13, 2008When a remote user tries to view my VIEW code using sp_helptext I want to deny permission.
Is there any way to achieve this in sql 2005?
Convert This Access Code To Sql
Jun 24, 2008Hello does anyone know how to convert this access code to sql. Also is there a program out there that will do it for you.
IIf([Ceridian]![UnionIndicator]="U" And [YearsService]>=3,[HealthBenefits]![medplan.Bi-WeeklyURBio-Rad]*26,[HealthBenefits]![medplan.Bi-WeeklyBio-Rad]*26) AS MedicalCostER, IIf([Ceridian]![UnionIndicator]="U" And [YearsService]>=3,[medplan]![Bi-WeeklyUREE]*26,[medplan]![Bi-WeeklyEE]*26) AS MEdicalCostEE
What Custom Code Can Access
Jun 8, 2007I am trying to find ways around the limitation of not being able to resize columns based on the text input. I am dynamically setting what values a column is getting from my query. I am wondering if I can access the table (and thus, the columns) from within the custom code.
For example:
Public Function Whynot() As Integer
Report.table1.TableColumn1.Width = ....
table1.TableColumn.Width = ....
Return 0
End Function
Neither of these seem to work, anyone else have any ideas?
Thanks
How To Access SqlDataSource Fields From Code?
Aug 18, 2007I have <asp:SqlDataSource> in aspx page that is being used by an updateable GridView. However, there are a couple fields in the dataset that contain filenames that I want to access directly in code and place them into some image tags on the page. This line should give me access to the dataset but how to I access the fields?
Dim myDataSource As DataView = DirectCast(SqlDataSourceGridView.[Select](DataSourceSelectArguments.Empty), DataView)
When the SqlDataSource is a DataReader I would access it with the code below but since this SqlDataSource is a DataSet I can't access it with this code.If myDataSource.Read Then If Convert.IsDBNull(myDataSource("CusAgentPhoto")) Then ImageAgent.ImageUrl = "/photos/nophoto.gif" Else ImageAgent.ImageUrl = AgentImagePath & myDataSource("CusAgentPhoto").ToString End If If Convert.IsDBNull(myDataSource("CusCompanyLogo")) Then ImageCompany.ImageUrl = "/photos/nophoto.gif" Else ImageCompany.ImageUrl = OfficeImagePath & myDataSource("CusCompanyLogo").ToString End IfEnd If What would be the correct way to get at the DataSet fields that contain the filenames?
Converting Access DB Code To Tsql--Help!!!!!
May 3, 2001We are migrating an access97 database to sql server7.0. there are queries in access which need to be made into tsql queries.And what are the steps to convert access97 to sql7.0 and how do i migrate memo fields to sql7. Is there some method to convert or tool that does that...any help is welcome and thanks in advance.
View 1 Replies View RelatedAccess C# Code From Integration Services
Apr 26, 2007I have an application in C# in which i have few events like download,Import and I have written all the code for that and its working fine but I have to schedule these processes so that I need not to run it manually daily. I am trying to write an Integration services package through which I could handle the required event, without rewriting the whole code.
Please Reply.
Thanks,
Pooja
bajaj.puja@gmail.com
Access Dataset In Custom Code
Jan 2, 2008Hi there!
For setting different languages on the column haeders i need to access a dataset from a custom code function call. I want to pass a parameter to the function that will allow me to find a specific item in the dataset - like a lookup function. I cannot find a way to get access to the entire dataset and to iterate through its components.
Is there any solution? Every hint will be helpful!
Thanks, Torsten
View Access
Jan 24, 2005I have a view created in Database1 accessing information in Database2. Do users need access to Database2 in order to select from the view?
View 4 Replies View RelatedHow To Access By Code Table Returned By SqlDataSource1 ???
Jun 5, 2008Hi to all,
I'm writting code that needs to pass data returned by a SqlDataSource object to another object (user control to graph data)
Query in SqlDataSource object is OK, I've seen data returned by it at a GridView Object but I need to pass that data to an user control that needs a reference to the table that has the data
... What would be the code that do that task ??
I'm new to develop web applications... using Visual Web Developer Express 2008
Help in advance
How Do I Access Sql System Stored Procs From Code
Jun 2, 2004Hello:
Okay not really understanding how to do this, but how do I access system stored procedures from code? Basically I would like to determine information about each table in my database, the primary key, number of columns, the names of the columns and the datatypes of the columns in each table. Not too much to ask. How do I go about accessing this information. I have a fairly good idea on how to do it using T-SQL but how do I do it using an assembly? Has anyone else done this before? Any help would be greatly appreciated!
Thanks
How Do I Access Sqldatasource Data Values In Code Behind?
Jan 1, 2006How do I access sqldatasource data values in code behind and bind them to strings or texboxes etc. as oposed to using Eval in the markup?
I can create a new database connection, but I would like to use the data values from the autogenerated sqldatasource control
Many thanks,
ERROR CODE 1204 WHEN UPSIZING FROM ACCESS 97
Dec 2, 1999How do you free up locks when there are not enough locks available to perform a large write of records to a table from Access97.
I have tried using the Access97 Upsizing wizard, appending data from Access97 to the SQL Server, and Importing from the SQL Server end.
Approx. number of records is 1 million.
Error code is 1204, "SQL Server has run out of LOCKS".
But when we increase the number of locks, we get another error message which says this is using up too much memory.
Please help with this error.
Gary J
How To Write Portable Database Access Code?
Jul 20, 2005I can access Microsoft Access database via ODBC on Windows 2000 usingC++. Now, I would like to use the SAME piece of code to access MS SQLon Windows. Can I achieve this without any modification to my existingcodes?Then, I would like to use the SAME piece of codes to access Oracleon Windows? Is it possible?Thanks in advance!
View 1 Replies View RelatedSQL Express - Access Denied Error Code 5
Dec 13, 2006Hi,
The install of SQL Express with advanced services on my WinXP Pro PC seemed to go fine but now I look at it and see that the actual SQL server hasn't installed. The log files says:
Product: Microsoft SQL Server 2005
Log File: ...SQLSetup0003_HKM1_SQL.log
Last Action: Install Finalise
Error String: Failed to set registry settings for server network libraries. The action is SetShilohRoot. The error is 5 (Acces is denied)
Also getting the same error for product: SQL Server Database Services.
Any help would be greatly appreciated.
How To Access Data From Dataset Specified In Report From Code?
Apr 6, 2007Hello all!
I have a question. I have report that have defined dataset. Can I somehow get access to data in this dataset from this report in Code section?
I need to write function that will return value from one field based on 45 parameters (they are values from 4 fields in this datset), like:
dataset fields:
RYear, RMonth, AYear, AMonth, CAtegory, Amount.
I need to get Amount based on RYear, RMonth, AYear, AMonth, Category values that I need to pass as parameters to functions.
Thank you
Copy Data From Access To SQL Express In Code?
Jun 1, 2006I have tried the upsize wizard from access 2000 and access 2003 and get records transfer in 90% of the tables I have data stored an 89 meg MS access database (2003) on an XP machine. I can use, compact and copy etc in MS Access without problem.
With the upsize, I get No errors, just does not reliably move ALL data. I have run it multiple times and occasionally get some data in these tables, but never all data in a few tables and inconsistent.
========================================
So I have written code to copy the data from the access database and move it field by field into an Upsized database that was structure only, no data.
I have ONLY one Identity field (autonumber in MS access) that is the Key Relationship number to all other tables.
1 questions and one "bug" report need solutions to both.
Question: How can I copy the value from the MS access autonumber field to the identity field. (Some of the autonumber rows have been deleted so not always sequentiall) (I can do this moving data in code between to MS access databases that have autonumber fields)
=========================
I tied an elaborate write around to addNEW and then read the data in the newly created row. Reading the Identity field, I look up that number in the MS access database. Then I copy the ACCESS data to the Newly created table row in SQL Exress and update.
It works fine for about 30 record updates, then the Identify field stops giving me the correct sequence for the Identity field value. Makes it impossible to make sure all of my Access rows get copied. Tried transactions but not supported with the ADODB configuration, but doubt that would help.
Example data returned in the SQL Express recordset, when I read the Identify field back I get
1,2,3 etc 31, 33, 32 (out of sequence) then it (my code) can't figure out the out of sequence and end up with the next identity value at 41.
The only info I found was that it could be a cursorlocation problem, what do you recommend?
?Is there a way in code to change the identity field to string, then copy the data and change back to identity field again?
Converting A View From Access
Nov 17, 2014I'm converting a View from access to Sql and I'm stuck on this IF statement.
IIf([FG_Qtys_1].[CoreQty]=0 Or [FG_Qtys_1].[CoreQty] Is Null,[KitCoreOnHand],[FG_Qtys_1].[CoreQty]);
I know that I could use CASE statement but i keep on getting errors.
My Case Statement:
case when [dbo.FG_Qtys_view].[CoreQty]=0 then dbo.FG_Qtys_Kits.KitCoreOnHand
when [dbo.FG_Qtys_view].[CoreQty] ISNULL then [dbo.FG_Qtys_view].[CoreQty]
end as CoreQty
MS Access 2003 SQL View
Feb 23, 2007Hello all, i hope someone that understands SQL can help me.
I have a report that runs from a access query, which is simple.
My problem is that i get an overflow error becuase one of the queries formulas are to divide some numbers and in this one instance there is a "0" involved.
My formula is:
Col2%: [Col 2 Hrs]/([TotHrs]-[Col 6 Hrs])
The problem is that [Col 2 Hrs]=0 this month for a specific employee.
In excel i know i can do
=IF([Col 2 Hrs]=0,"0",([Col 2 Hrs]/([TotHrs]-[Col 6 Hrs])))
and that works fine.
Can someone please show me how to do this in SQL so instead of returning an error it will display 0 or N/A.
Thanks inadvance
Hope i explained my issed well enough for everyone.
Deny Access To A View
Apr 5, 2006I'm having trouble creating a read-only view. I've got 1 or more tables that I wish to remain updatable but I want to create a view that covers the table and/or spans all the tables. However, I want the view to be select only. I can't seem to get it to work.
DENY UPDATE ON [dbo].[MyView] TO [dbo] CASCADE
All that seems to execute my dbo user can still use...
Update MyView set SomeID = SomeID + 10
Plus, ideally I just want to say, DENY UPDATE ON VIEW TO ALL
Any thoughts?
Truncate MS Access Table From Visual Studio Using Code
Dec 24, 2014I am building a Visual Sudio process that will Truncate and load data to an Access database (2010) from SQL Server (2012). I have one "Execute SQL Task" for one table (LungCancer_HF_Patients):
"DELETE LungCancer_HF_Patients.* FROM LungCancer_HF_Patients;" that parses and works.
I have another table (Report1):
"DELETE Report1.* FROM Report1;"
that does not parse with a message "The query failed to parse. Incorrect syntax near '*'."
Why the first one parses correctly and the other one does not?
To Create Unique Table Names And Access Them In Code...
Jan 17, 2006Hi all,My program is a central data processing application built in ASP.We have different companies that use different web pages on another webapplication (from different countries) to load some inventory data(merchandise - clothes).Here is my requirement: Load different types of data (in differentformats) into a common set of tables, to do this I have to firstfilter, do lookup's, use cross-reference tables on this data and thenload it into a couple of tables.Since data is so different everytime, I want to have one main storedprocedure in which I can build the table (I know the format of thisdata so I know what columns/types to create) into which I will feed thedata.After this I will create other stored procs/udf's that reference thistable (probably from the same main stored proc) 'cleanse' the databefore loading into the actual tables.How feasible is my approach? (if you can call it one :-) I do not wantto have seperate tables for each country, that will be too many and sothis plan...If not any other ideas will be really helpful.thanks much
View 3 Replies View RelatedCan Never Access Database Table From Code After Copying To Another SQL Server...
Nov 29, 2007I am not sure I understand the problem I am causing, but I am a beginner!
Here's the situation: I have a table located on MS SQL server database number 1. Said table, which we'll call WIDGET_PRICES, is accessed regularly by my existing source code and has no problems.
At some point, I decide to move operations to MS SQL database number 2 and do a very simple database copy of WIDGET_PRICES from database 1 to database 2 using the Microsoft SQL Server Management Studio.
The end result, inevitably, is that my source code can no longer access the very same table as it is located on the new database server. The code hasn't changed, it's still trying to access WIDGET_PRICES as always. And, from what I see on my screen through Management Studio, WIDGET_PRICES appears just fine.
An example error is the one I just got:
Microsoft OLE DB Provider for SQL Server error '80040e37'
Invalid object name 'YB_ITEMS'.
/yardbark/tampabay/header.asp, line 27
The only clue is that while my transferred tables often look named like "database1.WIDGET_PRICES on database 1, they wind up looking like database2.WIDGET_PRICES on dabase 2.
I include a little more detail and screenshots of the tables in questions at this web page.
Dave
I A View In Access Project I See Only All Columns
Oct 11, 2006I try to create a view in Access Project but in design view I only see "all columns" in a table. The individual columns are not listed. This happens only with a certain SQL Server database. Any suggestions ?
View 1 Replies View RelatedMigrate VIEW From Access To SQLserver ???
Jul 20, 2005I migrated a DB from Access 2000 to SQLserver 2000.The tables are OK, but I see the old Access views... as TABLE inSQLserver!!Any suggestions?--
View 3 Replies View RelatedMS Access' IIF Function In A MS SQL Server View
Oct 16, 2006I 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.....
SQL 2000 Taskpad View/access
Feb 22, 2006Hi there,
Not sure if this is possible but anyways. Need to restrict access on systems for users
and at the moment they are limited to being server and process administrators only.
This gives them enough room to do what they need to do, however the taskpad view in Enterprise manager only shows them the Log space used, not the datafiles.
The only way to get the TaskPad view to show the datafiles is to add them to the database creators role, which already gives them more permissions than they need.
So, just a shot in the dark but is there someway to get Taskpad view to show all the info there about the database?