Access denied after installing XP-SP2

Hi,
we have an app running in a network where the MSDE database is running on
win XP Pro PC. This has been running perfect since january but after
installing XP SP2 we get "SQL server does not exist or access denied" when
trying to run the app from a client. The app runs fine when started on the
server.
See my reply to "MSDE over the network". You may need to apply SP3 to MSDE.
Also, check the Windows Firewall in control panel and make sure that there
is an exception for sqlservr.exe or adjust the firewall accordingly to allow
incoming connections and broadcast packets from MSDE.
Jim
"Olav" <Olav@.discussions.microsoft.com> wrote in message
news:CB7DBF9C-07DA-4EF0-A822-C7F1EE5617D4@.microsoft.com...
> Hi,
> we have an app running in a network where the MSDE database is running on
> win XP Pro PC. This has been running perfect since january but after
> installing XP SP2 we get "SQL server does not exist or access denied" when
> trying to run the app from a client. The app runs fine when started on the
> server.
|||Thanks Jim,
making an exception for sqlserver.exe solved it!
"Jim Young" wrote:

> See my reply to "MSDE over the network". You may need to apply SP3 to MSDE.
> Also, check the Windows Firewall in control panel and make sure that there
> is an exception for sqlservr.exe or adjust the firewall accordingly to allow
> incoming connections and broadcast packets from MSDE.
> Jim
>
> "Olav" <Olav@.discussions.microsoft.com> wrote in message
> news:CB7DBF9C-07DA-4EF0-A822-C7F1EE5617D4@.microsoft.com...
>
>

Access Deleting from MSDE - Help!

