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 syntax for ADO.net recordsets Select method.

I have a interger stored in x.

I want to use x in a SELECT statement like so :

SELECT * from aTable WHERE A_Column = x

This select statement is then assigned to and passed as a string like :

sql = "SELECT * from aTable WHERE A_Column = x"

How does the x get interpreted correctly ?Try:

sql = "SELECT * from aTable WHERE A_Column = " & x

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.

Copying Views and Stored Procedures

Hello All,
I have two SQL databases which have the exact same tables - just different data. In database A there are only tables. In database B there are tables, stored procedures and views. I need to transfer the stored procedures and views from database B into database A. Is there a way to do this?
Thanks in advanceDTS or Script it. Your pick.|||Thank You.

I was playing around with it and I used a script. Thanks for your reply.|||What about contraints?

Just make sure you do them in the correct order...ie if a sproc references a view...

I guess if you did you'd just get a warning message...and once it was reference it would be resolved...

Anyone experience this?|||Originally posted by Brett Kaiser
What about contraints?

Just make sure you do them in the correct order...ie if a sproc references a view...

I guess if you did you'd just get a warning message...and once it was reference it would be resolved...

Anyone experience this?

I believe no entries would be inserted into sysdepends for the sproc corresponding to the view... which would lead to the view not showing up when you do a sp_depends on the sproc ...

Copying views

I have two databases A & B, I copied all of the tabels and data from A
to B. I can't figure out how to copy the Views and Stored procedures
from A to B. As you can see I'm new at this, can anyone point me in
the right direction? Oh... forgot running SQL 2000.

Thanks[posted and mailed, please reply in news]

Jim Davidson (raccoon@.icubed.com) writes:
> I have two databases A & B, I copied all of the tabels and data from A
> to B. I can't figure out how to copy the Views and Stored procedures
> from A to B. As you can see I'm new at this, can anyone point me in
> the right direction? Oh... forgot running SQL 2000.

If you want to make a complete copy of a database, there are certainly
easier ways to go. The below assumes that you run Query Analyzer:

exec sp_helpdb yourdb
-- Make notice of the values in the name and filename columns. (Cut
-- and paste to query window.)

BACKUP DATABASE yourdb TO DISK = 'C:\temp\yourbackup.bak'
RESTORE DATABASE yourdbcopy FROM DISK = 'C:\temp\yourbackup.bak'
WITH MOVE 'yourdb' TO 'C:\MSSQL\Data\copyofyourdb.mdf',
MOVE 'yourdblog' TO 'C:\MSSQL\Data\copyofyourdb.ldf',
REPLACE
EXEC master..xp_cmdshell 'DEL C:\temp\yourbackup.bak'

In the RESTORE command replace yourdb and yourdblog with the values
from the name column in the sp_helpdb output, and in paths, replace
the directory paths with the value from the filename columns. You
must change the file name.

Note that RESTORE DATABASE creates the database if it does not exist.

You can also do the backup and restore stuff from Enterprise Manager,
but I am more confident with the T-SQL commands, so I cannot describe
those dialogs.

An alternative, is to use sp_detach_db, copy the database files and
then use sp_attach_db on the copy and the original.

As for the original question, the answer is that you should maintain
all your SQL objects under version control and reload from the source
there. You can also use the scripting facilities in Enterprise
Manager. Right-click the database, and select All Tasks and then
Generate SQL Scripts.

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

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

Copying the database

Hi,

I am changing my hosting from one company to another company. How can I copy my full database along with views and stored procedures. I have only access to query analyzer and enterprise manager from where I am not able to backup the database on my local computer. As it is very urgent please suggest me a way to do this.

Thanks in advance,
UdayIf you have Enterprise Manager, right-click on the database, All Tasks -> Export Data.

The source should already be selected, Click next.

Set up the destination to a SQL Server that you have control over and create a <new> database. Type in a name, click OK, then click Next.

Select Copy objects and data, click Next.

Select run immediately, click Next. Then you'll need to click OK somewhere in there.|||Hi,

I did that but I am not able to copy the stored procedures and views, when I try to generate query it gives me

/****** Encrypted object is not transferable, and script can not be generated. ******/

How can I copy this stored procedures and views.

Thanks in advance,
Uday.

Copying structure of table form one database to another

I am wondering if there is a simple way, like a stored procudure or
using some type of table object, to copy a table form one database to
another. Just the structure , index, triggers, I don't want the data.kevin.jonas@.gmail.com wrote:
> I am wondering if there is a simple way, like a stored procudure or
> using some type of table object, to copy a table form one database to
> another. Just the structure , index, triggers, I don't want the data.
You can generate a script for the whole schema with EM and execute it on
the target db. Maybe there's also a solution involving DTS - I'm not sure.
robert|||Using DMO library also is possible and using DTS
--
Current location: Alicante (ES)
"Robert Klemme" wrote:

