Correct syntax for an update stored procedure

This is probably a very simple question but i would appreciate some helpwith the correct syntax for andupdate stored procedure

I have created user form that allows the user toupdate thenameandaddress fields in adatatable called customers based on the input valuecustomer ID = ( datatable/Customers)customerID
I have got this far and then got lost:
Create SP_UpdateCustomer
(@.customerID, @.name, @.address)
As
Update customers ( name, address)
Where customerID = @.customerID

GO
Could anyone tell me what the correct sntax should be.
many thanks
MartinHi Martin,
You'll need to specify the data types in the create clause, and add a set clause to change the fields you want to update:
create proc sp_updatecustomer (@.customerid varchar(50), @.name varchar(50), @.address varchar(50))
as
update customers
setname=@.name,address=@.address
where customerID= @.customerID
Note - this might be a bit dangerous from a security standpoint, and you might also want to introduce some validation on the Customer ID field, to avoid anyone maliciously changing all the records by entering a customer ID of "a or 1=1"
|||

rJonas
Many thanks for your reply
I note the securtiy points you made
Thank you
martin

|||

rjonas wrote:

Note - this might be a bit dangerous from asecurity standpoint, and you might also want to introduce somevalidation on the Customer ID field, to avoid anyone maliciouslychanging all the records by entering a customer ID of "a or 1=1"

?? That is not physcially possible with the stored procedure the posteris using. The stored procedure is corerctly parameterized and thedanger you pointed out does not exist here.
Here are some articles on SQL injection and parameterized queries:

Please, please, please, learn about injection attacks!
How To: Protect From SQL Injection in ASP.NET
Using Parameterized Query in ASP.NET, Part 1
Using Parameterized Query in ASP.NET, Part 2
Using Parameterized Queries in ASP.Net

Correct procedure for concurrent transactions

Hi,
I am struggeling to get my head around how to maximize throughput to
the server using several concurrent transactions. It seems I always
stumble into massive amount of deadlocks (in my test-environment)
which, the server dutyfully resolves, but performance suffers to below
concurrent values.
To illustrate my problem, I have one transaction which inserts around
50 interdepent rows in 10 tables.
Right now, the flow is something like ( pseudo language) :
BEGIN TRANSACTION
INSERT ROW IN A
SELECT ID FROM A
INSERT INTO B ID FROM TABLE A (along with other data)
INSERT INTO C ID FROM TABLE A (along with yet other data)
GET ID FROM TABLE C
INSERT INTO TABLE D ID FROM TABLE C
INSERT INTO C ID FORM TABLE A AND ID FROM TABLE C (in other column)
INSERT ROW IN A
INSERT INTO E ID FROM TABLE A
END TRANSACTION
(etc etc)
The exact amount of work inside the transaction is variable, but
rulebased. I.e. depends on the nature of the input from the end-user.
This is all fine, when I run it single-threaded (ofcourse), but when i
run it multithreaded, deadlocks will occur with alarming rapidity. I
know it can be minimized by careful selection of indexes etc, but what
I'm really after, is a best practices way of doing it. Currently I've
just dumb-ed it down by insureing that only one thread runs the
transaction at a time, and performance is ... ok. Not great, but ok.
Someone suggested lumping all accesses so they weren't spread out, i.e.
rewriting it to
INSERT INTO A
GET ID FROM A as id1
INSERT INTO A
GET ID FROM A as id2
INSERT INTO A
GET ID FROM A as id3
INSERT INTO B id1
INSERT INTO B id2
INSERT INTO C id1
GET ID FROM C as id6
INSERT INTO C id1,id6
INSERT INTO C id3
etc
This is a fairly complex job to perform just to see if it works, so if
anybody has any input I'd appreciate it. My app has to work with
severel DMBS's so I would like to minimize the sqlserver specific
parts.
I'm not sure how much the above would give, as thread 2 would halt on
the first INSERT INTO A until thread1 called commit or rollback.
Synchronization within my app is (obviously) not going to do any good,
since the DBMS will lock the tables for me.
Currently I'm using MSSQL2000, which is in the projects
minimum-requirements.
My tests have been using TRANSACTION_ISOLATION_SERIALIZABLE
So: Any clues? Any links? Any books that would help me here?
The problem only gets worse, when I factor in read-only operations, and
other updates to some of the tables. Any word on how that is best
practiced?
Thx in advance.
A couple things.
1. How are you getting the ID back?
2. Do you have proper indexes on each of the tables for these type
operations?
3. Why are you using Serializable mode? Nothing I see in your example
warrants that. Try Read Committed instead.
Andrew J. Kelly SQL MVP
<akj@.tmnet.dk> wrote in message
news:1106156798.520698.299220@.f14g2000cwb.googlegr oups.com...
> Hi,
> I am struggeling to get my head around how to maximize throughput to
> the server using several concurrent transactions. It seems I always
> stumble into massive amount of deadlocks (in my test-environment)
> which, the server dutyfully resolves, but performance suffers to below
> concurrent values.
> To illustrate my problem, I have one transaction which inserts around
> 50 interdepent rows in 10 tables.
> Right now, the flow is something like ( pseudo language) :
> BEGIN TRANSACTION
> INSERT ROW IN A
> SELECT ID FROM A
> INSERT INTO B ID FROM TABLE A (along with other data)
> INSERT INTO C ID FROM TABLE A (along with yet other data)
> GET ID FROM TABLE C
> INSERT INTO TABLE D ID FROM TABLE C
> INSERT INTO C ID FORM TABLE A AND ID FROM TABLE C (in other column)
> INSERT ROW IN A
> INSERT INTO E ID FROM TABLE A
> END TRANSACTION
> (etc etc)
> The exact amount of work inside the transaction is variable, but
> rulebased. I.e. depends on the nature of the input from the end-user.
> This is all fine, when I run it single-threaded (ofcourse), but when i
> run it multithreaded, deadlocks will occur with alarming rapidity. I
> know it can be minimized by careful selection of indexes etc, but what
> I'm really after, is a best practices way of doing it. Currently I've
> just dumb-ed it down by insureing that only one thread runs the
> transaction at a time, and performance is ... ok. Not great, but ok.
> Someone suggested lumping all accesses so they weren't spread out, i.e.
> rewriting it to
> INSERT INTO A
> GET ID FROM A as id1
> INSERT INTO A
> GET ID FROM A as id2
> INSERT INTO A
> GET ID FROM A as id3
> INSERT INTO B id1
> INSERT INTO B id2
> INSERT INTO C id1
> GET ID FROM C as id6
> INSERT INTO C id1,id6
> INSERT INTO C id3
> etc
> This is a fairly complex job to perform just to see if it works, so if
> anybody has any input I'd appreciate it. My app has to work with
> severel DMBS's so I would like to minimize the sqlserver specific
> parts.
> I'm not sure how much the above would give, as thread 2 would halt on
> the first INSERT INTO A until thread1 called commit or rollback.
>
> Synchronization within my app is (obviously) not going to do any good,
> since the DBMS will lock the tables for me.
> Currently I'm using MSSQL2000, which is in the projects
> minimum-requirements.
> My tests have been using TRANSACTION_ISOLATION_SERIALIZABLE
> So: Any clues? Any links? Any books that would help me here?
> The problem only gets worse, when I factor in read-only operations, and
> other updates to some of the tables. Any word on how that is best
> practiced?
> Thx in advance.
>
|||1) The table has an autoincrementing column, so I insert into it, and
read back the max value. For this, I need to be in serializable mode.
2) Yes; everything is indexed correctly, and constrained with foreign
keys.
3) Since read-committed would return a different max() for the ID I
think I need to be in serializable mode, right?
I know I could use some sql-server specific way of retrieving the IDs,
but that wouldnt alleiviate the locking occuring on the first ID
tables, that are held, until my transaction commits or rolls back.
|||You will never get good performance in generating the id's that way. For
one you are essentially making the application single user with the over use
of the Serialization Isolation level. As such you force the app to do things
one at a time and all the others must wait until the ones before them are
done. Either use an IDENTITY() datatype or generate your own ID's with
something like this:
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) NOT NULL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
That will allow you to generate ids for different tables at the same time
and there is no need for increasing the isolation level. YOu can generate
all the ID's for each of the tables before you Begin the transaction and
then simply do the inserts.
Andrew J. Kelly SQL MVP
<akj@.tmnet.dk> wrote in message
news:1106221353.508792.46620@.c13g2000cwb.googlegro ups.com...
> 1) The table has an autoincrementing column, so I insert into it, and
> read back the max value. For this, I need to be in serializable mode.
> 2) Yes; everything is indexed correctly, and constrained with foreign
> keys.
> 3) Since read-committed would return a different max() for the ID I
> think I need to be in serializable mode, right?
> I know I could use some sql-server specific way of retrieving the IDs,
> but that wouldnt alleiviate the locking occuring on the first ID
> tables, that are held, until my transaction commits or rolls back.
>

Correct procedure for concurrent transactions

