Showing posts with label transfer. Show all posts
Showing posts with label transfer. Show all posts

Friday, February 24, 2012

Best Way to transfer tables via Terminal Server ?

I have updated a number of tables at home (size is around
200MB) and I am able to connect to office via Terminal
Server.
I would like to know what is the best tools I can use to
transfer the tables to my office and update them.
Should I transfer the whole database (around 1 GB) and use
DTS on Terminal Server to update the whole database ?
Thanks
If you have any backup encryption type of software (SQL LiteSpeed) then that
would be the way to go. A gig may be painful, really a call for your
company.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:054e01c593f5$1d51fc30$a401280a@.phx.gbl...
>I have updated a number of tables at home (size is around
> 200MB) and I am able to connect to office via Terminal
> Server.
> I would like to know what is the best tools I can use to
> transfer the tables to my office and update them.
> Should I transfer the whole database (around 1 GB) and use
> DTS on Terminal Server to update the whole database ?
> Thanks
|||Dear Chris,
Is there any way that I only transfer those tables to the Terminal Server
and use DTS there to update the SQL Server ?
Thanks
Peter
"ChrisR" <noemail@.bla.com> wrote in message
news:eMhOclFlFHA.3260@.TK2MSFTNGP10.phx.gbl...
> If you have any backup encryption type of software (SQL LiteSpeed) then
> that would be the way to go. A gig may be painful, really a call for your
> company.
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:054e01c593f5$1d51fc30$a401280a@.phx.gbl...
>
|||Hi
If you don't have SQL Server port access, or file access to the remote
server, you can't transfer the data from your home machine.
Terminal server does not allow local copy to a remote server.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:eq4SdMKlFHA.2852@.TK2MSFTNGP14.phx.gbl...
> Dear Chris,
> Is there any way that I only transfer those tables to the Terminal Server
> and use DTS there to update the SQL Server ?
> Thanks
> Peter
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eMhOclFlFHA.3260@.TK2MSFTNGP10.phx.gbl...
>
|||Dear Mike,
I am able to access the Terminal Server. From the Terminal Server, I am
able to connect to the SQL Server via Remote Access. On the other hand, we
have installed Enterprise Manager on the Terminal Server.
Thanking you in anticipation.
Peter
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:Oe9ZlbNlFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi
> If you don't have SQL Server port access, or file access to the remote
> server, you can't transfer the data from your home machine.
> Terminal server does not allow local copy to a remote server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:eq4SdMKlFHA.2852@.TK2MSFTNGP14.phx.gbl...
>

Best Way to transfer tables via Terminal Server ?

I have updated a number of tables at home (size is around
200MB) and I am able to connect to office via Terminal
Server.
I would like to know what is the best tools I can use to
transfer the tables to my office and update them.
Should I transfer the whole database (around 1 GB) and use
DTS on Terminal Server to update the whole database ?
ThanksIf you have any backup encryption type of software (SQL LiteSpeed) then that
would be the way to go. A gig may be painful, really a call for your
company.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:054e01c593f5$1d51fc30$a401280a@.phx.gbl...
>I have updated a number of tables at home (size is around
> 200MB) and I am able to connect to office via Terminal
> Server.
> I would like to know what is the best tools I can use to
> transfer the tables to my office and update them.
> Should I transfer the whole database (around 1 GB) and use
> DTS on Terminal Server to update the whole database ?
> Thanks|||Dear Chris,
Is there any way that I only transfer those tables to the Terminal Server
and use DTS there to update the SQL Server ?
Thanks
Peter
"ChrisR" <noemail@.bla.com> wrote in message
news:eMhOclFlFHA.3260@.TK2MSFTNGP10.phx.gbl...
> If you have any backup encryption type of software (SQL LiteSpeed) then
> that would be the way to go. A gig may be painful, really a call for your
> company.
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:054e01c593f5$1d51fc30$a401280a@.phx.gbl...
>|||Hi
If you don't have SQL Server port access, or file access to the remote
server, you can't transfer the data from your home machine.
Terminal server does not allow local copy to a remote server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:eq4SdMKlFHA.2852@.TK2MSFTNGP14.phx.gbl...
> Dear Chris,
> Is there any way that I only transfer those tables to the Terminal Server
> and use DTS there to update the SQL Server ?
> Thanks
> Peter
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eMhOclFlFHA.3260@.TK2MSFTNGP10.phx.gbl...
>|||Dear Mike,
I am able to access the Terminal Server. From the Terminal Server, I am
able to connect to the SQL Server via Remote Access. On the other hand, we
have installed Enterprise Manager on the Terminal Server.
Thanking you in anticipation.
Peter
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:Oe9ZlbNlFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi
> If you don't have SQL Server port access, or file access to the remote
> server, you can't transfer the data from your home machine.
> Terminal server does not allow local copy to a remote server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:eq4SdMKlFHA.2852@.TK2MSFTNGP14.phx.gbl...
>