> kevin.jonas@.gmail.com wrote:
> You can generate a script for the whole schema with EM and execute it on
> the target db. Maybe there's also a solution involving DTS - I'm not sure
.
> robert
>

Copying Stored procs in SQL Server 2005

How can I copy stored procs from a 2000 server to a 2005 server? The only
thing I see in the SQL SMS is copy the database which I'm having problems
with. In Ent Mgr 2000 when you export you could select SPs. I don't see that
option available in SMS.
Thanks for your help.Hi
You can try using a DTS package to transfer only the Stored Procedures.
Use the 'Copy SQL Server Objects Task' and choose your source / destination
SQL Servers and then under the copy tab select the objects that you want to
transfer.
You need to install the Data Transformation Services support [Integratio
n
Services] on the SQL 2005 server and create the DTS package here. Since it i
s
backward compatible, I assume this will work.
Regards
Amer M J
MCP
"UJ" wrote:

> How can I copy stored procs from a 2000 server to a 2005 server? The only
> thing I see in the SQL SMS is copy the database which I'm having problems
> with. In Ent Mgr 2000 when you export you could select SPs. I don't see th
at
> option available in SMS.
> Thanks for your help.
>
>|||The cleanest way is to script out the sp's that you want in 2000 and run
that script in 2005. To get the script right click the db in EM and select
All Tasks - Generate Script. Then select the sp's you want.
Andrew J. Kelly SQL MVP
"UJ" <fred@.nowhere.com> wrote in message
news:%232rCEI9BGHA.1676@.TK2MSFTNGP09.phx.gbl...
> How can I copy stored procs from a 2000 server to a 2005 server? The only
> thing I see in the SQL SMS is copy the database which I'm having problems
> with. In Ent Mgr 2000 when you export you could select SPs. I don't see
> that option available in SMS.
> Thanks for your help.
>|||Where do I find the 'Copy SQL Server Objects Task' ?
"Amer M J" <AmerMJ@.discussions.microsoft.com> wrote in message
news:877834F8-0B0E-4982-B506-370C67D51E72@.microsoft.com...[vbcol=seagreen]
> Hi
> You can try using a DTS package to transfer only the Stored Procedures.
> Use the 'Copy SQL Server Objects Task' and choose your source /
> destination
> SQL Servers and then under the copy tab select the objects that you want
> to
> transfer.
> You need to install the Data Transformation Services support [Integrat
ion
> Services] on the SQL 2005 server and create the DTS package here. Since it
> is
> backward compatible, I assume this will work.
> Regards
> Amer M J
> MCP
> "UJ" wrote:
>|||That is all fine, but what is the proper way to do this in 2005 without
reverting back to DTS? Isnt DTS being depreciated anyway? Why isnt it as eas
y
as the old "import data" option in 2000? I am definately frustrated with SMS
2005 so far because I can't find a way to do the most simple things like
copying tables and procs between servers...
"Andrew J. Kelly" wrote:

> The cleanest way is to script out the sp's that you want in 2000 and run
> that script in 2005. To get the script right click the db in EM and selec
t
> All Tasks - Generate Script. Then select the sp's you want.
> --
> Andrew J. Kelly SQL MVP
>
> "UJ" <fred@.nowhere.com> wrote in message
> news:%232rCEI9BGHA.1676@.TK2MSFTNGP09.phx.gbl...
>
>

Copying Stored procs in SQL Server 2005