Hi,
I am struggeling to get my head around how to maximize throughput to
the server using several concurrent transactions. It seems I always
stumble into massive amount of deadlocks (in my test-environment)
which, the server dutyfully resolves, but performance suffers to below
concurrent values.
To illustrate my problem, I have one transaction which inserts around
50 interdepent rows in 10 tables.
Right now, the flow is something like ( pseudo language) :
BEGIN TRANSACTION
INSERT ROW IN A
SELECT ID FROM A
INSERT INTO B ID FROM TABLE A (along with other data)
INSERT INTO C ID FROM TABLE A (along with yet other data)
GET ID FROM TABLE C
INSERT INTO TABLE D ID FROM TABLE C
INSERT INTO C ID FORM TABLE A AND ID FROM TABLE C (in other column)
INSERT ROW IN A
INSERT INTO E ID FROM TABLE A
END TRANSACTION
(etc etc)
The exact amount of work inside the transaction is variable, but
rulebased. I.e. depends on the nature of the input from the end-user.
This is all fine, when I run it single-threaded (ofcourse), but when i
run it multithreaded, deadlocks will occur with alarming rapidity. I
know it can be minimized by careful selection of indexes etc, but what
I'm really after, is a best practices way of doing it. Currently I've
just dumb-ed it down by insureing that only one thread runs the
transaction at a time, and performance is ... ok. Not great, but ok.
Someone suggested lumping all accesses so they weren't spread out, i.e.
rewriting it to
INSERT INTO A
GET ID FROM A as id1
INSERT INTO A
GET ID FROM A as id2
INSERT INTO A
GET ID FROM A as id3
INSERT INTO B id1
INSERT INTO B id2
INSERT INTO C id1
GET ID FROM C as id6
INSERT INTO C id1,id6
INSERT INTO C id3
etc
This is a fairly complex job to perform just to see if it works, so if
anybody has any input I'd appreciate it. My app has to work with
severel DMBS's so I would like to minimize the sqlserver specific
parts.
I'm not sure how much the above would give, as thread 2 would halt on
the first INSERT INTO A until thread1 called commit or rollback.
Synchronization within my app is (obviously) not going to do any good,
since the DBMS will lock the tables for me.
Currently I'm using MSSQL2000, which is in the projects
minimum-requirements.
My tests have been using TRANSACTION_ISOLATION_SERIALIZABLE
So: Any clues? Any links? Any books that would help me here?
The problem only gets worse, when I factor in read-only operations, and
other updates to some of the tables. Any word on how that is best
practiced?
Thx in advance.A couple things.
1. How are you getting the ID back?
2. Do you have proper indexes on each of the tables for these type
operations?
3. Why are you using Serializable mode? Nothing I see in your example
warrants that. Try Read Committed instead.
Andrew J. Kelly SQL MVP
<akj@.tmnet.dk> wrote in message
news:1106156798.520698.299220@.f14g2000cwb.googlegroups.com...
> Hi,
> I am struggeling to get my head around how to maximize throughput to
> the server using several concurrent transactions. It seems I always
> stumble into massive amount of deadlocks (in my test-environment)
> which, the server dutyfully resolves, but performance suffers to below
> concurrent values.
> To illustrate my problem, I have one transaction which inserts around
> 50 interdepent rows in 10 tables.
> Right now, the flow is something like ( pseudo language) :
> BEGIN TRANSACTION
> INSERT ROW IN A
> SELECT ID FROM A
> INSERT INTO B ID FROM TABLE A (along with other data)
> INSERT INTO C ID FROM TABLE A (along with yet other data)
> GET ID FROM TABLE C
> INSERT INTO TABLE D ID FROM TABLE C
> INSERT INTO C ID FORM TABLE A AND ID FROM TABLE C (in other column)
> INSERT ROW IN A
> INSERT INTO E ID FROM TABLE A
> END TRANSACTION
> (etc etc)
> The exact amount of work inside the transaction is variable, but
> rulebased. I.e. depends on the nature of the input from the end-user.
> This is all fine, when I run it single-threaded (ofcourse), but when i
> run it multithreaded, deadlocks will occur with alarming rapidity. I
> know it can be minimized by careful selection of indexes etc, but what
> I'm really after, is a best practices way of doing it. Currently I've
> just dumb-ed it down by insureing that only one thread runs the
> transaction at a time, and performance is ... ok. Not great, but ok.
> Someone suggested lumping all accesses so they weren't spread out, i.e.
> rewriting it to
> INSERT INTO A
> GET ID FROM A as id1
> INSERT INTO A
> GET ID FROM A as id2
> INSERT INTO A
> GET ID FROM A as id3
> INSERT INTO B id1
> INSERT INTO B id2
> INSERT INTO C id1
> GET ID FROM C as id6
> INSERT INTO C id1,id6
> INSERT INTO C id3
> etc
> This is a fairly complex job to perform just to see if it works, so if
> anybody has any input I'd appreciate it. My app has to work with
> severel DMBS's so I would like to minimize the sqlserver specific
> parts.
> I'm not sure how much the above would give, as thread 2 would halt on
> the first INSERT INTO A until thread1 called commit or rollback.
>
> Synchronization within my app is (obviously) not going to do any good,
> since the DBMS will lock the tables for me.
> Currently I'm using MSSQL2000, which is in the projects
> minimum-requirements.
> My tests have been using TRANSACTION_ISOLATION_SERIALIZABLE
> So: Any clues? Any links? Any books that would help me here?
> The problem only gets worse, when I factor in read-only operations, and
> other updates to some of the tables. Any word on how that is best
> practiced?
> Thx in advance.
>|||1) The table has an autoincrementing column, so I insert into it, and
read back the max value. For this, I need to be in serializable mode.
2) Yes; everything is indexed correctly, and constrained with foreign
keys.
3) Since read-committed would return a different max() for the ID I
think I need to be in serializable mode, right?
I know I could use some sql-server specific way of retrieving the IDs,
but that wouldnt alleiviate the locking occuring on the first ID
tables, that are held, until my transaction commits or rolls back.|||You will never get good performance in generating the id's that way. For
one you are essentially making the application single user with the over use
of the Serialization Isolation level. As such you force the app to do things
one at a time and all the others must wait until the ones before them are
done. Either use an IDENTITY() datatype or generate your own ID's with
something like this:
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) NOT NULL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
That will allow you to generate ids for different tables at the same time
and there is no need for increasing the isolation level. YOu can generate
all the ID's for each of the tables before you Begin the transaction and
then simply do the inserts.
Andrew J. Kelly SQL MVP
<akj@.tmnet.dk> wrote in message
news:1106221353.508792.46620@.c13g2000cwb.googlegroups.com...
> 1) The table has an autoincrementing column, so I insert into it, and
> read back the max value. For this, I need to be in serializable mode.
> 2) Yes; everything is indexed correctly, and constrained with foreign
> keys.
> 3) Since read-committed would return a different max() for the ID I
> think I need to be in serializable mode, right?
> I know I could use some sql-server specific way of retrieving the IDs,
> but that wouldnt alleiviate the locking occuring on the first ID
> tables, that are held, until my transaction commits or rolls back.
>

Correct procedure for concurrent transactions

