Imporving Performence

Can any body tell me what measures I have to take to improve the performance of my SQL QUERIES and Stored procedures any thing relatted to SQL?Lookup "Query Tuning" in Books Online.

blindman|||We can definitely identify optimization opportunities for a specific query, but for all of your queries you either have to do it yourself or hire a consultant to do it for you.

Importing Stored procedures from one database to another

Hi,
Any one knows how to import stored produre from one DB another?
I imporeted tables and data by using import data wizard.
but i couldnt get the SPs
Can anyone help me?
Hello,
In Enterprise manager / SSMS right click above the dataabase, Tasks,
Genenrate SQL Scripts, Choose all stored procedures
and generate a SQL Script. Open the script and execute in target database.
Thanks
Hari
<parasu0107@.gmail.com> wrote in message
news:1173874578.990775.49490@.e1g2000hsg.googlegrou ps.com...
> Hi,
> Any one knows how to import stored produre from one DB another?
> I imporeted tables and data by using import data wizard.
> but i couldnt get the SPs
> Can anyone help me?
>
sql

Importing Stored procedures from one database to another

Hi,
Any one knows how to import stored produre from one DB another?
I imporeted tables and data by using import data wizard.
but i couldnt get the SPs
Can anyone help me?Hello,
In Enterprise manager / SSMS right click above the dataabase, Tasks,
Genenrate SQL Scripts, Choose all stored procedures
and generate a SQL Script. Open the script and execute in target database.
Thanks
Hari
<parasu0107@.gmail.com> wrote in message
news:1173874578.990775.49490@.e1g2000hsg.googlegroups.com...
> Hi,
> Any one knows how to import stored produre from one DB another?
> I imporeted tables and data by using import data wizard.
> but i couldnt get the SPs
> Can anyone help me?
>

Importing Stored procedures from one database to another

Hi,
Any one knows how to import stored produre from one DB another?
I imporeted tables and data by using import data wizard.
but i couldnt get the SPs
Can anyone help me?Hello,
In Enterprise manager / SSMS right click above the dataabase, Tasks,
Genenrate SQL Scripts, Choose all stored procedures
and generate a SQL Script. Open the script and execute in target database.
Thanks
Hari
<parasu0107@.gmail.com> wrote in message
news:1173874578.990775.49490@.e1g2000hsg.googlegroups.com...
> Hi,
> Any one knows how to import stored produre from one DB another?
> I imporeted tables and data by using import data wizard.
> but i couldnt get the SPs
> Can anyone help me?
>

Importing stored procedures from 1 db to another?

Hi there,

Can anyone help?

I just recently imported my Sql server express 2005 db into the full version of sql server 2005... All went according to plan (i used import in studio management 2005 vers not 2005 express) and the data was imported but no stored procedures went with it...

it seemed to only include tables and views...

Is there another way round this?

Unfortunatley i don't have FULL access to the SQL Server 2005 as this is at my hosts... but i have full access to the sql server 2005 express which is locally on my machine

Any advice would be really appreciated

Thanks

ian

Several of the easiest ways:

1. backup the database and restore it to your new server

2. In Object Explorer, you can script out the stored procedures and apply them to the new server

3. Use snapshot replication, but I'd stick with #1 or #2 first.

|||

The previous post is correct - the easiest way to make sure you get all objects from one DB to another is to back it up and restore it to another.

If you don't own the other box that might not be possible. You've already got the data, so to get the other objects you can right-click each and script them out. You can also use the scripting model in DMO to do the same thing, but get all of the objects at once. I have a series of tutorials on that starting here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=110

Buck Woody

Carpe Datum

Importing stored procedures

Hello

Sorry if this is a stupid question, but.....

I have just upgraded to SQL2005 and I have a local database containing about 30 stored procedures, and I cant find how to upload these to the live server.

In SQL2000 there used to be an import/export option for copying objects, but this is no longer there.