How can I copy stored procs from a 2000 server to a 2005 server? The only
thing I see in the SQL SMS is copy the database which I'm having problems
with. In Ent Mgr 2000 when you export you could select SPs. I don't see that
option available in SMS.
Thanks for your help.Hi
You can try using a DTS package to transfer only the Stored Procedures.
Use the 'Copy SQL Server Objects Task' and choose your source / destination
SQL Servers and then under the copy tab select the objects that you want to
transfer.
You need to install the Data Transformation Services support [Integration
Services] on the SQL 2005 server and create the DTS package here. Since it is
backward compatible, I assume this will work.
Regards
Amer M J
MCP
"UJ" wrote:
> How can I copy stored procs from a 2000 server to a 2005 server? The only
> thing I see in the SQL SMS is copy the database which I'm having problems
> with. In Ent Mgr 2000 when you export you could select SPs. I don't see that
> option available in SMS.
> Thanks for your help.
>
>|||The cleanest way is to script out the sp's that you want in 2000 and run
that script in 2005. To get the script right click the db in EM and select
All Tasks - Generate Script. Then select the sp's you want.
--
Andrew J. Kelly SQL MVP
"UJ" <fred@.nowhere.com> wrote in message
news:%232rCEI9BGHA.1676@.TK2MSFTNGP09.phx.gbl...
> How can I copy stored procs from a 2000 server to a 2005 server? The only
> thing I see in the SQL SMS is copy the database which I'm having problems
> with. In Ent Mgr 2000 when you export you could select SPs. I don't see
> that option available in SMS.
> Thanks for your help.
>|||Where do I find the 'Copy SQL Server Objects Task' ?
"Amer M J" <AmerMJ@.discussions.microsoft.com> wrote in message
news:877834F8-0B0E-4982-B506-370C67D51E72@.microsoft.com...
> Hi
> You can try using a DTS package to transfer only the Stored Procedures.
> Use the 'Copy SQL Server Objects Task' and choose your source /
> destination
> SQL Servers and then under the copy tab select the objects that you want
> to
> transfer.
> You need to install the Data Transformation Services support [Integration
> Services] on the SQL 2005 server and create the DTS package here. Since it
> is
> backward compatible, I assume this will work.
> Regards
> Amer M J
> MCP
> "UJ" wrote:
>> How can I copy stored procs from a 2000 server to a 2005 server? The only
>> thing I see in the SQL SMS is copy the database which I'm having problems
>> with. In Ent Mgr 2000 when you export you could select SPs. I don't see
>> that
>> option available in SMS.
>> Thanks for your help.
>>|||That is all fine, but what is the proper way to do this in 2005 without
reverting back to DTS? Isnt DTS being depreciated anyway? Why isnt it as easy
as the old "import data" option in 2000? I am definately frustrated with SMS
2005 so far because I can't find a way to do the most simple things like
copying tables and procs between servers...
"Andrew J. Kelly" wrote:
> The cleanest way is to script out the sp's that you want in 2000 and run
> that script in 2005. To get the script right click the db in EM and select
> All Tasks - Generate Script. Then select the sp's you want.
> --
> Andrew J. Kelly SQL MVP
>
> "UJ" <fred@.nowhere.com> wrote in message
> news:%232rCEI9BGHA.1676@.TK2MSFTNGP09.phx.gbl...
> > How can I copy stored procs from a 2000 server to a 2005 server? The only
> > thing I see in the SQL SMS is copy the database which I'm having problems
> > with. In Ent Mgr 2000 when you export you could select SPs. I don't see
> > that option available in SMS.
> >
> > Thanks for your help.
> >
> >
>
>sql

Copying Stored procs in SQL Server 2005

How can I copy stored procs from a 2000 server to a 2005 server? The only
thing I see in the SQL SMS is copy the database which I'm having problems
with. In Ent Mgr 2000 when you export you could select SPs. I don't see that
option available in SMS.
Thanks for your help.
Hi
You can try using a DTS package to transfer only the Stored Procedures.
Use the 'Copy SQL Server Objects Task' and choose your source / destination
SQL Servers and then under the copy tab select the objects that you want to
transfer.
You need to install the Data Transformation Services support [Integration
Services] on the SQL 2005 server and create the DTS package here. Since it is
backward compatible, I assume this will work.
Regards
Amer M J
MCP
"UJ" wrote:

> How can I copy stored procs from a 2000 server to a 2005 server? The only
> thing I see in the SQL SMS is copy the database which I'm having problems
> with. In Ent Mgr 2000 when you export you could select SPs. I don't see that
> option available in SMS.
> Thanks for your help.
>
>
|||The cleanest way is to script out the sp's that you want in 2000 and run
that script in 2005. To get the script right click the db in EM and select
All Tasks - Generate Script. Then select the sp's you want.
Andrew J. Kelly SQL MVP
"UJ" <fred@.nowhere.com> wrote in message
news:%232rCEI9BGHA.1676@.TK2MSFTNGP09.phx.gbl...
> How can I copy stored procs from a 2000 server to a 2005 server? The only
> thing I see in the SQL SMS is copy the database which I'm having problems
> with. In Ent Mgr 2000 when you export you could select SPs. I don't see
> that option available in SMS.
> Thanks for your help.
>
|||Where do I find the 'Copy SQL Server Objects Task' ?
"Amer M J" <AmerMJ@.discussions.microsoft.com> wrote in message
news:877834F8-0B0E-4982-B506-370C67D51E72@.microsoft.com...[vbcol=seagreen]
> Hi
> You can try using a DTS package to transfer only the Stored Procedures.
> Use the 'Copy SQL Server Objects Task' and choose your source /
> destination
> SQL Servers and then under the copy tab select the objects that you want
> to
> transfer.
> You need to install the Data Transformation Services support [Integration
> Services] on the SQL 2005 server and create the DTS package here. Since it
> is
> backward compatible, I assume this will work.
> Regards
> Amer M J
> MCP
> "UJ" wrote:
|||That is all fine, but what is the proper way to do this in 2005 without
reverting back to DTS? Isnt DTS being depreciated anyway? Why isnt it as easy
as the old "import data" option in 2000? I am definately frustrated with SMS
2005 so far because I can't find a way to do the most simple things like
copying tables and procs between servers...
"Andrew J. Kelly" wrote:

> The cleanest way is to script out the sp's that you want in 2000 and run
> that script in 2005. To get the script right click the db in EM and select
> All Tasks - Generate Script. Then select the sp's you want.
> --
> Andrew J. Kelly SQL MVP
>
> "UJ" <fred@.nowhere.com> wrote in message
> news:%232rCEI9BGHA.1676@.TK2MSFTNGP09.phx.gbl...
>
>

copying stored procedures?

Hi all, is there a way I can simply copy the stored procedures in one DB, and "paste" them over into another DB that is identical? Thanks!You can script out any database object (right click and select Generate SQL from the menu), or you can import/export data and objects between databases (right click on the database and select import/export data in the menu).|||If the tables and views (and other objects) referenced in the stored procedure are same in both the database, then you can simply copy the Stored procedure and paste it in the other DB and use it.

You can view the stroed procdure in the Enterprise Manager by just double clicking on the procudure name or you can view the stored procedure in Query Analyser by using the stored procure sp_help <your sp name>

copying stored procedures to C drive on local machine

I have a project with around 50 stored procedures, can not seem to see the
dbase when using explorer but have access to it with enterprize manager and
visual studio.net server explorer. Just wondering if there is an easy way to
copy the stored procedures onto the workstation possibly using enterprize
manager or .net server explorer? thanks.
Paul G
Software engineer.
The stored procedures don't really have any meaning outside of the database,
and you should only see MDF/NDF/LDF files in windows explorer (they are not
separate files). What exactly are you trying to accomplish?
http://www.aspfaq.com/
(Reverse address to reply.)
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> I have a project with around 50 stored procedures, can not seem to see the
> dbase when using explorer but have access to it with enterprize manager
and
> visual studio.net server explorer. Just wondering if there is an easy way
to
> copy the stored procedures onto the workstation possibly using enterprize
> manager or .net server explorer? thanks.
> --
> Paul G
> Software engineer.
|||You can generate a script of the stored procedures. In SQL Enterprise
manager, right click your database name and go to SQL Scripting... It will
walk you through the process..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> I have a project with around 50 stored procedures, can not seem to see the
> dbase when using explorer but have access to it with enterprize manager
and
> visual studio.net server explorer. Just wondering if there is an easy way
to
> copy the stored procedures onto the workstation possibly using enterprize
> manager or .net server explorer? thanks.
> --
> Paul G
> Software engineer.
|||Hi thanks for the response. Just wanted to put the stored procedure code
onto a laptop for reference, so copying out to a client machine is sufficient.
"Aaron [SQL Server MVP]" wrote:

> The stored procedures don't really have any meaning outside of the database,
> and you should only see MDF/NDF/LDF files in windows explorer (they are not
> separate files). What exactly are you trying to accomplish?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> and
> to
>
>
|||ok thanks will give it a try. Hopefully it allows you to select all the
stored procedures in some fashion as it would take a bit of time to do each
one individually.
"Wayne Snyder" wrote:

> You can generate a script of the stored procedures. In SQL Enterprise
> manager, right click your database name and go to SQL Scripting... It will
> walk you through the process..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> and
> to
>
>
|||Yes, it does, try it out...
http://www.aspfaq.com/
(Reverse address to reply.)
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:ABF8BA22-734A-472A-9019-91B0D16BD502@.microsoft.com...
> ok thanks will give it a try. Hopefully it allows you to select all the
> stored procedures in some fashion as it would take a bit of time to do
each[vbcol=seagreen]
> one individually.
> "Wayne Snyder" wrote:
will[vbcol=seagreen]
the[vbcol=seagreen]
manager[vbcol=seagreen]
way[vbcol=seagreen]
enterprize[vbcol=seagreen]

copying stored procedures to C drive on local machine

