I created a LinkedServer on MS SQL Server 2005 pointing to my Active Directory. Nothing too fancy. Simple LinkedServer with ReadOnlyAdmin Account assigned to CONNECTAS Property. Then I created some storedprocedures to retreive some data from the LinkedServer. Again nothing too fancy. Just a few simple LDAP Queries. Then I created a SQL...
What is the argument for and against putting code in stored procedures with the intention of making the code more maintainable (i.e. Easier to make changes to the business rules without recompiling code)? All else being equal what makes a stored procedure better/worse for maintenance? ...
I've just started using LINQ to SQL on a mid-sized project, and would like to increase my understanding of what advantages L2S offers. One disadvantage I see is that it adds another layer of code, and my understanding is that it has slower performance than using stored procedures and ADO.Net. It also seems that debugging could be a cha...
Hi, I'm rewriting a stored procedure at work, primarily to stop it from doing dirty reading and have not made any significant structural changes. However, running the new version against the current version I have found the new version to run almost twice as long on a dev database which doesn't have a lot of activity! Following the adv...
I'm trying to script the changes to a SQL Server Compact Edition database running on windows mobile 6 and could really use: EXECUTE sp_rename 'MyTable.SomeColumn', 'BrandNewName', 'COLUMN' What other system stored procedures are available? What are the differences to the non compact version? Edit: There ARE system stored procedures...
I am using MSSQL 2005 Server and I have the following SQL query. IF @CategoryId IN (1,2,3) BEGIN INSERT INTO @search_temp_table SELECT * FROM (SELECT d.DataId, (SELECT [Name] FROM Category WHERE CategoryId = d.CategoryId) AS 'Category', d.Description, d.CompanyName, d.City, d.CategoryId, d.CreatedOn, d.Rank, d.vot...
I currently have a stored procedure that returns a list of account numbers and associated details. The result set may contain multiple entries for the same account number. I also want to get some aggregate information such as how many distinct accounts are contained within a particular result set. Is there some way to retrieve such a vie...
Hi, i'm creating a procedure to update/insert a table using merge statement(upsert).now i have a problem: using procedure parameters i have to do this upsert. procedure xyz( a in table.a%type,b in table.b%type,....) is some local variables; begin merge into target_table using source_table --instead of the source table, i have to use p...
I have a stored procedure that returns a large number of results, and would like a better way to debug/ parse the results than copy/pasting into excel or whatever - is there a way to pass the results of the procedure into a query? e.g., if the procedure call was something like: exec database..proc 'arg1','arg2','arg3' my thought was ...
Hi, I am attemting to create a storedproc that reads like: Select ep.EmployeeID, GetEmployeeFirstName(ep.EmployeeID), GetEmployeeLastName(ep.EmployeeID), ed.EmployeeDateOfBirth, ed.EmployeeAddress, ed.EmployeeAddress2, ed.City, ed.State, ed.ZipCode From EmployeeProfile ep, EmployeeDetail ed Where ep.EmployeeID = ed.Em...
I just started using Linq to SQL, and it just occurred to me that I don't really need any sprocs in the database since i can do all the data access through Linq to SQL. Is there any reason to write sprocs with Linq to SQL? Thank you. ...
I'm creating an assembly in C# for MS SQL 2005. This assembly creates a stored procedure and runs a dynamic query based on parameters passed into the stored procedure. Is there a simple function in C# to prevent SQL injection? For example string myQuery = "SELECT * FROM dbo.MyTable WHERE lastName = '" + injectionCheck(arg1) + "'"; T...
Consider the following stored procedure: SELECT * FROM Customers; SELECT Customer.Id, Customer.Name, Order.Total, Order.DateOrdered FROM Customers INNER JOIN Orders ON Customers.Id = Orders.CustomerId; The procedure obviously returns two result sets which I'm trying to retrieve with this partial class method: public partial class DB...
I have this idea that using SQL VIEWS to abstract simple database computations (such as a count on a relation) is sufficient, and you don't need procedures (== procedural code) A simple sql view + a where clause >> a stored procedure with parameters sometimes While making this point I imagined a way of retrieving table/view data withou...
In SQL Server I have been using the ^ symbol however that doesn't seem to work in Oracle. How do I do a bitwise exclusive OR in Oracle?? Thanks ...
I have an oracle function which has an in parameter which is of rowtype of a table, from a select statement i need to pass the current row to this fucntion so that it does some processing and returns a value. Is there a pseudo variable that can be used within the context of a select statement something equivalent to a old and new in trig...
I think I have the same problem as kcrumley describes in the question "Problem calling stored procedure from another stored procedure via classic ASP". However his question does not really include an solution, so I'll give it another shot, adding my own observations: I have two stored procedures: CREATE PROCEDURE return_1 AS BEGIN ...
I have a records of events with starttime and endtime for a calendar control. I want to get the events done on a particular date say 2/28/2009 But the db table has date data in form 2/28/2009 10:00:00, 2/28/2009 12:00:00. I tried this query in a sproc created in VS 2005 IDE but it didn't work ALTER PROCEDURE dbo.spSelectBenchEvent (...
I am very new to SQL i can work with basic statements fairly easily but i haven't figured out loops yet. Foreach(JobHeaderID AS @OldJobHeaderID in dbo.EstimateJobHeader WHERE EstimateID=@OldEstimateID) { INSERT EstimateJobHeader (ServiceID,EstimateID) SELECT ServiceID, @NewEstimateID FROM EstimateJobHeader WHERE EstimateI...
In MSSQL Server 2008 is there a way to construct the flowing as a stored procedure where the ... is passed in as a parameter. And not have to have a "separating" stored procedure or function which splits a csv and returns a table? select * from atable where atable.id in (...) ...