Hi
I am a newbie to MSDE and am having a problem deleting records from an MSDE
backend using an Access query front end.
Our main data is stored on a UNIX server (informix type database) and we
link to it using ODBC. We load records to be updated into an Access database
and then use the Access table as a reference for updating the MSDE database.
This means I have a reference table in Access that is joined to the MSDE
table. The records that exist in the Access table are the ones to be deleted
from MSDE. When I use the Access UI to create a 'delete' query, I get an
error message (cannot delete records from this table) when I try to run the
query. I can delete the records individually.
Can anyone help with this please?
Regards
Tom
hi Tom,
Tom wrote:
> Hi
> I am a newbie to MSDE and am having a problem deleting records from
> an MSDE backend using an Access query front end.
> Our main data is stored on a UNIX server (informix type database) and
> we link to it using ODBC. We load records to be updated into an
> Access database and then use the Access table as a reference for
> updating the MSDE database. This means I have a reference table in
> Access that is joined to the MSDE table. The records that exist in
> the Access table are the ones to be deleted from MSDE. When I use the
> Access UI to create a 'delete' query, I get an error message (cannot
> delete records from this table) when I try to run the query. I can
> delete the records individually.
> Can anyone help with this please?
when defining the linked table in Access, did you identify the column(s)
uniquelly idenitying each row (primary key)?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea
In the Access UI I have the two tables. The local Access table is called
'WORKS_ORDERS_TO_DELETE'. This has a single field, [WORKS_ORDER], which is a
works order number. It joins to the MSDE table ('tblLOCAL_WORKS_ORDER')
where it joins to a field of the same name, [WORKS_ORDER]. The join is set
to select all from 'WORKS_ORDER_TO_DELETE', and only those that match on the
join in 'tblLOCAL_WORKS_ORDER'. I selected all fields in
'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then set the
query as a 'delete', so the only field in the UI grid is labelled
'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option is set to
'From'.
I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
database and I get the same error message, so the problem is nothing to do
with MSDE: it seems to be when you are identifying the records to delete
through a join. I have also tried specifying all the columns individually in
the records to be deleted, with the same result. I have also added the
single field in the table 'WORKS_ORDERS_TO_DELETE' to the grid and set it's
delete option as 'Where', both with and without criteria
('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').
Sorry to be so long winded answering your question, but I thought it best to
give all the details rather than to try and guess at what you meant.
Regards
Tom
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3cadgbF6mrrenU1@.individual.net...
> hi Tom,
> Tom wrote:
> when defining the linked table in Access, did you identify the column(s)
> uniquelly idenitying each row (primary key)?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Tom,
Tom wrote:
> Hi Andrea
> In the Access UI I have the two tables. The local Access table is
> called 'WORKS_ORDERS_TO_DELETE'. This has a single field,
> [WORKS_ORDER], which is a works order number. It joins to the MSDE
> table ('tblLOCAL_WORKS_ORDER') where it joins to a field of the same
> name, [WORKS_ORDER]. The join is set to select all from
> 'WORKS_ORDER_TO_DELETE', and only those that match on the join in
> 'tblLOCAL_WORKS_ORDER'. I selected all fields in
> 'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then
> set the query as a 'delete', so the only field in the UI grid is
> labelled 'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option
> is set to 'From'.
> I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
> database and I get the same error message, so the problem is nothing
> to do with MSDE: it seems to be when you are identifying the records
> to delete through a join. I have also tried specifying all the
> columns individually in the records to be deleted, with the same
> result. I have also added the single field in the table
> 'WORKS_ORDERS_TO_DELETE' to the grid and set it's delete option as
> 'Where', both with and without criteria
> ('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').
> Sorry to be so long winded answering your question, but I thought it
> best to give all the details rather than to try and guess at what you
> meant.
unfortunately I'm not an Access guy and I do not feel very confortable with
Access UI... perhaps you'll have better luck posting in Office NG
hierarchy...
apologise
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||more,
I do not know the syntax you got, but
SET NOCOUNT ON
USE tempdb
CREATE TABLE dbo.tb1 (
ID int
)
CREATE TABLE dbo.tb2 (
ID int ,
data varchar(10)
)
INSERT INTO dbo.tb1 VALUES ( 1 )
INSERT INTO dbo.tb1 VALUES ( 2 )
INSERT INTO dbo.tb1 VALUES ( 3 )
INSERT INTO dbo.tb2 VALUES ( 1 , 'a' )
INSERT INTO dbo.tb2 VALUES ( 2 , 'b' )
INSERT INTO dbo.tb2 VALUES ( 3 , 'c' )
INSERT INTO dbo.tb2 VALUES ( 4 , 'd' )
SELECT a.*, b.*
FROM dbo.tb1 a JOIN dbo.tb2 b
ON a.ID = b.ID
DELETE dbo.tb2
FROM dbo.tb2 a JOIN dbo.tb1 b
ON a.ID = b.ID
GO
DROP TABLE dbo.tb1, dbo.tb2
is a valid T-SQL syntax...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Tom,
I don't know if this will help....
but here are some simple example
queries for deleting records in one
table based on another table (in an mdb):
'Based on field in another table (slow on large data)
DELETE * FROM t1 WHERE t1.F1 IN (SELECT Afield FROM t2)
'Based on field in another table (faster way)
DELETE DISTINCTROW t1.* FROM t1 LEFT JOIN t2 ON t1.PK=t2.PK
'delete all records from tbl1 which do not have a correspoinding record in
tbl2
DELETE DISTINCTROW t1.* FROM t1 LEFT JOIN t2 ON t1.PK = t2.PK WHERE t2.PK
Is Null
Throw SQL Server (MSDE) in the mix,
and I am not sure of the complications.
OTOMH, I might "copy" the Access table to MSDE
and use pass-through query(s).
If you go that route, remember in MSDE that the
DELETE construct does not use "*"
(I spent nearly a whole morning once trying to empty
a SQL Server table using "DELETE * FROM ..."!!!)
gary
"Tom" wrote

