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.
================================================== ====
Showing posts with label sql2005. Show all posts
Showing posts with label sql2005. Show all posts
Friday, March 30, 2012
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.
========================================
==============
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.
========================================
==============
Wednesday, March 21, 2012
Running SQL2005 profiler from an SQLAgent job
Hi,
I would like to run a certain profiler template against a server on a
regular basis. I can run profiler90 from the Command Prompt and it starts
creating the trace file. Can I run profiler90 from within an SQLAgent job? I
have tried and it did not run.
Thanks
Chris
Hi Chris,
You have to use the trace procedures. An easy way to get started, is to
script the trace from file > export menu.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:e3dSWiwnIHA.4536@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I would like to run a certain profiler template against a server on a
> regular basis. I can run profiler90 from the Command Prompt and it starts
> creating the trace file. Can I run profiler90 from within an SQLAgent job?
> I have tried and it did not run.
> Thanks
> Chris
>
|||Hi
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:e3dSWiwnIHA.4536@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I would like to run a certain profiler template against a server on a
> regular basis. I can run profiler90 from the Command Prompt and it starts
> creating the trace file. Can I run profiler90 from within an SQLAgent job?
> I have tried and it did not run.
> Thanks
> Chris
>
|||Thanks Uri and Jason. I will look at the url link and go from there.
Chris
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uV5GL55nIHA.4328@.TK2MSFTNGP03.phx.gbl...
> Hi
> http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:e3dSWiwnIHA.4536@.TK2MSFTNGP06.phx.gbl...
>
I would like to run a certain profiler template against a server on a
regular basis. I can run profiler90 from the Command Prompt and it starts
creating the trace file. Can I run profiler90 from within an SQLAgent job? I
have tried and it did not run.
Thanks
Chris
Hi Chris,
You have to use the trace procedures. An easy way to get started, is to
script the trace from file > export menu.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:e3dSWiwnIHA.4536@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I would like to run a certain profiler template against a server on a
> regular basis. I can run profiler90 from the Command Prompt and it starts
> creating the trace file. Can I run profiler90 from within an SQLAgent job?
> I have tried and it did not run.
> Thanks
> Chris
>
|||Hi
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:e3dSWiwnIHA.4536@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I would like to run a certain profiler template against a server on a
> regular basis. I can run profiler90 from the Command Prompt and it starts
> creating the trace file. Can I run profiler90 from within an SQLAgent job?
> I have tried and it did not run.
> Thanks
> Chris
>
|||Thanks Uri and Jason. I will look at the url link and go from there.
Chris
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uV5GL55nIHA.4328@.TK2MSFTNGP03.phx.gbl...
> Hi
> http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:e3dSWiwnIHA.4536@.TK2MSFTNGP06.phx.gbl...
>
Running SQL2005 profiler from an SQLAgent job
Hi,
I would like to run a certain profiler template against a server on a
regular basis. I can run profiler90 from the Command Prompt and it starts
creating the trace file. Can I run profiler90 from within an SQLAgent job? I
have tried and it did not run.
Thanks
ChrisHi Chris,
You have to use the trace procedures. An easy way to get started, is to
script the trace from file > export menu.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:e3dSWiwnIHA.4536@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I would like to run a certain profiler template against a server on a
> regular basis. I can run profiler90 from the Command Prompt and it starts
> creating the trace file. Can I run profiler90 from within an SQLAgent job?
> I have tried and it did not run.
> Thanks
> Chris
>|||Hi
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:e3dSWiwnIHA.4536@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I would like to run a certain profiler template against a server on a
> regular basis. I can run profiler90 from the Command Prompt and it starts
> creating the trace file. Can I run profiler90 from within an SQLAgent job?
> I have tried and it did not run.
> Thanks
> Chris
>|||Thanks Uri and Jason. I will look at the url link and go from there.
Chris
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uV5GL55nIHA.4328@.TK2MSFTNGP03.phx.gbl...
> Hi
> http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:e3dSWiwnIHA.4536@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> I would like to run a certain profiler template against a server on a
>> regular basis. I can run profiler90 from the Command Prompt and it starts
>> creating the trace file. Can I run profiler90 from within an SQLAgent
>> job? I have tried and it did not run.
>> Thanks
>> Chris
>
I would like to run a certain profiler template against a server on a
regular basis. I can run profiler90 from the Command Prompt and it starts
creating the trace file. Can I run profiler90 from within an SQLAgent job? I
have tried and it did not run.
Thanks
ChrisHi Chris,
You have to use the trace procedures. An easy way to get started, is to
script the trace from file > export menu.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:e3dSWiwnIHA.4536@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I would like to run a certain profiler template against a server on a
> regular basis. I can run profiler90 from the Command Prompt and it starts
> creating the trace file. Can I run profiler90 from within an SQLAgent job?
> I have tried and it did not run.
> Thanks
> Chris
>|||Hi
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:e3dSWiwnIHA.4536@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I would like to run a certain profiler template against a server on a
> regular basis. I can run profiler90 from the Command Prompt and it starts
> creating the trace file. Can I run profiler90 from within an SQLAgent job?
> I have tried and it did not run.
> Thanks
> Chris
>|||Thanks Uri and Jason. I will look at the url link and go from there.
Chris
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uV5GL55nIHA.4328@.TK2MSFTNGP03.phx.gbl...
> Hi
> http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:e3dSWiwnIHA.4536@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> I would like to run a certain profiler template against a server on a
>> regular basis. I can run profiler90 from the Command Prompt and it starts
>> creating the trace file. Can I run profiler90 from within an SQLAgent
>> job? I have tried and it did not run.
>> Thanks
>> Chris
>
running sql2005 job
hi, I have a job failed for the following error. The job is send an email to
me(outlook). any ideas?
Thanks
Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
component is turned off as part of the security configuration for this
server. A system administrator can enable the use of 'Ole Automation
Procedures' by using sp_configure. For more information about enabling 'Ole
Automation Procedures', see "Surface Area Configuration" in SQL Server Books
Online. [SQLSTATE 42000] (Error 15281). The step failed.mecn,
Yes, the idea is to do what the messages says: "enable the use of 'Ole
Automation
Procedures' by using sp_configure."
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Or use the Surface Area Configuration tool to do the same thing
interactively.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
> hi, I have a job failed for the following error. The job is send an email
> to me(outlook). any ideas?
> Thanks
> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
> component is turned off as part of the security configuration for this
> server. A system administrator can enable the use of 'Ole Automation
> Procedures' by using sp_configure. For more information about enabling
> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
>|||Thanks a lot. I apprecite
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23eAfSXeqHHA.4548@.TK2MSFTNGP03.phx.gbl...
> mecn,
> Yes, the idea is to do what the messages says: "enable the use of 'Ole
> Automation
> Procedures' by using sp_configure."
> sp_configure 'Ole Automation Procedures', 1
> GO
> RECONFIGURE WITH OVERRIDE
> GO
> Or use the Surface Area Configuration tool to do the same thing
> interactively.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
>
me(outlook). any ideas?
Thanks
Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
component is turned off as part of the security configuration for this
server. A system administrator can enable the use of 'Ole Automation
Procedures' by using sp_configure. For more information about enabling 'Ole
Automation Procedures', see "Surface Area Configuration" in SQL Server Books
Online. [SQLSTATE 42000] (Error 15281). The step failed.mecn,
Yes, the idea is to do what the messages says: "enable the use of 'Ole
Automation
Procedures' by using sp_configure."
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Or use the Surface Area Configuration tool to do the same thing
interactively.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
> hi, I have a job failed for the following error. The job is send an email
> to me(outlook). any ideas?
> Thanks
> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
> component is turned off as part of the security configuration for this
> server. A system administrator can enable the use of 'Ole Automation
> Procedures' by using sp_configure. For more information about enabling
> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
>|||Thanks a lot. I apprecite
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23eAfSXeqHHA.4548@.TK2MSFTNGP03.phx.gbl...
> mecn,
> Yes, the idea is to do what the messages says: "enable the use of 'Ole
> Automation
> Procedures' by using sp_configure."
> sp_configure 'Ole Automation Procedures', 1
> GO
> RECONFIGURE WITH OVERRIDE
> GO
> Or use the Surface Area Configuration tool to do the same thing
> interactively.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
>
Tuesday, March 20, 2012
running sql2005 job
hi, I have a job failed for the following error. The job is send an email to
me(outlook). any ideas?
Thanks
Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
component is turned off as part of the security configuration for this
server. A system administrator can enable the use of 'Ole Automation
Procedures' by using sp_configure. For more information about enabling 'Ole
Automation Procedures', see "Surface Area Configuration" in SQL Server Books
Online. [SQLSTATE 42000] (Error 15281). The step failed.
mecn,
Yes, the idea is to do what the messages says: "enable the use of 'Ole
Automation
Procedures' by using sp_configure."
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Or use the Surface Area Configuration tool to do the same thing
interactively.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
> hi, I have a job failed for the following error. The job is send an email
> to me(outlook). any ideas?
> Thanks
> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
> component is turned off as part of the security configuration for this
> server. A system administrator can enable the use of 'Ole Automation
> Procedures' by using sp_configure. For more information about enabling
> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
>
|||Thanks a lot. I apprecite
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23eAfSXeqHHA.4548@.TK2MSFTNGP03.phx.gbl...
> mecn,
> Yes, the idea is to do what the messages says: "enable the use of 'Ole
> Automation
> Procedures' by using sp_configure."
> sp_configure 'Ole Automation Procedures', 1
> GO
> RECONFIGURE WITH OVERRIDE
> GO
> Or use the Surface Area Configuration tool to do the same thing
> interactively.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
>
me(outlook). any ideas?
Thanks
Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
component is turned off as part of the security configuration for this
server. A system administrator can enable the use of 'Ole Automation
Procedures' by using sp_configure. For more information about enabling 'Ole
Automation Procedures', see "Surface Area Configuration" in SQL Server Books
Online. [SQLSTATE 42000] (Error 15281). The step failed.
mecn,
Yes, the idea is to do what the messages says: "enable the use of 'Ole
Automation
Procedures' by using sp_configure."
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Or use the Surface Area Configuration tool to do the same thing
interactively.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
> hi, I have a job failed for the following error. The job is send an email
> to me(outlook). any ideas?
> Thanks
> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
> component is turned off as part of the security configuration for this
> server. A system administrator can enable the use of 'Ole Automation
> Procedures' by using sp_configure. For more information about enabling
> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
>
|||Thanks a lot. I apprecite
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23eAfSXeqHHA.4548@.TK2MSFTNGP03.phx.gbl...
> mecn,
> Yes, the idea is to do what the messages says: "enable the use of 'Ole
> Automation
> Procedures' by using sp_configure."
> sp_configure 'Ole Automation Procedures', 1
> GO
> RECONFIGURE WITH OVERRIDE
> GO
> Or use the Surface Area Configuration tool to do the same thing
> interactively.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
>
running sql2005 job
hi, I have a job failed for the following error. The job is send an email to
me(outlook). any ideas?
Thanks
Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
component is turned off as part of the security configuration for this
server. A system administrator can enable the use of 'Ole Automation
Procedures' by using sp_configure. For more information about enabling 'Ole
Automation Procedures', see "Surface Area Configuration" in SQL Server Books
Online. [SQLSTATE 42000] (Error 15281). The step failed.Thanks a lot. I apprecite
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23eAfSXeqHHA.4548@.TK2MSFTNGP03.phx.gbl...
> mecn,
> Yes, the idea is to do what the messages says: "enable the use of 'Ole
> Automation
> Procedures' by using sp_configure."
> sp_configure 'Ole Automation Procedures', 1
> GO
> RECONFIGURE WITH OVERRIDE
> GO
> Or use the Surface Area Configuration tool to do the same thing
> interactively.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
>> hi, I have a job failed for the following error. The job is send an email
>> to me(outlook). any ideas?
>> Thanks
>> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
>> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
>> component is turned off as part of the security configuration for this
>> server. A system administrator can enable the use of 'Ole Automation
>> Procedures' by using sp_configure. For more information about enabling
>> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
>> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
>|||mecn,
Yes, the idea is to do what the messages says: "enable the use of 'Ole
Automation
Procedures' by using sp_configure."
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Or use the Surface Area Configuration tool to do the same thing
interactively.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
> hi, I have a job failed for the following error. The job is send an email
> to me(outlook). any ideas?
> Thanks
> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
> component is turned off as part of the security configuration for this
> server. A system administrator can enable the use of 'Ole Automation
> Procedures' by using sp_configure. For more information about enabling
> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
>|||I am having the same problem here.
I have enabled OLE Automation, the services are running under local system
account, but i am still not able to create an object using sp_OACreate
the error message i am getting is
0x80010105 ODSOLE Extended Procedure The server threw an exception.
It is very nice error message that it won't give any idea :)
Thanks
Joko
"mecn" wrote:
> Thanks a lot. I apprecite
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23eAfSXeqHHA.4548@.TK2MSFTNGP03.phx.gbl...
> > mecn,
> >
> > Yes, the idea is to do what the messages says: "enable the use of 'Ole
> > Automation
> > Procedures' by using sp_configure."
> >
> > sp_configure 'Ole Automation Procedures', 1
> > GO
> > RECONFIGURE WITH OVERRIDE
> > GO
> >
> > Or use the Surface Area Configuration tool to do the same thing
> > interactively.
> >
> > RLF
> > "mecn" <mecn2002@.yahoo.com> wrote in message
> > news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
> >> hi, I have a job failed for the following error. The job is send an email
> >> to me(outlook). any ideas?
> >>
> >> Thanks
> >>
> >> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
> >> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
> >> component is turned off as part of the security configuration for this
> >> server. A system administrator can enable the use of 'Ole Automation
> >> Procedures' by using sp_configure. For more information about enabling
> >> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
> >> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
> >>
> >
> >
>
>
me(outlook). any ideas?
Thanks
Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
component is turned off as part of the security configuration for this
server. A system administrator can enable the use of 'Ole Automation
Procedures' by using sp_configure. For more information about enabling 'Ole
Automation Procedures', see "Surface Area Configuration" in SQL Server Books
Online. [SQLSTATE 42000] (Error 15281). The step failed.Thanks a lot. I apprecite
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23eAfSXeqHHA.4548@.TK2MSFTNGP03.phx.gbl...
> mecn,
> Yes, the idea is to do what the messages says: "enable the use of 'Ole
> Automation
> Procedures' by using sp_configure."
> sp_configure 'Ole Automation Procedures', 1
> GO
> RECONFIGURE WITH OVERRIDE
> GO
> Or use the Surface Area Configuration tool to do the same thing
> interactively.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
>> hi, I have a job failed for the following error. The job is send an email
>> to me(outlook). any ideas?
>> Thanks
>> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
>> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
>> component is turned off as part of the security configuration for this
>> server. A system administrator can enable the use of 'Ole Automation
>> Procedures' by using sp_configure. For more information about enabling
>> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
>> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
>|||mecn,
Yes, the idea is to do what the messages says: "enable the use of 'Ole
Automation
Procedures' by using sp_configure."
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Or use the Surface Area Configuration tool to do the same thing
interactively.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
> hi, I have a job failed for the following error. The job is send an email
> to me(outlook). any ideas?
> Thanks
> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
> component is turned off as part of the security configuration for this
> server. A system administrator can enable the use of 'Ole Automation
> Procedures' by using sp_configure. For more information about enabling
> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
>|||I am having the same problem here.
I have enabled OLE Automation, the services are running under local system
account, but i am still not able to create an object using sp_OACreate
the error message i am getting is
0x80010105 ODSOLE Extended Procedure The server threw an exception.
It is very nice error message that it won't give any idea :)
Thanks
Joko
"mecn" wrote:
> Thanks a lot. I apprecite
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23eAfSXeqHHA.4548@.TK2MSFTNGP03.phx.gbl...
> > mecn,
> >
> > Yes, the idea is to do what the messages says: "enable the use of 'Ole
> > Automation
> > Procedures' by using sp_configure."
> >
> > sp_configure 'Ole Automation Procedures', 1
> > GO
> > RECONFIGURE WITH OVERRIDE
> > GO
> >
> > Or use the Surface Area Configuration tool to do the same thing
> > interactively.
> >
> > RLF
> > "mecn" <mecn2002@.yahoo.com> wrote in message
> > news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
> >> hi, I have a job failed for the following error. The job is send an email
> >> to me(outlook). any ideas?
> >>
> >> Thanks
> >>
> >> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
> >> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
> >> component is turned off as part of the security configuration for this
> >> server. A system administrator can enable the use of 'Ole Automation
> >> Procedures' by using sp_configure. For more information about enabling
> >> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
> >> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
> >>
> >
> >
>
>
Tuesday, February 21, 2012
Running sp_blocker_pss80 under SQL2005 and SQL 2000 shows many spids have non zero waittype unde
I was trying to test sp_blocker_pss80 (and SHERLOCK) on an SQL 2005 system and noticed that it gave much more output than on an SQL 2000 system . This was because the waittypes of many spids were not zero while under SQL 2000 the same ones were zero. Why is that?
For instance here is the subset of the query which is part of the sp_blocker_pss80 run on an sql2005 and sql2000 respectively
select spid, status, blocked, open_tran, waittype,
waittime, cmd, lastwaittype
from master..sysprocesses
SQL 2005
spid status blocked open_tran waittype waittime cmd lastwaittype
- -- -- - --
1 background 0 0 0x0000 0 RESOURCE MONITOR SLEEP_TASK
2 background 0 0 0x0060 250 LAZY WRITER LAZYWRITER_SLEEP
3 suspended 0 0 0x007F 18062 LOG WRITER LOGMGR_QUEUE
4 background 0 0 0x0080 4828 LOCK MONITOR REQUEST_FOR_DEADLOCK_SEARCH
5 background 0 0 0x009D 195558000 SIGNAL HANDLER KSOURCE_WAKEUP
6 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
7 background 0 0 0x00A2 640 TRACE QUEUE TASK SQLTRACE_BUFFER_FLUSH
8 sleeping 0 0 0x0000 0 UNKNOWN TOKEN MISCELLANEOUS
9 background 0 0 0x00A9 195579953 BRKR TASK BROKER_TRANSMITTER
10 suspended 0 0 0x0081 894421 CHECKPOINT CHECKPOINT_QUEUE
11 background 0 0 0x007E 195583703 TASK MANAGER ONDEMAND_TASK_QUEUE
12 background 0 0 0x00AD 195557968 BRKR EVENT HNDLR BROKER_EVENTHANDLER
13 background 0 0 0x00A9 195579875 BRKR TASK BROKER_TRANSMITTER
14 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
15 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
16 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
17 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
18 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
19 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
20 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
21 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
51 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
54 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
55 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
56 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
57 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
58 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
59 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
61 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
62 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
63 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
64 runnable 0 0 0x0000 0 SELECT MISCELLANEOUS
65 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
66 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
67 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
68 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
69 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
(37 row(s) affected)
SQL 2000
spid status blocked open_tran waittype waittime cmd lastwaittype
- -- -- - --
1 background 0 0 0x0000 625 LAZY WRITER SLEEP
2 sleeping 0 0 0x0000 29750 LOG WRITER RESOURCE_QUEUE
3 background 0 0 0x0000 2625 LOCK MONITOR RESOURCE_QUEUE
4 background 0 0 0x0000 0 SIGNAL HANDLER PAGEIOLATCH_SH
5 background 0 0 0x0000 0 TASK MANAGER WRITELOG
6 background 0 0 0x0000 100035093 TASK MANAGER RESOURCE_QUEUE
7 sleeping 0 0 0x0000 738921 CHECKPOINT SLEEP RESOURCE_QUEUE
8 background 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
9 background 0 0 0x0000 0 TASK MANAGER WRITELOG
10 background 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
11 background 0 0 0x0000 0 TASK MANAGER WRITELOG
12 background 0 0 0x0000 0 TASK MANAGER WRITELOG
51 sleeping 0 0 0x0000 0 AWAITING COMMAND NETWORKIO
52 sleeping 0 0 0x0000 0 AWAITING COMMAND NETWORKIO
53 sleeping 0 0 0x0000 0 AWAITING COMMAND PAGELATCH_SH
54 sleeping 0 0 0x0000 0 AWAITING COMMAND NETWORKIO
55 runnable 0 0 0x0000 0 SELECT NETWORKIO
select spid, status, blocked, open_tran, waittype,
waittime, cmd, lastwaittype
from master..sysprocesses
where blocked!=0 or waittype != 0x0000
So because of the or waittype != 0x0000 I get a much more output under SQL 2005
It looks like all of the wonky ones are under spid 50 - which is for system pids - can anyone elaborate on that?|||I am not sure I understand what you mean. The spids where the waittype is not 0 range from 1 to 13 except 6 and 8|||You can't compare the tracking in 2000 to 2005. The number of wait types in 2005 is almost triple what it was in 2000. While several of these are due to new features such as Service Broker or Database Mirroring, many are due to a much more granular level of tracking. In 2000, a dozen of more things contributed to an OLE DB wait type whereas in 2005, each one is split out separately. You are almost always going to see system processes with a non-zero wait type and that is due to some architectural changes.
There is another side effect that you have to take into account. If the account that you are running this under does not have VIEW SERVER_STATE authority, you will get exactly 1 row from the wait stats view that is a dummy entry.
I'm not sure what you are really trying to accomplish here.
|||My point is that Microsoft whould have come up with a new version of sp_blocker_pss80 (let's call it sp_blocker_pss90) only for SQL 2005 which would have excluded these system processes with a non-zero wait type and would have made the diagnostics cleaner. Is this not a reasonable request?
|||Let's see if I understand this. You are trying to run sp_blocker_pss80, a procedure that was explicitly created for SQL Server 2000 as evidenced by the fact that part of the name is 80 in reference to version 8.0 which is the version number of SQL Server 2000, against version 9.0 - SQL Server 2005 and it is giving you different results. Microsoft extended the KB article to encompass SQL Server 2005, because it was determined that the same procedure was also applicable to SQL Server 2005, therefore a new version was never created. All code that you get is governed by their EULA, which pretty specifically states the warranties. This was also separate code that was written by someone and published as a KB article, because they thought it might be useful to someone. Is it supposed to be Microsoft's problem to make sure that before a product is released that they find every single utility script which might have been created by anyone within the entire organization and update all of them to the newest version of the product?
I have libraries of thousands of utility scripts from Sybase 4.9 through 11.5 as well as SQL Server 4.21a through 2005 that contain thousands of utility scripts. Some of them I've updated to SQL Server 2005, the vast majority haven't been touched yet. Some of the stuff from SQL Server 6.0 still works against 2005 although it gives different results.
I fail to see where this is Microsoft's issue. I also fail to see where the procedure is causing any problems. It explicitly pulls anything with a non-zero wait time. The reason that it pulls anything with a non-zero wait time is that regardless of whether it is a system process or a user process, it can possibly cause blocking and performance issues on the system which is the entire point of the procedure in the first place. Additionally, all of the code is published. If you don't like the way it is coded or works, then you are free to create your own version that does exactly what you want.
We explained what those SPIDs are and what they mean. The output from the proc you are running is still completely valid.
|||
Microsoft extended the KB article to encompass SQL Server 2005, because it was determined that the same procedure was also applicable to SQL Server 2005, therefore a new version was never created.
Well thank you for telling me that explicitly . I was determined to find out if a new version exists. Since proving the negative is all that much more difficult that proving the positive ( I just had to find a single example of sp_blocker_pss90) your explicit statement that no such code exists helps.
All code that you get is governed by their EULA, which pretty specifically states the warranties. This was also separate code that was written by someone and published as a KB article, because they thought it might be useful to someone
Apparently Microsoft itself found it useful since Roberto Farah who works at Microsofts's PSS is the co-author of Sherlock which pulls together the information from sp_blocker_pss80 and provides it in an easy to read format. Microsoft has been using it internally and decided to release it to the public.
Is it supposed to be Microsoft's problem to make sure that before a product is released that they find every single utility script which might have been created by anyone within the entire organization and update all of them to the newest version of the product?
Since Microsoft used it internally and then released it publicly I would consider it reasonable to indicate to the public to whom hey have released the tool to if any changes are due under SQL 2005 .
I have libraries of thousands of utility scripts from Sybase 4.9 through 11.5 as well as SQL Server 4.21a through 2005 that contain thousands of utility scripts. Some of them I've updated to SQL Server 2005, the vast majority haven't been touched yet. Some of the stuff from SQL Server 6.0 still works against 2005 although it gives different results.
You may also have some code written by Konrad Zuse, but I do no think it is relevant to the question I asked.
I fail to see where this is Microsoft's issue. I also fail to see where the procedure is causing any problems.
I explained the Microsoft connection above. I never said that the procedure is causing any problems. It will just produce much larger outputs than before.
If you don't like the way it is coded or works, then you are free to create your own version that does exactly what you want.
That is precisely what I intend to do . My old version of pss_blocker80 had already been modified anyway. I did consider it reasonable to inquire what is available before I change the code
We explained what those SPIDs are and what they mean.
Yes thank you. But you explained it only after I opened this query. Perhaps the same comment can be added in the source of sp_blocker_pss80 to indicate that extra SPID will appear under SQL 2005
Running sp_blocker_pss80 under SQL2005 and SQL 2000 shows many spids have non zero waittype
I was trying to test sp_blocker_pss80 (and SHERLOCK) on an SQL 2005 system and noticed that it gave much more output than on an SQL 2000 system . This was because the waittypes of many spids were not zero while under SQL 2000 the same ones were zero. Why is that?
For instance here is the subset of the query which is part of the sp_blocker_pss80 run on an sql2005 and sql2000 respectively
select spid, status, blocked, open_tran, waittype,
waittime, cmd, lastwaittype
from master..sysprocesses
SQL 2005
spid status blocked open_tran waittype waittime cmd lastwaittype
- -- -- - --
1 background 0 0 0x0000 0 RESOURCE MONITOR SLEEP_TASK
2 background 0 0 0x0060 250 LAZY WRITER LAZYWRITER_SLEEP
3 suspended 0 0 0x007F 18062 LOG WRITER LOGMGR_QUEUE
4 background 0 0 0x0080 4828 LOCK MONITOR REQUEST_FOR_DEADLOCK_SEARCH
5 background 0 0 0x009D 195558000 SIGNAL HANDLER KSOURCE_WAKEUP
6 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
7 background 0 0 0x00A2 640 TRACE QUEUE TASK SQLTRACE_BUFFER_FLUSH
8 sleeping 0 0 0x0000 0 UNKNOWN TOKEN MISCELLANEOUS
9 background 0 0 0x00A9 195579953 BRKR TASK BROKER_TRANSMITTER
10 suspended 0 0 0x0081 894421 CHECKPOINT CHECKPOINT_QUEUE
11 background 0 0 0x007E 195583703 TASK MANAGER ONDEMAND_TASK_QUEUE
12 background 0 0 0x00AD 195557968 BRKR EVENT HNDLR BROKER_EVENTHANDLER
13 background 0 0 0x00A9 195579875 BRKR TASK BROKER_TRANSMITTER
14 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
15 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
16 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
17 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
18 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
19 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
20 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
21 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
51 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
54 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
55 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
56 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
57 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
58 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
59 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
61 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
62 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
63 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
64 runnable 0 0 0x0000 0 SELECT MISCELLANEOUS
65 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
66 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
67 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
68 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
69 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
(37 row(s) affected)
SQL 2000
spid status blocked open_tran waittype waittime cmd lastwaittype
- -- -- - --
1 background 0 0 0x0000 625 LAZY WRITER SLEEP
2 sleeping 0 0 0x0000 29750 LOG WRITER RESOURCE_QUEUE
3 background 0 0 0x0000 2625 LOCK MONITOR RESOURCE_QUEUE
4 background 0 0 0x0000 0 SIGNAL HANDLER PAGEIOLATCH_SH
5 background 0 0 0x0000 0 TASK MANAGER WRITELOG
6 background 0 0 0x0000 100035093 TASK MANAGER RESOURCE_QUEUE
7 sleeping 0 0 0x0000 738921 CHECKPOINT SLEEP RESOURCE_QUEUE
8 background 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
9 background 0 0 0x0000 0 TASK MANAGER WRITELOG
10 background 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
11 background 0 0 0x0000 0 TASK MANAGER WRITELOG
12 background 0 0 0x0000 0 TASK MANAGER WRITELOG
51 sleeping 0 0 0x0000 0 AWAITING COMMAND NETWORKIO
52 sleeping 0 0 0x0000 0 AWAITING COMMAND NETWORKIO
53 sleeping 0 0 0x0000 0 AWAITING COMMAND PAGELATCH_SH
54 sleeping 0 0 0x0000 0 AWAITING COMMAND NETWORKIO
55 runnable 0 0 0x0000 0 SELECT NETWORKIO
select spid, status, blocked, open_tran, waittype,
waittime, cmd, lastwaittype
from master..sysprocesses
where blocked!=0 or waittype != 0x0000
So because of the or waittype != 0x0000 I get a much more output under SQL 2005
It looks like all of the wonky ones are under spid 50 - which is for system pids - can anyone elaborate on that?|||I am not sure I understand what you mean. The spids where the waittype is not 0 range from 1 to 13 except 6 and 8|||You can't compare the tracking in 2000 to 2005. The number of wait types in 2005 is almost triple what it was in 2000. While several of these are due to new features such as Service Broker or Database Mirroring, many are due to a much more granular level of tracking. In 2000, a dozen of more things contributed to an OLE DB wait type whereas in 2005, each one is split out separately. You are almost always going to see system processes with a non-zero wait type and that is due to some architectural changes.
There is another side effect that you have to take into account. If the account that you are running this under does not have VIEW SERVER_STATE authority, you will get exactly 1 row from the wait stats view that is a dummy entry.
I'm not sure what you are really trying to accomplish here.
|||My point is that Microsoft whould have come up with a new version of sp_blocker_pss80 (let's call it sp_blocker_pss90) only for SQL 2005 which would have excluded these system processes with a non-zero wait type and would have made the diagnostics cleaner. Is this not a reasonable request?
|||Let's see if I understand this. You are trying to run sp_blocker_pss80, a procedure that was explicitly created for SQL Server 2000 as evidenced by the fact that part of the name is 80 in reference to version 8.0 which is the version number of SQL Server 2000, against version 9.0 - SQL Server 2005 and it is giving you different results. Microsoft extended the KB article to encompass SQL Server 2005, because it was determined that the same procedure was also applicable to SQL Server 2005, therefore a new version was never created. All code that you get is governed by their EULA, which pretty specifically states the warranties. This was also separate code that was written by someone and published as a KB article, because they thought it might be useful to someone. Is it supposed to be Microsoft's problem to make sure that before a product is released that they find every single utility script which might have been created by anyone within the entire organization and update all of them to the newest version of the product?
I have libraries of thousands of utility scripts from Sybase 4.9 through 11.5 as well as SQL Server 4.21a through 2005 that contain thousands of utility scripts. Some of them I've updated to SQL Server 2005, the vast majority haven't been touched yet. Some of the stuff from SQL Server 6.0 still works against 2005 although it gives different results.
I fail to see where this is Microsoft's issue. I also fail to see where the procedure is causing any problems. It explicitly pulls anything with a non-zero wait time. The reason that it pulls anything with a non-zero wait time is that regardless of whether it is a system process or a user process, it can possibly cause blocking and performance issues on the system which is the entire point of the procedure in the first place. Additionally, all of the code is published. If you don't like the way it is coded or works, then you are free to create your own version that does exactly what you want.
We explained what those SPIDs are and what they mean. The output from the proc you are running is still completely valid.
|||
Microsoft extended the KB article to encompass SQL Server 2005, because it was determined that the same procedure was also applicable to SQL Server 2005, therefore a new version was never created.
Well thank you for telling me that explicitly . I was determined to find out if a new version exists. Since proving the negative is all that much more difficult that proving the positive ( I just had to find a single example of sp_blocker_pss90) your explicit statement that no such code exists helps.
All code that you get is governed by their EULA, which pretty specifically states the warranties. This was also separate code that was written by someone and published as a KB article, because they thought it might be useful to someone
Apparently Microsoft itself found it useful since Roberto Farah who works at Microsofts's PSS is the co-author of Sherlock which pulls together the information from sp_blocker_pss80 and provides it in an easy to read format. Microsoft has been using it internally and decided to release it to the public.
Is it supposed to be Microsoft's problem to make sure that before a product is released that they find every single utility script which might have been created by anyone within the entire organization and update all of them to the newest version of the product?
Since Microsoft used it internally and then released it publicly I would consider it reasonable to indicate to the public to whom hey have released the tool to if any changes are due under SQL 2005 .
I have libraries of thousands of utility scripts from Sybase 4.9 through 11.5 as well as SQL Server 4.21a through 2005 that contain thousands of utility scripts. Some of them I've updated to SQL Server 2005, the vast majority haven't been touched yet. Some of the stuff from SQL Server 6.0 still works against 2005 although it gives different results.
You may also have some code written by Konrad Zuse, but I do no think it is relevant to the question I asked.
I fail to see where this is Microsoft's issue. I also fail to see where the procedure is causing any problems.
I explained the Microsoft connection above. I never said that the procedure is causing any problems. It will just produce much larger outputs than before.
If you don't like the way it is coded or works, then you are free to create your own version that does exactly what you want.
That is precisely what I intend to do . My old version of pss_blocker80 had already been modified anyway. I did consider it reasonable to inquire what is available before I change the code
We explained what those SPIDs are and what they mean.
Yes thank you. But you explained it only after I opened this query. Perhaps the same comment can be added in the source of sp_blocker_pss80 to indicate that extra SPID will appear under SQL 2005
Subscribe to:
Posts (Atom)