Showing posts with label basic. Show all posts
Showing posts with label basic. Show all posts

Monday, March 26, 2012

Easy question for experienced developers

How do I add a previously-made SQL Compact Edition database to my Visual Basic.Net 2005 project inside Visual Studio, and ensure that it gets built into the ClickOnce-deployed solution? Our dba has completed the db design for our project, and I need to add the file to the project I am writing business logic for.

I've been writing software for about 15 years but just now using SQL CE for the first time. I hope my questions are not too simplistic for this forum, but I ran into a problem with something so simple that I'm at a loss, and I thought I'd throw it in this newbie thread.

To wit, what is wrong with this syntax:

INSERT INTO refState(StateName)
VALUES('Alberta')
INSERT INTO refState(StateName)
VALUES('Alaska')

This is a snippet from a T-SQL script I've been using for years that populates reference tables for things like states, countries, etc.

When I try to run the query in Visual Studio 2005 Pro while I have my refState table open in the IDE, I receive the following error:

'Unable to parse query text'.

Then I say to myself, What? Why? So I try to verify the SQL syntax, and then I receive this new error:

'This command is not supported by this provider.'

On the surface it looks like ANSI SQL is out the window. So, I'm guessing I'm missing some fundamental understanding.

Little help? thanx

|||

Well, consider that the Compact Edition is not SQL Server and does not understand TSQL. The problem with your SQL is that the SQLCe engine can process one (and only one) SQL statement at a time. It does not understand the concept of multiple operations or resultsets as supported in TSQL.

In my EBook, I show an example (and a class) used to import schema files that can be used to create a SQLCe database and populate the schema. Gettting data into the database is also a challenge as SSIS does not work well with SQLCe (yet). One approach is to setup Merge Replication with a SQL Server Workgroup (or better) engine, use RDL or the new (unreleased) ADO Synchronization Services. Sure, you can write your own INSERT loop to import data and this would not be that hard to do... code-intensive but doable.

hth

See www.hitchhikerguides.net

|||

Thx for the feedback Bill.

