Importing Access DB: menu

Showing posts with label menu. Show all posts

Monday, March 26, 2012

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

Friday, February 24, 2012

Importing CSV Data into a SQL Server 2005 Database

I need to import csv data into a SQL Server 2005 database using SQL Server Management Studio Express. I can't find any menu options for accomplishing this. Is this a limitation of the Express edition, or am I missing something when I try to find this feature?

Thanks for any help provided.

You are missing DTS which comes with service pack 1 the thread below covers how to get it, and a CVS file is comma delimited so SQL Server sees null values so you have to import into a temp table before the destination if you have primary key defined in the destination table. Hope this helps.

http://forums.asp.net/thread/1407898.aspx

|||

Caddre: Thanks for the info. I obtained Service Pack 1 and located DTS. As I run the wizard, it looks like it is set up primarily to transfer data between databases or tables, rather than importing from csv. The only way that I see for importing from csv is to write a sql query. Is there a more direct way using the Import/Export Wizard? Or will I have to write a SQL query?

|||

That cannot be it is a smaller version of a billion dollar ETL(extraction transformation and loading) tool even the previous version can do it, so the link below shows you the steps with the 2000 version. It is not complicated just choose a temp table first or a table without primary key. Hope this helps.

http://www.sqldts.com/default.aspx?276,4

|||

The screen capture images from the link you provided look very different from what I am seeing. I did download my version as described in the other thread and the filename/path matched. I captured some screen images that show what I am seeing. Here is the URL:

http://www.vagarden.com/importwizard.html

I can see how the program shown that you linked to would do the job for me, if I can get it.

Thanks for your help.

|||

I have been searching the Internet for a downloadable version of DTS without any luck.

Since it was not provided with the Express version of SQL Server 2005, I wonder if it is part of the full (Pro) version?

If not, does anyone know where DTS can be obtained?

If not, is it possible to write a sql query that would insert data to a MS SQL Server table from a csv file? If so, what might the query look like?

Thank for any help that can be provided.

|||

Hi sorry,

There is a single query way with the OPENROWSET function but I was hoping to get you the DTS info so I forgot about this function. Try the link below for details. Hope this helps.

http://www.databasejournal.com/features/mssql/article.php/3584751

|||

Caddre, thanks for the info. This should work for my project.

Monday, March 26, 2012

importing store procedures, How-to

importing store procedures, How-to

importing store procedures, How-to

Friday, February 24, 2012

Importing CSV Data into a SQL Server 2005 Database

Importing Access DB

Blog Archive

About Me