f



Storing all stored procedures in one database to minimize different connection strings

In order to minimize the number of connection strings I have to use to
access different databases on the same Sql Server, I was considering
storing all stored procedures in just one database.  I want to do this
because connection pooling in my application - ASP.NET is based on
this connection string.  So if I need to access 6 different databases
on one sql server & set 6 different connection strings, I end up
creating 6 different connection pools.

Other than it might create more management work for the DBA, are there
any performance implications with implementing this scheme?  Do stored
procedures run any slower if they access tables that are stored in
different databases within the same server?

Any comments/suggestions are appreciated.

TIA,
Minh Tran
0
8/16/2004 9:11:16 PM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

1 Replies
668 Views

Similar Articles

[PageSpeed] 40

Minh Tran (minhtran_01@yahoo.com) writes:
> In order to minimize the number of connection strings I have to use to
> access different databases on the same Sql Server, I was considering
> storing all stored procedures in just one database.  I want to do this
> because connection pooling in my application - ASP.NET is based on
> this connection string.  So if I need to access 6 different databases
> on one sql server & set 6 different connection strings, I end up
> creating 6 different connection pools.
> 
> Other than it might create more management work for the DBA, are there
> any performance implications with implementing this scheme?  Do stored
> procedures run any slower if they access tables that are stored in
> different databases within the same server?

The first question that strikes me is why you would have six 
databases for the same application.

But given that this is not something that you cannot change, I 
recommend against having all stored procedures in one database. This
is not because of performance, but one of manangement. Say that someone 
says "let's set up a test environment on the the same server". If you
have hardcoded database names in your stored procedures, you lose.

The connection-string problem could easily be overcome by
always using the same database when you connect, and then specify
the procedure as db.owner.sp.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
0
esquel (7068)
8/16/2004 9:43:29 PM
Reply:

Similar Artilces:

Trigger: To fill another Database with using Stored Procedures of the other Database
Hello everyone, I face currently a problem where I could need some input for searching the source of the Problem System: SQL Server 9.0 I fill from Database A with triggers Database B, everything works fine. On Database B there is a Stored Procedures that checks the records and add additional information accordingly, this Stored Procedures is normally called by the application on "update and insert" in the according table. When I try to call this Stored Procedures from the Database A, the trigger does not work anymore, even if I do a try catch over the whole trigger, he never reach the Catch and the insert I try to do there to get the error message. On both Databases the user, that is taken to execute the trigger is existent and DB-Owner of both Databases. If I go and execute the Stored Procedures manually after an insert or update to Database B everything works fine. I also already tried to check on Database B if there is an insert or update from Database A and if, to execute the Stored Procedures, with the same result, nothing and all happens anymore, neither update on Database A and also not on Database B. And also I cant catch the error as the Try/Catch is not working. Hope I could explain it understandable and maybe someone remembers already having the same problem. Thanks & Best regards Pascal (pascal.baetscher@gmail.com) writes: > I face currently a problem where I could need some input for searching > the source of the Problem > >...

How to FAST and EASY ENCRYPTION ALL Stored Procedure in my MS-SQL Database ?
My MS-SQL 2000 Database have 50 more Stored Procedure . How to FAST and EASY ENCRYPTION ALL Stored Procedure in my MS-SQL Database? (mickyang@gmail.com) writes: > My MS-SQL 2000 Database have 50 more Stored Procedure . > > How to FAST and EASY ENCRYPTION ALL Stored Procedure in my MS-SQL > Database? I assume that you have the source code in files. Use a text editor that supports Find/Replace with regular expressions. Open all files in the editor. The change " AS *$" to " WITH RECOMPILE AS" in all files. Good text editor to use is TextPad, http://www.textpad.com. I would also recommend that you keep a backup directory, and use a compare merge/tool that permits you easily undo false positives. An excellent tool for this is Beyond Compare, http://www.scootersoftware.com. If you want something more robust, you would need to write a program that parses the file. Hardly worth it, if you only have 50+ procedures. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx ...