I have a project with around 50 stored procedures, can not seem to see the
dbase when using explorer but have access to it with enterprize manager and
visual studio.net server explorer. Just wondering if there is an easy way to
copy the stored procedures onto the workstation possibly using enterprize
manager or .net server explorer? thanks.
--
Paul G
Software engineer.The stored procedures don't really have any meaning outside of the database,
and you should only see MDF/NDF/LDF files in windows explorer (they are not
separate files). What exactly are you trying to accomplish?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> I have a project with around 50 stored procedures, can not seem to see the
> dbase when using explorer but have access to it with enterprize manager
and
> visual studio.net server explorer. Just wondering if there is an easy way
to
> copy the stored procedures onto the workstation possibly using enterprize
> manager or .net server explorer? thanks.
> --
> Paul G
> Software engineer.|||You can generate a script of the stored procedures. In SQL Enterprise
manager, right click your database name and go to SQL Scripting... It will
walk you through the process..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> I have a project with around 50 stored procedures, can not seem to see the
> dbase when using explorer but have access to it with enterprize manager
and
> visual studio.net server explorer. Just wondering if there is an easy way
to
> copy the stored procedures onto the workstation possibly using enterprize
> manager or .net server explorer? thanks.
> --
> Paul G
> Software engineer.|||Hi thanks for the response. Just wanted to put the stored procedure code
onto a laptop for reference, so copying out to a client machine is sufficient.
"Aaron [SQL Server MVP]" wrote:
> The stored procedures don't really have any meaning outside of the database,
> and you should only see MDF/NDF/LDF files in windows explorer (they are not
> separate files). What exactly are you trying to accomplish?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> > I have a project with around 50 stored procedures, can not seem to see the
> > dbase when using explorer but have access to it with enterprize manager
> and
> > visual studio.net server explorer. Just wondering if there is an easy way
> to
> > copy the stored procedures onto the workstation possibly using enterprize
> > manager or .net server explorer? thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||ok thanks will give it a try. Hopefully it allows you to select all the
stored procedures in some fashion as it would take a bit of time to do each
one individually.
"Wayne Snyder" wrote:
> You can generate a script of the stored procedures. In SQL Enterprise
> manager, right click your database name and go to SQL Scripting... It will
> walk you through the process..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> > I have a project with around 50 stored procedures, can not seem to see the
> > dbase when using explorer but have access to it with enterprize manager
> and
> > visual studio.net server explorer. Just wondering if there is an easy way
> to
> > copy the stored procedures onto the workstation possibly using enterprize
> > manager or .net server explorer? thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||Yes, it does, try it out...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:ABF8BA22-734A-472A-9019-91B0D16BD502@.microsoft.com...
> ok thanks will give it a try. Hopefully it allows you to select all the
> stored procedures in some fashion as it would take a bit of time to do
each
> one individually.
> "Wayne Snyder" wrote:
> > You can generate a script of the stored procedures. In SQL Enterprise
> > manager, right click your database name and go to SQL Scripting... It
will
> > walk you through the process..
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> > > I have a project with around 50 stored procedures, can not seem to see
the
> > > dbase when using explorer but have access to it with enterprize
manager
> > and
> > > visual studio.net server explorer. Just wondering if there is an easy
way
> > to
> > > copy the stored procedures onto the workstation possibly using
enterprize
> > > manager or .net server explorer? thanks.
> > > --
> > > Paul G
> > > Software engineer.
> >
> >
> >

copying stored procedures to C drive on local machine

I have a project with around 50 stored procedures, can not seem to see the
dbase when using explorer but have access to it with enterprize manager and
visual studio.net server explorer. Just wondering if there is an easy way t
o
copy the stored procedures onto the workstation possibly using enterprize
manager or .net server explorer? thanks.
--
Paul G
Software engineer.The stored procedures don't really have any meaning outside of the database,
and you should only see MDF/NDF/LDF files in windows explorer (they are not
separate files). What exactly are you trying to accomplish?
http://www.aspfaq.com/
(Reverse address to reply.)
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> I have a project with around 50 stored procedures, can not seem to see the
> dbase when using explorer but have access to it with enterprize manager
and
> visual studio.net server explorer. Just wondering if there is an easy way
to
> copy the stored procedures onto the workstation possibly using enterprize
> manager or .net server explorer? thanks.
> --
> Paul G
> Software engineer.|||You can generate a script of the stored procedures. In SQL Enterprise
manager, right click your database name and go to SQL Scripting... It will
walk you through the process..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> I have a project with around 50 stored procedures, can not seem to see the
> dbase when using explorer but have access to it with enterprize manager
and
> visual studio.net server explorer. Just wondering if there is an easy way
to
> copy the stored procedures onto the workstation possibly using enterprize
> manager or .net server explorer? thanks.
> --
> Paul G
> Software engineer.|||Hi thanks for the response. Just wanted to put the stored procedure code
onto a laptop for reference, so copying out to a client machine is sufficien
t.
"Aaron [SQL Server MVP]" wrote:

> The stored procedures don't really have any meaning outside of the databas
e,
> and you should only see MDF/NDF/LDF files in windows explorer (they are no
t
> separate files). What exactly are you trying to accomplish?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> and
> to
>
>|||ok thanks will give it a try. Hopefully it allows you to select all the
stored procedures in some fashion as it would take a bit of time to do each
one individually.
"Wayne Snyder" wrote:

> You can generate a script of the stored procedures. In SQL Enterprise
> manager, right click your database name and go to SQL Scripting... It will
> walk you through the process..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> and
> to
>
>|||Yes, it does, try it out...
http://www.aspfaq.com/
(Reverse address to reply.)
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:ABF8BA22-734A-472A-9019-91B0D16BD502@.microsoft.com...
> ok thanks will give it a try. Hopefully it allows you to select all the
> stored procedures in some fashion as it would take a bit of time to do
each[vbcol=seagreen]
> one individually.
> "Wayne Snyder" wrote:
>
will[vbcol=seagreen]
the[vbcol=seagreen]
manager[vbcol=seagreen]
way[vbcol=seagreen]
enterprize[vbcol=seagreen]sql

Copying stored procedures to another database

Hi,
We have recently deployed our application into a parallel production
environment which will eventually become the main production environment. We
have had to make some changes to a number of CLR (VB) stored procedure,
which we've deployed and tested in production. Now we are ready to deploy
these stored procedures to our new production environment. Unfortunately, we
can't connect deploy remotely, and we can't perform a backup restore because
we need to keep the production data.
Is there a way that we can copy the stored procedures only from our
development server to our production server? They're CLR stored procedures,
so we can't script them out either.
Thanks> They're CLR stored procedures, so we can't script them out either.
Why now? Script the assembly, which will in your script file have the hex co
de for the assembly
instead of the file reference. Then also script your objects created from th
at assembly...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rob" <r_miller@.ozemail.com.au> wrote in message news:u042u2VQIHA.5288@.TK2MSFTNGP04.phx.gbl.
.
> Hi,
> We have recently deployed our application into a parallel production envir
onment which will
> eventually become the main production environment. We have had to make som
e changes to a number of
> CLR (VB) stored procedure, which we've deployed and tested in production.
Now we are ready to
> deploy these stored procedures to our new production environment. Unfortun
ately, we can't connect
> deploy remotely, and we can't perform a backup restore because we need to
keep the production
> data.
> Is there a way that we can copy the stored procedures only from our develo
pment server to our
> production server? They're CLR stored procedures, so we can't script them
out either.
> Thanks

Copying stored procedures to another database

Hi,
We have recently deployed our application into a parallel production
environment which will eventually become the main production environment. We
have had to make some changes to a number of CLR (VB) stored procedure,
which we've deployed and tested in production. Now we are ready to deploy
these stored procedures to our new production environment. Unfortunately, we
can't connect deploy remotely, and we can't perform a backup restore because
we need to keep the production data.
Is there a way that we can copy the stored procedures only from our
development server to our production server? They're CLR stored procedures,
so we can't script them out either.
Thanks> They're CLR stored procedures, so we can't script them out either.
Why now? Script the assembly, which will in your script file have the hex code for the assembly
instead of the file reference. Then also script your objects created from that assembly...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rob" <r_miller@.ozemail.com.au> wrote in message news:u042u2VQIHA.5288@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have recently deployed our application into a parallel production environment which will
> eventually become the main production environment. We have had to make some changes to a number of
> CLR (VB) stored procedure, which we've deployed and tested in production. Now we are ready to
> deploy these stored procedures to our new production environment. Unfortunately, we can't connect
> deploy remotely, and we can't perform a backup restore because we need to keep the production
> data.
> Is there a way that we can copy the stored procedures only from our development server to our
> production server? They're CLR stored procedures, so we can't script them out either.
> Thanks

copying stored procedures in SQL Server 2005?