Cheers
pie

Sorry, in addition when I use the copy tables and data tool, it doesn't maintain the increment fields.

This was the same for SQL2000, which is why I always used the Copy Objects tool.

pie

Importing Stored Procedures

Hi,
I have previously exported about 10 store procedures as PRC files, how would
I import them back into a SQL Server database?
Thanks.
Eric
If the files are from generating scripts in Enterprise
Manager, the PRC files are just text files of the scripts
for your stored procedures. You can open them in Query
Analyzer and execute the scripts to recreate your stored
procedures.
-Sue
On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:

>Hi,
>I have previously exported about 10 store procedures as PRC files, how would
>I import them back into a SQL Server database?
>Thanks.
>Eric
|||Thanks, Sue.
Yes, it was created from generating scripts.
Is this the official way to import files that have been created via
generated scripts? It appears that there is an export tool, but no import
tool.
Eric
"Sue Hoegemeier" wrote:

> If the files are from generating scripts in Enterprise
> Manager, the PRC files are just text files of the scripts
> for your stored procedures. You can open them in Query
> Analyzer and execute the scripts to recreate your stored
> procedures.
> -Sue
> On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
> <Eric@.discussions.microsoft.com> wrote:
>
>
|||Eric,
When you generate a script or scripts from Enterprise
Manager, it just creates SQL statements in the file or
files. You don't import scripts but you execute them. So
that's how you would do it with scripts. Generating scripts
isn't really an import/export tool.
If you are looking to move the database objects themselves
as opposed to scripting the objects, you would use DTS and
the Copy SQL Server objects tasks. Much of what that task
does under the covers is execute SQL scripts for you.
When you use which one or which one is better depends upon
what you are trying to do. Keep in mind though that most of
the objects you are working with are created using SQL so
that's a safe route to go. If you are use to working with
the database objects in Enterprise Manager, it's really just
executing SQL statements for you.
-Sue
On Mon, 31 Jan 2005 17:23:02 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks, Sue.
>Yes, it was created from generating scripts.
>Is this the official way to import files that have been created via
>generated scripts? It appears that there is an export tool, but no import
>tool.
>Eric
>"Sue Hoegemeier" wrote:
sql

Importing Stored Procedures

Hi,
I have previously exported about 10 store procedures as PRC files, how would
I import them back into a SQL Server database?
Thanks.
EricIf the files are from generating scripts in Enterprise
Manager, the PRC files are just text files of the scripts
for your stored procedures. You can open them in Query
Analyzer and execute the scripts to recreate your stored
procedures.
-Sue
On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:
>Hi,
>I have previously exported about 10 store procedures as PRC files, how would
>I import them back into a SQL Server database?
>Thanks.
>Eric|||Thanks, Sue.
Yes, it was created from generating scripts.
Is this the official way to import files that have been created via
generated scripts? It appears that there is an export tool, but no import
tool.
Eric
"Sue Hoegemeier" wrote:
> If the files are from generating scripts in Enterprise
> Manager, the PRC files are just text files of the scripts
> for your stored procedures. You can open them in Query
> Analyzer and execute the scripts to recreate your stored
> procedures.
> -Sue
> On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
> <Eric@.discussions.microsoft.com> wrote:
> >Hi,
> >
> >I have previously exported about 10 store procedures as PRC files, how would
> >I import them back into a SQL Server database?
> >
> >Thanks.
> >Eric
>|||Eric,
When you generate a script or scripts from Enterprise
Manager, it just creates SQL statements in the file or
files. You don't import scripts but you execute them. So
that's how you would do it with scripts. Generating scripts
isn't really an import/export tool.
If you are looking to move the database objects themselves
as opposed to scripting the objects, you would use DTS and
the Copy SQL Server objects tasks. Much of what that task
does under the covers is execute SQL scripts for you.
When you use which one or which one is better depends upon
what you are trying to do. Keep in mind though that most of
the objects you are working with are created using SQL so
that's a safe route to go. If you are use to working with
the database objects in Enterprise Manager, it's really just
executing SQL statements for you.
-Sue
On Mon, 31 Jan 2005 17:23:02 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:
>Thanks, Sue.
>Yes, it was created from generating scripts.
>Is this the official way to import files that have been created via
>generated scripts? It appears that there is an export tool, but no import
>tool.
>Eric
>"Sue Hoegemeier" wrote:
>> If the files are from generating scripts in Enterprise
>> Manager, the PRC files are just text files of the scripts
>> for your stored procedures. You can open them in Query
>> Analyzer and execute the scripts to recreate your stored
>> procedures.
>> -Sue
>> On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
>> <Eric@.discussions.microsoft.com> wrote:
>> >Hi,
>> >
>> >I have previously exported about 10 store procedures as PRC files, how would
>> >I import them back into a SQL Server database?
>> >
>> >Thanks.
>> >Eric
>>