> Hi Andrea
> In the Access UI I have the two tables. The local Access table is called
> 'WORKS_ORDERS_TO_DELETE'. This has a single field, [WORKS_ORDER], which is
> a works order number. It joins to the MSDE table ('tblLOCAL_WORKS_ORDER')
> where it joins to a field of the same name, [WORKS_ORDER]. The join is set
> to select all from 'WORKS_ORDER_TO_DELETE', and only those that match on
> the join in 'tblLOCAL_WORKS_ORDER'. I selected all fields in
> 'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then set the
> query as a 'delete', so the only field in the UI grid is labelled
> 'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option is set to
> 'From'.
> I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
> database and I get the same error message, so the problem is nothing to do
> with MSDE: it seems to be when you are identifying the records to delete
> through a join. I have also tried specifying all the columns individually
> in the records to be deleted, with the same result. I have also added the
> single field in the table 'WORKS_ORDERS_TO_DELETE' to the grid and set
> it's delete option as 'Where', both with and without criteria
> ('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').
> Sorry to be so long winded answering your question, but I thought it best
> to give all the details rather than to try and guess at what you meant.
> Regards
> Tom
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:3cadgbF6mrrenU1@.individual.net...
>
|||For "Access part,"
ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000
http://support.microsoft.com/default...b;en-us;207761
"Delete Queries
*****************************************
When a delete query contains more than one table,
such as a query that deletes duplicate records from one of the tables,
the UniqueRecords property must be set to Yes for all versions of Microsoft
Access.
******************************************
However, because the default value for UniqueRecords is No in Access 2000,
you must set the value of this property manually when you create a new
delete query in Access 2000.
To do so, follow these steps:
Open the delete query in Design view.
If the property sheet is not already open, on the View menu, click
Properties.
Click an empty area in the upper half of the query window so that the
property sheet displays "Query Properties" in the title bar.
Set the UniqueRecords property to Yes.
Save the query, close it, and then run the query. "
|||You are a star, Gary.
All I had to do was to open the properties in the query and set 'unique
queries' to 'yes' and it worked!
Many thanks - I know you said in your earlier message you spent hours trying
to resolve this, but you have just saved me a few.
Regards
Tom
"Gary Walter" <garylwplease@.nospamwamego.net> wrote in message
news:eLGCHSoQFHA.1476@.TK2MSFTNGP09.phx.gbl...
> For "Access part,"
> ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000
> http://support.microsoft.com/default...b;en-us;207761
> "Delete Queries
> *****************************************
> When a delete query contains more than one table,
> such as a query that deletes duplicate records from one of the tables,
> the UniqueRecords property must be set to Yes for all versions of
> Microsoft Access.
> ******************************************
> However, because the default value for UniqueRecords is No in Access 2000,
> you must set the value of this property manually when you create a new
> delete query in Access 2000.
> To do so, follow these steps:
> Open the delete query in Design view.
> If the property sheet is not already open, on the View menu, click
> Properties.
> Click an empty area in the upper half of the query window so that the
> property sheet displays "Query Properties" in the title bar.
> Set the UniqueRecords property to Yes.
> Save the query, close it, and then run the query. "
>
|||Hi Andrea
Gary Walter's suggestion worked of changing the 'Unique Record' property in
the UI to 'yes'.
However, I would like to thank you for your replies and your time. I went on
to your website and downloaded your SQL2000 manager, and its great! It seems
more flexible than the one I paid for from somewhere else in that it allows
me to change table structures (add/remove fields etc).
Many thanks for that.
Regards
Tom
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3cc9f6F6dtrtmU1@.individual.net...
> hi Tom,
> Tom wrote:
> unfortunately I'm not an Access guy and I do not feel very confortable
> with Access UI... perhaps you'll have better luck posting in Office NG
> hierarchy...
> apologise
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||:D
found another way...
modifying the SQL statement to
DELETE DISTINCTROW SQLtable.*
FROM SQLtable a JOIN AccessTable b
ON a.ID = b.ID
did the trick, even with no primary key
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

access db using vbscript?

Is it possible to access a msde db using vbscripting?
Thanks
Andres
If you mean from an ASP page, yes. But I don't think it is possible from a
vbscript within an HTML page.
"Andres" wrote:

> Is it possible to access a msde db using vbscripting?
> Thanks
> Andres
>
|||Arnar Tór Guemundsson wrote:[vbcol=seagreen]
> If you mean from an ASP page, yes. But I don't think it is possible from a
> vbscript within an HTML page.
> "Andres" wrote:
>
How to access MSDE from ASP or plain vbscript run from command line.?
Please show both if possible.
Thanks
Andres
|||hi Andres,
Andres wrote:
> How to access MSDE from ASP or plain vbscript run from command line.?
> Please show both if possible.
>
for ASP you have to use a web server (IIS, Cassini, ..) but via vbscript you
can use the ADO object model...
for instance you can connect to the local default instance of MSDE using a
trusted connection, opening the "pubs" sample database and open a recordeset
to iterate throught the [authors] table selecting the au_id, au_lname and
au_fname columns and show the result of this concatenation in a
messagebox...
something like
<-- file.vbs -->
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdText = 1
Const adStateClosed = 0
Dim myErr
Dim oCon
Dim oRs
Dim myStr
Dim iLoop
Set oCon = CreateObject("ADODB.Connection")
oCon.ConnectionString = "Provider=sqloledb;Data Source=(Local);Initial
Catalog=pubs;Integrated Security=SSPI;"
ON ERROR RESUME NEXT
oCon.Open
myErr = Err.description
ON ERROR GOTO 0
If myErr = "" Then
Set oRs = CreateObject("ADODB.RecordSet")
ON ERROR RESUME NEXT
oRs.Open "SELECT au_id, au_lname, au_fname FROM [authors]" , oCon,
adOpenForwardOnly, adLockReadOnly, adCmdText
myErr = Err.description
ON ERROR GOTO 0
If myErr = "" Then
If oRs.State = adStateClosed Then myErr = "Error"
End If
If myErr = "" Then
For iLoop = 0 To oRs.Fields.Count - 1
myStr = myStr + oRs.Fields(iLoop).Name + " "
Next
myStr = myStr + VbCrLf
While not oRs.Eof
'myStr = ""
For iLoop = 0 To oRs.Fields.Count - 1
myStr = myStr + oRs.Fields(iLoop).Value + " "
Next
myStr = myStr + VbCrLf
oRs.MoveNext
Wend
MsgBox myStr
End If
Set oRs = Nothing
End If
Set oCon = Nothing
<-->
then it really depends on your needs..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Access cannot see MSDE when network cable disonnected