Stored procedure from stored procedure
Is it possible to create a stored procedure from a stored procedure? When I attempt this inanity, it doesn't blow up until syntax error at the first "end procedure" statement of the SP I'm trying to create within the outer SP. Can this be done? Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask). On 24 Sep 2003 11:47:09 -0700, red_valsen@yahoo.com (Red Valsen) wrote: >Is it possible to create a stored procedure from a stored procedure? >When I attempt this inanity, it doesn't blow up until syntax error at >the first "end procedure" statement of the SP I'm trying to create >within the outer SP. Can this be done? > >Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask). Write it out to flatfile and execute the create from command line using dbaccess??? John Carlson wrote: > On 24 Sep 2003 11:47:09 -0700, red_valsen@yahoo.com (Red Valsen) > wrote: > > >>Is it possible to create a stored procedure from a stored procedure? >>When I attempt this inanity, it doesn't blow up until syntax error at >>the first "end procedure" statement of the SP I'm trying to create >>within the outer SP. Can this be done? >> >>Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask). > > > Write it out to flatfile and execute the create from command line > using dbaccess??? As John said, somewhat obliquely, there is...

bringing together two different databases (with different schemes) into one database
Hi, I use Oracle 9i and would like to mix two different databases into one. These two databases have different tables and views. Each name of a table or view is unique and does not exist in the other database. Now I would like to bring them all together into a new database so that this new database has all the information of the other two databases (table-descriptions and contents of tables/views). Also it would be great if this would be possible autimatically at a special time, i.e. in the night. If you can help me and have a manual reference, please tell me the URL because I am really new to Oracle. Thank you very much, Dennis How big the schmeas are in those database? I think export and import would be a good idea in this case. Take schema export from one database and import it in the a database where you want. For an example, On database A exp system/password@tnsname file=username.dmp log=username.log owner=user_name_which_you_want_to_export direct=y statistics=none On Database B Create a similar user and with the exact default tablespace (for convineance purpose). Once done with user creation. imp system/password@tnsname file=username.dmp log=imp_username.log fromuser=username touser=username statistics=none once you see 'imported successfully finished without warning', then analyze the newly imported schema in database B. For more help, search at http://asktom.oracle.com site. if you are really sure that none of table has same name : export from data...