Is there a quick way to copy a set of stored procedures in SQL Server 2005
from one database engine to another, as quickly as using DTS in SQL Server
2000? Other than restoring from a backup.
Thanks,
Dean S
Dean
In objects explorer details select stored procedures you want to copy to,
then right click and Script Stored Procedure as -- CREATE TO -- new
query, make sure that you under scope a target database and press F5
"Dean Slindee" <slindee@.charter.net> wrote in message
news:7D625CC4-05B4-4BF6-87F1-BA670532B06B@.microsoft.com...
> Is there a quick way to copy a set of stored procedures in SQL Server 2005
> from one database engine to another, as quickly as using DTS in SQL Server
> 2000? Other than restoring from a backup.
> Thanks,
> Dean S
|||On Mar 4, 11:23 pm, "Dean Slindee" <slin...@.charter.net> wrote:
> Is there a quick way to copy a set of stored procedures in SQL Server 2005
> from one database engine to another, as quickly as using DTS in SQL Server
> 2000? Other than restoring from a backup.
> Thanks,
> Dean S
I suggest that you can find a stored procedure named
dbo.sp_generate_script.
Using dbo.sp_generate_script, you can get all stored procedures in a
database.
GOOD LUCK.
|||Does this procedure copy one stored procedure at a time, or multiple stored
procedures?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OwmbByffIHA.1900@.TK2MSFTNGP02.phx.gbl...
> Dean
> In objects explorer details select stored procedures you want to copy
> to, then right click and Script Stored Procedure as -- CREATE TO -- new
> query, make sure that you under scope a target database and press F5
>
>
>
> "Dean Slindee" <slindee@.charter.net> wrote in message
> news:7D625CC4-05B4-4BF6-87F1-BA670532B06B@.microsoft.com...
>
|||Dean
multiple stored procedures
"Dean Slindee" <slindee@.charter.net> wrote in message
news:2B130575-80A1-40DA-A83C-C5A71D2AA39F@.microsoft.com...
> Does this procedure copy one stored procedure at a time, or multiple
> stored procedures?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OwmbByffIHA.1900@.TK2MSFTNGP02.phx.gbl...
>
|||Uri
One answer to my question could have been:
Create a new SSIS package using the "Transfer SQL Server Objects" tool.
In the tool, fill in the parameters and connections and execute the package.
This is conceptually similar to the former DTS process.
One just has to keep redefining a "temp" SSIS package, or create several
"work" packages, per database.
Your process would be more useful for a quick transfer; no package required.
However, I have been unable to accurately follow your brief instructions.
Would you please expand on the instructions with numbered bullet points?
Like:
1. In SQL Server 2005 Management Studio, click on the Source database table's
Stored Procedures folder.
2. Select the stored procedures to be copied (I can only select one at a
time).
3. Right click and select Script Stored Procedure as CREATE TO:
4. Now, click on the Destination database table's Stored Procedures folder
5. Press F5 at..
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uBLTdbofIHA.4140@.TK2MSFTNGP04.phx.gbl...
> Dean
> multiple stored procedures
>
> "Dean Slindee" <slindee@.charter.net> wrote in message
> news:2B130575-80A1-40DA-A83C-C5A71D2AA39F@.microsoft.com...
>

copying stored procedures in SQL Server 2005?

Is there a quick way to copy a set of stored procedures in SQL Server 2005
from one database engine to another, as quickly as using DTS in SQL Server
2000? Other than restoring from a backup.
Thanks,
Dean SDean
In objects explorer details select stored procedures you want to copy to,
then right click and Script Stored Procedure as -- CREATE TO -- new
query, make sure that you under scope a target database and press F5
"Dean Slindee" <slindee@.charter.net> wrote in message
news:7D625CC4-05B4-4BF6-87F1-BA670532B06B@.microsoft.com...
> Is there a quick way to copy a set of stored procedures in SQL Server 2005
> from one database engine to another, as quickly as using DTS in SQL Server
> 2000? Other than restoring from a backup.
> Thanks,
> Dean S|||On Mar 4, 11:23 pm, "Dean Slindee" <slin...@.charter.net> wrote:
> Is there a quick way to copy a set of stored procedures in SQL Server 2005
> from one database engine to another, as quickly as using DTS in SQL Server
> 2000? Other than restoring from a backup.
> Thanks,
> Dean S
I suggest that you can find a stored procedure named
dbo.sp_generate_script.
Using dbo.sp_generate_script, you can get all stored procedures in a
database.
GOOD LUCK.|||Does this procedure copy one stored procedure at a time, or multiple stored
procedures?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OwmbByffIHA.1900@.TK2MSFTNGP02.phx.gbl...
> Dean
> In objects explorer details select stored procedures you want to copy
> to, then right click and Script Stored Procedure as -- CREATE TO -- new
> query, make sure that you under scope a target database and press F5
>
>
>
> "Dean Slindee" <slindee@.charter.net> wrote in message
> news:7D625CC4-05B4-4BF6-87F1-BA670532B06B@.microsoft.com...
>> Is there a quick way to copy a set of stored procedures in SQL Server
>> 2005 from one database engine to another, as quickly as using DTS in SQL
>> Server 2000? Other than restoring from a backup.
>> Thanks,
>> Dean S
>|||Dean
multiple stored procedures
"Dean Slindee" <slindee@.charter.net> wrote in message
news:2B130575-80A1-40DA-A83C-C5A71D2AA39F@.microsoft.com...
> Does this procedure copy one stored procedure at a time, or multiple
> stored procedures?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OwmbByffIHA.1900@.TK2MSFTNGP02.phx.gbl...
>> Dean
>> In objects explorer details select stored procedures you want to copy
>> to, then right click and Script Stored Procedure as -- CREATE TO --
>> new query, make sure that you under scope a target database and press F5
>>
>>
>>
>> "Dean Slindee" <slindee@.charter.net> wrote in message
>> news:7D625CC4-05B4-4BF6-87F1-BA670532B06B@.microsoft.com...
>> Is there a quick way to copy a set of stored procedures in SQL Server
>> 2005 from one database engine to another, as quickly as using DTS in SQL
>> Server 2000? Other than restoring from a backup.
>> Thanks,
>> Dean S
>>
>|||Uri
One answer to my question could have been:
Create a new SSIS package using the "Transfer SQL Server Objects" tool.
In the tool, fill in the parameters and connections and execute the package.
This is conceptually similar to the former DTS process.
One just has to keep redefining a "temp" SSIS package, or create several
"work" packages, per database.
Your process would be more useful for a quick transfer; no package required.
However, I have been unable to accurately follow your brief instructions.
Would you please expand on the instructions with numbered bullet points?
Like:
1. In SQL Server 2005 Management Studio, click on the Source database table's
Stored Procedures folder.
2. Select the stored procedures to be copied (I can only select one at a
time).
3. Right click and select Script Stored Procedure as CREATE TO:
4. Now, click on the Destination database table's Stored Procedures folder
5. Press F5 at..
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uBLTdbofIHA.4140@.TK2MSFTNGP04.phx.gbl...
> Dean
> multiple stored procedures
>
> "Dean Slindee" <slindee@.charter.net> wrote in message
> news:2B130575-80A1-40DA-A83C-C5A71D2AA39F@.microsoft.com...
>> Does this procedure copy one stored procedure at a time, or multiple
>> stored procedures?
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OwmbByffIHA.1900@.TK2MSFTNGP02.phx.gbl...
>> Dean
>> In objects explorer details select stored procedures you want to copy
>> to, then right click and Script Stored Procedure as -- CREATE TO --
>> new query, make sure that you under scope a target database and press F5
>>
>>
>>
>> "Dean Slindee" <slindee@.charter.net> wrote in message
>> news:7D625CC4-05B4-4BF6-87F1-BA670532B06B@.microsoft.com...
>> Is there a quick way to copy a set of stored procedures in SQL Server
>> 2005 from one database engine to another, as quickly as using DTS in
>> SQL Server 2000? Other than restoring from a backup.
>> Thanks,
>> Dean S
>>
>