When my users are in the field and try to switch databases the
connection tool cannot see any servers even thought I know they are
there. The data link properties dialog does not see them. Also my data
entry program also cannot see them but is using sqldmo to list the
servers. However my SQL Service manager (inside my task bar) Can see
them. Any pointers on how to let the access project or the sqldmo
objects see the servers when they are disconnected. I even tried using
ODBC with no luck.
Thanks for any help.
Shane
Hello,
Personnaly, I use the following
Dim ListSvr As NameList
Set ListSvr = SQLDMO.ListAvailableSQLServers
' I add the servers to a collection after removing the ones I don't want
to show
' After that,
Set oServer = New SQLDMO.SQLServer2
Set ListSvr = oServer.ListInstalledInstances
' This function always work but only for the local instances (named or not)
' I add the needed servers to the other collection but I don't put the
servers already listed before
Marc Allard
Allcomp
Shane Lim wrote:
> When my users are in the field and try to switch databases the
> connection tool cannot see any servers even thought I know they are
> there. The data link properties dialog does not see them. Also my data
> entry program also cannot see them but is using sqldmo to list the
> servers. However my SQL Service manager (inside my task bar) Can see
> them. Any pointers on how to let the access project or the sqldmo
> objects see the servers when they are disconnected. I even tried using
> ODBC with no luck.
> Thanks for any help.
> Shane
>
|||That is PERFECT!!
On Fri, 25 Feb 2005 10:39:39 +0100, Allcomp <marc@.nospam.allcomp.be>
wrote:
[vbcol=seagreen]
>Hello,
>Personnaly, I use the following
>Dim ListSvr As NameList
>Set ListSvr = SQLDMO.ListAvailableSQLServers
>' I add the servers to a collection after removing the ones I don't want
>to show
>' After that,
>Set oServer = New SQLDMO.SQLServer2
>Set ListSvr = oServer.ListInstalledInstances
>' This function always work but only for the local instances (named or not)
>' I add the needed servers to the other collection but I don't put the
>servers already listed before
>Marc Allard
>Allcomp
>
>Shane Lim wrote:

Access and MSDE

MSDE works fine and i'm able to connect from everywhere i want to, but there is still a little problem in my f* configuration. I'm not able to update or insert any data in the database from MS Access. Using osql or Java with JDBC or any other I'm able to do so, but not in Access.
Are you able to help me and solve it ?

thanks dragunOriginally posted by dragun
MSDE works fine and i'm able to connect from everywhere i want to, but there is still a little problem in my f* configuration. I'm not able to update or insert any data in the database from MS Access. Using osql or Java with JDBC or any other I'm able to do so, but not in Access.
Are you able to help me and solve it ?

thanks dragun

In past, I had a situation like this - tables on SQL Server (MSDE) did not have primary keys or unique indexes. It sounds stupid but...|||Ok, it's stupid without any primary key, but i want to know why i can't update any table? I can just read data via access and i need a solution.

bye|||Access needs a Primary key on the linked table (the table in MSDE). Without it, it cannot issue update or insert commands. Put a Primary Key on your SQL table, re-link the tables in Access and that should do it for you.

hmscott

Access and MSDE

