We have two databases right now that house miscellaneous small applications. We have them in place so developers can develop small applications in them without going through the database request process and bother the DBAs. This was set up before I got here - so I wasn't involved in the decision to do such a thing. The obvious drawback to this is that a restore would wipe out data for multiple applications. As we move to a new 2005 server we are re-evaluating our methods and would like some input on this - and some other aspects.
I know that we could use filegroups and put objects related to certain applications within them so restore is independent of other apps. Each application has its own user which is granted execute on it's stored procedures. Are we missing anything here? I'm almost tempted to try to get separate databases created to uncomplicate it. If a developer can spend hours working on an app, they can spend 10 minutes on a form and wait 30 for us to create it.
Another thing I've noticed it that it can take quite a while to grant permissions to many stored procedures to a user in 2005. In 2000 there was a grid and you could arrow down and hit space, granting execute. You could also use code to do this, be we never really needed to since developers granted as they went and we would script the object and check the 'script obect level permissions' checkbox. This has since disappeared and granting execute is another step for us.
What do you think of 'sub schemas' for each role in the application - which is usually 'User' and 'Admin'? So we would have MylittleApp_User.ProcedureName and MylittleApp_Admin.ProcedureName with execute granted on the schema for each application user. Tables would be placed in MylittleApp schema.
In the past developers simply prefix their procedure with the application name to denote what application it belongs to.
Thanks for your advice on this - we don't want to get headed down the wrong path.
I have a webpage with a grid view in it that has sorting and filtering options. I am trying to take the sql statement and turn them into stored procedures. The problem I am having is converting this into a stored procedure. SQL = "Select CustSite.CustSite, FileInfo.FileID, FileInfo.Date, FileInfo.OriginalFileName, ReportType.ReportType, Equip.Equip, Tech.Name From [CustSite], [FileInfo], [ReportType], [Equip], [Tech] Where(FileInfo.CustomerID = '" & Session("CustomerID") & "') AND (CustSite.CustSiteID IN (Select Customer_CustSite.CustSiteID From [Customer_CustSite] Where(CustomerID = '" & Session("CustomerID") & "'))) AND (ReportType.ReportTypeID = FileInfo.ReportTypeID) AND (Tech.TechID = FileInfo.TechID) AND (Equip.EquipID = FileInfo.EquipID) AND (Equip.CustSiteID = CustSite.CustSiteID)" If Site.SelectedItem.Value <> "" Then SQL += " AND (CustSite.CustSiteID = '" & Site.SelectedItem.Value & "')" End If If Date_Set.SelectedItem.Value = "Single" Then SQL += " AND (FileInfo.Date = '" & Start_Date.SelectedItem.Value & "')" ElseIf Date_Set.SelectedItem.Value = "Between" Then SQL += " AND (FileInfo.Date Between '" & Start_Date.SelectedItem.Value & "' AND '" & End_Date.SelectedItem.Value & "')" End If If Report_Name.SelectedItem.Value <> "" Then SQL += " AND (FileInfo.FileID = '" & Report_Name.SelectedItem.Value & "')" End If If Report_Type.SelectedItem.Value <> "" Then SQL += " AND (FileInfo.ReportTypeID = '" & Report_Type.SelectedItem.Value & "')" End If If Equipment.SelectedItem.Value <> "" Then SQL += " AND (FileInfo.EquipID = '" & Equipment.SelectedItem.Value & "')" End If If Tech.SelectedItem.Value <> "" Then SQL += " AND (FileInfo.TechID = '" & Tech.SelectedItem.Value & "')" End If
Can someone point me in the right direction? Thanks in advance for the help.
Now I need to generate a datagrid with the following details: MyID, MyName, Doc's Name, Friend's Name, Pet's Name.
I have some success with the following code:
PROCEDURE dbo.PeopleISawTodayDB @ID as Int AS BEGIN SELECT PeopleISawTodayDB.MyID, PeopleISawTodayDB.MyName, PeopleISawTodayDB.DocID, PeopleISawTodayDB.FriendID, PeopleISawTodayDB.PetID, DoctorsDB.ID, DoctorsDB.Name, FriendDB.ID, FriendDB.Name, PetDB.ID, PetDB.Name FROM PeopleISawTodayDB INNER JOIN DoctorDB ON PeopleISawTodayDB.DocID = DoctorDB.ID INNER JOIN FriendDB ON PeopleISawTodayDB.FriendID = FriendDB.ID INNER JOIN PetDB ON PeopleISawTodayDB.PetID = Pet.ID WHERE PeopleISawTodayDB.JobID=@ID END
BUT it will only make a row appear if there are ID's in each of the PeopleISawTodayDB respective ID fields.
If I want to leave one blank (as in I didn't see that person that day), I would like it to still find the other details and populate the datagrid.
How can we call Stored Procedure inside any SQL Statement For Example. If we have procedure name sprocCurrentPriority select * from tablename where colmunname = exec sprocCurrentPriority
Hi Everyone, I have a Auto database table which store company auto business records. If I want to know how many BMW still in store, I use Stored Procedure as <code>Select Count(*) From AutoAd Where Make = 'BMW' and InStore = 1. </code> Now I want to know how many autos in store for all make (BMW, AUDI, HONDA, FORD...). Does single stored procedures can instead twenty stored procedures to solve this question? If can, please help me to show your stored procedures and how to reture the result to .NET program. Thanks, Lin
I am trying to simulate the <sequence name>.nextval of oracle in SQL Server 2000.
The situation is that i need to be able to run programmatically INSERT statements. In Oracle I am able to do INSERT INTO TABLE_A (ID, NAME) VALUES (TABLE_A_SEQUENCE.NEXTVAL, 'MIKKO') in a single prepared statement in my code.
I know that to recreate this in SQL Server 2000 I need to create a stored procedure and table to set up a way to generate "the next value" to use in my INSERT. but the schema below forces me to do my insert in 2 steps (first, to get the next value. second, to use that value in the INSERT statement), since I cannot execute the stored procedure inside my INSERT statement.
Is there any way for me to generate values within my INSERT statement that would simulate Oracle's <sequence name>.nextval and allow me to execute my INSERT in 1 line of code?
TABLE ----- CREATE TABLE sequences ( -- sequence is a reserved word seq varchar(100) primary key, sequence_id int );
MS SQL SERVER STORED PROCEDURE: ------------------------------- CREATE PROCEDURE nextval @sequence varchar(100)AS BEGIN -- return an error if sequence does not exist -- so we will know if someone truncates the table DECLARE @sequence_id int set @sequence_id = -1
UPDATE sequences SET @sequence_id = sequence_id = sequence_id + 1 WHERE seq = @sequence
I am trying to call a stored procedure inside a SQL SELECT statement. Has anybody had to do this in the past? I have a SELECT statement in a Microsoft Access database and I need that SELECT statement to call the stored procedure in the SQL server. Any help would be appreciated
This is my foray into Stored procedures, so I'm hoping this is a fairly basic question.
I'm writing a stored procedure, in which I dynamically create an SQL statement. At the end of this, the SQL statement reads like:
Code SnippetSELECT COUNT(*) FROM StockLedger WHERE StockCode = 'STOCK1' AND IsOpen = 1 AND SizeCode = 'L' AND ColourCode = 'RED' AND LocationCode IS NULL AND RemainingQty > 0
Now this statement works a charm, and returns a single value. I want to assign this count to a variable, and use it further on in the stored procedure. This is where the problems start - I cant seem to do it.
If I hard code a statement, like
Code SnippetSELECT @LineCount = COUNT(*) FROM StockLedger that works fine (although it brings back a count of all the lines).
But if I modify the dynamically created SQL Statement from earlier on to:
Code SnippetSELECT @LineCount = COUNT(*) FROM StockLedger WHERE StockCode = 'STOCK1' AND IsOpen = 1 AND SizeCode = 'L' AND ColourCode = 'RED' AND LocationCode IS NULL AND RemainingQty > 0 it doesnt work - it complains: Must declare the scalar variable "@LineCount".
Just to clarify, when I say "dynamically created an SQL statement, I mean that by a bunch of conditional statements I populate a varchar variable with the statement, and then eventually run it exec(@SQLStatementString)
So, my question would be, how do I do this? How do I make a dynamically generated SQL statement return a value to a variable?
This question is about SQL Server 2005: I have been trying to figure out how to copy tables and stored procedures between 2 databases (on the same server) using SQL Server Management Studio. I have tried right clicking on the table name, "script table as", "drop to", "clipboard", then I click on the 2nd database, and then click on the "tables" . I change the name of the database and click "execute". This creates the table but does not copy the data. I have also tried "create to" "clipboard" and "insert to" "clipboard" and cannot seem to be able to figure out how to get the results that I want. I am new at this but need to get the tables with the data copied along with the stored procedures, even if I have to do them one at a time. When I was using SQL Server 2000, I was able to use DTS to copy objects to other databases easily. Can someone please tell me a way to accomplish what I need to do? I have gotten information here before that was very useful and was hoping that someone can help me again.Thank you so much. Carol Quinn
One of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?
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!!
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
I know the SQL syntax to join two tables in different databases on the ame server, but does anyone know if a SQL statement will allow you to join tables from different databases on different servers?
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
I have a basic sql statement, where I have a usersID, and I want to joing that usersID to another table in another database to get the users first and last names. How do I join across databases... each with a different connection string? Here's what I want.. Select usersID from tableA in databaseA, and usersFirstName, usersLastName from table B in database B where the usersID from tableA = the usersID in tableb.
I want to create an UPDATE statement that updates a value in a table indatabase A based on the results of an inner join between tables eachresiding in deifferent databases.What is the correct syntax for doing this?The following should give you an idea of what I'm trying to do:UPDATE A.dbo.tblCarsSET A.dbo.tblCars.Car = 'Ferrari'FROMA.dbo.tblCars INNER JOIN B.dbo.tblHouses ONA.dbo.tblCars.RecID = B.dbo.tblHouses.RecIDWHERE (B.dbo.tblHouses = 'Mansion')
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
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?
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'
i need to use only one stored procedure and access many tablesso how write a stored procedure for that dohelp me looking forward for a reply to the earliest i am developing web page using asp.net using c# and sqlserver as backend
