sql server db: procedures

Showing posts with label procedures. Show all posts

Tuesday, March 27, 2012

Correct Procedures for Testing Against NULLs from SQL Server

Hi all,
I have some C# code that is pulling data from a database where a majority of the values being retrieved areNULL, yet their initial column data types are bothstringandint, which means that I have to temporarily store theseNULL's inintandstringdatatypes in C#. Later on in my code I have to test against these values,and was wondering if I am doing it correctly with the following code.
The following statement the variableor_team_home_idis of astringdata type, but may have had aNULLvalue assigned to it from the database
if (!or_team_home_id.Equals(DBNull.Value)) {}
The following statement the variableor_manager_id is of aintdata type, but also may have aNULLvalue assigned to it from the database.
if (!Convert.IsDBNull(or_manager_id)){}
Are these the correct way to test against NULL values retrieved fromteh database and that are stored in their respective data types.
Tryst
For string types you could compare with System.DbNull.Value as in (VB.NET)
If strVal IS System.DbNull.Value then

End If
For numeric datatypes I'd recommend doing an ISNULL(column,0) from the SQL stmt so it will be easier/faster to check for 0 from the front end.sql

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

Sunday, March 25, 2012

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.

Thursday, March 22, 2012

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

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

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

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

copying stored procedures

Hi All,
I have a small problem...
I need to replace stored procedures in an SQL database with ones from
another database without touching any of the tables or data is there a way I
can do this?
Any help would be great
Thanks
GavYou can generate an SQL script of the "new" storred procedures, then drop
the old ones (if your script does not contain the drop statement, or if you
changed the stored procedure names), and run your script from query
analyzer.
Francesco Anti
"Gav" <spam@.spam.com> wrote in message
news:%238WdCQFPFHA.2132@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a small problem...
> I need to replace stored procedures in an SQL database with ones from
> another database without touching any of the tables or data is there a way
> I can do this?
> Any help would be great
> Thanks
> Gav
>|||Use QA (in case you have both dbs in the same server.), edit the sp, change
the database in the comobobox and execute the alter statement in the second
db. If the db are in diff servers, generate the script, go to the second db,
drop the sp and execute the create procedure script.
AMB
"Gav" wrote:

> Hi All,
> I have a small problem...
> I need to replace stored procedures in an SQL database with ones from
> another database without touching any of the tables or data is there a way
I
> can do this?
> Any help would be great
> Thanks
> Gav
>
>

copying stored procedures

Hi,
How can I copy many sp's without recreate them by code
between two databases ?
Thanks,
OriUse the DTS copy objects function. You can set this up easily via the
wizard. Watch out for the option to copy dependent objects as well, as you
may wind up moving a table along with a SP.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"ori" <anonymous@.discussions.microsoft.com> wrote in message
news:137b501c44400$ef2e8a70$a401280a@.phx
.gbl...
> Hi,
> How can I copy many sp's without recreate them by code
> between two databases ?
> Thanks,
> Ori|||Take a look at Data Transformation Services. Right-click Tables, select All
Tasks > Export data, and when you get to the screen that offers three
choices, select "Copy objects and data..."
The wizard is fairly self-explanatory.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"ori" <anonymous@.discussions.microsoft.com> wrote in message
news:137b501c44400$ef2e8a70$a401280a@.phx
.gbl...
> Hi,
> How can I copy many sp's without recreate them by code
> between two databases ?
> Thanks,
> Ori|||Transfer via DTS?
Script and execute via Query Analyzer or Enterprise Manager?
--=20
Keith
"ori" <anonymous@.discussions.microsoft.com> wrote in message =
news:137b501c44400$ef2e8a70$a401280a@.phx
.gbl...
> Hi,
>=20
> How can I copy many sp's without recreate them by code=20
> between two databases ?
>=20
> Thanks,
>=20
> Ori|||Normal procedure is to generate a script of the required stored procedures,
and run that script on your target database.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"ori" <anonymous@.discussions.microsoft.com> wrote in message
news:137b501c44400$ef2e8a70$a401280a@.phx
.gbl...
Hi,
How can I copy many sp's without recreate them by code
between two databases ?
Thanks,
Ori

copying stored procedures