Also, I found what we needed as far as setting up private ClickOnce deployments (which is what we're using) at the following URL:

http://msdn2.microsoft.com/en-us/library/bb219482.aspx

Thursday, March 22, 2012

Easiest way to move database from 1 machine to another ?

I am not a DBA, so pardon me if my question is basic.
What is the easiest way to move SQL Server 2005 database from 1 machine to
another ?
If I backup the database, I can not restore it in another database or in
another machine, can I ?
Is exporting the database the easiest way to move it to another machine (in
the new machine before importing the data I will still need to create the
database and run the database script to create all the tables and stored
procedures and views, right ?) ?
Thank you.fniles,
Yes, restoring a backup to another server is a very easy way to move a
database from 1 machine to another. If you no longer want the database on
machine 1, detach the database, move the files (mdf & ldf( to the new
server, then attach them.
You will need to deal with any issues raised by logins not existing on both
servers, if that turns out to be the case for you.
RLF
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>|||Backups are easy, however, if you are using differently physically
configured machines that make the backup incompatible, you may want to
look at Microsoft SQL Server Management Studio and first scripting the
database and table definitions (in 2005, right click the database name
or table name and select Script As->Create To File) to generate SQL
scripts that recreate the database and table structures (less the
data) and then running the SQL Scripts in SQL Server Management Studio
on the target machine.
After doing this, you can move the data over by creating import/export
packages in SQL Server Management Studio by right clicking the
database the table is in and selecting tasks Export Data on the source
machine, and tasks Import Data on the target machine. Select
Microsoft Excel file format as the way to transport the data between
the machines if they cannot be connected to each other via a network
cable.|||Two easy ways:
1) Backup and restore. Backup database and restore it on another instance.
2) Detach and attach. Detach database, copy all database files to another
instance and attach database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"fniles" wrote:
> I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine (in
> the new machine before importing the data I will still need to create the
> database and run the database script to create all the tables and stored
> procedures and views, right ?) ?
> Thank you.
>
>|||For me, the easiest way to move databases from one machine to the other is
have a good documentation to back it up. Some pointers
1) Service pack levels and hotfixes should be the same on both source and
target
2) If you want the backup/restore to be easy and straightforward, make sure
that you have the same disk configurations. You can also do a detach/attach
approach
3) No need to move tempdb nor model databases
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>|||"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
sure you can; make sure you detach the DB first (after backup of course) ...
you might have to change the location of the physical files in the restore
dialogue options
or you can use the "Copy Database" facility in SQL Server Management Studio
(select DB, right click for menu; Tasks->Copy Database
or ... there are other options still ... but those are likely the easiest
ones
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>|||Thank you all for the replies.
I still want the original database on machine A, I just want another copy of
it in another machine.
So I backup the original database (say it's called MYDB) on machine A.
I then copied the MYDB.bak file from machine A to machine B.
In machineB, I then created a database called MYDB. Then I right click on
the database and select "Restore". Under "Specify source and location of
backup sets to restore" I selected "From device" and I pointed it to the
MYDB.bak in machine B.
I then got the error "Restore failed for server "serverB"
System.data.SqlClient.sqlerror: The backup set holds a backup of a database
other than the existing "MYDB" database. (microsoft.sqlserver.smo)
What did I do wrong on the above steps ?
Since I still need the original database and still using it, can I detach it
after backing it up ?
When I tried to copy the database from machine A to machine B, it works when
I selected "Use the detach and attach method" but when I selected "Use the
SQL Management Object method", I got an error with a view about an invalid
table name HistTradesOrig, which exists in the database, and I am able to
run the query fine.
Event Name: OnError
Message: ERROR : errorCode=-1073548784 description=Executing the query "
Create view [dbo].[Commission] AS
SELECT HistTradesOrig.email, Sum([Quantity]) AS Quan, HistTradesOrig.Status
FROM HistTradesOrig
GROUP BY HistTradesOrig.email, HistTradesOrig.Status
HAVING (((HistTradesOrig.Status)='F'))
" failed with the following error: "Invalid object name 'HistTradesOrig'.".
Possible failure reasons: Problems with the query, "ResultSet" property not
set correctly, parameters not set correctly, or connection not established
correctly.
helpFile= helpContext=0
idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
StackTrace: at
Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
Operator: PFGBEST\sqladm
Thank you.
"Liz" <liz@.tiredofspam.com> wrote in message
news:eaGlygxMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> "fniles" <fniles@.pfmail.com> wrote in message
> news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>>I am not a DBA, so pardon me if my question is basic.
>> What is the easiest way to move SQL Server 2005 database from 1 machine
>> to another ?
>
>> If I backup the database, I can not restore it in another database or in
>> another machine, can I ?
> sure you can; make sure you detach the DB first (after backup of course)
> ... you might have to change the location of the physical files in the
> restore dialogue options
> or you can use the "Copy Database" facility in SQL Server Management
> Studio (select DB, right click for menu; Tasks->Copy Database
> or ... there are other options still ... but those are likely the easiest
> ones
>> Is exporting the database the easiest way to move it to another machine
>> (in the new machine before importing the data I will still need to create
>> the database and run the database script to create all the tables and
>> stored procedures and views, right ?) ?
>> Thank you.
>>
>|||Did you solve this issue? If not, try going to tab 2 of the restore db page,
and clicking Overwrite Existing Database. That should work.
--
John
"fniles" wrote:
> Thank you all for the replies.
> I still want the original database on machine A, I just want another copy of
> it in another machine.
> So I backup the original database (say it's called MYDB) on machine A.
> I then copied the MYDB.bak file from machine A to machine B.
> In machineB, I then created a database called MYDB. Then I right click on
> the database and select "Restore". Under "Specify source and location of
> backup sets to restore" I selected "From device" and I pointed it to the
> MYDB.bak in machine B.
> I then got the error "Restore failed for server "serverB"
> System.data.SqlClient.sqlerror: The backup set holds a backup of a database
> other than the existing "MYDB" database. (microsoft.sqlserver.smo)
> What did I do wrong on the above steps ?
> Since I still need the original database and still using it, can I detach it
> after backing it up ?
> When I tried to copy the database from machine A to machine B, it works when
> I selected "Use the detach and attach method" but when I selected "Use the
> SQL Management Object method", I got an error with a view about an invalid
> table name HistTradesOrig, which exists in the database, and I am able to
> run the query fine.
> Event Name: OnError
> Message: ERROR : errorCode=-1073548784 description=Executing the query "
> Create view [dbo].[Commission] AS
> SELECT HistTradesOrig.email, Sum([Quantity]) AS Quan, HistTradesOrig.Status
> FROM HistTradesOrig
> GROUP BY HistTradesOrig.email, HistTradesOrig.Status
> HAVING (((HistTradesOrig.Status)='F'))
> " failed with the following error: "Invalid object name 'HistTradesOrig'.".
> Possible failure reasons: Problems with the query, "ResultSet" property not
> set correctly, parameters not set correctly, or connection not established
> correctly.
> helpFile= helpContext=0
> idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
> StackTrace: at
> Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
> at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
> at
> Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
> Operator: PFGBEST\sqladm
> Thank you.
> "Liz" <liz@.tiredofspam.com> wrote in message
> news:eaGlygxMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> >
> > "fniles" <fniles@.pfmail.com> wrote in message
> > news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
> >
> >>I am not a DBA, so pardon me if my question is basic.
> >> What is the easiest way to move SQL Server 2005 database from 1 machine
> >> to another ?
> >
> >
> >> If I backup the database, I can not restore it in another database or in
> >> another machine, can I ?
> >
> > sure you can; make sure you detach the DB first (after backup of course)
> > ... you might have to change the location of the physical files in the
> > restore dialogue options
> >
> > or you can use the "Copy Database" facility in SQL Server Management
> > Studio (select DB, right click for menu; Tasks->Copy Database
> >
> > or ... there are other options still ... but those are likely the easiest
> > ones
> >
> >> Is exporting the database the easiest way to move it to another machine
> >> (in the new machine before importing the data I will still need to create
> >> the database and run the database script to create all the tables and
> >> stored procedures and views, right ?) ?
> >>
> >> Thank you.
> >>
> >>
> >
> >
>
>

Easiest way to move database from 1 machine to another ?

I am not a DBA, so pardon me if my question is basic.
What is the easiest way to move SQL Server 2005 database from 1 machine to
another ?
If I backup the database, I can not restore it in another database or in
another machine, can I ?
Is exporting the database the easiest way to move it to another machine (in
the new machine before importing the data I will still need to create the
database and run the database script to create all the tables and stored
procedures and views, right ?) ?
Thank you.
fniles,
Yes, restoring a backup to another server is a very easy way to move a
database from 1 machine to another. If you no longer want the database on
machine 1, detach the database, move the files (mdf & ldf( to the new
server, then attach them.
You will need to deal with any issues raised by logins not existing on both
servers, if that turns out to be the case for you.
RLF
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>
|||Backups are easy, however, if you are using differently physically
configured machines that make the backup incompatible, you may want to
look at Microsoft SQL Server Management Studio and first scripting the
database and table definitions (in 2005, right click the database name
or table name and select Script As->Create To File) to generate SQL
scripts that recreate the database and table structures (less the
data) and then running the SQL Scripts in SQL Server Management Studio
on the target machine.
After doing this, you can move the data over by creating import/export
packages in SQL Server Management Studio by right clicking the
database the table is in and selecting tasks Export Data on the source
machine, and tasks Import Data on the target machine. Select
Microsoft Excel file format as the way to transport the data between
the machines if they cannot be connected to each other via a network
cable.
|||Two easy ways:
1) Backup and restore. Backup database and restore it on another instance.
2) Detach and attach. Detach database, copy all database files to another
instance and attach database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"fniles" wrote:

> I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine (in
> the new machine before importing the data I will still need to create the
> database and run the database script to create all the tables and stored
> procedures and views, right ?) ?
> Thank you.
>
>
|||For me, the easiest way to move databases from one machine to the other is
have a good documentation to back it up. Some pointers
1) Service pack levels and hotfixes should be the same on both source and
target
2) If you want the backup/restore to be easy and straightforward, make sure
that you have the same disk configurations. You can also do a detach/attach
approach
3) No need to move tempdb nor model databases
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>
|||"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...

>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?

> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
sure you can; make sure you detach the DB first (after backup of course) ...
you might have to change the location of the physical files in the restore
dialogue options
or you can use the "Copy Database" facility in SQL Server Management Studio
(select DB, right click for menu; Tasks->Copy Database
or ... there are other options still ... but those are likely the easiest
ones

> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>
|||Thank you all for the replies.
I still want the original database on machine A, I just want another copy of
it in another machine.
So I backup the original database (say it's called MYDB) on machine A.
I then copied the MYDB.bak file from machine A to machine B.
In machineB, I then created a database called MYDB. Then I right click on
the database and select "Restore". Under "Specify source and location of
backup sets to restore" I selected "From device" and I pointed it to the
MYDB.bak in machine B.
I then got the error "Restore failed for server "serverB"
System.data.SqlClient.sqlerror: The backup set holds a backup of a database
other than the existing "MYDB" database. (microsoft.sqlserver.smo)
What did I do wrong on the above steps ?
Since I still need the original database and still using it, can I detach it
after backing it up ?
When I tried to copy the database from machine A to machine B, it works when
I selected "Use the detach and attach method" but when I selected "Use the
SQL Management Object method", I got an error with a view about an invalid
table name HistTradesOrig, which exists in the database, and I am able to
run the query fine.
Event Name: OnError
Message: ERROR : errorCode=-1073548784 description=Executing the query "
Create view [dbo].[Commission] AS
SELECT HistTradesOrig.email, Sum([Quantity]) AS Quan, HistTradesOrig.Status
FROM HistTradesOrig
GROUP BY HistTradesOrig.email, HistTradesOrig.Status
HAVING (((HistTradesOrig.Status)='F'))
" failed with the following error: "Invalid object name 'HistTradesOrig'.".
Possible failure reasons: Problems with the query, "ResultSet" property not
set correctly, parameters not set correctly, or connection not established
correctly.
helpFile= helpContext=0
idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
StackTrace: at
Microsoft.SqlServer.Management.Dts.DtsTransferProv ider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.Transf erData()
at
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask. TransferObjectsTask.TransferDatabasesUsingSMOTrans fer()
Operator: PFGBEST\sqladm
Thank you.
"Liz" <liz@.tiredofspam.com> wrote in message
news:eaGlygxMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> "fniles" <fniles@.pfmail.com> wrote in message
> news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>
>
> sure you can; make sure you detach the DB first (after backup of course)
> ... you might have to change the location of the physical files in the
> restore dialogue options
> or you can use the "Copy Database" facility in SQL Server Management
> Studio (select DB, right click for menu; Tasks->Copy Database
> or ... there are other options still ... but those are likely the easiest
> ones
>
>
|||Did you solve this issue? If not, try going to tab 2 of the restore db page,
and clicking Overwrite Existing Database. That should work.
John
"fniles" wrote:

> Thank you all for the replies.
> I still want the original database on machine A, I just want another copy of
> it in another machine.
> So I backup the original database (say it's called MYDB) on machine A.
> I then copied the MYDB.bak file from machine A to machine B.
> In machineB, I then created a database called MYDB. Then I right click on
> the database and select "Restore". Under "Specify source and location of
> backup sets to restore" I selected "From device" and I pointed it to the
> MYDB.bak in machine B.
> I then got the error "Restore failed for server "serverB"
> System.data.SqlClient.sqlerror: The backup set holds a backup of a database
> other than the existing "MYDB" database. (microsoft.sqlserver.smo)
> What did I do wrong on the above steps ?
> Since I still need the original database and still using it, can I detach it
> after backing it up ?
> When I tried to copy the database from machine A to machine B, it works when
> I selected "Use the detach and attach method" but when I selected "Use the
> SQL Management Object method", I got an error with a view about an invalid
> table name HistTradesOrig, which exists in the database, and I am able to
> run the query fine.
> Event Name: OnError
> Message: ERROR : errorCode=-1073548784 description=Executing the query "
> Create view [dbo].[Commission] AS
> SELECT HistTradesOrig.email, Sum([Quantity]) AS Quan, HistTradesOrig.Status
> FROM HistTradesOrig
> GROUP BY HistTradesOrig.email, HistTradesOrig.Status
> HAVING (((HistTradesOrig.Status)='F'))
> " failed with the following error: "Invalid object name 'HistTradesOrig'.".
> Possible failure reasons: Problems with the query, "ResultSet" property not
> set correctly, parameters not set correctly, or connection not established
> correctly.
> helpFile= helpContext=0
> idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
> StackTrace: at
> Microsoft.SqlServer.Management.Dts.DtsTransferProv ider.ExecuteTransfer()
> at Microsoft.SqlServer.Management.Smo.Transfer.Transf erData()
> at
> Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask. TransferObjectsTask.TransferDatabasesUsingSMOTrans fer()
> Operator: PFGBEST\sqladm
> Thank you.
> "Liz" <liz@.tiredofspam.com> wrote in message
> news:eaGlygxMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>
>

Easiest way to move database from 1 machine to another ?

I am not a DBA, so pardon me if my question is basic.
What is the easiest way to move SQL Server 2005 database from 1 machine to
another ?
If I backup the database, I can not restore it in another database or in
another machine, can I ?
Is exporting the database the easiest way to move it to another machine (in
the new machine before importing the data I will still need to create the
database and run the database script to create all the tables and stored
procedures and views, right ?) ?
Thank you.fniles,
Yes, restoring a backup to another server is a very easy way to move a
database from 1 machine to another. If you no longer want the database on
machine 1, detach the database, move the files (mdf & ldf( to the new
server, then attach them.
You will need to deal with any issues raised by logins not existing on both
servers, if that turns out to be the case for you.
RLF
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>|||Backups are easy, however, if you are using differently physically
configured machines that make the backup incompatible, you may want to
look at Microsoft SQL Server Management Studio and first scripting the
database and table definitions (in 2005, right click the database name
or table name and select Script As->Create To File) to generate SQL
scripts that recreate the database and table structures (less the
data) and then running the SQL Scripts in SQL Server Management Studio
on the target machine.
After doing this, you can move the data over by creating import/export
packages in SQL Server Management Studio by right clicking the
database the table is in and selecting tasks Export Data on the source
machine, and tasks Import Data on the target machine. Select
Microsoft Excel file format as the way to transport the data between
the machines if they cannot be connected to each other via a network
cable.|||Two easy ways:
1) Backup and restore. Backup database and restore it on another instance.
2) Detach and attach. Detach database, copy all database files to another
instance and attach database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"fniles" wrote:

> I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine (i
n
> the new machine before importing the data I will still need to create the
> database and run the database script to create all the tables and stored
> procedures and views, right ?) ?
> Thank you.
>
>|||For me, the easiest way to move databases from one machine to the other is
have a good documentation to back it up. Some pointers
1) Service pack levels and hotfixes should be the same on both source and
target
2) If you want the backup/restore to be easy and straightforward, make sure
that you have the same disk configurations. You can also do a detach/attach
approach
3) No need to move tempdb nor model databases
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>|||"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...

>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?

> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
sure you can; make sure you detach the DB first (after backup of course) ...
you might have to change the location of the physical files in the restore
dialogue options
or you can use the "Copy Database" facility in SQL Server Management Studio
(select DB, right click for menu; Tasks->Copy Database
or ... there are other options still ... but those are likely the easiest
ones

> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>|||Thank you all for the replies.
I still want the original database on machine A, I just want another copy of
it in another machine.
So I backup the original database (say it's called MYDB) on machine A.
I then copied the MYDB.bak file from machine A to machine B.
In machineB, I then created a database called MYDB. Then I right click on
the database and select "Restore". Under "Specify source and location of
backup sets to restore" I selected "From device" and I pointed it to the
MYDB.bak in machine B.
I then got the error "Restore failed for server "serverB"
System.data.SqlClient.sqlerror: The backup set holds a backup of a database
other than the existing "MYDB" database. (microsoft.sqlserver.smo)
What did I do wrong on the above steps ?
Since I still need the original database and still using it, can I detach it
after backing it up ?
When I tried to copy the database from machine A to machine B, it works when
I selected "Use the detach and attach method" but when I selected "Use the
SQL Management Object method", I got an error with a view about an invalid
table name HistTradesOrig, which exists in the database, and I am able to
run the query fine.
Event Name: OnError
Message: ERROR : errorCode=-1073548784 description=Executing the query "
Create view [dbo].[Commission] AS
SELECT HistTradesOrig.email, Sum([Quantity]) AS Quan, HistTradesOrig.Sta
tus
FROM HistTradesOrig
GROUP BY HistTradesOrig.email, HistTradesOrig.Status
HAVING (((HistTradesOrig.Status)='F'))
" failed with the following error: "Invalid object name 'HistTradesOrig'.".
Possible failure reasons: Problems with the query, "ResultSet" property not
set correctly, parameters not set correctly, or connection not established
correctly.
helpFile= helpContext=0
idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
StackTrace: at
Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.Transf
erDatabasesUsingSMOTransfer()
Operator: PFGBEST\sqladm
Thank you.
"Liz" <liz@.tiredofspam.com> wrote in message
news:eaGlygxMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> "fniles" <fniles@.pfmail.com> wrote in message
> news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>
>
> sure you can; make sure you detach the DB first (after backup of course)
> ... you might have to change the location of the physical files in the
> restore dialogue options
> or you can use the "Copy Database" facility in SQL Server Management
> Studio (select DB, right click for menu; Tasks->Copy Database
> or ... there are other options still ... but those are likely the easiest
> ones
>
>

Wednesday, March 21, 2012

each time I build program, data is lost

I am using visual basic 2008

I am making a program, I used sql server compact edition (sdf) (i think it is no more only for mobile device, I am working for desktop application)which i created with the same visual basic. i update data by using table adapters,

when I close the program and build again, the data previosly updated are deleted, and I get empty database? why is that. do i need to set some copy to.........properties. i have used copy if new.

I want to add something to it, that when I manually entered data, they are not gone. But when I entered from form, next time the data lost.|||

See this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2115234&SiteID=1