Data transformation services: databases

Showing posts with label databases. Show all posts

Thursday, March 29, 2012

data synchronization

Hello! anybody know some tool to synchronizate data between databases (two
databases has the same structure) . I can not use replicate becouse I have
not a permanet connection between servers. Now, somebody can send me a
backup peridocally using email. And I can restore in a second database in
local and work in local with principal and second database, then send back
the backup sinchronized.
Thank you in advance and sorry for my english.
Guillermo
You might be able to use tools like RedGate software's data compare -
however they too will require a network connection.
You can replicate using merge replication or transactional replication to a
local database and then hack the msmerge_contents or msmerge_tombstone
tables for merge replication, or use sp_browsereplcmds for transactional
replication to get a list of the changes and then apply these on either
side.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Guillermo Villanueva" <guillermovil@.nospam.com> wrote in message
news:OTu6RLM4GHA.5000@.TK2MSFTNGP02.phx.gbl...
> Hello! anybody know some tool to synchronizate data between databases (two
> databases has the same structure) . I can not use replicate becouse I have
> not a permanet connection between servers. Now, somebody can send me a
> backup peridocally using email. And I can restore in a second database in
> local and work in local with principal and second database, then send back
> the backup sinchronized.
> Thank you in advance and sorry for my english.
> Guillermo
>
|||"Hilary Cotter" wrote:

> You might be able to use tools like RedGate software's data compare -
> however they too will require a network connection.
> You can replicate using merge replication or transactional replication to a
> local database and then hack the msmerge_contents or msmerge_tombstone
> tables for merge replication, or use sp_browsereplcmds for transactional
> replication to get a list of the changes and then apply these on either
> side.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Guillermo Villanueva" <guillermovil@.nospam.com> wrote in message
> news:OTu6RLM4GHA.5000@.TK2MSFTNGP02.phx.gbl...
>
>

data synchronization

Dear All,
I have 3 databases in sql , one db have a master data and I want to
synchronize to other 2 databases. is any sync. sw is available for that. or
is any thing in SQL also. databases may be in same place or remortly also
please help
thanking you
Regards
K R LalYou could write a script to keep the databases updated and run the script at
specific intervals. You may also consider replication. Take a look at
Replication in Books Online.
"Lal" <kr.lal@.luxoroffice.com> wrote in message
news:O8kSCgaCFHA.904@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> I have 3 databases in sql , one db have a master data and I want to
> synchronize to other 2 databases. is any sync. sw is available for that.
> or
> is any thing in SQL also. databases may be in same place or remortly also
> please help
> thanking you
> Regards
>
> K R Lal
>|||Lal
The question is how often do you want to get a fresh data from the master?
Replication, especially transaction replication could suite you.
Also . I'd look at BACKUP/RESTORE commands altogether with BACKUP LOG file
as well.
"Lal" <kr.lal@.luxoroffice.com> wrote in message
news:O8kSCgaCFHA.904@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> I have 3 databases in sql , one db have a master data and I want to
> synchronize to other 2 databases. is any sync. sw is available for that.
or
> is any thing in SQL also. databases may be in same place or remortly also
> please help
> thanking you
> Regards
>
> K R Lal
>|||Hello Lal.
Replication would be the defacto response although it may not suit your
environment or needs. You could also check out DB Ghost which can synchroniz
e
both data or schema or both which can be scheduled to run when you like. I
created a SQL Server job to synchronize my database with the hosting company
so I could have a reporting and disaster recovery database in case anything
went wrong with my hosting company. You can download the example here:
http://www.innovartis.co.uk/downloa...J
ob.zip
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Lal" wrote:

> Dear All,
> I have 3 databases in sql , one db have a master data and I want to
> synchronize to other 2 databases. is any sync. sw is available for that. o
r
> is any thing in SQL also. databases may be in same place or remortly also
> please help
> thanking you
> Regards
>
> K R Lal
>
>sql

data synchronization

data synchronisation

Hi,

I'm working with two databases on to physicaly separated servers: #1
is on a local machine en I can only connect to #2 via an ADSL
connection.
I need to syncrhonise the data. This works fine via the wizard from
the Enterprise Manager. But I want to get this done via a script form
the Query Analyzer that I can run whenever I want to. Something like:

insert into srv1.database.dbo.table1
select * from srv2.database.dbo.table1

When I do this I get an error about "linked servers". That surprises
me, because I have no linked servers configured and yet the wizard
works fine?

Can anyone tell me how to accomplish this?

tav
Jan Willemsjhmwillems@.hotmail.com (Jan) wrote in message news:<240176c1.0404060206.4bfaaca3@.posting.google.com>...
> Hi,
> I'm working with two databases on to physicaly separated servers: #1
> is on a local machine en I can only connect to #2 via an ADSL
> connection.
> I need to syncrhonise the data. This works fine via the wizard from
> the Enterprise Manager. But I want to get this done via a script form
> the Query Analyzer that I can run whenever I want to. Something like:
> insert into srv1.database.dbo.table1
> select * from srv2.database.dbo.table1
> When I do this I get an error about "linked servers". That surprises
> me, because I have no linked servers configured and yet the wizard
> works fine?
> Can anyone tell me how to accomplish this?
> tav
> Jan Willems

The Import/Export Data wizard uses a separate connection to each
server, so linked servers are not involved. The INSERT statement above
does require the linked servers to exist, however.

So you could just create the linked servers you need, or if that's not
possible for some reason, then you can save a DTS package from the
wizard, and execute it in whatever way is most convenient:

http://www.sqldts.com/default.aspx?104

Simon

Data storage question