Importing Stored Procedures

Hi,
I have previously exported about 10 store procedures as PRC files, how would
I import them back into a SQL Server database?
Thanks.
EricIf the files are from generating scripts in Enterprise
Manager, the PRC files are just text files of the scripts
for your stored procedures. You can open them in Query
Analyzer and execute the scripts to recreate your stored
procedures.
-Sue
On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:

>Hi,
>I have previously exported about 10 store procedures as PRC files, how woul
d
>I import them back into a SQL Server database?
>Thanks.
>Eric|||Thanks, Sue.
Yes, it was created from generating scripts.
Is this the official way to import files that have been created via
generated scripts? It appears that there is an export tool, but no import
tool.
Eric
"Sue Hoegemeier" wrote:

> If the files are from generating scripts in Enterprise
> Manager, the PRC files are just text files of the scripts
> for your stored procedures. You can open them in Query
> Analyzer and execute the scripts to recreate your stored
> procedures.
> -Sue
> On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
> <Eric@.discussions.microsoft.com> wrote:
>
>|||Eric,
When you generate a script or scripts from Enterprise
Manager, it just creates SQL statements in the file or
files. You don't import scripts but you execute them. So
that's how you would do it with scripts. Generating scripts
isn't really an import/export tool.
If you are looking to move the database objects themselves
as opposed to scripting the objects, you would use DTS and
the Copy SQL Server objects tasks. Much of what that task
does under the covers is execute SQL scripts for you.
When you use which one or which one is better depends upon
what you are trying to do. Keep in mind though that most of
the objects you are working with are created using SQL so
that's a safe route to go. If you are use to working with
the database objects in Enterprise Manager, it's really just
executing SQL statements for you.
-Sue
On Mon, 31 Jan 2005 17:23:02 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks, Sue.
>Yes, it was created from generating scripts.
>Is this the official way to import files that have been created via
>generated scripts? It appears that there is an export tool, but no import
>tool.
>Eric
>"Sue Hoegemeier" wrote:
>

Importing stored procedure

Hi,

I am trying to import a database from other server which is in sql2000 to server in sql 2005, i can successfully import all the tables but not able to import the stored procedure. Can any one suggest me the method to import stored procedure from other server.

Thanx

We deploy our sp's to multiple different database servers with a deploy (.sql) script.

So every stored procedure is also stored in this script.

Or you can use the following script:

USE MYDATABASE

SELECTname,text

FROM sysobjects

INNERJOIN syscomments

ON sysobjects.id= syscomments.id

WHERE xtype='p'ANDNameLIKE'%'

ORDERBYNAME

|||

What tool or method are you using to import the database? Is the stored procededure import failing? Do you get an error message?

An easy method to transfer a database from server to server is to backup the database and restore it to the new server - using SQL Server Management Studio (or the express version which is a free download). The tool will take care of upgrading the database from 2000 to 2005 for you behind the scenes.

|||

Hi divya4k2007,

