locked
Can you separate Code and Data RRS feed

  • Question

  • I worked with Microsoft Access quite a bit and have gotten very comforatable with the process of:

    • creating AP-Code.mdb( with all the VB, Forms, Queries, Reports and Macros )
    • creating AP-Data.mdb (with all the tables)
    • linking AP-Data.mdb to AP-Code.mdb

    Now with SQL-server, if I restored a database from last week to get last weeks data - I lose any changes to stored procedures I made since then.    This primarily comes into play during testing.

    Also with SQL-Server, I have a scenario where I have 10 databases for separate companies.  The version for these 10 databases is ALWAYS the same. 

    Question: Is there any feasible way to have one database with all the tables (or 10 databases with 10 sets of tables) and 1separate database with all the stored-procedures. 

    Thanx!!!

    Angelo

    Monday, June 13, 2011 10:00 PM

Answers

  • Hi Angelo,

    Yes you can have you data on separate database and stored procedure on separate database, but you need to use 3 part naming to reference the tables.

     

    Ex.

     

    USE DatabaseB;

    CREATE PROC dbo.sp_FetchTableDta

    AS

    SELECT * FROM DatabaseA.dbo.TableName

    The only disadvantage is your database name will be hard codedly referenced in the procedures, which might cause issues when migrating to other servers and by mistake you create the database in different name.


    Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
    If you found this post useful, let us know by clicking "Vote as Helpful" and Propose as Answer
    Tuesday, June 14, 2011 12:37 AM

All replies

  • Hi Angelo,

    Yes you can have you data on separate database and stored procedure on separate database, but you need to use 3 part naming to reference the tables.

     

    Ex.

     

    USE DatabaseB;

    CREATE PROC dbo.sp_FetchTableDta

    AS

    SELECT * FROM DatabaseA.dbo.TableName

    The only disadvantage is your database name will be hard codedly referenced in the procedures, which might cause issues when migrating to other servers and by mistake you create the database in different name.


    Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
    If you found this post useful, let us know by clicking "Vote as Helpful" and Propose as Answer
    Tuesday, June 14, 2011 12:37 AM
  • You can also see the differences between two technologies; https://sites.google.com/site/booksdeeplibra/Microsoft-Access-Developers-Guide-to-SQL-Server  & http://msdn.microsoft.com/en-us/library/aa140013(v=office.10).aspx too.
    Satya SKJ, SQL Server MVP www.sqlserver-qa.net [knowledge sharing network]
    Get knowledge sharing network feeds by Email
    Tuesday, June 14, 2011 12:26 PM
  • That answes my question.  Not what I wnated to hear, but it does answer my question.  hard coding the database name defeats the purpose - as I have abouit 15 different databases with the exact same schema - I wanted to be able have DB1.sql, DB2.sql, DB3.SQl .....  DB5.SQ and DB_StoredProcedures.SQL.  I couldn't really hard code the DB name since the call would be coming from abouit 10 different databases.

    I appreciate the help.  At leaset now I know not to waste time trying to accomplish the.

     

    Monday, June 20, 2011 8:29 PM