Copying Stored Procedures from one database to another

How do i copy Stored Procedures from one SQL Express database to another?

One way is, in Object Explorer (You do have SQL Server Management Studio Express, don't you), expand the Database, expand [Programability], expand [Stored Procedures], then right click on the stored procedure you want. One of the choices will be to [Script AS ...], choose [CREATE], then choose [File]. Save the file, copy the file, take to the other server, open it in Object Explorer, and then execute the file.

|||Thanks so much for your response Arnie. The part I'm confused about is importing it into the target database. I'm not sure how to do it...

I do use SQL Express Management Studio - from your explanation, I know how to export a stored procedure to a file. I'm unsure of how to import this file...
|||

Be sure to save the file with an extension of ".sql".

Then, in SSMSE, Click on [File], [Open], [File...] and navigate to and select your file. when it opens, then execute the code.

|||I'll just have to rename the database in the stored procedure to that of the target database, right? So executing it this way will cause the stored procedure to be copied over? Thanks again...

I have one last question...sorry to bother you...how do i export and import multiple stored procedures? I just noticed that there are over 30 SPs...doing this for each one would be time-consuming!!
|||Never mind - i figured out how to do that.

Right-click on source database>Tasks>Generate Script>

Select the source table, select Stored Procedures, then check the required stored procedures, finally save it in a single file with a .sql extension.

Open the file, rename the database to target database name, and execute.

Thanks again...
sql

Copying Stored Procedures from one database to another

How do i copy Stored Procedures from one SQL Express database to another?

One way is, in Object Explorer (You do have SQL Server Management Studio Express, don't you), expand the Database, expand [Programability], expand [Stored Procedures], then right click on the stored procedure you want. One of the choices will be to [Script AS ...], choose [CREATE], then choose [File]. Save the file, copy the file, take to the other server, open it in Object Explorer, and then execute the file.

|||Thanks so much for your response Arnie. The part I'm confused about is importing it into the target database. I'm not sure how to do it...

I do use SQL Express Management Studio - from your explanation, I know how to export a stored procedure to a file. I'm unsure of how to import this file...
|||

Be sure to save the file with an extension of ".sql".

Then, in SSMSE, Click on [File], [Open], [File...] and navigate to and select your file. when it opens, then execute the code.

|||I'll just have to rename the database in the stored procedure to that of the target database, right? So executing it this way will cause the stored procedure to be copied over? Thanks again...

I have one last question...sorry to bother you...how do i export and import multiple stored procedures? I just noticed that there are over 30 SPs...doing this for each one would be time-consuming!!
|||Never mind - i figured out how to do that.

Right-click on source database>Tasks>Generate Script>

Select the source table, select Stored Procedures, then check the required stored procedures, finally save it in a single file with a .sql extension.

Open the file, rename the database to target database name, and execute.

Thanks again...