Hi,
I am struggeling to get my head around how to maximize throughput to
the server using several concurrent transactions. It seems I always
stumble into massive amount of deadlocks (in my test-environment)
which, the server dutyfully resolves, but performance suffers to below
concurrent values.
To illustrate my problem, I have one transaction which inserts around
50 interdepent rows in 10 tables.
Right now, the flow is something like ( pseudo language) :
BEGIN TRANSACTION
INSERT ROW IN A
SELECT ID FROM A
INSERT INTO B ID FROM TABLE A (along with other data)
INSERT INTO C ID FROM TABLE A (along with yet other data)
GET ID FROM TABLE C
INSERT INTO TABLE D ID FROM TABLE C
INSERT INTO C ID FORM TABLE A AND ID FROM TABLE C (in other column)
INSERT ROW IN A
INSERT INTO E ID FROM TABLE A
END TRANSACTION
(etc etc)
The exact amount of work inside the transaction is variable, but
rulebased. I.e. depends on the nature of the input from the end-user.
This is all fine, when I run it single-threaded (ofcourse), but when i
run it multithreaded, deadlocks will occur with alarming rapidity. I
know it can be minimized by careful selection of indexes etc, but what
I'm really after, is a best practices way of doing it. Currently I've
just dumb-ed it down by insureing that only one thread runs the
transaction at a time, and performance is ... ok. Not great, but ok.
Someone suggested lumping all accesses so they weren't spread out, i.e.
rewriting it to
INSERT INTO A
GET ID FROM A as id1
INSERT INTO A
GET ID FROM A as id2
INSERT INTO A
GET ID FROM A as id3
INSERT INTO B id1
INSERT INTO B id2
INSERT INTO C id1
GET ID FROM C as id6
INSERT INTO C id1,id6
INSERT INTO C id3
etc
This is a fairly complex job to perform just to see if it works, so if
anybody has any input I'd appreciate it. My app has to work with
severel DMBS's so I would like to minimize the sqlserver specific
parts.
I'm not sure how much the above would give, as thread 2 would halt on
the first INSERT INTO A until thread1 called commit or rollback.
Synchronization within my app is (obviously) not going to do any good,
since the DBMS will lock the tables for me.
Currently I'm using MSSQL2000, which is in the projects
minimum-requirements.
My tests have been using TRANSACTION_ISOLATION_SERIALIZABLE
So: Any clues? Any links? Any books that would help me here?
The problem only gets worse, when I factor in read-only operations, and
other updates to some of the tables. Any word on how that is best
practiced?
Thx in advance.A couple things.
1. How are you getting the ID back?
2. Do you have proper indexes on each of the tables for these type
operations?
3. Why are you using Serializable mode? Nothing I see in your example
warrants that. Try Read Committed instead.
--
Andrew J. Kelly SQL MVP
<akj@.tmnet.dk> wrote in message
news:1106156798.520698.299220@.f14g2000cwb.googlegroups.com...
> Hi,
> I am struggeling to get my head around how to maximize throughput to
> the server using several concurrent transactions. It seems I always
> stumble into massive amount of deadlocks (in my test-environment)
> which, the server dutyfully resolves, but performance suffers to below
> concurrent values.
> To illustrate my problem, I have one transaction which inserts around
> 50 interdepent rows in 10 tables.
> Right now, the flow is something like ( pseudo language) :
> BEGIN TRANSACTION
> INSERT ROW IN A
> SELECT ID FROM A
> INSERT INTO B ID FROM TABLE A (along with other data)
> INSERT INTO C ID FROM TABLE A (along with yet other data)
> GET ID FROM TABLE C
> INSERT INTO TABLE D ID FROM TABLE C
> INSERT INTO C ID FORM TABLE A AND ID FROM TABLE C (in other column)
> INSERT ROW IN A
> INSERT INTO E ID FROM TABLE A
> END TRANSACTION
> (etc etc)
> The exact amount of work inside the transaction is variable, but
> rulebased. I.e. depends on the nature of the input from the end-user.
> This is all fine, when I run it single-threaded (ofcourse), but when i
> run it multithreaded, deadlocks will occur with alarming rapidity. I
> know it can be minimized by careful selection of indexes etc, but what
> I'm really after, is a best practices way of doing it. Currently I've
> just dumb-ed it down by insureing that only one thread runs the
> transaction at a time, and performance is ... ok. Not great, but ok.
> Someone suggested lumping all accesses so they weren't spread out, i.e.
> rewriting it to
> INSERT INTO A
> GET ID FROM A as id1
> INSERT INTO A
> GET ID FROM A as id2
> INSERT INTO A
> GET ID FROM A as id3
> INSERT INTO B id1
> INSERT INTO B id2
> INSERT INTO C id1
> GET ID FROM C as id6
> INSERT INTO C id1,id6
> INSERT INTO C id3
> etc
> This is a fairly complex job to perform just to see if it works, so if
> anybody has any input I'd appreciate it. My app has to work with
> severel DMBS's so I would like to minimize the sqlserver specific
> parts.
> I'm not sure how much the above would give, as thread 2 would halt on
> the first INSERT INTO A until thread1 called commit or rollback.
>
> Synchronization within my app is (obviously) not going to do any good,
> since the DBMS will lock the tables for me.
> Currently I'm using MSSQL2000, which is in the projects
> minimum-requirements.
> My tests have been using TRANSACTION_ISOLATION_SERIALIZABLE
> So: Any clues? Any links? Any books that would help me here?
> The problem only gets worse, when I factor in read-only operations, and
> other updates to some of the tables. Any word on how that is best
> practiced?
> Thx in advance.
>|||1) The table has an autoincrementing column, so I insert into it, and
read back the max value. For this, I need to be in serializable mode.
2) Yes; everything is indexed correctly, and constrained with foreign
keys.
3) Since read-committed would return a different max() for the ID I
think I need to be in serializable mode, right?
I know I could use some sql-server specific way of retrieving the IDs,
but that wouldnt alleiviate the locking occuring on the first ID
tables, that are held, until my transaction commits or rolls back.|||You will never get good performance in generating the id's that way. For
one you are essentially making the application single user with the over use
of the Serialization Isolation level. As such you force the app to do things
one at a time and all the others must wait until the ones before them are
done. Either use an IDENTITY() datatype or generate your own ID's with
something like this:
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) NOT NULL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
That will allow you to generate ids for different tables at the same time
and there is no need for increasing the isolation level. YOu can generate
all the ID's for each of the tables before you Begin the transaction and
then simply do the inserts.
--
Andrew J. Kelly SQL MVP
<akj@.tmnet.dk> wrote in message
news:1106221353.508792.46620@.c13g2000cwb.googlegroups.com...
> 1) The table has an autoincrementing column, so I insert into it, and
> read back the max value. For this, I need to be in serializable mode.
> 2) Yes; everything is indexed correctly, and constrained with foreign
> keys.
> 3) Since read-committed would return a different max() for the ID I
> think I need to be in serializable mode, right?
> I know I could use some sql-server specific way of retrieving the IDs,
> but that wouldnt alleiviate the locking occuring on the first ID
> tables, that are held, until my transaction commits or rolls back.
>

Correct format for set arithabort on

I am calling a stored procedure with the following syntax:

 Dim MyCommand1 As New SqlCommand("addprospcus", MyConnection)
 MyCommand1.CommandText = "set arithabort on"
 MyCommand1.CommandType = CommandType.StoredProcedure
 MyCommand1.Parameters.Add(New SqlParameter("@.Namecust", SqlDbType.NVarChar, 60))
 MyCommand1.Parameters("@.namecust").Value = txtProspName.Text.ToString
 MyCommand1.Parameters.Add(New SqlParameter("@.codeterr", SqlDbType.NVarChar, 6))
 MyCommand1.Parameters("@.codeterr").Value = Trim(TerritoryList1.SelectedItem.Value.ToString)
 MyConnection.Open()
 MyCommand1.ExecuteNonQuery()
 MyConnection.Close()

This is incorrect, but I can not find the correct syntax for calling my stored procedure but first setting "arithabort on".

Thanks in advance for your assistance.why wouldnt you set it inside the stored proc itself ?|||That does not work - I recieve the following error, "INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'". There is alot of information about this and some of the advice states that the "set arithabort on" needs to be set before executing the stored procedure.

Currently I am getting around this error by using a nested sproc I call a sproc from my asp.net web form this stored procedure sets arithabort on and then executes a second sproc that actually does the insert.

the internal sproc runs correctly from Sql query analyser and when executed from another sproc as stated above, but not from my webform. Several articles have stated that the "set arithabort on" needs to be set from the application such as microsoft article ID: 305333

MyConnection.Execute "SET ARITHABORT ON"

but I could not get this to work.|||when you create the stored proc

SET ARITHABORT ON
GO
ALTER PROCEDURE yourproc ...
-- your stored proc code
SET ARITHABORT OFF
GO

hth|||I trie this procedure but it does not help. I still get the error. The only way, so far, that I have been able to use my stored procedure from my web application is to use a nested Sproc.

I did based on your advice try to change my original sproc using the procedure in your post. I alter the sproc and it will execute and insert records from query analyzer but not from my application. But if I nest my sproc with the calling sproc being executed from my application and the calling sproc set arithabort on, executes my nested sproc, and then set arithabort off; my records are correctly inserted into my table.

Correct backup procedure

Hi all, I just havea general question I hope you guys could answer.
What is the best way to both backup my Report Development (i.e. visual
studio report project) and also my deployment on the Report Server?
Thanks for any advice.
Kind regards
TazBackup Report Project:
Do file backup (NTBackup Utility) of the folder containing both project
(rptproj), solution (sln), data source (rds) and report definition (rdl)
files.
Or use Visual Source Safe or similar products to have a centralized code
base, and backup that according to best practices for that product.
Backup Report Server:
Do file backup on the config files for Report Manager and Report Server
Do backup of IIS metadata for the virtual directories
Extract the encryption key and save this, using RSKeyMgmt.exe
These two only needs to be done when making changes to the Report Server,
which shouldn't be too often.
Then do SQL backup of the ReportServer database once a day.
Kaisa M. Lindahl Lervik
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:OpTQd092GHA.4924@.TK2MSFTNGP05.phx.gbl...
> Hi all, I just havea general question I hope you guys could answer.
> What is the best way to both backup my Report Development (i.e. visual
> studio report project) and also my deployment on the Report Server?
> Thanks for any advice.
> Kind regards
> Taz
>|||Fantastic,
excellent post.
Taz
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
news:%23KpG4f%232GHA.476@.TK2MSFTNGP06.phx.gbl...
> Backup Report Project:
> Do file backup (NTBackup Utility) of the folder containing both project
> (rptproj), solution (sln), data source (rds) and report definition (rdl)
> files.
> Or use Visual Source Safe or similar products to have a centralized code
> base, and backup that according to best practices for that product.
> Backup Report Server:
> Do file backup on the config files for Report Manager and Report Server
> Do backup of IIS metadata for the virtual directories
> Extract the encryption key and save this, using RSKeyMgmt.exe
> These two only needs to be done when making changes to the Report Server,
> which shouldn't be too often.
> Then do SQL backup of the ReportServer database once a day.
> Kaisa M. Lindahl Lervik
>
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:OpTQd092GHA.4924@.TK2MSFTNGP05.phx.gbl...
>> Hi all, I just havea general question I hope you guys could answer.
>> What is the best way to both backup my Report Development (i.e. visual
>> studio report project) and also my deployment on the Report Server?
>> Thanks for any advice.
>> Kind regards
>> Taz
>

Copying SQL Stored Procedures (SQL Server 2000)