How to return a Pk value from one stored procedure to another stored procedure
Dear All, I have one stored procedure like sp_insertEmployee Employee Table Fileds Eno int pk, ename varchar(100), designation varchar In stored Procedure After inserting the ename and designation it has to return the Eno pk Id automatically I have another Department deptno int pk, Eno int fk, Depname varchar In this stored procedure I need to execute the sp_insertEmployee Stored procedure and we need that Pk return value after executing that By using that Id in this Department table we will insert the eno can u help me out on this issue Thanks CREATE PROCEDURE sp_insertEmployee ( @Ename VARCHAR(100), @Designation VARCHAR(100), --Length? @Eno INT OUTPUT ) AS BEGIN --Procedure INSERT Employee ( Ename VARCHAR(100), Designation VARCHAR(100) --?? ) VALUES ( @Ename, @Designation ) SET @Eno = SCOPE_IDENTITY() END --Procedure ------------------------------ When you execute this procedure from the procedure that inserts the record in the cross-reference table between department and employee, call it like so. EXEC sp_insertEmployee @Ename, @Designation, @Eno OUTPUT Alternately, you can also have the sproc RETURN the @Eno, instead of having it as an OUTPUT parameter. CREATE PROCEDURE sp_insertEmployee ( @Ename VARCHAR(100), @Designation VARCHAR(100) --Length? ) AS BEGIN --Procedure INSERT Employee ( Ename VARCHAR(100), Designation VARCHAR(100) --?...

Search for a table/string within job steps, dts, database, stored procedures etc
Hi All : A couple of tables have been identified to be deleted. My job is to find if it is at all used. On searching the web, i found a proc to search for a string within all databases in a server. using system sproc : sp_msforeachdb it searches for a string in views, sprocs, functions, check constraints, defaults, foreign key, scalar function, inlined tablefunction, primary key, 'Replication filter stored procedure, System table, Table function, Trigger, 'User table, 'UNIQUE constraint''Extended stored procedure' So it is pretty extensive. But i dont think it is covering the code within execsqltasks in DTS, and tsql code within JOB STEPS. Those are the two more places where code exists in my server. If any of you have done so in the past, do let me know if there is a system stored proc or code that you have written, to do the same thanks RS Link to the above procedure http://www.sql-server-performance.com/ak_find_sql_server_database_objects.asp (rshivaraman@gmail.com) writes: > A couple of tables have been identified to be deleted. My job is to > find if it is at all used. > On searching the web, i found a proc to search for a string within all > databases in a server. > using system sproc : sp_msforeachdb > > it searches for a string in > views, sprocs, functions, check constraints, defaults, foreign key, > scalar function, inlined tablefunction, primary key, 'Replication > filter stored procedure, System ...

calling a stored procedure from a stored procedure
Hi Folks, I'm trying to define a (I thought) trivial stored procedure (SP) as a front-end to the standard SP "dbms_system.set_ev", which I want to use to generate a 10046 tracing event. I'm trying to execute the following PL/SQL: create or replace procedure rob_enable_tracing ( sid in integer, serial in integer ) is begin dbms_system.set_ev(sid, serial, 10046, 4, ''); end; and getting the error: PLS-00201: identifier 'DBMS_SYSTEM.SET_EV' must be declared If I provide the schema in which "set_ev" is defined, in this case "SYS", as in: create or replace procedure rob_enable_tracing ( sid in integer, serial in integer ) is begin sys.dbms_system.set_ev(sid, serial, 10046, 4, ''); end; I get: PLS-00201: identifier 'SYS.DBMS_SYSTEM' must be declared Any suggestions? thanks, RU On May 10, 1:25 pm, RU <r...@vakuum.de> wrote: > Hi Folks, > > I'm trying to define a (I thought) trivial stored procedure (SP) as > a front-end to the standard SP "dbms_system.set_ev", > which I want to use to generate a 10046 tracing event. I'm trying > to execute the following PL/SQL: > > create or replace procedure rob_enable_tracing ( > sid in integer, > serial in integer > ) > is > begin > dbms_system.set_ev(sid, serial, 10046,...

Assessment of Database Stored Procedures
I am attempting to compile a list of questions that will enable me assess 'at risk' stored procedures that need to be remediated in order to minimize unplanned downtime and enhance database performance. Is there a subset of criteria (parameters) from the MS SQL Server Best Practices Analyzer that can be used for this purpose? Any pointers to the appropriate documentation is most welcome. Thanks in advance. It depends what you class as acceptable criteria. It depends on the nature of the stored procedures. For example, if it's a simple SELECT statement , returning it in 6 seconds may be to slow for the end user., and could point to something else being the problem. Check http://www.sql-server-performance.com/articles_audit.asp for a systematic approach -- Jack Vamvas ___________________________________ Need an IT job? <a href="http://www.itjobfeed.com">uk it jobs</a> "Umar Reyi" <iyerra@indiatimes.com> wrote in message news:1177366044.733092.100680@y80g2000hsf.googlegroups.com... >I am attempting to compile a list of questions that will enable me > assess 'at risk' stored procedures that need to be remediated in order > to minimize unplanned downtime and enhance database performance. Is > there a subset of criteria (parameters) from the MS SQL Server Best > Practices Analyzer that can be used for this purpose? Any pointers to > the appropriate documentation is most welcome. > > T...

Database Database Database Database Software Cheap
Database Database Database Database Software Cheap Great Datase Software See Website Below. Ultra Easy to Learn (Typically 30 Seconds) Professional Programmable Database Ver. 2.3 2.1 Million Record Capacity, (New cond). Search Rate: 2000 / Records / Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Has Six Seperate Field Sets All Programmable. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Email). http://www.vehiclerepair.org/dbPro/dbpro.html ...

Database Database Database Database Software Cheap
Database Database Database Database Great Datase Software See Website Below. Ultra Easy to Learn (Typically 30 Seconds) Professional Programmable Database Ver. 2.3 2.1 Million Record Capacity, (New cond). Search Rate: 2000 / Records / Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Has Six Seperate Field Sets All Programmable. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Email). http://www.vehiclerepair.org/dbPro/dbpro.html ...