I am migrating Sybase databases to SQL 2000. For the most part I am using a
Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool. The
problem that I'm running into is that the size of the databases are 3, 4 or
more times bigger in SQL then they where in Sybase. The database that I
moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know why'
Some mentioned to me to how SQL is setup in regards to number of rows per
page. I've let to find anything that will help me to look this information
up. I've been through sp_configure but I didn't see anything that jump out
at me. Any help would be nice.
TIA All.
JoeIt's probably due to lots of page splits. Did you add the indexes before or
after you imported the data? Make sure each table has a clustered index and
try reindexing the table. That should free up a lot of space and then you
can do a one time shrink to a reasonable size. You will want to reindex
again after the shrink though since the shrink moves data around again.
--
Andrew J. Kelly SQL MVP
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df4s9i$j23$1@.sxnews1.qg.com...
>I am migrating Sybase databases to SQL 2000. For the most part I am using
>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool. The
>problem that I'm running into is that the size of the databases are 3, 4 or
>more times bigger in SQL then they where in Sybase. The database that I
>moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know why'
>Some mentioned to me to how SQL is setup in regards to number of rows per
>page. I've let to find anything that will help me to look this information
>up. I've been through sp_configure but I didn't see anything that jump out
>at me. Any help would be nice.
> TIA All.
> Joe
>|||Thanks Andrew, I'll take a look and see about the indexes.
JD
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ue2C9vlrFHA.3440@.TK2MSFTNGP10.phx.gbl...
> It's probably due to lots of page splits. Did you add the indexes before
> or after you imported the data? Make sure each table has a clustered
> index and try reindexing the table. That should free up a lot of space
> and then you can do a one time shrink to a reasonable size. You will want
> to reindex again after the shrink though since the shrink moves data
> around again.
> --
> Andrew J. Kelly SQL MVP
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df4s9i$j23$1@.sxnews1.qg.com...
>>I am migrating Sybase databases to SQL 2000. For the most part I am using
>>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool.
>>The problem that I'm running into is that the size of the databases are 3,
>>4 or more times bigger in SQL then they where in Sybase. The database that
>>I moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know
>>why' Some mentioned to me to how SQL is setup in regards to number of
>>rows per page. I've let to find anything that will help me to look this
>>information up. I've been through sp_configure but I didn't see anything
>>that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>|||reindexing your database will olso increase your performance as well as it
will organize the rows splited into pages
remember to make a backup before and after of this process and that the
database will not be abailable during this time
use dbcc dbreindex instead of dbcc indexdefrag
"Joe D" wrote:
> Thanks Andrew, I'll take a look and see about the indexes.
> JD
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ue2C9vlrFHA.3440@.TK2MSFTNGP10.phx.gbl...
> > It's probably due to lots of page splits. Did you add the indexes before
> > or after you imported the data? Make sure each table has a clustered
> > index and try reindexing the table. That should free up a lot of space
> > and then you can do a one time shrink to a reasonable size. You will want
> > to reindex again after the shrink though since the shrink moves data
> > around again.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Joe D" <jkdriscoll@.qg.com> wrote in message
> > news:df4s9i$j23$1@.sxnews1.qg.com...
> >>I am migrating Sybase databases to SQL 2000. For the most part I am using
> >>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool.
> >>The problem that I'm running into is that the size of the databases are 3,
> >>4 or more times bigger in SQL then they where in Sybase. The database that
> >>I moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know
> >>why' Some mentioned to me to how SQL is setup in regards to number of
> >>rows per page. I've let to find anything that will help me to look this
> >>information up. I've been through sp_configure but I didn't see anything
> >>that jump out at me. Any help would be nice.
> >>
> >> TIA All.
> >>
> >> Joe
> >>
> >
> >
>
>|||Hi Joe
Another reason for ballooning of db size could be if you have a lot of fixed
length columns that have a lot NULL values. I believe Sybase still works
like older versions of SQL Server, and any column that is NULL takes 0
bytes. In SQL Server 7 and 2000, a fixed width column takes the full number
of bytes, whether it is NULL or not..
HTH
Kalen Delaney
www.solidqualitylearning.com
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df4s9i$j23$1@.sxnews1.qg.com...
> I am migrating Sybase databases to SQL 2000. For the most part I am using
> a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool.
> The problem that I'm running into is that the size of the databases are 3,
> 4 or more times bigger in SQL then they where in Sybase. The database that
> I moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know
> why' Some mentioned to me to how SQL is setup in regards to number of
> rows per page. I've let to find anything that will help me to look this
> information up. I've been through sp_configure but I didn't see anything
> that jump out at me. Any help would be nice.
> TIA All.
> Joe
>|||Very good point Kalen.
--
Andrew J. Kelly SQL MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23rTo0KnrFHA.464@.TK2MSFTNGP15.phx.gbl...
> Hi Joe
> Another reason for ballooning of db size could be if you have a lot of
> fixed length columns that have a lot NULL values. I believe Sybase still
> works like older versions of SQL Server, and any column that is NULL takes
> 0 bytes. In SQL Server 7 and 2000, a fixed width column takes the full
> number of bytes, whether it is NULL or not..
> HTH
> Kalen Delaney
> www.solidqualitylearning.com
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df4s9i$j23$1@.sxnews1.qg.com...
>> I am migrating Sybase databases to SQL 2000. For the most part I am
>> using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>> tool. The problem that I'm running into is that the size of the databases
>> are 3, 4 or more times bigger in SQL then they where in Sybase. The
>> database that I moved today went from 13GB in Sybase to 81GB in SQL. I'd
>> like to know why' Some mentioned to me to how SQL is setup in regards to
>> number of rows per page. I've let to find anything that will help me to
>> look this information up. I've been through sp_configure but I didn't see
>> anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>
>|||Another explanation for the increased space requirements is that clustered
index keys are also stored in non-clustered index leaf pages. These keys
are used as 'pointers' to the data. This can add up if you have wide
clustered index keys and a lot of non-clustered indexes
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df4s9i$j23$1@.sxnews1.qg.com...
>I am migrating Sybase databases to SQL 2000. For the most part I am using
>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool. The
>problem that I'm running into is that the size of the databases are 3, 4 or
>more times bigger in SQL then they where in Sybase. The database that I
>moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know why'
>Some mentioned to me to how SQL is setup in regards to number of rows per
>page. I've let to find anything that will help me to look this information
>up. I've been through sp_configure but I didn't see anything that jump out
>at me. Any help would be nice.
> TIA All.
> Joe
>|||Morning Kalen,
This was my thought but I seemed unable to find any supporting
documentation. I appreciate the feed back.
Also, Andrew's suggestion of re-indexing has proved to be of help. Now I
need to go back through the databases that I have already migrated and
re-index those.
Thanks much,
Joe
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23rTo0KnrFHA.464@.TK2MSFTNGP15.phx.gbl...
> Hi Joe
> Another reason for ballooning of db size could be if you have a lot of
> fixed length columns that have a lot NULL values. I believe Sybase still
> works like older versions of SQL Server, and any column that is NULL takes
> 0 bytes. In SQL Server 7 and 2000, a fixed width column takes the full
> number of bytes, whether it is NULL or not..
> HTH
> Kalen Delaney
> www.solidqualitylearning.com
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df4s9i$j23$1@.sxnews1.qg.com...
>> I am migrating Sybase databases to SQL 2000. For the most part I am
>> using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>> tool. The problem that I'm running into is that the size of the databases
>> are 3, 4 or more times bigger in SQL then they where in Sybase. The
>> database that I moved today went from 13GB in Sybase to 81GB in SQL. I'd
>> like to know why' Some mentioned to me to how SQL is setup in regards to
>> number of rows per page. I've let to find anything that will help me to
>> look this information up. I've been through sp_configure but I didn't see
>> anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>
>|||Are there any work around for this Dan?
Thanks for you input.
Joe
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uyGx1cprFHA.1684@.TK2MSFTNGP14.phx.gbl...
> Another explanation for the increased space requirements is that clustered
> index keys are also stored in non-clustered index leaf pages. These keys
> are used as 'pointers' to the data. This can add up if you have wide
> clustered index keys and a lot of non-clustered indexes
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df4s9i$j23$1@.sxnews1.qg.com...
>>I am migrating Sybase databases to SQL 2000. For the most part I am using
>>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool.
>>The problem that I'm running into is that the size of the databases are 3,
>>4 or more times bigger in SQL then they where in Sybase. The database that
>>I moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know
>>why' Some mentioned to me to how SQL is setup in regards to number of
>>rows per page. I've let to find anything that will help me to look this
>>information up. I've been through sp_configure but I didn't see anything
>>that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>|||Regarding index space requirements, I can only suggest that you re-evaluate
your indexing strategy. Keep in mind that although storing the clustered
index key in non-clustered indexes may increase disk space requirements, it
also reduces non-clustered index maintenance because the non-clustered
indexes won't need to change when page splits occur. Also, non-clustered
indexes can better cover certain queries.
As with any DBMS migration, you need to consider architectural differences
in your migration. A simple migration tool may get you part of the way
there but additional work is often needed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df7052$vdu$1@.sxnews1.qg.com...
> Are there any work around for this Dan?
> Thanks for you input.
> Joe
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uyGx1cprFHA.1684@.TK2MSFTNGP14.phx.gbl...
>> Another explanation for the increased space requirements is that
>> clustered index keys are also stored in non-clustered index leaf pages.
>> These keys are used as 'pointers' to the data. This can add up if you
>> have wide clustered index keys and a lot of non-clustered indexes
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Joe D" <jkdriscoll@.qg.com> wrote in message
>> news:df4s9i$j23$1@.sxnews1.qg.com...
>>I am migrating Sybase databases to SQL 2000. For the most part I am
>>using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>>tool. The problem that I'm running into is that the size of the databases
>>are 3, 4 or more times bigger in SQL then they where in Sybase. The
>>database that I moved today went from 13GB in Sybase to 81GB in SQL. I'd
>>like to know why' Some mentioned to me to how SQL is setup in regards to
>>number of rows per page. I've let to find anything that will help me to
>>look this information up. I've been through sp_configure but I didn't see
>>anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>|||Thanks Dan for your advise and it does help.
Joe
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OiG%23X3vrFHA.2588@.tk2msftngp13.phx.gbl...
> Regarding index space requirements, I can only suggest that you
> re-evaluate your indexing strategy. Keep in mind that although storing
> the clustered index key in non-clustered indexes may increase disk space
> requirements, it also reduces non-clustered index maintenance because the
> non-clustered indexes won't need to change when page splits occur. Also,
> non-clustered indexes can better cover certain queries.
> As with any DBMS migration, you need to consider architectural differences
> in your migration. A simple migration tool may get you part of the way
> there but additional work is often needed.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df7052$vdu$1@.sxnews1.qg.com...
>> Are there any work around for this Dan?
>> Thanks for you input.
>> Joe
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:uyGx1cprFHA.1684@.TK2MSFTNGP14.phx.gbl...
>> Another explanation for the increased space requirements is that
>> clustered index keys are also stored in non-clustered index leaf pages.
>> These keys are used as 'pointers' to the data. This can add up if you
>> have wide clustered index keys and a lot of non-clustered indexes
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Joe D" <jkdriscoll@.qg.com> wrote in message
>> news:df4s9i$j23$1@.sxnews1.qg.com...
>>I am migrating Sybase databases to SQL 2000. For the most part I am
>>using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>>tool. The problem that I'm running into is that the size of the
>>databases are 3, 4 or more times bigger in SQL then they where in
>>Sybase. The database that I moved today went from 13GB in Sybase to 81GB
>>in SQL. I'd like to know why' Some mentioned to me to how SQL is setup
>>in regards to number of rows per page. I've let to find anything that
>>will help me to look this information up. I've been through sp_configure
>>but I didn't see anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>>
>|||Supporting documentation?
I guess you haven't read "Inside SQL Server". :-)
It shows you down to the byte level how rows are organized on your pages for
each datatype, and even has examples for null columns of large width. It
also provides the tools for you to examine the page layouts for yourself so
you don't have to take anybody's word for it. You can answer you own
questions.
The book also talks a lot about indexing strategies. (And yes, I am the
author.)
HTH
Kalen Delaney
www.solidqualitylearning.com
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df7001$vdq$1@.sxnews1.qg.com...
> Morning Kalen,
> This was my thought but I seemed unable to find any supporting
> documentation. I appreciate the feed back.
> Also, Andrew's suggestion of re-indexing has proved to be of help. Now
> I need to go back through the databases that I have already migrated and
> re-index those.
> Thanks much,
> Joe
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23rTo0KnrFHA.464@.TK2MSFTNGP15.phx.gbl...
>> Hi Joe
>> Another reason for ballooning of db size could be if you have a lot of
>> fixed length columns that have a lot NULL values. I believe Sybase still
>> works like older versions of SQL Server, and any column that is NULL
>> takes 0 bytes. In SQL Server 7 and 2000, a fixed width column takes the
>> full number of bytes, whether it is NULL or not..
>> HTH
>> Kalen Delaney
>> www.solidqualitylearning.com
>>
>> "Joe D" <jkdriscoll@.qg.com> wrote in message
>> news:df4s9i$j23$1@.sxnews1.qg.com...
>> I am migrating Sybase databases to SQL 2000. For the most part I am
>> using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>> tool. The problem that I'm running into is that the size of the
>> databases are 3, 4 or more times bigger in SQL then they where in
>> Sybase. The database that I moved today went from 13GB in Sybase to 81GB
>> in SQL. I'd like to know why' Some mentioned to me to how SQL is setup
>> in regards to number of rows per page. I've let to find anything that
>> will help me to look this information up. I've been through sp_configure
>> but I didn't see anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>>
>
>