Hi,
How can I copy many sp's without recreate them by code
between two databases ?
Thanks,
OriUse the DTS copy objects function. You can set this up easily via the
wizard. Watch out for the option to copy dependent objects as well, as you
may wind up moving a table along with a SP.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"ori" <anonymous@.discussions.microsoft.com> wrote in message
news:137b501c44400$ef2e8a70$a401280a@.phx.gbl...
> Hi,
> How can I copy many sp's without recreate them by code
> between two databases ?
> Thanks,
> Ori|||Take a look at Data Transformation Services. Right-click Tables, select All
Tasks > Export data, and when you get to the screen that offers three
choices, select "Copy objects and data..."
The wizard is fairly self-explanatory.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"ori" <anonymous@.discussions.microsoft.com> wrote in message
news:137b501c44400$ef2e8a70$a401280a@.phx.gbl...
> Hi,
> How can I copy many sp's without recreate them by code
> between two databases ?
> Thanks,
> Ori|||Transfer via DTS?
Script and execute via Query Analyzer or Enterprise Manager?
-- Keith
"ori" <anonymous@.discussions.microsoft.com> wrote in message =news:137b501c44400$ef2e8a70$a401280a@.phx.gbl...
> Hi,
> > How can I copy many sp's without recreate them by code > between two databases ?
> > Thanks,
> > Ori|||Normal procedure is to generate a script of the required stored procedures,
and run that script on your target database.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"ori" <anonymous@.discussions.microsoft.com> wrote in message
news:137b501c44400$ef2e8a70$a401280a@.phx.gbl...
Hi,
How can I copy many sp's without recreate them by code
between two databases ?
Thanks,
Ori

Copying stored procedures

Hi, Could someone please advise me of how to copy stored procedures from one database to another?

Many thanks,

James

Hi,

which version of SQL Server are you using ? Normally I would script the procedures, get the script and execute it on the other server and voilá, you done.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

sql

copying stored procedures

Hi,
How can I copy many sp's without recreate them by code
between two databases ?
Thanks,
Ori
Use the DTS copy objects function. You can set this up easily via the
wizard. Watch out for the option to copy dependent objects as well, as you
may wind up moving a table along with a SP.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"ori" <anonymous@.discussions.microsoft.com> wrote in message
news:137b501c44400$ef2e8a70$a401280a@.phx.gbl...
> Hi,
> How can I copy many sp's without recreate them by code
> between two databases ?
> Thanks,
> Ori
|||Take a look at Data Transformation Services. Right-click Tables, select All
Tasks > Export data, and when you get to the screen that offers three
choices, select "Copy objects and data..."
The wizard is fairly self-explanatory.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"ori" <anonymous@.discussions.microsoft.com> wrote in message
news:137b501c44400$ef2e8a70$a401280a@.phx.gbl...
> Hi,
> How can I copy many sp's without recreate them by code
> between two databases ?
> Thanks,
> Ori
|||Transfer via DTS?
Script and execute via Query Analyzer or Enterprise Manager?
--=20
Keith
"ori" <anonymous@.discussions.microsoft.com> wrote in message =
news:137b501c44400$ef2e8a70$a401280a@.phx.gbl...
> Hi,
>=20
> How can I copy many sp's without recreate them by code=20
> between two databases ?
>=20
> Thanks,
>=20
> Ori
|||Normal procedure is to generate a script of the required stored procedures,
and run that script on your target database.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"ori" <anonymous@.discussions.microsoft.com> wrote in message
news:137b501c44400$ef2e8a70$a401280a@.phx.gbl...
Hi,
How can I copy many sp's without recreate them by code
between two databases ?
Thanks,
Ori

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

Tuesday, March 20, 2012

Copying procedures in SQL from one server to another using VBA in excel

Is there any sample code or help on trying to copy/replace views or prodecure in SQL or Oracle? Im using VBA in Excel...

You can use SMO to do that and in SQL Server SMO/DMO section you can find , here , an example to copy a table between different instances; i think you can adapt it for sp.

|||SMO/DMO will certainly work as ggciubuc has mentioned if you'd like to continue using VBA, however there are a number of other tools out there that will make it easier.

Management Studio has the generate scripts menu item that will script out the database object so you can run the script against another server.

Red-Gate and other tools manufacturers have compare utilities that examine the database objects from one server and compare them to the objects of another server, scripting out the differences and even applying them if you wish.

Data Transformation Services (DTS) in SQL Server 2000 has a task that allows you to migrate database objects from one server to another.

HTH...

Joe

Tuesday, March 27, 2012

Correct Procedures for Testing Against NULLs from SQL Server

Copying Views and Stored Procedures

Sunday, March 25, 2012

Copying the database

Thursday, March 22, 2012

copying stored procedures?

copying stored procedures to C drive on local machine

copying stored procedures to C drive on local machine

copying stored procedures to C drive on local machine

Copying stored procedures to another database

Copying stored procedures to another database

copying stored procedures in SQL Server 2005?

copying stored procedures in SQL Server 2005?

Copying Stored Procedures from one database to another

Copying Stored Procedures from one database to another

copying stored procedures

copying stored procedures

copying stored procedures

Copying stored procedures

copying stored procedures

Copying SQL Stored Procedures (SQL Server 2000)

Tuesday, March 20, 2012

Copying procedures in SQL from one server to another using VBA in excel

sql server db

Blog Archive

About Me