Schemas, Stored Procedures And Shared Databases
Dec 11, 2007
Hi,
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.
View 3 Replies
ADVERTISEMENT
May 25, 2006
I have a database schema that has an Address table used to store addresses for different entities such as Customers and Employees. I want to reuse the same Address record between different Customers and Employees without duplicating any address information. I'm not sure what the best approach might be.
Should have I have seperate stored procedures on the Address table that update and insert new addresses, where each Address record remains immutable once created? (So the update stored procedure actually creates a new Address record if the data changes). These stored procedures would then be invoked by business logic and used in tandem with stored procedures that act on Customers and Employees to ensure that no address records are duplicated.
Or should I create a view on a Customer joined with Address, and similarily with Employee and Address, and have stored procedures that act on these views and ensure that no Address records are duplicated. Should I use instead of triggers to override the behavior of insert and update on the view to achieve these?
I'm rather lost as to what direction I should take. Any help would be much appreciated, thanks!
View 1 Replies
View Related
May 23, 2008
This works great in a query:
USE KCADOWNLOAD
USE OUTHOUSE
GO
DELETE FROM OUTHOUSE.KCA_BLDGS_ACCES
INSERT INTO OUTHOUSE.OUTHOUSE.KCA_BLDGS_ACCES
(MAJOR, MINOR, ACCYTYPE, ACCYDESCR,
QUANTITY, SIZE, UNIT, GRADE, EFFYR, PCNTNETCONDITION, ACCYVALUE, DATEVALUED, UPDATEDBY,
UPDATEDATE,PID)
SELECT MAJOR, MINOR, ACCYTYPE, ACCYDESCR,
QUANTITY, SIZE, UNIT, GRADE, EFFYR, PCNTNETCONDITION, ACCYVALUE, DATEVALUED, UPDATEDBY,
UPDATEDATE,PID
FROM KCADOWNLOAD.dbo.KCA_BLDGS_ACCES AS KCA_BLDGS_ACCES_1
But the stored procedure only creates ONE Use statement:
USE [KCADOWNLOAD]
...etc.
I assume I can run an SP utilizing two databases...
The SP fails because it hasn't got a clue what OUTHOUSE is...
Thank you!!!
View 3 Replies
View Related
Apr 28, 2008
Hey folks,
I'm looking to gather ideas on this before I start the project; so what better way to start than to ask here? ;)
The situation is this: we are in the process of replacing a large data driven application with a new one. Before turning the old'un off (that's not for a minimum of 6 months), we need to migrate data between the two entirely different schemas, and I've been tasked with writing the interfaces.
Now, here comes the kicker; they don't just want a "straight-forward data conversion", they want to run a period where they can still update both databases and any changes in the old one are to be pushed into the new db.
Because the schemas are not the same, I believe replication is out of the question so I was looking for advice as to what possibilies I have ahead of me to achieve this.
I fear I may not have expained this well enough; so as always if there are holes in the detail let me know and I'll do my best to fill them in :)
Old db = SQL Server 2000
New db = 2005
Thanks,
George
View 13 Replies
View Related
Jun 9, 2015
I have a SQL Server 2012 Enterprise Edition install that I am needing to do the following:
- Provide db_owner access to a single user. Have the db_owner privilege apply for the user in all Schemas of the database (this is a generic AD account that is being used for an application).
They are wanting me to do this because they do not want to get so granular as to tell me all of the objects that the user will need specific access to in any of the given Schemas of the database(s).I have 8 databases, and each one has additional Schemas. These are all contained in a Default instance.I am finding that without explicitly applying permissions to the various objects in the Schemas that are not the default Schema for this user, I am a bit stuck.Is there any way to have the db_owner permission apply to the single user for all the Schemas in a given database (without making them a Sysadmin)?
View 4 Replies
View Related
Aug 24, 2006
I have several distributed databases with identical schemas that I have added to my SQL server as a set of linked servers. For the sake of simplicity consider a schema with single table with one column called Info. I would like to present a view with columns DatabaseName and Info, that presents all the rows from all the databases in a single view.
How can this be achieved this using a SQL Server View?
If not is not possible using Views, what approach you recommend?
View 1 Replies
View Related
Oct 14, 2015
I am familiar with the sp_MSForEachDBand the USE Parameter I did Google and found [URL]
View 3 Replies
View Related
Jan 7, 2008
Hi,
I have two databases lets say DB1 and DB2.
Schemas for both databases is same.
In both database schemas there are tables which has identity columns as primary key.
Now i want to merge these two databases in a single database say DB3.
It may also possible that some master records in both databases are common so they should not repeat in DB3
Is there any way so that i can do it quickly and as soon as possible.
Thanks in advance
Rohit
View 1 Replies
View Related
Mar 5, 2008
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
View 9 Replies
View Related
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
Sep 30, 2006
Hi,
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.
Thank you in advance for any help on this matter
View 1 Replies
View Related
Apr 11, 2007
I can't seem to get this to work and have to resort to a custom connection for each report in Server Manager. The problem with that is that I have to re-do it every time I re-deploy the reports. Is there a secret to this that I have not found yet?
Dick Campbell
View 9 Replies
View Related
Jan 5, 2007
I'm trying to use stored credentials to enable caching. I've created a special windows user account with minimum permissions for just this task, and once set up, it works great (almost).
I can update the shared data source using SQL Mgt Studio or directly via the Report Manager to use a set of -windows- stored credentials. But I don't seem to be able to do the same via VS.NET 2005. I can only store -SQL- credentials, which I have no need to enable and no desire to add to my surface area.
The problem lies that every time I deploy any report that using that data source (which is nearly all of them), the data source is re-published, which wipes out the stored credentials and caching immediately stops.
I've tried messing with the XML directly, with no luck, and changing assorted advanced settings, but nothing seems to stick.
Obviously I can manually update the credentials each time I deploy something, but surely it seems that there must be a better way. Is there a way to either set the DS to use stored windows credentials, or just plain prevent the deployment of the DS every time a dependent report is published?
Suggestions?
Geof
P.S. SQL 2005 Standard Ed SP1 32-bit
View 1 Replies
View Related
Nov 6, 2007
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?
Thanks,
Jason
View 16 Replies
View Related
Apr 29, 2008
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'
View 1 Replies
View Related
Jun 13, 2007
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?
View 3 Replies
View Related
May 13, 2008
Greetings:
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?
Thanks!
View 5 Replies
View Related
Apr 7, 2006
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"?
Thanks!
View 24 Replies
View Related
Apr 23, 2008
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
View 1 Replies
View Related
Mar 26, 2008
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
View 9 Replies
View Related
Jun 16, 2007
Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.
Create procedure spMyProc inputData varchar(50)
AS
----- some logical
procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------
View 5 Replies
View Related
May 8, 2008
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)?
Thanks in advance
John
View 5 Replies
View Related
Nov 1, 2007
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?
View 1 Replies
View Related
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
Jun 17, 2006
hi
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
looking forward for a replygayathri
View 1 Replies
View Related
Dec 15, 2006
I am interested to know about stored procedures in Mssql .Can anyone please help me out.
Thanx in advance.
View 1 Replies
View Related
Feb 24, 2007
Hello I have two stored procedures
@ID INT
AS
SELECT (CASE WHEN NUM >= 10 THEN CAST(PAID AS FLOAT) / CAST(NUM AS FLOAT) * 100 WHEN NUM < 10 THEN 0 END) AS PER
FROM (SELECT (SELECT COUNT(*) AS Expr1
FROM Event_data AS D LEFT OUTER JOIN
Events AS E ON E.id = D.Event_id
WHERE (D.Transaction_type = 1) AND (D.Player_id = @ID)) AS NUM,
(SELECT COUNT(*) AS Expr1
FROM Event_data AS D LEFT OUTER JOIN
Events AS E ON E.id = D.Event_id
WHERE (D.Transaction_type = 1) AND (D.Transaction_value > 0) AND (D.Player_id = @ID)) AS PAID) AS X
and
@ID INT
AS
SELECT P.*,'/' + DBO.GETCHIPFOLDER(@ID) + '/' + ISNULL(P.PHOTO,'BLANK.GIF') AS PIC,ISNULL(
(SELECT SUM(TRANSACTION_VALUE)
FROM EVENT_DATA WHERE PLAYER_ID=@ID AND TRANSACTION_TYPE=1
GROUP BY PLAYER_ID),0) AS WINNINGS FROM PLAYERS P
undefined P
The first returns a percentage for player wins, the second gives me a photo and sums the player winnings
I would like to combine the results so I can get the percentage and wininngs in one query, in another matter all together I would like create a procedure like the first but instead of returning only one player, I would like to return the percentage for each player
Thanks in advance for any light you can shine on this.
View 4 Replies
View Related
Feb 25, 2007
I have a question about stored procedures, Is it better to use stored procedures even if I only use it once at my site? Or is it better to write the sql-part directly in the sqldatasource?
And am I forced to create two different stored procedures even if they are exactly the same except the "Where-part"?
Now I have around 40 stored procedures, and quite many of them looks the same except the where-part...
(Iam just a beginner with SQL)
View 2 Replies
View Related
May 2, 2007
Hello every one,
I m working in aspx 2.0 with sql server 2005, please tell me how can I create Stored Procedures for two or more tables not a single table(select,insert,update,delete please send me the queries which can help me in easy way I will very thankful to you
Thank you
View 5 Replies
View Related
Jun 19, 2007
I am learning to make a ASP web site and feel that if i can do it the harder way using some stored procedures instead of using multiple datasources on each page requiring that it might be better. So i am wondering what are these used for:DECLARE vs just entering "@param1 varchar(30)"When i use "DECLARE @rc int" i get the error "Incorrect syntax near DECLARE"How to return values to ASP page in Visual Studio 2005 How to use @@rowcount - doesn't seem to work for me?i tried using DECLARE @rc intSET @rc = @@rowcountWhen to use GO, BEGIN etcIf i want to use the variable only in the procedure, and not needed to be inputed, do i need to put it in the CREATE PROCEDURE (section)?Should i use my own stored procedures or VS2005 created ones using datasources? not really procedures but SQL, in SQL can i do like IF ELSE? if i use my own i cant use the Optimistic Concurrency right? and whats that?
View 1 Replies
View Related
Jul 5, 2007
Is there a website or somewhere i can go to read up on stored procedures??
View 2 Replies
View Related
Aug 2, 2007
cREATE PROCEDURE emp @val varchar(50)AS
declare @test varchar(50)declare @a varchar(50)set @a= @val + '_a'set @test = 'alter table dbo.rights_user add ' + @a + ' varchar(50) null'
execute(@test)
GO
this is my procedure...anyth wrong here...i can able to execute procedure only with 3 char..egexec emp 'na'exec emp 'hr10'--->wen i try like this cannot..showing datataype mis match
View 2 Replies
View Related
Nov 18, 2007
Hi,
Can anyone help me. I've created a stored procedure in sql server and I'm trying to run it from my asp.net page. On Database Explorer I can't see it, or any for that matter, however I can see tables in the same schema.
Also I can't see it when I build a table adapter either.
Can anybody help?
Thanks
Sam
View 5 Replies
View Related