Best Way to transfer tables via Terminal Server ?

I have updated a number of tables at home (size is around
200MB) and I am able to connect to office via Terminal
Server.
I would like to know what is the best tools I can use to
transfer the tables to my office and update them.
Should I transfer the whole database (around 1 GB) and use
DTS on Terminal Server to update the whole database ?
ThanksIf you have any backup encryption type of software (SQL LiteSpeed) then that
would be the way to go. A gig may be painful, really a call for your
company.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:054e01c593f5$1d51fc30$a401280a@.phx.gbl...
>I have updated a number of tables at home (size is around
> 200MB) and I am able to connect to office via Terminal
> Server.
> I would like to know what is the best tools I can use to
> transfer the tables to my office and update them.
> Should I transfer the whole database (around 1 GB) and use
> DTS on Terminal Server to update the whole database ?
> Thanks|||Dear Chris,
Is there any way that I only transfer those tables to the Terminal Server
and use DTS there to update the SQL Server ?
Thanks
Peter
"ChrisR" <noemail@.bla.com> wrote in message
news:eMhOclFlFHA.3260@.TK2MSFTNGP10.phx.gbl...
> If you have any backup encryption type of software (SQL LiteSpeed) then
> that would be the way to go. A gig may be painful, really a call for your
> company.
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:054e01c593f5$1d51fc30$a401280a@.phx.gbl...
>>I have updated a number of tables at home (size is around
>> 200MB) and I am able to connect to office via Terminal
>> Server.
>> I would like to know what is the best tools I can use to
>> transfer the tables to my office and update them.
>> Should I transfer the whole database (around 1 GB) and use
>> DTS on Terminal Server to update the whole database ?
>> Thanks
>|||Hi
If you don't have SQL Server port access, or file access to the remote
server, you can't transfer the data from your home machine.
Terminal server does not allow local copy to a remote server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:eq4SdMKlFHA.2852@.TK2MSFTNGP14.phx.gbl...
> Dear Chris,
> Is there any way that I only transfer those tables to the Terminal Server
> and use DTS there to update the SQL Server ?
> Thanks
> Peter
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eMhOclFlFHA.3260@.TK2MSFTNGP10.phx.gbl...
>> If you have any backup encryption type of software (SQL LiteSpeed) then
>> that would be the way to go. A gig may be painful, really a call for your
>> company.
>>
>> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
>> news:054e01c593f5$1d51fc30$a401280a@.phx.gbl...
>>I have updated a number of tables at home (size is around
>> 200MB) and I am able to connect to office via Terminal
>> Server.
>> I would like to know what is the best tools I can use to
>> transfer the tables to my office and update them.
>> Should I transfer the whole database (around 1 GB) and use
>> DTS on Terminal Server to update the whole database ?
>> Thanks
>>
>|||Dear Mike,
I am able to access the Terminal Server. From the Terminal Server, I am
able to connect to the SQL Server via Remote Access. On the other hand, we
have installed Enterprise Manager on the Terminal Server.
Thanking you in anticipation.
Peter
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:Oe9ZlbNlFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi
> If you don't have SQL Server port access, or file access to the remote
> server, you can't transfer the data from your home machine.
> Terminal server does not allow local copy to a remote server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:eq4SdMKlFHA.2852@.TK2MSFTNGP14.phx.gbl...
>> Dear Chris,
>> Is there any way that I only transfer those tables to the Terminal Server
>> and use DTS there to update the SQL Server ?
>> Thanks
>> Peter
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:eMhOclFlFHA.3260@.TK2MSFTNGP10.phx.gbl...
>> If you have any backup encryption type of software (SQL LiteSpeed) then
>> that would be the way to go. A gig may be painful, really a call for
>> your company.
>>
>> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
>> news:054e01c593f5$1d51fc30$a401280a@.phx.gbl...
>>I have updated a number of tables at home (size is around
>> 200MB) and I am able to connect to office via Terminal
>> Server.
>> I would like to know what is the best tools I can use to
>> transfer the tables to my office and update them.
>> Should I transfer the whole database (around 1 GB) and use
>> DTS on Terminal Server to update the whole database ?
>> Thanks
>>
>>
>

Best Way to transfer a table

What is the best way to send someone a table - Complete definition and data.
I have tried creating a new empty database and just add the one table and
back that up but it is a huge file. I tried exporting the rows to Excel but
that will only allow 65K rows. I was surprised to see not option for xml in
E.M.
thanks,
GMS Access? FoxPro? There are dozens of ways to _send_ the data.
However, no matter what you do, unless you send the end user an MSSQL DDL
Script, they'll never really know just _exactly_ how it was in SQL Server.
Why not just send a DDL script and a fixed-width text file?
James Hokes
"Gary" <gb@.nospam.com> wrote in message
news:epSLJt0xDHA.1764@.TK2MSFTNGP10.phx.gbl...
> What is the best way to send someone a table - Complete definition and
data.
> I have tried creating a new empty database and just add the one table and
> back that up but it is a huge file. I tried exporting the rows to Excel
but
> that will only allow 65K rows. I was surprised to see not option for xml
in
> E.M.
> thanks,
> G
>|||My opinion is that the data should be sent in TDF (tab
delimited) format. Comma-separated is no good - since so
much data can have commas.
TDF's BULK INSERT perfectly.
In query analyzer, do a SELECT * From table and in the
RESULT GRID - select all / copy&paste into NOTEPAD.
This gives you the TDF data in a text file.
If you were to BULK INSERT this back into the table it
would be identical.
We have two little VB programs here called TextImport and
TextOutput that we use constantly. Part of
our "distribute" table data to a customer and back from a
customer. We use these because copy/paste and notepad do
have limitations on width that we have encountered...
>--Original Message--
>MS Access? FoxPro? There are dozens of ways to _send_
the data.
>However, no matter what you do, unless you send the end
user an MSSQL DDL
>Script, they'll never really know just _exactly_ how it
was in SQL Server.
>Why not just send a DDL script and a fixed-width text
file?
>James Hokes
>"Gary" <gb@.nospam.com> wrote in message
>news:epSLJt0xDHA.1764@.TK2MSFTNGP10.phx.gbl...
>> What is the best way to send someone a table -
Complete definition and
>data.
>> I have tried creating a new empty database and just
add the one table and
>> back that up but it is a huge file. I tried exporting
the rows to Excel
>but
>> that will only allow 65K rows. I was surprised to see
not option for xml
>in
>> E.M.
>> thanks,
>> G
>>
>
>.
>|||Even Tab is not bullet-proof.
Tab is ASCII character 9. Plenty of tabs in varchar fields, I assure you.
I'll send you a file that'll break your app right now if you like.
The only bullet-proof text file is a fixed-width one.
Absolutely bullet-proof.
Ask any mainframe DBA.
James Hokes
"Steve z" <szlamany@.antarescomputing.com> wrote in message
news:018701c3c75e$fca3c3b0$a601280a@.phx.gbl...
> My opinion is that the data should be sent in TDF (tab
> delimited) format. Comma-separated is no good - since so
> much data can have commas.
> TDF's BULK INSERT perfectly.
> In query analyzer, do a SELECT * From table and in the
> RESULT GRID - select all / copy&paste into NOTEPAD.
> This gives you the TDF data in a text file.
> If you were to BULK INSERT this back into the table it
> would be identical.
> We have two little VB programs here called TextImport and
> TextOutput that we use constantly. Part of
> our "distribute" table data to a customer and back from a
> customer. We use these because copy/paste and notepad do
> have limitations on width that we have encountered...
>
> >--Original Message--
> >MS Access? FoxPro? There are dozens of ways to _send_
> the data.
> >
> >However, no matter what you do, unless you send the end
> user an MSSQL DDL
> >Script, they'll never really know just _exactly_ how it
> was in SQL Server.
> >
> >Why not just send a DDL script and a fixed-width text
> file?
> >
> >James Hokes
> >
> >"Gary" <gb@.nospam.com> wrote in message
> >news:epSLJt0xDHA.1764@.TK2MSFTNGP10.phx.gbl...
> >> What is the best way to send someone a table -
> Complete definition and
> >data.
> >> I have tried creating a new empty database and just
> add the one table and
> >> back that up but it is a huge file. I tried exporting
> the rows to Excel
> >but
> >> that will only allow 65K rows. I was surprised to see
> not option for xml
> >in
> >> E.M.
> >> thanks,
> >> G
> >>
> >>
> >
> >
> >.
> >|||Actually if this is going to another SQL Server I would use the native
format of BCP instead of text.
--
Andrew J. Kelly SQL MVP
"James Hokes" <noemail@.noway.com> wrote in message
news:eiwloA3xDHA.2396@.TK2MSFTNGP09.phx.gbl...
> Even Tab is not bullet-proof.
> Tab is ASCII character 9. Plenty of tabs in varchar fields, I assure you.
> I'll send you a file that'll break your app right now if you like.
> The only bullet-proof text file is a fixed-width one.
> Absolutely bullet-proof.
> Ask any mainframe DBA.
> James Hokes
> "Steve z" <szlamany@.antarescomputing.com> wrote in message
> news:018701c3c75e$fca3c3b0$a601280a@.phx.gbl...
> > My opinion is that the data should be sent in TDF (tab
> > delimited) format. Comma-separated is no good - since so
> > much data can have commas.
> >
> > TDF's BULK INSERT perfectly.
> >
> > In query analyzer, do a SELECT * From table and in the
> > RESULT GRID - select all / copy&paste into NOTEPAD.
> >
> > This gives you the TDF data in a text file.
> >
> > If you were to BULK INSERT this back into the table it
> > would be identical.
> >
> > We have two little VB programs here called TextImport and
> > TextOutput that we use constantly. Part of
> > our "distribute" table data to a customer and back from a
> > customer. We use these because copy/paste and notepad do
> > have limitations on width that we have encountered...
> >
> >
> > >--Original Message--
> > >MS Access? FoxPro? There are dozens of ways to _send_
> > the data.
> > >
> > >However, no matter what you do, unless you send the end
> > user an MSSQL DDL
> > >Script, they'll never really know just _exactly_ how it
> > was in SQL Server.
> > >
> > >Why not just send a DDL script and a fixed-width text
> > file?
> > >
> > >James Hokes
> > >
> > >"Gary" <gb@.nospam.com> wrote in message
> > >news:epSLJt0xDHA.1764@.TK2MSFTNGP10.phx.gbl...
> > >> What is the best way to send someone a table -
> > Complete definition and
> > >data.
> > >> I have tried creating a new empty database and just
> > add the one table and
> > >> back that up but it is a huge file. I tried exporting
> > the rows to Excel
> > >but
> > >> that will only allow 65K rows. I was surprised to see
> > not option for xml
> > >in
> > >> E.M.
> > >> thanks,
> > >> G
> > >>
> > >>
> > >
> > >
> > >.
> > >
>|||Been a VAX mainframe programmer since late 70's - used
fixed width for 20+ years on the mainframe. Lots of
effort in mapping data - input and output sides - lots of
room for bugs (we always consider the maintenance
nightmare).
We happen to be very familiar with our data - no tabs
anywhere - and we have medical claim tables with 2.5
million rows... (program checks for tab in data anyway -
simple to do)
What we like about our method is that our VB textoutput
program puts dates in a nice format so that BULK INSERT
works without issue. But as an app development shop, we
only support a handful of datatypes - we like the variety
that SQL has, but no reason to beat up the programmers
with each ones quirks...
Our textoutput program also uses an .INI file to specify
server/database/table and even a SELECT statement if you
want to override the defaults of SELECT *. We actually
use it inside the SQL Agent to run nightly jobs that push
text files back to the legacy VAX's at several sites.
The VAX-11 BASIC programs on the mainframe only have to
cutup the input lines by looking for TAB's - so column
issues are the only ones to consider - no mapping of
specific byte positions.
We even have mainframe programs that analyze the legacy
database structures and create .SQL scripts to "CREATE
TABLE's" and text files for BULK INSERT's. We even
include the BULK INSERT statement (with proper server
path to text file) in the .SQL script created on the
mainframe.
We can take a mainframe's application data and get it
into SQL in short order with these tools.
>--Original Message--
>Even Tab is not bullet-proof.
>Tab is ASCII character 9. Plenty of tabs in varchar
fields, I assure you.
>I'll send you a file that'll break your app right now if
you like.
>The only bullet-proof text file is a fixed-width one.
>Absolutely bullet-proof.
>Ask any mainframe DBA.
>James Hokes
>"Steve z" <szlamany@.antarescomputing.com> wrote in
message
>news:018701c3c75e$fca3c3b0$a601280a@.phx.gbl...
>> My opinion is that the data should be sent in TDF (tab
>> delimited) format. Comma-separated is no good - since
so
>> much data can have commas.
>> TDF's BULK INSERT perfectly.
>> In query analyzer, do a SELECT * From table and in the
>> RESULT GRID - select all / copy&paste into NOTEPAD.
>> This gives you the TDF data in a text file.
>> If you were to BULK INSERT this back into the table it
>> would be identical.
>> We have two little VB programs here called TextImport
and
>> TextOutput that we use constantly. Part of
>> our "distribute" table data to a customer and back
from a
>> customer. We use these because copy/paste and notepad
do
>> have limitations on width that we have encountered...
>>
>> >--Original Message--
>> >MS Access? FoxPro? There are dozens of ways to _send_
>> the data.
>> >
>> >However, no matter what you do, unless you send the
end
>> user an MSSQL DDL
>> >Script, they'll never really know just _exactly_ how
it
>> was in SQL Server.
>> >
>> >Why not just send a DDL script and a fixed-width text
>> file?
>> >
>> >James Hokes
>> >
>> >"Gary" <gb@.nospam.com> wrote in message
>> >news:epSLJt0xDHA.1764@.TK2MSFTNGP10.phx.gbl...
>> >> What is the best way to send someone a table -
>> Complete definition and
>> >data.
>> >> I have tried creating a new empty database and just
>> add the one table and
>> >> back that up but it is a huge file. I tried
exporting
>> the rows to Excel
>> >but
>> >> that will only allow 65K rows. I was surprised to
see
>> not option for xml
>> >in
>> >> E.M.
>> >> thanks,
>> >> G
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>

Sunday, February 19, 2012

best way to set up development and test databases

Hi,
I would like to set up 2 copies of my database - one as a development
database and another for testing.
I have tried to use the Transfer database option but it fails consistently.
Could I simply backup my "live" database and restore it onto a blank database.
Yes.
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:133A5514-D004-4187-B3F6-CD6F724AA5D9@.microsoft.com...
> Hi,
> I would like to set up 2 copies of my database - one as a development
> database and another for testing.
> I have tried to use the Transfer database option but it fails
> consistently.
> Could I simply backup my "live" database and restore it onto a blank
> database.
>
|||If on the same machine, be sure & use the "with move" option on the
restore, so you have different mdf & ldf filenames for the OS to work
with.
ChrisR wrote:[vbcol=seagreen]
> Yes.
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:133A5514-D004-4187-B3F6-CD6F724AA5D9@.microsoft.com...
|||The same would apply if on different machines, and SQL was installed in a
different location.
<unc27932@.yahoo.com> wrote in message
news:1123608429.243967.43720@.g44g2000cwa.googlegro ups.com...
> If on the same machine, be sure & use the "with move" option on the
> restore, so you have different mdf & ldf filenames for the OS to work
> with.
> ChrisR wrote:
>

best way to set up development and test databases

Hi,
I would like to set up 2 copies of my database - one as a development
database and another for testing.
I have tried to use the Transfer database option but it fails consistently.
Could I simply backup my "live" database and restore it onto a blank databas
e.Yes.
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:133A5514-D004-4187-B3F6-CD6F724AA5D9@.microsoft.com...
> Hi,
> I would like to set up 2 copies of my database - one as a development
> database and another for testing.
> I have tried to use the Transfer database option but it fails
> consistently.
> Could I simply backup my "live" database and restore it onto a blank
> database.
>|||If on the same machine, be sure & use the "with move" option on the
restore, so you have different mdf & ldf filenames for the OS to work
with.
ChrisR wrote:[vbcol=seagreen]
> Yes.
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:133A5514-D004-4187-B3F6-CD6F724AA5D9@.microsoft.com...|||The same would apply if on different machines, and SQL was installed in a
different location.
<unc27932@.yahoo.com> wrote in message
news:1123608429.243967.43720@.g44g2000cwa.googlegroups.com...
> If on the same machine, be sure & use the "with move" option on the
> restore, so you have different mdf & ldf filenames for the OS to work
> with.
> ChrisR wrote:
>