a database, is a database, is a err database
How many times can we see the same request from someone who wants to access data from a 'pick' database through what has come to be 'standard' practices (odbc, oledb) and still get the same old sloppy ' buy this proprietary utility (and above all, my services)' answer. I think most of these pick flavors should have some sort of layer (by now!) to handle this; If someone needs to do this, the service is really 'education' i.e to show them how. Lets cut the shit now and stop with this tired and silly BS and sad marketing schlock. Regards, -Jim Jim wrote: > How many times can we see the same request from someone who wants to > access data from a 'pick' database through what has come to be > 'standard' practices (odbc, oledb) > and still get the same old sloppy ' buy this proprietary utility (and > above all, my services)' answer. I think most of these pick flavors > should have some sort of layer (by now!) to handle this; If someone > needs to do this, the service is really 'education' i.e to show them > how. Lets cut the shit now and stop with this tired and silly BS and > sad marketing schlock. > > Regards, > > -Jim Jim who? I wonder? What is this? An attack on capitalism? Providing services for those who perhaps lack the time, skill, or knowledge to perform such tasks is hardly a crime. Maybe "standard" odbc and...

Stored Procedure querying another database
Hello, I need to come up with a stored procedure that will allow me to read data from another database. The database I need to read the data from is a UniData residing on a Unix server. The stored procedure needs to reside on my sql 2005 server. The task is very simple in Access as we have ODBC connections set up to the UniData via Informix (or IBM) UniData ODBC drivers. I can easily combine my UniData and Sql Server tables from within access. However, I can't seem to find a way to replicate the same behavior in MS SQL Stored Procedure without the use of Access. Is that even possible? Thanks, Marek On Feb 6, 1:25 pm, "mkarb...@gmail.com" <mkarb...@gmail.com> wrote: > Hello, > > I need to come up with a stored procedure that will allow me to read > data from another database. The database I need to read the data from > is a UniData residing on a Unix server. The stored procedure needs to > reside on my sql 2005 server. The task is very simple in Access as we > have ODBC connections set up to the UniData via Informix (or IBM) > UniData ODBC drivers. I can easily combine my UniData and Sql Server > tables from within access. However, I can't seem to find a way to > replicate the same behavior in MS SQL Stored Procedure without the use > of Access. Is that even possible? > > Thanks, > > Marek Linked server or OPENROWSET In SQL Server 2005 Books Online see the topic: Distributed Queries On Feb ...

Stored procedure access remote database
Hi, I am trying to write a stored procedure that can access a table on a remote database to insert some data on the calling server. Stored procedure would reside on Server A, establish some sort of database link to Server B, query the remote table, switch context back to the local server and insert data into a local table. Is this possible? What should I be searching for to get more information on how to do this? Any help is appreciated. Cliff On Mar 26, 4:44 pm, "Cliff Martin" <cliff.mar...@gmail.com> wrote: > Hi, > > I am trying to write a stored procedure that can access a table on a > remote database to insert some data on the calling server. > > Stored procedure would reside on Server A, establish some sort of > database link to Server B, query the remote table, switch context back > to the local server and insert data into a local table. > > Is this possible? What should I be searching for to get more > information on how to do this? > > Any help is appreciated. > > Cliff Wouldn't you better just issue insert into <local_table> select * from <table>@remote_db directly? What you describe is very inefficient. Look into the Distributed database manuals for further info. -- Sybrand Bakker Senior Oracle DBA > Wouldn't you better just issue > insert into <local_table> > select * > from <table>@remote_db > ...