Hi folks
I am writing a humdinger of a stored procedure, which I can use to
automatically create a second copy of a database, and ensure that the
tables, etc, are all of the same specification.
Here's what I've done so far:
1. Check to see if the second copy of the database exists. If it doesn't
exist, create it.
2. Build up a dynamic SQL string, by using the contents of the sysObjects
and sysColumns tables in the first database.
3. Build up a dynamic SQL string, by using the contents of the sysObjects
and sysColumns tables in the second database.
4. Compare the two SQL strings to ensure that each table in the second
database is an exact replica of the tables in the first database.
By the end of all of this, the result is that the second database contains
exactly the same tables as the first database, with both sets of tables
being identical. The only exception is that the second database doesn't
have any relationships set up between the tables, although that's to come.
So far, so good. However, when I turned my attention to the stored
procedures in the first database, it all went a bit wrong. I can use the
sysObjects and sysComments tables to build up the dynamic SQL from the first
database that would have to be executed against the second database.
However, I've discovered that it isn't possible to create a stored procedure
in any database other than the one you are currently working with. If I
append "Uses <databasename>" at the beginning of the dynamic SQL string, it
then complains that the "CREATE PROCEDURE" command should be the first
command in any batch process.
Can anyone tell me if it's possible for me to do this?
Incidentally, before anyone suggests it, I've never done any DTS stuff
before, so I'm hoping there are other ways of doing it.
TIA
UK_CodemonkeyHave you thought of backing up your database and
restoring it with a new name?|||Ian
DECLARE @.dbname AS VARCHAR(100),@.sql AS VARCHAR(100)
SET @.dbname ='pubs'
SET @.sql ='
CREATE PROCEDURE dbo.nameofSP
AS
SELECT * FROM '+@.dbname+'.dbo.Authors'
EXEC (@.sql)
EXEC dbo.nameofSP
Note: Learn using DTS Packages
"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:drahq7$b7r$1$8300dec7@.news.demon.co.uk...
> Hi folks
> I am writing a humdinger of a stored procedure, which I can use to
> automatically create a second copy of a database, and ensure that the
> tables, etc, are all of the same specification.
> Here's what I've done so far:
> 1. Check to see if the second copy of the database exists. If it doesn't
> exist, create it.
> 2. Build up a dynamic SQL string, by using the contents of the sysObjects
> and sysColumns tables in the first database.
> 3. Build up a dynamic SQL string, by using the contents of the sysObjects
> and sysColumns tables in the second database.
> 4. Compare the two SQL strings to ensure that each table in the second
> database is an exact replica of the tables in the first database.
> By the end of all of this, the result is that the second database contains
> exactly the same tables as the first database, with both sets of tables
> being identical. The only exception is that the second database doesn't
> have any relationships set up between the tables, although that's to come.
> So far, so good. However, when I turned my attention to the stored
> procedures in the first database, it all went a bit wrong. I can use the
> sysObjects and sysComments tables to build up the dynamic SQL from the
> first database that would have to be executed against the second database.
> However, I've discovered that it isn't possible to create a stored
> procedure in any database other than the one you are currently working
> with. If I append "Uses <databasename>" at the beginning of the dynamic
> SQL string, it then complains that the "CREATE PROCEDURE" command should
> be the first command in any batch process.
> Can anyone tell me if it's possible for me to do this?
> Incidentally, before anyone suggests it, I've never done any DTS stuff
> before, so I'm hoping there are other ways of doing it.
> TIA
>
> UK_Codemonkey
>|||Uri
I'll give this a bash. Incidentally, I've been administering SQL Server for
the past 4 1/2 years, and have never needed to get into DTS, mainly because
I've been able to do everything through SQL Stored Procedures.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23x2PuxnIGHA.140@.TK2MSFTNGP12.phx.gbl...
> Ian
> DECLARE @.dbname AS VARCHAR(100),@.sql AS VARCHAR(100)
> SET @.dbname ='pubs'
> SET @.sql ='
> CREATE PROCEDURE dbo.nameofSP
> AS
> SELECT * FROM '+@.dbname+'.dbo.Authors'
> EXEC (@.sql)
> EXEC dbo.nameofSP
>
> Note: Learn using DTS Packages
>
>
> "Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
> news:drahq7$b7r$1$8300dec7@.news.demon.co.uk...
>

copying a row to new table - not copying one date column

I have two tables, a PendingOrder table and a CompletedOrder table. I have a stored procedure that moves an order from the pending table to the completed table, and I am having an issue with copying over a date field (the date the order was created). Here is what I have....

The store procedure takes in the following:
@.PendOrderKey INT,
@.Status INT

Here is my insert statement:

INSERT INTO CustOrder (CustAddrKey, UserKey, ContactName, ContactPhone, CustPONo, VendLocalSupplierKey, Notes, Status, OrderTypeKey, CreatedDate, ModifiedDate)
SELECT CustAddrKey, UserKey, ContactName, ContactPhone, CustPONo, VendLocalSupplierKey, Notes, @.Status, OrderTypeKey, CreatedDate, GETDATE()
FROM PendCustOrder
WHERE PendOrderKey = @.PendOrderKey

For some reason, when I run this on an order, instead of taking the value of "CreatedDate" from the pending order table and inserting it into the CreatedDate field of the completed order table, it inserts the current date and time into the completed order table. The "CreatedDate" field in the completed orders table does not allow nulls and has no default value, so I'm confused as to why it's inserting the current date/time. Any help would be greatly appreciated. Thanks!!

Maguidhir:

Check and see if the target table has a trigger that sets the "CreatedDate" field when a new record is inserted into the table.

Dave

|||Thank you so much! It does have a trigger that sets the CreatedDate=GetDate()!

CopyFromRecordSet In Excel 2003

I want to use the CopyFromRecordSet however I am using a stored procedure that returns a recordset. Here is the code, but the recordset never opens. I have commented out the Execute

Here is a code snip, any Ideas? TIA Mike

Dim Con1 As New ADODB.Connection
Dim Cmd1 As New ADODB.Command
Dim Rs As New ADODB.Recordset

Con1.Open

Cmd1.ActiveConnection = Con1
Cmd1.CommandType = adCmdText
Cmd1.CommandText = "MyStroredProc"
Cmd1.Execute
Rs.Open Cmd1

Worksheets("T1").Range("B1").CopyFromRecordset Rs

I believe it should be like

Con1.Open

Set Cmd1.ActiveConnection = Con1
Cmd1.CommandType = adCmdStoredProc ' Please verify spelling for adCmdStoredProc, since I do not have documentation with me
Cmd1.CommandText = "MyStroredProc"
Set Rs = Cmd1.Execute

|||

VMazur,

Thank you for the code snip however when I query the recordset state, it is still closed or 0 and I generate an error. I did use 4 as the commandType

Any other thoughts would be appreciated.

'Open the connection
Con1.Open

Set Cmd1.ActiveConnection = Con1
Cmd1.CommandType = 4 '' Stored Proc
Cmd1.CommandText = "REPORT_procASDCStatus"
Set Rs = Cmd1.Execute

'Rs.Open Cmd1 '' Tried this also
Worksheets("T1").Range("A1").CopyFromRecordset Rs <<Fails with message that rs is not open

MikeD

|||What happens when you call Execute method? Does it generate any error or not?|||

The issue was Set NO COUNT ON in the SP. This was missing in the SP.

As soon as I added this, the recordset opened and I could do copy from.

Thank you Mazur

|||

Hi Mazur,

There was no error message, only issue was there was no data transfer. As soon as we set NOCOuNT ON, data started transfering.

This question is resolved.

Thank you

Mike

CopyFromRecordSet In Excel 2003

I want to use the CopyFromRecordSet however I am using a stored procedure that returns a recordset. Here is the code, but the recordset never opens. I have commented out the Execute

Here is a code snip, any Ideas? TIA Mike

Dim Con1 As New ADODB.Connection
Dim Cmd1 As New ADODB.Command
Dim Rs As New ADODB.Recordset

Con1.Open

Cmd1.ActiveConnection = Con1
Cmd1.CommandType = adCmdText
Cmd1.CommandText = "MyStroredProc"
Cmd1.Execute
Rs.Open Cmd1

Worksheets("T1").Range("B1").CopyFromRecordset Rs

I believe it should be like

Con1.Open

Set Cmd1.ActiveConnection = Con1
Cmd1.CommandType = adCmdStoredProc ' Please verify spelling for adCmdStoredProc, since I do not have documentation with me
Cmd1.CommandText = "MyStroredProc"
Set Rs = Cmd1.Execute

|||

VMazur,

Thank you for the code snip however when I query the recordset state, it is still closed or 0 and I generate an error. I did use 4 as the commandType

Any other thoughts would be appreciated.

'Open the connection
Con1.Open

Set Cmd1.ActiveConnection = Con1
Cmd1.CommandType = 4 '' Stored Proc
Cmd1.CommandText = "REPORT_procASDCStatus"
Set Rs = Cmd1.Execute

'Rs.Open Cmd1 '' Tried this also
Worksheets("T1").Range("A1").CopyFromRecordset Rs <<Fails with message that rs is not open

MikeD

|||What happens when you call Execute method? Does it generate any error or not?|||

The issue was Set NO COUNT ON in the SP. This was missing in the SP.

As soon as I added this, the recordset opened and I could do copy from.

Thank you Mazur

|||

Hi Mazur,

There was no error message, only issue was there was no data transfer. As soon as we set NOCOuNT ON, data started transfering.

This question is resolved.

Thank you

Mike

Copy XML File to SQL Server Table.