best way to set up development and test databases

Hi,
I would like to set up 2 copies of my database - one as a development
database and another for testing.
I have tried to use the Transfer database option but it fails consistently.
Could I simply backup my "live" database and restore it onto a blank database.Yes.
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:133A5514-D004-4187-B3F6-CD6F724AA5D9@.microsoft.com...
> Hi,
> I would like to set up 2 copies of my database - one as a development
> database and another for testing.
> I have tried to use the Transfer database option but it fails
> consistently.
> Could I simply backup my "live" database and restore it onto a blank
> database.
>|||If on the same machine, be sure & use the "with move" option on the
restore, so you have different mdf & ldf filenames for the OS to work
with.
ChrisR wrote:
> Yes.
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:133A5514-D004-4187-B3F6-CD6F724AA5D9@.microsoft.com...
> > Hi,
> > I would like to set up 2 copies of my database - one as a development
> > database and another for testing.
> > I have tried to use the Transfer database option but it fails
> > consistently.
> > Could I simply backup my "live" database and restore it onto a blank
> > database.
> >|||The same would apply if on different machines, and SQL was installed in a
different location.
<unc27932@.yahoo.com> wrote in message
news:1123608429.243967.43720@.g44g2000cwa.googlegroups.com...
> If on the same machine, be sure & use the "with move" option on the
> restore, so you have different mdf & ldf filenames for the OS to work
> with.
> ChrisR wrote:
>> Yes.
>> "lindab" <lindab@.discussions.microsoft.com> wrote in message
>> news:133A5514-D004-4187-B3F6-CD6F724AA5D9@.microsoft.com...
>> > Hi,
>> > I would like to set up 2 copies of my database - one as a development
>> > database and another for testing.
>> > I have tried to use the Transfer database option but it fails
>> > consistently.
>> > Could I simply backup my "live" database and restore it onto a blank
>> > database.
>> >
>

