f



Stored Procedures Syntax

Hi All

Im still pretty new to using stored procedures and am not sure what syntax i
should be using. The variable @LocationID will be something along the lines
of 002, 003 and so on. What i want to do in the procedure is see if the
table already exists, and if so delete it (The code i have only works if
there is a record in the table). My problem with the syntax is that i want
to combine the word "Location" with the @LocationID variable when making the
new table and when checking if the table already exists but im unsure how to
combine the two for use in the procedure

Thanks in advance


/*
** Create New Location ID Table
*/

CREATE PROCEDURE [dbo].[CreateLocation]

 @LocationID Char(3)   /* New Location ID Number */

AS

 IF EXISTS (SELECT * FROM (Location + @LocationID))
  DROP TABLE [dbo].[(Location + @LocationID)]

 CREATE TABLE [dbo].[(Location + @LocationID)](
  [MachineName] [VarChar] (100) NOT NULL)
GO


0
jarrodm (28)
10/25/2003 10:47:29 AM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

1 Replies
317 Views

Similar Articles

[PageSpeed] 23

"Jarrod Morrison" <jarrodm@ihug.com.au> wrote in message
news:bndkc2$vda$1@lust.ihug.co.nz...
> Hi All
>
> Im still pretty new to using stored procedures and am not sure what syntax
i
> should be using. The variable @LocationID will be something along the
lines
> of 002, 003 and so on. What i want to do in the procedure is see if the
> table already exists, and if so delete it (The code i have only works if
> there is a record in the table). My problem with the syntax is that i want
> to combine the word "Location" with the @LocationID variable when making
the
> new table and when checking if the table already exists but im unsure how
to
> combine the two for use in the procedure
>
> Thanks in advance
>
>
> /*
> ** Create New Location ID Table
> */
>
> CREATE PROCEDURE [dbo].[CreateLocation]
>
>  @LocationID Char(3)   /* New Location ID Number */
>
> AS
>
>  IF EXISTS (SELECT * FROM (Location + @LocationID))
>   DROP TABLE [dbo].[(Location + @LocationID)]
>
>  CREATE TABLE [dbo].[(Location + @LocationID)](
>   [MachineName] [VarChar] (100) NOT NULL)
> GO
>
>

You could do this with dynamic SQL (see link below), but there are a couple
of reasons why you probably shouldn't. First, the data model is wrong, in
that the location ID should be part of the key in a single, larger table -
one table per location isn't a good model, and won't scale well. Second,
users who don't have permission to drop and create tables won't be able to
use this procedure, and generally you want to limit those permissions to a
limited number of users.

http://www.algonet.se/~sommar/dynamic_sql.html

This is your situation:

http://www.algonet.se/~sommar/dynamic_sql.html#Sales_yymm

Simon


0
sql (2120)
10/25/2003 12:53:20 PM
Reply:

Similar Artilces:

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...

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,...

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 > >...

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 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 ...

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',..... > ...

Stored Procedure Syntax
Hi All Im trying to use the code at the bottom of this message inside my stored procedure and when i execute the procedure in query analyzer i get the following error: Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'H'. This error happens a few times and im pretty sure it is because the select statement needs the ' around the data that is being searched. This is what the select statement looks like for me: SELECT * FROM Locations WHERE GroupID = H OR GroupID = HM OR GroupID = HMS OR GroupID = HMSS OR GroupID = HMSSR OR GroupID = HMSSRV And im pretty sure it is failing because sql wants the select statement to look like this: SELECT * FROM Locations WHERE GroupID = 'H' OR GroupID = 'HM' OR GroupID = 'HMS' OR GroupID = 'HMSS' OR GroupID = 'HMSSR' OR GroupID = 'HMSSRV' Am i thinking along the right lines with this ? If so does anybody know of a way that i can put the ' mark around the the data that is being searched for ? Any help is greatly appreciated Thanks CREATE PROCEDURE [dbo].[TestSP] @MachineName VarChar(50), @UserName VarChar(50) AS DECLARE @MachineLength Char(2) /* Local Machine Name Length */ DECLARE @SrchInt Char(1) /* Search Loop Integer Counter */ DECLARE @SqlStr VarChar(300) /* SQL Select String */ DECLARE @CurrMach VarChar(50) /* Local Machine Name Counter */ SET @SrchInt = 1 SET @MachineLength = Len(@MachineName) SET @SqlStr = 'SELECT * FROM Lo...