You can script your database to a *.sql file and then run this query file in a new database. After that you will have a db with the same schema/structure as your orginal one ( All the tables, sp's will be copied but the data won't. So you need to Impor the data later).

Another easy way is to use backup/restore (if migrate direction is sql2000 to sql2005). You can also try database detach/attach,however, since i don't have sql2000 installed on my box,i'm not sure if detach/attach will work. You can refer to my another post which addresses the same issue: http://forums.asp.net/t/1144769.aspx

Hope my suggestion helps

importing store procedures, How-to

Hi,
Can anyone tell me how I can import/export stored procedures in MS SQL 2005
from a different SQL server? Used to use the right click menu on a database
(tasks, import/export) to do the job using DTS, and update all the SP and
UDF at once. Now I cannot seem to see a way of doing it using IS
(Integration Services) in 2005, only able to import/export tables and views.
Can anyone please advise, there's got to be an easy way, but I've had no
luck looking on the web or books online.
Thanks
Pritchie
If someone knows how I can export the procedures then re-import them, it
would be appreciated.
Thanks
"Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> Hi,
> Can anyone tell me how I can import/export stored procedures in MS SQL
> 2005
> from a different SQL server? Used to use the right click menu on a
> database
> (tasks, import/export) to do the job using DTS, and update all the SP and
> UDF at once. Now I cannot seem to see a way of doing it using IS
> (Integration Services) in 2005, only able to import/export tables and
> views.
> Can anyone please advise, there's got to be an easy way, but I've had no
> luck looking on the web or books online.
> Thanks
> Pritchie
>
>
|||Why not generate the scripts and then load into the new database? If 2000,
right click DB & choose "Generate SQL Script...", then choose stored
procedures, and click preview. From there, you can grab the scripts. Then
go over to your new database and copy the script into the query window and
load. If in 2005, right click DB & choose Tasks, then Generate Scripts, then
choose what you need, etc.
"Shayaan Siddiqui" wrote:

> If someone knows how I can export the procedures then re-import them, it
> would be appreciated.
> Thanks
> "Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
> message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
>
>
|||Hi CB,
Yes, this can be done, however it has a far bigger admin overhead than just
using the import/export task to transfer them. Is this now the only way to
do it? In my mind its 2 steps back after 3 steps forward... :o(
Thanks for your help CB..
Grubble to MS staff...
What happened to all the HCI/user friendly icons too in Enterprise
Manager!!! now all collections (table, views, SP, UDF) all have the same
yellow folder icon, again 2 steps back after 3 steps forward (forward as in
separating the system and user tables). Such a shame that little things
like this are missed.
Regards
Steve
"CB" <CB@.discussions.microsoft.com> wrote in message
news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> Why not generate the scripts and then load into the new database? If
2000,
> right click DB & choose "Generate SQL Script...", then choose stored
> procedures, and click preview. From there, you can grab the scripts.
Then
> go over to your new database and copy the script into the query window and
> load. If in 2005, right click DB & choose Tasks, then Generate Scripts,
then[vbcol=seagreen]
> choose what you need, etc.
> "Shayaan Siddiqui" wrote:
and[vbcol=seagreen]
no[vbcol=seagreen]
|||Unless you're looking to automate this process every day or something, I
guess I would disagree about the admin overhead, but that's ok - everybody
has their preferred way of doing things.
If you'd like to use SSIS, you can still transfer objects. When you open
Vis. Studio & choose Integration Services Project, drag over the "Transfer
SQL Server Objects Task". Double-click, then choose your source &
destination connections & databases. Scroll down to the "Objects to Copy"
property & expand it. Then click the StoredProceduresList collection (and
UDF, etc.) and choose the SPs you want to transfer. Up at the top you can
choose whether you want to drop the destination objects first, etc. (and a
whole lot of other options). Then save & choose Debug, Start Debugging if
you want to run in design mode. Or save the package off to the server &
stick in a job. Good Luck!
"Pritchie" wrote:

> Hi CB,
> Yes, this can be done, however it has a far bigger admin overhead than just
> using the import/export task to transfer them. Is this now the only way to
> do it? In my mind its 2 steps back after 3 steps forward... :o(
> Thanks for your help CB..
> Grubble to MS staff...
> What happened to all the HCI/user friendly icons too in Enterprise
> Manager!!! now all collections (table, views, SP, UDF) all have the same
> yellow folder icon, again 2 steps back after 3 steps forward (forward as in
> separating the system and user tables). Such a shame that little things
> like this are missed.
> Regards
> Steve
>
> "CB" <CB@.discussions.microsoft.com> wrote in message
> news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> 2000,
> Then
> then
> and
> no
>
>

importing store procedures, How-to

Hi,
Can anyone tell me how I can import/export stored procedures in MS SQL 2005
from a different SQL server? Used to use the right click menu on a database
(tasks, import/export) to do the job using DTS, and update all the SP and
UDF at once. Now I cannot seem to see a way of doing it using IS
(Integration Services) in 2005, only able to import/export tables and views.
Can anyone please advise, there's got to be an easy way, but I've had no
luck looking on the web or books online.
Thanks
PritchieIf someone knows how I can export the procedures then re-import them, it
would be appreciated.
Thanks
"Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> Hi,
> Can anyone tell me how I can import/export stored procedures in MS SQL
> 2005
> from a different SQL server? Used to use the right click menu on a
> database
> (tasks, import/export) to do the job using DTS, and update all the SP and
> UDF at once. Now I cannot seem to see a way of doing it using IS
> (Integration Services) in 2005, only able to import/export tables and
> views.
> Can anyone please advise, there's got to be an easy way, but I've had no
> luck looking on the web or books online.
> Thanks
> Pritchie
>
>|||Why not generate the scripts and then load into the new database? If 2000,
right click DB & choose "Generate SQL Script...", then choose stored
procedures, and click preview. From there, you can grab the scripts. Then
go over to your new database and copy the script into the query window and
load. If in 2005, right click DB & choose Tasks, then Generate Scripts, the
n
choose what you need, etc.
"Shayaan Siddiqui" wrote:

> If someone knows how I can export the procedures then re-import them, it
> would be appreciated.
> Thanks
> "Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
> message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
>
>|||Hi CB,
Yes, this can be done, however it has a far bigger admin overhead than just
using the import/export task to transfer them. Is this now the only way to
do it? In my mind its 2 steps back after 3 steps forward... :o(
Thanks for your help CB..
Grubble to MS staff...
What happened to all the HCI/user friendly icons too in Enterprise
Manager!!! now all collections (table, views, SP, UDF) all have the same
yellow folder icon, again 2 steps back after 3 steps forward (forward as in
separating the system and user tables). Such a shame that little things
like this are missed.
Regards
Steve
"CB" <CB@.discussions.microsoft.com> wrote in message
news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> Why not generate the scripts and then load into the new database? If
2000,
> right click DB & choose "Generate SQL Script...", then choose stored
> procedures, and click preview. From there, you can grab the scripts.
Then
> go over to your new database and copy the script into the query window and
> load. If in 2005, right click DB & choose Tasks, then Generate Scripts,
then[vbcol=seagreen]
> choose what you need, etc.
> "Shayaan Siddiqui" wrote:
>
and[vbcol=seagreen]
no[vbcol=seagreen]|||Unless you're looking to automate this process every day or something, I
guess I would disagree about the admin overhead, but that's ok - everybody
has their preferred way of doing things.
If you'd like to use SSIS, you can still transfer objects. When you open
Vis. Studio & choose Integration Services Project, drag over the "Transfer
SQL Server Objects Task". Double-click, then choose your source &
destination connections & databases. Scroll down to the "Objects to Copy"
property & expand it. Then click the StoredProceduresList collection (and
UDF, etc.) and choose the SPs you want to transfer. Up at the top you can
choose whether you want to drop the destination objects first, etc. (and a
whole lot of other options). Then save & choose Debug, Start Debugging if
you want to run in design mode. Or save the package off to the server &
stick in a job. Good Luck!
"Pritchie" wrote:

> Hi CB,
> Yes, this can be done, however it has a far bigger admin overhead than jus
t
> using the import/export task to transfer them. Is this now the only way t
o
> do it? In my mind its 2 steps back after 3 steps forward... :o(
> Thanks for your help CB..
> Grubble to MS staff...
> What happened to all the HCI/user friendly icons too in Enterprise
> Manager!!! now all collections (table, views, SP, UDF) all have the same
> yellow folder icon, again 2 steps back after 3 steps forward (forward as i
n
> separating the system and user tables). Such a shame that little things
> like this are missed.
> Regards
> Steve
>
> "CB" <CB@.discussions.microsoft.com> wrote in message
> news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> 2000,
> Then
> then
> and
> no
>
>sql

importing store procedures, How-to

Hi,
Can anyone tell me how I can import/export stored procedures in MS SQL 2005
from a different SQL server? Used to use the right click menu on a database
(tasks, import/export) to do the job using DTS, and update all the SP and
UDF at once. Now I cannot seem to see a way of doing it using IS
(Integration Services) in 2005, only able to import/export tables and views.
Can anyone please advise, there's got to be an easy way, but I've had no
luck looking on the web or books online.
Thanks
PritchieIf someone knows how I can export the procedures then re-import them, it
would be appreciated.
Thanks
"Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> Hi,
> Can anyone tell me how I can import/export stored procedures in MS SQL
> 2005
> from a different SQL server? Used to use the right click menu on a
> database
> (tasks, import/export) to do the job using DTS, and update all the SP and
> UDF at once. Now I cannot seem to see a way of doing it using IS
> (Integration Services) in 2005, only able to import/export tables and
> views.
> Can anyone please advise, there's got to be an easy way, but I've had no
> luck looking on the web or books online.
> Thanks
> Pritchie
>
>|||Why not generate the scripts and then load into the new database? If 2000,
right click DB & choose "Generate SQL Script...", then choose stored
procedures, and click preview. From there, you can grab the scripts. Then
go over to your new database and copy the script into the query window and
load. If in 2005, right click DB & choose Tasks, then Generate Scripts, then
choose what you need, etc.
"Shayaan Siddiqui" wrote:
> If someone knows how I can export the procedures then re-import them, it
> would be appreciated.
> Thanks
> "Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
> message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> > Hi,
> > Can anyone tell me how I can import/export stored procedures in MS SQL
> > 2005
> > from a different SQL server? Used to use the right click menu on a
> > database
> > (tasks, import/export) to do the job using DTS, and update all the SP and
> > UDF at once. Now I cannot seem to see a way of doing it using IS
> > (Integration Services) in 2005, only able to import/export tables and
> > views.
> >
> > Can anyone please advise, there's got to be an easy way, but I've had no
> > luck looking on the web or books online.
> >
> > Thanks
> > Pritchie
> >
> >
> >
>
>|||Hi CB,
Yes, this can be done, however it has a far bigger admin overhead than just
using the import/export task to transfer them. Is this now the only way to
do it? In my mind its 2 steps back after 3 steps forward... :o(
Thanks for your help CB..
Grubble to MS staff...
What happened to all the HCI/user friendly icons too in Enterprise
Manager!!! now all collections (table, views, SP, UDF) all have the same
yellow folder icon, again 2 steps back after 3 steps forward (forward as in
separating the system and user tables). Such a shame that little things
like this are missed.
Regards
Steve
"CB" <CB@.discussions.microsoft.com> wrote in message
news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> Why not generate the scripts and then load into the new database? If
2000,
> right click DB & choose "Generate SQL Script...", then choose stored
> procedures, and click preview. From there, you can grab the scripts.
Then
> go over to your new database and copy the script into the query window and
> load. If in 2005, right click DB & choose Tasks, then Generate Scripts,
then
> choose what you need, etc.
> "Shayaan Siddiqui" wrote:
> > If someone knows how I can export the procedures then re-import them, it
> > would be appreciated.
> >
> > Thanks
> >
> > "Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
> > message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> > > Hi,
> > > Can anyone tell me how I can import/export stored procedures in MS SQL
> > > 2005
> > > from a different SQL server? Used to use the right click menu on a
> > > database
> > > (tasks, import/export) to do the job using DTS, and update all the SP
and
> > > UDF at once. Now I cannot seem to see a way of doing it using IS
> > > (Integration Services) in 2005, only able to import/export tables and
> > > views.
> > >
> > > Can anyone please advise, there's got to be an easy way, but I've had
no
> > > luck looking on the web or books online.
> > >
> > > Thanks
> > > Pritchie
> > >
> > >
> > >
> >
> >
> >|||Unless you're looking to automate this process every day or something, I
guess I would disagree about the admin overhead, but that's ok - everybody
has their preferred way of doing things.
If you'd like to use SSIS, you can still transfer objects. When you open
Vis. Studio & choose Integration Services Project, drag over the "Transfer
SQL Server Objects Task". Double-click, then choose your source &
destination connections & databases. Scroll down to the "Objects to Copy"
property & expand it. Then click the StoredProceduresList collection (and
UDF, etc.) and choose the SPs you want to transfer. Up at the top you can
choose whether you want to drop the destination objects first, etc. (and a
whole lot of other options). Then save & choose Debug, Start Debugging if
you want to run in design mode. Or save the package off to the server &
stick in a job. Good Luck!
"Pritchie" wrote:
> Hi CB,
> Yes, this can be done, however it has a far bigger admin overhead than just
> using the import/export task to transfer them. Is this now the only way to
> do it? In my mind its 2 steps back after 3 steps forward... :o(
> Thanks for your help CB..
> Grubble to MS staff...
> What happened to all the HCI/user friendly icons too in Enterprise
> Manager!!! now all collections (table, views, SP, UDF) all have the same
> yellow folder icon, again 2 steps back after 3 steps forward (forward as in
> separating the system and user tables). Such a shame that little things
> like this are missed.
> Regards
> Steve
>
> "CB" <CB@.discussions.microsoft.com> wrote in message
> news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> > Why not generate the scripts and then load into the new database? If
> 2000,
> > right click DB & choose "Generate SQL Script...", then choose stored
> > procedures, and click preview. From there, you can grab the scripts.
> Then
> > go over to your new database and copy the script into the query window and
> > load. If in 2005, right click DB & choose Tasks, then Generate Scripts,
> then
> > choose what you need, etc.
> >
> > "Shayaan Siddiqui" wrote:
> >
> > > If someone knows how I can export the procedures then re-import them, it
> > > would be appreciated.
> > >
> > > Thanks
> > >
> > > "Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
> > > message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> > > > Hi,
> > > > Can anyone tell me how I can import/export stored procedures in MS SQL
> > > > 2005
> > > > from a different SQL server? Used to use the right click menu on a
> > > > database
> > > > (tasks, import/export) to do the job using DTS, and update all the SP
> and
> > > > UDF at once. Now I cannot seem to see a way of doing it using IS
> > > > (Integration Services) in 2005, only able to import/export tables and
> > > > views.
> > > >
> > > > Can anyone please advise, there's got to be an easy way, but I've had
> no
> > > > luck looking on the web or books online.
> > > >
> > > > Thanks
> > > > Pritchie
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>

Importing DTS From 2000 to 2005

Hi, I'm getting slightly frustrated...!

I've imported a couple of DTSs from 2k to 2005, now I know that they are stored in MSDB and I know that I need to export them to a file before I can work on them in SSIS.

The problem is:

How do I export them to a flat file? I've looked everywhere and it's driving me nuts!

Also I can't seem to find out how to delete them either?! I can see the data sotred in the dtspackages90 table but how do I edit/get rid of them.

Help!

SQuirreLs_r_nutz wrote:

Hi, I'm getting slightly frustrated...!

I've imported a couple of DTSs from 2k to 2005, now I know that they are stored in MSDB and I know that I need to export them to a file before I can work on them in SSIS.

The problem is:

How do I export them to a flat file? I've looked everywhere and it's driving me nuts!

You need to import the package into an existing SSIS project. Then, in Solution Explorer, right-click on "SSIS Packages" and select "Add Existing Package". From there you'll get a dialog box from which you can import your package.

Also I can't seem to find out how to delete them either?! I can see the data sotred in the dtspackages90 table but how do I edit/get rid of them.

Help!

There are two ways to manage packages: SQL Server Management Studio and Dtutil. Either of these tools can be used to delete packages stored in MSDB.

Importing and Exporting of XML Files (as Instance Data) in SQL Server 2000

Hello,
I have a SQL Server 2000 Database that feature individual records
which are stored in a table with data (essentially a large XML file
full of data) in the InstanceData field as XML.
I would appreciate it if someone could place some code that
demonstrates how to do importing and exporting? For 2 simple examples:
- Importing to a Excel Spreadsheet
- Export from a Excel Spreadsheet
Your sample code would be very much appreciated.
Also, is it possible to simply configure a ODBC Data Source for
linkage to other databases or applications?
Thank you for your kind support and assistance.
Cheers,
Colin.
You may get a better answer if you as this information in the Excel specific
newsgroup.
The data can easily be retrieved from the database or stored there. The main
issue will be to hook it up with Excel for which you will need an Excel
expert...
Best regards
Michael
"Colin Sheppard" <luddite1812@.yahoo.com> wrote in message
news:c652818c.0412010324.6ff80edd@.posting.google.c om...
> Hello,
> I have a SQL Server 2000 Database that feature individual records
> which are stored in a table with data (essentially a large XML file
> full of data) in the InstanceData field as XML.
> I would appreciate it if someone could place some code that
> demonstrates how to do importing and exporting? For 2 simple examples:
> - Importing to a Excel Spreadsheet
> - Export from a Excel Spreadsheet
> Your sample code would be very much appreciated.
> Also, is it possible to simply configure a ODBC Data Source for
> linkage to other databases or applications?
> Thank you for your kind support and assistance.
> Cheers,
> Colin.

Importing Access Reports

Hi,
I'm trying to import some access reports into Report Services.
The reports are stored in a .adp file and connect to a MS SQL server database.
I'm using the import from Reporting Services in VS.NET, but am getting the
following error on 90% of the reports.
An error occurred while the report <<report name>> was being imported: The
given path's format is not supported.
Out of about 20 reports, it only successfully imports 4. All of the reports
work in the adp file fine. Have anyone seen this error before, and possibly
provide a fix or workaround to import the reports?
Thanks in advance.Anthony, hello. I'm getting this same error. Did you ever figure this out?
I would love to figure this out.
Thanks!
Shane Eckel
shane.eckel@.seattlesoftware.com
--
Thank You!
"Anthony" wrote:
> Hi,
> I'm trying to import some access reports into Report Services.
> The reports are stored in a .adp file and connect to a MS SQL server database.
> I'm using the import from Reporting Services in VS.NET, but am getting the
> following error on 90% of the reports.
> An error occurred while the report <<report name>> was being imported: The
> given path's format is not supported.
> Out of about 20 reports, it only successfully imports 4. All of the reports
> work in the adp file fine. Have anyone seen this error before, and possibly
> provide a fix or workaround to import the reports?
> Thanks in advance.
>