Migrating ext drive array

Please consider the following scenario
I have sql 7 NT server the application is on the C:\
and the databases are stored on a external drive array
i.e D:\
What i want to do is migrate the external drive array
from the above server to a new sql7 NT server of higher
spec.
Has anyone attempted this before or any ideas on how to
do this. I believe that i will have to start sql in
single user mode after the arrray is attached and run a
attach comand within query analyzer.
Any help would be much appreciated.You won't have to start the server in single-user mode... But you will have
to put the database in single-user mode to detach it (if that's how you
choose to get it over to the new server.) Your options are to detach it
from the old server, copy it, and re-attach it, or back it up and restore it
on the new server... Pretty much equivalent options except that backup will
double the disc space required (as you will have the database files as well
as the backup file to deal with); detach/attach, on the other hand, will
require you to A) put the database into single-user mode, and B) take it
offline while you copy the files, which you will not have to do with a
backup. So weigh your options carefully...
"Jonathan Gray" <jonathan.gray@.exel.com> wrote in message
news:0dbc01c3adda$7e102950$a101280a@.phx.gbl...
> Please consider the following scenario
> I have sql 7 NT server the application is on the C:\
> and the databases are stored on a external drive array
> i.e D:\
> What i want to do is migrate the external drive array
> from the above server to a new sql7 NT server of higher
> spec.
> Has anyone attempted this before or any ideas on how to
> do this. I believe that i will have to start sql in
> single user mode after the arrray is attached and run a
> attach comand within query analyzer.
>
> Any help would be much appreciated.

Migrating database

Can anyone tell me how I can migrate database from one server to another
I need to migrate all the tables, stored procedures, functions and so on

thank youYou can use the Copy Database Wizard to achieve this. See BOL for more info.

As you are moving the database to a new server, you will have to manually delete the data and log files on the old server once the copy process has completed.|||Can anyone tell me how I can migrate database from one server to another
I need to migrate all the tables, stored procedures, functions and so on

thank you

As noted earlier, you can use the Copy Database Objects wizard. Or:

1. You can load a backup of your database onto the new database (you will have to manually add in SQL Logins and resynch those logins with the users in the new database).

2. You can detach the old database, copy over the .mdf and .ldf files and then attach it to a new server (again, you will have to manually handle SQL Logins and synch them to the new db).

Regards,

hmscott|||I dont want to detach the old database. That needs to stay the way it is. Can I just copy over all the tables, sp, views and fns along with the logins. Would it work that way. I know its going to be a lot of work but it seems like the simplest way to go without making changes to the old database

Migrate to Compact Edition

What is the fastest way to migrate a database from SQL server express to sql compact edition. My database includes one stored procedure. Do I have to rewrite it into the code of my application?You can use some of the tools from www.primeworks-mobile.com (Data Port Wizard, for example) and yes, stored procedures does not exist in SQL Compact, so you will have to rewrite it as procedural code.|||

It looks fantastic, but it fails when I try to connect it to a SQL compact database even it does not have a password

Migrate to Compact Edition

What is the fastest way to migrate a database from SQL server express to sql compact edition. My database includes one stored procedure. Do I have to rewrite it into the code of my application?You can use some of the tools from www.primeworks-mobile.com (Data Port Wizard, for example) and yes, stored procedures does not exist in SQL Compact, so you will have to rewrite it as procedural code.|||

It looks fantastic, but it fails when I try to connect it to a SQL compact database even it does not have a password

migrate sql2k dts packages to sql2k5