Data Space Problem

Hello:
I have several SQL 2000 databases that displays 0 space
available when I view the properties of the database.
However when I change to the taskpad view, the view
indicates that there is space available.
Is this some kind of bug in SQL Server? Is there any fix
for it?
Any help would be appreciated?
Brennanrun DBCC UPDATEUSAGE and see if that helps.
Andrew J. Kelly SQL MVP
"Brennan" <anonymous@.discussions.microsoft.com> wrote in message
news:59ea01c40090$b4eedc60$a401280a@.phx.gbl...
> Hello:
> I have several SQL 2000 databases that displays 0 space
> available when I view the properties of the database.
> However when I change to the taskpad view, the view
> indicates that there is space available.
> Is this some kind of bug in SQL Server? Is there any fix
> for it?
> Any help would be appreciated?
> Brennan|||Thanks
That worked
Brennan

>--Original Message--
>run DBCC UPDATEUSAGE and see if that helps.
>--
>Andrew J. Kelly SQL MVP
>
>"Brennan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:59ea01c40090$b4eedc60$a401280a@.phx.gbl...
fix
>
>.
>

Data Space Problem

Hello:
I have several SQL 2000 databases that displays 0 space
available when I view the properties of the database.
However when I change to the taskpad view, the view
indicates that there is space available.
Is this some kind of bug in SQL Server? Is there any fix
for it?
Any help would be appreciated?
Brennanrun DBCC UPDATEUSAGE and see if that helps.
--
Andrew J. Kelly SQL MVP
"Brennan" <anonymous@.discussions.microsoft.com> wrote in message
news:59ea01c40090$b4eedc60$a401280a@.phx.gbl...
> Hello:
> I have several SQL 2000 databases that displays 0 space
> available when I view the properties of the database.
> However when I change to the taskpad view, the view
> indicates that there is space available.
> Is this some kind of bug in SQL Server? Is there any fix
> for it?
> Any help would be appreciated?
> Brennan|||Thanks
That worked
Brennan
>--Original Message--
>run DBCC UPDATEUSAGE and see if that helps.
>--
>Andrew J. Kelly SQL MVP
>
>"Brennan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:59ea01c40090$b4eedc60$a401280a@.phx.gbl...
>> Hello:
>> I have several SQL 2000 databases that displays 0 space
>> available when I view the properties of the database.
>> However when I change to the taskpad view, the view
>> indicates that there is space available.
>> Is this some kind of bug in SQL Server? Is there any
fix
>> for it?
>> Any help would be appreciated?
>> Brennan
>
>.
>

Tuesday, March 27, 2012

Data Source View and Report Model

Guys,

I created a Data Source View that uses two different Data Source connections to two different databases. I was able to drop in the tables in the data source view designer from the two databases, but when I try to create the Report Model based on that data source view, it's complaining that it can't find table in the non-primary data source from the data source view.

Here is the error I'm getting:

An error occurred while executing a command.

Message: Invalid object name 'dbo.[tablename]'

Command:

SELECT COUNT(*) FROM [dbo].[tablename] t

Why can't it find the database to connect to since the data source view is already creating using two different databases. No problem in the data source view, but when it actually becomes a model, it complains. How come it can't find one of the tables in a different database?

Please help and any help is appreciated.

Enkh.

semantic queries currently do not support multiple data sources. you can create named query that would reference table from another database using db.schema.tablename notation.

Thursday, March 22, 2012

Data Retrieval

Our production environment has multiple SQL Servers installed and running and
i need to retrieve data from multiple different servers and databases. We
have established multiple Web Service DAL's to retrieve data for out
applications but i cannot see how to leverage that into Reporting Services.
Any ideas?
Thanks in advance
Goaler...Read up on data processing extentions. That should fullfill your needs.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"goaler" <goaler@.discussions.microsoft.com> wrote in message
news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
> Our production environment has multiple SQL Servers installed and running
and
> i need to retrieve data from multiple different servers and databases. We
> have established multiple Web Service DAL's to retrieve data for out
> applications but i cannot see how to leverage that into Reporting
Services.
> Any ideas?
> Thanks in advance
> Goaler...|||Thaks Bruce
"Bruce L-C [MVP]" wrote:
> Read up on data processing extentions. That should fullfill your needs.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "goaler" <goaler@.discussions.microsoft.com> wrote in message
> news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
> > Our production environment has multiple SQL Servers installed and running
> and
> > i need to retrieve data from multiple different servers and databases. We
> > have established multiple Web Service DAL's to retrieve data for out
> > applications but i cannot see how to leverage that into Reporting
> Services.
> >
> > Any ideas?
> >
> > Thanks in advance
> > Goaler...
>
>|||As a follow-up, I think i need a bit more clarification and understanding.
Here is the issue
I need to be able to read multiple predefined shared data sources from
within MS-Reporting Services, to avoid the configuration of connection
information out side of the product. Then to combine the result set and
return it into a new report object.
There must be a way to do this, the designer allows you to create multiple
data sources but you cannot join the data together. Additionally, linked
servers are not an option in my environment.
Thanks again,
"Bruce L-C [MVP]" wrote:
> Read up on data processing extentions. That should fullfill your needs.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "goaler" <goaler@.discussions.microsoft.com> wrote in message
> news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
> > Our production environment has multiple SQL Servers installed and running
> and
> > i need to retrieve data from multiple different servers and databases. We
> > have established multiple Web Service DAL's to retrieve data for out
> > applications but i cannot see how to leverage that into Reporting
> Services.
> >
> > Any ideas?
> >
> > Thanks in advance
> > Goaler...
>
>|||You can not join datasets together.
Your clarification confused me, I thought you had web services that you
wanted to use to return the data? If so then data processing extensions are
the way to go.
Bruce Loehle-Conger
"goaler" <goaler@.discussions.microsoft.com> wrote in message
news:6C959BD5-C681-4E15-96EE-78EA69C282BA@.microsoft.com...
> As a follow-up, I think i need a bit more clarification and understanding.
> Here is the issue
> I need to be able to read multiple predefined shared data sources from
> within MS-Reporting Services, to avoid the configuration of connection
> information out side of the product. Then to combine the result set and
> return it into a new report object.
> There must be a way to do this, the designer allows you to create multiple
> data sources but you cannot join the data together. Additionally, linked
> servers are not an option in my environment.
> Thanks again,
> "Bruce L-C [MVP]" wrote:
>> Read up on data processing extentions. That should fullfill your needs.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "goaler" <goaler@.discussions.microsoft.com> wrote in message
>> news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
>> > Our production environment has multiple SQL Servers installed and
>> > running
>> and
>> > i need to retrieve data from multiple different servers and databases.
>> > We
>> > have established multiple Web Service DAL's to retrieve data for out
>> > applications but i cannot see how to leverage that into Reporting
>> Services.
>> >
>> > Any ideas?
>> >
>> > Thanks in advance
>> > Goaler...
>>