Friday, February 10, 2012

Best way of importing Excel to SQL Server

Hello,

I nee to write something that will transfer excel data into an SQL Server
table.

I have for another database application I wrote have it importing Excel
spreadsheet data using cell by cell, row by row method. This is fully
automated so the user can choose whatever spreadsheet they want to import
and press a button which sits on a VB6 frontend.
This has been good for that situsation but it can be very slow when there
are large amounts of data to process.

I am just wondering are there any faster, better alternatives that would
still enable a user to select which excel spreadsheet to import
as the application I am writing now will sit on a website frontend, using
ASP, and I'd really like to try and speed things up if I could.

any advice would be much appreciated.

Thankyou,

Oh, and hello, this is my first post here!

JayneLittle PussyCat wrote:
> Hello,
> I nee to write something that will transfer excel data into an SQL Server
> table.
> I have for another database application I wrote have it importing Excel
> spreadsheet data using cell by cell, row by row method. This is fully
> automated so the user can choose whatever spreadsheet they want to import
> and press a button which sits on a VB6 frontend.
> This has been good for that situsation but it can be very slow when there
> are large amounts of data to process.
> I am just wondering are there any faster, better alternatives that would
> still enable a user to select which excel spreadsheet to import
> as the application I am writing now will sit on a website frontend, using
> ASP, and I'd really like to try and speed things up if I could.
> any advice would be much appreciated.
> Thankyou,
> Oh, and hello, this is my first post here!
> Jayne

DTS Packages are design just for this sort of thing.

Zach|||nib <individual_news@.nibsworld.com> wrote in message news:<2tqnibF21lmapU1@.uni-berlin.de>...
> Little PussyCat wrote:
> > Hello,
> > I nee to write something that will transfer excel data into an SQL Server
> > table.
> > I have for another database application I wrote have it importing Excel
> > spreadsheet data using cell by cell, row by row method. This is fully
> > automated so the user can choose whatever spreadsheet they want to import
> > and press a button which sits on a VB6 frontend.
> > This has been good for that situsation but it can be very slow when there
> > are large amounts of data to process.
> > I am just wondering are there any faster, better alternatives that would
> > still enable a user to select which excel spreadsheet to import
> > as the application I am writing now will sit on a website frontend, using
> > ASP, and I'd really like to try and speed things up if I could.
> > any advice would be much appreciated.
> > Thankyou,
> > Oh, and hello, this is my first post here!
> > Jayne
> DTS Packages are design just for this sort of thing.
> Zach

I second that notion -- however, there are some problems with
importing Excel data into SQL Server.

I've noticed that if you have a column whose first value is numeric,
and then there are other values in that column that are not numeric,
those values get dropped. Kind of a pain.|||>>
>> DTS Packages are design just for this sort of thing.
>>
>> Zach
>
> I second that notion -- however, there are some problems with
> importing Excel data into SQL Server.

Will a DTX package still be as flexible as what I am doing now,
i.e. would a user still be able to browse through a VB6 / webfrontend to the
spreadsheet they want to import,
i.e. can it be coded in VB6 / ASP?
> I've noticed that if you have a column whose first value is numeric,
> and then there are other values in that column that are not numeric,
> those values get dropped. Kind of a pain.

There are loads of problems with importing excel into sql server, I've found
if the spreadsheet is not set up as a text format data then strange things
will happen.
Similar when you export from sql server to excel, leading zeros of numeric
data gets dropped etc...
Anyway,
Enough of that, thanks