stored procedure to create new database
Is there a stored procedure installed by sql server 2000 that I can call and just pass in the name of a new database and have it create the database for me? If not, how do I do it in sql? Thanks. "geoff" <cakewalkr7@hotmail.com> wrote in message news:bf5dd49d.0402271016.b8f3255@posting.google.com... > Is there a stored procedure installed by sql server 2000 that I can > call and just pass in the name of a new database and have it create > the database for me? If not, how do I do it in sql? Thanks. This is one way: declare @newdb sysname set @newdb = 'NewDat...

Creating database from stored proc with variable holding the database name
Here is my code ALTER PROCEDURE Test AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @From varchar(10) DECLARE @To varchar(10) DECLARE @DBName varchar SELECT TOP 1 @From = CONVERT(char,CreateDate,101) FROM CustomerInfo WHERE TicketNum = (SELECT TOP 1 TicketNum FROM CustomerInfo WHERE CreateDate <= DATEADD(mm, -30, CURRENT_TIMESTAMP) ORDER BY CreateDate DESC) SELECT @To = CONVERT(char,GETDATE(),101) SET @DBName = 'Archive_SafeHelp' CREATE DATABASE @DBName + ' ' + @From + ' ' + @To END I am trying to create a database based on the name contained in the variables. I get the error 'Incorrect syntax near '@DBName'. How do i accomplish this? Thanks Ganesh (gancy26@gmail.com) writes: > SET @DBName = 'Archive_SafeHelp' > CREATE DATABASE @DBName + ' ' + @From + ' ' + @To > END > > I am trying to create a database based on the name contained in the > variables. I get the error 'Incorrect syntax near '@DBName'. How do > i accomplish this? You need to use dynamic SQL: SELECT @sql = 'CREATE DATABASE ' + @DBName + ' ' + @From + ' ' + @To EXEC sp_executesql @sql For more information on dynamic SQL, I have an article on my web site: http://www.sommarskog.se/dynamic_sql.html -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se B...