A need, to take a XML File and copy the contents into SQL Server 2000 Table,
from a Store Procedure.
Necesito, tomar un archivo XML y copiar el contenido de este a una Tabla de
SQL SERVER 2000, desde un Procedimiento Almacenado.Javier,
Take a look at OPENXML in the BOL. I have provided you with a code sample
below. Just remember to use sp_xml_preparedocument and sp_xml_removedocument
correctly or you will encounter a memory leak.
Good Luck.
--
DECLARE @.idoc INT
EXEC sp_xml_preparedocument @.idoc OUTPUT, '<Person ID="5"
FirstName="John" LastName="Doe">
<Sales QtyPurchased="2"/>
<Sales QtyPurchased="3"/>
</Person>'
select * from openxml(@.idoc, '*')
EXEC sp_xml_removedocument @.idoc|||Thank you, very much Brian, but it not is the I like me. I need that in place
of I write '<Person ID="5"
> FirstName="John" LastName="Doe">
> <Sales QtyPurchased="2"/>
> <Sales QtyPurchased="3"/>
> </Person>', I will can writing the File.xml name.
Muchas Gracias Brian, pero no es lo que buscaba. Yo necesito que en lugar de
escribir <Person ID="5"
FirstName="John" LastName="Doe">
<Sales QtyPurchased="2"/>
<Sales QtyPurchased="3"/>
</Person>, yo pudiera escribir el nombre del archivo.xml.
Javier.|||Sorry Javier, but you will have to open the file with an application and then
pass it to your stored procedure. If it must be contained within SQL Server
you could create a DTS to parse the xml file and then import it into your
table.
I hope that this helps!

Copy XML File to SQL Server Table.

A need, to take a XML File and copy the contents into SQL Server 2000 Table,
from a Store Procedure.
Necesito, tomar un archivo XML y copiar el contenido de este a una Tabla de
SQL SERVER 2000, desde un Procedimiento Almacenado.
Javier,
Take a look at OPENXML in the BOL. I have provided you with a code sample
below. Just remember to use sp_xml_preparedocument and sp_xml_removedocument
correctly or you will encounter a memory leak.
Good Luck.
DECLARE @.idoc INT
EXEC sp_xml_preparedocument @.idoc OUTPUT, '<Person ID="5"
FirstName="John" LastName="Doe">
<Sales QtyPurchased="2"/>
<Sales QtyPurchased="3"/>
</Person>'
select * from openxml(@.idoc, '*')
EXEC sp_xml_removedocument @.idoc
|||Thank you, very much Brian, but it not is the I like me. I need that in place
of I write '<Person ID="5"
> FirstName="John" LastName="Doe">
> <Sales QtyPurchased="2"/>
> <Sales QtyPurchased="3"/>
> </Person>', I will can writing the File.xml name.
Muchas Gracias Brian, pero no es lo que buscaba. Yo necesito que en lugar de
escribir <Person ID="5"
FirstName="John" LastName="Doe">
<Sales QtyPurchased="2"/>
<Sales QtyPurchased="3"/>
</Person>, yo pudiera escribir el nombre del archivo.xml.
Javier.
|||Sorry Javier, but you will have to open the file with an application and then
pass it to your stored procedure. If it must be contained within SQL Server
you could create a DTS to parse the xml file and then import it into your
table.
I hope that this helps!

Copy without Locks

I have a stored procedure which copies data from a view into a
temporary table (x2) and then from the temporary table into a table
which the users use. It takes 1 minute to get the data into the temp
table and seconds to update into the final one (hence the two stages).

When I do the initial copy from the view, it locks the various tables
used in the view and potentially blocks the users. It's a complex view
and uses plenty of other tables. We get massive performance issues
'generating' the data into a table as opposed to the view.

What I want to do is take all the data without locking it. I don't
want to modify the data, just read it and stick the data into a table.

Thanks

Ryan

SQL as follows :

/*Drop into temp tables first and then proper ones later as this
works out a lot less time when no data will be available*/

TRUNCATE TABLE MISGENERATE.dbo.CBFA_MISDATATemp -- Temp Table
TRUNCATE TABLE MISGENERATE.dbo.CBFA_MISPIPDATATemp -- Temp Table

INSERT INTO MISGENERATE.dbo.CBFA_MISDATATemp
SELECT * FROM MIS.dbo.CBFA_MISDATA -- View

INSERT INTO MISGENERATE.dbo.CBFA_MISPIPDATATemp
SELECT * FROM MIS.dbo.CBFA_MISPIPDATA -- View

/*Now drop this into full MIS tables for speed*/

TRUNCATE TABLE MISGENERATE.dbo.CBFA_MISDATA
TRUNCATE TABLE MISGENERATE.dbo.CBFA_MISPIPDATA

INSERT INTO MISGENERATE.dbo.CBFA_MISDATA -- Final Table
SELECT * FROM MISGENERATE.dbo.CBFA_MISDATATemp

INSERT INTO MISGENERATE.dbo.CBFA_MISPIPDATA -- Final Table
SELECT * FROM MISGENERATE.dbo.CBFA_MISPIPDATATempRyan (ryanofford@.hotmail.com) writes:
> What I want to do is take all the data without locking it. I don't
> want to modify the data, just read it and stick the data into a table.

You can say things like:

SELECT * FROM tbl WITH (NOLOCK)

although, I am uncertain how this works with a view.

You should be very careful with NOLOCK. Using NOLOCK may save you from
users screaming because they are blocked, but since you are reading
uncommitted data, you may produce incorrect or incoherent results. The
users may not scream about this - they will just make incorrect decisions
because of bad input.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

copy tuples from table tsql?

Hi all,
I'm trying to write a TSQL procedure that takes as an argument a
tablename in a database and then it copies the first 100 tuples of the
given table in the same table (it actually duplicates them). The table
doesn't have a standard schema and the query should be able to deal with
different kinds of schemas.
Any help on this?
Thanks in advance,
PeterWhere to start! Why would you ever want to copy a row* in the same table?
That's undesirable and unnecessary in SQL. Every table should have a key
that prevents duplicate rows. Maybe you mean you want to copy rows to
another table? But then you go on to say you want to copy the "first 100
tuples". Tables in SQL have no inherent logical order. What do you mean by
"first 100"?
Finally, parameterizing table names is generally a very bad idea in a
production system. Sometimes DBAs need to do that for admin tasks and the
way to do it is to use dynamic SQL. The following article has the gory
details and all the caveats that go with dynamic code:
http://www.sommarskog.se/dynamic_sql.html
I do recommend you study some relational database theory and practice and
then reconsider your requirements.
[ * In relational theory tuples by definition are not duplicated in a
relation. Rows are not tuples and your use of that term is incorrect. ]
David Portas
SQL Server MVP
--
"pnp" <pnpNOSPAM@.softlab.ntua.gr> wrote in message
news:OcHFgFUxFHA.2232@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I'm trying to write a TSQL procedure that takes as an argument a tablename
> in a database and then it copies the first 100 tuples of the given table
> in the same table (it actually duplicates them). The table doesn't have a
> standard schema and the query should be able to deal with different kinds
> of schemas.
> Any help on this?
> Thanks in advance,
> Peter

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

Copy stored procedure?

I copied a database using export wizard, but that doesnt copy the stored procedures in the database. I know that dts needs to be used to copy stored procedures, but can someone give me a bit more explanation on how this is to be done? Any help is greatly appreciated.

Try the link below for two easy ways to do it. Hope this helps.

http://www.dotnetspider.com/qa/Question13042.aspx

copy stored procedure from dbase 1 to dbase2

Hi just wondering if there is an easy way to copy stored procedures from one
dbase to another on the same server, possibly using query analyzer, or vs.net
server explorer? There are around 100 procedures so hoping do not have to do
each one individually. Thanks.
Paul G
Software engineer.
You could use Enterprise Manager to script all stored procedures to a file.
You could then execute that file within your other database.
If you have your stored procedures stored within a version control system
you could simply grab the most recent files and execute them within the
appropriate database. Source control is nice because you can look back at
the changes of the stored procedure. You can see what changed, who changed
it, and why. You can also roll back to a previous version if necessary. If
you don't store your stored procedures within source control I encourage you
to do so.
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> Hi just wondering if there is an easy way to copy stored procedures from
one
> dbase to another on the same server, possibly using query analyzer, or
vs.net
> server explorer? There are around 100 procedures so hoping do not have to
do
> each one individually. Thanks.
> --
> Paul G
> Software engineer.
|||Thanks for the response. I have enterprise manager but am not too familiar
with it, not even sure how to connect to the desired database. Also have
visual source safe on my machine, not sure if vss would have to be on the
server.
Paul.
"Keith Kratochvil" wrote:

> You could use Enterprise Manager to script all stored procedures to a file.
> You could then execute that file within your other database.
> If you have your stored procedures stored within a version control system
> you could simply grab the most recent files and execute them within the
> appropriate database. Source control is nice because you can look back at
> the changes of the stored procedure. You can see what changed, who changed
> it, and why. You can also roll back to a previous version if necessary. If
> you don't store your stored procedures within source control I encourage you
> to do so.
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> one
> vs.net
> do
>
|||EM:
connect to the server
expand the databases tab
right-click a database that you want to script the stored procedures for
choose All Tasks and Generate SQL Script
Click the "show all" button
put a checkmark in the "all stored procedures" checkbox
The options tab allows you to create one file or one file per object
hit preview (or ok)
VSS is more of a client side tool. We create stored procedures using Query
Analyzer. We save each stored procedure to a text file of the same name
(with a .sql extension) and then we check these into VSS. To modify a
stored procedure we check it our of VSS, open the file within Query
Analyzer, make the change, test it, save the file, recreate the stored
procedure (Ctrl-E), and check the file back into VSS.
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> Thanks for the response. I have enterprise manager but am not too
familiar[vbcol=seagreen]
> with it, not even sure how to connect to the desired database. Also have
> visual source safe on my machine, not sure if vss would have to be on the
> server.
> Paul.
> "Keith Kratochvil" wrote:
file.[vbcol=seagreen]
system[vbcol=seagreen]
at[vbcol=seagreen]
changed[vbcol=seagreen]
If[vbcol=seagreen]
you[vbcol=seagreen]
from[vbcol=seagreen]
have to[vbcol=seagreen]
|||thanks for the additional information, just wondering how to connect to the
database with EM? paul.
"Keith Kratochvil" wrote:

> EM:
> connect to the server
> expand the databases tab
> right-click a database that you want to script the stored procedures for
> choose All Tasks and Generate SQL Script
> Click the "show all" button
> put a checkmark in the "all stored procedures" checkbox
> The options tab allows you to create one file or one file per object
> hit preview (or ok)
> VSS is more of a client side tool. We create stored procedures using Query
> Analyzer. We save each stored procedure to a text file of the same name
> (with a .sql extension) and then we check these into VSS. To modify a
> stored procedure we check it our of VSS, open the file within Query
> Analyzer, make the change, test it, save the file, recreate the stored
> procedure (Ctrl-E), and check the file back into VSS.
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> familiar
> file.
> system
> at
> changed
> If
> you
> from
> have to
>
|||When you generate the script for your stored procedures, you also have the
choice the generate the permissions for each SP (that's if you will have the
same database users on the destination database).
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Paul" wrote:
[vbcol=seagreen]
> thanks for the additional information, just wondering how to connect to the
> database with EM? paul.
> "Keith Kratochvil" wrote:
|||ok thanks for the information. Actually my application does authentication,
password and username check and the connection uses a dedicated username and
password for the application so all stored procedures are accessable for this
dedicated username and password.
"Sasan Saidi" wrote:
[vbcol=seagreen]
> When you generate the script for your stored procedures, you also have the
> choice the generate the permissions for each SP (that's if you will have the
> same database users on the destination database).
> --
> Sasan Saidi, MSc in CS
> Senior DBA
> Brascan Business Services
> "I saw it work in a cartoon once so I am pretty sure I can do it."
>
> "Paul" wrote:
|||In order to connect to a [database] server you have to register that server
within the Enterprise Manager GUI. I am guessing that you already did this
step and you want to know how to "connect" to a specific database.
Open Enterprise Manager
Connect to a specific server.
Expand the databases folder
Now you will see each of the databases
Right-click on one and choose All Tasks and Generate SQL Script
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:7DF6A163-273B-4314-AFCB-AE46382D3CA3@.microsoft.com...
> thanks for the additional information, just wondering how to connect to
the[vbcol=seagreen]
> database with EM? paul.
> "Keith Kratochvil" wrote:
Query[vbcol=seagreen]
have[vbcol=seagreen]
the[vbcol=seagreen]
a[vbcol=seagreen]
the[vbcol=seagreen]
back[vbcol=seagreen]
necessary.[vbcol=seagreen]
encourage[vbcol=seagreen]
procedures[vbcol=seagreen]
analyzer, or[vbcol=seagreen]
|||actually have not registered it yet, just getting started. Thanks for the
information.
"Keith Kratochvil" wrote:

> In order to connect to a [database] server you have to register that server
> within the Enterprise Manager GUI. I am guessing that you already did this
> step and you want to know how to "connect" to a specific database.
> Open Enterprise Manager
> Connect to a specific server.
> Expand the databases folder
> Now you will see each of the databases
> Right-click on one and choose All Tasks and Generate SQL Script
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:7DF6A163-273B-4314-AFCB-AE46382D3CA3@.microsoft.com...
> the
> Query
> have
> the
> a
> the
> back
> necessary.
> encourage
> procedures
> analyzer, or
>

copy stored procedure from dbase 1 to dbase2

Hi just wondering if there is an easy way to copy stored procedures from one
dbase to another on the same server, possibly using query analyzer, or vs.net
server explorer? There are around 100 procedures so hoping do not have to do
each one individually. Thanks.
--
Paul G
Software engineer.You could use Enterprise Manager to script all stored procedures to a file.
You could then execute that file within your other database.
If you have your stored procedures stored within a version control system
you could simply grab the most recent files and execute them within the
appropriate database. Source control is nice because you can look back at
the changes of the stored procedure. You can see what changed, who changed
it, and why. You can also roll back to a previous version if necessary. If
you don't store your stored procedures within source control I encourage you
to do so.
--
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> Hi just wondering if there is an easy way to copy stored procedures from
one
> dbase to another on the same server, possibly using query analyzer, or
vs.net
> server explorer? There are around 100 procedures so hoping do not have to
do
> each one individually. Thanks.
> --
> Paul G
> Software engineer.|||Thanks for the response. I have enterprise manager but am not too familiar
with it, not even sure how to connect to the desired database. Also have
visual source safe on my machine, not sure if vss would have to be on the
server.
Paul.
"Keith Kratochvil" wrote:
> You could use Enterprise Manager to script all stored procedures to a file.
> You could then execute that file within your other database.
> If you have your stored procedures stored within a version control system
> you could simply grab the most recent files and execute them within the
> appropriate database. Source control is nice because you can look back at
> the changes of the stored procedure. You can see what changed, who changed
> it, and why. You can also roll back to a previous version if necessary. If
> you don't store your stored procedures within source control I encourage you
> to do so.
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > Hi just wondering if there is an easy way to copy stored procedures from
> one
> > dbase to another on the same server, possibly using query analyzer, or
> vs.net
> > server explorer? There are around 100 procedures so hoping do not have to
> do
> > each one individually. Thanks.
> > --
> > Paul G
> > Software engineer.
>|||EM:
connect to the server
expand the databases tab
right-click a database that you want to script the stored procedures for
choose All Tasks and Generate SQL Script
Click the "show all" button
put a checkmark in the "all stored procedures" checkbox
The options tab allows you to create one file or one file per object
hit preview (or ok)
VSS is more of a client side tool. We create stored procedures using Query
Analyzer. We save each stored procedure to a text file of the same name
(with a .sql extension) and then we check these into VSS. To modify a
stored procedure we check it our of VSS, open the file within Query
Analyzer, make the change, test it, save the file, recreate the stored
procedure (Ctrl-E), and check the file back into VSS.
--
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> Thanks for the response. I have enterprise manager but am not too
familiar
> with it, not even sure how to connect to the desired database. Also have
> visual source safe on my machine, not sure if vss would have to be on the
> server.
> Paul.
> "Keith Kratochvil" wrote:
> > You could use Enterprise Manager to script all stored procedures to a
file.
> > You could then execute that file within your other database.
> >
> > If you have your stored procedures stored within a version control
system
> > you could simply grab the most recent files and execute them within the
> > appropriate database. Source control is nice because you can look back
at
> > the changes of the stored procedure. You can see what changed, who
changed
> > it, and why. You can also roll back to a previous version if necessary.
If
> > you don't store your stored procedures within source control I encourage
you
> > to do so.
> >
> > --
> > Keith
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > Hi just wondering if there is an easy way to copy stored procedures
from
> > one
> > > dbase to another on the same server, possibly using query analyzer, or
> > vs.net
> > > server explorer? There are around 100 procedures so hoping do not
have to
> > do
> > > each one individually. Thanks.
> > > --
> > > Paul G
> > > Software engineer.
> >
> >|||thanks for the additional information, just wondering how to connect to the
database with EM? paul.
"Keith Kratochvil" wrote:
> EM:
> connect to the server
> expand the databases tab
> right-click a database that you want to script the stored procedures for
> choose All Tasks and Generate SQL Script
> Click the "show all" button
> put a checkmark in the "all stored procedures" checkbox
> The options tab allows you to create one file or one file per object
> hit preview (or ok)
> VSS is more of a client side tool. We create stored procedures using Query
> Analyzer. We save each stored procedure to a text file of the same name
> (with a .sql extension) and then we check these into VSS. To modify a
> stored procedure we check it our of VSS, open the file within Query
> Analyzer, make the change, test it, save the file, recreate the stored
> procedure (Ctrl-E), and check the file back into VSS.
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > Thanks for the response. I have enterprise manager but am not too
> familiar
> > with it, not even sure how to connect to the desired database. Also have
> > visual source safe on my machine, not sure if vss would have to be on the
> > server.
> > Paul.
> > "Keith Kratochvil" wrote:
> >
> > > You could use Enterprise Manager to script all stored procedures to a
> file.
> > > You could then execute that file within your other database.
> > >
> > > If you have your stored procedures stored within a version control
> system
> > > you could simply grab the most recent files and execute them within the
> > > appropriate database. Source control is nice because you can look back
> at
> > > the changes of the stored procedure. You can see what changed, who
> changed
> > > it, and why. You can also roll back to a previous version if necessary.
> If
> > > you don't store your stored procedures within source control I encourage
> you
> > > to do so.
> > >
> > > --
> > > Keith
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > Hi just wondering if there is an easy way to copy stored procedures
> from
> > > one
> > > > dbase to another on the same server, possibly using query analyzer, or
> > > vs.net
> > > > server explorer? There are around 100 procedures so hoping do not
> have to
> > > do
> > > > each one individually. Thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
> > >
> > >
>|||When you generate the script for your stored procedures, you also have the
choice the generate the permissions for each SP (that's if you will have the
same database users on the destination database).
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Paul" wrote:
> thanks for the additional information, just wondering how to connect to the
> database with EM? paul.
> "Keith Kratochvil" wrote:
> > EM:
> > connect to the server
> > expand the databases tab
> > right-click a database that you want to script the stored procedures for
> > choose All Tasks and Generate SQL Script
> > Click the "show all" button
> > put a checkmark in the "all stored procedures" checkbox
> > The options tab allows you to create one file or one file per object
> > hit preview (or ok)
> >
> > VSS is more of a client side tool. We create stored procedures using Query
> > Analyzer. We save each stored procedure to a text file of the same name
> > (with a .sql extension) and then we check these into VSS. To modify a
> > stored procedure we check it our of VSS, open the file within Query
> > Analyzer, make the change, test it, save the file, recreate the stored
> > procedure (Ctrl-E), and check the file back into VSS.
> >
> > --
> > Keith
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > > Thanks for the response. I have enterprise manager but am not too
> > familiar
> > > with it, not even sure how to connect to the desired database. Also have
> > > visual source safe on my machine, not sure if vss would have to be on the
> > > server.
> > > Paul.
> > > "Keith Kratochvil" wrote:
> > >
> > > > You could use Enterprise Manager to script all stored procedures to a
> > file.
> > > > You could then execute that file within your other database.
> > > >
> > > > If you have your stored procedures stored within a version control
> > system
> > > > you could simply grab the most recent files and execute them within the
> > > > appropriate database. Source control is nice because you can look back
> > at
> > > > the changes of the stored procedure. You can see what changed, who
> > changed
> > > > it, and why. You can also roll back to a previous version if necessary.
> > If
> > > > you don't store your stored procedures within source control I encourage
> > you
> > > > to do so.
> > > >
> > > > --
> > > > Keith
> > > >
> > > >
> > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > > Hi just wondering if there is an easy way to copy stored procedures
> > from
> > > > one
> > > > > dbase to another on the same server, possibly using query analyzer, or
> > > > vs.net
> > > > > server explorer? There are around 100 procedures so hoping do not
> > have to
> > > > do
> > > > > each one individually. Thanks.
> > > > > --
> > > > > Paul G
> > > > > Software engineer.
> > > >
> > > >
> >
> >|||ok thanks for the information. Actually my application does authentication,
password and username check and the connection uses a dedicated username and
password for the application so all stored procedures are accessable for this
dedicated username and password.
"Sasan Saidi" wrote:
> When you generate the script for your stored procedures, you also have the
> choice the generate the permissions for each SP (that's if you will have the
> same database users on the destination database).
> --
> Sasan Saidi, MSc in CS
> Senior DBA
> Brascan Business Services
> "I saw it work in a cartoon once so I am pretty sure I can do it."
>
> "Paul" wrote:
> > thanks for the additional information, just wondering how to connect to the
> > database with EM? paul.
> >
> > "Keith Kratochvil" wrote:
> >
> > > EM:
> > > connect to the server
> > > expand the databases tab
> > > right-click a database that you want to script the stored procedures for
> > > choose All Tasks and Generate SQL Script
> > > Click the "show all" button
> > > put a checkmark in the "all stored procedures" checkbox
> > > The options tab allows you to create one file or one file per object
> > > hit preview (or ok)
> > >
> > > VSS is more of a client side tool. We create stored procedures using Query
> > > Analyzer. We save each stored procedure to a text file of the same name
> > > (with a .sql extension) and then we check these into VSS. To modify a
> > > stored procedure we check it our of VSS, open the file within Query
> > > Analyzer, make the change, test it, save the file, recreate the stored
> > > procedure (Ctrl-E), and check the file back into VSS.
> > >
> > > --
> > > Keith
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > > > Thanks for the response. I have enterprise manager but am not too
> > > familiar
> > > > with it, not even sure how to connect to the desired database. Also have
> > > > visual source safe on my machine, not sure if vss would have to be on the
> > > > server.
> > > > Paul.
> > > > "Keith Kratochvil" wrote:
> > > >
> > > > > You could use Enterprise Manager to script all stored procedures to a
> > > file.
> > > > > You could then execute that file within your other database.
> > > > >
> > > > > If you have your stored procedures stored within a version control
> > > system
> > > > > you could simply grab the most recent files and execute them within the
> > > > > appropriate database. Source control is nice because you can look back
> > > at
> > > > > the changes of the stored procedure. You can see what changed, who
> > > changed
> > > > > it, and why. You can also roll back to a previous version if necessary.
> > > If
> > > > > you don't store your stored procedures within source control I encourage
> > > you
> > > > > to do so.
> > > > >
> > > > > --
> > > > > Keith
> > > > >
> > > > >
> > > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > > > Hi just wondering if there is an easy way to copy stored procedures
> > > from
> > > > > one
> > > > > > dbase to another on the same server, possibly using query analyzer, or
> > > > > vs.net
> > > > > > server explorer? There are around 100 procedures so hoping do not
> > > have to
> > > > > do
> > > > > > each one individually. Thanks.
> > > > > > --
> > > > > > Paul G
> > > > > > Software engineer.
> > > > >
> > > > >
> > >
> > >|||In order to connect to a [database] server you have to register that server
within the Enterprise Manager GUI. I am guessing that you already did this
step and you want to know how to "connect" to a specific database.
Open Enterprise Manager
Connect to a specific server.
Expand the databases folder
Now you will see each of the databases
Right-click on one and choose All Tasks and Generate SQL Script
--
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:7DF6A163-273B-4314-AFCB-AE46382D3CA3@.microsoft.com...
> thanks for the additional information, just wondering how to connect to
the
> database with EM? paul.
> "Keith Kratochvil" wrote:
> > EM:
> > connect to the server
> > expand the databases tab
> > right-click a database that you want to script the stored procedures for
> > choose All Tasks and Generate SQL Script
> > Click the "show all" button
> > put a checkmark in the "all stored procedures" checkbox
> > The options tab allows you to create one file or one file per object
> > hit preview (or ok)
> >
> > VSS is more of a client side tool. We create stored procedures using
Query
> > Analyzer. We save each stored procedure to a text file of the same name
> > (with a .sql extension) and then we check these into VSS. To modify a
> > stored procedure we check it our of VSS, open the file within Query
> > Analyzer, make the change, test it, save the file, recreate the stored
> > procedure (Ctrl-E), and check the file back into VSS.
> >
> > --
> > Keith
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > > Thanks for the response. I have enterprise manager but am not too
> > familiar
> > > with it, not even sure how to connect to the desired database. Also
have
> > > visual source safe on my machine, not sure if vss would have to be on
the
> > > server.
> > > Paul.
> > > "Keith Kratochvil" wrote:
> > >
> > > > You could use Enterprise Manager to script all stored procedures to
a
> > file.
> > > > You could then execute that file within your other database.
> > > >
> > > > If you have your stored procedures stored within a version control
> > system
> > > > you could simply grab the most recent files and execute them within
the
> > > > appropriate database. Source control is nice because you can look
back
> > at
> > > > the changes of the stored procedure. You can see what changed, who
> > changed
> > > > it, and why. You can also roll back to a previous version if
necessary.
> > If
> > > > you don't store your stored procedures within source control I
encourage
> > you
> > > > to do so.
> > > >
> > > > --
> > > > Keith
> > > >
> > > >
> > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > > Hi just wondering if there is an easy way to copy stored
procedures
> > from
> > > > one
> > > > > dbase to another on the same server, possibly using query
analyzer, or
> > > > vs.net
> > > > > server explorer? There are around 100 procedures so hoping do not
> > have to
> > > > do
> > > > > each one individually. Thanks.
> > > > > --
> > > > > Paul G
> > > > > Software engineer.
> > > >
> > > >
> >
> >|||actually have not registered it yet, just getting started. Thanks for the
information.
"Keith Kratochvil" wrote:
> In order to connect to a [database] server you have to register that server
> within the Enterprise Manager GUI. I am guessing that you already did this
> step and you want to know how to "connect" to a specific database.
> Open Enterprise Manager
> Connect to a specific server.
> Expand the databases folder
> Now you will see each of the databases
> Right-click on one and choose All Tasks and Generate SQL Script
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:7DF6A163-273B-4314-AFCB-AE46382D3CA3@.microsoft.com...
> > thanks for the additional information, just wondering how to connect to
> the
> > database with EM? paul.
> >
> > "Keith Kratochvil" wrote:
> >
> > > EM:
> > > connect to the server
> > > expand the databases tab
> > > right-click a database that you want to script the stored procedures for
> > > choose All Tasks and Generate SQL Script
> > > Click the "show all" button
> > > put a checkmark in the "all stored procedures" checkbox
> > > The options tab allows you to create one file or one file per object
> > > hit preview (or ok)
> > >
> > > VSS is more of a client side tool. We create stored procedures using
> Query
> > > Analyzer. We save each stored procedure to a text file of the same name
> > > (with a .sql extension) and then we check these into VSS. To modify a
> > > stored procedure we check it our of VSS, open the file within Query
> > > Analyzer, make the change, test it, save the file, recreate the stored
> > > procedure (Ctrl-E), and check the file back into VSS.
> > >
> > > --
> > > Keith
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > > > Thanks for the response. I have enterprise manager but am not too
> > > familiar
> > > > with it, not even sure how to connect to the desired database. Also
> have
> > > > visual source safe on my machine, not sure if vss would have to be on
> the
> > > > server.
> > > > Paul.
> > > > "Keith Kratochvil" wrote:
> > > >
> > > > > You could use Enterprise Manager to script all stored procedures to
> a
> > > file.
> > > > > You could then execute that file within your other database.
> > > > >
> > > > > If you have your stored procedures stored within a version control
> > > system
> > > > > you could simply grab the most recent files and execute them within
> the
> > > > > appropriate database. Source control is nice because you can look
> back
> > > at
> > > > > the changes of the stored procedure. You can see what changed, who
> > > changed
> > > > > it, and why. You can also roll back to a previous version if
> necessary.
> > > If
> > > > > you don't store your stored procedures within source control I
> encourage
> > > you
> > > > > to do so.
> > > > >
> > > > > --
> > > > > Keith
> > > > >
> > > > >
> > > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > > > Hi just wondering if there is an easy way to copy stored
> procedures
> > > from
> > > > > one
> > > > > > dbase to another on the same server, possibly using query
> analyzer, or
> > > > > vs.net
> > > > > > server explorer? There are around 100 procedures so hoping do not
> > > have to
> > > > > do
> > > > > > each one individually. Thanks.
> > > > > > --
> > > > > > Paul G
> > > > > > Software engineer.
> > > > >
> > > > >
> > >
> > >
>

copy stored procedure from dbase 1 to dbase2

Hi just wondering if there is an easy way to copy stored procedures from one
dbase to another on the same server, possibly using query analyzer, or vs.ne
t
server explorer? There are around 100 procedures so hoping do not have to d
o
each one individually. Thanks.
--
Paul G
Software engineer.You could use Enterprise Manager to script all stored procedures to a file.
You could then execute that file within your other database.
If you have your stored procedures stored within a version control system
you could simply grab the most recent files and execute them within the
appropriate database. Source control is nice because you can look back at
the changes of the stored procedure. You can see what changed, who changed
it, and why. You can also roll back to a previous version if necessary. If
you don't store your stored procedures within source control I encourage you
to do so.
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> Hi just wondering if there is an easy way to copy stored procedures from
one
> dbase to another on the same server, possibly using query analyzer, or
vs.net
> server explorer? There are around 100 procedures so hoping do not have to
do
> each one individually. Thanks.
> --
> Paul G
> Software engineer.|||Thanks for the response. I have enterprise manager but am not too familiar
with it, not even sure how to connect to the desired database. Also have
visual source safe on my machine, not sure if vss would have to be on the
server.
Paul.
"Keith Kratochvil" wrote:

> You could use Enterprise Manager to script all stored procedures to a file
.
> You could then execute that file within your other database.
> If you have your stored procedures stored within a version control system
> you could simply grab the most recent files and execute them within the
> appropriate database. Source control is nice because you can look back at
> the changes of the stored procedure. You can see what changed, who change
d
> it, and why. You can also roll back to a previous version if necessary.
If
> you don't store your stored procedures within source control I encourage y
ou
> to do so.
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> one
> vs.net
> do
>|||EM:
connect to the server
expand the databases tab
right-click a database that you want to script the stored procedures for
choose All Tasks and Generate SQL Script
Click the "show all" button
put a checkmark in the "all stored procedures" checkbox
The options tab allows you to create one file or one file per object
hit preview (or ok)
VSS is more of a client side tool. We create stored procedures using Query
Analyzer. We save each stored procedure to a text file of the same name
(with a .sql extension) and then we check these into VSS. To modify a
stored procedure we check it our of VSS, open the file within Query
Analyzer, make the change, test it, save the file, recreate the stored
procedure (Ctrl-E), and check the file back into VSS.
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> Thanks for the response. I have enterprise manager but am not too
familiar[vbcol=seagreen]
> with it, not even sure how to connect to the desired database. Also have
> visual source safe on my machine, not sure if vss would have to be on the
> server.
> Paul.
> "Keith Kratochvil" wrote:
>
file.[vbcol=seagreen]
system[vbcol=seagreen]
at[vbcol=seagreen]
changed[vbcol=seagreen]
If[vbcol=seagreen]
you[vbcol=seagreen]
from[vbcol=seagreen]
have to[vbcol=seagreen]|||thanks for the additional information, just wondering how to connect to the
database with EM? paul.
"Keith Kratochvil" wrote:

> EM:
> connect to the server
> expand the databases tab
> right-click a database that you want to script the stored procedures for
> choose All Tasks and Generate SQL Script
> Click the "show all" button
> put a checkmark in the "all stored procedures" checkbox
> The options tab allows you to create one file or one file per object
> hit preview (or ok)
> VSS is more of a client side tool. We create stored procedures using Quer
y
> Analyzer. We save each stored procedure to a text file of the same name
> (with a .sql extension) and then we check these into VSS. To modify a
> stored procedure we check it our of VSS, open the file within Query
> Analyzer, make the change, test it, save the file, recreate the stored
> procedure (Ctrl-E), and check the file back into VSS.
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> familiar
> file.
> system
> at
> changed
> If
> you
> from
> have to
>|||When you generate the script for your stored procedures, you also have the
choice the generate the permissions for each SP (that's if you will have the
same database users on the destination database).
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Paul" wrote:
[vbcol=seagreen]
> thanks for the additional information, just wondering how to connect to th
e
> database with EM? paul.
> "Keith Kratochvil" wrote:
>|||ok thanks for the information. Actually my application does authentication,
password and username check and the connection uses a dedicated username and
password for the application so all stored procedures are accessable for thi
s
dedicated username and password.
"Sasan Saidi" wrote:
[vbcol=seagreen]
> When you generate the script for your stored procedures, you also have the
> choice the generate the permissions for each SP (that's if you will have t
he
> same database users on the destination database).
> --
> Sasan Saidi, MSc in CS
> Senior DBA
> Brascan Business Services
> "I saw it work in a cartoon once so I am pretty sure I can do it."
>
> "Paul" wrote:
>|||In order to connect to a [database] server you have to register that ser
ver
within the Enterprise Manager GUI. I am guessing that you already did this
step and you want to know how to "connect" to a specific database.
Open Enterprise Manager
Connect to a specific server.
Expand the databases folder
Now you will see each of the databases
Right-click on one and choose All Tasks and Generate SQL Script
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:7DF6A163-273B-4314-AFCB-AE46382D3CA3@.microsoft.com...
> thanks for the additional information, just wondering how to connect to
the[vbcol=seagreen]
> database with EM? paul.
> "Keith Kratochvil" wrote:
>
Query[vbcol=seagreen]
have[vbcol=seagreen]
the[vbcol=seagreen]
a[vbcol=seagreen]
the[vbcol=seagreen]
back[vbcol=seagreen]
necessary.[vbcol=seagreen]
encourage[vbcol=seagreen]
procedures[vbcol=seagreen]
analyzer, or[vbcol=seagreen]|||actually have not registered it yet, just getting started. Thanks for the
information.
"Keith Kratochvil" wrote:

> In order to connect to a [database] server you have to register that s
erver
> within the Enterprise Manager GUI. I am guessing that you already did thi
s
> step and you want to know how to "connect" to a specific database.
> Open Enterprise Manager
> Connect to a specific server.
> Expand the databases folder
> Now you will see each of the databases
> Right-click on one and choose All Tasks and Generate SQL Script
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:7DF6A163-273B-4314-AFCB-AE46382D3CA3@.microsoft.com...
> the
> Query
> have
> the
> a
> the
> back
> necessary.
> encourage
> procedures
> analyzer, or
>

Copy SQL 7.0 db to SQL 2k

Hi all;
Is there a procedure to copy a database from 7.0 to 2K or can in fact a 7.0
db be copied into 2K?
Do I have to perform a backup/restore situation for this db or are there
possibly some CLI commands for a db copy?
Thanks in advance.
Steve
There is no "Copy" command per say but you can either do a Restore or a
sp_attach_db. Both operations will take a 7.0 db and upgrade it to a 2000
db and leave the data and objects etc intact.
Andrew J. Kelly SQL MVP
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:F4B16A4D-126A-4BDA-A16D-8BB57CD7A301@.microsoft.com...
> Hi all;
> Is there a procedure to copy a database from 7.0 to 2K or can in fact a
> 7.0
> db be copied into 2K?
> Do I have to perform a backup/restore situation for this db or are there
> possibly some CLI commands for a db copy?
> Thanks in advance.
> Steve
|||Hi Steve,
One of the easiest method is using backup/restore.
Thanks
Yogish
|||Keep in mind the collation type...
--
Sasan Saidi
MSc in CS, MCSE4, IBM Certified MQ 5.3 Administrator
Senior DBA
"Yogish" wrote:

> Hi Steve,
> One of the easiest method is using backup/restore.
> --
> Thanks
> Yogish