Jayne|||Little PussyCat wrote:
>>>DTS Packages are design just for this sort of thing.
>>>
>>>Zach
>>
>>
>>I second that notion -- however, there are some problems with
>>importing Excel data into SQL Server.
>
> Will a DTX package still be as flexible as what I am doing now,
> i.e. would a user still be able to browse through a VB6 / webfrontend to the
> spreadsheet they want to import,
> i.e. can it be coded in VB6 / ASP?

You'd have to investigate exactly how much you can do with it. Even if
DTS requires a hard-coded file name, you could always have your front
end copy the chosen spreadsheet to said name and then run the dts package.

Zach

Best Way of data transfer

Hello All,
I am a newbie dba and need some expert advice on one of my development
scenario I am currently struck with. I am in process of designing a
reporting solution for my company. It is going to be a web based
intranet thin client multitiered application using sql server 2000 and
..net platform.
This application basically shows the real time KPI's or statistics in
different forms of reports on an hourly basis to the senior managers to
right on their desktop. Eventually these reports will help them in
decision making for the better performance of the business...
My question here is what is the best way of transferring large chunk of
data (not entire table(s)) from production server (SQL 2000) to a
reporting server or staging database with an hourly refresh without
stressing the production environment?
Is it a) Replication preferably snapshot? Or
b) BCP? or
c) DTS?
Or do you suggest any better way of achieving this task?
Any suggestion or tips would be greatly appreciated.
Looking forward for your responses.
Many Thanks,
AK
I'd recommend using transactional replication - after the snapshot, it'll
just take the changes to the data.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks very much for your reply. Paul,
Is it viable to do hourly refresh through out the day using your
advised approach? Will there be any performance issues?
Thanks very much for your reply.
Ak
Paul Ibison wrote:
> I'd recommend using transactional replication - after the snapshot, it'll
> just take the changes to the data.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The log-reader will ov course affect the production system, and if you have
a publisher/distributor, there will be disk access required to write and
read from the distribution database. Exactly what this all amounts to is
difficult to say and is more empirically determined, although the section
entitled "Cost of Transactional Replication at the Publisher" in this
article will give you some idea:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx.
The other thing to take into account is the effect on reporting queries to
have the distribution agent aplying transactions, and the consequential
potential blocking issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I would use transactional replication as it offers the lowest latency.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"AK" <arshad.khan@.policyadmin.co.uk> wrote in message
news:1163424096.157922.228010@.k70g2000cwa.googlegr oups.com...
> Hello All,
> I am a newbie dba and need some expert advice on one of my development
> scenario I am currently struck with. I am in process of designing a
> reporting solution for my company. It is going to be a web based
> intranet thin client multitiered application using sql server 2000 and
> .net platform.
> This application basically shows the real time KPI's or statistics in
> different forms of reports on an hourly basis to the senior managers to
> right on their desktop. Eventually these reports will help them in
> decision making for the better performance of the business...
>
> My question here is what is the best way of transferring large chunk of
> data (not entire table(s)) from production server (SQL 2000) to a
> reporting server or staging database with an hourly refresh without
> stressing the production environment?
> Is it a) Replication preferably snapshot? Or
> b) BCP? or
> c) DTS?
> Or do you suggest any better way of achieving this task?
> Any suggestion or tips would be greatly appreciated.
> Looking forward for your responses.
>
> Many Thanks,
> AK
>
|||Thanks very much for all your replies guys.
I will give at a go this way then. Will post more queries on this
thread if i get stuck any where.
AK
Hilary Cotter wrote:[vbcol=seagreen]
> I would use transactional replication as it offers the lowest latency.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "AK" <arshad.khan@.policyadmin.co.uk> wrote in message
> news:1163424096.157922.228010@.k70g2000cwa.googlegr oups.com...