Thursday, March 8, 2012

Data missing after transaction committed

We have discovered a very strange anomaly in one of our databases yesterday.
The database is being run on SQL 2000, and has been running problem free for
about 5 years, until an isolated event yesterday.
In simple terms, a transaction that has been committed is "missing" from the
database.
The normal steps for our transaction are as follows.
1. Begin a transaction.
2. Update some records in some tables.
3. Write a new record to the database in another table.
4. If all successful, Commit transaction. Read the data from written in step
3, and print out a hard copy. Included in the hardcopy is the ID field
(primary Key) of the row written in step 3.
5. If any step fails, roll back transaction, give message to the user.
Yesterday, we had a printout generated. The printout happens after the
commit, is a totally separate step, and is read from the data that is
written in step 3. At some point shortly there after, that record has
"disappeared" from the database. (Also, the Updated rows from step 2 were
not updated like they should be. They looked as they would have if the
transaction had never been committed.)
There is no way for that record to be deleted by the user, and I can say
with certainty a sysadmin didn't delete it, as I am the only user with admin
rights to do something like that, and I know I didn't execute a delete on
that row (let alone un-do the updates in step 2 above).
Now, there is where it gets more interesting. At around the same this
happened, we had blocking situation. Essencially, another transaction had
started, and locked a row on an unrelated table. That users network
connection went down before his transaction could be completed. Since the
lock being placed on the table was affecting other users (who needed to read
from that table) we killed the process of that connection.
Now, my questions are:
1. It seems like more than a coincidence that these 2 events happened at the
same time, but they happened at completly different and unrelated tables.
Can anyone think of a reason something like this might happen?
2. How could this have happened at all? I was under the assumption that once
a transaction was committed, there was no way it could not be in the
database?
3. Is there anything I can do to prevent this from happening in the future?
Thanks for any suggestions.
-Ryan
> 1. It seems like more than a coincidence that these 2 events happened at
> the same time, but they happened at completly different and unrelated
> tables. Can anyone think of a reason something like this might happen?
Does your application maintain persistent database connections? If that is
the case, consider that the blocking episode could have caused a client-side
timeout. The transaction that was in progress when the timeout occurred
might not have gotten rolled back and all subsequent work done on that
connection was performed in the context of a transaction that was never
committed.

> 2. How could this have happened at all? I was under the assumption that
> once a transaction was committed, there was no way it could not be in the
> database?
A committed transaction is durable. However, note that COMMIT only
decrements @.@.TRANCOUNT; the transaction is not durable until @.@.TRANCOUNT
reaches zero.

> 3. Is there anything I can do to prevent this from happening in the
> future?
One method is to SET XACT_ABORT ON, which will roll back the transaction in
the event of a timeout. This is especially important when you issue BEGIN
TRAN in stored procedure code. I also believe some client APIs have the
smarts to issue a ROLLBACK following a detected exception as long as the
transaction was started via the API rather than a Transact-SQL BEGIN TRAN.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:ObACMuiVHHA.4832@.TK2MSFTNGP04.phx.gbl...
> We have discovered a very strange anomaly in one of our databases
> yesterday.
> The database is being run on SQL 2000, and has been running problem free
> for about 5 years, until an isolated event yesterday.
> In simple terms, a transaction that has been committed is "missing" from
> the database.
> The normal steps for our transaction are as follows.
> 1. Begin a transaction.
> 2. Update some records in some tables.
> 3. Write a new record to the database in another table.
> 4. If all successful, Commit transaction. Read the data from written in
> step 3, and print out a hard copy. Included in the hardcopy is the ID
> field (primary Key) of the row written in step 3.
> 5. If any step fails, roll back transaction, give message to the user.
> Yesterday, we had a printout generated. The printout happens after the
> commit, is a totally separate step, and is read from the data that is
> written in step 3. At some point shortly there after, that record has
> "disappeared" from the database. (Also, the Updated rows from step 2 were
> not updated like they should be. They looked as they would have if the
> transaction had never been committed.)
> There is no way for that record to be deleted by the user, and I can say
> with certainty a sysadmin didn't delete it, as I am the only user with
> admin rights to do something like that, and I know I didn't execute a
> delete on that row (let alone un-do the updates in step 2 above).
> Now, there is where it gets more interesting. At around the same this
> happened, we had blocking situation. Essencially, another transaction had
> started, and locked a row on an unrelated table. That users network
> connection went down before his transaction could be completed. Since the
> lock being placed on the table was affecting other users (who needed to
> read from that table) we killed the process of that connection.
> Now, my questions are:
> 1. It seems like more than a coincidence that these 2 events happened at
> the same time, but they happened at completly different and unrelated
> tables. Can anyone think of a reason something like this might happen?
> 2. How could this have happened at all? I was under the assumption that
> once a transaction was committed, there was no way it could not be in the
> database?
> 3. Is there anything I can do to prevent this from happening in the
> future?
> Thanks for any suggestions.
> -Ryan
>

Data missing after transaction committed

We have discovered a very strange anomaly in one of our databases yesterday.
The database is being run on SQL 2000, and has been running problem free for
about 5 years, until an isolated event yesterday.
In simple terms, a transaction that has been committed is "missing" from the
database.
The normal steps for our transaction are as follows.
1. Begin a transaction.
2. Update some records in some tables.
3. Write a new record to the database in another table.
4. If all successful, Commit transaction. Read the data from written in step
3, and print out a hard copy. Included in the hardcopy is the ID field
(primary Key) of the row written in step 3.
5. If any step fails, roll back transaction, give message to the user.
Yesterday, we had a printout generated. The printout happens after the
commit, is a totally separate step, and is read from the data that is
written in step 3. At some point shortly there after, that record has
"disappeared" from the database. (Also, the Updated rows from step 2 were
not updated like they should be. They looked as they would have if the
transaction had never been committed.)
There is no way for that record to be deleted by the user, and I can say
with certainty a sysadmin didn't delete it, as I am the only user with admin
rights to do something like that, and I know I didn't execute a delete on
that row (let alone un-do the updates in step 2 above).
Now, there is where it gets more interesting. At around the same this
happened, we had blocking situation. Essencially, another transaction had
started, and locked a row on an unrelated table. That users network
connection went down before his transaction could be completed. Since the
lock being placed on the table was affecting other users (who needed to read
from that table) we killed the process of that connection.
Now, my questions are:
1. It seems like more than a coincidence that these 2 events happened at the
same time, but they happened at completly different and unrelated tables.
Can anyone think of a reason something like this might happen?
2. How could this have happened at all? I was under the assumption that once
a transaction was committed, there was no way it could not be in the
database?
3. Is there anything I can do to prevent this from happening in the future?
Thanks for any suggestions.
-Ryan> 1. It seems like more than a coincidence that these 2 events happened at
> the same time, but they happened at completly different and unrelated
> tables. Can anyone think of a reason something like this might happen?
Does your application maintain persistent database connections? If that is
the case, consider that the blocking episode could have caused a client-side
timeout. The transaction that was in progress when the timeout occurred
might not have gotten rolled back and all subsequent work done on that
connection was performed in the context of a transaction that was never
committed.

> 2. How could this have happened at all? I was under the assumption that
> once a transaction was committed, there was no way it could not be in the
> database?
A committed transaction is durable. However, note that COMMIT only
decrements @.@.TRANCOUNT; the transaction is not durable until @.@.TRANCOUNT
reaches zero.

> 3. Is there anything I can do to prevent this from happening in the
> future?
One method is to SET XACT_ABORT ON, which will roll back the transaction in
the event of a timeout. This is especially important when you issue BEGIN
TRAN in stored procedure code. I also believe some client APIs have the
smarts to issue a ROLLBACK following a detected exception as long as the
transaction was started via the API rather than a Transact-SQL BEGIN TRAN.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:ObACMuiVHHA.4832@.TK2MSFTNGP04.phx.gbl...
> We have discovered a very strange anomaly in one of our databases
> yesterday.
> The database is being run on SQL 2000, and has been running problem free
> for about 5 years, until an isolated event yesterday.
> In simple terms, a transaction that has been committed is "missing" from
> the database.
> The normal steps for our transaction are as follows.
> 1. Begin a transaction.
> 2. Update some records in some tables.
> 3. Write a new record to the database in another table.
> 4. If all successful, Commit transaction. Read the data from written in
> step 3, and print out a hard copy. Included in the hardcopy is the ID
> field (primary Key) of the row written in step 3.
> 5. If any step fails, roll back transaction, give message to the user.
> Yesterday, we had a printout generated. The printout happens after the
> commit, is a totally separate step, and is read from the data that is
> written in step 3. At some point shortly there after, that record has
> "disappeared" from the database. (Also, the Updated rows from step 2 were
> not updated like they should be. They looked as they would have if the
> transaction had never been committed.)
> There is no way for that record to be deleted by the user, and I can say
> with certainty a sysadmin didn't delete it, as I am the only user with
> admin rights to do something like that, and I know I didn't execute a
> delete on that row (let alone un-do the updates in step 2 above).
> Now, there is where it gets more interesting. At around the same this
> happened, we had blocking situation. Essencially, another transaction had
> started, and locked a row on an unrelated table. That users network
> connection went down before his transaction could be completed. Since the
> lock being placed on the table was affecting other users (who needed to
> read from that table) we killed the process of that connection.
> Now, my questions are:
> 1. It seems like more than a coincidence that these 2 events happened at
> the same time, but they happened at completly different and unrelated
> tables. Can anyone think of a reason something like this might happen?
> 2. How could this have happened at all? I was under the assumption that
> once a transaction was committed, there was no way it could not be in the
> database?
> 3. Is there anything I can do to prevent this from happening in the
> future?
> Thanks for any suggestions.
> -Ryan
>

Data missing after transaction committed

We have discovered a very strange anomaly in one of our databases yesterday.
The database is being run on SQL 2000, and has been running problem free for
about 5 years, until an isolated event yesterday.
In simple terms, a transaction that has been committed is "missing" from the
database.
The normal steps for our transaction are as follows.
1. Begin a transaction.
2. Update some records in some tables.
3. Write a new record to the database in another table.
4. If all successful, Commit transaction. Read the data from written in step
3, and print out a hard copy. Included in the hardcopy is the ID field
(primary Key) of the row written in step 3.
5. If any step fails, roll back transaction, give message to the user.
Yesterday, we had a printout generated. The printout happens after the
commit, is a totally separate step, and is read from the data that is
written in step 3. At some point shortly there after, that record has
"disappeared" from the database. (Also, the Updated rows from step 2 were
not updated like they should be. They looked as they would have if the
transaction had never been committed.)
There is no way for that record to be deleted by the user, and I can say
with certainty a sysadmin didn't delete it, as I am the only user with admin
rights to do something like that, and I know I didn't execute a delete on
that row (let alone un-do the updates in step 2 above).
Now, there is where it gets more interesting. At around the same this
happened, we had blocking situation. Essencially, another transaction had
started, and locked a row on an unrelated table. That users network
connection went down before his transaction could be completed. Since the
lock being placed on the table was affecting other users (who needed to read
from that table) we killed the process of that connection.
Now, my questions are:
1. It seems like more than a coincidence that these 2 events happened at the
same time, but they happened at completly different and unrelated tables.
Can anyone think of a reason something like this might happen?
2. How could this have happened at all? I was under the assumption that once
a transaction was committed, there was no way it could not be in the
database?
3. Is there anything I can do to prevent this from happening in the future?
Thanks for any suggestions.
-Ryan> 1. It seems like more than a coincidence that these 2 events happened at
> the same time, but they happened at completly different and unrelated
> tables. Can anyone think of a reason something like this might happen?
Does your application maintain persistent database connections? If that is
the case, consider that the blocking episode could have caused a client-side
timeout. The transaction that was in progress when the timeout occurred
might not have gotten rolled back and all subsequent work done on that
connection was performed in the context of a transaction that was never
committed.
> 2. How could this have happened at all? I was under the assumption that
> once a transaction was committed, there was no way it could not be in the
> database?
A committed transaction is durable. However, note that COMMIT only
decrements @.@.TRANCOUNT; the transaction is not durable until @.@.TRANCOUNT
reaches zero.
> 3. Is there anything I can do to prevent this from happening in the
> future?
One method is to SET XACT_ABORT ON, which will roll back the transaction in
the event of a timeout. This is especially important when you issue BEGIN
TRAN in stored procedure code. I also believe some client APIs have the
smarts to issue a ROLLBACK following a detected exception as long as the
transaction was started via the API rather than a Transact-SQL BEGIN TRAN.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:ObACMuiVHHA.4832@.TK2MSFTNGP04.phx.gbl...
> We have discovered a very strange anomaly in one of our databases
> yesterday.
> The database is being run on SQL 2000, and has been running problem free
> for about 5 years, until an isolated event yesterday.
> In simple terms, a transaction that has been committed is "missing" from
> the database.
> The normal steps for our transaction are as follows.
> 1. Begin a transaction.
> 2. Update some records in some tables.
> 3. Write a new record to the database in another table.
> 4. If all successful, Commit transaction. Read the data from written in
> step 3, and print out a hard copy. Included in the hardcopy is the ID
> field (primary Key) of the row written in step 3.
> 5. If any step fails, roll back transaction, give message to the user.
> Yesterday, we had a printout generated. The printout happens after the
> commit, is a totally separate step, and is read from the data that is
> written in step 3. At some point shortly there after, that record has
> "disappeared" from the database. (Also, the Updated rows from step 2 were
> not updated like they should be. They looked as they would have if the
> transaction had never been committed.)
> There is no way for that record to be deleted by the user, and I can say
> with certainty a sysadmin didn't delete it, as I am the only user with
> admin rights to do something like that, and I know I didn't execute a
> delete on that row (let alone un-do the updates in step 2 above).
> Now, there is where it gets more interesting. At around the same this
> happened, we had blocking situation. Essencially, another transaction had
> started, and locked a row on an unrelated table. That users network
> connection went down before his transaction could be completed. Since the
> lock being placed on the table was affecting other users (who needed to
> read from that table) we killed the process of that connection.
> Now, my questions are:
> 1. It seems like more than a coincidence that these 2 events happened at
> the same time, but they happened at completly different and unrelated
> tables. Can anyone think of a reason something like this might happen?
> 2. How could this have happened at all? I was under the assumption that
> once a transaction was committed, there was no way it could not be in the
> database?
> 3. Is there anything I can do to prevent this from happening in the
> future?
> Thanks for any suggestions.
> -Ryan
>

Wednesday, March 7, 2012

Data Mining

Hi,

I've come along way learning about databases and SQL. I can write basic queries now. Even some with subQueries. What I need to learn, is how to approach data mining. Can someone suggest the best path to follow, to learn how to accomplish data mining from a very large database?
I don't just need to produce reports of acquired data. I need to go in and grab data and look for patterns against known result sets. I hope that makes sense.

Thanks,
MilfredoWell, do you have a basic grounding in statistics?
TSQL lends itself fairly well to exception reporting, but unfortunately it does not have a rich function set for correlational studies. I've written my own code for simple linear regression, but I haven't attempted true multi-linear analysis.
SQL Server 2005 has the CLR toolkit that allows you to build custom aggregate functions, so it may prove more useful for data analysis and I wouldn't be surprised to see 3rd party developers marketing more advanced statistical functions. In the meantime, you will need to spin off subsets of data for further analysis is a stronger statistical tool, such as Excel or SAS.|||Thanks Blindman,

I'm looking into some third party software. There seems to be several things on the market that will alllow me to drill down into my data and do some analysis. For single user's the price isn't bad.

Milfredo|||If you are just looking at pivoting and drill-downs, a simple Excel pivot table linked to a SQL Server view might get you started.|||Thanks. I actually over simplified my project. I have a fairly extensive database, that I need to work with. I need to select certain data set and see what factors that I can add that will make it more predictive. I'm test driving a software called Purple Mineset at present.

Milfredo

Data Migration using Transactional Replication

I need to move my database servers from one location to another. The issue is that I have over 200 databases to move and my clients can't afford a downtime. The collective volume of all the databases is over 2.5 TB and growing.

I am thinking to copy these databases in batches over the WAN to the new location and replicate them using Transactional replication till I have all the databases moved and synchronized.

Will it be wise enough to use replication for synchronizing 200 databases or is there a better approach which I can use to move these databases with minimum downtime and compromise on performance of applications.

Note: Migration is from SQL Server 2000 to SQL Server 2005.

Thanks in advance.

Riteshi do not think replication is going to be easy and effective solution for 200 DB with 2.5 TB. replication has its own set of rules, issues and overheads. better move a few db at a time (backup/restore or detach/attach) and then point the application using those databases to the new db server.

Saturday, February 25, 2012

Data Migration :- SQL Server1 - SQL Server2

Hi,
I have 2 similar sql server databases DB1 and DB2 with around 450 tables and much data. My problem is I need to copy specific records from all tables in DB1 to corresponding tables in DB2. What I have done right now is, running seperate INSERT scripts for each table like

INSERT INTO DB2..table1 SELECT * from DB1..table1 where code='XX'

I would like to know whether this is the right approach or any other better way or tool available to do so. Also since the no of records are very high, I insert it in blocks say 30,000 records each, so that log file limit will not create problem.

Thanks in advance. Please help

I would use integration Service for a job like this. I nice easy way to start this is to rightclick the database -> tasks ->export data and follow the wizard.

The outcome from this will be an integration service packagde that following can be edited in SQL Server Business Intelligence Development Studio as a normal Integration service object.

Take a look into the toturials in there. They helped me a lot.

see also this http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=631236&SiteID=1

|||

other alternatives could be

define views and bcp the data out from DB1 and in to DB2

copy the database files (they should not be accessible) and attach them as DB2

Integration Services and use the Database Copy Task

Regards

Norbert

|||

you can use snapshot replication with row filter

data migration

Hi all.
I need to to a lot of data migration from excel spreadsheets, access
databases, and SQL Server databases into a SQL Server database.
In the past I would have achieved this using a combination of linked
servers and ad hoc connections using opendatasource(). However my new
site is having trouble with Microsoft.Jet.OLEDB.4.0 and I'm worried
that I may have to come up with a different way to do the migration.
Can anyone let me know any other ways (other than linked servers and
DTS - which is not flexible enough for my situation here) that I can
get data from Excel and Access into SQL Server?
No idea is too simple.
Thanks,
Josh.
Hi Josh,
Well I tend to create insert statements in the excel sheet or access query.
That always works for me
Statement formula's usually look like
="insert into Table(Col1,Col2,Col3) values('" & a1 & "'," & b1 & ",'" & c1 &
"')"
then I copy the values into notepad and then into ISQL
I hope this helps
Greg O
Looking for SQL Scripts, Need Help? http://www.SQL-Scripts.com
Document all your databases now.
http://www.ag-software.com/ags_scribe_index.aspx
Crosstab queries from SQL Server.
http://www.ag-software.com/xp_ags_crosstab.aspx
Edit Extended Properties as easy as can be.
http://www.ag-software.com/ags_SSEPE_index.aspx
"Josh White" <whitegoose@.inorbit.com> wrote in message
news:aafea0a8.0404201812.196002df@.posting.google.c om...
> Hi all.
> I need to to a lot of data migration from excel spreadsheets, access
> databases, and SQL Server databases into a SQL Server database.
> In the past I would have achieved this using a combination of linked
> servers and ad hoc connections using opendatasource(). However my new
> site is having trouble with Microsoft.Jet.OLEDB.4.0 and I'm worried
> that I may have to come up with a different way to do the migration.
> Can anyone let me know any other ways (other than linked servers and
> DTS - which is not flexible enough for my situation here) that I can
> get data from Excel and Access into SQL Server?
> No idea is too simple.
> Thanks,
> Josh.
|||Hi,
Easiest and fastest way is using DTS to move data from Access and Excel. If
DTS is really not feasible then save the Access / Excel files to a comma
seperated csv / text file and then use "BULK INSERT" to load the data into
SQL Server.
Thanks
Hari
MCDBA
"Josh White" <whitegoose@.inorbit.com> wrote in message
news:aafea0a8.0404201812.196002df@.posting.google.c om...
> Hi all.
> I need to to a lot of data migration from excel spreadsheets, access
> databases, and SQL Server databases into a SQL Server database.
> In the past I would have achieved this using a combination of linked
> servers and ad hoc connections using opendatasource(). However my new
> site is having trouble with Microsoft.Jet.OLEDB.4.0 and I'm worried
> that I may have to come up with a different way to do the migration.
> Can anyone let me know any other ways (other than linked servers and
> DTS - which is not flexible enough for my situation here) that I can
> get data from Excel and Access into SQL Server?
> No idea is too simple.
> Thanks,
> Josh.

Data migration

I have SQL 2K (SP3) box with 2 partitions and all of the databases are on
the first one (which is very small), and that is nearing 100% capacity. What
is the best way to point/move the databases to the second parition?
tia,
Garyhmm, should I just detach, move the .mdf and .ldf files to the second
partition and then atach it back? Would that cause any problems with the
front end?
-Gary
"Gary" <gary.rey@.NOOOOSPAM.virtium.com> wrote in message
news:OdOblJPbDHA.1740@.TK2MSFTNGP10.phx.gbl...
> I have SQL 2K (SP3) box with 2 partitions and all of the databases are on
> the first one (which is very small), and that is nearing 100% capacity.
What
> is the best way to point/move the databases to the second parition?
> tia,
> Gary
>

data migration

Hi all.
I need to to a lot of data migration from excel spreadsheets, access
databases, and SQL Server databases into a SQL Server database.
In the past I would have achieved this using a combination of linked
servers and ad hoc connections using opendatasource(). However my new
site is having trouble with Microsoft.Jet.OLEDB.4.0 and I'm worried
that I may have to come up with a different way to do the migration.
Can anyone let me know any other ways (other than linked servers and
DTS - which is not flexible enough for my situation here) that I can
get data from Excel and Access into SQL Server?
No idea is too simple.
Thanks,
Josh.Hi Josh,
Well I tend to create insert statements in the excel sheet or access query.
That always works for me
Statement formula's usually look like
="insert into Table(Col1,Col2,Col3) values('" & a1 & "'," & b1 & ",'" & c1 &
"')"
then I copy the values into notepad and then into ISQL
I hope this helps
--
Greg O
Looking for SQL Scripts, Need Help? http://www.SQL-Scripts.com
Document all your databases now.
http://www.ag-software.com/ags_scribe_index.aspx
Crosstab queries from SQL Server.
http://www.ag-software.com/xp_ags_crosstab.aspx
Edit Extended Properties as easy as can be.
http://www.ag-software.com/ags_SSEPE_index.aspx
"Josh White" <whitegoose@.inorbit.com> wrote in message
news:aafea0a8.0404201812.196002df@.posting.google.com...
> Hi all.
> I need to to a lot of data migration from excel spreadsheets, access
> databases, and SQL Server databases into a SQL Server database.
> In the past I would have achieved this using a combination of linked
> servers and ad hoc connections using opendatasource(). However my new
> site is having trouble with Microsoft.Jet.OLEDB.4.0 and I'm worried
> that I may have to come up with a different way to do the migration.
> Can anyone let me know any other ways (other than linked servers and
> DTS - which is not flexible enough for my situation here) that I can
> get data from Excel and Access into SQL Server?
> No idea is too simple.
> Thanks,
> Josh.|||Hi,
Easiest and fastest way is using DTS to move data from Access and Excel. If
DTS is really not feasible then save the Access / Excel files to a comma
seperated csv / text file and then use "BULK INSERT" to load the data into
SQL Server.
Thanks
Hari
MCDBA
"Josh White" <whitegoose@.inorbit.com> wrote in message
news:aafea0a8.0404201812.196002df@.posting.google.com...
> Hi all.
> I need to to a lot of data migration from excel spreadsheets, access
> databases, and SQL Server databases into a SQL Server database.
> In the past I would have achieved this using a combination of linked
> servers and ad hoc connections using opendatasource(). However my new
> site is having trouble with Microsoft.Jet.OLEDB.4.0 and I'm worried
> that I may have to come up with a different way to do the migration.
> Can anyone let me know any other ways (other than linked servers and
> DTS - which is not flexible enough for my situation here) that I can
> get data from Excel and Access into SQL Server?
> No idea is too simple.
> Thanks,
> Josh.

Thursday, March 29, 2012

data synchronization

data synchronization

data synchronization

data synchronisation

Data storage question

Data storage question

Data storage question

Data Space Problem

Data Space Problem

Tuesday, March 27, 2012

Data Source View and Report Model

Thursday, March 22, 2012

Data Retrieval

Thursday, March 8, 2012

Data missing after transaction committed

Data missing after transaction committed

Data missing after transaction committed

Wednesday, March 7, 2012

Data Mining

Data Migration using Transactional Replication

Saturday, February 25, 2012

Data Migration :- SQL Server1 - SQL Server2

data migration

Data migration

data migration

Data transformation services

Blog Archive

About Me