Monday, March 19, 2012
Copying Indexes from one table to another table programatically
from one table to another table provided both tables have same column
deifinition? Please let me know.
On 30 May, 22:27, Sal <S...@.discussions.microsoft.com> wrote:
> Programatically in a stored proc, Is there anyway to copy/create all indexes
> from one table to another table provided both tables have same column
> deifinition? Please let me know.
Why can't you use CREATE INDEX? Script the indexes when you create the
proc.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||How can I script the indexes within the proc?
"David Portas" wrote:
> On 30 May, 22:27, Sal <S...@.discussions.microsoft.com> wrote:
> Why can't you use CREATE INDEX? Script the indexes when you create the
> proc.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
Copying Indexes from one table to another table programatically
from one table to another table provided both tables have same column
deifinition? Please let me know.On 30 May, 22:27, Sal <S...@.discussions.microsoft.com> wrote:
> Programatically in a stored proc, Is there anyway to copy/create all indexes
> from one table to another table provided both tables have same column
> deifinition? Please let me know.
Why can't you use CREATE INDEX? Script the indexes when you create the
proc.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||How can I script the indexes within the proc?
"David Portas" wrote:
> On 30 May, 22:27, Sal <S...@.discussions.microsoft.com> wrote:
> > Programatically in a stored proc, Is there anyway to copy/create all indexes
> > from one table to another table provided both tables have same column
> > deifinition? Please let me know.
> Why can't you use CREATE INDEX? Script the indexes when you create the
> proc.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
Copying Indexes from one table to another table programatically
from one table to another table provided both tables have same column
deifinition? Please let me know.On 30 May, 22:27, Sal <S...@.discussions.microsoft.com> wrote:
> Programatically in a stored proc, Is there anyway to copy/create all index
es
> from one table to another table provided both tables have same column
> deifinition? Please let me know.
Why can't you use CREATE INDEX? Script the indexes when you create the
proc.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Sunday, February 19, 2012
Copy tables on remote servers
Hi all, I'm trying to create a script / stored proc that copies one table from a remote server to another table on a different remote server.
The approach I wanted to take was...
create a stored proc that can be called remotely
the stored proc tests to see if the table exists, if it does it is dropped, if not it is created
new table structure and data copied from remote server A to remote server B
...I feel that this should be quite easy, but I must admit I'm struggling. Any help would be really appreciated.
Cheers, Jon
Did you try to setup a linked server and then do a SELECT INTO?
How about using DTS instead?
|||Thanks for your reply William.
I have tried SELECT INTO...
select * into remoteServer.intranetcms.dbo.woodford_bridge
from localServer.intranetcms.dbo.woodford_bridge
... but I get the error...
The object name 'remoteServer.intranetcms.dbo.' contains more than the maximum number of prefixes. The maximum is 2.
...I am familiar with DTS but what I would like to do is create a stored proc that can be executed inside an on_click event from a web page.
- Jon
|||In Ado.net, there is a pretty good method for doing this. The api name is SqlBulkCopy.
Let me know if you want to know more about the api.
Thanks
Bei
|||Thanks Bei, unfortunately we're not using .Net 2 so, as I understand it, can't use SQLBulkCopy. Any other ideas?
- Jon
|||Hi, maybe very late, but I ran into the same problem and solved this by doing this:
Ex:
SELECT t.Bilagnr
FROM [172.18.165.25\ASNV].[AS_14830].[dbo].[tblSalesDetails] as t WHERE Pkey > 1
Hope this helps!
- Per S.
|||I'll recommend the use of DTS. then
create an SP that triggers the DTS thru xp_cmdshell using dtsrun.
call the SP from ASP.net
Copy tables on remote servers
Hi all, I'm trying to create a script / stored proc that copies one table from a remote server to another table on a different remote server.
The approach I wanted to take was...
create a stored proc that can be called remotely the stored proc tests to see if the table exists, if it does it is dropped, if not it is created new table structure and data copied from remote server A to remote server B...I feel that this should be quite easy, but I must admit I'm struggling. Any help would be really appreciated.
Cheers, Jon
Did you try to setup a linked server and then do a SELECT INTO?
How about using DTS instead?
|||Thanks for your reply William.
I have tried SELECT INTO...
select * into remoteServer.intranetcms.dbo.woodford_bridge
from localServer.intranetcms.dbo.woodford_bridge
... but I get the error...
The object name 'remoteServer.intranetcms.dbo.' contains more than the maximum number of prefixes. The maximum is 2.
...I am familiar with DTS but what I would like to do is create a stored proc that can be executed inside an on_click event from a web page.
- Jon
|||In Ado.net, there is a pretty good method for doing this. The api name is SqlBulkCopy.
Let me know if you want to know more about the api.
Thanks
Bei
|||Thanks Bei, unfortunately we're not using .Net 2 so, as I understand it, can't use SQLBulkCopy. Any other ideas?
- Jon
|||Hi, maybe very late, but I ran into the same problem and solved this by doing this:
Ex:
SELECT t.Bilagnr
FROM [172.18.165.25\ASNV].[AS_14830].[dbo].[tblSalesDetails] as t WHERE Pkey > 1
Hope this helps!
- Per S.
|||I'll recommend the use of DTS. then
create an SP that triggers the DTS thru xp_cmdshell using dtsrun.
call the SP from ASP.net
Monday, February 13, 2012
Copy Stored proc from one DB to other
I want to copy my stored proc from one DB to other. I am wondering how
can I do it??Can I use DTS to do that?I couldn't find option in to
do it in DTS?
Thanks!
One method is to use a DTS 'Copy SQL Server Objects' task. For a single
stored proc, you might find it faster to script the object in Query Analyzer
and then run the script in the target database.
You might also consider keeping scripts for all of your objects (preferably
under source control). You can then use these scripts to promote database
changes for testing or production.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sheryl" <kiranpannu@.gmail.com> wrote in message
news:1165647570.822429.269540@.16g2000cwy.googlegro ups.com...
> Hi All,
> I want to copy my stored proc from one DB to other. I am wondering how
> can I do it??Can I use DTS to do that?I couldn't find option in to
> do it in DTS?
>
> Thanks!
>
|||Hello,
In the source sql server .. open enterprise manager.. Select database ..
right click-- all tasks-- Generate SQL Scripts...
choose all objects , Select all procedures -- permissions...Script it as a
SQL file. Then you could use this script to create procedures
in destination sql server.
Thanks
Hari
"Sheryl" <kiranpannu@.gmail.com> wrote in message
news:1165647570.822429.269540@.16g2000cwy.googlegro ups.com...
> Hi All,
> I want to copy my stored proc from one DB to other. I am wondering how
> can I do it??Can I use DTS to do that?I couldn't find option in to
> do it in DTS?
>
> Thanks!
>
|||Thanks it worked!!!!
Hari Prasad wrote:[vbcol=seagreen]
> Hello,
> In the source sql server .. open enterprise manager.. Select database ..
> right click-- all tasks-- Generate SQL Scripts...
> choose all objects , Select all procedures -- permissions...Script it as a
> SQL file. Then you could use this script to create procedures
> in destination sql server.
> Thanks
> Hari
> "Sheryl" <kiranpannu@.gmail.com> wrote in message
> news:1165647570.822429.269540@.16g2000cwy.googlegro ups.com...
|||Thanks very much!!!!!!!
Dan Guzman wrote:[vbcol=seagreen]
> One method is to use a DTS 'Copy SQL Server Objects' task. For a single
> stored proc, you might find it faster to script the object in Query Analyzer
> and then run the script in the target database.
> You might also consider keeping scripts for all of your objects (preferably
> under source control). You can then use these scripts to promote database
> changes for testing or production.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Sheryl" <kiranpannu@.gmail.com> wrote in message
> news:1165647570.822429.269540@.16g2000cwy.googlegro ups.com...
Copy Stored proc from one DB to other
I want to copy my stored proc from one DB to other. I am wondering how
can I do it'?Can I use DTS to do that?I couldn't find option in to
do it in DTS?
Thanks!One method is to use a DTS 'Copy SQL Server Objects' task. For a single
stored proc, you might find it faster to script the object in Query Analyzer
and then run the script in the target database.
You might also consider keeping scripts for all of your objects (preferably
under source control). You can then use these scripts to promote database
changes for testing or production.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sheryl" <kiranpannu@.gmail.com> wrote in message
news:1165647570.822429.269540@.16g2000cwy.googlegroups.com...
> Hi All,
> I want to copy my stored proc from one DB to other. I am wondering how
> can I do it'?Can I use DTS to do that?I couldn't find option in to
> do it in DTS?
>
> Thanks!
>|||Hello,
In the source sql server .. open enterprise manager.. Select database ..
right click-- all tasks-- Generate SQL Scripts...
choose all objects , Select all procedures -- permissions...Script it as a
SQL file. Then you could use this script to create procedures
in destination sql server.
Thanks
Hari
"Sheryl" <kiranpannu@.gmail.com> wrote in message
news:1165647570.822429.269540@.16g2000cwy.googlegroups.com...
> Hi All,
> I want to copy my stored proc from one DB to other. I am wondering how
> can I do it'?Can I use DTS to do that?I couldn't find option in to
> do it in DTS?
>
> Thanks!
>|||Thanks it worked!!!!
Hari Prasad wrote:[vbcol=seagreen]
> Hello,
> In the source sql server .. open enterprise manager.. Select database ..
> right click-- all tasks-- Generate SQL Scripts...
> choose all objects , Select all procedures -- permissions...Script it as a
> SQL file. Then you could use this script to create procedures
> in destination sql server.
> Thanks
> Hari
> "Sheryl" <kiranpannu@.gmail.com> wrote in message
> news:1165647570.822429.269540@.16g2000cwy.googlegroups.com...|||Thanks very much!!!!!!!
Dan Guzman wrote:[vbcol=seagreen]
> One method is to use a DTS 'Copy SQL Server Objects' task. For a single
> stored proc, you might find it faster to script the object in Query Analyz
er
> and then run the script in the target database.
> You might also consider keeping scripts for all of your objects (preferabl
y
> under source control). You can then use these scripts to promote database
> changes for testing or production.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Sheryl" <kiranpannu@.gmail.com> wrote in message
> news:1165647570.822429.269540@.16g2000cwy.googlegroups.com...
Copy Stored proc from one DB to other
I want to copy my stored proc from one DB to other. I am wondering how
can I do it'?Can I use DTS to do that?I couldn't find option in to
do it in DTS?
Thanks!One method is to use a DTS 'Copy SQL Server Objects' task. For a single
stored proc, you might find it faster to script the object in Query Analyzer
and then run the script in the target database.
You might also consider keeping scripts for all of your objects (preferably
under source control). You can then use these scripts to promote database
changes for testing or production.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sheryl" <kiranpannu@.gmail.com> wrote in message
news:1165647570.822429.269540@.16g2000cwy.googlegroups.com...
> Hi All,
> I want to copy my stored proc from one DB to other. I am wondering how
> can I do it'?Can I use DTS to do that?I couldn't find option in to
> do it in DTS?
>
> Thanks!
>|||Hello,
In the source sql server .. open enterprise manager.. Select database ..
right click-- all tasks-- Generate SQL Scripts...
choose all objects , Select all procedures -- permissions...Script it as a
SQL file. Then you could use this script to create procedures
in destination sql server.
Thanks
Hari
"Sheryl" <kiranpannu@.gmail.com> wrote in message
news:1165647570.822429.269540@.16g2000cwy.googlegroups.com...
> Hi All,
> I want to copy my stored proc from one DB to other. I am wondering how
> can I do it'?Can I use DTS to do that?I couldn't find option in to
> do it in DTS?
>
> Thanks!
>|||Thanks it worked!!!!
Hari Prasad wrote:
> Hello,
> In the source sql server .. open enterprise manager.. Select database ..
> right click-- all tasks-- Generate SQL Scripts...
> choose all objects , Select all procedures -- permissions...Script it as a
> SQL file. Then you could use this script to create procedures
> in destination sql server.
> Thanks
> Hari
> "Sheryl" <kiranpannu@.gmail.com> wrote in message
> news:1165647570.822429.269540@.16g2000cwy.googlegroups.com...
> > Hi All,
> >
> > I want to copy my stored proc from one DB to other. I am wondering how
> > can I do it'?Can I use DTS to do that?I couldn't find option in to
> > do it in DTS?
> >
> >
> > Thanks!
> >|||Thanks very much!!!!!!!
Dan Guzman wrote:
> One method is to use a DTS 'Copy SQL Server Objects' task. For a single
> stored proc, you might find it faster to script the object in Query Analyzer
> and then run the script in the target database.
> You might also consider keeping scripts for all of your objects (preferably
> under source control). You can then use these scripts to promote database
> changes for testing or production.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Sheryl" <kiranpannu@.gmail.com> wrote in message
> news:1165647570.822429.269540@.16g2000cwy.googlegroups.com...
> > Hi All,
> >
> > I want to copy my stored proc from one DB to other. I am wondering how
> > can I do it'?Can I use DTS to do that?I couldn't find option in to
> > do it in DTS?
> >
> >
> > Thanks!
> >