Migrate MSDE data to SQL 2000

I have local copy of MSDE 2000 on an XP workstation.
My program has already been reconfigured to point to a remote (from the XP
viewpoint) SQL 2000 server. As such, it has created the 2 databases that it
uses and is already populating those databases with data.
The question is, how do I get the "old" data from MSDE on the XP box into
the "new" databases on the remote SQL server?
When I try to use DTS and choosing the default of OLE Provider for SQL for
the source, I can't connect to the XP MSDE, which is using SQL Auth. I know
I am using the correct "sa" password for the MSDE database.
Robert
You have a few options: DTS, Detatch/Reattach, or Backup/Restore.
I prefer to backup on the old server, copy the backup file to the new
server, and restore it on the new server. This is fairly easy to do. Just
make sure you create the users from the old server on the new server first,
and then run sp_change_users_login to eliminate orphaned users after you do
the restore.
"Robert Gandrud" wrote:

> I have local copy of MSDE 2000 on an XP workstation.
> My program has already been reconfigured to point to a remote (from the XP
> viewpoint) SQL 2000 server. As such, it has created the 2 databases that it
> uses and is already populating those databases with data.
> The question is, how do I get the "old" data from MSDE on the XP box into
> the "new" databases on the remote SQL server?
> When I try to use DTS and choosing the default of OLE Provider for SQL for
> the source, I can't connect to the XP MSDE, which is using SQL Auth. I know
> I am using the correct "sa" password for the MSDE database.
> Robert
|||As far as I know, the only user is the "sa" user in the old MSDE database.
What then?
"Dan" wrote:
[vbcol=seagreen]
> You have a few options: DTS, Detatch/Reattach, or Backup/Restore.
> I prefer to backup on the old server, copy the backup file to the new
> server, and restore it on the new server. This is fairly easy to do. Just
> make sure you create the users from the old server on the new server first,
> and then run sp_change_users_login to eliminate orphaned users after you do
> the restore.
> "Robert Gandrud" wrote:
|||hi Robert,
Robert Gandrud wrote:
> I have local copy of MSDE 2000 on an XP workstation.
> My program has already been reconfigured to point to a remote (from
> the XP viewpoint) SQL 2000 server. As such, it has created the 2
> databases that it uses and is already populating those databases with
> data.
> The question is, how do I get the "old" data from MSDE on the XP box
> into the "new" databases on the remote SQL server?
> When I try to use DTS and choosing the default of OLE Provider for
> SQL for the source, I can't connect to the XP MSDE, which is using
> SQL Auth. I know I am using the correct "sa" password for the MSDE
> database.
>
MSDE installs by default disabling network protocols...in order to them
after installation you have to run the
Server Network Utility (svrnetcn.exe) and enabling the desired
protocol(s)...
Win XP Windows Firewall could be an issue too and please have a look at
http://support.microsoft.com/kb/841251/en-us in order to enable remote
connectivity...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

Microsofts Sql web admin and sqldmo

Hello all,

has anyone had the opportunity to try the sql web admin tool? if so, i have one question.

How can i use this to connect to a remote sql/msde server?

in the server textbox on the login screen i try 'machine name'\'instance name', along with the sa username and password. This does not work however.

am i just entering the wrong value , or do i need to change the code of the .connect method to allow for remote connections?

TIA.

-C-By "does not work", do you mean you are getting an error?

I have been successful using Web Data Administrator to connect to a remote database. The remote database must have mixed authentication (SQL Server and Windows). and you must be using a valid login and password. I accessed the server using the IP address.

As an additional point, I was VPN authenticated on the remote network before connecting.

Terri

Microsoft.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL S

Hi Everybody,

On localhost this application works fine but when I put on remote server. I am getting following errors. For both localhost and server, I am using same remote sql 2000. I will appreciate any help.

Thanks,

Arif

Server Error in '/' Application.
------------------------

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Microsoft.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.]
Microsoft.Data.Odbc.OdbcConnection.HandleError(IntPtr hHandle, SQL_HANDLE hType, RETCODE retcode) +27
Microsoft.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method) +838
Microsoft.Data.Odbc.OdbcCommand.ExecuteNonQuery() +80
Calgary.venues.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\Calgary\Site\venues\venues.aspx.vb:32
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731are you using ODBC to connect to sql server ??if so you should be using sqlclient...its much better integrated into .net...

hth|||Hi,

Thanks for reply. I am doing some maintaince on some existing code. Somebody else wrote the code. Application was runing againt MySQL but I am converting over to SQL 2000.

Thanks,

Arif|||Here are the Code: Its failing on myCommand.ExecuteNonQuery(). It was working fine before. Even now its works fine on my localhost.

Thanks,

Arif

Dim strSQL As String

' strSQL = "SELECT * from tblbands where name = "

' strSQL = "SELECT tblAdminUsers_ID as Expr1, RealName as Expr2 From tblAdminUsers order by RealName"

Dim sBandName = Request("bandID")

If sBandName = "09" Then

strSQL = "select name, location, genre from tblbands,tbllocation,tblgenre

WHERE(tblbands.Location_ID = tbllocation.tblLocation_ID)AND tblgenre.tblGenre_ID =

tblbands.Genre_ID and tblbands.isapproved=1 and tblbands.name between '0'

AND '9'ORDER BY name "

End If

If sBandName = "AK" Then

strSQL = "select name, location, genre from tblbands,tbllocation,tblgenre

WHERE(tblbands.Location_ID = tbllocation.tblLocation_ID)AND tblgenre.tblGenre_ID =

tblbands.Genre_ID and tblbands.isapproved=1 and tblbands.name between 'A' AND 'K'

ORDER BY name"

End If

If sBandName = "LZ" Then

strSQL = "select name, location, genre from tblbands,tbllocation,tblgenre

WHERE(tblbands.Location_ID = tbllocation.tblLocation_ID)AND tblgenre.tblGenre_ID =

tblbands.Genre_ID and tblbands.isapproved=1 and tblbands.name between 'L' AND 'Z'

ORDER BY name "

End If

Dim myConnString = ConfigurationSettings.AppSettings("ConnectionString")

Dim myConn As New OdbcConnection(myConnString)

Dim myCommand As New OdbcCommand(strSQL, myConn)

myConn.Open()

myCommand.ExecuteNonQuery()

Dim myReader As OdbcDataReader = myCommand.ExecuteReader()

Repeater1.DataSource = myReader

Repeater1.DataBind()

myReader.Close()

myConn.Close()|||remove tht line...you are already using the execute reader...
comment out this line
myCommand.ExecuteNonQuery()

hth|||Same code was working before. Thanks and let me test again.

best regards,

arif

Microsoft SSMSE - Printing database layout?

I'm connected to my remote SQL 2000 server using Microsofts SQL Server
Management Studio Express.
I can't see any way to print out my database table properties. Under
Database Diagrams all I have is "Working with SQL Server 2000 Diagrams",
which does not work.
How does one print out the layout of their database from this app?
Thanks!Noozer
Do you want to print a diagram by using SSMS of the SQL Server 2000
database?
"Noozer" <dont.spam@.me.here> wrote in message
news:%23S4naZ$ZGHA.4060@.TK2MSFTNGP02.phx.gbl...
> I'm connected to my remote SQL 2000 server using Microsofts SQL Server
> Management Studio Express.
> I can't see any way to print out my database table properties. Under
> Database Diagrams all I have is "Working with SQL Server 2000 Diagrams",
> which does not work.
> How does one print out the layout of their database from this app?
> Thanks!
>|||Diagrams would be great!
But, I'd settle for simply printing the properties from each of my tables.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23$oEp0CaGHA.4292@.TK2MSFTNGP02.phx.gbl...
> Noozer
> Do you want to print a diagram by using SSMS of the SQL Server 2000
> database?
>
>
> "Noozer" <dont.spam@.me.here> wrote in message
> news:%23S4naZ$ZGHA.4060@.TK2MSFTNGP02.phx.gbl...
>> I'm connected to my remote SQL 2000 server using Microsofts SQL Server
>> Management Studio Express.
>> I can't see any way to print out my database table properties. Under
>> Database Diagrams all I have is "Working with SQL Server 2000 Diagrams",
>> which does not work.
>> How does one print out the layout of their database from this app?
>> Thanks!
>|||Noozer
What properties? There is an option Script Table As , are you talking about
this feature?
"Noozer" <dont.spam@.me.here> wrote in message
news:%23EuOR6CaGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Diagrams would be great!
> But, I'd settle for simply printing the properties from each of my tables.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23$oEp0CaGHA.4292@.TK2MSFTNGP02.phx.gbl...
>> Noozer
>> Do you want to print a diagram by using SSMS of the SQL Server 2000
>> database?
>>
>>
>> "Noozer" <dont.spam@.me.here> wrote in message
>> news:%23S4naZ$ZGHA.4060@.TK2MSFTNGP02.phx.gbl...
>> I'm connected to my remote SQL 2000 server using Microsofts SQL Server
>> Management Studio Express.
>> I can't see any way to print out my database table properties. Under
>> Database Diagrams all I have is "Working with SQL Server 2000 Diagrams",
>> which does not work.
>> How does one print out the layout of their database from this app?
>> Thanks!
>>
>|||Why can't you go to File, Print...
or right-click the db diagram, "Copy Diagram to Clipboard," and paste/print
from Word?
"Noozer" <dont.spam@.me.here> wrote in message
news:%23EuOR6CaGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Diagrams would be great!
> But, I'd settle for simply printing the properties from each of my tables.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23$oEp0CaGHA.4292@.TK2MSFTNGP02.phx.gbl...
>> Noozer
>> Do you want to print a diagram by using SSMS of the SQL Server 2000
>> database?
>>
>>
>> "Noozer" <dont.spam@.me.here> wrote in message
>> news:%23S4naZ$ZGHA.4060@.TK2MSFTNGP02.phx.gbl...
>> I'm connected to my remote SQL 2000 server using Microsofts SQL Server
>> Management Studio Express.
>> I can't see any way to print out my database table properties. Under
>> Database Diagrams all I have is "Working with SQL Server 2000 Diagrams",
>> which does not work.
>> How does one print out the layout of their database from this app?
>> Thanks!
>>
>|||Because "Print..." is coming up disabled.
The server is a remote MS SQL 2000 server.
Diagrams does not work at all.
"JFoushee" <someone@.somewhere-donotspam.com> wrote in message
news:O$O9PTRbGHA.4116@.TK2MSFTNGP05.phx.gbl...
> Why can't you go to File, Print...
> or right-click the db diagram, "Copy Diagram to Clipboard," and
> paste/print from Word?
> "Noozer" <dont.spam@.me.here> wrote in message
> news:%23EuOR6CaGHA.1192@.TK2MSFTNGP03.phx.gbl...
>> Diagrams would be great!
>> But, I'd settle for simply printing the properties from each of my
>> tables.
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23$oEp0CaGHA.4292@.TK2MSFTNGP02.phx.gbl...
>> Noozer
>> Do you want to print a diagram by using SSMS of the SQL Server 2000
>> database?
>>
>>
>> "Noozer" <dont.spam@.me.here> wrote in message
>> news:%23S4naZ$ZGHA.4060@.TK2MSFTNGP02.phx.gbl...
>> I'm connected to my remote SQL 2000 server using Microsofts SQL Server
>> Management Studio Express.
>> I can't see any way to print out my database table properties. Under
>> Database Diagrams all I have is "Working with SQL Server 2000
>> Diagrams", which does not work.
>> How does one print out the layout of their database from this app?
>> Thanks!
>>
>>
>

Microsoft SSMSE - Printing database layout?

I'm connected to my remote SQL 2000 server using Microsofts SQL Server
Management Studio Express.
I can't see any way to print out my database table properties. Under
Database Diagrams all I have is "Working with SQL Server 2000 Diagrams",
which does not work.
How does one print out the layout of their database from this app?
Thanks!Noozer
Do you want to print a diagram by using SSMS of the SQL Server 2000
database?
"Noozer" <dont.spam@.me.here> wrote in message
news:%23S4naZ$ZGHA.4060@.TK2MSFTNGP02.phx.gbl...
> I'm connected to my remote SQL 2000 server using Microsofts SQL Server
> Management Studio Express.
> I can't see any way to print out my database table properties. Under
> Database Diagrams all I have is "Working with SQL Server 2000 Diagrams",
> which does not work.
> How does one print out the layout of their database from this app?
> Thanks!
>|||Diagrams would be great!
But, I'd settle for simply printing the properties from each of my tables.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23$oEp0CaGHA.4292@.TK2MSFTNGP02.phx.gbl...
> Noozer
> Do you want to print a diagram by using SSMS of the SQL Server 2000
> database?
>
>
> "Noozer" <dont.spam@.me.here> wrote in message
> news:%23S4naZ$ZGHA.4060@.TK2MSFTNGP02.phx.gbl...
>|||Noozer
What properties? There is an option Script Table As , are you talking about
this feature?
"Noozer" <dont.spam@.me.here> wrote in message
news:%23EuOR6CaGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Diagrams would be great!
> But, I'd settle for simply printing the properties from each of my tables.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23$oEp0CaGHA.4292@.TK2MSFTNGP02.phx.gbl...
>|||Why can't you go to File, Print...
or right-click the db diagram, "Copy Diagram to Clipboard," and paste/print
from Word?
"Noozer" <dont.spam@.me.here> wrote in message
news:%23EuOR6CaGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Diagrams would be great!
> But, I'd settle for simply printing the properties from each of my tables.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23$oEp0CaGHA.4292@.TK2MSFTNGP02.phx.gbl...
>|||Because "Print..." is coming up disabled.
The server is a remote MS SQL 2000 server.
Diagrams does not work at all.
"JFoushee" <someone@.somewhere-donotspam.com> wrote in message
news:O$O9PTRbGHA.4116@.TK2MSFTNGP05.phx.gbl...
> Why can't you go to File, Print...
> or right-click the db diagram, "Copy Diagram to Clipboard," and
> paste/print from Word?
> "Noozer" <dont.spam@.me.here> wrote in message
> news:%23EuOR6CaGHA.1192@.TK2MSFTNGP03.phx.gbl...
>

Microsoft SQL Server, Error: 87 17march

Source computer: SQL server 2005 SP1
Remote computer : SQLEXPRESS
When I try to register/connect with remote server (named instance) using sql
server management studio I am getting following error:
TITLE: New Server Registration
--
Testing the registered server failed. Verify the server name, login
credentials, and database, and then click Test again.
--
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 25 - Connection string is not
valid) (Microsoft SQL Server, Error: 87)
Thanks
Ontario, canadaSource computer: SQL server 2005 SP1 (Microsoft windows nt 5.1)
Remote computer : Microsoft SQL server express edition (Microsoft windows
nt 5.1)
-
ontario, canada
"db" wrote:
> Source computer: SQL server 2005 SP1
> Remote computer : SQLEXPRESS
>
> When I try to register/connect with remote server (named instance) using sql
> server management studio I am getting following error:
> TITLE: New Server Registration
> --
> Testing the registered server failed. Verify the server name, login
> credentials, and database, and then click Test again.
> --
> ADDITIONAL INFORMATION:
> An error has occurred while establishing a connection to the server. When
> connecting to SQL Server 2005, this failure may be caused by the fact that
> under the default settings SQL Server does not allow remote connections.
> (provider: SQL Network Interfaces, error: 25 - Connection string is not
> valid) (Microsoft SQL Server, Error: 87)
>
> Thanks
> Ontario, canada

Microsoft SQL Server, Error: 87 17march

Source computer: SQL server 2005 SP1
Remote computer : SQLEXPRESS
When I try to register/connect with remote server (named instance) using sql
server management studio I am getting following error:
TITLE: New Server Registration
Testing the registered server failed. Verify the server name, login
credentials, and database, and then click Test again.
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 25 - Connection string is not
valid) (Microsoft SQL Server, Error: 87)
Thanks
Ontario, canada
Source computer: SQL server 2005 SP1 (Microsoft windows nt 5.1)
Remote computer : Microsoft SQL server express edition (Microsoft windows
nt 5.1)
-
ontario, canada
"db" wrote:

> Source computer: SQL server 2005 SP1
> Remote computer : SQLEXPRESS
>
> When I try to register/connect with remote server (named instance) using sql
> server management studio I am getting following error:
> TITLE: New Server Registration
> --
> Testing the registered server failed. Verify the server name, login
> credentials, and database, and then click Test again.
> --
> ADDITIONAL INFORMATION:
> An error has occurred while establishing a connection to the server. When
> connecting to SQL Server 2005, this failure may be caused by the fact that
> under the default settings SQL Server does not allow remote connections.
> (provider: SQL Network Interfaces, error: 25 - Connection string is not
> valid) (Microsoft SQL Server, Error: 87)
>
> Thanks
> Ontario, canada

Microsoft SQL Server, Error: 53

hi, i am trying to acces my database but i get this error. I allowed remote
connections in sql server 2005 but i still get the error.
TITLE: Connect to Server
--
Cannot connect to MSSQLSERVER.
--
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server) (Microsoft SQL Server, Error: 53)
Hoping for some help , kind regards xenta.How is the client configured? Check with cliconfg.exe.
What app are you using? If not a tool like QA, SSMS etc, then what does the connection string look
like?
How is the server configured? Check with SQL Server Configuration Manager. Does it listen to Named
Pipes, for instance.
The error states that the client tries to connect using Named Pipes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"zentax" <millionsmillions@.hotmail.com> wrote in message
news:eYGOxgcdHHA.2088@.TK2MSFTNGP05.phx.gbl...
> hi, i am trying to acces my database but i get this error. I allowed remote connections in sql
> server 2005 but i still get the error.
> TITLE: Connect to Server
> --
> Cannot connect to MSSQLSERVER.
> --
> ADDITIONAL INFORMATION:
> An error has occurred while establishing a connection to the server. When connecting to SQL
> Server 2005, this failure may be caused by the fact that under the default settings SQL Server
> does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a
> connection to SQL Server) (Microsoft SQL Server, Error: 53)
>
> Hoping for some help , kind regards xenta.
>
>