new to sql2k5.
i have some sql2k dts packages i migrated to sql2k5 using wizard and
they showed up under intergration services, stored packages, msdb, but
I can't open or edit any of the package.
For the same packages, if I saved them as external file in sql2k, then
import them in sql2k5, they will show up in SSMS, management, legacy,
DTS folder, and if had downloaded the Microsoft SQL Server 2000 DTS
Designer Components , I can open and edit the packages.
This is all very confusing to me.
first, why the dts showed up at different place in sql2k5 (what's the
difference between import and migrate?)
2nd, how to edit the migrated dts packages?
3rd, in sql2k, dts package is part of msdb, as long as I backup msdb, I
can restore all the pckages, how about in sql2k5, where are dts being
stored and are they still part of msdb?
thank you.=== Steve L === wrote:
> new to sql2k5.
> i have some sql2k dts packages i migrated to sql2k5 using wizard and
> they showed up under intergration services, stored packages, msdb, but
> I can't open or edit any of the package.
> For the same packages, if I saved them as external file in sql2k, then
> import them in sql2k5, they will show up in SSMS, management, legacy,
> DTS folder, and if had downloaded the Microsoft SQL Server 2000 DTS
> Designer Components , I can open and edit the packages.
> This is all very confusing to me.
> first, why the dts showed up at different place in sql2k5 (what's the
> difference between import and migrate?)
> 2nd, how to edit the migrated dts packages?
> 3rd, in sql2k, dts package is part of msdb, as long as I backup msdb, I
> can restore all the pckages, how about in sql2k5, where are dts being
> stored and are they still part of msdb?
> thank you.
>
DTS doesn't exist in SQL 2005, it has been replaced with Integration
Services...|||DTS services is no loginer in sql2k5, but you can still import or
migrate sql2k dts to it. I think you are missing what I asked in my
posting.
Tracy McKibben wrote:
> === Steve L === wrote:
> DTS doesn't exist in SQL 2005, it has been replaced with Integration
> Services...

Migrate SQL Server StoredProcedures

Dear Users
Can any body tell me that how can I migrate SQL Server Stored Procedures to Oracle using DTS. I want to do it in VB.Net.
Thanks
OsmanYou can't migrate the actual procedures themselves, but you could migrate the text of the procedure to Oracle. I'm not sure what good that would do you.

What exactly do you want to do once you get the procedures into Oracle?

-PatP

Microsoft SQL-DMO [SQL-DMO]Out of memory error