Using stored procedure result set in another stored procedure
I've been developing a stored procedure that uses a user defined function in the query portion of the procedure. However, since the end product needs to allow for dynamic table names, the UDF will not work. I've been trying to get this to work with converting the UDF to a procedure, but I'm having no luck. Here is the background on what I'm trying to accomplish. I need to perform a sub-identity on a table, I have the normal identity set, but there are multiple duplicates in the table and I need each set of duplicates numbered also (1,2,3,4 for duplicate set 1, 1,2,3 for dup 2). Here is what I have using the UDF (the UDF returns a variable table with indetity and ID for each record) ********** UDF ************* CREATE FUNCTION dbo.setDuplicateTransactions(@accountNumber as varchar(50)) RETURNS @dupTransactions TABLE ( ID int IDENTITY, transactionID int ) AS BEGIN INSERT @dupTransactions SELECT t1.transactionID FROM providerTransactions t1 WHERE t1.accountNumber = @accountNumber ORDER BY t1.transactionID RETURN END ******** Stored Procedure ************ CREATE PROCEDURE dbo.sp_parseTransactions AS DECLARE @accountNumber varchar(50) DECLARE temp_cursor CURSOR FORWARD_ONLY FOR SELECT t1.accountNumber FROM providerTransactions t1 GROUP BY t1.accountNumber HAVING MAX(isNull(t1.duplicateCount,0)) != COUNT(t1.transactionID) ORDER BY t1.accountNumber OPEN temp_cursor FETCH NEXT FROM temp_cursor INTO @acco...

Syntax error in stored procedure
Hi, I have problems with one stored procedure that I am trying to develop in my informix database. The procedure is at folloews create procedure updateFlags(emp LIKE employee.idEmployee,check like dailycheck.checkdate) DEFINE selected integer; FOREACH cursor FOR select first 1 iddailycheck into selected from dailycheck DC where DC.idEmployee=emp and DC.checkdate=check and DC.modificationtype='PLANNED' order by DC.modificationdate desc; update dailycheck set lastPlanned=1 where iddailycheck=selected; end foreach; end procedure; I am using dbaccess, what the result is always the same.... Can you help me???? loose the ; at > FOREACH cursor FOR select first 1 iddailycheck > into selected from dailycheck DC > where DC.idEmployee=emp and DC.checkdate=check > and DC.modificationtype='PLANNED' order by > DC.modificationdate desc; -------------------------^^---HERE------- see you Superboer. Unholy schreef: > Hi, I have problems with one stored procedure that I am trying to > develop in my informix database. > > The procedure is at folloews > > create procedure updateFlags(emp LIKE employee.idEmployee,check like > dailycheck.checkdate) > DEFINE selected integer; > FOREACH cursor FOR select first 1 iddailycheck > ...

Oracle Stored Procedures VERSUS SQL Server Stored Procedures
I want to know the differences between SQL Server 2000 stored procedures and oracle stored procedures? Do they have different syntax? The concept should be the same that the stored procedures execute in the database server with better performance? Please advise good references for Oracle stored procedures also. thanks!! <jrefactors@hotmail.com> wrote in message news:1120793932.351921.5760@g43g2000cwa.googlegroups.com... > I want to know the differences between SQL Server 2000 stored > procedures and oracle stored procedures? Do they have different > syntax? The concept should be the same that the stored procedures > execute in the database server with better performance? > > Please advise good references for Oracle stored procedures also. > > thanks!! > SQL Server Stored procedures use transact sql and run on SQL Server Oracle Stored Procedures use pl/sql (modeled on ADA) and run on Oracle. Oracle!=SQL Server They use two very different concurrent models and the syntax is totally different. (eg you don't need to create temp tables in Oracle like in SQL Server) otn.oracle.com and look under documentation. Jim On Thu, 07 Jul 2005 20:38:52 -0700, jrefactors wrote: > I want to know the differences between SQL Server 2000 stored > procedures and oracle stored procedures? Do they have different > syntax? No, you just have to obtain Microsoft version of PL/SQL. -- http://www.mgogala.com On Thu, 07 Jul 2005 20:38:52 -0700...

Stored Procedure Syntax Problem
I'm trying to use a couple of variables in a stored procedure. Things work fine when I hard code the data into the variables and also work fine when I use the variable in the WHERE clause and hard code data for the other variable. So, I think I have a syntax problem when trying to use "FrontPage.@FrontpageProduct" as seen in my example code below. I've tried many variations... and either get syntax errors or end up with a result of "no records." If somebody could assist me with the proper syntax for a "table_name.@variable_name" reference it would be greatly appreciated. The following procedure is called from a VB/.asp page. It's for a storefront front page where product codes listed in the table "FrontPage" are used to pull product data from table "Products." ============================= CREATE PROCEDURE dbo.frontpage @FrontpageProduct varchar, @FrontpageDay varchar AS SELECT * FROM Products LEFT JOIN FrontPage ON Products.Code = FrontPage.@FrontpageProduct WHERE FrontPage.theDay = @FrontPageDay GO ============================= Again, thank you in advance for any help. Dave Variable names are not qualified. The scope is the variable declared in the proc. Also, you need to specify the varchar length. For example:: CREATE PROCEDURE dbo.frontpage @FrontpageProduct varchar(30), @FrontpageDay varchar(30) AS SELECT * FROM Products LEFT JOIN FrontPage ON Products.Code = @FrontpageProduct WHERE...

Stored Procedure Syntax #2
Hi All Im using a stored procedure on my sql server and am unsure of the syntax that i should use in it. Im pretty sure that there is a way to do what i want, but as yet i havent been able to find much info on it. Basically the procedure takes the machinename and username supplied and searches a table or two for some matches and this part works great. The only problem i have is that with the app that ties in with the procedure returns some strange errors when no matches are found IE a blank recordset is returned. I know that i can change the program to trap this error but i would prefer to be able to basically say in the stored procedure, if the result of the SELECT statement returns no records i want to set the output to be something instead of nothing if possible. At the moment when i match is found it will be a 3 digit number IE 001 002 003 etc and i would like to basically say that if nothing is found make the output to be 000 if possible. Any help is greatly appreciated Thanks In Advance CODE: CREATE PROCEDURE [dbo].[Memberships] @MachineName VarChar(50), @UserName VarChar(50) AS DECLARE @MachineLength Int /* Local Machine Name Length */ DECLARE @SrchInt Int /* Search Loop Integer Counter */ DECLARE @SqlStr VarChar(500) /* SQL Select String */ DECLARE @CurrMach VarChar(50) /* Local Machine Name Counter */ SET @SrchInt = 1 SET @MachineLength = Len(@MachineName) SET @SqlStr = 'SELECT LocationID FROM Locations WHERE GroupID = ''...

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...

DB2 COBOL stored procedure to UDB SQL stored procedure
I have been given the task of taking a 3,200 line COBOL stored procedure and duplicating the same functionality in UDB 7.2 on the Windows platform with a procedural SQL stored procedure. I have fiddled with procedural SQL stored procs on UDB, but mostly short,trivial ones. Looking for good advice, links, etc. on the best approach to this. There doesn't seem to be the concept of subroutines within a stored proc, so I am guessing one main stored proc that may be calling multiple smaller stored procs? Just found out about this need late this afternoon and wondering what the best approach is to doing such a task. Any and all ideas most welcome. thanks, fv <PJackson@txfb-ins.com> wrote in message news:1175743257.812351.234300@y80g2000hsf.googlegroups.com... >I have been given the task of taking a 3,200 line COBOL stored > procedure and duplicating the same functionality in UDB 7.2 on the > Windows platform with a procedural SQL stored procedure. I have > fiddled with procedural SQL stored procs on UDB, but mostly > short,trivial ones. Looking for good advice, links, etc. on the best > approach to this. There doesn't seem to be the concept of subroutines > within a stored proc, so I am guessing one main stored proc that may > be calling multiple smaller stored procs? Just found out about this > need late this afternoon and wondering what the best approach is to > doing such a task. > > Any...

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) --?...

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 = 'NewDatabase' exec('create database ' + @newdb) See CREATE DATABASE in Books Online for full syntax. Simon ...

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 ...

Syntax for Stored Procedure Pervasive 8x
Pervasive 8, WinXP client Hi folks, I've just started working with Pervavsive and am simply trying to create an Update Stored Procedure. CREATE PROCEDURE spTestUpdate() AS BEGIN SELECT TOP 1000 "Part Master".PMDES1_01, "Part Master"."UDFKEY_01", LENGTH("Part Master".PMDES1_01) FROM "Part Master" where "Part Master"."UDFKEY_01" ='R' OR "Part Master"."UDFKEY_01" ='RS' END I'm gettign syntax errors. Looking online I have seen some samples using an ";" for end of statemen...

Copy stored procedures to another database
I have 2 databases, one that we use called MyShop and one that I develop on called TestShop. After I have a stored procedure working the way I want in TestShop , is there a way to just copy the SP to the other database without the copy and paste method?. Same if I have a new table. Any way to add it in without recreating it in the MyShop database? I am using Sql Server 2000 Thanks Andy "Andy" <andy@shirtshackomaha.com> wrote in message news:0JRNc.4$oA5.1@okepread05... > I have 2 databases, one that we use called MyShop and one that I develop on > called TestShop. > After I have a stored procedure working the way I want in TestShop , is > there a way to just copy the SP to the other database without the copy and > paste method?. Same if I have a new table. Any way to add it in without > recreating it in the MyShop database? > I am using Sql Server 2000 > > Thanks > Andy > > If you're in Query Analyzer, just change the database name from the drop-down at the top of the screen and run the script again. But hopefully you're storing your procedure code in some sort of source control system, so you can take the current version from source control, and execute it against the other database using osql.exe for example. This is easy to automate when you need to deploy to multiple target databases and/or servers. The same applies to tables and other objects, of course. Simon ...

How to create stored procedures in Ms access
Hi i'm Ashish Can anyone tell me how to create procedures in Ms Access Please do mail me at pandita4u@gmail.com "Ashish" <pandita4u@gmail.com> wrote in message news:1146123076.742991.139490@v46g2000cwv.googlegroups.com... > Hi > i'm Ashish > Can anyone tell me how to create procedures in Ms Access > Please do mail me at pandita4u@gmail.com > That's not how this group works, sorry. Ask a specific question and someone will be glad to help I'm sure. By procedures, do you mean VBA code or SQL queries or ... Posting your e-mail address will likely cause you to receive lots of junk mail too. Regards, Keith. www.keithwilby.com I don't think you can create storage procedure in access. Ashish wrote in message <1146123076.742991.139490@v46g2000cwv.googlegroups.com> : > Hi > i'm Ashish > Can anyone tell me how to create procedures in Ms Access > Please do mail me at pandita4u@gmail.com If you really mean Jet stored procedures, have a look at these http://support.microsoft.com/kb/q202116/ http://support.microsoft.com/?kbid=201493 This is for VB.Net, but I think you can can execute these SQL statements on an ADO connection. http://www.devcity.net/PrintArticle.aspx?ArticleID=18 I don't think this allows for any "Alter Proc" statements, I think you'll need to drop the SP, then recreate it, if necessary. -- Roy-Vidar ...

Need Help with a Stored Procedure Syntax
I need help in writing a stored procedure on SQL Server 2000. Basically the stored procedure's primary task is to generate invoice records and insert the records in a invoice table. In order to generate the invoice records, I have an initial table which are basically Day Records. The task that I would like to complete is to select all records from that initial table and I guess put them into a temp table. Now that i have my temp table, I would like to loop thru the table record by record, and do inserts in the invoice table. I cant seem to figure out the syntax since I am somewhat weak in ...

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 > ...

call stored procedure from ms access
I have written a stored procedure (sp) that calculates the number of seats remaining for an event. I need to pass the event id to the sp. I have a combo box that lists all the events. When I choose the event from the combo box the event executes the sp and the 'Enter Parameter Value' box appears. How can I execute this sp and pass the value to it via vba and ms access and not have the input box appear? Also, how do return the value from this sp to a form? Any help is appreciated. Thanks. You said "the enter value parameter box appears" suggesting somewhere in what you are doing there is a parameter query. If i understand your question correctly you could replace the predicate in the parameter query with a reference to the combo box that you mention. eg. in you query make the predicate forms!formname!controlname where "formname" is the name of the form you are working on and "controlname" is the name of the combo box. "kevin" <dickiex@comcast.net> wrote in message news:c4d3d03e.0409201548.71d922b7@posting.google.com... > I have written a stored procedure (sp) that calculates the number of > seats remaining for an event. I need to pass the event id to the sp. > I have a combo box that lists all the events. When I choose the event > from the combo box the event executes the sp and the 'Enter Parameter > Value' box appears. How can I execute this sp and pass the value to > it via vba and ms a...

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 ...

Web resources about - Stored Procedures Syntax - 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 10:30:21 AM