database performance (ie stored procedures.
I do a number of transaction oriented queries such as: select top 1 * from data_table where (([field_name]='data_value') AND ([identity]>6556)) Now would this be possible as a stored procedure: find_eq(data_table, field_name, data_value, 6556) and have the stored procedure execute the select and give me back the results. If possible would it be benefitial from a performance standpoint? Thank, David (quincy451@yahoo.com) writes: > I do a number of transaction oriented queries such as: > select top 1 * from data_table where (([field_name]='data_value') AND > ([identity]>6556)) > > Now would this be possible as a stored procedure: > find_eq(data_table, field_name, data_value, 6556) > > and have the stored procedure execute the select and give me back the > results. > > If possible would it be benefitial from a performance standpoint? Yes and no. First, it's important to understand that you cannot pass table or column names as parameters to stored procedure. (Well you can, but then you would have to build the SQL string in the stored procedure, and you would be back where you started.) So you would have to have one procedure per table and column. This may sound clunky to you, but this is exactly where performance comes in. Say that you have these two procedures: CREATE PROCEDURE get_this_data @data_value varchar(20), @ident int AS SELECT TOP 1 col1, col2, col3 ...

Getting Data from a storeed procedure in a stored procedure
What I am looking to do is use a complicated stored procedure to get data for me while in another stored procedure. Its like a view, but a view you can't pass parameters to. In essence I would like a sproc that would be like this Create Procedure NewSproc AS Select * from MAIN_SPROC 'a','b',..... WHERE ......... Or Delcare Table @TEMP @Temp = MAIN_SPROC 'a','b',..... Any ideas how I could return rows of data from a sproc into another sproc and then run a WHERE clause on that data? Thanks Chris Auer Instead of using the complicated stored procedure, why don't you use a user defined function? Hi You may be better with a table function, but for a stored procedure you can create a (temporary) table and use the INSERT...EXEC statement (see Books online) e.g. CREATE TABLE #temporary_table (col1 char(1), col2 char(1) ) INSERT INTO #temporary_table (col1, col2 ) EXEC my_proc SELECT col1, col2 FROM #temporary_table John "Chris Auer" <chris.auer@gmail.com> wrote in message news:1117740278.709876.4550@o13g2000cwo.googlegroups.com... > What I am looking to do is use a complicated stored procedure to get > data for me while in another stored procedure. > > Its like a view, but a view you can't pass parameters to. > > In essence I would like a sproc that would be like this > > Create Procedure NewSproc > AS > > Select * from MAIN_SPROC 'a','b',..... > ...

Oracle Database and JavaMail in Stored Procedure?
Can I access JavaMail from the Oracle Database (not the App Server)? I am new to Java in Oracle database - any exampl,es out there of using JavaMail from Oracle (say, from a stroed procedure?) In answer to my own question, I have gotten JavaMail to work from an Oracle Stored procedure. There is an execellent walk thru of just such a thing in wrox Press 'Oracle 9i Java Programming'. Works quite easily actually. ...

EXcel sheet into database with stored procedure
Hi, What is the syntax of storing the content of an Excelsheet in a predefined table with a stored prcedure. Arno de Jong, The Netherlands. A.M. de Jong (arnojo@wxs.nl) writes: > What is the syntax of storing the content of an Excelsheet in a predefined > table > with a stored prcedure. As I don't use Excel much with SQL Server, I went to http://www.google.com and entered: Excel INSERT "SQL Server" and god a couple of hits. The first one is a blog entry from SQL Server MVP Dejan Sarka: http://solidqualitylearning.com/blogs/dejan/archive/2005/03/12/441.aspx. This should get you started. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx ...

Designing a database within a database... design question storing data...
I have a system that basically stores a database within a database (I'm sure lots have you have done this before in some form or another). At the end of the day, I'm storing the actual data generically in a column of type nvarchar(4000), but I want to add support for unlimited text. I want to do this in a smart fashion. Right now I am leaning towards putting 2 nullable Value fields: ValueLong ntext nullable ValueShort nvarchar(4000) nullable and dynamically storing the info in one or the other depending on the size. ASP.NET does this exact very thing in it's Session State model; look at the ASPStateTempSessions table. This table has both a SessionItemShort of type varbinary (7000) and a SessionItemLong of type Image. My question is, is it better to user varbinary (7000) and Image? I'm thinking maybe I should go down this path, simply because ASP.NET does, but I don't really know why. Does anyone know what would be the benifit of using varbinary and Image datatypes? If it's just to allow saving of binary data, then I don't really need that right now (and I don't think ASP.NET does either). Are there any other reasons? thanks, dave >I have a system that basically stores a database within a database (I'm > sure lots have you have done this before in some form or another). Please explain. What form is the data you are storing? If it isn't represented relationally then why use SQL Server? -- David Portas SQL Server MVP -- ...

Inter-database stored procedures and permissions
Hello all, this is my second post to this newsgroup. It's a question about stored procedures and permissions and how these behave between databases. Here's the scenario. I have a database that stores information for a system "A", and I have a different database on the same SQL server that stores the login and other info "LOGIN". I write a stored procedure in the "A" database that checks some tables in the "LOGIN" database, let's call this "SP_A". Additionally I have a user account that accesses all appropriate stored procedures in...

database link, dynamic sql and stored procedure
Hi all I'm a newbie in pl/sql , and i have a problem i cannot solve My problem is: i need a stored procedure to insert a row in a table that is in a remote database I need to pass the link to the procedure as parameter because i may have more than one table (and more than one server) to insert into. the table i'm trying to insert into is ibf_members, and it is in a schema named ipb_stage. The database name for both server and client is EMDB (i have only one server for testing purpose , so i'm trying to make the server to connect itself on a different schema) I definded this database link create database link emdb.forum_cv connect to ipb_stage identified by mypwd using 'ORACLE2'; now, if i try to execute this PROCEDURE add_to_cv (u_id in number, cv_list in varchar2) AS .... BEGIN .... update ibf_members@emdb.forum_cv set org_perm_id =cv_list where CV_MEMBERS.id=u_id; .... END; it works, but if i try this ( i try to send the table name as parameter in table_link) PROCEDURE add_to_cv (u_id in number, cv_list in varchar2, table_link in varchar2) AS .... BEGIN .... execute immediate 'alter session set global_names = true'; myquery:='update '||table_link||' set org_perm_id =:1 where CV_MEMBERS.id=:2;'; execute immediate myquery using cv_list, u_id; .... END; I have this error SQL error code: 6550 SQL error mes...

Stored procedure to create a new database with tables
I want my application to create a new database/tables when run for the first time. I have created a stored procedure to create the new database named "budget". When I run the stored procedure, it creates the budget database but the tables are created in the "master" database. Please help. Before the CREATE TABLE statements you should put this: USE Budget go "Shiller" <shillerc@gmail.com> ha scritto nel messaggio news:1172675843.097303.293060@8g2000cwh.googlegroups.com... > I want my application to create a new database/tables when run for the > first time. I have created a stored procedure to create the new > database named "budget". When I run the stored procedure, it creates > the budget database but the tables are created in the "master" > database. Please help. > On Feb 28, 10:44 am, "Massimo-Mastino" <mast...@hotmail.it> wrote: > Before the CREATE TABLE statements you should put this: > > USE Budget > go > > "Shiller" <shill...@gmail.com> ha scritto nel messaggionews:1172675843.097303.293060@8g2000cwh.googlegroups.com... > > > > > I want my application to create a new database/tables when run for the > > first time. I have created a stored procedure to create the new > > database named "budget". When I run the stored procedure, it creates > > the budget database but the tables are created i...

Identical database w/ identical stored procedures?
We have written an application which splits up our customers data into their individual databases. The structure of the databases is the same. Is it better to create the same stored procedures in each database or have them in one central location and use the sp_executesql and execute the generated the SQL statement. Thank you. Mayur Patel Better in terms of what? I mean, there are risks with using sp_executesql, and you've already made the decision to split your data (thus minimizing contamination and/or infiltration) so it seems a bit odd to allow users to jump into a common database (but there are ways to do this without sp_executeSQL)... What's your criteria for defining "better"? Stu Better in terms of performance of SQL Server. B/C if there is one common stored procedure, then the stored proc will have to create the sql statement to include which database to perform the action to. But, is the performance hit to do this really that high compared to creating the maintence nightmare of updating the same stored procedure in every database? Thank you Stu wrote: > Better in terms of what? > > I mean, there are risks with using sp_executesql, and you've already > made the decision to split your data (thus minimizing contamination > and/or infiltration) so it seems a bit odd to allow users to jump into > a common database (but there are ways to do this without > sp_executeSQL)... > > What's your criteria for ...

Web resources about - Storing all stored procedures in one database to minimize different connection strings - comp.databases.ms-sqlserver

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Eye tattoos: Tattooist Luna Cobra defends practice following calls to ban procedure
IT SOUNDS like the really gross scene in a horror movie.

Survivors of female genital mutilation outraged by 'compromise' which could legalise procedure
Survivors of female genital mutilation are fighting back against a so-called compromise, which could let a modified form of the procedure continue. ...

Doctors Perform Medical Procedure On Wrong Newborn
The procedure was meant for another child.

Butt procedures, male breast reduction growing slices of plastic surgery
Every 30 min of 2015, a rump was remodeled. Men had 40% of all breast reductions.

"Gynecologists Kavita Shah Arora and Allan Jacobs said procedures that slightly changed the look of a ...
"... were comparable to male circumcision or cosmetic procedures in Western countries like labiaplasty. Laws against mild modifications were ...

Mix-up leads to surgical procedure on wrong baby
Tennessee mom says her newborn was mistakenly given treatment he didn't need

New Chipotle Food Safety Procedures Include Shutting Down Restaurant If Anyone Barfs
... what happened inside, but it’s pretty much exactly what you would expect: repetitive instructional videos about food safety and cleaning procedures, ...

New Video Series Shows The Reality Of Abortion Procedures
New Video Series Shows The Reality Of Abortion Procedures

Yay going in for kidney stone procedure soon!
There is a G-d i have been in the hospital with stone pain and bowel pain the past week. the bowel issue resolved by itself without surgery ...

Resources last updated: 3/22/2016 8:28:05 AM