Hi,
We have a stored procedure that we call very frequently
that polls servers to see if they are available. This is
used from various other stored procedures that may then
use an available SQL Server or try another one.
We have implemented the stored procedure such that it uses
the SP_OA routines to perform a SQL-DMO connect. This
works very well and allows us to catch the 'server
unavailable' error in stored procedure code and branch.
Unfortunataly, if the SQL Server that issues the connect
has not been restarted for a few says we get the following
message back from the connect method (from
sp_OAGetErrorInfo):
0x80045900 Microsoft SQL-DMO [SQL-DMO]Out of
memory.
I have included the code fragment below we are using. Has
anyone any thoughts, please, on where our memory leak is
happening. All our SQL Servers are SP2 currently.
Many thanks, Ken
---
CREATE PROCEDURE xxx @.ServerName VARCHAR(20),
@.LoginTimeout INT = 10
AS
SET NOCOUNT ON
DECLARE @.iRet int
DECLARE @.object int
DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)
-- @.iRet - return value of procedures
-- @.object - object reference
-- @.hr - handle
-- @.src - source of error message
-- @.desc - description of error message
-- Set up parameters/variables
SELECT @.iRet = 0, @.hr = 0, @.object = 0
-- create SQLDMO SQLServer object
EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT,
@.desc OUT
SELECT hr=convert(varbinary(4),@.hr),
Source=@.src, Description=@.desc
RETURN (2)
END
-- Set the properties
-- Set the LoginTimeout property.
EXEC @.hr = sp_OASetProperty @.object, 'LoginTimeout',
@.LoginTimeout
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
Description=@.desc
EXEC @.hr = sp_OADestroy @.object
RETURN (3)
END
-- Set the integrated login property.
EXEC @.hr = sp_OASetProperty @.object,'LoginSecure',1
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
Description=@.desc
EXEC @.hr = sp_OADestroy @.object
RETURN (4)
END
-- attempt to connect to server
EXEC @.hr = sp_OAMethod @.object, 'Connect', NULL,
@.ServerName
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
Description=@.desc
SELECT @.iRet = convert(varbinary(4),@.hr)
EXEC @.hr = sp_OADestroy @.object
RETURN (CASE @.iRet WHEN 0x80020009 THEN 2 ELSE 1
END) -- 2 = 'SQL Server does not exist or access denied',
1 = misc. error
END
-- Disconnect
EXEC @.hr = sp_OAMethod @.object, 'DisConnect'
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr),
Source=@.src, Description=@.desc
EXEC @.hr = sp_OADestroy @.object
RETURN (5)
END
-- Destroy object
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
Description=@.desc
RETURN (6)
END
-- success
RETURN (0)
GOIt's difficult to trace memory leaks like this. I can't help with the DMO
specific troubleshooting, but... I would encourage to you to review the fix
list for SP3a. I remember reading about some memory leak issues, don't
remember what they were... but you don't want to waste time on a fix in the
latest SP.
Also... do you have an ability to either a) rollout SP3 (which you really
should anyway for security purposes) or b) at least roll it out in a QA
envrionment where you can test this memory leak against Sp3. That might save
you a lot of time...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ken Eng" <keneng23@.hotmail.com> wrote in message
news:0d5001c36e33$069fcfb0$a301280a@.phx.gbl...
> Hi,
> We have a stored procedure that we call very frequently
> that polls servers to see if they are available. This is
> used from various other stored procedures that may then
> use an available SQL Server or try another one.
> We have implemented the stored procedure such that it uses
> the SP_OA routines to perform a SQL-DMO connect. This
> works very well and allows us to catch the 'server
> unavailable' error in stored procedure code and branch.
> Unfortunataly, if the SQL Server that issues the connect
> has not been restarted for a few says we get the following
> message back from the connect method (from
> sp_OAGetErrorInfo):
> 0x80045900 Microsoft SQL-DMO [SQL-DMO]Out of
> memory.
> I have included the code fragment below we are using. Has
> anyone any thoughts, please, on where our memory leak is
> happening. All our SQL Servers are SP2 currently.
>
> Many thanks, Ken
> ---
> CREATE PROCEDURE xxx @.ServerName VARCHAR(20),
> @.LoginTimeout INT = 10
> AS
> SET NOCOUNT ON
>
> DECLARE @.iRet int
> DECLARE @.object int
> DECLARE @.hr int
> DECLARE @.src varchar(255), @.desc varchar(255)
> -- @.iRet - return value of procedures
> -- @.object - object reference
> -- @.hr - handle
> -- @.src - source of error message
> -- @.desc - description of error message
> -- Set up parameters/variables
> SELECT @.iRet = 0, @.hr = 0, @.object = 0
> -- create SQLDMO SQLServer object
> EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT,
> @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr),
> Source=@.src, Description=@.desc
> RETURN (2)
> END
> -- Set the properties
> -- Set the LoginTimeout property.
> EXEC @.hr = sp_OASetProperty @.object, 'LoginTimeout',
> @.LoginTimeout
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
> Description=@.desc
> EXEC @.hr = sp_OADestroy @.object
> RETURN (3)
> END
>
> -- Set the integrated login property.
> EXEC @.hr = sp_OASetProperty @.object,'LoginSecure',1
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
> Description=@.desc
> EXEC @.hr = sp_OADestroy @.object
> RETURN (4)
> END
> -- attempt to connect to server
> EXEC @.hr = sp_OAMethod @.object, 'Connect', NULL,
> @.ServerName
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
> Description=@.desc
> SELECT @.iRet = convert(varbinary(4),@.hr)
> EXEC @.hr = sp_OADestroy @.object
> RETURN (CASE @.iRet WHEN 0x80020009 THEN 2 ELSE 1
> END) -- 2 = 'SQL Server does not exist or access denied',
> 1 = misc. error
> END
> -- Disconnect
> EXEC @.hr = sp_OAMethod @.object, 'DisConnect'
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr),
> Source=@.src, Description=@.desc
> EXEC @.hr = sp_OADestroy @.object
> RETURN (5)
> END
> -- Destroy object
> EXEC @.hr = sp_OADestroy @.object
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
> Description=@.desc
> RETURN (6)
> END
> -- success
> RETURN (0)
> GO
>
>|||Thanks Brian,
I will move to SP3 to take this out of the equation.
Cheers
Ken
>--Original Message--
>It's difficult to trace memory leaks like this. I can't
help with the DMO
>specific troubleshooting, but... I would encourage to you
to review the fix
>list for SP3a. I remember reading about some memory leak
issues, don't
>remember what they were... but you don't want to waste
time on a fix in the
>latest SP.
>Also... do you have an ability to either a) rollout SP3
(which you really
>should anyway for security purposes) or b) at least roll
it out in a QA
>envrionment where you can test this memory leak against
Sp3. That might save
>you a lot of time...
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"Ken Eng" <keneng23@.hotmail.com> wrote in message
>news:0d5001c36e33$069fcfb0$a301280a@.phx.gbl...
>> Hi,
>> We have a stored procedure that we call very frequently
>> that polls servers to see if they are available. This is
>> used from various other stored procedures that may then
>> use an available SQL Server or try another one.
>> We have implemented the stored procedure such that it
uses
>> the SP_OA routines to perform a SQL-DMO connect. This
>> works very well and allows us to catch the 'server
>> unavailable' error in stored procedure code and branch.
>> Unfortunataly, if the SQL Server that issues the connect
>> has not been restarted for a few says we get the
following
>> message back from the connect method (from
>> sp_OAGetErrorInfo):
>> 0x80045900 Microsoft SQL-DMO [SQL-DMO]Out of
>> memory.
>> I have included the code fragment below we are using.
Has
>> anyone any thoughts, please, on where our memory leak is
>> happening. All our SQL Servers are SP2 currently.
>>
>> Many thanks, Ken
>> ---
>> CREATE PROCEDURE xxx @.ServerName VARCHAR(20),
>> @.LoginTimeout INT = 10
>> AS
>> SET NOCOUNT ON
>>
>> DECLARE @.iRet int
>> DECLARE @.object int
>> DECLARE @.hr int
>> DECLARE @.src varchar(255), @.desc varchar(255)
>> -- @.iRet - return value of procedures
>> -- @.object - object reference
>> -- @.hr - handle
>> -- @.src - source of error message
>> -- @.desc - description of error message
>> -- Set up parameters/variables
>> SELECT @.iRet = 0, @.hr = 0, @.object = 0
>> -- create SQLDMO SQLServer object
>> EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT,
>> @.desc OUT
>> SELECT hr=convert(varbinary(4),@.hr),
>> Source=@.src, Description=@.desc
>> RETURN (2)
>> END
>> -- Set the properties
>> -- Set the LoginTimeout property.
>> EXEC @.hr = sp_OASetProperty @.object, 'LoginTimeout',
>> @.LoginTimeout
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
>> Description=@.desc
>> EXEC @.hr = sp_OADestroy @.object
>> RETURN (3)
>> END
>>
>> -- Set the integrated login property.
>> EXEC @.hr = sp_OASetProperty @.object,'LoginSecure',1
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
>> Description=@.desc
>> EXEC @.hr = sp_OADestroy @.object
>> RETURN (4)
>> END
>> -- attempt to connect to server
>> EXEC @.hr = sp_OAMethod @.object, 'Connect', NULL,
>> @.ServerName
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
>> Description=@.desc
>> SELECT @.iRet = convert(varbinary(4),@.hr)
>> EXEC @.hr = sp_OADestroy @.object
>> RETURN (CASE @.iRet WHEN 0x80020009 THEN 2 ELSE 1
>> END) -- 2 = 'SQL Server does not exist or access
denied',
>> 1 = misc. error
>> END
>> -- Disconnect
>> EXEC @.hr = sp_OAMethod @.object, 'DisConnect'
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr),
>> Source=@.src, Description=@.desc
>> EXEC @.hr = sp_OADestroy @.object
>> RETURN (5)
>> END
>> -- Destroy object
>> EXEC @.hr = sp_OADestroy @.object
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
>> Description=@.desc
>> RETURN (6)
>> END
>> -- success
>> RETURN (0)
>> GO
>>
>
>.
>

Microsoft SQL Server Management Studio Express Problem

Microsoft SQL Server Management Studio Express

I have restored the database into it with 100tables and stored procedures.

I have also created the new database in it.

But,i don't know how to add the existing database in to Visual Studio.Net 2005 .I m using ASP.Net.

Where are the database stored ??

I don't want to create the new database,.

Can anyone pls tell me ...

Hi,

To add an existing database .mdf file to SQL Server, you can use Attach. Here are the steps.

1. Copy the .mdf file and .ldf(if exists) to another folder.
2. Right click on Databases, and select Attach from the popup menu.
3. In the Attach Databases dialog box, click Add button and select the .mdf file.
4. Click OK to finish, and the database will be attached to your database list in Management Studio.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!