Easiest way to update lots of records

I have a database where several thousand records have NULL in a binary field. I want to change all the NULLs to false. I have Visual Studio 5, and the database is a SQL Server 5 database on a remote server. What is the easiest way to do this? Is there a query I can run that will set all ReNew to false where ReNew is Null? This is a live database so I want to get it right. I can't afford to mess it up.

Diane

You can do something like the following:

UPDATE [Table] SET ReNew=0 WHERE ReNew IS NULL

Before I run an update or delete statement, I always run it as a select first, just to make sure I'm only updating the rows I want. Something like this:

SELECT * FROM [Table] WHERE ReNew IS NULL

|||

Benners_J:

Before I run an update or delete statement, I always run it as a select first, just to make sure I'm only updating the rows I want. Something like this:

SELECT * FROM [Table] WHERE ReNew IS NULL

Rather, SELECT COUNT(*) FROM [Table] WHERE ReNew IS NULL would be good, since the poster is saying there are too many rows. Anything which makes you feel comfortable.

Easiest way to start and read a trace from within a SSIS package

I'm trying to gather information from within a SSIS package for benchmarking, reconciliation, and reporting purposes in regards to cube processing, which I'm initiating using the AS processing task.

What is the easiest way to capture this information?

The only way I've been able to come up with is to use a profiler trace. If this is really the only way, what is the easiest way to execute and read the trace from within SSIS?

Also, if a script task has to be used, does anyone have a code sample?

Thanks in advance!

Simplest way is to read the trace into SQL server. You can use the fn_trace_gettable to either populate a new table or return the data to SSIS by using an OLEDB source

see BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/c2590159-6ec5-4510-81ab-e935cc4216cd.htm

|||Thanks for the help! It looks like that may work. I'll give it a shot.

Thanks again.

Easiest way to move SQL databases to a new server?

What's the easiest way to move SQL databases to a new server? I have a
computer with SQL Server 2000 on it with several databases, and I just built
a new Win 2003 Server with SQL Server 2000 on it, and want to move all the
databases to the new server and decommission the old one.
Thanks in advance.
-MikeMike,
Perhaps the easiest and safest is to back up each database to a file, copy
it to the other server, and restore. The benefit is that you then have a
backup of the databases.
Just as easily, you can also detach the databases, copy the files to the new
server, and attach them there.
Ron
--
Ron Talmage
SQL Server MVP
"Mike Schinkel" <ms@.mikeschinkel.com> wrote in message
news:%23QlbsbQzDHA.1740@.TK2MSFTNGP09.phx.gbl...
> What's the easiest way to move SQL databases to a new server? I have a
> computer with SQL Server 2000 on it with several databases, and I just
built
> a new Win 2003 Server with SQL Server 2000 on it, and want to move all the
> databases to the new server and decommission the old one.
> Thanks in advance.
> -Mike
>|||Hi,
If you can maintain the same directory structure in the new server, Stop the
MSSQL server service in both servers and copy all the .MDF and .LDF files
to the identical folder in new server and then start the MSSQL service.
Once the service come up , you can use the below system stored procedure to
change the server name.
sp_dropserver 'oldservername'
go
sp_addserver 'newservername','local'
After this restart the SQL server service.
Thanks
Hari
MCDBA
"Mike Schinkel" <ms@.mikeschinkel.com> wrote in message
news:#QlbsbQzDHA.1740@.TK2MSFTNGP09.phx.gbl...
> What's the easiest way to move SQL databases to a new server? I have a
> computer with SQL Server 2000 on it with several databases, and I just
built
> a new Win 2003 Server with SQL Server 2000 on it, and want to move all the
> databases to the new server and decommission the old one.
> Thanks in advance.
> -Mike
>|||[posted and mailed, please reply in news]
Mike Schinkel (ms@.mikeschinkel.com) writes:
> What's the easiest way to move SQL databases to a new server? I have a
> computer with SQL Server 2000 on it with several databases, and I just
> built a new Win 2003 Server with SQL Server 2000 on it, and want to move
> all the databases to the new server and decommission the old one.
See http://support.microsoft.com/default.aspx?scid=kb;EN-US;224071.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||There is an HOWTO Article on this as well
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
HOW TO: Move Databases Between Computers That Are Running SQL Server
Yih-Yoon Lee
"Mike Schinkel" <ms@.mikeschinkel.com> wrote in message news:<#QlbsbQzDHA.1740@.TK2MSFTNGP09.phx.gbl>...
> What's the easiest way to move SQL databases to a new server? I have a
> computer with SQL Server 2000 on it with several databases, and I just built
> a new Win 2003 Server with SQL Server 2000 on it, and want to move all the
> databases to the new server and decommission the old one.
> Thanks in advance.
> -Mike|||The simplest way is to use the wizard.
1. Ensure that your enterprise manager can see both
servers and has access to both servers as owner (SA
access)
2. On the source server, select the "Management" folder
3. Go to the Tools menu, choose Wizards
4. Select Management from the Wizard menu
5. Select "Copy Database Wizard"
The Wizard allows you to select the source and
destination servers, and choose the databases to be moved
or copied. It creates all the logins on the destination
server, and moves supported shared objects, shared
procedures,jobs and error messages. It also allows you to
schedule the migration.
It is simple, fast and works a treat. Even a non-SQL
person can use this and correctly move/copy the databases
easily
>--Original Message--
>What's the easiest way to move SQL databases to a new
server? I have a
>computer with SQL Server 2000 on it with several
databases, and I just built
>a new Win 2003 Server with SQL Server 2000 on it, and
want to move all the
>databases to the new server and decommission the old one.
>Thanks in advance.
>-Mike
>
>.
>sql

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

Easiest way to get a value in af file into a variable ?

I get a file with some key information delivered to an ftp destination each day along with some files containing rawdata.

The file is a csv file containing some short description of what is being delivered.

Numrows;pulltime;sourceinfo

25302524;25-01-2006;dssrv34

So the file has columndescription and 1 row with some information.

My question is, what is the easiest way to get those 3 informations into 3 variables ?

How about a Flat File Adapter into a script component in which you can load the values into the variables.

-Jamie

|||

Or you could do this: http://blogs.conchango.com/jamiethomson/archive/2005/06/15/1693.aspx

(I forgot I'd done this before)

-Jamie

|||Personally, I would create a script task that takes in a filespec variable and writes to three variables. Like so.

Dim strFileSpec as String = cstr(Dts.Variables("MyFileSpec").Value)

Dim sr As System.IO.StreamReader

Dim strVals() As String

' Check if file exists

If System.IO.File.Exists(strFileSpec) Then

' Open the stream reader

sr = New System.IO.StreamReader(strFileSpec)

' Check if not at end of stream

' Split the first line at the semicolons and put in string array

If Not sr.EndOfStream Then

strVals = sr.ReadLine.Split(";")

End If

' Close the stream reader

sr.Close

' Check if there are three variables,

' If so right to output variables

If strVals.GetLength = 3 Then

Dts.Variables("MyVar1").Value = strVals(0)

Dts.Variables("MyVar2").Value = strVals(1)

Dts.Variables("MyVar3").Value = strVals(2)

End If

End If

Larry Pope

Easiest way to determine if only client tools are installed?

Hi all,
I've been trying to register my local sql server in enterprise manager
without any success - I can see my remote sql server, but it just wont
connect to the local one on the laptop - I think I may have only install
"client tools" in the past because I thought I'd only want to connect to the
remote server - is there an easy to way to determine whether this is the
case? I've just checked under Add/Remove Programs and there's a SQL Server
entry for about 70mb...
Any info appreciated..
Regards
RobRob Meade (ten.bewdoowsgnik@.edaem.bbor) writes:
> I've been trying to register my local sql server in enterprise manager
> without any success - I can see my remote sql server, but it just wont
> connect to the local one on the laptop - I think I may have only install
> "client tools" in the past because I thought I'd only want to connect to
> the remote server - is there an easy to way to determine whether this is
> the case? I've just checked under Add/Remove Programs and there's a SQL
> Server entry for about 70mb...
Start->Programs->SQL Server. Do you see a Server Network Utility? If you
don't, you don't have the server installed. If you do, you have it
installed. If you also have a Service Manager, start it, and then find
the SQL Server service, and make sure it's running.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Easiest way to copy a MS-SQL Database from one machine to another

Can anyone recommend the easiest way to get a full copy of a database from
one server to another. The servers are not part of the same organization or
network.

I have received a backup of the database created with enterprise manager but
am unable to restore it into a database of the same name on my server.

Thanks,

Kevin"Kevin" <noemail@.provided.com> wrote in message
news:tr_Zd.699401$8l.384177@.pd7tw1no...
> Can anyone recommend the easiest way to get a full copy of a database from
> one server to another. The servers are not part of the same organization
> or
> network.
> I have received a backup of the database created with enterprise manager
> but
> am unable to restore it into a database of the same name on my server.
> Thanks,
> Kevin

http://support.microsoft.com/defaul...kb;en-us;314546

Backup and restore is usually the easiest way - if you're having a problem
restoring a backup, then I suggest you post whatever error message you get.
You can also try restoring from Query Analyzer:

restore database MyDB from disk = 'c:\temp\db.bak'

Sometimes errors in QA are more informative than what EM tells you. See the
full RESTORE syntax in Books Online for moving file locations etc.

Simon|||Did you checked the paths to database files before restoring?

If you are using the EM restore database function, the database name
doesn't matter. You can name your new database as you would like to.
The only importand thing is, check the path, where the database should
be restored to.

If the backup was done on other server, probably the Db files was
located under different path (for example: C:\Program
Files\Database\database_name.mdf").

Lets say, that you try to restore it over database BUBU. In this case,
you can have totaly diff. path and of course filenames ... (for
example: "C:\Databases\BUBU_Data.mdf). Therefore, you'll get an error
message, that the files does not match. This is because why, database
reads information from backup about location of database ...

Greatings

Matik|||Simon,

I've tried restoring from QA, the directories for the other server and my
server are different so I am using the MOVE option for the data and log
files. The same problem occurs that I was getting using EM, it complains
that the logical file being restored is not part of my database. I've tried
this without a database called 'Test', as well as after creating one and
using the RESTORE FILELISTONLY but the error message is always the same:

here's the code and error message:

RESTORE DATABASE Test FROM DISK = 'C:\Backups\Project Recovery\Sample
Data\RebillingMarch102005.SQLBackup'
WITH NORECOVERY,
MOVE 'g:\mssql\data\rebilling_Data.MDF' TO 'C:\Program Files\Microsoft
SQL Server\MSSQL\Data\Rebilling_Data.mdf',
MOVE 'E:\MSSQL\log\rebilling_Log.LDF' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\Rebilling_Log.ldf'

response:
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'g:\mssql\data\rebilling_Data.MDF' is not part of database
'Test'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Thanks,

- Kevin

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:42387d3d$1_3@.news.bluewin.ch...
> "Kevin" <noemail@.provided.com> wrote in message
> news:tr_Zd.699401$8l.384177@.pd7tw1no...
> > Can anyone recommend the easiest way to get a full copy of a database
from
> > one server to another. The servers are not part of the same organization
> > or
> > network.
> > I have received a backup of the database created with enterprise manager
> > but
> > am unable to restore it into a database of the same name on my server.
> > Thanks,
> > Kevin
> http://support.microsoft.com/defaul...kb;en-us;314546
> Backup and restore is usually the easiest way - if you're having a problem
> restoring a backup, then I suggest you post whatever error message you
get.
> You can also try restoring from Query Analyzer:
> restore database MyDB from disk = 'c:\temp\db.bak'
> Sometimes errors in QA are more informative than what EM tells you. See
the
> full RESTORE syntax in Books Online for moving file locations etc.
> Simon|||Instead of 'g:\mssql\data\rebilling_Data.*MDF' use
'rebilling_Data.*MDF' and
rebilling_Log.*LDF'

Madhivanan|||The first name after "MOVE" should be the logical file name, not the
physical. In your case, probably, "rebilling_Data" and "rebilling_Log"
are the logical names you need to use.

Easiest way of combining multiple fields from different records into one record?

I have a table;

CREATE TABLE theLiterals (
theKey varchar (255) NOT NULL ,
theValue varchar (255) NULL
)
INSERT INTO theLiterals VALUES('defaultServer','\\MyServer\')
INSERT INTO theLiterals VALUES('defaultShare','MyShare\')
INSERT INTO theLiterals VALUES('defaultFolder','MyFolder\')
INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')

I then try;

SELECT
defaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,
defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,
defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,
defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue END
FROM theLiterals

and I get;

defaultServer defaultShare defaultFolder defaultFile
\\MyServer\ NULL NULL NULL
NULL MyShare\ NULL NULL
NULL NULL MyFolder\ NULL
NULL NULL NULL MyFile.dat

but I want it COALESCEd like this;

defaultServer defaultShare defaultFolder defaultFile
\\MyServer\ MyShare\ MyFolder\ MyFile.dat

...but my syntax is incorrect. Is there an efficient way of doing this.

I want to have a script/UDF where I can say...
GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile')
and then my one-row recordset will be...

RS(0) will = '\\MyServer\'
RS(1) will = 'MyShare\'
RS(2) will = 'MyFolder\'
RS(3) will = 'MyFile.dat'

Thanks for any help!Just add MIN():

SELECT
defaultServer = MIN(CASE WHEN theKey = 'defaultServer' THEN theValue END),
defaultShare = MIN(CASE WHEN theKey = 'defaultShare' THEN theValue END),
defaultFolder = MIN(CASE WHEN theKey = 'defaultFolder' THEN theValue END),
defaultFile = MIN(CASE WHEN theKey = 'defaultFile' THEN theValue END)
FROM theLiterals

--
David Portas
SQL Server MVP
--|||Hi

Maybe:
SELECT A.defaultServer, B.defaultShare, C.defaultFolder, D.defaultFile
FROM
( SELECT theValue AS defaultServer
FROM theLiterals
WHERE theKey = 'defaultServer' ) A,
( SELECT theValue AS defaultShare
FROM theLiterals
WHERE theKey = 'defaultShare' ) B,
( SELECT theValue AS defaultFolder
FROM theLiterals
WHERE theKey = 'defaultFolder' ) C,
( SELECT theValue AS defaultFile
FROM theLiterals
WHERE theKey = 'defaultFile' ) D

OR

SELECT
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultServer' ) AS defaultServer ,
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultShare' ) AS defaultShare ,
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultFolder' ) AS defaultFolder ,
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultFile' ) AS defaultFile

You should put a unique or primary key on theKey to make sure only one row
is returned.

John

"Steve" <steve.lin@.cognizantdesign.com> wrote in message
news:27b20cea.0407090955.690c2c8b@.posting.google.c om...
> I have a table;
> CREATE TABLE theLiterals (
> theKey varchar (255) NOT NULL ,
> theValue varchar (255) NULL
> )
> INSERT INTO theLiterals VALUES('defaultServer','\\MyServer\')
> INSERT INTO theLiterals VALUES('defaultShare','MyShare\')
> INSERT INTO theLiterals VALUES('defaultFolder','MyFolder\')
> INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')
>
> I then try;
> SELECT
> defaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,
> defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,
> defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,
> defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue END
> FROM theLiterals
> and I get;
> defaultServer defaultShare defaultFolder defaultFile
> \\MyServer\ NULL NULL NULL
> NULL MyShare\ NULL NULL
> NULL NULL MyFolder\ NULL
> NULL NULL NULL MyFile.dat
> but I want it COALESCEd like this;
> defaultServer defaultShare defaultFolder defaultFile
> \\MyServer\ MyShare\ MyFolder\ MyFile.dat
> ...but my syntax is incorrect. Is there an efficient way of doing this.
> I want to have a script/UDF where I can say...
GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile')
> and then my one-row recordset will be...
> RS(0) will = '\\MyServer\'
> RS(1) will = 'MyShare\'
> RS(2) will = 'MyFolder\'
> RS(3) will = 'MyFile.dat'
> Thanks for any help!|||How about this:

SELECT TOP 1
defaultServer = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultServer'),
defaultShare = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultShare'),
defaultFolder = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultFolder'),
defaultFile = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultFile')
FROM #theLiterals

That returns the desired record:

\\MyServer\MyShare\MyFolder\MyFile.dat

Or you could create a function that takes 4 parameters like 'defaultServer'
and returns a one-record table populated with the results from those 4
SELECTs.

Jim Geissman|||Missed the beginning of this thread, but if #theLiterals is not trivially
small,
you get an (avg) 2:1 speedup by doing:

SELECT
defaultServer = max(case theKey when 'defaultServer' then theValue
end)
,defaultShare = max(case theKey when 'defaultShare' then theValue
end)
,defaultFolder = max(case theKey when 'defaultFolder' then theValue
end)
,defaultFile = max(case theKey when 'defaultFile' then theValue
end)
FROM #theLiterals

"Jim Geissman" <jim_geissman@.countrywide.com> wrote in message
news:b84bf9dc.0407091511.6338405b@.posting.google.c om...
> How about this:
> SELECT TOP 1
> defaultServer = (SELECT theValue FROM #theLiterals
> WHERE theKey = 'defaultServer'),
> defaultShare = (SELECT theValue FROM #theLiterals
> WHERE theKey = 'defaultShare'),
> defaultFolder = (SELECT theValue FROM #theLiterals
> WHERE theKey = 'defaultFolder'),
> defaultFile = (SELECT theValue FROM #theLiterals
> WHERE theKey = 'defaultFile')
> FROM #theLiterals
> That returns the desired record:
> \\MyServer\ MyShare\ MyFolder\ MyFile.dat
> Or you could create a function that takes 4 parameters like
'defaultServer'
> and returns a one-record table populated with the results from those 4
> SELECTs.
> Jim Geissman|||And first place for minimum reads goes to David Portas!

Thanks everyone for the help. I originally thought doing an aggregate
function
to get rid of NULLS would be inefficient, but by looking at the TRACE
it looks
like it has the most efficient execution plan.

FYI, I listed each of your solutions and the number of reads each
took and some additional questions.

NOTE: The 'theLiterals' table would never be big enough to cause more
than a seconds execution but it is always best to strive for
efficiency anyway. I hope you agree.

-- David Portas
-- 6 reads
-- Warning: Null value is eliminated by an aggregate or other SET
operation.
-- Why is MIN so much faster than MAX?
SELECT
defaultServer = MIN(CASE WHEN theKey = 'defaultServer' THEN theValue
END),
defaultShare = MIN(CASE WHEN theKey = 'defaultShare' THEN theValue
END),
defaultFolder = MIN(CASE WHEN theKey = 'defaultFolder' THEN theValue
END),
defaultFile = MIN(CASE WHEN theKey = 'defaultFile' THEN theValue
END)
FROM theLiterals

-- Mischa Sandberg
-- 18 reads
-- Warning: Null value is eliminated by an aggregate or other SET
operation.
-- Why is MIN so much faster than MAX or is it the way the CASE-WHEN
is
-- formatted?
SELECT
defaultServer = max(case theKey when 'defaultServer' then theValue
end)
,defaultShare = max(case theKey when 'defaultShare' then theValue
end)
,defaultFolder = max(case theKey when 'defaultFolder' then theValue
end)
,defaultFile = max(case theKey when 'defaultFile' then theValue
end)
FROM theLiterals

-- John Bell
-- 24 reads
SELECT
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultServer' ) AS
defaultServer ,
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultShare' ) AS
defaultShare ,
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultFolder' ) AS
defaultFolder ,
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultFile' ) AS
defaultFile

-- John Bell
-- 24 reads
SELECT A.defaultServer, B.defaultShare, C.defaultFolder,
D.defaultFile
FROM
( SELECT theValue AS defaultServer FROM theLiterals WHERE theKey =
'defaultServer' ) A,
( SELECT theValue AS defaultShare FROM theLiterals WHERE theKey =
'defaultShare' ) B,
( SELECT theValue AS defaultFolder FROM theLiterals WHERE theKey =
'defaultFolder' ) C,
( SELECT theValue AS defaultFile FROM theLiterals WHERE theKey =
'defaultFile' ) D

-- Jim Geissman
-- 80 reads
-- Taking off the outside 'FROM theLiterals' returns only the one
record rather
-- than four duplicate records. Therefore the TOP function is then
not needed.
-- So the query becomes the same as John Bell's above with 24 reads
SELECT TOP 1
defaultServer = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultServer'),
defaultShare = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultShare'),
defaultFolder = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultFolder'),
defaultFile = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultFile')
FROM theLiterals

Easiest method for moving databases from one partition to another

The first SQL install went on the smaller partition and we need to
move it from C: to D:. It seems to me there should be a startup
parameter we can change, stop the server, move the databases, and
restart the server. Our DB admin is saying we need a full reinstall.
It seems to me this should be easiert. I am seeing pathing
information in the database parameters in the server properties tabs
in enterprize manager. Can I just change those, do my move, and
restart?
suggestions greatly appreciated
Hal
Hi
If you want to move the databases, look at sp_attachdb and sp_detachdb in BOL.
No need for re-install. (The location of master DB is in the registry so
moving that takes a bit more effort).
If you want to move the SQL EXE's, then un-install and re-install is required.
Regards
Mike
"hal@.nospam.com" wrote:

> The first SQL install went on the smaller partition and we need to
> move it from C: to D:. It seems to me there should be a startup
> parameter we can change, stop the server, move the databases, and
> restart the server. Our DB admin is saying we need a full reinstall.
> It seems to me this should be easiert. I am seeing pathing
> information in the database parameters in the server properties tabs
> in enterprize manager. Can I just change those, do my move, and
> restart?
> suggestions greatly appreciated
> Hal
>
|||If you want to move the master database, you can add some options to the
sqlservr.exe program that is started as a service, you should use something
like
sqlservr -d<new masterdatafilepath> -l<new master log path>
Marc
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:21A185C6-89AD-4DC6-901F-B770BD9CA72D@.microsoft.com...
> Hi
> If you want to move the databases, look at sp_attachdb and sp_detachdb in
BOL.
> No need for re-install. (The location of master DB is in the registry so
> moving that takes a bit more effort).
> If you want to move the SQL EXE's, then un-install and re-install is
required.[vbcol=seagreen]
> Regards
> Mike
> "hal@.nospam.com" wrote:

Easiest method for moving databases from one partition to another

The first SQL install went on the smaller partition and we need to
move it from C: to D:. It seems to me there should be a startup
parameter we can change, stop the server, move the databases, and
restart the server. Our DB admin is saying we need a full reinstall.
It seems to me this should be easiert. I am seeing pathing
information in the database parameters in the server properties tabs
in enterprize manager. Can I just change those, do my move, and
restart?
suggestions greatly appreciated
HalHi
If you want to move the databases, look at sp_attachdb and sp_detachdb in BO
L.
No need for re-install. (The location of master DB is in the registry so
moving that takes a bit more effort).
If you want to move the SQL EXE's, then un-install and re-install is require
d.
Regards
Mike
"hal@.nospam.com" wrote:

> The first SQL install went on the smaller partition and we need to
> move it from C: to D:. It seems to me there should be a startup
> parameter we can change, stop the server, move the databases, and
> restart the server. Our DB admin is saying we need a full reinstall.
> It seems to me this should be easiert. I am seeing pathing
> information in the database parameters in the server properties tabs
> in enterprize manager. Can I just change those, do my move, and
> restart?
> suggestions greatly appreciated
> Hal
>|||If you want to move the master database, you can add some options to the
sqlservr.exe program that is started as a service, you should use something
like
sqlservr -d<new masterdatafilepath> -l<new master log path>
Marc
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:21A185C6-89AD-4DC6-901F-B770BD9CA72D@.microsoft.com...
> Hi
> If you want to move the databases, look at sp_attachdb and sp_detachdb in
BOL.
> No need for re-install. (The location of master DB is in the registry so
> moving that takes a bit more effort).
> If you want to move the SQL EXE's, then un-install and re-install is
required.[vbcol=seagreen]
> Regards
> Mike
> "hal@.nospam.com" wrote:
>sql

Easiest method for moving databases from one partition to another

The first SQL install went on the smaller partition and we need to
move it from C: to D:. It seems to me there should be a startup
parameter we can change, stop the server, move the databases, and
restart the server. Our DB admin is saying we need a full reinstall.
It seems to me this should be easiert. I am seeing pathing
information in the database parameters in the server properties tabs
in enterprize manager. Can I just change those, do my move, and
restart?
suggestions greatly appreciated
HalHi
If you want to move the databases, look at sp_attachdb and sp_detachdb in BOL.
No need for re-install. (The location of master DB is in the registry so
moving that takes a bit more effort).
If you want to move the SQL EXE's, then un-install and re-install is required.
Regards
Mike
"hal@.nospam.com" wrote:
> The first SQL install went on the smaller partition and we need to
> move it from C: to D:. It seems to me there should be a startup
> parameter we can change, stop the server, move the databases, and
> restart the server. Our DB admin is saying we need a full reinstall.
> It seems to me this should be easiert. I am seeing pathing
> information in the database parameters in the server properties tabs
> in enterprize manager. Can I just change those, do my move, and
> restart?
> suggestions greatly appreciated
> Hal
>|||If you want to move the master database, you can add some options to the
sqlservr.exe program that is started as a service, you should use something
like
sqlservr -d<new masterdatafilepath> -l<new master log path>
Marc
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:21A185C6-89AD-4DC6-901F-B770BD9CA72D@.microsoft.com...
> Hi
> If you want to move the databases, look at sp_attachdb and sp_detachdb in
BOL.
> No need for re-install. (The location of master DB is in the registry so
> moving that takes a bit more effort).
> If you want to move the SQL EXE's, then un-install and re-install is
required.
> Regards
> Mike
> "hal@.nospam.com" wrote:
> > The first SQL install went on the smaller partition and we need to
> > move it from C: to D:. It seems to me there should be a startup
> > parameter we can change, stop the server, move the databases, and
> > restart the server. Our DB admin is saying we need a full reinstall.
> > It seems to me this should be easiert. I am seeing pathing
> > information in the database parameters in the server properties tabs
> > in enterprize manager. Can I just change those, do my move, and
> > restart?
> >
> > suggestions greatly appreciated
> >
> > Hal
> >