RunSQL method with tables linked to SQL2005 Express

In ACCESS 2003 :
1. The DoCmd.RunSQL method works fine with tables linked (ODBC) to SQL 2000
on a first machine. On this machine, SQL2005 is not installed.
2. On a second machine, with SQL2000 and SQL2005Express installed,
DoCmd.RunSQL fails with tables linked to SQL2000 or SQL2005Express. In both
cases, I get error '3024' 'File C:\Documents and Settings\ ... \dbo.mdb not
found'.
I checked SQL2005 configuration with the SQL2005 configuration tools, and it
seems ok.
Apart from the RunSQL method, the linked tables can be accessed normally.
Any idea ? Thanks in advance.
Hi,
My understanding of your issue is that:
Your Acess application failed with tables linked to SQL Server 2000 or SQL
2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
\dbo.mdb not
found'.
If I have misunderstood, please let me know.
I performed a test but unfortunately I didn't reproduce your issue. My test
was based on the following steps:
1. Create an Access database in Acess 2003
2. Create a SQL Server 2000 database to the File DSN
4. Add linked tables from the ODBC File DSN
5. Create a Form in Acess 2003
6. Add code in the click event:
Dim strSQL As String
strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "'
where ProductID=" & Me.comProductId
DoCmd.RunSQL strSQL
MsgBox "OK"
7. Save the database
Firstly, I opened the database file and triggered the click event of the
Form on my local computer with SQL Server 2000 and SQL Server 2005 without
any problem, then I copied the .mdb file to another machine with SQL Server
2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
From your error message, it seems that your dbo.mdb file cannot be found.
Please check that.
Also, could you please tell me more detailed on your scenario? It is better
that you could describe your whole process so that I could better
understand your issue and probably reproduce your issue according to your
steps.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Charles,
Thanks for your testing and fast reply.
Your scenario is quite similar to mine and I can't see why results are
different.
In fact, it looks like if access to the tables concerned is denied. So I am
suspecting SQL 2005 security. Before going further in tests, I will have a
closer look at SQL 2005 schemas, which I am new in as they did not exist in
SQL 2000.
When I have completed this, I will let you know more.
Thanks to you.
"Charles Wang[MSFT]" wrote:

> Hi,
> My understanding of your issue is that:
> Your Acess application failed with tables linked to SQL Server 2000 or SQL
> 2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
> \dbo.mdb not
> found'.
> If I have misunderstood, please let me know.
> I performed a test but unfortunately I didn't reproduce your issue. My test
> was based on the following steps:
> 1. Create an Access database in Acess 2003
> 2. Create a SQL Server 2000 database to the File DSN
> 4. Add linked tables from the ODBC File DSN
> 5. Create a Form in Acess 2003
> 6. Add code in the click event:
> Dim strSQL As String
> strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "'
> where ProductID=" & Me.comProductId
> DoCmd.RunSQL strSQL
> MsgBox "OK"
> 7. Save the database
> Firstly, I opened the database file and triggered the click event of the
> Form on my local computer with SQL Server 2000 and SQL Server 2005 without
> any problem, then I copied the .mdb file to another machine with SQL Server
> 2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
> From your error message, it seems that your dbo.mdb file cannot be found.
> Please check that.
> Also, could you please tell me more detailed on your scenario? It is better
> that you could describe your whole process so that I could better
> understand your issue and probably reproduce your issue according to your
> steps.
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
|||Charles,
Finally, I got it. The issue is this :
1. When, in ACCESS, you create a link to a SQL table which hasn't got a
primary key, a dialog box appears in second, asking for a field unique
identifier. If you do not select a field, the link is however created and you
can access normally to the table from ACCESS. But the Docmd.RunSql method
does not work. What is disturbing in that case is that the table link is
created however.
2. On another hand, if you select a unique identifier while the source table
hasn't got any primary key, it leads to incoherent behaviour such as tables
appearing different in ACCESS from their real content in SQL.
3. My conclusion is : If you have source tables without primary key (which
of course can happen), you have to choose between A/ Not using Docmd.RunSql
(or CurrentDb.Execute) or B/ Having dangerous behaviour in your app.
I would say it sounds more or less buggy.
I think you will understand my pain when I say it is a real waste that
Microsoft is abandonning ADP platform. With ADPs you can directly manipulate
SQL objects in ACCESS, such as tables, views, procs, ... (and what is more:
most often without writing any code). And this trouble could not happen.
I would be very glad to know your feeling about all this.
Once more, many thanks for your help.
"Charles Wang[MSFT]" wrote:

> Hi,
> My understanding of your issue is that:
> Your Acess application failed with tables linked to SQL Server 2000 or SQL
> 2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
> \dbo.mdb not
> found'.
> If I have misunderstood, please let me know.
> I performed a test but unfortunately I didn't reproduce your issue. My test
> was based on the following steps:
> 1. Create an Access database in Acess 2003
> 2. Create a SQL Server 2000 database to the File DSN
> 4. Add linked tables from the ODBC File DSN
> 5. Create a Form in Acess 2003
> 6. Add code in the click event:
> Dim strSQL As String
> strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "'
> where ProductID=" & Me.comProductId
> DoCmd.RunSQL strSQL
> MsgBox "OK"
> 7. Save the database
> Firstly, I opened the database file and triggered the click event of the
> Form on my local computer with SQL Server 2000 and SQL Server 2005 without
> any problem, then I copied the .mdb file to another machine with SQL Server
> 2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
> From your error message, it seems that your dbo.mdb file cannot be found.
> Please check that.
> Also, could you please tell me more detailed on your scenario? It is better
> that you could describe your whole process so that I could better
> understand your issue and probably reproduce your issue according to your
> steps.
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
|||Hi Jason,
Thanks for your detailed explanation on this issue which make me totally
understand your issue.
Yes, I reproduced your issue according to your steps. It is indeed an by
design issue.
Please refer to:
276035PRB: ADOX Creates Read Only Linked Tables to Access Databases
http://support.microsoft.com/default...b;EN-US;276035
If you are very concerned on this issue, I recommend that you give
Microsoft feedback which will be routed to the related team via the link:
http://www.microsoft.com/office/comm...pe=suggestion&
lang=en&cr=US&cat=en-us-office&pt=3a4e9862-cdce-4bdc-8664-91038e3eb1e9
Also, ADP is still available in Access 2007
http://technet2.microsoft.com/Office...1c-446a-8ff2-2
21769a58ba51033.mspx?mfr=true
For now, you may assign the primary key in your Access database file when
you add the linked table due to no better alternative resolutions on this
issue.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Charles,
Thanks for your reply. Just one comment about your suggestion of assigning a
field unique identifier to a link, while source table hasn't got a primary
key.
1. Let us consider a SQL table with no primary key :
empl_Name empl_FirstName
-- --
BROWN Bob
COPPERFIELD Jim
TAYLOR Evans
BROWN Mary
2. In ACCESS (.mdb), create an odbc link to this table, selecting empl_Name
as unique identifier. The link is created without any warning.
3. Then, you can have a look at your table in ACCESS. You will see :
empl_Name empl_FirstName
-- --
BROWN Bob
COPPERFIELD Jim
TAYLOR Evans
BROWN Bob
A bit confusing ...
|||Hi Jason,
Thanks for your detailed response.
In this case, you can choose the two columns empl_Name and empl_FirstName
as the unique record identifier.
Please feel free to let me know if you have any other questions or
concerns.
Have a great day!
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support
|||Charles,
Thanks very much for your interest in my issue.
My context is the following :
1. I have been developing for long with ADPs, doing the "hard job" in SQL
Server, and using ACCESS as front end. I considered this way of doing as
highly productive. My field could be described as the construction of
analytical relational databases fed with data coming from transactional
systems (finance, supply chain, ... ).
2. Unfortunately, Microsoft is leaving ADP platform. As a matter of fact,
you will see ADPs in ACCESS 2007, but just for the purpose of compatibility.
From now, ADPs don't work with SQL 2005 as far as building purpose is
concerned.
3. So, I am going back to ODBC and ADO with ACCESS .mdb. And problems come.
The one I met with DoCmd.RunSql (or CurrentDb.execute) is one amongst many.
For instance, links refreshing is quite an issue. If you have to frequently
create/kill tables in SQL Server, links management gets complicated.
Thanks for your suggestion, about my example, of selecting two fields as
unique identifier. I confess I didn't think of it. But the problem is
elsewhere : when you receive, from transactional systems, data in tables with
no primary key (and this frequently happens), it would not be reasonable to
add a unique identifier when creating odbc link, as you may never be sure
about the existence of duplicates.
With ADPs, everything is more simple : you work with the database "as it
is", without interfering with risky adds. Roughly speaking, "what you see is
what you get". And of course the direct manipulation of SQL objects in
ACCESS, with no code writing, allows fast developments.
4. As a conclusion, I would say it is possible to find a solution, more or
less satisfying, to this RunSQL problem (In fact, the definite solution will
be to avoid this method). But the real fact is that there is galaxy between
ADPs and ODBC/ADO (in ACCESS world of course) ...
What is rather strange is that Microsoft has, for years, presented ADPs as
THE solution for client/server ACCESS apps.
Once again, thanks very much for your help and interest in these questions.
"Charles Wang[MSFT]" wrote:

> Hi,
> I am interested in this issue. Would you mind letting me know the result of
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
> Have a great day!
> Charles Wang
> Microsoft Online Community Support
>
|||Hi Jason,
Appreciate your detailed response. I do understand your concerns. Just one
point that you mentioned I cannot understand clearly.
You said, "From now, ADPs don't work with SQL 2005 as far as building
purpose is concerned.". Did you mean that ADPs cannot work with SQL 2005
from now on? As far as I know, both Access 2003 and Access 2007 can work
well with SQL 2005 Express, Standard and Enterprise edtions.
Did you encounter some issue when you use ADP to work with SQL 2005? If so,
please post the issue here and I can assist you on it.
I performed a test with ADP and managed to update a SQL Server 2005 Express
table with primary key. Also, I managed to execute DoCmd.RunSQL on a no
primary key table in ADP:
I created a table in SQL 2005 Express:
CREATE TABLE [dbo].[Employee](
[First_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Last_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
INSERT INTO Employee VALUES('F1','L1','F1 L1')
INSERT INTO Employee VALUES('F2','L2','F2 L2')
INSERT INTO Employee VALUES('F1','L1','F1 L1')
Then in ADP, I managed to run the query:
DoCmd.RunSQL "Update Employee SET Description='FX LX' WHERE First_Name =
'F1'"
For a table with no primary key, I think it is a by design limitation in
Access database (.mdb) and I have submited a feedback for this.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

RunSQL method with tables linked to SQL2005 Express

In ACCESS 2003 :
1. The DoCmd.RunSQL method works fine with tables linked (ODBC) to SQL 2000
on a first machine. On this machine, SQL2005 is not installed.
2. On a second machine, with SQL2000 and SQL2005Express installed,
DoCmd.RunSQL fails with tables linked to SQL2000 or SQL2005Express. In both
cases, I get error '3024' 'File C:\Documents and Settings\ ... \dbo.mdb not
found'.
I checked SQL2005 configuration with the SQL2005 configuration tools, and it
seems ok.
Apart from the RunSQL method, the linked tables can be accessed normally.
Any idea ? Thanks in advance.Hi,
My understanding of your issue is that:
Your Acess application failed with tables linked to SQL Server 2000 or SQL
2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
\dbo.mdb not
found'.
If I have misunderstood, please let me know.
I performed a test but unfortunately I didn't reproduce your issue. My test
was based on the following steps:
1. Create an Access database in Acess 2003
2. Create a SQL Server 2000 database to the File DSN
4. Add linked tables from the ODBC File DSN
5. Create a Form in Acess 2003
6. Add code in the click event:
Dim strSQL As String
strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "'
where ProductID=" & Me.comProductId
DoCmd.RunSQL strSQL
MsgBox "OK"
7. Save the database
Firstly, I opened the database file and triggered the click event of the
Form on my local computer with SQL Server 2000 and SQL Server 2005 without
any problem, then I copied the .mdb file to another machine with SQL Server
2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
From your error message, it seems that your dbo.mdb file cannot be found.
Please check that.
Also, could you please tell me more detailed on your scenario? It is better
that you could describe your whole process so that I could better
understand your issue and probably reproduce your issue according to your
steps.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles,
Thanks for your testing and fast reply.
Your scenario is quite similar to mine and I can't see why results are
different.
In fact, it looks like if access to the tables concerned is denied. So I am
suspecting SQL 2005 security. Before going further in tests, I will have a
closer look at SQL 2005 schemas, which I am new in as they did not exist in
SQL 2000.
When I have completed this, I will let you know more.
Thanks to you.
"Charles Wang[MSFT]" wrote:

> Hi,
> My understanding of your issue is that:
> Your Acess application failed with tables linked to SQL Server 2000 or SQL
> 2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
> \dbo.mdb not
> found'.
> If I have misunderstood, please let me know.
> I performed a test but unfortunately I didn't reproduce your issue. My tes
t
> was based on the following steps:
> 1. Create an Access database in Acess 2003
> 2. Create a SQL Server 2000 database to the File DSN
> 4. Add linked tables from the ODBC File DSN
> 5. Create a Form in Acess 2003
> 6. Add code in the click event:
> Dim strSQL As String
> strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "
'
> where ProductID=" & Me.comProductId
> DoCmd.RunSQL strSQL
> MsgBox "OK"
> 7. Save the database
> Firstly, I opened the database file and triggered the click event of the
> Form on my local computer with SQL Server 2000 and SQL Server 2005 without
> any problem, then I copied the .mdb file to another machine with SQL Serve
r
> 2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
> From your error message, it seems that your dbo.mdb file cannot be found.
> Please check that.
> Also, could you please tell me more detailed on your scenario? It is bette
r
> that you could describe your whole process so that I could better
> understand your issue and probably reproduce your issue according to your
> steps.
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>|||Charles,
Finally, I got it. The issue is this :
1. When, in ACCESS, you create a link to a SQL table which hasn't got a
primary key, a dialog box appears in second, asking for a field unique
identifier. If you do not select a field, the link is however created and yo
u
can access normally to the table from ACCESS. But the Docmd.RunSql method
does not work. What is disturbing in that case is that the table link is
created however.
2. On another hand, if you select a unique identifier while the source table
hasn't got any primary key, it leads to incoherent behaviour such as tables
appearing different in ACCESS from their real content in SQL.
3. My conclusion is : If you have source tables without primary key (which
of course can happen), you have to choose between A/ Not using Docmd.RunSql
(or CurrentDb.Execute) or B/ Having dangerous behaviour in your app.
I would say it sounds more or less buggy.
I think you will understand my pain when I say it is a real waste that
Microsoft is abandonning ADP platform. With ADPs you can directly manipulate
SQL objects in ACCESS, such as tables, views, procs, ... (and what is more:
most often without writing any code). And this trouble could not happen.
I would be very glad to know your feeling about all this.
Once more, many thanks for your help.
"Charles Wang[MSFT]" wrote:

> Hi,
> My understanding of your issue is that:
> Your Acess application failed with tables linked to SQL Server 2000 or SQL
> 2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
> \dbo.mdb not
> found'.
> If I have misunderstood, please let me know.
> I performed a test but unfortunately I didn't reproduce your issue. My tes
t
> was based on the following steps:
> 1. Create an Access database in Acess 2003
> 2. Create a SQL Server 2000 database to the File DSN
> 4. Add linked tables from the ODBC File DSN
> 5. Create a Form in Acess 2003
> 6. Add code in the click event:
> Dim strSQL As String
> strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "
'
> where ProductID=" & Me.comProductId
> DoCmd.RunSQL strSQL
> MsgBox "OK"
> 7. Save the database
> Firstly, I opened the database file and triggered the click event of the
> Form on my local computer with SQL Server 2000 and SQL Server 2005 without
> any problem, then I copied the .mdb file to another machine with SQL Serve
r
> 2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
> From your error message, it seems that your dbo.mdb file cannot be found.
> Please check that.
> Also, could you please tell me more detailed on your scenario? It is bette
r
> that you could describe your whole process so that I could better
> understand your issue and probably reproduce your issue according to your
> steps.
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>|||Hi Jason,
Thanks for your detailed explanation on this issue which make me totally
understand your issue.
Yes, I reproduced your issue according to your steps. It is indeed an by
design issue.
Please refer to:
276035 PRB: ADOX Creates Read Only Linked Tables to Access Databases
http://support.microsoft.com/defaul...kb;EN-US;276035
If you are very concerned on this issue, I recommend that you give
Microsoft feedback which will be routed to the related team via the link:
http://www.microsoft.com/office/com...ype=suggestion&
lang=en&cr=US&cat=en-us-office&pt=3a4e9862-cdce-4bdc-8664-91038e3eb1e9
Also, ADP is still available in Access 2007
http://technet2.microsoft.com/Offic...a1c-446a-8ff2-2
21769a58ba51033.mspx?mfr=true
For now, you may assign the primary key in your Access database file when
you add the linked table due to no better alternative resolutions on this
issue.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles,
Thanks for your reply. Just one comment about your suggestion of assigning a
field unique identifier to a link, while source table hasn't got a primary
key.
1. Let us consider a SQL table with no primary key :
empl_Name empl_FirstName
-- --
BROWN Bob
COPPERFIELD Jim
TAYLOR Evans
BROWN Mary
2. In ACCESS (.mdb), create an odbc link to this table, selecting empl_Name
as unique identifier. The link is created without any warning.
3. Then, you can have a look at your table in ACCESS. You will see :
empl_Name empl_FirstName
-- --
BROWN Bob
COPPERFIELD Jim
TAYLOR Evans
BROWN Bob
A bit confusing ...|||Hi Jason,
Thanks for your detailed response.
In this case, you can choose the two columns empl_Name and empl_FirstName
as the unique record identifier.
Please feel free to let me know if you have any other questions or
concerns.
Have a great day!
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support|||Charles,
Thanks very much for your interest in my issue.
My context is the following :
1. I have been developing for long with ADPs, doing the "hard job" in SQL
Server, and using ACCESS as front end. I considered this way of doing as
highly productive. My field could be described as the construction of
analytical relational databases fed with data coming from transactional
systems (finance, supply chain, ... ).
2. Unfortunately, Microsoft is leaving ADP platform. As a matter of fact,
you will see ADPs in ACCESS 2007, but just for the purpose of compatibility.
From now, ADPs don't work with SQL 2005 as far as building purpose is
concerned.
3. So, I am going back to ODBC and ADO with ACCESS .mdb. And problems come.
The one I met with DoCmd.RunSql (or CurrentDb.execute) is one amongst many.
For instance, links refreshing is quite an issue. If you have to frequently
create/kill tables in SQL Server, links management gets complicated.
Thanks for your suggestion, about my example, of selecting two fields as
unique identifier. I confess I didn't think of it. But the problem is
elsewhere : when you receive, from transactional systems, data in tables wit
h
no primary key (and this frequently happens), it would not be reasonable to
add a unique identifier when creating odbc link, as you may never be sure
about the existence of duplicates.
With ADPs, everything is more simple : you work with the database "as it
is", without interfering with risky adds. Roughly speaking, "what you see is
what you get". And of course the direct manipulation of SQL objects in
ACCESS, with no code writing, allows fast developments.
4. As a conclusion, I would say it is possible to find a solution, more or
less satisfying, to this RunSQL problem (In fact, the definite solution will
be to avoid this method). But the real fact is that there is galaxy between
ADPs and ODBC/ADO (in ACCESS world of course) ...
What is rather strange is that Microsoft has, for years, presented ADPs as
THE solution for client/server ACCESS apps.
Once again, thanks very much for your help and interest in these questions.
"Charles Wang[MSFT]" wrote:

> Hi,
> I am interested in this issue. Would you mind letting me know the result o
f
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
> Have a great day!
> Charles Wang
> Microsoft Online Community Support
>|||Hi Jason,
Appreciate your detailed response. I do understand your concerns. Just one
point that you mentioned I cannot understand clearly.
You said, "From now, ADPs don't work with SQL 2005 as far as building
purpose is concerned.". Did you mean that ADPs cannot work with SQL 2005
from now on? As far as I know, both Access 2003 and Access 2007 can work
well with SQL 2005 Express, Standard and Enterprise edtions.
Did you encounter some issue when you use ADP to work with SQL 2005? If so,
please post the issue here and I can assist you on it.
I performed a test with ADP and managed to update a SQL Server 2005 Express
table with primary key. Also, I managed to execute DoCmd.RunSQL on a no
primary key table in ADP:
I created a table in SQL 2005 Express:
CREATE TABLE [dbo].[Employee](
[First_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[Last_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL,
[Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L
)
INSERT INTO Employee VALUES('F1','L1','F1 L1')
INSERT INTO Employee VALUES('F2','L2','F2 L2')
INSERT INTO Employee VALUES('F1','L1','F1 L1')
Then in ADP, I managed to run the query:
DoCmd.RunSQL "Update Employee SET Description='FX LX' WHERE First_Name =
'F1'"
For a table with no primary key, I think it is a by design limitation in
Access database (.mdb) and I have submited a feedback for this.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Running the exec command on the second machine...

I have a query to execute. The query is :-

exec master.dbo.xp_fileexist @.nFilePath

This i can execute on the current machine.

But how do i run this one on another machine.
I took the machinename as a parameter and executed like :-

exec @.nMachinename.master.dbo.xp_fileexist @.nFilePath

But the exec command is not taking this...

Giving me errors.

Now how can i give the second machines name as a paramter and run dynamically??

Thanks,
Sandu.not sure about this but try using linked server.|||Okies..

I got it...

I have to set the value into a variable of the above mentioned query and then execute the variable...

Anyway thanks...

--Sandu.|||try

declare @.sql nvarchar(1000)
select @.sql = 'exec ' + @.nMachinename + '.master.dbo.xp_fileexist ''' + @.nFilePath + ''''
exec (@.sql)

Running Symantec AntiVirus software on SQL Server 2000 v8.00.194

Is anyone running Symantec (Norton) AntiVirus Corporate v9 on a machine that
has SQL Server 2000 v8.00.194? If so, have you experienced any problems. W
e
are planning to add this antivirus software to our server running eBackOffic
e
Suite Release 7.2 that uses databases stored in SQL Server 2000.
--
Thanks for any assistance you can provide!
--
TammyFirst of all, is there any reason why you are still running on build
194? That's the RTM release of SQL Server 2000 without any service pack,
and more importantly without any security fixes. That itself is a big
risk, exposing the server to virus attack such as slammer.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Tambo wrote:
> Is anyone running Symantec (Norton) AntiVirus Corporate v9 on a machine t
hat
> has SQL Server 2000 v8.00.194? If so, have you experienced any problems.
We
> are planning to add this antivirus software to our server running eBackOff
ice
> Suite Release 7.2 that uses databases stored in SQL Server 2000.|||You'd want to exclude your data and log files. Additionally,
the following article has some guidelines:
INF: Consideration for a Virus Scanner on a Computer That Is
Running SQL Server
http://support.microsoft.com/?kbid=309422
-Sue
On Thu, 20 Jan 2005 11:53:02 -0800, "Tambo"
<Tambo@.discussions.microsoft.com> wrote:

> Is anyone running Symantec (Norton) AntiVirus Corporate v9 on a machine th
at
>has SQL Server 2000 v8.00.194? If so, have you experienced any problems.
We
>are planning to add this antivirus software to our server running eBackOffi
ce
>Suite Release 7.2 that uses databases stored in SQL Server 2000.
>--
>Thanks for any assistance you can provide!|||Tambo wrote:
> Is anyone running Symantec (Norton) AntiVirus Corporate v9 on a
> machine that has SQL Server 2000 v8.00.194? If so, have you
> experienced any problems. We are planning to add this antivirus
> software to our server running eBackOffice Suite Release 7.2 that
> uses databases stored in SQL Server 2000. --
> Thanks for any assistance you can provide!
194 is the "buggy" retail release of SQL Server. You should first
consider updating your server to SP3a.
I personally have not had a problem running NAV Corporate on a SQL 2000
box.
David Gugick
Imceda Software
www.imceda.com|||.194?!?!?!
You need to upgrade to SP3a NOW. DO NOT DELAY! There are still cases of
the slammer worm affecting SQL Servers, the last one in the media was
reported in Australia last week.
I have used NAV Corp on SQL Servers with success, but I always exclude
the SQL Server data and log file directories.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Tambo wrote:
> Is anyone running Symantec (Norton) AntiVirus Corporate v9 on a machine t
hat
> has SQL Server 2000 v8.00.194? If so, have you experienced any problems.
We
> are planning to add this antivirus software to our server running eBackOff
ice
> Suite Release 7.2 that uses databases stored in SQL Server 2000.

Running Symantec AntiVirus software on SQL Server 2000 v8.00.194

Is anyone running Symantec (Norton) AntiVirus Corporate v9 on a machine that
has SQL Server 2000 v8.00.194? If so, have you experienced any problems. We
are planning to add this antivirus software to our server running eBackOffice
Suite Release 7.2 that uses databases stored in SQL Server 2000.
Thanks for any assistance you can provide!
Tammy
First of all, is there any reason why you are still running on build
194? That's the RTM release of SQL Server 2000 without any service pack,
and more importantly without any security fixes. That itself is a big
risk, exposing the server to virus attack such as slammer.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Tambo wrote:
> Is anyone running Symantec (Norton) AntiVirus Corporate v9 on a machine that
> has SQL Server 2000 v8.00.194? If so, have you experienced any problems. We
> are planning to add this antivirus software to our server running eBackOffice
> Suite Release 7.2 that uses databases stored in SQL Server 2000.
|||You'd want to exclude your data and log files. Additionally,
the following article has some guidelines:
INF: Consideration for a Virus Scanner on a Computer That Is
Running SQL Server
http://support.microsoft.com/?kbid=309422
-Sue
On Thu, 20 Jan 2005 11:53:02 -0800, "Tambo"
<Tambo@.discussions.microsoft.com> wrote:

> Is anyone running Symantec (Norton) AntiVirus Corporate v9 on a machine that
>has SQL Server 2000 v8.00.194? If so, have you experienced any problems. We
>are planning to add this antivirus software to our server running eBackOffice
>Suite Release 7.2 that uses databases stored in SQL Server 2000.
>--
>Thanks for any assistance you can provide!
|||Tambo wrote:
> Is anyone running Symantec (Norton) AntiVirus Corporate v9 on a
> machine that has SQL Server 2000 v8.00.194? If so, have you
> experienced any problems. We are planning to add this antivirus
> software to our server running eBackOffice Suite Release 7.2 that
> uses databases stored in SQL Server 2000. --
> Thanks for any assistance you can provide!
194 is the "buggy" retail release of SQL Server. You should first
consider updating your server to SP3a.
I personally have not had a problem running NAV Corporate on a SQL 2000
box.
David Gugick
Imceda Software
www.imceda.com
|||..194?!?!?!
You need to upgrade to SP3a NOW. DO NOT DELAY! There are still cases of
the slammer worm affecting SQL Servers, the last one in the media was
reported in Australia last week.
I have used NAV Corp on SQL Servers with success, but I always exclude
the SQL Server data and log file directories.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Tambo wrote:
> Is anyone running Symantec (Norton) AntiVirus Corporate v9 on a machine that
> has SQL Server 2000 v8.00.194? If so, have you experienced any problems. We
> are planning to add this antivirus software to our server running eBackOffice
> Suite Release 7.2 that uses databases stored in SQL Server 2000.

Running Symantec AntiVirus software on SQL Server 2000 v8.00.194

Is anyone running Symantec (Norton) AntiVirus Corporate v9 on a machine that
has SQL Server 2000 v8.00.194? If so, have you experienced any problems. We
are planning to add this antivirus software to our server running eBackOffice
Suite Release 7.2 that uses databases stored in SQL Server 2000.
--
Thanks for any assistance you can provide!
--
TammyFirst of all, is there any reason why you are still running on build
194? That's the RTM release of SQL Server 2000 without any service pack,
and more importantly without any security fixes. That itself is a big
risk, exposing the server to virus attack such as slammer.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Tambo wrote:
> Is anyone running Symantec (Norton) AntiVirus Corporate v9 on a machine that
> has SQL Server 2000 v8.00.194? If so, have you experienced any problems. We
> are planning to add this antivirus software to our server running eBackOffice
> Suite Release 7.2 that uses databases stored in SQL Server 2000.|||You'd want to exclude your data and log files. Additionally,
the following article has some guidelines:
INF: Consideration for a Virus Scanner on a Computer That Is
Running SQL Server
http://support.microsoft.com/?kbid=309422
-Sue
On Thu, 20 Jan 2005 11:53:02 -0800, "Tambo"
<Tambo@.discussions.microsoft.com> wrote:
> Is anyone running Symantec (Norton) AntiVirus Corporate v9 on a machine that
>has SQL Server 2000 v8.00.194? If so, have you experienced any problems. We
>are planning to add this antivirus software to our server running eBackOffice
>Suite Release 7.2 that uses databases stored in SQL Server 2000.
>--
>Thanks for any assistance you can provide!|||Tambo wrote:
> Is anyone running Symantec (Norton) AntiVirus Corporate v9 on a
> machine that has SQL Server 2000 v8.00.194? If so, have you
> experienced any problems. We are planning to add this antivirus
> software to our server running eBackOffice Suite Release 7.2 that
> uses databases stored in SQL Server 2000. --
> Thanks for any assistance you can provide!
194 is the "buggy" retail release of SQL Server. You should first
consider updating your server to SP3a.
I personally have not had a problem running NAV Corporate on a SQL 2000
box.
David Gugick
Imceda Software
www.imceda.com|||.194?!?!?!
You need to upgrade to SP3a NOW. DO NOT DELAY! There are still cases of
the slammer worm affecting SQL Servers, the last one in the media was
reported in Australia last week.
I have used NAV Corp on SQL Servers with success, but I always exclude
the SQL Server data and log file directories.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Tambo wrote:
> Is anyone running Symantec (Norton) AntiVirus Corporate v9 on a machine that
> has SQL Server 2000 v8.00.194? If so, have you experienced any problems. We
> are planning to add this antivirus software to our server running eBackOffice
> Suite Release 7.2 that uses databases stored in SQL Server 2000.sql

Running SQLSERVER2005 with SQLSERVER2000

Hi,
Is it possible to run SQL2000 on the same machine as 2005? Will there be any
major problems? Can it be done at all? Will they simply be treated as
seperate applications?
Thanks for your help on this.
AntHi,
You can run both in parellel, but make the memory static (FIX) for both
instances. As you said both will be running as seperate applications
(Thread) .
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:C8C2BEE3-7F22-418F-B954-DEDEDCFFAE74@.microsoft.com...
> Hi,
> Is it possible to run SQL2000 on the same machine as 2005? Will there be
> any
> major problems? Can it be done at all? Will they simply be treated as
> seperate applications?
> Thanks for your help on this.
> Ant|||Ant wrote:
> Hi,
> Is it possible to run SQL2000 on the same machine as 2005? Will there be a
ny
> major problems? Can it be done at all? Will they simply be treated as
> seperate applications?
> Thanks for your help on this.
> Ant
Hi
As Hari has pointed out, there are no problems in running both versions
on a server. You just have to keep in mind that only one of them can be
a default instance - the other will have to be a named instance.
Regards
Steen Schlüter Persson
Database Administrator / System Administratorsql

Running SQLSERVER2005 with SQLSERVER2000

Hi,
Is it possible to run SQL2000 on the same machine as 2005? Will there be any
major problems? Can it be done at all? Will they simply be treated as
seperate applications?
Thanks for your help on this.
Ant
Hi,
You can run both in parellel, but make the memory static (FIX) for both
instances. As you said both will be running as seperate applications
(Thread) .
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:C8C2BEE3-7F22-418F-B954-DEDEDCFFAE74@.microsoft.com...
> Hi,
> Is it possible to run SQL2000 on the same machine as 2005? Will there be
> any
> major problems? Can it be done at all? Will they simply be treated as
> seperate applications?
> Thanks for your help on this.
> Ant

Running SQLSERVER2005 with SQLSERVER2000

Hi,
Is it possible to run SQL2000 on the same machine as 2005? Will there be any
major problems? Can it be done at all? Will they simply be treated as
seperate applications?
Thanks for your help on this.
AntHi,
You can run both in parellel, but make the memory static (FIX) for both
instances. As you said both will be running as seperate applications
(Thread) .
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:C8C2BEE3-7F22-418F-B954-DEDEDCFFAE74@.microsoft.com...
> Hi,
> Is it possible to run SQL2000 on the same machine as 2005? Will there be
> any
> major problems? Can it be done at all? Will they simply be treated as
> seperate applications?
> Thanks for your help on this.
> Ant|||Ant wrote:
> Hi,
> Is it possible to run SQL2000 on the same machine as 2005? Will there be any
> major problems? Can it be done at all? Will they simply be treated as
> seperate applications?
> Thanks for your help on this.
> Ant
Hi
As Hari has pointed out, there are no problems in running both versions
on a server. You just have to keep in mind that only one of them can be
a default instance - the other will have to be a named instance.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Running SqlAgent on a different processor

I have a machine with dual processor installed. I want sqlagent to be run on
the second processor while my normal queries run on the first processor is i
t
viable"Genius" <Genius@.discussions.microsoft.com> wrote in message
news:2D2F162B-F706-46BE-85E2-63D3BA9AF516@.microsoft.com...
> I have a machine with dual processor installed. I want sqlagent to be run
on
> the second processor while my normal queries run on the first processor is
it
> viable
>
As far as I know this is not possible... in almost every instance you're
better off letting the server OS manage the use context switching of
multi-processor machines.
Steve|||All the Whitepaper studies I've seen show this to be true: that the OS is
far more capable of handling the scheduling work through the SMP processors
than attempting to manually affinitizing the process yourself.
That being said, yes you can affinitize processes. You can manually set the
process affinity to a currently running executable through task manager. If
you want this to be permanent, you must create a registry key in the
Services Key for that process. There is a tool you can download from
Microsoft that will handle the details for you. Search for Process Affinity
to find it.
Sincerely,
Anthony Thomas
"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:O896ydn8EHA.208@.TK2MSFTNGP12.phx.gbl...
"Genius" <Genius@.discussions.microsoft.com> wrote in message
news:2D2F162B-F706-46BE-85E2-63D3BA9AF516@.microsoft.com...
> I have a machine with dual processor installed. I want sqlagent to be run
on
> the second processor while my normal queries run on the first processor is
it
> viable
>
As far as I know this is not possible... in almost every instance you're
better off letting the server OS manage the use context switching of
multi-processor machines.
Steve

Running SqlAgent on a different processor

I have a machine with dual processor installed. I want sqlagent to be run on
the second processor while my normal queries run on the first processor is it
viable
"Genius" <Genius@.discussions.microsoft.com> wrote in message
news:2D2F162B-F706-46BE-85E2-63D3BA9AF516@.microsoft.com...
> I have a machine with dual processor installed. I want sqlagent to be run
on
> the second processor while my normal queries run on the first processor is
it
> viable
>
As far as I know this is not possible... in almost every instance you're
better off letting the server OS manage the use context switching of
multi-processor machines.
Steve
|||All the Whitepaper studies I've seen show this to be true: that the OS is
far more capable of handling the scheduling work through the SMP processors
than attempting to manually affinitizing the process yourself.
That being said, yes you can affinitize processes. You can manually set the
process affinity to a currently running executable through task manager. If
you want this to be permanent, you must create a registry key in the
Services Key for that process. There is a tool you can download from
Microsoft that will handle the details for you. Search for Process Affinity
to find it.
Sincerely,
Anthony Thomas

"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:O896ydn8EHA.208@.TK2MSFTNGP12.phx.gbl...
"Genius" <Genius@.discussions.microsoft.com> wrote in message
news:2D2F162B-F706-46BE-85E2-63D3BA9AF516@.microsoft.com...
> I have a machine with dual processor installed. I want sqlagent to be run
on
> the second processor while my normal queries run on the first processor is
it
> viable
>
As far as I know this is not possible... in almost every instance you're
better off letting the server OS manage the use context switching of
multi-processor machines.
Steve
sql

Running SqlAgent on a different processor

I have a machine with dual processor installed. I want sqlagent to be run on
the second processor while my normal queries run on the first processor is it
viable"Genius" <Genius@.discussions.microsoft.com> wrote in message
news:2D2F162B-F706-46BE-85E2-63D3BA9AF516@.microsoft.com...
> I have a machine with dual processor installed. I want sqlagent to be run
on
> the second processor while my normal queries run on the first processor is
it
> viable
>
As far as I know this is not possible... in almost every instance you're
better off letting the server OS manage the use context switching of
multi-processor machines.
Steve|||All the Whitepaper studies I've seen show this to be true: that the OS is
far more capable of handling the scheduling work through the SMP processors
than attempting to manually affinitizing the process yourself.
That being said, yes you can affinitize processes. You can manually set the
process affinity to a currently running executable through task manager. If
you want this to be permanent, you must create a registry key in the
Services Key for that process. There is a tool you can download from
Microsoft that will handle the details for you. Search for Process Affinity
to find it.
Sincerely,
Anthony Thomas
"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:O896ydn8EHA.208@.TK2MSFTNGP12.phx.gbl...
"Genius" <Genius@.discussions.microsoft.com> wrote in message
news:2D2F162B-F706-46BE-85E2-63D3BA9AF516@.microsoft.com...
> I have a machine with dual processor installed. I want sqlagent to be run
on
> the second processor while my normal queries run on the first processor is
it
> viable
>
As far as I know this is not possible... in almost every instance you're
better off letting the server OS manage the use context switching of
multi-processor machines.
Steve

running sql xml versions next to each other?

Hi,
i have installed sql 2005 on my test machine, and it installed sql xml 4.0.
Now i wanted to try running BizTalk 2004 on it, but the install complains i
have to have sql xml 3.0 sp2 or higher...
So the question is, can i install sql xml 3.0sp3 on the machine, without
breaking the 4.0 or the sql 2005 install altogether?
thnx,
J.
Hello,
You should be able to use sqlxml 3.0 and sqlxml 4.0 side by side without
any problem.
Thanks,
Monica Frintu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

running sql xml versions next to each other?

Hi,
i have installed sql 2005 on my test machine, and it installed sql xml 4.0.
Now i wanted to try running BizTalk 2004 on it, but the install complains i
have to have sql xml 3.0 sp2 or higher...
So the question is, can i install sql xml 3.0sp3 on the machine, without
breaking the 4.0 or the sql 2005 install altogether?
thnx,
J.Hello,
You should be able to use sqlxml 3.0 and sqlxml 4.0 side by side without
any problem.
Thanks,
Monica Frintu
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Running SQL Server on a Desktop Machine

Hi,

The company I work at was going to install SQL Server 2005 on a server running Windows Server 2000. However this server is no longer available, and due to cost restraints we are unable to purchase another server.

I still wish to get SQL Server 2005 running, and have a spare desktop computer avaible. This computer is a P4 3.2GHz with 1GB of RAM and 40GB hard disk.
I was thinking of adding RAM and a larger hard disk, and installing Windows Server 2003 on it.

Is it advisable to run SQL Server 2005 (and Server 2003) on a desktop computer? Any comments or experiences of similar situations would be appreciated.

Thanks,
MattThere's lots of issues here;
1 What's it for?
2 Version
You have not mentioned which version of SQL Server 2005
3 Capacity
Your hardware spec. meets the minimum requirements, you can install SQL 2005, but what you will actually be doing with it will determine the hardware.
4 Availability
Again, what do you need to do on this server; server hardware is built to serve mission critical applications, the numbers might not look as good but you generally get a system that allows for RAID, hot swapping HDDs and memory, etc.
5 Maintenance & Support
E.g. If a HDD fails, will your hardware guys fix it, or will they say - what's this mickey-mouse setup?

And so on...|||Thanks for the reply, sorry I should have been more specific.

The version used would be the Standard Edition.

I am responsible for the reporting of our organisation. Currently data is taken from the enterprise system and reported on using Microsoft Access and Excel. So if I upgraded to SQL Server then it would be used to implement a data warehousing solution. Table updates would run in the morning, along with the emailing/printing of several reports. After that most usage would be via Excel queries. There would be around 20-30 users doing this, with no more than 10 accessing the SQL Server concurrently.

Eventually the SQL Server system would be moved to a server, but this cannot happen until 8 months or so. I am ready to start going live with a SQL Server solution, and want to use to desktop machine as a temporary solution. I want to know whether using this desktop will provide a workable solution. I know it is far from perfect, but considering we are currently running Access as a reporting database server, will the solution work temporarily?

Regarding maintennance and support, seeing it is not a mission critical solution, I had planned to backup the data files and any code created in BIDS on a seperate server. In the event of a disaster this would allow me to recreate the data warehouse on another hard drive. Again, it is an imperfect solution but I am hoping it would do the job until a server machine becomes available.

Purchasing a server is not really an option due to the price. The cost of SQL Server has already been approved, and if I go with this desktop machine only another $1200 or so will be needed for Server 2003 and hardware upgrades.

Any help or opinions with this would be great.|||You haven't said anything that makes me think it won't be able to handle the capacity, but I still would worry about a live service on a server-under-the-desk scenario.
Apart from the issues I've alreay mentioned, make sure you aren't breaking any corporate standards, laws or regulatory requirements for the data you are handling.

Running SQL Server DTS Package from ASP.NET page problem

I'm having some problems running a DTS package from my ASP.NET appllication. It runs prefectly in debug from my local machine but as soon as I move it to the web server I receive the following error:

Error: -2147467259
Source: Microsoft JET Database Engine
Description: The Microsoft Jet database engine cannot open the file '\\serverB\PayFiles\Pay3.xls'. It is already opened exclusively by another user, or you need permission to view its data.

The file is not opened by anyone else and the share permissions are full control for everyone.

My set up is as follows

serverA - Webserver & DB Server
serverB - File Server

The DTS package pulls in the file from serverB to a database table on serverA. The sa account is used for the database connection and connecting to the DTS package from the application. My code for running the DTS package is as follows -

Private Sub ExecutePackage(ByVal serverAs String,ByVal packageAs String,ByVal usernameAs String,ByVal passwordAs String)Const DTSSQLStgFlag_Default = 0Const DTSStepExecResult_Failure = 1Dim oPKGAs DTS.Package, oStepAs DTS.Step oPKG =New DTS.PackageDim sServerAs String, sUsernameAs String, sPasswordAs String Dim sPackageNameAs String, sMessageAs String Dim lErrAs Long, sSourceAs String, sDescAs String' Set Parameter Values sServer = server sUsername = username sPassword = password sPackageName = package' Load Package oPKG.LoadFromSQLServer(sServer, sUsername, sPassword, _ DTSSQLStgFlag_Default, , , , sPackageName)' Set Exec on Main ThreadFor Each oStepIn oPKG.Steps oStep.ExecuteInMainThread =True Next' Execute oPKG.Execute()' Get Status and Error MessageFor Each oStepIn oPKG.StepsIf oStep.ExecutionResult = DTSStepExecResult_FailureThen oStep.GetExecutionErrorInfo(lErr, sSource, sDesc) sMessage = sMessage &"Step " & oStep.Name &" Failed<br><br>Error: " & lErr &"<br>Source: " & sSource &"<br>Description: " & sDesc &"<br><br>"Else sMessage = sMessage &"Step " & oStep.Name &" Succeeded<br>"End If Next oPKG.UnInitialize() oStep =Nothing oPKG =Nothing' Display Results 'MsgBox(sMessage) lblOutput.Text = sMessageEnd Sub

I'm using windows authentication with identity impersonate set to true. If I change to use basic authentication and enter my login when I go to the site the DTS package runs successfully. So I assume that I'm seeing this 'double hop' issue.

serverA is for testing so I moved the DTS package and web application to serverB so now everything is on the same server and should eliminate the double hop? No! Works perfectly from my PC in debug, but from the web server the same error occurs. Changing to basic authentication does not workaround the issue on the live server.

The DTS package always works when run from Enterprise manager.

I know this is a premissions issue - does anyone know what I'm doing wrong? Hopefully I've supplied enough info, if not just ask!

You may want to use a windows service instead of a web page, as when the web page stops rendering the process will also stop.

|||

If I use a windows service will I still be able to give the user control over when the package is run?

|||

I've decided to use a stored procedure and DTSRun to do this instead and then simply execute the stored procedure from my page. Just in case anyone else wants to use it this is my code.

CREATE PROC [dbo].[sp_DTSRun] @.ServerNameVARCHAR(30),@.PackageNameVARCHAR(50)ASBEGINDECLARE @.ReturnValueint, @.Cmdvarchar(500)SET @.ReturnValue = -1SET @.Cmd ='dtsrun /S ' + @.ServerName +' /N ' + @.PackageName +' /E'--' /U "LoginName" /P "password" 'EXECUTE @.ReturnValue = master..xp_cmdshell @.Cmd, NO_OUTPUT--RETURN @.ReturnValueSELECT @.ReturnValue [Result]END

Private Sub ExecutePackage(ByVal serverAs String,ByVal packageAs String)Dim ExecuteAdapterAs New PAY_DAL.RunDTSTableAdapters.sp_DTSRunTableAdapter() ExecuteAdapter.SetCommandTimeOut(0)Dim strResultAs String = ExecuteAdapter.Execute(server, package)Select Case strResultCase 0 lblOutput.Text ="The package executed successfully."Case 1 lblOutput.Text ="The package failed - contact support."Case 2 lblOutput.Text ="2"Case 3 lblOutput.Text ="The package was cancelled by the user."Case 4 lblOutput.Text ="The utility was unable to locate the requested package. The package could not be found."Case 5 lblOutput.Text ="The utility was unable to load the requested package. The package could not be loaded."Case 6 lblOutput.Text ="The utility encountered an internal error of syntactic or semantic errors in the command line."End Select lblOutput.Visible =True End Sub

Just pass the package and server name in. To use a username and password remove the /E (trusted connection) and add /U and /P.

running sql server data marked suspect

hi all,
I run application & database on same machine and suddenly my database marked
suspect and data base is unavailable.
I copy mdf file to other location and try to attached data but it show me
error number 3624 any one help my I don't know why the database marked
suspect and how can I resolve this problem if any one has solution of this
problem then please help me as soon as possible
Thanks in advance
Regards
khurramhttp://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Syed khurram alam" <khurram.alam@.eintelligencesoft.com> wrote in message
news:e1X$u1gVFHA.2684@.TK2MSFTNGP09.phx.gbl...
> hi all,
> I run application & database on same machine and suddenly my database mark
ed suspect and data base
> is unavailable.
> I copy mdf file to other location and try to attached data but it show me
error number 3624 any
> one help my I don't know why the database marked suspect and how can I res
olve this problem if any
> one has solution of this problem then please help me as soon as possible
> Thanks in advance
> Regards
> khurram
>

running sql server data marked suspect

hi all,
I run application & database on same machine and suddenly my database marked
suspect and data base is unavailable.
I copy mdf file to other location and try to attached data but it show me
error number 3624 any one help my I don't know why the database marked
suspect and how can I resolve this problem if any one has solution of this
problem then please help me as soon as possible
Thanks in advance
Regards
khurram
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Syed khurram alam" <khurram.alam@.eintelligencesoft.com> wrote in message
news:e1X$u1gVFHA.2684@.TK2MSFTNGP09.phx.gbl...
> hi all,
> I run application & database on same machine and suddenly my database marked suspect and data base
> is unavailable.
> I copy mdf file to other location and try to attached data but it show me error number 3624 any
> one help my I don't know why the database marked suspect and how can I resolve this problem if any
> one has solution of this problem then please help me as soon as possible
> Thanks in advance
> Regards
> khurram
>

running sql server data marked suspect

hi all,
I run application & database on same machine and suddenly my database marked
suspect and data base is unavailable.
I copy mdf file to other location and try to attached data but it show me
error number 3624 any one help my I don't know why the database marked
suspect and how can I resolve this problem if any one has solution of this
problem then please help me as soon as possible
Thanks in advance
Regards
khurramhttp://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Syed khurram alam" <khurram.alam@.eintelligencesoft.com> wrote in message
news:e1X$u1gVFHA.2684@.TK2MSFTNGP09.phx.gbl...
> hi all,
> I run application & database on same machine and suddenly my database marked suspect and data base
> is unavailable.
> I copy mdf file to other location and try to attached data but it show me error number 3624 any
> one help my I don't know why the database marked suspect and how can I resolve this problem if any
> one has solution of this problem then please help me as soon as possible
> Thanks in advance
> Regards
> khurram
>

Running SQL server 2000 & 2005 side by side

Hai

My system is already installed with SQL server 2000 . Is it possible for me to install SQL server 2005 BETA CTP in the same machine and run both side by side.

Regards

I am not sure which CTP you have, since we have RTMed SQL 2005, it would be better if you want to evalute SQL Server to install the full SQL Server 2005 Eval version. Depening on which Beta you have, side-by-side installations may or may not work for you.

|||

hai bob

Thanks for your reply.

How can i check my version supports side by side execution.