how to i can connect the access database and the msde server?
i try whit the "odbc databases" function of access 2000 but not work
sql state 01000
errore di sqlserver
10060 impossibile effettuare la connessione
sql state 08001
errore di sqlserver 17
sorry for my english... ;(
IgnazioC
hi Ignazio,
IgnazioC wrote:
> how to i can connect the access database and the msde server?
> i try whit the "odbc databases" function of access 2000 but not work
> sql state 01000
> errore di sqlserver
> 10060 impossibile effettuare la connessione
> sql state 08001
> errore di sqlserver 17
10060 is a timeout problem. Make sure that you don't have firewall issues
between the client and server. Even personal firewalls on the Server will
block this traffic by default.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||before it worked,but to the unexpected one it did not work more.
I have changed the name of the pc and all now it works.
why happens this? my name of the pc are "locked" on msde sever? as I
can unlock it?
|||hi Ignazio,
IgnazioC wrote:
> before it worked,but to the unexpected one it did not work more.
> I have changed the name of the pc and all now it works.
> why happens this? my name of the pc are "locked" on msde sever? as I
> can unlock it?
as regard MSDE by it self, there's no problem at all... this was a problem
with SQL Server 7.0/MSDE 1.0 but it is no longer the case...
you can have now problems regarding ie: the registration on Enterprise
Manager, and, re-registering servers on Enterprise Manager UI will solve EM
problems..
or, of course, net problems due to not resolved computer names for firewall
as well...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

Access 2K to MSDE

With Acces 2000.
When i go to migrate a BD in Access 2000 to MSDE.
Acces 2000 gives me an overflow error.
I suppose it is an overfolw error ( in spanish DESBORDAMIENTO)
Is it possible to migrate from acces 2000 to MSDE ?
See How to Convert an Access Database to SQL Server
http://support.microsoft.com/default...EN-US;Q237980. This
paper has links to other papers that detail the errors and issues
invoving using the upsizing wizard.
--Mary
On Thu, 7 Oct 2004 09:24:56 +0200, "Miguel Genol" <mgenol@.teleline.es>
wrote:

>With Acces 2000.
>When i go to migrate a BD in Access 2000 to MSDE.
>Acces 2000 gives me an overflow error.
>I suppose it is an overfolw error ( in spanish DESBORDAMIENTO)
>Is it possible to migrate from acces 2000 to MSDE ?
>

Access 200 and MSDE 2000

Are there any known issues with installing MSDE 2000 on a computer that
already has Access 2000 installed?
No. Many people have been running Access and SQL Server on the same
computer for years. As long as you meet the minimum hardware and
memory requirements you should be fine.
--Mary
On Wed, 30 Jun 2004 13:12:40 -0500, "Mark Castelli"
<mcastelli98@.yahoo.com> wrote:

>Are there any known issues with installing MSDE 2000 on a computer that
>already has Access 2000 installed?
>
|||Many thanks
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:h6n8e01j5aopa7q2qkh6hlp5ih86l6bsus@.4ax.com...
> No. Many people have been running Access and SQL Server on the same
> computer for years. As long as you meet the minimum hardware and
> memory requirements you should be fine.
> --Mary
> On Wed, 30 Jun 2004 13:12:40 -0500, "Mark Castelli"
> <mcastelli98@.yahoo.com> wrote:
>

about using osql BACKUP/RESTORE commands

Hi!
I'm using MSDE 2000 SP4 on local computer.
I backed up database into local hard drive using osql command like...
OSQL -E -Q "BACKUP DATABASE MYDB TO DISK = 'C:\MYDB.bak'" >
LogBackupDatabase.log
...and it's working fine. But when I try to save backup file directly
into network drive, it's not working. "LogBackupDatabase.log"-file
contains error message...
"Msg 3201, Level 16, State 1, Server MYLOCALCOMPUTER, Line 1
Cannot open backup device 'Z:\MyFolder\MYDB.bak'. Device error or device
off-line. See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Server MYLOCALCOMPUTER, Line 1
BACKUP DATABASE is terminating abnormally."
Z: is mapped network drive.
Is it possible to save backup file into network drive somehow? Maybe my
osql is not correctly formed to save into backup file into network
drive, or am I missing something?
Also restore command using osql is not working if I try to restore
backup file from network drive.
Thanks in advance!
Mika
Hi,
Use UNC (REMOTESERVER\SHARE\PATH\) path along with Backup or Restore
commands.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharenameX\dbname.bak' with
init
Perform the same for Restore Database
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
Thanks
Hari
SQL Server MVP
"Mika M" <mahmik_removethis@.luukku.com> wrote in message
news:OASY$mStFHA.3452@.TK2MSFTNGP14.phx.gbl...
> Hi!
> I'm using MSDE 2000 SP4 on local computer.
> I backed up database into local hard drive using osql command like...
> OSQL -E -Q "BACKUP DATABASE MYDB TO DISK = 'C:\MYDB.bak'" >
> LogBackupDatabase.log
> ...and it's working fine. But when I try to save backup file directly into
> network drive, it's not working. "LogBackupDatabase.log"-file contains
> error message...
> "Msg 3201, Level 16, State 1, Server MYLOCALCOMPUTER, Line 1
> Cannot open backup device 'Z:\MyFolder\MYDB.bak'. Device error or device
> off-line. See the SQL Server error log for more details.
> Msg 3013, Level 16, State 1, Server MYLOCALCOMPUTER, Line 1
> BACKUP DATABASE is terminating abnormally."
> Z: is mapped network drive.
> Is it possible to save backup file into network drive somehow? Maybe my
> osql is not correctly formed to save into backup file into network drive,
> or am I missing something?
> Also restore command using osql is not working if I try to restore backup
> file from network drive.
> --
> Thanks in advance!
> Mika
|||hi,
Mika M wrote:
in addition to Hari valuable hint, please have a look at
http://support.microsoft.com/?id=283811 in order to modify the account SQL
Server and SQL Server Agent are running on...
unfortunately this article is no longer alligned with modifications applied
with sp4.. and you will no longer be able to start and run SQL Server and
the Agent only using the reported info..
I'm still trying troubleshooting it..
try "propagating" file permissions to all sub folders as described, as long
as assigning registry permissions as
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\Microsoft\MSSQLServer\MSSQLServer (FULL CONTROL)
for the account running SQL Server and
HKLM\Software\Microsoft\MSSQLServer\SQLSERVERAGENT (FULL CONTROL)
HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSo cketNetLib\LastConnect
(FULL CONTROL)
HKLM\Software\Description\Microsoft\Rpc\UuidTempor aryData (FULL CONTROL)
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\ODBC\ODBC.INI (FULL CONTROL)
for the account running the Agent...
make those accounts member of the local sysadmins WinNT role
but i definitevely hope kb article 283811 get updated..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

About the new MSDE

I am in the process of downgrading an MSDE database to a Jet (Access)
database for the following reason:
1. Size: Deployment of an MSDE database engine is inflating my distribution
file (Setup.exe) over 20 Megs! With an Access database this number is ZERO
because all the infrastructure to access my Access database is already
included on the .Net framework.
2. Setup: MSDE is complex to setup, you pretty much have to use an installer
to accomplish this where with an Access database you just copy the mdb file
and you are done.
3. Maintainability: With MSDE I will have to issue independent patches for
fixes to the MSDE engine where with an Access database, fixes are included
in the .Net framework service pack.
4. Portability: With a .Net application and an Access database, I could copy
my program and the database into a USB memory stick or a writable CD and
actually run my application from there, this is a big deal for me as people
can carry my application on their USB keychain and use it any time in any
computer. This is not possible with MSDE.
Number 1 & 4 are biggies for me. The think that really bothers me is that my
application already runs online against an SQL server database so using an
MSDE to have the database running locally makes it EXTREMELY simple for me
because I can use the exact same SQL server database directly were with
access I will have create a new database, rewrite my stored procedure logic
and maintain 2 database (online SQL server and local Access database).
If the new version of MSDE is basically the same thing as the old MSDE
except that it has more features then I guess I am doomed and I will have to
use an Access database.
Any comment regarding my predicament are very much appreciated. Thanks.
hi Rene,
Rene wrote:
> I am in the process of downgrading an MSDE database to a Jet (Access)
> database for the following reason:
> 1. Size: Deployment of an MSDE database engine is inflating my
> distribution file (Setup.exe) over 20 Megs! With an Access database
> this number is ZERO because all the infrastructure to access my
> Access database is already included on the .Net framework.
actually 43mb :D
http://www.microsoft.com/downloads/d...displaylang=en

> 2. Setup: MSDE is complex to setup, you pretty much have to use an
> installer to accomplish this where with an Access database you just
> copy the mdb file and you are done.
right, but the installer can really be a little companion tool that provides
an UI to the setup.exe boostrap installer to gather all required parameters
and user options, then shells to setup.exe... but ok, it's more complex than
mdb file copy..

> 3. Maintainability: With MSDE I will have to issue independent
> patches for fixes to the MSDE engine where with an Access database,
> fixes are included in the .Net framework service pack.
AFAIK .Net FWK does not contain MDAC... and MDAC itself, up to v. 2.6 no
longer provides the JET components, so you manually have to install/upgrade
it.. ok, actual Win OS releases already have it as part of core
installation, but you have to manually upgrade JET "things"..

> 4. Portability: With a .Net application and an Access database, I
> could copy my program and the database into a USB memory stick or a
> writable CD and actually run my application from there, this is a big
> deal for me as people can carry my application on their USB keychain
> and use it any time in any computer. This is not possible with MSDE.
you can do sort of this with MSDE too... your application must attach the
dbs residing on the USB key at application start up, and obviously detach
them before key removal (or the dbs will be marked as suspect).. this is not
the very best solution for SQL Server database, both for performance and
security reasons, but it can be done... and, of course, even if this is an
MSDE default setting, set the autoclose db option :D
just a side note... I recently bought an USB key and read something about
100.000 writes warranty...
well, I really do not know how long does it takes for SQL Server/MSDE (but
even JET) to exceed this value, but I'd expect not that much time... so
stay tuned :D

> Number 1 & 4 are biggies for me. The think that really bothers me is
> that my application already runs online against an SQL server
> database so using an MSDE to have the database running locally makes
> it EXTREMELY simple for me because I can use the exact same SQL
> server database directly were with access I will have create a new
> database, rewrite my stored procedure logic and maintain 2 database
> (online SQL server and local Access database).
> If the new version of MSDE is basically the same thing as the old MSDE
> except that it has more features then I guess I am doomed and I will
> have to use an Access database.
SQLExpress will support (single user) User Instances,
http://tinyurl.com/bm3to , that combined with .Net XCopy deployment can
perhaps get your point...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea:

> AFAIK .Net FWK does not contain MDAC... and MDAC itself, up to v. 2.6 no
> longer provides the JET components, so you manually have to
> install/upgrade it.. ok, actual Win OS releases already have it as part of
> core installation, but you have to manually upgrade JET "things"..
I know for sure of two things about this Access db trip. The first one is
that I am using ADO.Net as my data object layer and the second one, that I
am using the Microsoft.Jet.OLEDB.4.0 provider and as a result the Jet
engine.
I did some research on the web and found out that it looks like the
"smarties" form Microsoft are retiring the Jet engine and we now can all
look forward to having to distribute a 70 Megabytes SQL express to go with
our little 2 meg applications.
It also looks like the Jet engine is indeed distributed with the OS (after
Win 2000), I am wondering if this will stop happening with new OS versions.
For earlier version of Windows I guess I will have to distribute the MDAC
2.6 and pray that that comes with Jet 4.0
Frankly, I think this is absolutely retarded, Access/Jet were perfect for
your average database application and it should be fully supported and
distribute on the .Net. This is REALLY stupid.
|||> Frankly, I think this is absolutely retarded, Access/Jet were perfect for
> your average database application and it should be fully supported and
> distribute on the .Net. This is REALLY stupid.
Luckily it seems both Access and Jet will survive.
Check this:
http://blogs.msdn.com/access/
Jesper
|||I don't know where you did your research on the web, but it is
absolutely inaccurate. Access/Jet will continue to be supported for
exactly the scenario you describe, and is being enhanced for Office
12, which is now in limited technical beta.
Bear in mind that Access/Jet is not recommended for Web apps or even
heavy-duty intranet apps, and you should be fine.
--Mary
On Fri, 18 Nov 2005 15:19:43 -0600, "Rene" <nospam@.nospam.com> wrote:

>Hi Andrea:
>
>I know for sure of two things about this Access db trip. The first one is
>that I am using ADO.Net as my data object layer and the second one, that I
>am using the Microsoft.Jet.OLEDB.4.0 provider and as a result the Jet
>engine.
>I did some research on the web and found out that it looks like the
>"smarties" form Microsoft are retiring the Jet engine and we now can all
>look forward to having to distribute a 70 Megabytes SQL express to go with
>our little 2 meg applications.
>It also looks like the Jet engine is indeed distributed with the OS (after
>Win 2000), I am wondering if this will stop happening with new OS versions.
>For earlier version of Windows I guess I will have to distribute the MDAC
>2.6 and pray that that comes with Jet 4.0
>Frankly, I think this is absolutely retarded, Access/Jet were perfect for
>your average database application and it should be fully supported and
>distribute on the .Net. This is REALLY stupid.
>

about text ,ntext,image error message

hello all:
my sql server is msde 2000,no sp4 & win xp
when inster data there are some error like this:
location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
or when delete date :error like this:
location(1:3568).slot 0 for text,ntext,or image node does not exist.
or location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
I don't know why and how this happend .who can help me ,thx.
Hi
"lxg" wrote:

> hello all:
> my sql server is msde 2000,no sp4 & win xp
> when inster data there are some error like this:
> location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
> or when delete date :error like this:
> location(1:3568).slot 0 for text,ntext,or image node does not exist.
> or location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
> I don't know why and how this happend .who can help me ,thx.
>
The error you are getting are from a MFC class, have your run a DBCC CHECKDB
to check the database is ok?
John

about text ,ntext,image error message

hello all:
my sql server is msde 2000,no sp4 & win xp
when inster data there are some error like this:
location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
or when delete date :error like this:
location(1:3568).slot 0 for text,ntext,or image node does not exist.
or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
I don't know why and how this happend .who can help me ,thx.Hi
"lxg" wrote:
> hello all:
> my sql server is msde 2000,no sp4 & win xp
> when inster data there are some error like this:
> location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> or when delete date :error like this:
> location(1:3568).slot 0 for text,ntext,or image node does not exist.
> or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> I don't know why and how this happend .who can help me ,thx.
>
The error you are getting are from a MFC class, have your run a DBCC CHECKDB
to check the database is ok?
John

about text ,ntext,image error message

hello all:
my sql server is msde 2000,no sp4 & win xp
when inster data there are some error like this:
location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
or when delete date :error like this:
location(1:3568).slot 0 for text,ntext,or image node does not exist.
or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
I don't know why and how this happend .who can help me ,thx.Hi
"lxg" wrote:

> hello all:
> my sql server is msde 2000,no sp4 & win xp
> when inster data there are some error like this:
> location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> or when delete date :error like this:
> location(1:3568).slot 0 for text,ntext,or image node does not exist.
> or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> I don't know why and how this happend .who can help me ,thx.
>
The error you are getting are from a MFC class, have your run a DBCC CHECKDB
to check the database is ok?
John

about MSDE

Hi,
We need to have a clarification and a full technical explanation about the
folowing issue:
A Year ago Microsoft had announced and released MSDE, Microsoft desktop
engine,
where we can download it from the internet at the Microsoft website, (it
comes also free with Microsoft office 2003), which is the engine of SQL
server database without the enterprise manager service, so what is the
difference if we use MSDE or the complete SQL server package from a client
view and application working propery view, and what is the Most suitable
solution
and why?
Thanks,
Tarek
hi Tarek,
"Tarek" <analystprog@.hotmail.com> ha scritto nel messaggio
news:Oooo6%23kEEHA.3408@.tk2msftngp13.phx.gbl...
> Hi,
> We need to have a clarification and a full technical explanation about the
> folowing issue:
> A Year ago Microsoft had announced and released MSDE, Microsoft desktop
> engine,
> where we can download it from the internet at the Microsoft website, (it
> comes also free with Microsoft office 2003), which is the engine of SQL
> server database without the enterprise manager service, so what is the
> difference if we use MSDE or the complete SQL server package from a client
> view and application working propery view, and what is the Most suitable
> solution
> and why?
you can download the free version MSDE release A from
http://www.microsoft.com/sql/msde/do...s/download.asp and/or the original
version of MSDE from http://www.microsoft.com/sql/downloads/2000/sp3.asp.
they share the same code base and are identical from a technical view
point...
but they are different from a License point of view...
the Release A is free to use for everyone but requires to freely register
for distribution to other end users
(http://www.microsoft.com/sql/msde/ho...stregister.asp ), while the
latter is free to use and redistribute for all licensed owner of products
such as MSDN universal, VS.Net, Fox Pro, SQL Server and so on...
the main differences between MSDE and the full blown SQL Server are listed
in http://www.microsoft.com/sql/msde/pr...o/features.asp and a
comparative sheet of all SQL server edition can be found at
http://www.microsoft.com/sql/evaluat...ew/default.asp
IMHO the main limitations are the 2gb database size and the workload query
governor, which is better described at
http://www.microsoft.com/sql/evaluat...ew/default.asp
it is a powerfull solution for limited (number of clients) requirements and,
in the very small business, has the advantage to be free for the end users..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.6.0 - DbaMgr ver 0.52.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

About MDSE

What are the differences between MSDE and SQL Server 2000 ?
is MSDE Free Licence ?
Thank You All..
hi,
BonGee wrote:
> What are the differences between MSDE and SQL Server 2000 ?
> is MSDE Free Licence ?
>
MSDE is now free to use and download in the form of "MSDE Web Release A"
(http://www.microsoft.com/sql/msde/do...s/download.asp), as you can see
at http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp, but you have to
register (http://www.microsoft.com/sql/msde/ho...stregister.asp) for
distribution rights..
it is a scaled down edition of SQL Server, including a built-in Workloads
Governor
(http://msdn.microsoft.com/library/?u...c/8_ar_sa2_0ci
q.asp?frame=true) that limits somehow it's functionality as MSDE has been
devoted for little workgroups... futher limitations are listed at
http://www.microsoft.com/sql/msde/pr...o/features.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply